学习数据库的第三天

学习回顾

day02

1. 主键约束： 唯一且非空 primary key
2. 自增: auto_increment 历史最大值+1 只增不减
3. 注释 comment
4. `用于修饰表名和字段名 可以不写
5. 冗余： 重复数据，通过拆分表解决
6. 事务：数据库中执行同一业务多条SQL语句的工作单元，保证多条SQL全部执行成功或全部执行失败

• 开启事务begin 提交commit 回滚 rollback 保存回滚点savepoint xxxx 回滚到回滚点 rollback to xxx;

7. SQL分类

• DDL：数据定义语言 包括：create drop alter truncate 不支持事务
• DML：数据操作语言 包括：insert delete update select 支持事务
• DQL：数据查询语言 包括： select
• TCL：事务控制语言 包括：开启事务begin 提交commit 回滚 rollback 保存回滚点savepoint xxxx 回滚到回滚点 rollback to xxx;
• DCL：数据控制语言 分配用户权限相关的SQL

8. 数据类型

• 整数： int(m)和 bigint(m) m代表显示长度 需要结合zerofill
• 浮点数： double(m,d) m总长度 d小数长度 超高精度浮点数 decimal（m,d）
• 字符串：char不可变长度最大255，varchar可变长度 最大65535超过255建议使用text，text可变长度最大65535
• 日期： date年月日 time时分秒 datetime：默认null 最大9999-12-31， timestamp：默认当前系统时间 最大2038-1-19
• 其它

9. is null 和 is not null
10. 别名
11. 导入sql文件
    source 路径;

去重 distinct

1. 查询员工表中出现的部门编号有哪些？
   select distinct deptno from emp;
2. 查询员工表中的工作有哪些？
   select distinct job from emp;

比较运算符 > < >= <= = !=和<>

1. 查询员工表中部门编号大于10 并且工资在2000以内的员工姓名，工资，部门编号
   select ename,sal,deptno from emp where deptno>10 and sal<2000;
2. 查询员工工资小于等于1600的员工姓名，职位和工资
   select ename,job,sal from emp where sal<=1600;
3. 查询工作是manager的员工姓名，工资和工作
   select ename,sal,job from emp where job=‘manager’;
4. 查询不是10号部门的员工姓名，部门编号（两种写法）
   select ename,deptno from emp where deptno!=10;
   select ename,deptno from emp where deptno<>10;
5. 查询单价为23的商品信息
   select * from t_item where price=23;
   select * from t_item where price=23 \G;
6. 查询单价不等于8443的商品标题和单价
   select title,price from t_item where price!=8443;

and 和 or

• 当查询数据时多个条件需要全部满足时使用 and
• 当查询数据时多个条件满足一个就可以的时候使用 or

1. 查询员工表中10号部门工资低于2000的员工信息
   select * from emp where deptno=10 and sal<2000;
2. 查询查询30号部门或工资大于3000的员工信息
   select * from emp where deptno=30 or sal>3000;

模糊查询 like

• %:代表的是0或多个未知字符
• _:代表的是1个未知字符
  以a开头 a% 以m结尾 %m
  第一个字符是x 最后一个是y x%y
  包含x %x%
  第三个字符时x 倒数第二个字符是y _x%y

1. 查询名字包含a的员工姓名
   select ename from emp where ename like ‘%a%’;
2. 查询第二个字符是l的员工姓名和工资
   select ename,sal from emp where ename like ‘_l%’;
3. 查询t_item表中，标题title中包含记事本的商品标题
   select title from t_item where title like ‘%记事本%’;
4. 查询职位job中包含an并且工资高于1500的员工姓名、工资、职位
   select ename,sal,job from emp where job like ‘%an%’ and sal>1500;
5. 查询有赠品的dell商品详情(卖点sell_point中包含“赠”字,标题中包含dell)
   select * from t_item where sell_point like ‘%赠%’ and title like ‘%dell%’;
6. 查询单价低于100的笔记本信息
   select * from t_item where price<100 and title like ‘%笔记本%’;
7. 查询有图片的得力商品信息（image字段不为null）
   select * from t_item where image is not null and title like ‘%得力%’;
8. 查询员工姓名不包含a的员工姓名、工资和部门编号
   select ename,sal,deptno from emp where ename not like ‘%a%’;

在两者之间 between x and y

1. 查询工资在2000到3000之间的员工姓名和工资
   select ename,sal from emp where sal>=2000 and sal<=3000;
   select ename,sal from emp where sal between 2000 and 3000;
2. 查询单价在50到100之间的商品标题和单价
   select title,price from t_item where price between 50 and 100;
3. 查询工资在1000到2000之外的员工姓名和工资
   select ename,sal from emp where sal not between 1000 and 2000;

in

1. 查询员工工资为800，1300，1500的员工信息
   select * from emp where sal=800 or sal=1300 or sal=1500;

   select * from emp where sal in(800,1300,1500);

2. 查询商品价格为56，58，89的商品标题和单价
   select title,price from t_item where price in(56,58,89);

3. 查询工资不是3000，5000，1500的员工姓名和工资
   select ename,sal from emp where sal not in(3000,5000,1500);

综合练习

1. 查询分类id为238，917的商品信息
   select * from t_item where category_id in(238,917);
2. 查询价格在50-200之间的得力商品
   select * from t_item where price between 50 and 200 and title like ‘%得力%’;
3. 查询有上级领导并且工资小于2000的员工姓名，工资和领导编号
   select ename,sal,mgr from emp where mgr is not null and sal<2000;
4. 查询有奖金并且有上级领导的员工信息
   select * from emp where comm>0 and mgr is not null;
5. 查询名字中包含a并且工资在3000以内的员工从事的工作有哪几种
   select distinct job from emp where ename like ‘%a%’ and sal<3000;

排序 order by

• 格式： order by 字段名 asc/desc;

1. 查询员工姓名和工资 按照工资降序排序
   select ename,sal from emp order by sal desc;

2. 查询有奖金的员工姓名、工资、奖金 按照奖金降序排序
   select ename,sal,comm from emp where comm>0 order by comm desc;

3. 查询单价低于100的商品标题和单价并按照单价升序排序
   select title,price from t_item where price<100 order by price;

4. 查询每个员工的姓名、工资、部门编号 按照部门编号降序排序,如果部门编号相同则按钮工资降序排序
   select ename,sal,deptno from emp order by deptno desc,sal desc;

5. 查询价格在200以内商品名称，单价，分类id 按照分类id降序排序，如果分类id相同则按照单价升序排序

课程回顾

1. 去重 distinct
2. 比较运算符 > < >= <= = !=和<>
3. and 和 or
4. 模糊查询 like _单个未知 %0或多个未知
5. 排序 order by 字段 asc/desc, 字段

分页查询 limit

• 格式: limit 跳过的条数,请求条数
• 请求 前5条数据 limit 0,5
• 第三页的4条数据 limit 8,4
• 第10的7条数据 limit 63,7
• 第5页的四条 limit 16,4
• 第9页的8条 limit （页数-1）*条数,条数

1. 查询员工表工资降序前5条数据
   select * from emp order by sal desc limit 0,5;
2. 查询商品标题和单价 按照单价升序排序 第三页的5条数据
   select title,price from t_item order by price limit 10,5;
3. 查询30号部门中工资最高的三个员工的信息
   select * from emp where deptno=30 order by sal desc limit 0,3;

数值计算 + - * / 7%2 = mod(7,2)

1. 查询员工姓名，工资和年终奖(工资5个月)
   select ename,sal,sal5 年终奖 from emp;
2. 查询商品标题，单价，库存和总价值（库存单价）
   select title,price,num,numprice 总价值 from t_item;
3. 查询每个员工涨薪5块钱之后的工资
   select ename,sal,sal+5 from emp;

日期相关

1. SQL的helloworld
   select “helloworld”;

2. 获取当前系统时间 now()
   select now();

   create table t_date(name varchar(10),created_time datetime);

   insert into t_date values(‘刘德华’,now());

3. 获取当前年月日 curdate() cur=current
   select curdate();

4. 获取当前时分秒 curtime()
   select curtime();

5. 从年月日时分秒中提取年月日和提取时分秒
   select date(now()),time(now());

• 查询每个商品的创建时间(年月日)
  select date(created_time) from t_item;
  select time(created_time) from t_item;

6. 从年月日时分秒中提取时间分量
   select extract(year from now());
   select extract(month from now());
   select extract(day from now());
   select extract(hour from now());
   select extract(minute from now());
   select extract(second from now());

• 查询每个员工的姓名和入职年份
  select ename,extract(year from hiredate) from emp;
• 查询员工表中有员工入职的年份
  select distinct extract(year from hiredate) from emp;

7. 日期格式化 date_format()

• 格式： date_format(时间,格式);
• %Y 4位年 %y 2位年
• %m 2位月 05 %c 1位月 5
• %d 日
• %H 24小时 %h 12小时
• %i 分
• %s 秒
• 把now() 转成 2019年4月19号 15点15分20秒
  select date_format(now(),’%Y年%c月%d号 %H点%i分%s秒’);

8. 把非标准时间格式转成标准时间格式 str_to_date()

• 格式： str_to_date(非标准字符串时间,格式)
• 把 19.04.2019 15:15:20 转成标准时间
  select str_to_date(‘19.04.2019 15:15:20’,’%d.%m.%Y %H:%i:%s’);

ifnull()

• age = ifnull(x,y); 如果x值为null则age=y 如果不为null则age=x
• 把员工表中奖金为null的改成0 不为null的不变
  update emp set comm=ifnull(comm,0);

聚合函数

• 对多条数据进行统计查询，统计平均值、最大值、最小值、求和、计数

1. 平均值 avg(字段名)
   查询员工表中20号部门的平均工资
   select avg(sal) from emp where deptno=20;
   查询员工表的平均奖金
   select avg(comm) from emp;
2. 最大值 max(字段名)
3. 最小值 min(字段名)、
   查询30号部门的最高工资和最低工资
   select max(sal),min(sal) from emp where deptno=30;
4. 求和sum(字段名)
   查询所有员工每月发出的总工资
   select sum(sal) from emp;
   把低于100块钱的商品全都买了花多少钱
   select sum(price) from t_item where price<100;
5. 统计数量 count(字段名)
   查询员工表的人数
   select count() from emp;
   查询有奖金的员工数量
   select count() from emp where comm>0;

字符串相关函数

1. 字符串拼接 concat(s1,s2)
   select concat(‘aaa’,‘bbb’);
   查询每个员工的姓名和工资 工资以元为单位
   select ename,concat(sal,‘元’) from emp;
2. 获取字符串长度 char_length()
   查询每个员工的姓名和名字长度
   select ename,char_length(ename) from emp;
3. 获取字符串出现的位置
   • 格式： instr(str,substr)
     select instr(‘abcdefg’,‘d’);
4. 转大写和转小写
   select upper(‘nba’),lower(‘NBa’);
5. 截取字符串
   • 左边截取
     select left(‘abcdefg’,2);
   • 右边截取
     select right(‘abcdefg’,2);
   • 自由截取
     select substring(‘abcdefg’,2,3);
6. 去空白 trim
   select trim(’ a b ');
7. 重复 repeat
   select repeat(‘ab’,2);
8. 替换 replace(str,old,new)
   select replace(‘abcd abc’,‘b’,‘m’);
9. 反转 reverse(str)
   select reverse(‘abc’);

案例：查询没有上级领导的员工的编号，姓名，工资
select empno,ename,sal from emp where mgr is null;
案例：查询emp表中没有奖金的员工的姓名，职位，工资，以及奖金
select ename,job,sal,comm from emp where comm=0 or comm is null;
案例：查询emp表中含有奖金的员工的编号，姓名，职位，以及奖金
select empno,ename,job,comm from emp where comm>0;
案例：查询含有上级领导的员工的姓名，工资以及上级领导的编号
select ename,sal,mgr from emp where mgr is not null;
案例：查询emp表中名字以‘S’开头的所有员工的姓名
select ename from emp where ename like ‘s%’;
案例：查询emp表中名字的最后一个字符是’S’的员工的姓名
select ename from emp where ename like ‘%s’;
案例：查询倒数的第2个字符是‘E’的员工的姓名
select ename from emp where ename like ‘%e_’;
案例：查询emp表中员工的倒数第3个字符是‘N’的员工姓名
select ename from emp where ename like ‘%n__’;
案例：查询emp表中员工的名字中包含‘A’的员工的姓名
select ename from emp where ename like ‘%a%’;
案例：查询emp表中名字不是以’K’开头的员工的所有信息
select ename from emp where ename not like ‘k%’;
案例：查询emp表中名字中不包含‘A’的所有员工的信息
select ename from emp where ename not like ‘%a%’;
案例：做文员的员工人数（job 中 含有 CLERK 的）
select count(*) from emp where job=‘clerk’;
案例：销售人员 job: SALESMAN 的最高薪水
select max(sal) from emp where job=‘salesman’;
案例：最早和最晚入职时间
select min(hiredate),max(hiredate) from emp;
案例：查询类别 163的商品总库存量
select sum(num) from t_item where category_id=163;
案例：查询 类别 163 的商品
select * from t_item where category_id=163;
案例：查询商品价格不大于100的商品名称列表
select title from t_item where price<=100;
案例：查询品牌是联想,且价格在40000以上的商品名称和价格
select title,price from t_item where title like ‘%联想%’ and price>40000;
案例：查询品牌是三木,或价格在50以下的商品名称和价格
select title,price from t_item where title like ‘%三目%’ or price<50;
案例：查询品牌是三木、广博、齐心的商品名称和价格
select title,price from t_item where title like ‘%三目%’ or title like ‘%广博%’ or title like ‘%齐心%’;
案例：查询品牌不是联想、戴尔的商品名称和价格
select title,price from t_item where title not like ‘%联想%’ or title not like ‘%戴尔%’;
案例：查找品牌是联想且价格大于10000的名称
select title from t_item where title like ‘%联想%’ and price>10000；
案例：查询联想或戴尔的电脑名称列表
select title from t_item where title like ‘%联想%’ or title like ‘%戴尔%’;
案例：查询emp表中员工的编号，姓名，职位，工资，并且工资在1000~2000之间。
select empno,ename,job,sal from emp where sal between 1000 and 2000;
案例：查询emp表中员工在10号部门，并且含有上级领导的员工的姓名，职位，上级领导编号以及所属部门的编号
select ename,job,mgr,deptno from emp where deptno=10 and mgr is not null;
案例：查询emp表中名字中包含’E’，并且职位不是MANAGER的员工的编号，姓名，职位，以及工资。
select empno,ename,job,sal from emp where ename like ‘%e%’ and job!=‘manager’;
案例：查询emp表中10号部门或者20号部门中员工的编号，姓名，所属部门的编号
select empno,ename,deptno from emp where deptno in(10,20);
案例：查询emp表中没有奖金或者名字的倒数第2个字母不是T的员工的编号，姓名，职位以及奖金
select empno,ename,job,comm from emp where comm=0 or ename not like ‘%t_’;
案例：查询工资高于3000或者部门编号是30的员工的姓名，职位，工资，入职时间以及所属部门的编号
select ename,job,sal,hiredate,deptno from emp where sal>3000 or deptno=30;
案例：查询不是30号部门的员工的所有信息
案例：查询奖金不为空的员工的所有信息
案例：查询emp表中所有员工的编号，姓名，职位，根据员工的编号进行降序排列
案例：查询emp表中部门编号是10号或者30号中，所有员工姓名，职务，工资，根据工资进行升序排列
案例：查询emp表中所有的数据，然后根据部门的编号进行升序排列，如果部门编号一致，根据员工的编号进行降序排列
案例：查询emp表中工资高于1000或者没有上级领导的员工的编号，姓名，工资，所属部门的编号，以及上级领导的编号，根据部门编号进行降序排列，如果部门编号一致根据工资进行升序排列。
案例：查询emp表中名字中不包含S的员工的编号，姓名，工资，奖金，根据工资进行升序排列，如果工资一致，根据编号进行降序排列
案例：统计emp表中员工的总数量
案例：统计emp表中获得奖金的员工的数量
案例：求出emp表中所有的工资累加之和
案例：求出emp表中所有的奖金累加之和
案例：求出emp表中员工的平均工资
案例：求出emp表中员工的平均奖金
案例：求出emp表中员工的最高工资
案例：求出emp表中员工编号的最大值
案例：查询emp表中员工的最低工资。
案例：查询emp表中员工的人数，工资的总和，平均工资，奖金的最大值，奖金的最小值,并且对返回的列起别名。