MySQL - 思维导图

数据库相关概念

DB（数据库）

按照数据结构来组织、存储和管理数据的仓库

DBMS（数据库管理系统）

DBA（数据库管理员）

pymysql模块

import pymysql db=pymysql.connect('localhost','root','','day040') cursor=db.cursor() #创建游标 book_list=["学python从开始到放弃|alex|人民大学出版社|50|2018-7-1","学mysql从开始到放弃|egon|机械工业出版社|60|2018-6-3", "学html从开始到放弃|alex|机械工业出版社|20|2018-4-1","学css从开始到放弃|wusir|机械工业出版社|120|2018-5-2", "学js从开始到放弃|wusir|机械工业出版社|100|2018-7-30"] # for row in book_list: # book=row.split("|") # book[-2]=float(book[-2]) # book=tuple(book) # sql=f"-- insert into book2(b_name,b_author,b_press,b_price,publish_date) values {book};" # cursor.execute(sql) #执行语句，只在内存执行 # db.commit() #提交给数据库修改（增删改需要提交才能完成） sql='select * from book2' cursor.execute(sql) results=cursor.fetchall() for row in results: for i in row: print(i,end='') print() db.close()

连接数据库

db=pymysql.connect(主机,用户名,密码,数据库)

创建游标对象

cursor=db.cursor()

创建字符串sql语句

sql

执行sql语句

cursor.execute(sql)

提交到数据库执行

cursor.commit()

获取执行结果

结果为元组，获取不到返回none

全部获取

results=cursor.fetchall()

单条获取

result=cursor.fetchone()

指定条数获取

results=cursor.fetchmany(size)

游标关闭

cyrsor.close()

数据库连接关闭

db.close()

mysql数据库的引擎

数据库存储引擎：5.6版本之后的默认为InnoDB存储，5.5版本之前的默认是MyISAM存储引擎： 1.第一种myisam：三个文件（1）表结构存文件、数据文件、搜索目录三个文件都存放在硬盘上（2）应用于以读和插入为主的操作，很少的更新和删除（2）不支持事务安全、不支持外键、不支持行级锁,表级锁严重影响并发 2.第二种innodb:两个文件（1）表结构文件、数据和搜索目录文件都存放在硬盘上（2）支持事务安全、支持外键、支持行级锁、表级锁 3.第三种memory:两个文件（1）表结构存储在硬盘，数据存储在内存（2）读写速度快，关机会导致内存数据的丢失，多用于热新闻查看当前的默认存储引擎: mysql> show variables like "default_storage_engine"; +------------------------+--------+ | Variable_name | Value | +------------------------+--------+ | default_storage_engine | InnoDB | +------------------------+--------+ 1 row in set (0.00 sec) 查询当前数据库支持的存储引擎： mysql> show engines \G;

InnoDB

5.6之后的版本默认，两个文件存储在硬盘：表结构和带有数据内容的索引；支持事务、行级锁、表级锁、支持外键

MyISAM

5.5及以前版本默认，三个文件存储在硬盘：表结构、索引目录、数据内容；不支持事务、不支持行级锁、表解锁降低并发效率、不支持外键

Memory

两个文件：表结构在硬盘，数据在内存，关机丢失，可用于热新闻等

数据库的安装于使用

数据库的安装： 1.下载社区版本的数据 2.解压放在一个全英文的文件路径下 3.新建修改配置文件my.ini的路径，放到数据库文件夹中 4.配置环境变量 5.以管理员身份运行cmd窗口 6.在命令行输入数据库文件夹bin目录的绝对路径\mysqld install 通过调用mysqld.exe程序安装mysql 7.安装成功之后，启动server端：net start mysql 8.登录客户端：mysql -uroot -p (默认管理员账号密码空) 9.查看配置文件my.ini是否成功：show variables like '%charac%'; 10.安装失败，需要执行在命令行输入数据库文件夹bin目录的绝对路径\mysqld remove;

mysql数据库支持的数据类型

数字

int 整型

float 浮点数

decimal 精确小数

字符

char 定长字符串

长度受限，短字符浪费空间，读写效率高

varchar 变长字符串

长度不限，短字符节省空间，读写效率低

时间和日期

year 年

date 年月日

time 时分秒

datetime 年月日时分秒

timestamp 年月日时分秒（2038年之前）

enum和set

enum（）枚举单选

自动屏蔽不存在项

set()集合多选

多个重复赋值自动去重

数据库的分类

关系型数据库

释义

关系型数据库，是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。

RDBMS即关系数据库管理系统(Relational Database Management System)的特点： 1.数据以表格的形式出现 2.每行为各种记录名称 3.每列为记录名称所对应的数据域 4.许多的行和列组成一张表单 5.若干的表单组成database

表现形式

以表的形式存储

实例

oracle，access，sql server，MySQL

非关系型数据库

表现形式

以键值对：key-value方式存储

实例

mangoDB、redis、memcache

SQL的三种操作

SQL : 结构化查询语言(Structured Query Language)简称SQL 一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统 SQL语言分为3种类型：　　1、DDL语句数据库定义语言：数据库、表、视图、索引、存储过程，例如CREATE DROP ALTER 　　2、DML语句数据库操纵语言：插入数据INSERT、删除数据DELETE、更新数据UPDATE、查询数据SELECT 　　3、DCL语句数据库控制语言：例如控制用户的访问权限GRANT、REVOKE（取消授权）管理员登录：mysql -uroot -p 设置管理员密码：set password=password(密码); 查看数据库所有用户：select * from mysql.user; 查看用户权限：show grants for 用户名@网络地址 [identified by 密码] 普通用户登录：mysql -u用户名 -h网络地址 -p 查看当前用户：select user(); 创建用户：create user 用户名@网络地址 identified by 密码; 用户授权：grant all[SELECT INSERT UPDATE DELETE] on 数据库名.表名 to 用户名@网络地址（%表示所有） identified by 用户密码; 取消授权：revoke all[SELECT INSERT UPDATE DELETE ] on 数据库名.表名 from 用户名@网络地址 [identified by 密码]; 修改普通用户密码：set password for 用户名=password(密码); 查看当前用户权限：show grants; 查看数据库：show databases; 创建数据库：create database 数据库名; 删除数据库：drop database 数据库名;

DDL(数据库定义语言)

DDL语句数据库定义语言：数据库、表、视图、索引、存储过程，例如CREATE DROP ALTER

DML(数据库操纵语言)

DML语句数据库操纵语言：插入数据INSERT、删除数据DELETE、更新数据UPDATE、查询数据SELECT

DCL(数据库控制语言)

DCL语句数据库控制语言例如控制用户的访问权限GRANT、REVOKE（取消授权）

索引

# 索引 # 认识mysql中的key # index key 普通索引,能够加速查询,辅助索引 # unique key 唯一 + 索引,辅助索引 # primary key 唯一 + 非空 + 聚集索引 # 主键作为条件的查询如果能够让索引生效那么效率总是更高 # foreign key 本身没有索引的,但是它关联的外表中的字段是unique索引 # primary key 和unique 标识的字段不需要再添加索引 # 直接就可以利用索引加速查询 # 能用unique的时候尽量不用index # unique除了是索引之外还能做唯一约束,如果做了唯一约束 # b+树就更健康 # 正确的使用索引 # 创建索引 creeat index 索引名 on 表名(字段名) # 删除索引 drop index 索引名 on 表名 # 1.条件一定是建立了索引的字段,如果条件使用的字段根本就没有创建索引,那么索引不生效 # 2.如果条件是一个范围,随着范围的值逐渐增大,那么索引能发挥的作用也越小 # 3.如果使用like进行模糊查询,那么使用a%的形式能命中索引,%a形式不能命中索引 # 4.尽量选择区分度高的字段作为索引列 # 5.索引列不能在条件中参与计算,也不能使用函数 # 6.在多个条件以and相连的时候,会优点选择区分度高的索引列来进行查询 # 在多个条件以or相连的时候,就是从左到右依次判断 # 7.制作联合索引 # 1.最左前缀原则 a,b,c,d 条件是a的能命中索引,条件是a,b能命中索引,a,b,c能命中,a,c.... 只要没有a就不能命中索引 # 如果在联合查询中,总是涉及到同一个字段,那么就在建立联合索引的时候将这个字段放在最左侧 # 2.联合索引如果按照定义顺序,从左到右遇到的第一个在条件中以范围为条件的字段,索引失效 # 尽量将带着范围查询的字段,定义在联合索引的最后面 # drop index # 如果我们查询的条件总是多个列合在一起查,那么就建立联合索引 # create index ind_mix on s1(id,email) # select * from s1 where id = 1000000 命中索引 # select * from s1 where email = 'eva1000000@oldboy' 未命中索引 # 但凡是创建了联合索引,那么在查询的时候,再创建顺序中从左到右的第一列必须出现在条件中 # select count(*) from s1 where id = 1000000 and email = 'eva10%'; 命中索引 # select count(*) from s1 where id = 1000000 and email like 'eva10%'; 可以命中索引 # 范围 : # select * from s1 where id >3000 and email = 'eva300000@oldboy'; 不能命中索引 # 8.条件中涉及的字段的值必须和定义表中字段的数据类型一致,否则不能命中索引 # 关于索引的两个名词 # 覆盖索引查一个数据不需要回表 # select name from 表 where age = 20 不是覆盖索引 # select age from 表 where age =20 是覆盖索引 # select count(age) from 表 where age =20 是覆盖索引 # 合并索引 # 当我们为单独的一列创建索引的时候 # 如果条件是这一列,且使用正确就可以命中索引 # 当我们为两列分别创建单独的索引的时候 # 如果这两列都是条件,那么可能只能命中期中一个条件 # 如果这两列都是条件,那么可能会命中两个索引 - 合并索引 # 我们为多列直接创建联合所以 # 条件命中联合索引 # 执行计划 # 看看mysql准备怎么执行这条语句可以看到是否命中索引,计划能命中哪些,实际命中了哪些,执行的顺序,是否发生了索引合并,覆盖索引 # explain select * from s1; # 数据库部分的拾遗 # pymysql # 数据库备份和还原 # 使用事务和锁的方式来保证数据安全

释义

创建表时自动创建一个聚集索引，一般为主键，没有设置主键即自动创建但后期无法使用默认的聚集索引查找

分类

聚集索引

辅助索引

原理：B+树

子主题

创建与删除

创建（后期创建的只能是辅助索引）

create index 索引名字 on 表名(字段)

删除

drop index 索引名字 on 表名

数据库的备份与恢复

备份

#语法： # mysqldump -h 服务器 -u用户名 -p密码数据库名 > 备份文件.sql #示例： #单库备份 mysqldump -uroot -p123 db1 > db1.sql mysqldump -uroot -p123 db1 table1 table2 > db1-table1-table2.sql #多库备份 mysqldump -uroot -p123 --databases db1 db2 mysql db3 > db1_db2_mysql_db3.sql #备份所有库 mysqldump -uroot -p123 --all-databases > all.sql

恢复

#方法一： [root@egon backup]# mysql -uroot -p123 < /backup/all.sql #方法二： mysql> use db1; mysql> SET SQL_LOG_BIN=0; #关闭二进制日志，只对当前session生效 mysql> source /root/db1.sql

MySQL常用操作

表的操作

表的操作（alter table 表名......）：（1）改名 alter table 表名 rename 新表名；（2）添加表字段 alter table 表名 add 字段类型（长度） [约束] [first/after 字段]; （3）删除表字段 alter table 表名 drop 字段 ; (4)修改表字段 alter table 表名 change 字段新字段类型（长度）; （5）修改表字段的类型（长度）、约束 alter table 表名 modify 字段类型（长度）[约束] ; （6）修改表中字段的顺序 alter table 表名 change 字段新字段类型（长度）[约束] [first/after 字段]; alter table 表名 modify 字段类型（长度）[约束] [first/after 字段] ; (7)添加/删除外键 alter table 表名 add constraint 名称描述 foreign key (字段) references 关系表名 (关系表内字段) on update cascade on delete cascade; alter table 表名 drop foreign key 名称描述;

创建

create table 表名(字段1 类型（长度） [完整性约束],字段2 类型（长度） [完整性约束]...)

改名

alter table 表名 rename 新表名

删除

drop table 表名

添加字段

alter table 表名 add 新字段类型（长度） [完整性约束] [fisrt/after 字段]

删除字段

alter table 表名 drop 字段

修改字段

alter table 表名 modify 字段类型（长度）[完整性约束] [fisrt/after 字段]

只能修改字段的类型、长度和部分约束条件

alter table 表名 cahnge 原字段新字段类型（长度）[完整性约束] [fisrt/after 字段]

可以同时修改字段名和类型及长度、约束性条件

排序字段

modify和change均可操作顺序只有first和after

查看表结构

show create table 表名

详细查看表的创建信息

desc 表名

简单查看表结构

外键的创建与删除

alter table 表名 add constraint 名称描述 foreign key (字段) references 关系表名 (关系表内字段) on update cascade on delete cascade;

alter table 表名 drop foreign key 名称描述;

内容操作

增:insert into

增insert into：（1）单条插入 insert into 表名 value(值1,值2,...); （2）多条插入 insert into 表名 values(值1,值2,...),(值1,值2,...),...; （3）指定字段插入 insert into 表名(字段1,字段2,字段3,...) values(值1,值2,值3,...); （4）查询结果插入 insetr into 表名(字段1,字段2,字段3,...) select (字段1,字段2,字段3,...) from 表名1;

insert into 表名（字段1,字段2...） vlaues(值1,值2...)

删:delete from

删delete from：（1）清空列表 delete from 表名; （2）删除某条记录 delete from 表名 where 条件; 清空表： delete from t1; #如果有自增id，新增的数据，仍然是以删除前的最后一样作为起始。 truncate table t1;数据量大，删除速度比上一条快，且直接从零开始，

delete from 表名 where 条件

改:update set

update 表名 set 字段1=值1,字段2=值2... where 条件

查:delect from

查select from：（1）单表查询语法： select distinct 字段1，字段2... from 表名 where 条件 group by 字段 having 条件 order by 字段[asc/desc] limit n/m,n （2）执行优先级：from>where>group by>select>having>order by>limit 1.找到表:from 2.拿着where指定的约束条件，去文件/表中取出一条条记录 3.将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组 4.执行select（去重） 5.将分组的结果进行having过滤 6.将结果按条件排序：order by 7.限制结果的显示条数（3）简单查询： 1.重命名查询as: select * from 表名 select 字段 as 自定义字段名 from 表名 2.去重查询distinct： select distinct 字段 from 表名 3.四则运算查询+-*/%等： select 字段*n from 表名; 4.定义格式查询concat： select concat('字符串',字段1,'字符串',字段2...) from 表名; select concat_ws('连接字符串',字段1，字段2 ,...) from 表名; (4)where约束条件： 1.比较运算：>,<,=,>=,<=,!=,<> 2.between m and n m到n之间的值（包括m、n） 3.in（a,b,c...）在a,b,c...中的值 4.逻辑运算：and、or 、not 5.模糊条件 like '%'/'_' 通配符：%表示任意个字符； _下划线表示一个字符（5）group by 分组：

查询语句

select */字段 [as] 新字段名 from 表名 where 条件 group by 字段 having 条件 order by 字段[asc/desc] limit 起始位置-1,条数

关键字

条件:where

比较运算

>,<,=,>=,<=,!=,<>

闭合区间

between（a,b）[包含a,b]

条件范围

in(a,b,c)

逻辑运算

and、or、not

模糊条件

like

通配符%

单配符_

分组:group by

分组之后要显示全部内容在select中使用group_concat(字段)

筛选:having

多与group by 联合使用，配合聚合函数条件

排序:order by

默认顺序asc

倒序desc

长度:limit

limit n默认从1开始显示n条

limit m,n从m+1开始显示n条

聚合函数

max()

min()

sum()

avg()

count()

其它

重命名as

字段/表 as 新名字(或者不写as用空格)

格式化concat

select concat(字符串1,字段,字符串2,字段2...)as 新名字 from...

select concat_ws(字符串,字段1,字段2...) as 新名字 from ...

分组显示group_concat

select group_concat(字段) from 表 group by 字段

去重distinct

select distinct 字段 from...

四则远算+-*/

select 字段*n from ...

查询优先级

from>where>select>group by>having>order by>limit

单表查询

联表查询

内连接

select * from 表1 inner join 表2 on 表1.字段=表2.字段 where...

外连接

左连接

select * from 表1 left join 表2 on 表1.字段=表2.字段 where...

右连接

select * from 表1 right join 表2 on 表1.字段=表2.字段 where...

全连接

select * from 表1 left join 表2 on 表1.字段=表2.字段 union select * from 表1 right join 表2 on 表1.字段=表2.字段 where...

mysql数据的完整性约束

''' MySQL支持的数据类型：https://www.cnblogs.com/Eva-J/articles/9683316.html#_label1 1.数值类型（有约束条件无符号unsigned）： int：整型4字节 -2^31-2147483647 无符号：2^32-1 float：单精度浮点数4字节 double：双精度浮点数8字节 decimal: 小数值 2.字符类型： char: 定长字符，能表示的字符个数有限（0-255），读写快 varchar: 变长字符，能表示的字符个数多（0-65535），读写慢 3. 时间和日期类型：（系统内置函数now()获取当前时间） year：年 date：年月日 time：时分秒 datetime：年月日时分秒 timestamp：年月日时分秒（1970-2038年之间，如果不设置这默认显示当前时间） 4.enum 和set 类型： enum(): 枚举，单选，自动屏蔽不存在的项 set(): 集合，多选，自动屏蔽不存在且去重 MySQL中的约束条件（可配合使用）： 1.整型无符号： unsiged 2.唯一： unique 只能约束数据类型内不能重复，但不能约束null 3.非空： not null 4.默认值 default 值 5.自增： auto_increment 必须为数值类型，且设置唯一unique 6.主键： primary key 7.外键： foreign key 在没有设置主键的情况下，遇到约束条件为非空唯一时系统默认为主键！ '''

usigned（无符号）

实例操作

# 约束条件的使用： # 1.unsigned、not null约束 ''' (1)创建一个无约束表t1 create table t1( id int, name char(4), sex enum('male','female') ); mysql> insert into t1 values(2147483647,'yang',null); #有符号正常在规定范围内正常写入 mysql> select * from t1; +------------+------+------+ | id | name | sex | +------------+------+------+ | 2147483647 | yang | NULL | +------------+------+------+ 1 row in set (0.00 sec) mysql> insert into t1 values(2147483648,'peng','nihao'); #超出范围之后会自动按最大值写入，enum超出单选范围自动屏蔽 Query OK, 1 row affected, 2 warnings (0.01 sec) mysql> select * from t1; +------------+------+------+ | id | name | sex | +------------+------+------+ | 2147483647 | yang | NULL | | 2147483647 | peng | | +------------+------+------+ 2 rows in set (0.00 sec) （2）约束条件的表格t2 create table t2( id int unsigned, name char(4), sex enum('male','female') not null ); mysql> insert into t2 values(2147483648,'peng','nihao'); #约束无符号之后，超出有符号的范围正常写入；enum超出单选范围自动屏蔽 Query OK, 1 row affected, 1 warning (0.00 sec) mysql> select * from t2; +------------+------+-----+ | id | name | sex | +------------+------+-----+ | 2147483648 | peng | | +------------+------+-----+ 1 row in set (0.00 sec) mysql>insert into t2 (id,name) values((21474836488,'yang'); #sex二选一没给值报错 mysql> insert into t2 values(2147483648,'peng','male'); #给正确的值直接写入 Query OK, 1 row affected (0.01 sec) mysql> select * from t2; +------------+------+------+ | id | name | sex | +------------+------+------+ | 2147483648 | peng | | | 2147483648 | peng | male | +------------+------+------+ 2 rows in set (0.00 sec) ''' # 2.default 、unique约束 ''' # create table t3( # id int unique, # name char(4), # sex enum('male','female')default 'male' # ); mysql> desc t3; +-------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+-------+ | id | int(11) | YES | UNI | NULL | | | name | char(4) | YES | | NULL | | | sex | enum('male','female') | YES | | male | | +-------+-----------------------+------+-----+---------+-------+ 3 rows in set (0.02 sec) mysql> insert into t3(id,name) values(1,'yang'); #不加sex的值会自动使用default默认的 Query OK, 1 row affected (0.00 sec) mysql> select * from t3; +------+------+------+ | id | name | sex | +------+------+------+ | 1 | yang | male | +------+------+------+ 1 row in set (0.00 sec) mysql> insert into t3 values(1,'peng','female'); #id设置唯一，给了一个重复值直接报错 ERROR 1062 (23000): Duplicate entry '1' for key 'id' ''' #3.联合唯一unique（字段1，字段2） ''' # ip + port # 192.168.16.13 mysql 3306 # 192.168.16.13 kugou 8080 # 192.168.16.13 flask 5000 # 192.168.16.15 mysql 3306 # 192.168.16.16 mysql 3306 # create table service( # id int, # ip char(15), # name char(15), # port int(5), # unique(ip,port) #ip+port联合唯一 # ); mysql> desc service; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | ip | char(15) | YES | MUL | NULL | | | name | char(15) | YES | | NULL | | | port | int(5) | YES | | NULL | | +-------+----------+------+-----+---------+-------+ 4 rows in set (0.01 sec) mysql> insert into service values(1,'192.168.16.13', 'mysql', 3306),(2,'192.168.16.13', 'kugou', 8080),(3,'192.168.16.13', 'flask', 5000) -> ,(4,'192.168.16.15' ,'mysql',3306),(5,'192.168.16.16','mysql', 3306); Query OK, 5 rows affected (0.01 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> select * from service; +------+---------------+-------+------+ | id | ip | name | port | +------+---------------+-------+------+ | 1 | 192.168.16.13 | mysql | 3306 | | 2 | 192.168.16.13 | kugou | 8080 | | 3 | 192.168.16.13 | flask | 5000 | | 4 | 192.168.16.15 | mysql | 3306 | | 5 | 192.168.16.16 | mysql | 3306 | +------+---------------+-------+------+ 5 rows in set (0.00 sec) mysql> insert into service values(6,'192.168.16.13', 'hha', 3306); #重复的ip+port直接报错 ERROR 1062 (23000): Duplicate entry '192.168.16.13-3306' for key 'ip' ''' #4.auto_increment自增（int型且uniqu） ''' # create table t6( # id int unique auto_increment, # name char(4) , # sex enum('male','female') # ); mysql> desc t6; +-------+-----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | char(4) | YES | | NULL | | | sex | enum('male','female') | YES | | NULL | | +-------+-----------------------+------+-----+---------+----------------+ 3 rows in set (0.01 sec) mysql> insert into t6 (name,sex) values('yang','male'); #id 自动从1开始 Query OK, 1 row affected (0.01 sec) mysql> select * from t6; +----+------+------+ | id | name | sex | +----+------+------+ | 1 | yang | male | +----+------+------+ 1 row in set (0.00 sec) mysql> insert into t6 (name,sex) values('peng','haha'); #id自动变成2 Query OK, 1 row affected, 1 warning (0.01 sec) mysql> select * from t6; +----+------+------+ | id | name | sex | +----+------+------+ | 1 | yang | male | | 2 | peng | | +----+------+------+ 2 rows in set (0.00 sec) ''' #5.primary key 约束==unique+not null(没有设置主键默认为主键) ''' （1） # create table t4( # id int unique , # name char(4) unique not null, #没有设置主键时，默认为主键PRI # sex enum('male','female') # ); mysql> desc t4; +-------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+-------+ | id | int(11) | YES | UNI | NULL | | | name | char(4) | NO | PRI | NULL | | | sex | enum('male','female') | YES | | NULL | | +-------+-----------------------+------+-----+---------+-------+ 3 rows in set (0.01 sec) （2） create table t5( id int primary key , #设置了id为主键 name char(4) unique not null, sex enum('male','female') ); mysql> desc t5; +-------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | char(4) | NO | UNI | NULL | | | sex | enum('male','female') | YES | | NULL | | +-------+-----------------------+------+-----+---------+-------+ 3 rows in set (0.01 sec) ''' #6.联合主键 : 约束多个字段各自不能为空,并且联合唯一 ''' # create table t7( # id1 int, # num int, # primary key(id1,num) # ); mysql> desc t7; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | id1 | int(11) | NO | PRI | 0 | | | num | int(11) | NO | PRI | 0 | | +-------+---------+------+-----+---------+-------+ 2 rows in set (0.01 sec) ''' # 7.foreign key 外键约束（导致关联的相关数据的更新、删除操作不成） ''' # 表2 班级表 cid class_name # create table clas( # cid int primary key, # class_name char(20) # ) # 表1 学生表 id name class_id # create table stu( # id int primary key , # name char(18), # class_id int, # foreign Key(class_id) references clas(cid) #设置外键连接到clas表的主键cid # ) mysql> insert into clas values(1,'一'); #在clas表中创建一个班级 Query OK, 1 row affected (0.01 sec) mysql> select * from clas; +-----+------------+ | cid | class_name | +-----+------------+ | 1 | 一 | +-----+------------+ 1 row in set (0.00 sec) mysql> insert into stu values(1,'yang',1); #stu表中添加外键班级信息（在clas中必须存在，否则报错） Query OK, 1 row affected (0.00 sec) mysql> select * from stu; +----+------+----------+ | id | name | class_id | +----+------+----------+ | 1 | yang | 1 | +----+------+----------+ 1 row in set (0.00 sec) mysql> insert into stu values(1,'yang',2); #stu表中添加外键不存在的班级信息直接报错 ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY' mysql> insert into clas values(2,'二'); #在clas表中创建一个班级2 Query OK, 1 row affected (0.00 sec) mysql> select * from clas; +-----+------------+ | cid | class_name | +-----+------------+ | 1 | 一 | | 2 | 二 | +-----+------------+ 2 rows in set (0.00 sec) #在设置了外键之后会对关联表的数据更新、删除产生约束 mysql> update clas set cid=3 where cid=1; ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`day038`.`stu`, CONSTRAINT `stu_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `clas` (`cid`)) mysql> delete from clas where cid=1; ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`day038`.`stu`, CONSTRAINT `stu_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `clas` (`cid`)) #未关联的可以自行修改 mysql> update clas set cid=3 where cid=2; Query OK, 1 row affected (0.00 sec) mysql> delete from clas where cid=3; Query OK, 1 row affected (0.00 sec) ''' #8.外键的级联更新、删除操作（修改一个自动同步更新，删除字后默认为空） ''' # 表2 班级表 cid class_name # create table clas( # cid int primary key, # class_name char(20) # ) (1)外键关联：表1 学生表 id name class_id # create table stu( # id int primary key , # name char(18), # class_id int, # foreign Key(class_id) references clas(cid) #设置外键连接到clas表的主键cid # ) # （2）级联更新级联删除(# 表1 学生表 id name class_id) # create table stu( # id int primary key , # name char(18), # class_id int, # foreign key(class_id) references clas(cid) on update cascade on delete cascade # ); '''

unique（唯一）

不能约束null

联合唯一unique(字段1,字段2...)

default（默认值）

not null（非空）

auto_increment（自增）

前提必须是唯一

primary key（主键）

联合主键

foreign key （外键）

外键设置：foreign key(字段) references 表名(字段)

级联更新修改、删除

foreign key(字段) references 表名(字段) on upadte cascade [on delete cascade]

实例操作

# 约束条件的使用： # 1.unsigned、not null约束 ''' (1)创建一个无约束表t1 create table t1( id int, name char(4), sex enum('male','female') ); mysql> insert into t1 values(2147483647,'yang',null); #有符号正常在规定范围内正常写入 mysql> select * from t1; +------------+------+------+ | id | name | sex | +------------+------+------+ | 2147483647 | yang | NULL | +------------+------+------+ 1 row in set (0.00 sec) mysql> insert into t1 values(2147483648,'peng','nihao'); #超出范围之后会自动按最大值写入，enum超出单选范围自动屏蔽 Query OK, 1 row affected, 2 warnings (0.01 sec) mysql> select * from t1; +------------+------+------+ | id | name | sex | +------------+------+------+ | 2147483647 | yang | NULL | | 2147483647 | peng | | +------------+------+------+ 2 rows in set (0.00 sec) （2）约束条件的表格t2 create table t2( id int unsigned, name char(4), sex enum('male','female') not null ); mysql> insert into t2 values(2147483648,'peng','nihao'); #约束无符号之后，超出有符号的范围正常写入；enum超出单选范围自动屏蔽 Query OK, 1 row affected, 1 warning (0.00 sec) mysql> select * from t2; +------------+------+-----+ | id | name | sex | +------------+------+-----+ | 2147483648 | peng | | +------------+------+-----+ 1 row in set (0.00 sec) mysql>insert into t2 (id,name) values((21474836488,'yang'); #sex二选一没给值报错 mysql> insert into t2 values(2147483648,'peng','male'); #给正确的值直接写入 Query OK, 1 row affected (0.01 sec) mysql> select * from t2; +------------+------+------+ | id | name | sex | +------------+------+------+ | 2147483648 | peng | | | 2147483648 | peng | male | +------------+------+------+ 2 rows in set (0.00 sec) ''' # 2.default 、unique约束 ''' # create table t3( # id int unique, # name char(4), # sex enum('male','female')default 'male' # ); mysql> desc t3; +-------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+-------+ | id | int(11) | YES | UNI | NULL | | | name | char(4) | YES | | NULL | | | sex | enum('male','female') | YES | | male | | +-------+-----------------------+------+-----+---------+-------+ 3 rows in set (0.02 sec) mysql> insert into t3(id,name) values(1,'yang'); #不加sex的值会自动使用default默认的 Query OK, 1 row affected (0.00 sec) mysql> select * from t3; +------+------+------+ | id | name | sex | +------+------+------+ | 1 | yang | male | +------+------+------+ 1 row in set (0.00 sec) mysql> insert into t3 values(1,'peng','female'); #id设置唯一，给了一个重复值直接报错 ERROR 1062 (23000): Duplicate entry '1' for key 'id' ''' #3.联合唯一unique（字段1，字段2） ''' # ip + port # 192.168.16.13 mysql 3306 # 192.168.16.13 kugou 8080 # 192.168.16.13 flask 5000 # 192.168.16.15 mysql 3306 # 192.168.16.16 mysql 3306 # create table service( # id int, # ip char(15), # name char(15), # port int(5), # unique(ip,port) #ip+port联合唯一 # ); mysql> desc service; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | ip | char(15) | YES | MUL | NULL | | | name | char(15) | YES | | NULL | | | port | int(5) | YES | | NULL | | +-------+----------+------+-----+---------+-------+ 4 rows in set (0.01 sec) mysql> insert into service values(1,'192.168.16.13', 'mysql', 3306),(2,'192.168.16.13', 'kugou', 8080),(3,'192.168.16.13', 'flask', 5000) -> ,(4,'192.168.16.15' ,'mysql',3306),(5,'192.168.16.16','mysql', 3306); Query OK, 5 rows affected (0.01 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> select * from service; +------+---------------+-------+------+ | id | ip | name | port | +------+---------------+-------+------+ | 1 | 192.168.16.13 | mysql | 3306 | | 2 | 192.168.16.13 | kugou | 8080 | | 3 | 192.168.16.13 | flask | 5000 | | 4 | 192.168.16.15 | mysql | 3306 | | 5 | 192.168.16.16 | mysql | 3306 | +------+---------------+-------+------+ 5 rows in set (0.00 sec) mysql> insert into service values(6,'192.168.16.13', 'hha', 3306); #重复的ip+port直接报错 ERROR 1062 (23000): Duplicate entry '192.168.16.13-3306' for key 'ip' ''' #4.auto_increment自增（int型且uniqu） ''' # create table t6( # id int unique auto_increment, # name char(4) , # sex enum('male','female') # ); mysql> desc t6; +-------+-----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | char(4) | YES | | NULL | | | sex | enum('male','female') | YES | | NULL | | +-------+-----------------------+------+-----+---------+----------------+ 3 rows in set (0.01 sec) mysql> insert into t6 (name,sex) values('yang','male'); #id 自动从1开始 Query OK, 1 row affected (0.01 sec) mysql> select * from t6; +----+------+------+ | id | name | sex | +----+------+------+ | 1 | yang | male | +----+------+------+ 1 row in set (0.00 sec) mysql> insert into t6 (name,sex) values('peng','haha'); #id自动变成2 Query OK, 1 row affected, 1 warning (0.01 sec) mysql> select * from t6; +----+------+------+ | id | name | sex | +----+------+------+ | 1 | yang | male | | 2 | peng | | +----+------+------+ 2 rows in set (0.00 sec) ''' #5.primary key 约束==unique+not null(没有设置主键默认为主键) ''' （1） # create table t4( # id int unique , # name char(4) unique not null, #没有设置主键时，默认为主键PRI # sex enum('male','female') # ); mysql> desc t4; +-------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+-------+ | id | int(11) | YES | UNI | NULL | | | name | char(4) | NO | PRI | NULL | | | sex | enum('male','female') | YES | | NULL | | +-------+-----------------------+------+-----+---------+-------+ 3 rows in set (0.01 sec) （2） create table t5( id int primary key , #设置了id为主键 name char(4) unique not null, sex enum('male','female') ); mysql> desc t5; +-------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | char(4) | NO | UNI | NULL | | | sex | enum('male','female') | YES | | NULL | | +-------+-----------------------+------+-----+---------+-------+ 3 rows in set (0.01 sec) ''' #6.联合主键 : 约束多个字段各自不能为空,并且联合唯一 ''' # create table t7( # id1 int, # num int, # primary key(id1,num) # ); mysql> desc t7; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | id1 | int(11) | NO | PRI | 0 | | | num | int(11) | NO | PRI | 0 | | +-------+---------+------+-----+---------+-------+ 2 rows in set (0.01 sec) ''' # 7.foreign key 外键约束（导致关联的相关数据的更新、删除操作不成） ''' # 表2 班级表 cid class_name # create table clas( # cid int primary key, # class_name char(20) # ) # 表1 学生表 id name class_id # create table stu( # id int primary key , # name char(18), # class_id int, # foreign Key(class_id) references clas(cid) #设置外键连接到clas表的主键cid # ) mysql> insert into clas values(1,'一'); #在clas表中创建一个班级 Query OK, 1 row affected (0.01 sec) mysql> select * from clas; +-----+------------+ | cid | class_name | +-----+------------+ | 1 | 一 | +-----+------------+ 1 row in set (0.00 sec) mysql> insert into stu values(1,'yang',1); #stu表中添加外键班级信息（在clas中必须存在，否则报错） Query OK, 1 row affected (0.00 sec) mysql> select * from stu; +----+------+----------+ | id | name | class_id | +----+------+----------+ | 1 | yang | 1 | +----+------+----------+ 1 row in set (0.00 sec) mysql> insert into stu values(1,'yang',2); #stu表中添加外键不存在的班级信息直接报错 ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY' mysql> insert into clas values(2,'二'); #在clas表中创建一个班级2 Query OK, 1 row affected (0.00 sec) mysql> select * from clas; +-----+------------+ | cid | class_name | +-----+------------+ | 1 | 一 | | 2 | 二 | +-----+------------+ 2 rows in set (0.00 sec) #在设置了外键之后会对关联表的数据更新、删除产生约束 mysql> update clas set cid=3 where cid=1; ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`day038`.`stu`, CONSTRAINT `stu_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `clas` (`cid`)) mysql> delete from clas where cid=1; ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`day038`.`stu`, CONSTRAINT `stu_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `clas` (`cid`)) #未关联的可以自行修改 mysql> update clas set cid=3 where cid=2; Query OK, 1 row affected (0.00 sec) mysql> delete from clas where cid=3; Query OK, 1 row affected (0.00 sec) ''' #8.外键的级联更新、删除操作（修改一个自动同步更新，删除字后默认为空） ''' # 表2 班级表 cid class_name # create table clas( # cid int primary key, # class_name char(20) # ) (1)外键关联：表1 学生表 id name class_id # create table stu( # id int primary key , # name char(18), # class_id int, # foreign Key(class_id) references clas(cid) #设置外键连接到clas表的主键cid # ) # （2）级联更新级联删除(# 表1 学生表 id name class_id) # create table stu( # id int primary key , # name char(18), # class_id int, # foreign key(class_id) references clas(cid) on update cascade on delete cascade # ); '''