注意:
在查询的时候,CHAR列删除了尾部的空格,而VARCHAR则保留这些空格。 mysql> create table t1(x char(5),y varchar(5)); mysql> insert into t1 values('你瞅啥 ','瞅你妹 '); mysql> select x,length(x),y,length(y) from t1; +--------+-----------+----------+-----------+ | x | length(x) | y | length(y) | +--------+-----------+----------+-----------+ | 你瞅啥 | 9 | 瞅你妹 | 11 | +--------+-----------+----------+-----------+
7、日期类型
表示时间值的日期和时间类型为DATETIME,DATE,TIMESTAMP,TIME和YEAR。
每个时间类型有一个有效值范围和一个“零”值,当指定不合法的mysql不能表示的值时使用“零”值。
作用:存储用户注册时间,文章发布时间,员工入职时间,出生时间,过期时间等
8、year
============year=================== create table t_year(born_year year); insert into t_year values (1901), (2155); select * from t_year; +-----------+ | born_year | +-----------+ | 1901 | | 2155 | +-----------+ rows in set (0.00 sec)
9、date time datetime
1 mysql> select now(); #查看当前mysql数据库时间 2 +---------------------+ 3 | now() | 4 +---------------------+ 5 | 2017-09-11 16:37:48 | 6 +---------------------+ 7 1 row in set (0.00 sec)
============date,time,datetime=========== create table t_mul(d date,t time,dt datetime); insert into t_mul values(now(),now(),now()); select * from t_mul; mysql> select * from t_mul; +------------+----------+---------------------+ | d | t | dt | +------------+----------+---------------------+ | 2017-08-01 | 19:42:22 | 2017-08-01 19:42:22 | +------------+----------+---------------------+ row in set (0.00 sec)
10、timestamp
create table t_stamp(t TIMESTAMP); insert into t_stamp values(); insert into t_stamp values(NULL ); select * from t_stamp; +---------------------+ | t | +---------------------+ | 2017-08-01 19:46:24 | | 2017-08-01 19:46:24 | +---------------------+ rows in set (0.00 sec)
1 /* 2 在实际应用的很多场景中,MySQL的这两种日期类型都能够满足我们的需要,存储精度都为秒, 3 但在某些情况下,会展现出他们各自的优劣。下面就来总结一下两种日期类型的区别。 4 5 1.DATETIME的日期范围是1001——9999年,TIMESTAMP的时间范围是1970——2038年。 6 7 2.DATETIME存储时间与时区无关,TIMESTAMP存储时间与时区有关,显示的值也依赖于时区。 8 在mysql服务器,操作系统以及客户端连接都有时区的设置。 9 10 3.DATETIME使用8字节的存储空间,TIMESTAMP的存储空间为4字节。因此,TIMESTAMP比DATETIME的 11 空间利用率更高。 12 13 4.DATETIME的默认值为null;TIMESTAMP的字段默认不为空(not null),默认值为当前时间 14 (CURRENT_TIMESTAMP),如果不做特殊处理,并且update语句中没有指定该列的更新值,则默 15 认更新为当前时间。 16 17 */ 18 19 datetime与timestamp
11、枚举类型与集合类型
字段的值只能在给定范围中选择,如单选框,多选框
enum单选 只能在给定的范围内选一个值,如性别 sex 男male/女female
set 多选 在给定的范围内可以选择一个或一个以上的值(爱好1,爱好2,爱好3.....)
解析:
1 /* 2 枚举类型(enum) 3 An ENUM column can have a maximum of 65,535 distinct elements. 4 (The practical limit is less than 3000.) 5 示例: 6 CREATE TABLE shirts ( 7 name VARCHAR(40), 8 size ENUM('x-small', 'small', 'medium', 'large', 'x-large') 9 ); 10 INSERT INTO shirts (name, size) VALUES ('dress shirt','large'), 11 ('t-shirt','medium'), 12 ('polo shirt','small'); 13 14 集合类型(set) 15 A SET column can have a maximum of 64 distinct members. 16 示例: 17 CREATE TABLE myset (col SET('a', 'b', 'c', 'd')); 18 INSERT INTO myset (col) VALUES ('a,d'), 19 ('d,a'), 20 ('a,d,a'), 21 ('a,d,d'), 22 ('d,a,d'); 23 24 25 */
五、表操作
1、创建表
-- 语法
CREATE TABLE tab_name(
field1 type[完整性约束条件],
field2 type,
...
fieldn type
)[character set xxx];
示例:
1 CREATE TABLE employee( 2 id int primary key auto_increment , 3 name varchar(20), 4 gender bit default 1, 5 birthday date, 6 department varchar(20), 7 salary double(8,2) unsigned, 8 resume text 9 );
2、查看表信息
desc tab_name 查看表结构 show columns from tab_name 查看表结构 show tables 查看当前数据库中的所有的表 show create table tab_name 查看当前数据库表建表语句
3、修改表结构
-- (1)增加列(字段) alter table tab_name add [column] 列名 类型[完整性约束条件][first|after 字段名]; #添加多个字段 alter table users2 add addr varchar(20), add age int first, add birth varchar(20) after name; -- (2)修改一列类型 alter table tab_name modify 列名 类型 [完整性约束条件][first|after 字段名]; -- (3)修改列名 alter table tab_name change [column] 列名 新列名 类型 [完整性约束条件][first|after 字段名]; -- (4)删除一列 alter table tab_name drop [column] 列名;
-- (5)修改表名 rename table 表名 to 新表名; -- (6)修该表所用的字符集 alter table student character set utf8;
alter table table_name add field type 完整性约束
alter table table_name modify field type 完整性约束
alter table table_name change field type 完整性约束
alter table table_name drop field type 完整性约束
4、删除表
drop table tab_name;
六、表记录操作
1、增加表记录
/* <1>插入一条记录: insert [into] tab_name (field1,filed2,.......) values (value1,value2,.......); <2>插入多条记录: insert [into] tab_name (field1,filed2,.......) values (value1,value2,.......), (value1,value2,.......), ... ; <3>set插入: insert [into] tab_name set 字段名=值 */
示例:
1 INSERT employee (name,gender,birthday,salary,department) VALUES 2 ("alex",1,"1985-12-12",8000,"保洁部"), 3 ("egon",1,"1987-08-08",5000,"保安部"), 4 ("yuan",1,"1990-06-06",20000,"教学部"); 5 6 7 INSERT employee VALUES (8,"女神",0,"1992-02-12","教学部",7000,""); 8 9 INSERT employee SET name="wusir",birthday="1990-11-11";
2、修改表记录
update tab_name set field1=value1,field2=value2,......[where 语句]
示例:
update employee_new set birthday="1989-10-24" WHERE id=1; --- 将yuan的薪水在原有基础上增加1000元。 update employee_new set salary=salary+4000 where name='yuan';
3、删除表记录
方式1: delete from tab_name [where ....] 方式2: truncate table emp_new; /* 如果不跟where语句则删除整张表中的数据 delete只能用来删除一行记录 delete语句只能删除表中的内容,不能删除表本身,想要删除表,用drop TRUNCATE TABLE也可以删除表中的所有数据,词语句首先摧毁表,再新建表。此种方式删除的数据不能在 事务中恢复。 */
示例:
-- 删除表中名称为’alex’的记录。 delete from employee_new where name='alex'; -- 删除表中所有记录。 delete from employee_new; -- 注意auto_increment没有被重置:alter table employee auto_increment=1;
七、查询表记录
1、语法
-- 查询语法: SELECT *|field1,filed2 ... FROM tab_name WHERE 条件 GROUP BY field HAVING 筛选 ORDER BY field LIMIT 限制条数 -- Mysql在执行sql语句时的执行顺序: -- from where select group by having order by
2、准备表和数据
1 CREATE TABLE emp( 2 id INT PRIMARY KEY AUTO_INCREMENT, 3 name VARCHAR(20), 4 gender ENUM("male","female","other"), 5 age TINYINT, 6 dep VARCHAR(20), 7 city VARCHAR(20), 8 salary DOUBLE(7,2) 9 ); 10 11 12 INSERT INTO emp (name,gender,age,dep,city,salary) VALUES 13 ("yuan","male",24,"教学部","河北省",8000), 14 ("egon","male",34,"保安部","山东省",8000), 15 ("alex","male",28,"保洁部","山东省",10000), 16 ("景丽阳","female",22,"教学部","北京",9000), 17 ("张三", "male",24,"教学部","河北省",6000), 18 ("李四", "male",32,"保安部","北京",12000), 19 ("王五", "male",38,"教学部","河北省",7000), 20 ("赵六", "male",19,"保安部","河北省",9000), 21 ("猪七", "female",24,"保洁部","北京",9000); 22 23 SELECT * FROM emp;
mysql> SELECT * FROM emp; +----+-----------+--------+------+-----------+-----------+----------+ | id | name | gender | age | dep | city | salary | +----+-----------+--------+------+-----------+-----------+----------+ | 1 | yuan | male | 24 | 教学部 | 河北省 | 8000.00 | | 2 | egon | male | 34 | 保安部 | 山东省 | 8000.00 | | 3 | alex | male | 28 | 保洁部 | 山东省 | 10000.00 | | 4 | 景丽阳 | female | 22 | 教学部 | 北京 | 9000.00 | | 5 | 张三 | male | 24 | 教学部 | 河北省 | 6000.00 | | 6 | 李四 | male | 32 | 保安部 | 北京 | 12000.00 | | 7 | 王五 | male | 38 | 教学部 | 河北省 | 7000.00 | | 8 | 赵六 | male | 19 | 保安部 | 河北省 | 9000.00 | | 9 | 猪七 | female | 24 | 保洁部 | 北京 | 9000.00 | +----+-----------+--------+------+-----------+-----------+----------+ rows in set (0.00 sec)
3、where 子句:过滤查询
-- where字句中可以使用: -- 比较运算符: > <>= <= <> != between 80 and 100 值在10到20之间 in(80,90,100) 值是10或20或30 like 'yuan%' /* pattern可以是%或者_, 如果是%则表示任意多字符,此例如唐僧,唐国强 如果是_则表示一个字符唐_,只有唐僧符合。两个_则表示两个字符:__ */ -- 逻辑运算符 在多个条件直接可以使用逻辑运算符 and or not
示例:
-- 查询年纪大于24的员工 SELECT * FROM emp WHERE age>24; -- 查询教学部的男老师信息 SELECT * FROM emp WHERE dep="教学部" AND gender="male";
4、order :排序
按指定的列进行,排序的列即可是表中的列名,也可以是select 语句后指定的别名
-- 语法: select *|field1,field2... from tab_name order by field [Asc|Desc] -- Asc 升序、Desc 降序,其中asc为默认值 ORDER BY 子句应位于SELECT语句的结尾。
示例:
-- 按年龄从高到低进行排序 SELECT * FROM emp ORDER BY age DESC ; -- 按工资从低到高进行排序 SELECT * FROM emp ORDER BY salary;
5、group by:分组查询(******)
GROUP BY 语句根据某个列对结果集进行分组。在分组的列我们可以使用COUNT,SUM,AVG等函数进行相关查询。
-- 语法:
SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;
示例:
-- 查询男女员工各有多少人 SELECT gender 性别,count(*) 人数 FROM emp5 GROUP BY gender; -- 查询各个部门的人数 SELECT dep 部门,count(*) 人数 FROM emp5 GROUP BY dep; -- 查询每个部门最大的年龄 SELECT dep 部门,max(age) 最大年纪 FROM emp5 GROUP BY dep; -- 查询每个部门年龄最大的员工姓名 SELECT * FROM emp5 WHERE age in (SELECT max(age) FROM emp5 GROUP BY dep); -- 查询每个部门的平均工资 SELECT dep 部门,avg(salary) 最大年纪 FROM emp GROUP BY dep; -- 查询教学部的员工最高工资: SELECT dep,max(salary) FROM emp11 GROUP BY dep HAVING dep="教学部"; -- 查询平均薪水超过8000的部门 SELECT dep,AVG(salary) FROM emp GROUP BY dep HAVING avg(salary)>8000; -- 查询每个组的员工姓名 SELECT dep,group_concat(name) FROM emp GROUP BY dep; -- 查询公司一共有多少员工(可以将所有记录看成一个组) SELECT COUNT(*) 员工总人数 FROM emp; -- KEY: 查询条件中的每个后的词就是分组的字段
6、limit记录条数限制
SELECT * from ExamResult limit 1; SELECT * from ExamResult limit 2,5; -- 跳过前两条显示接下来的五条纪录 SELECT * from ExamResult limit 2,2;
7、正则表达式
SELECT * FROM employee WHERE emp_name REGEXP '^yu'; SELECT * FROM employee WHERE emp_name REGEXP 'yun$'; SELECT * FROM employee WHERE emp_name REGEXP 'm{2}';
八、多表查询
1、创建表
1 CREATE TABLE emp( 2 id INT PRIMARY KEY AUTO_INCREMENT, 3 name VARCHAR(20), 4 salary DOUBLE(7,2), 5 dep_id INT 6 ); 7 8 INSERT INTO emp (name,salary,dep_id) VALUES ("张三",8000,2), 9 ("李四",12000,1), 10 ("王五",5000,2), 11 ("赵六",8000,3), 12 ("猪七",9000,1), 13 ("周八",7000,4), 14 ("蔡九",7000,2); 15 16 CREATE TABLE dep( 17 id INT PRIMARY KEY AUTO_INCREMENT, 18 name VARCHAR(20) 19 ); 20 21 22 INSERT INTO dep (name) VALUES ("教学部"), 23 ("销售部"), 24 ("人事部");
mysql> select * from emp; +----+--------+----------+--------+ | id | name | salary | dep_id | +----+--------+----------+--------+ | 1 | 张三 | 8000.00 | 2 | | 2 | 李四 | 12000.00 | 1 | | 3 | 王五 | 5000.00 | 2 | | 4 | 赵六 | 8000.00 | 3 | | 5 | 猪七 | 9000.00 | 1 | | 6 | 周八 | 7000.00 | 4 | | 7 | 蔡九 | 7000.00 | 2 | +----+--------+----------+--------+ rows in set (0.00 sec) mysql> select * from dep; +----+-----------+ | id | name | +----+-----------+ | 1 | 教学部 | | 2 | 销售部 | | 3 | 人事部 | +----+-----------+ rows in set (0.00 sec)
2、笛卡尔积查询
select * from emp,dep;
mysql> select * from emp,dep; +----+--------+----------+--------+----+-----------+ | id | name | salary | dep_id | id | name | +----+--------+----------+--------+----+-----------+ | 1 | 张三 | 8000.00 | 2 | 1 | 教学部 | | 1 | 张三 | 8000.00 | 2 | 2 | 销售部 | | 1 | 张三 | 8000.00 | 2 | 3 | 人事部 | | 2 | 李四 | 12000.00 | 1 | 1 | 教学部 | | 2 | 李四 | 12000.00 | 1 | 2 | 销售部 | | 2 | 李四 | 12000.00 | 1 | 3 | 人事部 | | 3 | 王五 | 5000.00 | 2 | 1 | 教学部 | | 3 | 王五 | 5000.00 | 2 | 2 | 销售部 | | 3 | 王五 | 5000.00 | 2 | 3 | 人事部 | | 4 | 赵六 | 8000.00 | 3 | 1 | 教学部 | | 4 | 赵六 | 8000.00 | 3 | 2 | 销售部 | | 4 | 赵六 | 8000.00 | 3 | 3 | 人事部 | | 5 | 猪七 | 9000.00 | 1 | 1 | 教学部 | | 5 | 猪七 | 9000.00 | 1 | 2 | 销售部 | | 5 | 猪七 | 9000.00 | 1 | 3 | 人事部 | | 6 | 周八 | 7000.00 | 4 | 1 | 教学部 | | 6 | 周八 | 7000.00 | 4 | 2 | 销售部 | | 6 | 周八 | 7000.00 | 4 | 3 | 人事部 | | 7 | 蔡九 | 7000.00 | 2 | 1 | 教学部 | | 7 | 蔡九 | 7000.00 | 2 | 2 | 销售部 | | 7 | 蔡九 | 7000.00 | 2 | 3 | 人事部 | +----+--------+----------+--------+----+-----------+ rows in set (0.00 sec)
3、内连接
查询两张表中都有的关联数据,相当于利用条件从笛卡尔积结果中筛选出了正确的结果。
SELECT * FROM emp,dep WHERE emp.dep_id=dep.id;
OR
SELECT * FROM emp INNER JOIN dep ON emp.dep_id=dep.id;
查询结果:
+----+--------+----------+--------+----+-----------+ | id | name | salary | dep_id | id | name | +----+--------+----------+--------+----+-----------+ | 1 | 张三 | 8000.00 | 2 | 2 | 销售部 | | 2 | 李四 | 12000.00 | 1 | 1 | 教学部 | | 3 | 王五 | 5000.00 | 2 | 2 | 销售部 | | 4 | 赵六 | 8000.00 | 3 | 3 | 人事部 | | 5 | 猪七 | 9000.00 | 1 | 1 | 教学部 | | 7 | 蔡九 | 7000.00 | 2 | 2 | 销售部 | +----+--------+----------+--------+----+-----------+ rows in set (0.00 sec)
这时我们就可以利用两张表中所有的字段进行查询了
示例:
-- 查询李四所在的部门名称 SELECT emp.name,dep.name FROM emp INNER JOIN dep ON emp.dep_id=dep.id WHERE emp.name="李四"; -- 查询销售部所有员工姓名以及部门名称 -- SELECT name FROM emp WHERE dep_id in (SELECT id FROM dep WHERE name="销售部"); SELECT emp.name,dep.name FROM emp INNER JOIN dep ON emp.dep_id=dep.id WHERE dep.name="销售部";
4、外连接
--(1)左外连接:在内连接的基础上增加左边有右边没有的结果
SELECT * FROM emp LEFT JOIN dep ON dep.id=emp.dep_id;
+----+--------+----------+--------+------+-----------+ | id | name | salary | dep_id | id | name | +----+--------+----------+--------+------+-----------+ | 2 | 李四 | 12000.00 | 1 | 1 | 教学部 | | 5 | 猪七 | 9000.00 | 1 | 1 | 教学部 | | 1 | 张三 | 8000.00 | 2 | 2 | 销售部 | | 3 | 王五 | 5000.00 | 2 | 2 | 销售部 | | 7 | 蔡九 | 7000.00 | 2 | 2 | 销售部 | | 4 | 赵六 | 8000.00 | 3 | 3 | 人事部 | | 6 | 周八 | 7000.00 | 4 | NULL | NULL | +----+--------+----------+--------+------+-----------+ rows in set (0.00 sec)
--(1)外右连接:在内连接的基础上增加右边有左边没有的结果
SELECT * FROM emp RIGHT JOIN dep ON dep.id=emp.dep_id;
mysql> SELECT * FROM emp RIGHT JOIN dep ON dep.id=emp.dep_id; +------+--------+----------+--------+----+-----------+ | id | name | salary | dep_id | id | name | +------+--------+----------+--------+----+-----------+ | 1 | 张三 | 8000.00 | 2 | 2 | 销售部 | | 2 | 李四 | 12000.00 | 1 | 1 | 教学部 | | 3 | 王五 | 5000.00 | 2 | 2 | 销售部 | | 4 | 赵六 | 8000.00 | 3 | 3 | 人事部 | | 5 | 猪七 | 9000.00 | 1 | 1 | 教学部 | | 7 | 蔡九 | 7000.00 | 2 | 2 | 销售部 | +------+--------+----------+--------+----+-----------+ rows in set (0.00 sec)
九、完整性约束
完整性约束是对字段进行限制,从而符合该字段达到我们期望的效果比如含有默认值,不能是NULL等,直观点说:如果插入的数据不满足要求,数据库管理系统就拒绝执行操作。
1、唯一约束
唯一约束可以有多个但索引的值必须唯一,索引列的值允许有空值。
如果能确定某个数据列将只包含彼此各不相同的值,在为这个数据列创建索引的时候就应该使用关键字UNIPUE。
1 CREATE TABLE t5( 2 id INT AUTO_INCREMENT, 3 name VARCHAR(20) DEFAULT NULL, 4 PRIMARY KEY (id), 5 UNIQUE KEY UK_t5_name (name) 6 ); 7 -- 建表后添加约束: 8 9 alter table t5 add constraint UK_t5_name unique (name); 10 11 -- 如果不需要唯一约束,则可以这样删除 12 13 ALTER TABLE t5 DROP INDEX UK_t5_name;
2、添加约束和删除约束
1 -- 创建唯一约束: 2 create unique index UK_t5_name on t5 (name); 3 4 -- 建表后添加约束: 5 6 alter table t5 add constraint UK_t5_name unique (name); 7 8 -- 如果不需要唯一约束,则可以这样删除 9 10 ALTER TABLE t5 DROP INDEX UK_t5_name;
3、自增约束
mysql每张表只能有1个自动增长字段,这个自动增长字段通常作为主键,也可以用作非主键使用,但是请注意将自动增长字段当做非主键使用时必须为其添加唯一索引,否则系统将会报错。
mysql> CREATE TABLE t4( -> id INT NOT NULL, -> name VARCHAR(20), -> age INT AUTO_INCREMENT -> ); ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
可以改为:
1 mysql> CREATE TABLE t4( 2 -> id INT NOT NULL, 3 -> name VARCHAR(20), 4 -> age INT UNIQUE AUTO_INCREMENT 5 -> ); 6 Query OK, 0 rows affected (0.13 sec)
4、主键约束
主键是用于唯一标识一条记录的约束,如同身份证。
主键有两个约束:非空且唯一。
5、创建主键
-- 方式1 CREATE TABLE t1( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20) ); -- 方式2 CREATE TABLE t2( id INT NOT NULL, name VARCHAR(20) );
注意:
1)、一张表中最多只能有一个主键
2)、表中如果没有设置主键,默认设置NOT NULL的字段为主键,此外,表中如果有多个NOT NULL的字段,则按顺序将第一个设置NOT NULL的字段设为主键。
结论:主键一定是非空且唯一,但非空且唯一的字段不一定是主键。
3)、主键类型不一定必须是整型。
6、添加主键和删除主键
-- 添加主键
alter table tab_name add primary key(字段名称,...)
-- 删除主键
alter table users drop primary key;
注意,如果主键是AUTO_INCREMENT,需要先取消AUTO_INCREMENT,因为AUTO_INCREMENT只能加在KEY上。
CREATE TABLE test(num INT PRIMARY KEY AUTO_INCREMENT);
-- 思考,如何删除主键? ALTER TABLE test modify id int; -- auto_increment没了,但这样写主键依然存在,所以还要加上下面这句 ALTER TABLE test drop primary key;-- 仅仅用这句也无法直接删除主键
7、复合主键
所谓的复合主键就是指表的主键含有一个以上的字段。
如果一列不能唯一区分一个表里的记录时,可以考虑多个列组合起来达到区分表记录的唯一性,形式
创建时:
1 create table sc ( 2 studentid int, 3 courseid int, 4 score int, 5 primary key (studentno,courseid) 6 );
修改时:
alter table tb_name add primary key (字段1,字段2,字段3);
8、外键约束
1)、外键语法
1 外键的定义语法: 2 [CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...) 3 REFERENCES tbl_name (index_col_name, ...) 4 [ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}] 5 [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
该语法可以在CREATE TABLE 和 ALTER TABLE 时使用,如果不指定CONSTRAINT symbol,mysql会自动生产一个名字。
2)、准备表和数据
-- 子表 CREATE TABLE emp( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20), dep_id INT -- CONSTRAINT emp_fk_emp FOREIGN KEY (dep_id) REFERENCES dep(id) -- 注意外键字段的数据类型必须与关联字段一致 ); -- 主表 CREATE TABLE dep( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20) ); INSERT emp (name,dep_id) VALUES ("alex",1), ("egon",2), ("alvin",2), ("莎莎",1), ("wusir",2), ("女神",2), ("冰冰",3), ("姗姗",3); INSERT dep (name) VALUES ("市场部"), ("教学部"), ("销售部");
3)、添加外键
现在,删除市场部:
mysql> DELETE FROM dep WHERE name="市场部"; Query OK, 1 row affected (0.01 sec)
居然删除成功了,不可思议,现在问题来了:alex和莎莎两个人怎么办?
所以,为了避免类似操作,我们需要两张表建立约束,这种约束外键约束,外键的好处:可以使得两张表关联,保证数据的一致性和实现一些级联操作
INSERT dep (id,name) VALUES (1,"市场部"); -- 思考为什么加这一句? ALTER TABLE emp ADD CONSTRAINT dep_fk_emp FOREIGN KEY (dep_id) REFERENCES dep(id);
mysql> DELETE FROM dep WHERE name="市场部";
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fail
9、INNODB支持的ON语句
外键约束对子表的含义:如果在主表中(比如dep)找不到候选键,则不允许在子表(比如emp)上进行insert/update
外键约束对父表的含义:在主表上进行update/delete以更新或删除在子表中有一条或多条应匹配行的候选键时,
父表的行为取决于:在定义子表的外键时指定的 -- on update/on delete子句。
-- ------------------------innodb支持的四种方式--------------------------------- cascade方式 在父表上update/delete记录时,同步update/delete掉子表的匹配记录外键的级联删除:如果父表中的记录被删除,则子表中对应的记录自动被删除 FOREIGN KEY (charger_id) REFERENCES ClassCharger(id) ON DELETE CASCADE; set null方式 在父表上update/delete记录时,将子表上匹配记录的列设为null ; 要注意子表的外键列不能为not null FOREIGN KEY (charger_id) REFERENCES ClassCharger(id) ON DELETE SET NULL; Restrict方式 :拒绝对父表进行删除更新操作(了解) No action方式 在mysql中同Restrict,如果子表中有匹配的记录,则不允许对父表对应候选键 ; 进行update/delete操作(了解)
十、表与表的关系(两张表)
1、一对多:将关联字段设置在多的表中
1 CREATE TABLE emp( 2 id INT PRIMARY KEY , 3 name VARCHAR (20), 4 dep_id INT , 5 FOREIGN KEY (dep_id) REFERENCES dep(id) 6 );
2、多对多:借助第三张表实现的
1 CREATE TABLE STUDENT2TEACHER( 2 id INT PRIMARY KEY auto_increment, 3 studnet_id INT , 4 teacher_id INT , 5 FOREIGN KEY (studnet_id) REFERENCES student(id), 6 FOREIGN KEY (teacher_id) REFERENCES teacher(id), 7 );
3、一对一:将关联字段设为unique
1 CREATE TABLE author( 2 id INT PRIMARY KEY , 3 name VARCHAR (20), 4 authorDetial_id INT unique, 5 FOREIGN KEY (dep_id) REFERENCES dep(id), 6 );