Web前端105天-day24-MySQL-CSDN博客

检查约束也称作自定义约束，用户可以自己添加约束条件，mysql不支持检查约束，认为这种约束会严重影响数据的插入速度。后期这个功能是由JS代替。
   create  table  t1(
      score  tinyint   check(score>=0 && score<=100)
   );

2.3 默认值约束 —— default

声明了默认值约束的列，插入的值时候可以使用默认值

使用default设置默认值

插入值的时候使用默认值

insert into 数据表 values( default ,.... )--插入值的位置default关键字就会寻找默认值

insert into 数据表(列名称,..) values(值,...)--没有出现的列会自动应用默认值

2.4外键约束

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

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

注意事项：

外键列和另一个表的主键列的列类型要保持一致

代码位置上，外键约束之前，对应的另一个表必须已经创建了。

三、自增列

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

注意事项：
自增列必须定义在主键形式的整数列
自增列允许手动赋值

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

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

创建保存员工数据的表emp，包含的列有编号eid(主键约束、自增列)，姓名ename(非空约束)，性别sex(默认值约束)，生日birthday，工资salary，所属部门编号deptid(外键约束)，插入若干条数据。
-- 设置客户端连接服务器端的编码
set names utf8;
-- 丢弃数据库，如果存在
drop database if exists tedu;
-- 创建新的数据库，设置存储字符的编码
create database tedu charset=utf8;
-- 进入数据库
use tedu;
-- 创建保存部门数据的表
create table dept(
	did int primary key auto_increment,
	dname varchar(8) unique
);
-- 插入数据 
insert into dept values(10,'研发部');
insert into dept values(20,'运营部');
insert into dept values(30,'市场部');
insert into dept values(40,'测试部');
-- 创建保存员工数据的表
create table emp(
	eid int primary key auto_increment,
	ename varchar(16) not null,
	sex boolean default 0,  -- 1-男  0-女
	birthday date,
	salary decimal(7,2),   -- 99999.99
	deptid int,
	foreign key(deptid) references dept(did)
);
-- 插入数据
insert into emp values(null,'xin',default,'1983-7-15',50000,20);
insert into emp(ename,birthday,salary,deptid) values('nan','1984-8-20',40000,30);
INSERT INTO emp VALUES(NULL,'Tom',1,'1990-5-5',6000,20);
INSERT INTO emp VALUES(NULL,'Jerry',0,'1991-8-20',7000,10);
INSERT INTO emp VALUES(NULL,'David',1,'1995-10-20',3000,30);
INSERT INTO emp VALUES(NULL,'Maria',0,'1992-3-20',5000,10);
INSERT INTO emp VALUES(NULL,'Leo',1,'1993-12-3',8000,20);
INSERT INTO emp VALUES(NULL,'Black',1,'1991-1-3',4000,10);
INSERT INTO emp VALUES(NULL,'Peter',1,'1990-12-3',10000,10);
INSERT INTO emp VALUES(NULL,'Franc',1,'1994-12-3',6000,30);
INSERT INTO emp VALUES(NULL,'Tacy',1,'1991-12-3',9000,10);
INSERT INTO emp VALUES(NULL,'Lucy',0,'1995-12-3',10000,20);
INSERT INTO emp VALUES(NULL,'Jone',1,'1993-12-3',8000,30);
INSERT INTO emp VALUES(NULL,'Lily',0,'1992-12-3',12000,10);
INSERT INTO emp VALUES(NULL,'Lisa',0,'1989-12-3',8000,10);
INSERT INTO emp VALUES(NULL,'King',1,'1988-12-3',10000,10);
INSERT INTO emp VALUES(NULL,'Brown',1,'1993-12-3',22000,NULL);

四、查询

4.1简单查询

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

-- 2.查询所有的列
select * from emp;

-- 3.给列其别名
-- 示例：查询出所有员工的编号和姓名，是由一个字母作为别名
select eid a,ename b from emp;
-- 练习：查询出所有员工的姓名、生日、工资，使用别名
select ename '姓名',birthday '生日',salary '工资' from emp;

-- 4.显示不同的记录
-- 示例：查询出员工都分布在哪些编号的部门
select distinct deptid from emp;
-- 练习：查询出都有哪些性别的员工
select distinct sex from emp;

-- 5.查询时执行计算
-- 示例：计算2*3*4*5+6*7*8
select 2*3*4*5+6*7*8;
-- 练习：查询出所有员工的姓名和年薪
select ename,salary*12 from emp;
-- 练习：假设每个员工的工资增长2000，年终奖20000，查询出所有员工的姓名和年薪，使用别名
select ename,(salary+2000)*12+20000 '年薪' from emp;

-- 6.查询结果排序
-- 示例：查询出所有的部门，结果按照编号升序排列
select * from dept order by did asc;
-- 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;
-- 不加排序规则，默认是按照升序(asc)排列
-- 示例：查询出所有的员工，结果按照工资升序排列，如果工资相同按照姓名排列
select* from emp order by salary asc,ename asc;
-- 练习：查询出所有的员工，结果按照性别升序排列，如果性别相同按照年龄从小到大排列
select * from emp order by sex asc,birthday desc;

-- 7.条件查询
-- 示例：查询出编号为5的员工
select * from emp where eid=5;
-- 练习：查询出姓名为king的员工
select * from emp where ename='king';
-- 练习：查询出所有的女员工
select * from emp where sex=0;
-- 练习：查询出工资在8000以上的员工
select * from emp where salary>8000;
-- 比较运算符：>  <  >=  <=  =  !=
-- 练习：查询出没有明确部门的员工
select * from emp where deptid is null;
-- 练习：查询出有明确部门的员工
select * from emp where deptid is not null;
-- is null 查找某一列为null
-- is not null 查找某一列不为null

-- 示例：查询出工资在8000以下男员工
select * from emp where salary<8000 and sex=1;

-- 练习：查询出工资在8000~10000有哪些
select * from emp where salary>=8000 and salary<=10000;
-- 示例：查询出工资在8000以下或者10000以上的员工有哪些
select * from emp where salary<=8000 or salary>=10000;

-- 练习：查询出1993年出生的员工的员工有哪些(1993-1-1到1993-12-31)
select * from emp where birthday>='1993-1-1' or birthday<='1993-12-31';
-- 练习：查询出20部门或者30部门的员工
select * from emp where deptid in (20,30);

-- 练习：查询出不在20号部门，并且不在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个字符     =1



 -- (3)查询出工资在8000以上的女员工，结果按照工资的降序排列
 select * from emp where salary >=8000 and sex=0 order by salary desc;

4.2复杂查询

-- 简单查询：分页查询
/*
查询的结果中有太多的数据，一次显示不完可以做成分页
需要有两个已知的条件：当前的页码、每页显示的数据量
每页开始查询的值 = (当前的页码-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)开始查询的值不能写运算，只能写最终的值
	(2)开始查询的值和每页数据量，不能加引号
*/
-- 练习：查询出工资在5000以上的男员工中，工资最高的前3个人
select * from emp where salary>5000 && sex=1 order by salary desc limit 0,3;

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

-- 分组查询只能查询分组条件和聚合函数
-- 示例：查询出男女员工的数量，工资总和分别是多少
select count(*),sum(salary),sex from emp group by sex;
-- 练习：查询出各部门的员工的平均工资，最高工资，最低工资
select avg(salary),max(salary),min(salary),deptid from emp group by deptid;

-- year() 获取日期中的年份部分
-- 示例：查询出所有员工出生的年份
select year(birthday) from emp;
-- 练习：查询出1993年出生的员工
select * from emp where year(birthday)=1993;

-- 加密函数 md5()
select md5('123456'); -- e10adc3949ba59abbe56e057f20f883e

-- 2.子查询：是多个查询命令的组合，把一个的结果作为另一个的条件
-- 示例：查询出工资最高的员工
-- 步骤1：查询出工资的最高值 —— 50000
select max(salary) from emp;
-- 步骤2：查询工资最高值对应的员工
select * from emp where salary=50000;
-- 综合：
select * from emp where salary=(select max(salary) from emp);

-- 练习：查询出高于平均工资的员工有哪些
-- 步骤1：查询出平均工资
select avg(salary) from emp; -- 12823.529412
-- 步骤2：查询高于平均工资
select * from emp where salary>12823.529412;
-- 综合
select * from emp where salary>(select avg(salary) from emp);
-- 练习：查询出和xin同一个部门的员工
-- 步骤1：查询出xin的部门编号 ———— 20
select deptid from emp where ename='xin';
-- 步骤2：查询出部门编号下的员工
select * from emp where deptid=20 && ename!='xin';
-- 综合：
select * from emp where deptid=(select deptid from emp where ename='xin') && ename!='xin';

-- 练习：查询出和tom同一年出生的员工
-- 步骤1：tom出生的年份 ———— 1990
select year(birthday) from emp where ename='tom';
-- 步骤2：查询出这个年份下出生的员工
select * from emp where year(birthday)=1990 && ename!='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 ename,dname from emp inner join dept on deptid=did;

总结

day24-mysql03学习结束