mysql数据库
初始数据库
# 环境变量 # python -->python.exe # 在任何目录下都能够找到python.exe文件 # 才能在任意位置输入python命令启动python解释器 # mysqld install 安装mysql服务 mysql服务就被注册到操作系统中 # net start mysql 启动mysql服务 # net stop mysql # 启动客户端连接server # mysql -uroot -p123 -h192.168.14.12 # mysql>select user(); 查看当前登录的用户 # mysql>set password = password('123'); 给当前用户设置密码 # # 创建一个其他用户 # create user 'guest'@'192.168.14.%' identified by '123'; # 给一个用户授权 # grant 权限类型 on ftp.* to 'guest'@'192.168.14.%'; # grant all # grant select on day37.* to 'guest'@'192.168.14.%'; # grant select,insert # 操作数据库 # 查看所有数据库 show databases; # 创建一个数据库 create database 数据库名; # 切换到这个库下 use 数据库的名字 # 查看这个库下有多少表 show tables; # drop database 数据库 名 删除数据库。。跑路 # 操作表 # 创建一张表 create table 表名（字段名1 ，数据类型，） # create table student(name char(12),age int); # 查看表结构 # desc student; # 操作数据 # 插入数据 : insert into student values ('wusir',73); # 查询数据 : select * from student; # 修改数据 : update student set age=85 where name='alex'; # 删除数据 : delete from student where name = 'alex';
Innodb存储引擎 # 数据和索引存储在一起 2个文件 # 数据索引\表结构 # 数据持久化 # 支持事务 : 为了保证数据的完整性,将多个操作变成原子性操作 : 保持数据安全 # 支持行级锁 : 修改的行少的时候使用 : 修改数据频繁的操作 # 支持表级锁 : 批量修改多行的时候使用 : 对于大量数据的同时修改 # 支持外键 : 约束两张表中的关联字段不能随意的添加\删除 : 能够降低数据增删改的出错率 # 你了解mysql的存储引擎么? # 你的项目用了什么存储引擎,为什么? # innodb # 多个用户操作的过程中对同一张表的数据同时做修改 # innodb支持行级锁,所以我们使用了这个存储引擎 # 为了适应程序未来的扩展性,扩展新功能的时候可能会用到...,涉及到要维护数据的完整性 # 项目中有一两张xx xx表,之间的外键关系是什么,一张表的修改或者删除比较频繁,怕出错所以做了外键约束
创建表
#语法： create table 表名( 字段名1 类型[(宽度) 约束条件], 字段名2 类型[(宽度) 约束条件], 字段名3 类型[(宽度) 约束条件] ); #注意： 1. 在同一张表中，字段名是不能相同 2. 宽度和约束条件可选 3. 字段名和类型是必须的
# create table 表名( # id int, # name char(18), # 字段名3 类型[(宽度) 约束条件] # ); # 放在中括号里的内容可以不写
建表
mysql> create database staff; Query OK, 1 row affected (0.00 sec) mysql> use staff; Database changed mysql> create table staff_info (id int,name varchar(50),age int(3),sex enum('male','female'),phone bigint(11),job varchar(11)); Query OK, 0 rows affected (0.02 sec) mysql> show tables; +-----------------+ | Tables_in_staff | +-----------------+ | staff_info | +-----------------+ 1 row in set (0.00 sec) mysql> desc staff_info; +-------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(50) | YES | | NULL | | | age | int(3) | YES | | NULL | | | sex | enum('male','female') | YES | | NULL | | | phone | bigint(11) | YES | | NULL | | | job | varchar(11) | YES | | NULL | | +-------+-----------------------+------+-----+---------+-------+ 6 rows in set (0.00 sec) mysql> select id,name,sex from staff_info; Empty set (0.00 sec) mysql> select * from staff_info; Empty set (0.00 sec) 建表
插入数据
写入数据的方式 # insert into 表 values (值1,值2,值3); # 这张表有多少的字段,就需要按照字段的顺序写入多少个值 # insert into 表 values (值1,值2,值3),(值1,值2,值3),(值1,值2,值3); # 例2 insert into zzl values(1,'zeng'),(2,'alex'); # 一次性写入多条数据 # insert into 表 (字段1,字段3 ) values (值1,值3); #例3 issert into zzl(id,name) values(1,'zeng'); # 指定字段名写入,可以任意的选择表中你需要写入的字段进行 # 查表中的数据 # select * from 表 # 查看表结构 # desc 表名; # 能够查看到有多少个字段\类型\长度,看不到表编码,引擎,具体的约束信息只能看到一部分 # show create table 表名; # 能查看字段\类型\长度\编码\引擎\约束
mysql> insert into staff_info (id,name,age,sex,phone,job) values (1,'Alex',83,'female',13651054608,'IT'); Query OK, 1 row affected (0.00 sec) mysql> insert into staff_info values (2,'Egon',26,'male',13304320533,'Teacher'); Query OK, 1 row affected (0.00 sec) mysql> insert into staff_info values (3,'nezha',25,'male',13332353222,'IT'),(4,'boss_jin',40,'male',13332353333,'IT'); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from staff_info; +------+----------+------+--------+-------------+---------+ | id | name | age | sex | phone | job | +------+----------+------+--------+-------------+---------+ | 1 | Alex | 83 | female | 13651054608 | IT | | 2 | Egon | 26 | male | 13304320533 | Teacher | | 3 | nezha | 25 | male | 13332353222 | IT | | 4 | boss_jin | 40 | male | 13332353333 | IT | +------+----------+------+--------+-------------+---------+ 4 rows in set (0.00 sec) 插入数据
数据类型--数字
# int 不约束长度,最多表示10位数 # float(m,n) # m 一共多少位, # n 小数部分多少位 # create table t1( # id int, # 默认是有符号的 # age tinyint unsigned # 如果需要定义无符号的使用unsigned # ); # create table t2( # f1 float(5,2), # 保留2位小数 并四舍五入 # f2 float, # f3 double(5,2), # f4 double # ) # insert into t2(f2,f4) values(5.1783682169875975,5.1783682169875975179); # create table t3( # f1 float, # 保留2位小数 并四舍五入 # d1 double, # d2 decimal(30,20), # d3 decimal # ); # insert into t3 values(5.1783682169875975179,5.1783682169875975179, # 5.1783682169875975179,5.1783682169875975179);
int整数示例
# 创建表一个是默认宽度的int，一个是指定宽度的int(5) mysql> create table t1 (id1 int,id2 int(5)); Query OK, 0 rows affected (0.02 sec) # 像t1中插入数据1，1 mysql> insert into t1 values (1,1); Query OK, 1 row affected (0.01 sec) # 可以看出结果上并没有异常 mysql> select * from t1; +------+------+ | id1 | id2 | +------+------+ | 1 | 1 | +------+------+ 1 row in set (0.00 sec) # 那么当我们插入了比宽度更大的值，会不会发生报错呢？ mysql> insert into t1 values (111111,111111); Query OK, 1 row affected (0.00 sec) # 答案是否定的，id2仍然显示了正确的数值，没有受到宽度限制的影响 mysql> select * from t1; +------------+--------+ | id1 | id2 | +------------+--------+ | 0000000001 | 00001 | | 0000111111 | 111111 | +------------+--------+ 2 rows in set (0.00 sec) # 修改id1字段 给字段添加一个unsigned表示无符号 mysql> alter table t1 modify id1 int unsigned; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc t1; +-------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------------+------+-----+---------+-------+ | id1 | int(10) unsigned | YES | | NULL | | | id2 | int(5) | YES | | NULL | | +-------+------------------+------+-----+---------+-------+ 2 rows in set (0.01 sec) # 当给id1添加的数据大于214748364时，可以顺利插入 mysql> insert into t1 values (2147483648,2147483647); Query OK, 1 row affected (0.00 sec) # 当给id2添加的数据大于214748364时，会报错 mysql> insert into t1 values (2147483647,2147483648); ERROR 1264 (22003): Out of range value for column 'id2' at row 1 int整数示例
小数示例
# 创建表的三个字段分别为float，double和decimal参数表示一共显示5位，小数部分占2位 mysql> create table t2 (id1 float(5,2),id2 double(5,2),id3 decimal(5,2)); Query OK, 0 rows affected (0.02 sec) # 向表中插入1.23，结果正常 mysql> insert into t2 values (1.23,1.23,1.23); Query OK, 1 row affected (0.00 sec) mysql> select * from t2; +------+------+------+ | id1 | id2 | id3 | +------+------+------+ | 1.23 | 1.23 | 1.23 | +------+------+------+ 1 row in set (0.00 sec) # 向表中插入1.234，会发现4都被截断了 mysql> insert into t2 values (1.234,1.234,1.234); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> select * from t2; +------+------+------+ | id1 | id2 | id3 | +------+------+------+ | 1.23 | 1.23 | 1.23 | | 1.23 | 1.23 | 1.23 | +------+------+------+ 2 rows in set (0.00 sec) # 向表中插入1.235发现数据虽然被截断，但是遵循了四舍五入的规则 mysql> insert into t2 values (1.235,1.235,1.235); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> select * from t2; +------+------+------+ | id1 | id2 | id3 | +------+------+------+ | 1.23 | 1.23 | 1.23 | | 1.23 | 1.23 | 1.23 | | 1.24 | 1.24 | 1.24 | +------+------+------+ 3 rows in set (0.00 sec) # 建新表去掉参数约束 mysql> create table t3 (id1 float,id2 double,id3 decimal); Query OK, 0 rows affected (0.02 sec) # 分别插入1.234 mysql> insert into t3 values (1.234,1.234,1.234); Query OK, 1 row affected, 1 warning (0.00 sec) # 发现decimal默认值是(10,0)的整数 mysql> select * from t3; +-------+-------+------+ | id1 | id2 | id3 | +-------+-------+------+ | 1.234 | 1.234 | 1 | +-------+-------+------+ 1 row in set (0.00 sec) # 当对小数位没有约束的时候，输入超长的小数，会发现float和double的区别 mysql> insert into t3 values (1.2355555555555555555,1.2355555555555555555,1.2355555555555555555555); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> select * from t3; +---------+--------------------+------+ | id1 | id2 | id3 | +---------+--------------------+------+ | 1.234 | 1.234 | 1 | | 1.23556 | 1.2355555555555555 | 1 | +---------+--------------------+------+ 2 rows in set (0.00 sec) 小数示例
数据类型--时间
# date 20190620 # time 121953 # datetime 20190620121900 # datetime # year # date # time # timestamp # create table t4( # dt datetime, # y year, # d date, # t time, # ts timestamp # ); # 自动带出当前时间 # NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP # mysql> create table t5( # -> id int, # -> dt datetime NOT NULL # 不能为空 # DEFAULT CURRENT_TIMESTAMP # 默认是当前时间 # ON UPDATE CURRENT_TIMESTAMP); # 在更新的时候使用当前时间更新字段
date/time /datetime示例
mysql> create table t4 (d date,t time,dt datetime); Query OK, 0 rows affected (0.02 sec) mysql> desc t4; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | d | date | YES | | NULL | | | t | time | YES | | NULL | | | dt | datetime | YES | | NULL | | +-------+----------+------+-----+---------+-------+ 3 rows in set (0.01 sec) mysql> insert into t4 values (now(),now(),now()); Query OK, 1 row affected, 1 warning (0.01 sec) mysql> select * from t4; +------------+----------+---------------------+ | d | t | dt | +------------+----------+---------------------+ | 2018-09-21 | 14:51:51 | 2018-09-21 14:51:51 | +------------+----------+---------------------+ 1 row in set (0.00 sec) mysql> insert into t4 values (null,null,null); Query OK, 1 row affected (0.01 sec) mysql> select * from t4; +------------+----------+---------------------+ | d | t | dt | +------------+----------+---------------------+ | 2018-09-21 | 14:51:51 | 2018-09-21 14:51:51 | | NULL | NULL | NULL | +------------+----------+---------------------+ 2 rows in set (0.00 sec) date/time/datetime示例
timestamp示例
如对表进行修改.会修改成当前时间
mysql> create table t5 (id1 timestamp); Query OK, 0 rows affected (0.02 sec) mysql> desc t5; +-------+-----------+------+-----+-------------------+-----------------------------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------+------+-----+-------------------+-----------------------------+ | id1 | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | +-------+-----------+------+-----+-------------------+-----------------------------+ 1 row in set (0.00 sec) # 插入数据null，会自动插入当前时间的时间 mysql> insert into t5 values (null); Query OK, 1 row affected (0.00 sec) mysql> select * from t5; +---------------------+ | id1 | +---------------------+ | 2018-09-21 14:56:50 | +---------------------+ 1 row in set (0.00 sec) #添加一列 默认值是'0000-00-00 00:00:00' mysql> alter table t5 add id2 timestamp; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table t5 \G; *************************** 1. row *************************** Table: t5 Create Table: CREATE TABLE `t5` ( `id1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `id2` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) ERROR: No query specified # 手动修改新的列默认值为当前时间 mysql> alter table t5 modify id2 timestamp default current_timestamp; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table t5 \G; *************************** 1. row *************************** Table: t5 Create Table: CREATE TABLE `t5` ( `id1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `id2` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) ERROR: No query specified mysql> insert into t5 values (null,null); Query OK, 1 row affected (0.01 sec) mysql> select * from t5; +---------------------+---------------------+ | id1 | id2 | +---------------------+---------------------+ | 2018-09-21 14:56:50 | 0000-00-00 00:00:00 | | 2018-09-21 14:59:31 | 2018-09-21 14:59:31 | +---------------------+---------------------+ 2 rows in set (0.00 sec) timestamp示例
timestamp示例2 mysql> create table t6 (t1 timestamp); Query OK, 0 rows affected (0.02 sec) mysql> desc t6; +-------+-----------+------+-----+-------------------+-----------------------------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------+------+-----+-------------------+-----------------------------+ | t1 | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | +-------+-----------+------+-----+-------------------+-----------------------------+ 1 row in set (0.01 sec) mysql> insert into t6 values (19700101080001); Query OK, 1 row affected (0.00 sec) mysql> select * from t6; +---------------------+ | t1 | +---------------------+ | 1970-01-01 08:00:01 | +---------------------+ 1 row in set (0.00 sec) # timestamp时间的下限是19700101080001 mysql> insert into t6 values (19700101080000); ERROR 1292 (22007): Incorrect datetime value: '19700101080000' for column 't1' at row 1 mysql> insert into t6 values ('2038-01-19 11:14:07'); Query OK, 1 row affected (0.00 sec) # timestamp时间的上限是2038-01-19 11:14:07 mysql> insert into t6 values ('2038-01-19 11:14:08'); ERROR 1292 (22007): Incorrect datetime value: '2038-01-19 11:14:08' for column 't1' at row 1 mysql> timestamp示例2
year示例
mysql> create table t7 (y year); Query OK, 0 rows affected (0.02 sec) mysql> insert into t7 values (2018); Query OK, 1 row affected (0.00 sec) mysql> select * from t7; +------+ | y | +------+ | 2018 | +------+ 1 row in set (0.00 sec) year示例
datetime示例
mysql> create table t8 (dt datetime); Query OK, 0 rows affected (0.01 sec) mysql> insert into t8 values ('2018-9-26 12:20:10'); Query OK, 1 row affected (0.01 sec) mysql> insert into t8 values ('2018/9/26 12+20+10'); Query OK, 1 row affected (0.00 sec) mysql> insert into t8 values ('20180926122010'); Query OK, 1 row affected (0.00 sec) mysql> insert into t8 values (20180926122010); Query OK, 1 row affected (0.00 sec) mysql> select * from t8; +---------------------+ | dt | +---------------------+ | 2018-09-26 12:20:10 | | 2018-09-26 12:20:10 | | 2018-09-26 12:20:10 | | 2018-09-26 12:20:10 | +---------------------+ 4 rows in set (0.00 sec) datetime示例
数据类型--字符串
# char # varchar # char(18) 最多只能表示255个字符 # 定长存储,浪费空间,节省时间 # 'alex' 'alex ' # varchar(18) 最多能表示65535个字符 # 变长存储,节省空间,存取速度慢 # 'alex' 'alex4' # 适合使用char # 身份证号 # 手机号码 # qq号 # username 12-18 # password 32 # 银行卡号 # 适合使用varchar # 评论 # 朋友圈 # 微博 # create table t6(c1 char(1),v1 varchar(1),c2 char(8),v2 varchar(8)); # create table t6(c1 char,v1 varchar(1),c2 char(8),v2 varchar(8));
char/varcher 示例
mysql> create table t9 (v varchar(4),c char(4)); Query OK, 0 rows affected (0.01 sec) mysql> insert into t9 values ('ab ','ab '); Query OK, 1 row affected (0.00 sec) # 在检索的时候char数据类型会去掉空格 mysql> select * from t9; +------+------+ | v | c | +------+------+ | ab | ab | +------+------+ 1 row in set (0.00 sec) # 来看看对查询结果计算的长度 mysql> select length(v),length(c) from t9; +-----------+-----------+ | length(v) | length(c) | +-----------+-----------+ | 4 | 2 | +-----------+-----------+ 1 row in set (0.00 sec) # 给结果拼上一个加号会更清楚 mysql> select concat(v,'+'),concat(c,'+') from t9; +---------------+---------------+ | concat(v,'+') | concat(c,'+') | +---------------+---------------+ | ab + | ab+ | +---------------+---------------+ 1 row in set (0.00 sec) # 当存储的长度超出定义的长度，会截断 mysql> insert into t9 values ('abcd ','abcd '); Query OK, 1 row affected, 1 warning (0.01 sec) mysql> select * from t9; +------+------+ | v | c | +------+------+ | ab | ab | | abcd | abcd | +------+------+ 2 rows in set (0.00 sec) char/varchar示例
ENUM和SET类型
ENUM中文名称叫枚举类型，它的值范围需要在创建表时通过枚举方式显示。ENUM只允许从值集合中选取单个值，而不能一次取多个值。
SET和ENUM非常相似，也是一个字符串对象，里面可以包含0-64个成员。根据成员的不同，存储上也有所不同。set类型可以允许值集合中任意选择1或多个元素进行组合。对超出范围的内容将不允许注入，而对重复的值将进行自动去重。
# enum 单选 # set 多选 # create table t8( # id int, # name char(18), # gender enum('male','female') # ) # create table t9( # id int, # name char(18), # hobby set('抽烟','喝酒','烫头','洗脚','按摩') # ); # insert into t9 values (1,'太白','烫头,抽烟,喝酒,按摩'); # insert into t9 values (1,'大壮','洗脚,洗脚,洗脚,按摩,打游戏');
set/enum 示例
mysql> create table t10 (name char(20),gender enum('female','male')); Query OK, 0 rows affected (0.01 sec) # 选择enum('female','male')中的一项作为gender的值，可以正常插入 mysql> insert into t10 values ('nezha','male'); Query OK, 1 row affected (0.00 sec) # 不能同时插入'male,female'两个值，也不能插入不属于'male,female'的值 mysql> insert into t10 values ('nezha','male,female'); ERROR 1265 (01000): Data truncated for column 'gender' at row 1 mysql> create table t11 (name char(20),hobby set('抽烟','喝酒','烫头','翻车')); Query OK, 0 rows affected (0.01 sec) # 可以任意选择set('抽烟','喝酒','烫头','翻车')中的项，并自带去重功能 mysql> insert into t11 values ('yuan','烫头,喝酒,烫头'); Query OK, 1 row affected (0.01 sec) mysql> select * from t11; +------+---------------+ | name | hobby | +------+---------------+ | yuan | 喝酒,烫头 | +------+---------------+ 1 row in set (0.00 sec) # 不能选择不属于set('抽烟','喝酒','烫头','翻车')中的项， mysql> insert into t11 values ('alex','烫头,翻车,看妹子'); ERROR 1265 (01000): Data truncated for column 'hobby' at row 1 set/enum示例
mysql表的完整性约束
# 约束某一个字段 # 无符号的 int unsigned # 不能为空 not null # 默认值 default # 唯一约束 unique # 联合唯一 unique(字段1,字段2) # 自增 auto_increment # 只能对数字有效.自带非空约束 # 至少是unique的约束之后才能使用auto_increment # 主键 primary key # 一张表只能有一个 # 如果不指定主键,默认是第一个非空+唯一 # 联合主键 primary key(字段1,字段2) # 外键 Foreign key # Foreign key(自己的字段) references 外表(外表字段) # 外表字段必须至少是"唯一"的 # create table t10( # id int unsigned # ); # create table t11( # id int unsigned not null, # name char(18) not null # ); # create table t12( # id int unsigned not null, # name char(18) not null, # male enum('male','female') not null default 'male' #default设置默认值 # ); # 不能重复 unique 值不能重复,但是null可以写入多个 # create table t13( # id1 int unique, # id2 int # ) # 联合唯一 unique # create table t14( # id int, # server_name char(12), # ip char(15), # port char(5), # unique(ip,port) # ); # unique(ip, port) ip和port联合在一起，不能重复 # 非空 + 唯一约束 # 第一个被定义为非空+唯一的那一列会成为这张表的primary key # 一张表只能定义一个主键 # create table t15( # id int not null unique, # username char(18) not null unique # ); # create table t16( # username char(18) not null unique, # id int not null unique # ); # create table t17( # username char(18) not null unique, # id int primary key # ); # 联合主键 # create table t18( # id int, # server_name char(12), # ip char(15) default '', # port char(5) default '', # primary key(ip,port) #后面有个默认 为空。所以为空不行。用下面的就可以 # ); # create table t19( # id int primary key, # server_name char(12), # ip char(15) not null, # port char(5) not null, # unique(ip,port) # ); # 自增 # create table t20(id int unique auto increment,name char(12)); # create table t20( # id int primary key auto_increment, # name char(12) # ); # insert into t20(name) values('alex'); # 外键 # 班级表 # create table class( # cid int primary key auto_increment, # cname char(12) not null, # startd date # ) ''' # 学生表 create table stu( id int primary key auto_increment, name char(12) not null, gender enum('male','female') default 'male', class_id int, foreign key(class_id) references class(cid) ) ''' # foreign key 外键， # references 关联 # create table stu2( # id int primary key auto_increment, # name char(12) not null, # gender enum('male','female') default 'male', # class_id int, # foreign key(class_id) references class(cid) # on update cascade #叠连修改，叠连删除 # on delete cascade # 尽量不用 # )
为了防止不符合规范的数据进入数据库，在用户对数据进行插入、修改、删除等操作时，DBMS自动按照一定的约束条件对数据进行监测，使不符合规范的数据不能进入数据库，以确保数据库中存储的数据正确、有效、相容。 约束条件与数据类型的宽度一样，都是可选参数，主要分为以下几种： # NOT NULL ：非空约束，指定某列不能为空； # UNIQUE : 唯一约束，指定某列或者几列组合不能重复 # PRIMARY KEY ：主键，指定该列的值可以唯一地标识该列记录 # FOREIGN KEY ：外键，指定该行记录从属于主表中的一条记录，主要用于参照完整性
NOT NULL
是否可空，null表示空，非字符串
not null - 不可空
null - 可空
mysql> create table t12 (id int not null); Query OK, 0 rows affected (0.02 sec) mysql> select * from t12; Empty set (0.00 sec) mysql> desc t12; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | id | int(11) | NO | | NULL | | +-------+---------+------+-----+---------+-------+ 1 row in set (0.00 sec) #不能向id列插入空元素。 mysql> insert into t12 values (null); ERROR 1048 (23000): Column 'id' cannot be null mysql> insert into t12 values (1); Query OK, 1 row affected (0.01 sec) not null示例
DEFAULT
我们约束某一列不为空，如果这一列中经常有重复的内容，就需要我们频繁的插入，这样会给我们的操作带来新的负担，于是就出现了默认值的概念。
默认值，创建列时可以指定默认值，当插入数据时如果未主动设置，则自动添加默认值
not null + default 示例
mysql> create table t13 (id1 int not null,id2 int not null default 222); Query OK, 0 rows affected (0.01 sec) mysql> desc t13; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | id1 | int(11) | NO | | NULL | | | id2 | int(11) | NO | | 222 | | +-------+---------+------+-----+---------+-------+ 2 rows in set (0.01 sec) # 只向id1字段添加值，会发现id2字段会使用默认值填充 mysql> insert into t13 (id1) values (111); Query OK, 1 row affected (0.00 sec) mysql> select * from t13; +-----+-----+ | id1 | id2 | +-----+-----+ | 111 | 222 | +-----+-----+ 1 row in set (0.00 sec) # id1字段不能为空，所以不能单独向id2字段填充值； mysql> insert into t13 (id2) values (223); ERROR 1364 (HY000): Field 'id1' doesn't have a default value # 向id1，id2中分别填充数据，id2的填充数据会覆盖默认值 mysql> insert into t13 (id1,id2) values (112,223); Query OK, 1 row affected (0.00 sec) mysql> select * from t13; +-----+-----+ | id1 | id2 | +-----+-----+ | 111 | 222 | | 112 | 223 | +-----+-----+ 2 rows in set (0.00 sec) not null + default 示例
not null不生效
设置严格模式： 不支持对not null字段插入null值 不支持对自增长字段插入”值 不支持text字段有默认值 直接在mysql中生效(重启失效): mysql>set sql_mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"; 配置文件添加(永久失效)： sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" not null不生效
UNIQUE
唯一约束，指定某列或者几列组合不能重复
unique示例
方法一： create table department1( id int, name varchar(20) unique, comment varchar(100) ); 方法二： create table department2( id int, name varchar(20), comment varchar(100), unique(name) ); mysql> insert into department1 values(1,'IT','技术'); Query OK, 1 row affected (0.00 sec) mysql> insert into department1 values(1,'IT','技术'); ERROR 1062 (23000): Duplicate entry 'IT' for key 'name' unique示例
not null 和unique的结合
mysql> create table t1(id int not null unique); Query OK, 0 rows affected (0.02 sec) mysql> desc t1; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | +-------+---------+------+-----+---------+-------+ 1 row in set (0.00 sec) not null 和unique的结合
联合唯一
create table service( id int primary key auto_increment, name varchar(20), host varchar(15) not null, port int not null, unique(host,port) #联合唯一 ); mysql> insert into service values -> (1,'nginx','192.168.0.10',80), -> (2,'haproxy','192.168.0.20',80), -> (3,'mysql','192.168.0.30',3306) -> ; Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> insert into service(name,host,port) values('nginx','192.168.0.10',80); ERROR 1062 (23000): Duplicate entry '192.168.0.10-80' for key 'host' 联合唯一
PRIMARY KEY
主键为了保证表中的每一条数据的该字段都是表格中的唯一值。换言之，它是用来独一无二地确认一个表格中的每一行数据。
主键可以包含一个字段或多个字段。当主键包含多个栏位时，称为组合键 (Composite Key),也可以叫联合主键。
主键可以在建置新表格时设定 (运用 CREATE TABLE 语句)，或是以改变现有的表格架构方式设定 (运用 ALTER TABLE)。
主键必须唯一，主键值非空；可以是单一字段，也可以是多字段组合。
1.单字段主键
============单列做主键=============== #方法一：not null+unique create table department1( id int not null unique, #主键 name varchar(20) not null unique, comment varchar(100) ); mysql> desc department1; +---------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+--------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(20) | NO | UNI | NULL | | | comment | varchar(100) | YES | | NULL | | +---------+--------------+------+-----+---------+-------+ rows in set (0.01 sec) #方法二：在某一个字段后用primary key create table department2( id int primary key, #主键 name varchar(20), comment varchar(100) ); mysql> desc department2; +---------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+--------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(20) | YES | | NULL | | | comment | varchar(100) | YES | | NULL | | +---------+--------------+------+-----+---------+-------+ rows in set (0.00 sec) #方法三：在所有字段后单独定义primary key create table department3( id int, name varchar(20), comment varchar(100), primary key(id); #创建主键并为其命名pk_name mysql> desc department3; +---------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+--------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(20) | YES | | NULL | | | comment | varchar(100) | YES | | NULL | | +---------+--------------+------+-----+---------+-------+ rows in set (0.01 sec) # 方法四：给已经建成的表添加主键约束 mysql> create table department4( -> id int, -> name varchar(20), -> comment varchar(100)); Query OK, 0 rows affected (0.01 sec) mysql> desc department4; +---------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+--------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | comment | varchar(100) | YES | | NULL | | +---------+--------------+------+-----+---------+-------+ 3 rows in set (0.01 sec) mysql> alter table department4 modify id int primary key; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc department4; +---------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+--------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(20) | YES | | NULL | | | comment | varchar(100) | YES | | NULL | | +---------+--------------+------+-----+---------+-------+ 3 rows in set (0.01 sec) 单字段主键
2.多字段主键
==================多列做主键================ create table service( ip varchar(15), port char(5), service_name varchar(10) not null, primary key(ip,port) ); mysql> desc service; +--------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+-------------+------+-----+---------+-------+ | ip | varchar(15) | NO | PRI | NULL | | | port | char(5) | NO | PRI | NULL | | | service_name | varchar(10) | NO | | NULL | | +--------------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> insert into service values -> ('172.16.45.10','3306','mysqld'), -> ('172.16.45.11','3306','mariadb') -> ; Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> insert into service values ('172.16.45.10','3306','nginx'); ERROR 1062 (23000): Duplicate entry '172.16.45.10-3306' for key 'PRIMARY' 多字段主键
AUTO_INCREMENT
约束字段为自动增长，被约束的字段必须同时被key约束
设置auto_increment
#不指定id，则自动增长 create table student( id int primary key auto_increment, name varchar(20), sex enum('male','female') default 'male' ); mysql> desc student; +-------+-----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | YES | | NULL | | | sex | enum('male','female') | YES | | male | | +-------+-----------------------+------+-----+---------+----------------+ mysql> insert into student(name) values -> ('egon'), -> ('alex') -> ; mysql> select * from student; +----+------+------+ | id | name | sex | +----+------+------+ | 1 | egon | male | | 2 | alex | male | +----+------+------+ #也可以指定id mysql> insert into student values(4,'asb','female'); Query OK, 1 row affected (0.00 sec) mysql> insert into student values(7,'wsb','female'); Query OK, 1 row affected (0.00 sec) mysql> select * from student; +----+------+--------+ | id | name | sex | +----+------+--------+ | 1 | egon | male | | 2 | alex | male | | 4 | asb | female | | 7 | wsb | female | +----+------+--------+ #对于自增的字段，在用delete删除后，再插入值，该字段仍按照删除前的位置继续增长 mysql> delete from student; Query OK, 4 rows affected (0.00 sec) mysql> select * from student; Empty set (0.00 sec) mysql> insert into student(name) values('ysb'); mysql> select * from student; +----+------+------+ | id | name | sex | +----+------+------+ | 8 | ysb | male | +----+------+------+ #应该用truncate清空表，比起delete一条一条地删除记录，truncate是直接清空表，在删除大表时用它 mysql> truncate student; Query OK, 0 rows affected (0.01 sec) mysql> insert into student(name) values('egon'); Query OK, 1 row affected (0.01 sec) mysql> select * from student; +----+------+------+ | id | name | sex | +----+------+------+ | 1 | egon | male | +----+------+------+ row in set (0.00 sec) 设置auto_increment
offset偏移量
#在创建完表后，修改自增字段的起始值 mysql> create table student( -> id int primary key auto_increment, -> name varchar(20), -> sex enum('male','female') default 'male' -> ); mysql> alter table student auto_increment=3; mysql> show create table student; ....... ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 mysql> insert into student(name) values('egon'); Query OK, 1 row affected (0.01 sec) mysql> select * from student; +----+------+------+ | id | name | sex | +----+------+------+ | 3 | egon | male | +----+------+------+ row in set (0.00 sec) mysql> show create table student; ....... ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 #也可以创建表时指定auto_increment的初始值，注意初始值的设置为表选项，应该放到括号外 create table student( id int primary key auto_increment, name varchar(20), sex enum('male','female') default 'male' )auto_increment=3; #设置步长 sqlserver：自增步长 基于表级别 create table t1（ id int。。。 ）engine=innodb,auto_increment=2 步长=2 default charset=utf8 mysql自增的步长： show session variables like 'auto_inc%'; #基于会话级别 set session auth_increment_increment=2 #修改会话级别的步长 #基于全局级别的 set global auth_increment_increment=2 #修改全局级别的步长（所有会话都生效） #！！！注意了注意了注意了！！！ If the value of auto_increment_offset is greater than that of auto_increment_increment, the value of auto_increment_offset is ignored. 翻译：如果auto_increment_offset的值大于auto_increment_increment的值，则auto_increment_offset的值会被忽略 ，这相当于第一步步子就迈大了，扯着了蛋 比如：设置auto_increment_offset=3，auto_increment_increment=2 mysql> set global auto_increment_increment=5; Query OK, 0 rows affected (0.00 sec) mysql> set global auto_increment_offset=3; Query OK, 0 rows affected (0.00 sec) mysql> show variables like 'auto_incre%'; #需要退出重新登录 +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | auto_increment_increment | 1 | | auto_increment_offset | 1 | +--------------------------+-------+ create table student( id int primary key auto_increment, name varchar(20), sex enum('male','female') default 'male' ); mysql> insert into student(name) values('egon1'),('egon2'),('egon3'); mysql> select * from student; +----+-------+------+ | id | name | sex | +----+-------+------+ | 3 | egon1 | male | | 8 | egon2 | male | | 13 | egon3 | male | +----+-------+------+ 步长:auto_increment_increment,起始偏移量:auto_increment_offset offset偏移量
FOREIKEY
多表 ：
假设我们要描述所有公司的员工，需要描述的属性有这些 ： 工号 姓名 部门
公司有3个部门，但是有1个亿的员工，那意味着部门这个字段需要重复存储，部门名字越长，越浪费
解决方法： 我们完全可以定义一个部门表 然后让员工信息表关联该表，如何关联，即foreign key
创造外键的条件
mysql> create table departments (dep_id int(4),dep_name varchar(11)); Query OK, 0 rows affected (0.02 sec) mysql> desc departments; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | dep_id | int(4) | YES | | NULL | | | dep_name | varchar(11) | YES | | NULL | | +----------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) # 创建外键不成功 mysql> create table staff_info (s_id int,name varchar(20),dep_id int,foreign key(dep_id) references departments(dep_id)); ERROR 1215 (HY000): Cannot add foreign key # 设置dep_id非空，仍然不能成功创建外键 mysql> alter table departments modify dep_id int(4) not null; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc departments; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | dep_id | int(4) | NO | | NULL | | | dep_name | varchar(11) | YES | | NULL | | +----------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> create table staff_info (s_id int,name varchar(20),dep_id int,foreign key(dep_id) references departments(dep_id)); ERROR 1215 (HY000): Cannot add foreign key constraint # 当设置字段为unique唯一字段时，设置该字段为外键成功 mysql> alter table departments modify dep_id int(4) unique; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc departments; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | dep_id | int(4) | YES | UNI | NULL | | | dep_name | varchar(11) | YES | | NULL | | +----------+-------------+------+-----+---------+-------+ 2 rows in set (0.01 sec) mysql> create table staff_info (s_id int,name varchar(20),dep_id int,foreign key(dep_id) references departments(dep_id)); Query OK, 0 rows affected (0.02 sec) 创造外键的条件
外键操作示例
#表类型必须是innodb存储引擎，且被关联的字段，即references指定的另外一个表的字段，必须保证唯一 create table department( id int primary key, name varchar(20) not null )engine=innodb; #dpt_id外键，关联父表（department主键id），同步更新，同步删除 create table employee( id int primary key, name varchar(20) not null, dpt_id int, foreign key(dpt_id) references department(id) on delete cascade # 级连删除 on update cascade # 级连更新 )engine=innodb; #先往父表department中插入记录 insert into department values (1,'教质部'), (2,'技术部'), (3,'人力资源部'); #再往子表employee中插入记录 insert into employee values (1,'yuan',1), (2,'nezha',2), (3,'egon',2), (4,'alex',2), (5,'wusir',3), (6,'李沁洋',3), (7,'皮卡丘',3), (8,'程咬金',3), (9,'程咬银',3) ; #删父表department，子表employee中对应的记录跟着删 mysql> delete from department where id=2; Query OK, 1 row affected (0.00 sec) mysql> select * from employee; +----+-----------+--------+ | id | name | dpt_id | +----+-----------+--------+ | 1 | yuan | 1 | | 5 | wusir | 3 | | 6 | 李沁洋 | 3 | | 7 | 皮卡丘 | 3 | | 8 | 程咬金 | 3 | | 9 | 程咬银 | 3 | +----+-----------+--------+ 6 rows in set (0.00 sec) #更新父表department，子表employee中对应的记录跟着改 mysql> update department set id=2 where id=3; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from employee; +----+-----------+--------+ | id | name | dpt_id | +----+-----------+--------+ | 1 | yuan | 1 | | 5 | wusir | 2 | | 6 | 李沁洋 | 2 | | 7 | 皮卡丘 | 2 | | 8 | 程咬金 | 2 | | 9 | 程咬银 | 2 | +----+-----------+--------+ 6 rows in set (0.00 sec) 外键操作示例
