web学习笔记8-MySQL基础（列约束与查询）

一、列约束

1、主键约束

primary key：声明了主键约束的列，不允许插入重复的值，一个表中只能有一个主键约束，通常加在编号列，会加快数据的查询速度。

null：空，表示一个暂时无法确定的值，例如：无法确定一个员工的手机号码、无法确定一个商品的价格…

主键约束的列禁止插入null

did int primary key,

2,非空约束

not null：声明了非空约束的列，不允许插入null

3、唯一约束

unique：生成了唯一约束的列，不允许插入重复的值，允许插入null，甚至多个null

primary key = not null + unique

4、默认值约束

（1）设置默认值，使用关键字default设置

（2）应用默认值

       在要使用默认值的位置直接写default，就会调用这个列的默认值

insert into 数据表 values(列名 列类型 default 默认值);


insert into 数据表(nid,title) values(null,'标题5');

5、检查约束

check ：用户指定约束条件

create table student(
    score tinyint check(score>=0 && score<=100)
);

mysql 不支持检查约束，后期由JS完成验证

6、外键约束

声明外键约束的列的值，确保插入的值必须是在另一个表的主键中出现的值，目的是为了让两个表建立关联。

--外键约束语法
foreign key(外键列) references 另一个表名(表主键)

-- 示例
-- 创建表声明外键
create table news(
    cat_id int,
    -- 将cat_id这个列蛇者为外键约束，插入的值必须要在分类表中出现过
    foreign key(cat_id) references category(cid)
);

查看表的描述，可以看到key值已经变为MUL

注意：外键约束的列的列类型要盒另一个表的主键列的列类型要保持一直，外键约束允许插入null。

二、简单查询

1.查询特定的列

-- 示例：查询出所有员工的编号和姓名
select eid,ename from emp;
-- 练习查询所有员工的姓名，性别，生日，工资
select ename,sex,birthday,salary from emp;

2.查询所有的列- *

-- 1,使用通配符
select * from 表名;
-- 2,将所有列名写出
select eid,ename,sex,birthday,salary,deptid from emp;

3.给列起别名-as

--示例 查询所有员工的编号和姓名，使用别名
select eid as bianhao,ename as xm from emp;
-- 练习查询所有员工的姓名，性别，生日，工资，使用别名
select ename as a,sex as b,birthday as c,salary as d from emp;
--简化，可以省掉as关键字
select ename a,sex b,birthday c,salary d from emp;

查询显示的结果列名已经改变

4.显示不同的记录(相同记录只显示一个)-distinct

distinct 不同的，有区别的

-- 示例：查询出员工都分布在那些部门
select distinct deptid from emp;
-- 练习查询性别
select distinct sex from emp;

5.查询时执行计算

-- 示例：计算出2+3+4*5*8.3+9*2.4
select 2+3+4*5*8.3+9*2.4;
-- 练习，查询所有员工的姓名及年薪
select ename,salary*12 as yearSalary from emp;
-- 练习：假设每个员工工资增长2000元，年终奖20000
select ename,(salary+2000)*12+20000 as yearSalary from emp;

6.查询结果排序-order by

asc -> ascendant 升序的

desc -> descendant 降序的

--示例：查询所有部门，按照部门编号升序排列
select * from dept order by did asc;
--示例：查询所有部门，按照部门编号升序排列
select * from dept order by did asc;
-- 练习 查询员工，按照工资升序
select * from emp order by salary asc;
-- 练习 查询员工，按照性别降序
select * from emp order by sex desc;
-- 练习 查询员工 ，按照年龄从大到小(生日从小到大)
select * from emp order by birthday asc;
-- 练习 查询员工，按照姓名升序
select * from emp order by ename asc;
-- 示例 查询员工，按照性别升序，工资降序
select * from emp order by sex,salary desc;
-- 练习 查询员工，按照部门编号升序，生日降序
select * from emp order by deptid,birthday desc;

说明：按照字符串排序，按照首个字符的Unicode编码排序；排序默认按照升序排列。

7.条件查询-where

-- 示例 查询出编号为8的员工
select * from emp where eid = 8;
-- 查询姓名为king的员工
select * from emp where ename = 'king';
-- 查询所有女员工
select * from emp where sex = 0;
-- 查询工资大于10000
select * from emp where salary >= 10000;
-- 查询20号部门的所有员工
select * from emp where deptid = 20;
-- 查询不在20部门的员工
select * from emp where deptid != 20;
-- 查询没有明确部门的员工
select * from emp where deptid is null;
-- 查找所有明确部门的员工
select * from emp where deptid is not null;
-- 多条件查询  查询出工资在8000-10000的员工
select * from emp where salary >=8000 && salary<=10000;
select * from emp where salary >=8000 and salary<=10000;
-- 查询出工资在8000以下或者10000以上的员工
select * from emp where salary <8000 || salary>10000;
select * from emp where salary <8000 or salary>10000;
-- 查询出在20号部门或者在30号部门的员工
select * from emp where deptid in(20,30);
-- 查询出不在20号部门或者不在30号部门的员工
select * from emp where deptid  not in(20,30);

说明：

is null 查找某一列的值为null的数据

is not null 查找某一列的值不为null的数据

&&(and) 与，满足两边的条件

||（or）或 ，满足两边的其中一个条件

in(值1，值2) 查询值满足等于其中一个

not in(值1，值2) 查询值满足不等于括号中任何一个

8.模糊条件查询-like

-- 示例 查询姓名中含有e的员工
select * from emp where ename like '%e%';
-- 练习 查询姓名中以e结尾的员工
select * from emp where ename like '%e';
-- 练习 查询姓名中以e开始的员工
select * from emp where ename like 'e%';
-- 练习 查询姓名中倒数第二个字符是e的员工
select * from emp where ename like '%e_';

说明：

% 匹配任意个字符

_ 匹配任意一个字符

注意： 以上两个匹配符号必须要结合like关键字使用

9.分页查询

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

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

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

分页查询的语法

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

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

-- 第一页
select * from emp limit 0,5;
-- 第二页
select * from emp limit 5,5;
-- 第三页
select * from emp limit 10,5;
-- 第四页
select * from emp limit 15,5;

注意事项：

1、limit后开始查询的值不能写运算，必须写最终的运算结果

2、limit后开始查询的值和每页的数据量必须是数值型，不能加引号

三、复杂查询

1.聚合查询/分组查询

聚合函数：count() / sum() / avg() / max() / min()

                    数量 求和 平均 最大 最小

-- 示例 查询所有员工的数量
select count(*) from emp;
-- 练习 使用员工编号列查询员工数量
select count(eid) from emp;
-- 练习 使用员工所属部门编号查询出员工数量
select count(deptid) from emp;
-- 练习 查询10号部门的工资总和
select sum(salary) from emp where deptid = 10;
-- 练习 查询女员工的平均工资
select avg(salary) from emp where sex = 0;
-- 练习 查询男员工年龄最大的生日
select min(birthday) from emp where sex = 1;
-- 练习 查询20部门的最高工资
select max(salary) from emp where deptid = 20;

分组查询-group

--示例 查询男女员工的数量，工资总和
select sex,count(eid),sum(salary) from emp group by sex;
-- 练习 查询每个部门的最高工资，最低工资，平均工资
select deptid,max(salary),min(salary),avg(salary) from emp group by deptid;
-- 

说明：分组查询通常只是查询聚合函数和分组条件

year() 获取一个日期中的年份

-- 练习 查询所有员工出生的年份
select ename,year(birthday) from emp;
-- 练习 查询1996年出生的员工
select * from emp where year(birthday)=1996;

md5() 加密函数

selec md5('123456');

2.子查询

子查询是多个sql命令的嵌套

-- 示例 查询出高于平均工资的员工
-- 步骤一 先查询出平均工资
select avg(salary) from emp;
-- 步骤二 查询出高于平均工资的员工
select * from emp where salary > 平均工资;
-- 子查询方法
select * from emp where salary > (select avg(salary) from emp);
-- 练习 查询出与jerry同部门的员工
select * from emp where deptid = 
(select deptid from emp where ename = 'jerry') && ename not in('jerry');
-- 练习 查询工资最高的员工
select * from emp where salary = (select max(salary) from emp) ;
-- 练习 查询出与tom同一年出生的员工
select * from emp where year(birthday) = 
(select year(birthday) from emp where ename='tom') && 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;
-- 左外连接 确保左边表的数据都显示
select emp.ename,dept.dname from emp left join dept
    on emp.deptid = dept.id;