课程回顾

day01

1. 数据库相关sql
2. 查看所有 show databases;
3. 创建 create database db1 character set utf8/gbk;
4. 查看详情 show create database db1;
5. 删除 drop database db1;
6. 使用 use db1;
7. 表相关sql
8. 创建 create table t1(id int,name varchar(10)) engine=myisam/innodb charset=utf8/gbk;
9. 查看所有 show tables;
10. 查看详情 show create table t1;
11. 表字段 desc t1;
12. 删除表 drop table t1;
13. 删除并创建新的 truncate table t1;
14. 修改表名 rename table t1 to t2;
15. 修改引擎字符集 alter table t1 engine=myisam/innodb charset=utf8/gbk;
16. 添加表字段 alter table t1 add age int first/ after xxx;
17. 删除表字段 alter table t1 drop age;
18. 修改字段名和类型 alter table t1 change 原名 新名 新类型;
19. 修改类型和位置 alter table t1 modify 字段名 类型 first/after xxx;
20. 数据相关
21. 插入数据： insert into t1 (name,age) values(值1,值2),(值1,值2)
22. 删除数据： delete from t1 where id=10;
23. 修改数据： update t1 set age=18,name='abc' where id=5;
24. 查询数据： select name,age from t1 where id<5;

day02

1. 主键约束+自增 唯一且非空 create table t1(id int primary key auto_increment)
2. 从历史最大值基础+1
3. 只增不减
4. 注释 comment create table t1(id int comment '注释内容');
5. `用于修饰表名和字段名可以省略 '修饰字符串
6. 冗余：重复数据 通过拆分表解决
7. 事务：数据库中执行同一业务多条sql语句的工作单元，可以保证多条sql全部成功或全部失败
8. 开启事务 begin
9. 回滚 rollback
10. 提交 commit
11. 保存回滚点 savepoint xxx
12. 回滚到某个回滚点 rollback to xxx;
13. sql分类
14. DDL：数据定义语言 包括：create alter drop truncate 不支持事务
15. DML：数据操作语言 包括：insert delete update select(DQL) 支持事务
16. DQL：数据查询语言 只包括：select
17. TCL：事务控制语言 包括事务那些指令
18. DCL：数据控制语言 分配用户权限相关的sql
19. 数据类型
20. 整数： int(m) bigint(m) m代表显示长度 需要结合zerofill使用
21. 浮点数：double(m,d) m代表总长度 d是小数长度 decimal(m,d) 超高精度浮点数
22. 字符串：char(m)不可变长度 最大255 执行效率略高 varchar(m)可变长度最大65535超过255建议使用text text(m)可变长度 最大65535
23. 日期：date 年月日 time 时分秒 datetime:默认null 最大9999-12-31 timestamp：默认当前时间 最大2038-1-19

导入*.sql数据到数据库中

• window系统
• 把*.sql 文件保存到 d盘的根目录
• 在命令行中执行 source d:/tables.sql
• linux系统
• 把*.sql 文件保存到桌面
• 在命令行中执行 source /home/soft01/桌面/tables.sql

is null 和 is not null

1. 查询奖金为null的员工姓名、工资、奖金 select ename,sal,comm from emp where comm is null;
2. 查询没有上级领导mgr的员工信息(所有信息) select * from emp where mgr is null;
3. 有上级领导的员工姓名、工资和领导编号 select ename,sal,mgr from emp where mgr is not null;
4. 查询有奖金的员工信息 select * from emp where comm is not null and comm>0;

别名

1. 查询所有员工的姓名 select ename as '姓名' from emp; select ename '姓名' from emp; select ename 姓名 from emp;
2. 查询工资大于2000的员工信息要求每个字段的名称改成中文 select empno 编号,ename 姓名,job 工作,mgr 领导编号,hiredate 入职日期,sal 工资,comm 奖金,deptno 部门编号 from emp where sal>2000;

去重 distinct

1. 查询员工从事的所有职位 select distinct job from emp;
2. 查询员工所属的部门编号有哪几个 select distinct deptno from emp;

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

1. 查询工资小于1600的所有员工的姓名和工资 select ename,sal from emp where sal<1600;
2. 查询部门编号为20的所有员工的姓名，职位，和部门编号 select ename,job,deptno from emp where deptno=20;
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. 查询商品表titem中单价price 等于23的商品信息 select * from titem where price=23;
6. 查询商品表中单价不等于8443的商品标题和价格 select title,price from t_item where price!=8443;

and和or

• and 和java 中的&&效果类似
• or 和java 中的||效果类似
• 查询不是10号部门并且工资小于3000的员工信息 select * from emp where deptno!=10 and sal<3000;
• 查询有上级领导并且工资低于2000的员工信息 select * from emp where mgr is not null and sal<2000;
• 查询部门编号为30或者上级领导为7698的员工姓名、工资、部门编号、上级领导编号 select ename,sal,deptno,mgr from emp where deptno=30 or mgr=7698;
• 查询没有上级领导或者工资小于1000的员工信息 select * from emp where mgr is null or sal<1000;

in

• 当查询某个字段的值为多个值的时候使用
• 查询员工工资为5000，800，950的员工信息 select * from emp where sal=5000 or sal=800 or sal=950; -使用in select * from emp where sal in(5000,800,950);
• 查询员工工资不为5000，800，950的员工信息 select * from emp where sal not in(5000,800,950);

between x and y

• 查询某个字段的数值在 x和y之间 包含x和y
• 查询工资在1000到2000之间的员工信息 select * from emp where sal>=1000 and sal<=2000; select * from emp where sal between 1000 and 2000;
• 查询商品表中，单价price在50到100之间的商品价格 select price from t_item where price between 50 and 100;
• 查询工资在1000到4000之外的员工姓名和工资 select ename,sal from emp where sal<1000 or sal>4000; select ename,sal from emp where sal not between 1000 and 4000;

模糊查询 like

• _:代表单个未知字符
• %：代表0或多个未知字符
• 举例：
  1. 以a开头 a%
  2. 以b结尾 %b
  3. 以x开头y结尾 x%y
  4. 包含abc %abc%
  5. 第二个字母是b _b%
  6. 第一个字母是m 倒数第三个是d m%d__
• 使用方式：
• 查询名字以k开头的员工姓名和工资 select ename,sal from emp where ename like 'k%';
• 查询员工表中，职位名称包含s的职位名称 select distinct job from emp where job like '%s%';
• 查询员工姓名中第三个字母是n的员工信息 select * from emp where ename like '__n%';

• 查询名字倒数第二个字母是e的员工姓名 select ename from emp where ename like '%e_';

• 查询商品表中标题包含记事本的商品信息 select title from t_item where title like '%记事本%';

• 查询有赠品的dell商品(有赠品：卖点字段中包含赠字, dell商品：标题字段中包含dell) select * from titem where sellpoint like '%赠%' and title like '%dell%';
• 查询价格在50到200之间的得力商品 select * from t_item where price between 50 and 200 and title like '%得力%';
• 查询有图片的得力商品 (有图片：图片字段不为null) select * from t_item where image is not null and title like '%得力%';
• 查询商品标题中不包含得力的商品信息 select * from t_item where title not like '%得力%';
• 查询商品分类为238，917并且商品标题中不包含广博的商品信息 select * from titem where categoryid in (238,917) and title not like '%广博%';

排序 order by 字段名

• 字段名后面 什么都不加默认为升序 添加 desc为降序 asc升序
• 查询所有员工姓名 按照名字排序 select ename from emp order by ename;
• 查询所有员工的姓名，工资 按照工资进行升序排序 select ename,sal from emp order by sal ;
• 查询所有员工的姓名，工资 按照工资进行降序排序 select ename,sal from emp order by sal desc;
• 查询员工表中有上级领导并且名字中包含a的员工姓名，工资，上级领导编号，按照工资降序排序 select ename,sal,mgr from emp where mgr is not null and ename like '%a%' order by sal desc;
• 多字段排序，如果需要多个字段排序则在order by 后面写多个字段名通过逗号分隔即可
• 查询所有员工的姓名，工资，部门编号 按照部门编号升序排序,如果部门一致则按照工资降序排序 select ename,sal,deptno from emp order by deptno,sal desc;

分页查询

• 格式: limit 跳过的条数,请求的条数/每页的条数
• 请求前20条 limit 0,20
• 请求第三页每页8条 limit 16,8 //(3-1)*8,8

• 请求第5页每页4条 limit 16,4 //(5-1)*4,4

• 查询商品表中的前20条数据 select * from t_item limit 0,20;

• 查询员工表第三页每页两条数据 select * from empt limit 4,2;
• 查询工资前三名的员工姓名和工资 select ename,sal from emp order by sal desc limit 0,3;
• 查询奖金最高员工信息 select * from emp order by comm desc limit 0,1;

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

1. 查询每个员工的姓名，工资，年终奖(年终奖=工资5) select ename,sal,sal5 年终奖 from emp;
2. 查询商品表中商品单价，库存和总金额(单价库存) select price,num,pricenum 总金额 from t_item;
3. 给20号部门的员工涨薪10块钱 update emp set sal=sal+10 where deptno=20;

日期相关的函数

• sql的helloWord select 'helloword';
• 获取当前的日期+时间 now() select now();
• 获取当前的年月日 curdate(); current当前 select curdate();
• 获取当前的时间 curtime(); select curtime();
• 从完整年月日时分秒中提取年月日 和提取时分秒 select date(now()),time(now());
• 从完整年月日时分秒中提取时间分量 extract() 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) year from emp;

• 日期格式化 date_format(日期,格式)

• format：
• %Y 四位年 %y 两位年
• %m 两位月 %c 一位月
• %d 日
• %H 24小时 %h 12小时
• %i 分
• %s 秒
• 练习： 把now()转换成 2019年01月16号 15点45分22秒 select date_format(now(),'%Y年%m月%d号 %H点%i分%s秒');
• 把自定义时间格式转回标准格式 strtodate(自定义的字符串时间,格式)
• 练习： 14.08.2008 08:00:00

select strtodate('14.08.2008 08:00:00','%d.%m.%Y %H:%i:%s');

ifnull(x,y)函数

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

聚合函数

• 对多行数据进行统计，可以统计平均值，最大值，最小值，求和，统计数量
• 平均值 avg(字段名)
• 统计20号部门的平均工资 select avg(sal) from emp where deptno=20;
• 查询得力商品的平均单价 select avg(price) from t_item where title like '%得力%';
• 最大值 max(字段名)
• 统计所有员工中的最高奖金 select max(comm) from emp;
• 查询30号部门的最高工资 select max(sal) from emp where deptno=30;
• 最小值 min(字段名)
• 统计10号部门的最低工资 select min(sal) from emp where deptno=10;
• 查询商品表中的最低商品单价 select min(price) from t_item;
• 求和 sum(字段名)
• 统计20号部门每个月共发多少工资 select sum(sal) from emp where deptno=20;
• 统计员工表中所有员工的奖金总和 select sum(comm) from emp;
• 统计数量 count(字段名)
• 统计emp表中有多少员工 select count(*) from emp;
• 查询30号部门工资大于1500的员工数量 select count(*) from emp where deptno=30 and sal>1500;

字符串相关的函数

1. 字符串拼接 concat(s1,s2) s1s2
2. 案例：查询员工姓名和工资 要求在工资的后面添加单位元 select ename,concat(sal,'元') from emp;
3. 获取字符串的长度 charlength(str) select charlength('abc');
4. 案例： 查询每个员工的姓名和员工姓名的长度 select ename,char_length(ename) from emp;
5. 获取字符串在另外一个字符串中出现的位置
   -格式一： instr(str,substr) select instr('abcdefg','d'); -格式二： locate(substr,str); select locate('d','abcdefg');
6. 插入字符串 insert(str,start,length,newstr) select insert('abcdefg',3,2,'m');
7. 转大写 转小写 select upper('nba'),lower('ABC');
8. 去两端空白 trim select trim(' a b ');
9. 截取字符串
   • 从左边截取 select left('abcdefg',2);
   • 从右边截取 select right('abcdefg',2);
   • 任意位置截取 select substring('abcdefg',2); //从2截取到最后 select substring('abcdefg',2,3);//从2开始 长度为3
10. 重复 repeat(str,count) select repeat('ab',2);
11. 替换 replace(str,old,new) select replace('abcefg','e','mm');
12. 反转 reverse(str) select reverse('abc');

课程回顾

1. 导入sql文件 source 文件路径;
2. is null 和 is not null
3. 别名
4. 去重 distinct
5. 运算符 > < >= <= = !=和<>
6. and和or
7. in 和 not in
8. between x and y 包含x和y not between x and y
9. like 模糊查询 _单个未知字符 %0或多个未知
10. 排序 order by 字段1 asc/desc,字段2
11. 分页 limit 跳过的条数,请求的条数/每页的条数
12. 数值计算 + - * / % 和 mod()
13. 日期： now() curdate() curtime() date(now()) time(now()) extract(year/month/day/hour/minute/second from now()) ,日期格式化 dateformat(日期,格式) %YymcdHhis ，strto_date(非标准日期,格式)
14. ifnull(x,y) 如果x为null则取y 不为null则取x
15. 聚合函数： 平均值avg 最大值 max 最小值 min 求和 sum 计数count
16. 字符串相关
17. concat()
18. char_length()
19. instr(str,substr) locate(substr,str)
20. upper() lower()
21. trim()
22. left() right() substring
23. repeat()
24. replace()
25. reverse()

练习

1.案例：查询没有上级领导的员工的编号，姓名，工资
2.案例：查询emp表中没有奖金的员工的姓名，职位，工资，以及奖金
3.案例：查询emp表中含有奖金的员工的编号，姓名，职位，以及奖金
4.案例：查询含有上级领导的员工的姓名，工资以及上级领导的编号
5.案例：查询emp表中名字以‘S’开头的所有员工的姓名
6.案例：查询emp表中名字的最后一个字符是'S'的员工的姓名
7.案例：查询倒数的第2个字符是‘E’的员工的姓名
8.案例：查询emp表中员工的倒数第3个字符是‘N’的员工姓名
9.案例：查询emp表中员工的名字中包含‘A’的员工的姓名   
10.案例：查询emp表中名字不是以'K'开头的员工的所有信息
11.案例：查询emp表中名字中不包含‘A’的所有员工的信息
12.案例：做文员的员工人数（job_id 中 含有 CLERK 的）
13.案例：销售人员 job: SALESMAN 的最高薪水
14.案例：最早和最晚入职时间
15.案例：查询类别 163的商品总库存量
16.案例：查询 类别 163 的商品
17.案例：查询商品价格不大于100的商品名称列表
18.案例：查询品牌是联想,且价格在40000以上的商品名称和价格
19.案例：查询品牌是三木,或价格在50以下的商品名称和价格
20.案例：查询品牌是三木、广博、齐心的商品名称和价格
21.案例：查询品牌不是联想、戴尔的商品名称和价格
22.案例：查找品牌是联想且价格大于10000的电脑名称
23.案例：查询联想或戴尔的电脑名称列表
24.案例：查询联想、戴尔、三木的商品名称列表
25.案例：查询不是戴尔的电脑名称列表
26.案例：查询所有是记事本的商品品牌、名称和价格
27.案例：查询品牌是末尾字符是'力'的商品的品牌、名称和价格
28.案例：名称中有联想字样的商品名称
29.案例：查询卖点含有'赠'产品名称
30.案例：查询emp表中员工的编号，姓名，职位，工资，并且工资在1000~2000之间。




31.案例：查询emp表中员工在10号部门，并且含有上级领导的员工的姓名，职位，上级领导编号以及所属部门的编号
32.案例：查询emp表中名字中包含'E'，并且职位不是MANAGER的员工的编号，姓名，职位，以及工资。   
33.案例：查询emp表中10号部门或者20号部门中员工的编号，姓名，所属部门的编号
34.案例：查询emp表中没有奖金或者名字的倒数第2个字母不是T的员工的编号，姓名，职位以及奖金
35.案例：查询工资高于3000或者部门编号是30的员工的姓名，职位，工资，入职时间以及所属部门的编号 
36.案例：查询不是30号部门的员工的所有信息
37.案例：查询奖金不为空的员工的所有信息
38.案例：查询emp表中所有员工的编号，姓名，职位，根据员工的编号进行降序排列
39.案例：查询emp表中部门编号是10号或者30号中，所有员工姓名，职务，工资，根据工资进行升序排列
40.案例：查询emp表中所有的数据，然后根据部门的编号进行升序排列，如果部门编号一致，根据员工的编号进行降序排列
41.案例：查询emp表中工资高于1000或者没有上级领导的员工的编号，姓名，工资，所属部门的编号，以及上级领导的编号，根据部门编号进行降序排列，如果部门编号一致根据工资进行升序排列。
42.案例：查询emp表中名字中不包含S的员工的编号，姓名，工资，奖金，根据工资进行升序排列，如果工资一致，根据编号进行降序排列
43.案例：统计emp表中员工的总数量
44.案例：统计emp表中获得奖金的员工的数量
45.案例：求出emp表中所有的工资累加之和
46.案例：求出emp表中所有的奖金累加之和
47.案例：求出emp表中员工的平均工资
48.案例：求出emp表中员工的平均奖金
49.案例：求出emp表中员工的最高工资
50.案例：求出emp表中员工编号的最大值
51.案例：查询emp表中员工的最低工资。
52.案例：查询emp表中员工的人数，工资的总和，平均工资，奖金的最大值，奖金的最小值,并且对返回的列起别名。
53.案例：查询emp表中每个部门的编号，人数，工资总和，最后根据人数进行升序排列，如果人数一致，根据工资总和降序排列。
54.案例：查询工资在1000~3000之间的员工信息，每个部门的编号，平均工资，最低工资，最高工资，根据平均工资进行升序排列。
55.案例：查询含有上级领导的员工，每个职业的人数，工资的总和，平均工资，最低工资，最后根据人数进行降序排列，如果人数一致，根据平均工资进行升序排列
56.案例：查询工资在1000~3000之间每一个员工的编号，姓名，职位，工资
57.案例：查询emp表中奖金在500~2000之间所有员工的编号，姓名，工资以及奖金
58.案例：查询员工的编号是7369，7521，    
59.案例：查询emp表中，职位是ANALYST，
60.案例：查询emp表中职位不是ANALYST,