SQL day_03(5.6)DQL详解

一.列约束

 1.唯一约束 —— unique

  声明了唯一约束的列不允许插入重复的值，允许插入null，甚至多个null；一个表中可以使用多个唯一约束

  练习：给laptop表的title添加唯一约束和非空约束

 2.默认值约束

  插入数据的时候，如果不提供值，就会使用默认值

  (1)设置默认值

    使用default关键字设置默认值

  (2)应用默认值

    给特定的列提供值，没有出现的列自动应用默认值

    insert  into  数据表(列名称...)  values(值...);

    insert  into  数据表  values(值,... default...); 哪个列要使用默认值，直接赋值为default即可。

   练习：给laptop的price列添加默认值约束，设置默认价格为3000；应用默认值

 3.检查约束 —— check

   也称为自定义约束，是程序员指定的约束条件

   create  table  student(

     score  tinyint  check(score>=0  and  score<=100)

   );

   mysql不支持检查约束；认为会严重影响数据的插入速度。 后期都是通过JS来完成

 4.外键约束

  声明了外键约束列，插入的值必须在另一个表的主键列出现过；

  外键列要和对应的主键列两者的类型要一致

  foreign  key(外键列)  references  另一个表(主键列)

  

二.自增列

 auto_increment：自动增长，声明了自增列，只需要赋值为null，会自动的获取最大值然后加1插入。

   注意事项：

    必须添加在整数形式的主键列

    允许手动赋值

练习：编写脚本文件01_tedu.sql，设置编码为utf-8，先丢弃再创建数据库tedu，进入到数据库，创建保存部门数据的表dept，包含的列有编号did(主键、自增)，部门名称dname(唯一约束)，插入以下数据

  10  研发部      20  运营部     30  市场部        40  测试部

 创建保存员工数据的表emp，包含的列有编号eid(主键，自增)，姓名ename(非空约束)，性别sex，生日birthday，工资salary，所属部门编号deptid(外键约束)；插入若干条数据

三.简单查询

 1.查询特定的列

   示例：查询出所有员工的编号和姓名

   select eid,ename from emp;

   练习：查询出所有员工的姓名，性别，生日，工资

   select ename,sex,birthday,salary from emp;

 2.查询所有的列

   select eid,ename,sex,birthday,salary,deptid from emp;

   select * from emp;

 3.给列起别名

   示例：查询出所有员工的编号和姓名，使用一个字母作为别名

   select  eid as a, ename as b  from emp;

   练习：查询出所有员工的姓名，性别，工资，添加别名

   select  ename a, sex b, salary c  from emp;

as  用来设置别名 as关键字可以省略，保留空格即可

 4.显示不同的记录

  示例：查询出都有哪些性别的员工

  select distinct sex  from emp;

  练习：查询出员工都分布在哪些部门

  select distinct deptid from emp;

distinct  不同的，有区别

 5.查询时执行计算

  示例：查询出所有员工的姓名及其年薪

  select ename,salary*12 from emp;

  练习：假设每个员工的工资增长2000，年终奖是30000，查询出所有员工的姓名及其年薪，使用一个字母作为别名

  select ename a, (salary+2000)*12+30000 b from emp;

 6.查询结果排序

  示例：查询出所有的部门，结果按照部门编号升序排列

  select * from dept order by did asc;  #ascendant升序的

  示例：查询出所有的部门，结果按照部门编号降序排列

  select * from dept order by did desc; 

       describe  描述  /  descendant   降序的

  练习：查询出所有员工，结果按照工资降序排列

  select * from emp order by salary desc;

  练习：查询出所有员工，结果按照年龄从大到小排列

  select * from emp order by birthday asc;

  练习：查询出所有员工，结果按照姓名升序排列

  select * from emp order by ename asc;

按照字符串排列，会按照首个字符的Unicode编码排列

  示例：查询出所有员工，结果按照工资降序排列，如果工资相同按照姓名排列

  select * from emp order by salary desc,ename asc;

  练习：查询出所有员工，结果要求女员工显示在前，如果性别相同按照年龄从大到小排列

  select * from emp order by sex,birthday;

不加排序规则，默认是按照升序排列

 7.条件查询

  示例：查询出编号为5的员工

  select * from emp where eid=5;

  练习：查询出姓名为king的员工

  select * from emp where ename='king';

  练习：查询出10号的员工有哪些

  select * from emp where deptid=10;

  练习：查询出不在10号部门有哪些

  select * from emp where deptid!=10;

>   <   >=   <=   =   !=(不等于)

  练习：查询出没有明确部门的员工有哪些(部门编号为null)

  select * from emp where deptid is null;

  练习：查询出有明确部门的员工有哪些(部门编号不为null)

  select * from emp where deptid is not null;

and(&&)   两个条件都满足 or(||)   两个条件只需要满足其中一个

  练习：查询出工资在8000以上的男员工有哪些

  select * from emp where salary>8000 and sex=1;

  select * from emp where salary>8000 && sex=1;

  练习：查询出工资在6000~8000之间的员工有哪些

  select * from emp where salary>=6000 and salary<=8000;

  练习：查询出1993年以后出生或者1990年之前出生的员工

  select * from emp where birthday>'1993-12-31' or birthday<'1990-1-1';

  select * from emp where birthday>'1993-12-31' || birthday<'1990-1-1';

  练习：查询出20号部门或者30号部门的员工

  select * from emp where deptid=20 or deptid=30;

  select * from emp where deptid in(20,30);

  练习：查询出不在20号部门并且也不在30号部门的员工

  select * from emp where deptid!=20 and deptid!=30;

  select * from emp where deptid not in(20,30);

 8.模糊条件查询

  示例：查询出姓名中含有字母e的员工有哪些

  select * from emp where ename like '%e%';

  练习：查询出姓名中以e结尾的员工有哪些

  select * from emp where ename like '%e';

  练习：查询出姓名中倒数第2个字符是e的员工有哪些

  select * from emp where ename like '%e_';

    %  匹配任意个字符  >=0

    _  匹配任意一个字符  =1

    以上两个匹配的符号必须结合like关键字使用

 9.分页查询

  查询的结果中有太多的数据，一次显示不完可以做成分页

  需要有两个已知条件：当前的页码、每页的数据量

开始查询的值 = (当前的页码-1)*每页的数据量

  select * from emp  limit 开始查询的值,每页的数据量;

  练习：假设每页显示5条数据，查询出前4页的数据

  第1页：select * from emp limit 0,5;

  第2页：select * from emp limit 5,5;

  第3页：select * from emp limit 10,5;

  第4页：select * from emp limit 15,5;

四、复杂查询

 1.聚合查询/分组查询

  示例：查询出所有员工的数量

  select count(*) from emp;

  练习：使用员工的编号列查询数量

  select count(eid) from emp;  #推荐使用主键列

  练习：使用员工所属部门编号列查询数量

  select count(deptid) from emp;  #null不会计算在内

函数：是一个功能体，需要提供若干数据，返回结果 —— 饺子机 聚合函数 count()/sum()/avg()/max()/min()  数量   总和  平均  最大  最小

  练习：查询出所有男员工的工资总和

  select sum(salary) from emp where sex=1;

  练习：查询出20号部门的平均工资

  select avg(salary) from emp where deptid=20;

  练习：查询出女员工的最高工资

  select max(salary) from emp where sex=0;

  练习：查询出年龄最大的员工的生日

  select min(birthday) from emp;

通常分组查询只用于查询聚合函数和分组条件。

  示例：查询出男女员工的数量、工资总和、平均工资

  select count(eid),sum(salary),avg(salary),sex from emp group by sex;

  练习：查询出各部门的员工数量，最高工资，最低工资

  select count(eid),max(salary),min(salary),deptid from emp group by deptid;

year() 获取日期中的年份

  示例：select  year('2022-5-7');

  练习：查询出1990年出生的员工有哪些

  select * from emp where year(birthday)=1990;

  练习：查询出所有员工姓名和出生的年份

  select ename,year(birthday) from emp;

 2.子查询

多个SQL命令的组合，把一个SQL命令的查询结果作为另一个的条件使用

  示例：查询出年龄最小的员工

   步骤1：年龄最小的员工生日是哪一天 —— ' 1995-12-03'

   select max(birthday) from emp;

   步骤2：通过这一天找到对应的员工

   select * from emp where birthday='1995-12-03';

   综合：

   select * from emp where birthday=(select max(birthday) from emp);

  练习：查询出比tom工资高的员工有哪些

   步骤1：查询出tom的工资 —— 6000

   select salary from emp where ename='tom';

   步骤2：查询工资高于6000的员工

   select * from emp where salary>6000;

   综合：

   select * from emp where salary>(select salary from emp where ename='tom');

  练习：查询出和tao同一个部门的员工

   步骤1：查询出tao的部门编号 —— 20

   select deptid from emp where ename='tao';

   步骤2：查询出20号部门的员工，不包含tao

   select * from emp where deptid=20 and ename!='tao';

   综合：

   select * from emp where deptid=(select deptid from emp where ename='tao') and ename!='tao';

  练习：查询出和tom同一年出生的员工

   步骤1：查询tom出生的年份 —— 1990

   select year(birthday) from emp where ename='tom';

   步骤2：查询出1990年出生的员工，不包含tom

   select * from emp where year(birthday)=1990 and ename!='tom';

   综合：

   select * from emp where year(birthday)=(select year(birthday) from emp where ename='tom') and ename!='tom';

 3.多表查询

  查询的列分布在多个表中，前提是表之间是建立了关联

  示例：查询出所有员工的姓名及其部门名称

  select ename,dname from emp,dept where deptid=did;

  防止多个表列名称相同，可以在列名称前加表名称

  select emp.ename,dept.dname from emp,dept where emp.deptid=dept.did;

  (1)内连接

   select ename,dname from emp inner join dept on deptid=did;

   和之前的结果是一样的

  (2)左外连接

   select ename,dname from emp left outer join dept on deptid=did;

   显示所有的员工

  (3)右外连接   

   select ename,dname from emp right outer join dept on deptid=did;

   显示所有的部门

   左外连接和右外连接的outer关键字可以省略

  (4)全连接

   同时显示所有的员工和所有的部门

    full  join   on

   mysql不支持全连接

   联合

    union all  联合后，不合并相同记录

    union   联合后，合并相同的记录

解决全连接：将左右连接和右外连接两组进行联合，联合后合并相同记录

   (select ename,dname from emp left outer join dept on deptid=did)

   union

   (select ename,dname from emp right outer join dept on deptid=did);

注意事项：   limit后开始查询的值，不能写运算，只能写某一个值   limit后的两个值必须是数值型，不能加引号