内容概要

• 约束条件

• 约束条件之外键(重点)

  表与表之间建立关系

• 查询关键字(重点)

  selectfromwheregroup byhavingdistinctorder bylimitregexp

内容详细

约束条件

在字段类型的基础之上添加的额外约束1.unsigned无符号(正负号)2.zerofill填充3.not null非空create table t2(id int,name varchar(32) not null);4.default默认值create table t3(id int,name char(32),gender enum(\'male\',\'female\',\'others\') default \'male\');5.unique唯一# 单列唯一create table t4(id int,name varchar(32) unique);# 多列唯一create table t5(id int,host varchar(32),port int,unique(host,port));6.primary key主键\"\"\"1.单从限制角度上来看 主键相当于是非空且唯一id int primary key == id int not null uniquecreate table t6(id int primary key);2.InnoDB存储引擎规定了在创建表的时候必须有且只有一个主键1.为什么之前创建表的时候没有主键也可以呢？当表中没有任何的约束条件字段，为了能够顺利的创建出表，InnoDB存储引擎会采用内部隐藏的字段作为主键隐藏意味着我们无法使用到，主键其实可以帮助我们加快数据的查询速度2.没有主键但是有非空且唯一的字段 那么会自动将该字段升级为主键create table t7(id int,age int not null unique,pwd int not null unique);结论:我们在创建表的时候一般都会创建一个id字段(序号\\编号)并且我们会将该字段设置为主键id int primary keyps:id名称可以不固定 uid cid pid\"\"\"7.auto_increment自增# 单一主键专门用来给key键使用(你就理解为是专门给主键用的)create table t8(id int primary key auto_increment,name varchar(32));# 联合主键create table t8(id int,nid int,name varchar(32),primary key(id,nid)...);\"\"\"以后的主键字段固定写法\"\"\"id int primary key auto_increment

主键的特征

# 1.delete from不会影响主键的自增# 2.truncate清空表数据并且重置主键

添加数据的两种方式

insert into t1 values(...);  # 按照字段顺序一一传值insert into t1(name) values(...);  # 指名道姓的传值

外键前戏

\'\'\'员工表\'\'\'id emp_name emp_salary dep_name dep_desc# 问题1.表结构不够明确(忽略)到底是员工表还是部门表2.浪费存储空间(忽略)硬盘不值钱 不够花钱买3.表扩展性极差(不能忽略)# 拆分上述三个问题都能够解决 但是员工与部门之间没有了关联外键字段用来建立表与表之间的关系的字段

表关系判断

表关系分类1.一对多2.多对多3.一对一4.没有关系表关系判断\"换位思考\"1.以员工表与部门表为例1.先站在员工表角度问:一名员工能否对应多个部门答:不可以2.再站在部门表角度问:一个部门能否对应多名员工答:可以结论:两边一个可以一个不可以 那么表关系就是\"一对多\"\'\'\'针对一对多表关系外键字段建在多的一方(建在员工表中)\'\'\'2.以书籍表与作者表为例1.先站在书籍表角度问:一本书能否对应多名作者答:可以2.再站在作者表角度问:一名作者能否对应多本书答:可以结论:两边都可以 那么表关系就是\"多对多\"\"\"\"针对多对多表关系外键字段并不会建在任何一张关系表而是单独开设一张新表专门存储\"\"\"3.以用户表与用户详情表为例1.先站在用户表角度问:一名用户能否对应多个用户详情答:不可以2.再站在用户详情表角度问:一个用户详情能否对应多名用户答:不可以结论:两边都不可以 那么要么没关系 要么\"一对一\"\"\"\"针对一对一表关系外键字段建在任何一方都可以但是推荐你建在查询频率较高的表中\"\"\"

SQL语句实现

############################一对多########################## 1.先创建基本字段# 2.再考虑外键字段create table emp(id int primary key auto_increment,emp_name varchar(32),emp_salary int,dep_id int,foreign key(dep_id) references dep(id));create table dep(id int primary key auto_increment,dep_name varchar(32),dep_desc varchar(64));\"\"\"1.在创建表的是先创建被关联的表(没有外键字段的表)2.在录入数据的时候先录入被关联表的数据3.修改或者删除被关联表数据\"\"\"####################多对多######################create table book(id int primary key auto_increment,title varchar(32),price float(8,2));create table author(id int primary key auto_increment,name varchar(32),age int);create table book2author(id int primary key auto_increment,author_id int,foreign key(author_id) references author(id)on update cascadeon delete cascade,book_id int,foreign key(book_id) references book(id)on update cascadeon delete cascade);######################一对一####################create table userinfo(id int primary key auto_increment,name varchar(32),age int,detail_id int unique,foreign key(detail_id) references userinfo_detail(id)on update cascadeon delete cascade);create table userinfo_detail(id int primary key auto_increment,addr varchar(32),phone bigint);

级联更新级联删除

create table dep(id int primary key auto_increment,dep_name varchar(32),dep_desc varchar(64));create table emp(id int primary key auto_increment,emp_name varchar(32),emp_salary int,dep_id int,foreign key(dep_id) references dep(id)on update cascade  # 级联更新on delete cascade  # 级联删除);

作业

1.书籍表与出版社表2.课程表与老师表3.班级表与学生表4.作者表与作者详情表\"\"\"书写出完整的判断过程并且写上对应的SQL语句\"\"\"

查询关键字

表准备

create table emp(id int primary key 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);#插入记录#三个部门：教学，销售，运营insert into emp(name,sex,age,hire_date,post,salary,office,depart_id) values(\'jason\',\'male\',18,\'20170301\',\'张江第一帅形象代言\',7300.33,401,1), #以下是教学部(\'tom\',\'male\',78,\'20150302\',\'teacher\',1000000.31,401,1),(\'kevin\',\'male\',81,\'20130305\',\'teacher\',8300,401,1),(\'tony\',\'male\',73,\'20140701\',\'teacher\',3500,401,1),(\'owen\',\'male\',28,\'20121101\',\'teacher\',2100,401,1),(\'jack\',\'female\',18,\'20110211\',\'teacher\',9000,401,1),(\'jenny\',\'male\',18,\'19000301\',\'teacher\',30000,401,1),(\'sank\',\'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);

1.select用来指定表的字段数据select * from emp;select id,name from emp;\"\"\"在工作中一般很少使用*号 我们只是为了教学方便\"\"\"2.from后面跟需要查询的表名即可3.where筛选数据

查询关键字之where

# 1.查询id大于等于3小于等于6的数据select id,name from emp where id >= 3 and id <= 6;select *  from emp where id between 3 and 6;# 2.查询薪资是20000或者18000或者17000的数据select * from emp where salary = 20000 or salary = 18000 or salary = 17000;select * from emp where salary in (20000,18000,17000);  # 简写\"\"\"模糊查询关键字like关键符号%:匹配任意个数的任意字符_:匹配单个个数的任意字符show variables like \'%mode%\';\"\"\"# 3.查询姓名中带有字母o的员工姓名和薪资select name,salary from emp where name like \'%o%\';# 4.查询姓名由四个字符组成的员工姓名和薪资select name,salary from emp where name like \'____\';select name,salary from emp where char_length(name) =4;# 5.查询id小于3或者大于6的数据select *  from emp where id not between 3 and 6;# 6.查询薪资不在20000，18000，17000范围的数据select * from emp where salary not in (20000,18000,17000);# 7.查询岗位描述为空的员工名与岗位名  针对null不能用等号，只能用isselect name,post from emp where post_comment = NULL;  # 查询为空！select name,post from emp where post_comment is NULL;select name,post from emp where post_comment is not NULL;

查询关键字之group by分组

分组将单个单个的个体按照指定的条件分成一个个整体\"\"\"分组之后默认只能直接获取到分组的依据其他字段无法再直接获取(可以间接获取)\"\"\"# 严格模式set global sql_mode=\'STRICT_TRANS_TABLES,PAD_CHAR_TO_FULL_LENGTH,only_full_group_by\'# 1.每个部门的最高薪资select post,max(salary) from emp group by post;# 2.每个部门的最低薪资select post,min(salary) from emp group by post;# 3.每个部门的平均薪资select post,avg(salary) from emp group by post;# 4.每个部门的人数select post,count(id) from emp group by post;# 5.每个部门的月工资总和select post,sum(salary) from emp group by post;\"\"\"可以给字段起别名(as还可以给表起别名)select post as \'部门\',sum(salary) as \'总和\' from emp group by post;\"\"\"# 查询分组之后的部门名称和每个部门下所有的员工姓名\"\"\"group_concat()  获取分组以外的字段数据 并且支持拼接操作select post,group_concat(name) from emp group by post;select post,group_concat(name,\':\',salary) from emp group by post;concat()  未分组之前使用的拼接功能select concat(name,\':\',sex) from emp;concat_ws()select concat_ws(\':\',name,sex,salary,age) from emp;\"\"\"

聚合函数

分组之后频繁需要使用的max     最大值min     最小值sum     求和count   计数avg     平均值

作业

1. 查询岗位名以及岗位包含的所有员工名字2. 查询岗位名以及各岗位内包含的员工个数3. 查询公司内男员工和女员工的个数4. 查询岗位名以及各岗位的平均薪资5. 查询岗位名以及各岗位的最高薪资6. 查询岗位名以及各岗位的最低薪资7. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资