测试时间类型数据+完整性约束条件+表相关操作

一、测试时间类型数据

1、测试YEAR

建表

插入数据

查表
year的范围是1901到2155
输入超过这个范围的数值会报错

当日期以字符串形式插入也可以
当插入的是两位数字或者两位字符串时，00-69之间或转换到2000-2069年；70-99之间会转换成1970-1999年之间

当插入数字0时，我们得到是数字0

当插入的是字符串0时，得到的是2000年

2、TIME类型

在time里面第一个值36等于1天24小时+12小时，第一个值=天数*24+小时数

省略秒数
当插入的是一个四位数时，小时的位置省略掉，存入的是分钟和秒数

如何插入的是两位数，那么插入的是秒数

在time里面不管是字符串的0还是整数0，得到的都是四个0


如果插入的值超过了存储范围，则会报错

3、测试date

输入字符串，年月日之间用“-”分割

或者是用/分割

或者是120706

二、完整性约束条件

1、主键

主键：唯一标识符，一般会加到无意义字段上，例如编号。注意：被标记成主键的字段数值不能重复，且自动非空。

建表：

mysql> CREATE TABLE IF NOT EXISTS user1(
    -> id INT PRIMARY KEY,
    -> username VARCHAR(20)
    -> );

查看表结构

mysql> DESC user1;

key这一列显示PRI则表示主键
查看创建表的定义

mysql> SHOW CREATE TABLE 表名；

可以看到PRIMARY KEY 是id字段
插入数据

INSERT user1 VALUES(1,'king');
INSERT user1 VALUES(2,'QUEEN');

查看表

被标记成主键的字段数值不能重复
查看信息的时候可以通过主键来查询

SELECT * FROM user1 WHERE id=1;

多字段主键：

mysql> CREATE TABLE IF NOT EXISTS user3(
    -> id INT,
    -> username VARCHAR(20),
    -> card CHAR(18),
    -> PRIMARY KEY(id, card)
    -> );

查看表结构：

可以看出来有两个主键字段PRI
这是复合主键——一个主键，多字段主键
一个表中只能有一个主键，不能有多个主键
当id一样，card不一样时，是两条记录
注意：索引的时候，将复合主键的第一个字段作为索引

创建主键的时候可以将PIRMARY KEY 简写为KEY
主键字段可以是任意的，只要保证该字段没有重复记录即可。

2、自增长

自增长：让新增加的用户编号自动增加1
**注意：**一个表中只能有一个自增长字段，而且这个自增长需要配合主键来使用
被标记成自增长的字段一定是主键，但是主键不一定是自增长的
自增长只对整数字段有效果，对字符串没有意义
创建表六，令编号字段作为主键，且复合自增长

mysql> CREATE TABLE IF NOT EXISTS user6(
    -> id SMALLINT KEY AUTO_INCREMENT,
    -> username VARCHAR(20)
    -> );

插入数据，第一种可以指定编号

或者是只给用户名字段赋值

查看表

当我们不指定标号，只给其他字段赋值的时候，得到的自增长编号是已有编号最大值加一

mysql> INSERT user6 VALUES(111,'little star');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM user6;

mysql> INSERT user6(username) VALUES('QUEEN1');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM user6;

我们可以查看一下表的定义

mysql> SHOW CREATE TABLE user6;

此时，表的自增长编号为113，下一个自增长编号为113

编号字段插入null值也是可以的

mysql> INSERT user6 VALUES(NULL, 'AAAA');
Query OK, 1 row affected (0.01 sec)

或者是defualt:INSERT user6 VALUES(DEFAULT,‘CUT STAR’);

也可以在创建的时候，定义好自增长的初始值

mysql>  CREATE TABLE IF NOT EXISTS use8(
    -> id SMALLINT KEY AUTO_INCREMENT,
    -> username VARCHAR(20)
    -> )AUTO_INCREMENT=100;

更改自增长编号

 ALTER TABLE user8 AUTO_INCREMENT=500;

3、非空约束

非空约束：用户名和密码字段一定要非空

mysql> CREATE TABLE IF NOT EXISTS user_1(
    -> id INT UNSIGNED KEY AUTO_INCREMENT,
    -> username VARCHAR(20) NOT NULL,
    -> password CHAR(32) NOT NULL,
    -> age TINYINT UNSIGNED
    -> );

其中，id是非负的，既是逐渐，也复合自增长
username不为空，密码不为空，年龄不为负数

插入数据：由于用户名和密码一定不能为空，我们可以只给这两个字段赋值，由于id字段是主键且自增长，所以可以不赋值

INSERT user_1(username,password) VALUES('KING',123456);

也可以给年龄字段赋值

 INSERT user_1(username,password,age) VALUES('queen',151265,18);

4、测试默认值

一般来说not null 会配合默认值一起使用

CREATE TABLE IF NOT EXISTS user_2(

id INT UNSIGNED KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL,

password CHAR(32) NOT NULL,
age TINYINT UNSIGNED DEFAULT 18,
addr VARCHAR(50) NOT NULL DEFAULT '北京',
sex ENUM('男','女','保密') NOT NULL DEFAULT '男'
);

地址和性别字段不能为空，但是如果不赋值的话，就以默认值输入

插入数据，值给不能为空的用户名和密码赋值

 INSERT user_2(username,password) VALUES('queen',151265);

其他字段按照默认值输入
当所有的字段都赋值的时候就按照我们的赋值进行输入

 INSERT user_2 VALUES(2,'king',151265,25,'上海','保密');

或者是INSERT user_2 VALUES(2,‘king’,151265,DEFAULT,‘上海’,‘保密’);

在默认值字段使用default值

5、唯一UNIQUE

UNIQUE KEY 中key可以省略掉
PRIMARY KEY中PRIMARY可以省略掉
一个表中只能有一个主键，但是可以有多个唯一
null值不算重复值

mysql> CREATE TABLE IF NOT EXISTS user_3(
    -> id INT UNSIGNED KEY AUTO_INCREMENT,
    -> username VARCHAR(20) NOT NULL UNIQUE,
    -> card CHAR(18) UNIQUE
    -> );

SHOW CREATE TABLE user_3;

其中有两个唯一索引：username和card

我们可以只给用户名赋值

INSERT user_3(username) VALUES('A');

INSERT user_3(username) VALUES('A1');

card部分没有给值，默认是null，null值不算重复

给身份证赋值INSERT user_3(username,age) VALUES('b',111);

身份证字段填写null是可成功的

总结，建表语法结构：
CREATE TABLE [IF NOT EXISTS0] 表名称(
字段名称 字段类型 [UNSIGNED|ZEROFILL] [NOT NULL] [DEFAULT 默认值] [[PRIMARY] KEY| UNIQUE [KEY]] [AUTO_INCREMENT]
)ENGINES=INNODB CHARSET=utf8 AUTO_INCREMENT=100;
按照上述的顺序进行编辑或省略

三、表相关操作

1、重命名数据表

CREATE TABLE IF NOT EXISTS user_4(
id SMALLINT UNSIGNED KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL UNIQUE,
password CHAR(32) NOT NULL,
email VARCHAR(50) NOT NULL DEFAULT '852989197@qq.com',
age TINYINT UNSIGNED DEFAULT 18,
sex ENUM('男','女','保密') DEFAULT '保密',
addr VARCHAR(200) NOT NULL DEFAULT '北京',
salary FLOAT(6,2),
regTime INT UNSIGNED,
face CHAR(100) NOT NULL DEFAULT 'default.jpg'
)ENGINE=INNODB CHARSET=UTF8;

查看表结构

修改表名 ：
ALTER TABLE 原表名 RENAME 【TO | AS】新表名;
或者是RENAME TBALE user_4 TO user_5;

2、添加和删除字段——都是通过ALTER语句来实现的

ALTER TABLE 表名 ADD card字段 CHAR(18)类型;

添加字段并赋约束条件

ALTER TABLE user_4 ADD test1 VARCHAR(100) NOT NULL UNIQUE;

将字段添加到第一个位置

ALTER TABLE user_4 ADD test2 VARCHAR(20) NOT NULL FIRST;

添加到指定位置之后

ALTER TABLE user_4 ADD test3 INT NOT NULL DEFAULT 1OO AFTER username;

选中一个表，一次进行多项操作

ALTER TABLE user_4
ADD test4 INT NOT NULL DEFAULT 123 AFTER password,
ADD test5 FLOAT(6,2) FIRST,
ADD test6 SET('A','B','C'); 

添加和删除同时进行

ALTER TABLE user_4
ADD test INT UNSIGNED NOT NULL DEFAULT 10 AFTER sex,
DROP addr;

3、修改字段

（1）MODIFY只能修改字段类型和位置，但是不能修改字段名称

ALTER TABLE 表名称 MODIFY 字段名称 字段类型 完整性约束条件 位置

ALTER TABLE user_4 MODIFY email VARCHAR(200);

将email字段的存储空间改为200，但是原来的完整性约束条件没有了

改变类型的时候，需要确保其他完整性约束条件

ALTER TABLE user_4 MODIFY email VARCHAR(50) NOT NULL DEFAULT '852989197@qq.com';

修改字段的位置
将card 字段修改到test 字段之后

ALTER TABLE user_4 MODIFY card CHAR(18) AFTER test;

ALTER TABLE user_4 MODIFY test VARCHAR(32) NOT NULL DEFAULT '123' FIRST;

（2）使用change关键字来修改字段名称

ALTER TABLE 表明 CHANGE 旧字段名称 新字段名称 字段类型 完整性约束条件 位置
例如：将test字段改名为test1，其他都不变

ALTER TABLE user_4 CHANGE test test1 CHAR(32) NOT NULL DEFAULT '123';

可以同时修改字段名称和位置以及属性

 ALTER TABLE user_4 CHANGE test1 test VARCHAR(32) NOT NULL AFTER username;

仅改变属性的情况

ALTER TABLE user_4 CHANGE test test INT;

CHANGE可以完成MODIFY的功能，但是大可不必，我们可以直接通过MODIFY来修改字段的类型等

4、 添加删除默认值

mysql> CREATE TABLE IF NOT EXISTS user_5(
    -> id TINYINT UNSIGNED KEY AUTO_INCREMENT,
    -> username VARCHAR(20) NOT NULL UNIQUE,
    -> age TINYINT UNSIGNED
    -> );

 ALTER TABLE user_5 ALTER age SET DEFAULT 18;

添加email字段

ALTER TABLE user_5 ADD email VARCHAR(50);

然后再添加默认值

ALTER TABLE user_5 ALTER email SET DEFAULT '852989197@qq.com';

删除默认值，选中表，选中字段，删除默认值

 ALTER TABLE user_5 ALTER age DROP DEFAULT;

5、添加删除主键

添加主键ALTER TABLE 表名 ADD PRIMARY KEY(字段)

mysql> CREATE TABLE user_6(
    -> id INT
    -> );

最初是没有主键的

添加主键：ALTER TABLE user_6 ADD PRIMARY KEY(id);

mysql> CREATE TABLE user_7(
    -> id INT,
    -> card CHAR(18),
    -> username VARCHAR(20) NOT NULL
    -> );

同时添加两个主键
ALTER TABLE user_7 ADD PRIMARY KEY(id, card);

删除主键

 ALTER TABLE user_6 DROP PRIMARY KEY;

删除复合主键

 ALTER TABLE user_7 DROP PRIMARY KEY;

代码一样，是因为主键只能有一个
添加主键的第二种方式：

ALTER TABLE user_6 ADD CONSTRAINT symbol PRIMARY KEY index_type(id);

建表，id既是主键，又是自增长的

mysql> CREATE TABLE IF NOT EXISTS user_8(
    -> id INT UNSIGNED KEY AUTO_INCREMENT
    -> );

这种情况下直接删除主键会报错
一个表中有且只有一个列是自增长列，而且这一列必须是主键
此时要删除id这一列的话，必须先删除它的自增长属性，用modify

ALTER TABLE user_8 MODIFY id INT UNSIGNED;

这个命令就将id字段的属性更改了，也就是说把自增长的属性去掉了
然后再来删除主键就可以了

ALTER TABLE user_8 DROP PRIMARY KEY;

6、添加删除唯一

ALTER TABLE 表名 ADD [CONSTRAINT [symbol] UNIQUE [INDEX KEY] 索引名称

mysql> CREATE TABLE IF NOT EXISTS game1(
    -> id TINYINT UNSIGNED KEY AUTO_INCREMENT,
    -> username VARCHAR(20) NOT NULL,
    -> card CHAR(18) NOT NULL,
    -> test VARCHAR(20) NOT NULL,
    -> test1 CHAR(32) NOT NULL
    -> );

添加唯一字段

ALTER TABLE game1 ADD UNIQUE(username);

当没有索引名称的时候，系统将字段名称默认为索引名称

ALTER TABLE game1 ADD CONSTRAINT symbol UNIQUE KEY uni_card(card);

索引名称在字段名称之前，字段名称需要加括号card字段前面有了索引名称uni_card

添加复合索引

ALTER TABLE game1 ADD CONSTRAINT symbol UNIQUE INDEX mulUni_test_test1(test, test1);

形成了复合索引 MUL

从表定义可以看出复合索引有两个字段，test和test1

删除唯一索引
ALTER TABLE 表名 DROP {INDEX|KEY} 索引

ALTER TABLE game1 DROP INDEX username;
ALTER TABLE game1 DROP INDEX uni_card;
ALTER TABLE game1 DROP INDEX mulUni_test_test1;

修改存储引擎

ALTER TABLE game1 ENGINE=MyISAM;

修改自增长值

前提条件是一定要有一个字段是自增长的

ALTER TABLE game1 AUTO_INCREMENT=100;

7、删除数据表

表删除的同时，数据也都删除了
DROP TABLE [IF EXISTS] 表名
例如：DROP TABLE user_info;或者DROP TABLE IF EXISTS user_info;
同时删除多张表格
DROP TABLE IF EXISTS user_8,user_7,user_6;
注意：在一开始登录的时候就可以直接打开需要运用的数据库，例如：mysql -uroot -p -D test

8、数据增删改查

（1）插入数据：不指定具体的字段名，需要按照建表时的字段顺序，给每个字段都赋值
INSERT table VALUES(数值)

mysql> CREATE TABLE IF NOT EXISTS finall(
    -> id TINYINT UNSIGNED AUTO_INCREMENT KEY,
    -> username VARCHAR(20) NOT NULL UNIQUE,
    -> password CHAR(32) NOT NULL,
    -> email VARCHAR(50)NOT NULL DEFAULT '852989197@qq.com',
    -> age TINYINT UNSIGNED DEFAULT 18
    -> );

INSERT finall VALUES(1,‘QUEEN’,123456,0,20);

（2）可以列出指定的字段，并赋值，字段的顺序要和值的顺序一致，想写默认值的时候，在该字段填写DEFAULT

INSERT finall(username,password) VALUES('A','AAA');

INSERT finall(password,username) VALUES('BBB','B');

（3）一次插入多条记录
INSERT 表名称 VALUES(值，第二个值…)，

mysql> INSERT finall VALUES(4,'C','CCC',DEFAULT,30),
    -> (5,'D','DDD',DEFAULT,20),
    -> (6,'E','EEE',DEFAULT,35) ;

（4）通过insert set形式插入键值对

INSERT finall SET id=99,username='pysche',password='151265mylove',email=DEFAULT,age=18;

或者只插入我们需要的字段数据

INSERT finall SET username='小星星',age=23,password='little_star';

（5）将查询结果插入到表中
INSERT 表名 SELECT 字段名 FROM 表名
这里必须保证字段和数据类型都符合

INSERT testuser SELECT id,username FROM finall;

或者只插入其他表中的一个字段

INSERT testuser(username) SELECT username FROM finall;

9、更新数据

UPDATE 表名 SET 字段名=值 [WHERE 条件] [ORDER BY 字段名][LIMIT限制条数]
注意：如果不加where条件，表中所有该字段的数据都会被更新

UPDATE finall SET age=5;

不加条件，表中所有记录都会更新

当同时改变两个字段的时候，用逗号进行分割

当同时改变两个字段的时候，用逗号进行分割

加上条件，一般来说用唯一标识符字段来作为条件

 UPDATE finall SET password='151265mylove',email='852989197@qq.com',age=18 WHERE id=1;

UPDATE finall SET age=age-5 WHERE id>3;

删除数据
DELETE FROM 表名 [WHERE 条件] [ORDER BY 字段名][LIMIT限制条数]

DELETE FROM testuser; 

不加条件的话会把表中所有数据都删除

DELETE FROM finall WHERE id=1;

只删除id=1的数据记录

将表中所有记录都删除 DELETE FROM finall;
再次增加值的时候，看一下自增长值的变化

INSERT finall SET username='psyche',age=18,password='151265mylove';

说明虽然把表中的记录都删除了，但是自增长还是从原来的开始

所以在删除记录之后要改变自增长

彻底清空数据表：TURNCATE 表名

可以清空数据并重置自增长的值

TRUNCATE [TABLE] finall;

注意：彻底清空不能带有where条件