表与表之间的关系
多对一：表1 foreign key 表2，则表1的多条记录对应表2的一条记录。
利用foreign key的原理我们可以制作两张表的多对多，一对一关系。
多对多：表1的多条记录可以对应表2的一条记录，表2的多条记录也可以对应表1的一条记录
一对一：表1的一条记录唯一对应表2的一条记录，反之亦然
理解表与表之间关系的步骤：
先确立关系
找到多的一方，把关联字段写在多的一方
多对一或者一对多
左边表的多条记录对应右边表的唯一一条记录
需要注意的：1.先建被关联的表，保证被关联表的字段必须唯一。
2.在创建关联表，关联字段一定保证是要有重复的。
这是一个书和出版社的一个例子，书要关联出版社(多个书可以是一个出版社，一个出版社也可以有好多书)，谁关联谁就是谁要按照谁的标准。
#书要关联出版社，被关联的表create table press(id int primary key auto_increment,name char(20));# 关联的表create table book(book_id int primary key auto_increment,book_name varchar(20),book_price int,press_id int,constraint Fk_pressid_id foreign key(press_id) references press(id)on delete cascadeon update cascade);# 插记录insert into press(name) values('新华出版社'), ('海燕出版社'), ('摆渡出版社'), ('大众出版社');insert into book(book_name,book_price,press_id) values('Python爬虫',100,1), ('Linux',80,1), ('操作系统',70,2), ('数学',50,2), ('英语',103,3), ('网页设计',22,3);一对一
例子一：用户和管理员(只有管理员才可以登录，一个管理员对应一个用户)，管理员关联用户
# 例子一：用户表和管理员表# 先建被关联的表create table user(id int primary key auto_increment, #主键自增name char(10));# 再建关联表create table admin(id int primary key auto_increment,user_id int unique,password varchar(16),foreign key(user_id) references user(id)on delete cascadeon update cascade);insert into user(name) values('susan1'), ('susan2'), ('susan3'), ('susan4'), ('susan5'), ('susan6');insert into admin(user_id,password) values(4,'sds156'), (2,'531561'), (6,'f3swe');运行结果截图：
例子二：学生表和客户表
# 例子二：学生表和客户表create table customer(id int primary key auto_increment,name varchar(10),qq int unique,phone int unique);create table student1(sid int primary key auto_increment,course char(20),class_time time,cid int unique,foreign key(cid) references customer(id)on delete cascadeon update cascade);insert into customer(name,qq,phone) values('小小',13564521,11111111), ('嘻哈',14758254,22222222), ('王维',44545522,33333333), ('胡军',545875212,4444444), ('李希',145578543,5555555), ('李迪',754254653,8888888), ('艾哈',74545145,8712547), ('啧啧',11147752,7777777);insert into student1(course,class_time,cid) values('python','08:30:00',3), ('python','08:30:00',4), ('linux','08:30:00',1), ('linux','08:30:00',7);运行结果截图：
多对多(多条记录对应多条记录)
书和作者(我们可以再创建一张表，用来存book和author两张表的关系)，要把book_id和author_id设置成联合唯一
联合唯一：unique(book_id，author_id)
联合主键：alter table t1 add primary key(id,avg)
多对多：一个作者可以写多本书，一本书也可以有多个作者，双向的一对多，即多对多
关联方式：foreign key+一张新的表
# 书和作者，另外在建一张表来存书和作者的关系#被关联的create table book1(id int primary key auto_increment,name varchar(10),price float(3,2));#========被关联的create table author(id int primary key auto_increment,name char(5));#========关联的create table author2book(id int primary key auto_increment,book_id int not null,author_id int not null,unique(book_id,author_id),foreign key(book_id) references book1(id)on delete cascadeon update cascade,foreign key(author_id) references author(id)on delete cascadeon update cascade);#========插入记录insert into book1(name,price) values('九阳神功',9.9), ('葵花宝典',9.5), ('辟邪剑谱',5), ('降龙十巴掌',7.3);insert into author(name) values('egon'),('e1'),('e2'),('e3'),('e4');insert into author2book(book_id,author_id) values(1,1), (1,4), (2,1), (2,5), (3,2), (3,3), (3,4), (4,5);例子二：用户表，用户组，主机表
-- 用户组create table user (id int primary key auto_increment,username varchar(20) not null,password varchar(50) not null);insert into user(username,password) values('egon','123'), ('root',147), ('alex',123), ('haiyan',123), ('yan',123);-- 用户组表create table usergroup(id int primary key auto_increment,groupname varchar(20) not null unique);insert into usergroup(groupname) values('IT'), ('Sale'), ('Finance'), ('boss');-- 建立user和usergroup的关系表create table user2usergroup(id int not NULL UNIQUE auto_increment,user_id int not null,group_id int not NULL,PRIMARY KEY(user_id,group_id),foreign key(user_id) references user(id)ON DELETE CASCADEon UPDATE CASCADE ,foreign key(group_id) references usergroup(id)ON DELETE CASCADEon UPDATE CASCADE);insert into user2usergroup(user_id,group_id) values(1,1), (1,2), (1,3), (1,4), (2,3), (2,4), (3,4); -- 主机表CREATE TABLE host(id int primary key auto_increment,ip CHAR(15) not NULL UNIQUE DEFAULT '127.0.0.1');insert into host(ip) values('172.16.45.2'), ('172.16.31.10'), ('172.16.45.3'), ('172.16.31.11'), ('172.10.45.3'), ('172.10.45.4'), ('172.10.45.5'), ('192.168.1.20'), ('192.168.1.21'), ('192.168.1.22'), ('192.168.2.23'), ('192.168.2.223'), ('192.168.2.24'), ('192.168.3.22'), ('192.168.3.23'), ('192.168.3.24');-- 业务线表create table business(id int primary key auto_increment,business varchar(20) not null unique);insert into business(business) values ('轻松贷'), ('随便花'), ('大富翁'), ('穷一生');-- 建立host和business关系表CREATE TABLE host2business(id int not null unique auto_increment,host_id int not null ,business_id int not NULL ,PRIMARY KEY(host_id,business_id),foreign key(host_id) references host(id)，FOREIGN KEY(business_id) REFERENCES business(id));insert into host2business(host_id,business_id) values (1,1), (1,2), (1,3), (2,2), (2,3), (3,4); -- 建立user和host的关系create table user2host(id int not null unique auto_increment,user_id int not null,host_id int not null,primary key(user_id,host_id),foreign key(user_id) references user(id),foreign key(host_id) references host(id));insert into user2host(user_id,host_id) values(1,1), (1,2), (1,3), (1,4), (1,5), (1,6), (1,7), (1,8), (1,9), (1,10), (1,11), (1,12), (1,13), (1,14), (1,15), (1,16), (2,2), (2,3), (2,4), (2,5), (3,10), (3,11), (3,12);单表查询
先创建表
#创建表create table employee(id int not null unique auto_increment,name varchar(20) not null,sex enum('male','female') not null default 'male', #大部分是男的age int(3) unsigned not null default 28,hire_date date not null,post varchar(50),post_comment varchar(100),salary double(15,2),office int, #一个部门一个屋子depart_id int);#查看表结构mysql> desc employee;+--------------+-----------------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+--------------+-----------------------+------+-----+---------+----------------+| id | int(11) | NO | PRI | NULL | auto_increment || name | varchar(20) | NO | | NULL | || sex | enum('male','female') | NO | | male | || age | int(3) unsigned | NO | | 28 | || hire_date | date | NO | | NULL | || post | varchar(50) | YES | | NULL | || post_comment | varchar(100) | YES | | NULL | || salary | double(15,2) | YES | | NULL | || office | int(11) | YES | | NULL | || depart_id | int(11) | YES | | NULL | |+--------------+-----------------------+------+-----+---------+----------------+#插入记录#三个部门：教学，销售，运营insert into employee(name,sex,age,hire_date,post,salary,office,depart_id) values('egon','male',18,'20170301','teacher',7300.33,401,1), #以下是教学部('alex','male',78,'20150302','teacher',1000000.31,401,1),('wupeiqi','male',81,'20130305','teacher',8300,401,1),('yuanhao','male',73,'20140701','teacher',3500,401,1),('liwenzhou','male',28,'20121101','teacher',2100,401,1),('jingliyang','female',18,'20110211','teacher',9000,401,1),('jinxin','male',18,'19000301','teacher',30000,401,1),('成龙','male',48,'20101111','teacher',10000,401,1),('歪歪','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门('丫丫','female',38,'20101101','sale',2000.35,402,2),('丁丁','female',18,'20110312','sale',1000.37,402,2),('星星','female',18,'20160513','sale',3000.29,402,2),('格格','female',28,'20170127','sale',4000.33,402,2),('张野','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门('程咬金','male',18,'19970312','operation',20000,403,3),('程咬银','female',18,'20130311','operation',19000,403,3),('程咬铜','male',18,'20150411','operation',18000,403,3),('程咬铁','female',18,'20140512','operation',17000,403,3);注意： select * from t1 where 条件 group by 分组字段
分组只能查询分组字段，要想查看其余的利用聚合函数
聚合函数的分类：count,min,max,avg,group_concat,sum等。
模糊匹配：用like关键字
select * from t1 where name like '%eg%'; # %表示任意字符
select * from t1 where name like 'd__l'; #一个下划线表示一个字符，两个下划线就表示两个字符
拷贝表
create table t2 select * from t1;
create table t2 select * from t1 where 1=2 ;
查询语法
SELECT 字段1,字段2... FROM 表名 WHERE 条件 GROUP BY field HAVING 筛选 ORDER BY field LIMIT 限制条数简单查询# 简单查询SELECT id,name,sex,age,hire_date,post,post_comment,salary,office,depart_id FROM employee;SELECT * FROM employee;SELECT name,salary FROM employee;#避免重复DISTINCTSELECT DISTINCT post FROM employee; #通过四则运算查询SELECT name, salary*12 FROM employee;SELECT name, salary*12 AS Annual_salary FROM employee;SELECT name, salary*12 Annual_salary FROM employee;#定义显示格式# CONCAT() 函数用于连接字符串SELECT CONCAT('姓名: ',name,' 年薪: ', salary*12) AS Annual_salary FROM employee;# CONCAT_WS() 第一个参数为分隔符SELECT CONCAT_WS(':',name,salary*12) AS Annual_salary FROM employee;小练习：
# 1 查出所有员工的名字，薪资,格式为 select concat(',name,'> ' ,',salary,'>' ) from employee;# 2 查出所有的岗位(去掉重复)select distinct depart_id from employee;# 3 查出所有员工名字，以及他们的年薪,年薪的字段名为年薪select name,salary*12 年薪 from employee;where约束where字句中可以使用：
1. 比较运算符：> < >= <= <> !=
2. between 80 and 100 值在10到20之间
3. in(80,90,100) 值是80或90或100
4. like 'eg%'，可以是%或_，%表示任意多字符，_表示一个字符，like 'e__n'
5. 逻辑运算符：在多个条件直接可以使用逻辑运算符 and or not
#1:单条件查询SELECT name FROM employee WHERE post='sale';#2:多条件查询SELECT name,salary FROM employee WHERE post='teacher' AND salary>10000;#3:关键字BETWEEN ANDSELECT name,salary FROM employee WHERE salary BETWEEN 10000 AND 20000;SELECT name,salary FROM employee WHERE salary NOT BETWEEN 10000 AND 20000;#4:关键字IS NULL(判断某个字段是否为NULL不能用等号，需要用IS)SELECT name,post_comment FROM employee WHERE post_comment IS NULL;SELECT name,post_comment FROM employee WHERE post_comment IS NOT NULL;SELECT name,post_comment FROM employee WHERE post_comment=''; # 注意''是空字符串，不是null# ps：执行update employee set post_comment='' where id=2; # 再用上条查看，就会有结果了#5:关键字IN集合查询SELECT name,salary FROM employee WHERE salary=3000 OR salary=3500 OR salary=4000 OR salary=9000 ;SELECT name,salary FROM employee WHERE salary IN (3000,3500,4000,9000) ;SELECT name,salary FROM employee WHERE salary NOT IN (3000,3500,4000,9000) ;#6:关键字LIKE模糊查询# 通配符’%’SELECT * FROM employee WHERE name LIKE 'eg%';# 通配符’_’SELECT * FROM employee WHERE name LIKE 'al__';having过滤having和where语法上是一样的。
select * from employee where id>15; select * from employee having id>15;执行优先级从高到低：where > group by > 聚合函数 > having >order by
where和having的区别：
where 是一个约束声明，使用where约束来自数据库的数据，where是在结果返回之前起作用的，(先找到表，按照where的约束条件，从表(文件)中取出数据)，where中不能使用聚合函数
having是一个过滤声明，是在查询返回结果集以后对查询结果进行的过滤操作(先找到表，按照where的约束条件，从表(文件)中取出数据，然后group by分组， 如果没有group by则所有记录整体为一组，然后执行聚合函数，然后使用having对聚合的结果进行过滤)，在having中可以使用聚合函数。
where的优先级比having的优先级高。
having可以放到group by之后，而where只能放到group by 之前。
验证不同之处
1.查看员工的id>15的有多少个select count(id) from employee where id>15;#正确，分析：where先执行，后执行聚合count(id),然后select出结果select count(id) from employee having id>15; #报错，分析：先执行聚合count(id)，后执行having过滤，无法对id进行id>15的过滤#以上两条sql的顺序是# 1:找到表employee--->用where过滤---->没有分组则默认一组执行聚合count(id)--->select执行查看组内id数目# 2:找到表employee--->没有分组则默认一组执行聚合count(id)---->having 基于上一步聚合的结果(此时只有count(id)字段了)进行id>15的过滤，很明显，根本无法获取到id字段1 ------having-----------2 select depart_id,count(id) from employee group by depart_id;3 select depart_id,count(id) from employee group by depart_id having depart_id = 3;4 select depart_id,count(id) from employee group by depart_id having count(id)>7;5 select max(salary) 最大工资 from employee where id>2 group by depart_id having count(id)>3;6 select * from employee where id>7; #查看所有id>7的员工信息小练习：
1. 查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数select post,group_concat(name) 员工姓名,count(id) 个数 from employee group by post having count(id)<2;2. 查询各岗位平均薪资大于10000的岗位名、平均工资select post,avg(salary) from employee group by post having avg(salary)>10000;3. 查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资select post,avg(salary) from employee group by post having avg(salary) between 10000 and 20000;分组查询 group by单独使用GROUP BY关键字分组
select post from employee group by post;# 注意：我们按照post字段分组，那么select查询的字段只能是post，想要获取组内的其他相关信息，需要借助函数GROUP BY关键字和group_concat()函数一起使用select post,group_concat(name) from employee group by post;#按照岗位分组，并查看组内成员名select post,group_concat(name) as emp_members FROM employee group by post;GROUP BY与聚合函数一起使用select post,count(id) as count from employee group by post;#按照岗位分组，并查看每个组有多少人小练习：
1. 查询岗位名以及岗位包含的所有员工名字select post,group_concat(name) from employee group by post;2. 查询岗位名以及各岗位内包含的员工个数select post,count(id) from employee group by post;3. 查询公司内男员工和女员工的个数select sex,count(id) from employee group by sex;4. 查询岗位名以及各岗位的平均薪资select post,max(salary) from employee group by post;5. 查询岗位名以及各岗位的最高薪资select post,max(salary) from employee group by post;6. 查询岗位名以及各岗位的最低薪资select post,min(salary) from employee group by post;7. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资 select sex,avg(salary) from employee group by sex;关键字的执行优先级(重点)fromwheregroup byhavingselectdistinctorder bylimit 1.找到表:from
2.拿着where指定的约束条件，去文件/表中取出一条条记录
3.将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组
4.如果有聚合函数，则将组进行聚合
5.将4的结果过滤：having
6.查出结果：select
7.去重
8.将6的结果按条件排序：order by
9.将7的结果限制显示条数
查询排序order by
按单列排序SELECT * FROM employee ORDER BY salary;SELECT * FROM employee ORDER BY salary ASC;SELECT * FROM employee ORDER BY salary DESC;按多列排序：先按照age排序，如果年纪相同，则按照薪资排序SELECT * from employee ORDER BY age,salary DESC;===========order by==========1.select * from employee order by salary; #如果不指定，默认就是升序2.select * from employee order by salary asc;3.select * from employee order by salary desc;#先按照年龄升序，当年龄相同的太多，分不清大小时，在按照工资降序4.select * from employee order by age asc, salary desc;小例子：
# 1. 查询所有员工信息，先按照age升序排序，如果age相同则按照hire_date降序排序select * form employee order by age,hire_date desc;# 2. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资升序排列select post ,avg(salary) from employee group by post having avg(salary)>10000;# 3. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资降序排列select post ,avg(salary) from employee group by post having avg(salary)>10000 desc;使用聚合函数查询先from找到表
再用where的条件约束去表中取出记录
然后进行分组group by，没有分组则默认一组
然后进行聚合
最后select出结果
示例：
select count(*) from employee;select count(*) from employee where depart_id=1;select max(salary) from employee;select min(salary) from employee;select avg(salary) from employee;select sum(salary) from employee;select sum(salary) form employee WHERE depart_id=3;where的补充(使用正则表达式查询)
1.select * from employee where name regexp '^ale'; #匹配以ale开头的员工信息2.select * from employee where name regexp 'on$'; #匹配以on结尾的员工信息3.select * from employee where name regexp 'n{1,2}'; #匹配name里面包含1到2个n的员工信息# 小结：对字符串匹配的方式where name = 'egon';where name like 'yua%';where name regexp 'on$';小练习：
# 查看所有员工中名字是jin开头，n或者g结果的员工信息select * from employee where name regexp '^jin.*[ng]$';限制查询的记录数：LIMIT
limit：限制打印几条# 1.打印前三条select * from employee limit 3;# 2.像这样表示的：指的是从哪开始，往后取几条 (这样的操作一般用来分页)select * from employee limit 0,3;select * from employee limit 3,4;select * from employee limit 6,3;select * from employee limit 9,3;# 3. 查看后三条select * from employee order by id desc limit 3;小练习
# 1. 分页显示，每页5条select * from employee limit 0,5;select * from employee limit 5,5;select * from employee limit 10,5;
1429
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
