MySQL的数据类型，MySQL增删改--添加主外键、添加属性、删除主外键、改表名、获取系统当前时间等

2023-08-30 17:53:45

ls /etc/rc.d/init.d/mysql56
service mysql56 start

ps aux |grep "mysql"|grep "socket=" --color
mysql -S/var/run/mysqld/mysql56.sock
[root@localhost ~]# service mysql56 start
Starting MySQL.. SUCCESS!
[root@localhost ~]# mysql -S/var/run/mysqld/mysql56.sock

vim /etc/my.cnf
作为mysql这个客户端程序的配置文件
[mysql]
socket=/var/run/mysqld/mysql56.sock
prompt=\u@\h>

create table table s(stuID int zerofill);
alter database sx charset=utf8 创建数据库时将字符集设为utf8，这样在sx数据库下建的表的字符集默认都为utf8，主要是避免出现乱码。
alter table s add stuName varchar(32) default 'tom' not null; 主键也是一种特殊的索引，自动增长列必须是索引
alter table s add primary key(stuID) ; 给s表添加主键
alter table s modify stuID int auto_increment; 将s表中的stuID改为自增长变量
alter table s modify stuID int zerofill auto_increment;
alter table s modify stuID int(4) zerofill auto_increment;
mysql> insert into s(stuName) select '';
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> set names utf8; 将客户端输入的名字转为utf8，保证客户端与表端字符集一致
Query OK, 0 rows affected (0.00 sec)

mysql> select last_insert_id();         查询最后一次插入数据的id号
+------------------+
| last_insert_id() |
+------------------+
|                5 |
+------------------+
1 row in set (0.00 sec)
show create table s;                   显示创建表的过程
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                   |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| s     | CREATE TABLE `s` (
`stuID` int(4) unsigned zerofill NOT NULL AUTO_INCREMENT,
`stuName` varchar(32) NOT NULL DEFAULT 'tom',
PRIMARY KEY (`stuID`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

text数据类型不区分大小写

set数据类型，一般用enum，不用set
set不过是取值范围的限制
mysql> alter table s add ab set('L','M','N','O');
Query OK, 5 rows affected (0.09 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql> insert into s(ab) values('L');
Query OK, 1 row affected (0.00 sec)

mysql> select * from s ;
+-------+---------+------+
| stuID | stuName | ab   |
+-------+---------+------+
| 0001 | tom     | NULL |
| 0002 | tom     | NULL |
| 0003 | tom     | NULL |
| 0004 |         | NULL |
| 0005 | ??      | NULL |
| 0006 | tom     | L    |
+-------+---------+------+
6 rows in set (0.00 sec)

mysql> insert into s(ab) values('L,M,N,O');
Query OK, 1 row affected (0.00 sec)

mysql> select * from s ;
+-------+---------+---------+
| stuID | stuName | ab      |
+-------+---------+---------+
| 0001 | tom     | NULL    |
| 0002 | tom     | NULL    |
| 0003 | tom     | NULL    |
| 0004 |         | NULL    |
| 0005 | ??      | NULL    |
| 0006 | tom     | L       |
| 0007 | tom     | L,M,N,O |
+-------+---------+---------+
7 rows in set (0.00 sec)

日期时间
datetime
date
time
year
timestamp
mysql> -- hiredate 2012-1-2 3:4:5
mysql> alter table stu change a hiredate datetime; 将stu表中的a改名为hiredate，数据类型为datetime，即年月日时分秒形式
Query OK, 3 rows affected (0.28 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> insert into stu(hiredate,sno) select '2013-1-2',4;
Query OK, 1 row affected, 1 warning (0.05 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> insert into stu(hiredate,sno) select '2013-1-2 03:04:05',5;
Query OK, 1 row affected, 1 warning (0.04 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> create table stu2(a datetime,b timestamp); 创建表stu2，a的数据类型为datetime,b的数据类型为timestamp,timestamp会自动获取系统的当前时间
Query OK, 0 rows affected (0.06 sec)

mysql> select current_timestamp; 获取当前时间
+---------------------+
| current_timestamp |
+---------------------+
| 2013-07-18 10:56:52 |
+---------------------+
1 row in set (0.03 sec)
mysql> insert into stu2(a) select '2013-1-2 2:3:4';
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> select * from stu2;
+---------------------+---------------------+
| a                   | b                   |
+---------------------+---------------------+
| 2013-01-02 02:03:04 | 2013-07-18 10:57:46 |
+---------------------+---------------------+
1 row in set (0.00 sec)
mysql> select now();       取出当前服务器的时间
+---------------------+
| now()               |
+---------------------+
| 2013-07-18 10:58:17 |
+---------------------+
1 row in set (0.00 sec)
mysql> insert into stu2 select now(),now();
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> select * from stu2;
+---------------------+---------------------+
| a                   | b                   |
+---------------------+---------------------+
| 2013-01-02 02:03:04 | 2013-07-18 10:57:46 |
| 2013-07-18 10:59:22 | 2013-07-18 10:59:22 |
+---------------------+---------------------+
2 rows in set (0.00 sec)
mysql> show variables like '%zone%';
+------------------+--------+
| Variable_name    | Value |
+------------------+--------+
| system_time_zone | CST    |
| time_zone        | SYSTEM |
+------------------+--------+
2 rows in set (0.00 sec)

mysql> set time_zone='+8:00';
Query OK, 0 rows affected (0.00 sec)

设计数据库的时候，三种完整性：
1、实体完整性：通过主键来保证
2、引用的完整性：通过外键来保证
mysql> alter table stu add foreign key(deptno) references dept(deptno);             将dept表中的deptno作为stu表中的外键
3、用户自定义的完整性，域的完整性
create table stu(
      stuID int,
       age int check(age >=0 and age<=120)   --在sqlServer中可以实现，但在Mysql中还实现不了
);
check约束在客户端实现，不在db中实现
mysql> set foreign_key_checks=on;
Query OK, 0 rows affected (0.00 sec)

mysql> set foreign_key_checks=off;
Query OK, 0 rows affected (0.00 sec)

sql
structured query language
client->server
ddl definition create dabetase table alter drop table
dml insert delete update select
dcl control grant deny revoke
sql标准：
create table ...;
ansi,iso/iec
ansi C iso c
sql -86
sql:2003
ansi c iso c
gcc
在mysql中，and的优先级比or高
mysql> create database if not exists sx charset=utf8; 创建数据库的时候就指定字符集，这样在数据库下创建表时，字符集也都是创建数据库时指定的字符集，但是建议在创建表时指定字符集
在Mysql中不能改数据库名，但在微软的sqlServer中可以改

mysql> create table t1 like dept; 创建与dept结构一致的表t1
Query OK, 0 rows affected (0.15 sec)

mysql> select * from dept;
+--------+----------+
| deptno | deptname |
+--------+----------+
|      1 | dsh      |
|      2 | sdfg     |
|      3 | swgrwg   |
+--------+----------+
3 rows in set (0.00 sec)

mysql> select * from t1;
Empty set (0.00 sec)

mysql> create table t2 select * from dept; 创建一个与dept结构一致的表t2并将dept中的数据导入到t2中
Query OK, 3 rows affected (0.10 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> create table t3 select * from dept where deptno>2; 创建表时刷选数据导入t3中
Query OK, 1 row affected (0.08 sec)
Records: 1 Duplicates: 0 Warnings: 0

alter table t3 rename table3;                        将表名t3改为table3
alter table table3 add deptLeader varchar(32) not null;          添加属性deptLeader
alter table table3 drop deptLeader;                                      删除属性deptLeader
alter table table3 modify deptName varchar(64) not null default'cc';
alter table table3 change deptno deptID int;     修改属性名称
alter table table3 add primary key(deptID);   添加主键
alter table table3 drop primary key;        删除主键
alter table stu2 engine=myisam;      设置引擎
alter table stu2 charset=utf8;            设置字符集
mysql> insert into dept values(8,'a1'),(9,'a2'),(10,'a3'); （微软的sqlserver不支持这样一次性插入多条数据，逗号隔开，微软的sqlserver2008可以支持多条数据的插入eg:insert into dept values(8,'a1') (9,'a2') (10,'a3');只是中间没逗号而已）
Query OK, 3 rows affected (0.04 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> truncate table stu; 将stu表中的数据都删除
Query OK, 0 rows affected (0.03 sec)

mysql> select * from stu;
Empty set (0.00 sec)

mysql> select * from dept;
+--------+----------+
| deptno | deptname |
+--------+----------+
|      1 | dsh      |
|      2 | sdfg     |
|      3 | swgrwg   |
|      5 | sales    |
|      6 | NULL     |
|      8 | a1       |
|      9 | a2       |
|     10 | a3       |
+--------+----------+
8 rows in set (0.00 sec)
mysql> select deptno into @a from dept where deptname='dsh';
Query OK, 1 row affected (0.00 sec)

mysql> update stu set age=age*1.1 where deptno=@a;
mysql> select @a;
+------+
| @a |
+------+
| 1 |
+------+
1 row in set (0.00 sec)

码农公寓

相关文章