###上次总结：
####数据库相关SQL
1. 查询所有 show databases;
2. 创建 create database db1 character set utf8/gbk;
3. 查看详情 show create database db1;
4. 删除 drop database db1;
5. 使用 use db1;
####表相关SQL
1. 查询所有 show tables;
2. 创建表 create table t1(name varchar(10),age int);
3. 查看详情 show create table t1;
4. 字段信息 desc t1;
5. 删除表 drop table t1;
6. 删除并创建新的 truncate table t1;
7. 修改表名 rename table t1 to t2;
8. 改引擎和字符集 alter table t1 engine=myisam/innodb charset=utf8/gbk;
9. 添加字段 alter table t1 add age int first/after xxx;
10. 删除字段 alter table t1 drop age;
11. 修改名称和类型 alter table t1 change age newAge int;
12. 修改类型和位置 alter table t1 modify age int first/after xxx;
####数据相关
1. 插入数据 insert into t1 (name,age) values(值1,值2),(值1,值2);
2. 查询数据 select name,age from t1 where age<30;
3. 修改数据 update t1 set age=18, name='aaa' where id=3;
4. 删除数据 delete from t1 where id=10;
####day02
1. 主键约束+自增 唯一且非空 primary key auto_increment
2. 注释： comment
3. '和`
4. 冗余：重复数据 通过拆分表解决
5. 事务：数据库中执行sql语句的最小工作单元，保证里面的多条SQL语句全部成功或全部失败 show variables like '%autocommit%'; set autocommit=0/1; 提交commit 回滚rollback 回滚点 savepoint s1; rollback to s1;
6. SQL分类
- DDL 数据定义语言 包括：create alter drop truncate 不支持事务
- DML 数据操作语言 包括：insert delete update select(DQL) 支持事务
- DQL 数据查询语言 包括：select
- TCL 事务控制语言 包括：commit rollback savepoint 。。。
- DCL 数据控制欲要 分配用户权限相关SQL
7. 数据类型
- 整数： int(m) 和bigint m代表显示长度 需要结合zerofill使用
- 浮点数：double(m,d) m代表总长度 d代表小数长度 decimal超高精度浮点数
- 字符串：char固定长度 执行效率高 最大255 varchar可变长度 节省空间 最大65535超高255建议使用text
- 日期： date保存年月日 time保存时分秒 datetime：9999-12-31 默认为null timestamp：最大2038-1-19 默认值为当前时间
###is null 和 is not null
CREATE TABLE `emp` (
`id` int(11) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`salary` int(11) DEFAULT NULL,
`dept` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into emp values(1,'刘备',25,4000,'三国部'),
(2,'关羽',24,8000,'三国部'),
(3,'张飞',22,3000,'三国部'),
(4,'悟空',250,5000,'取经部'),
(5,'八戒',350,6000,'取经部'),
(6,'沙僧',450,7000,'取经部'),
(7,'唐僧',50,8000,'取经部');
1. 查询emp表中id为空的，姓名name，薪水salary ,工资salary
select name,salary from emp where id is null;
3. 查询所有id不为空的所有员工信息
select * from emp where id is not null;
###别名
1. 将查询到的员工姓名ename改成‘姓名’
select name from emp;
select name as '姓名' from emp;
select name '姓名' from emp;
select name '姓名',salary '工资' from emp; ###去重 distinct
1. 查询emp表中出现的所有部门dept
select distinct dept from emp;
###比较运算符 >,<,>=,<=,=,!=和<>
1. 查询工资小于等于1600的所有员工的姓名和工资
select name,salary from emp where salary<=1600;
2. 查询部门编号是取经部的所有员工姓名、职位和部门编号deptno
select ename,job,deptno from emp where deptno='取经部';
3. 查询部门不是取经部门的所有员工信息(两种写法)
select * from emp where dept!='取经部';
select * from emp where dept<>'取经部';
###and 和 or
- and等效java中的&&
- or等效java中的||
- 查询年龄大于50岁并且在取经部的员工信息
select * from emp where age>50 and dept='取经部';
2. 查询年龄大于24，或者在取经部的
select * from emp where age>24 or dept='取经部';
###in
1. 查询emp表中年龄在22,25,50的员工信息
select * from emp where age =22 or age=25 or age=50;
select * from emp where age in(22,25,50);
###between x and y 包含x和y
1. 查询年龄在22到50之间的员工姓名和工资
select * from emp where age>=22 and age<=50;
select * from emp where age between 22 and 50;
###模糊查询 like
- _:代表单个未知字符
- %：代表0或多个未知字符
- 举例：
1. 以a开头的字符串 a%
2. 以m结尾 %m
3. 包含x %x%
4. 第二个字符是a _a%
5. 倒数第三个字母是m %m__
6. 以a开头并且倒数第二个字母是b a%b_
- 案例：
update emp set name='liubei' where name='刘备';
update emp set name='zhangfei' where name='张飞';
update emp set name='guanyu' where name='关羽';
1. 查询名字中包含a的所有员
select * from emp where name like '%a%';
2. 查询名字中倒数第三个字母是f的员工信息
select * from emp where name like '%f__';
###排序 order by
- order by 关键字， by后面写排序的字段名称 默认是升序(asc)，
- asc升序 desc降序
1. 查询所有员工的姓名和工资按照年龄升序/降序排序
select * from emp order by age asc;
select * from emp order by age desc;
###分页查询 limit
- limit 跳过的条数,请求的数量（每页的数量）
1. 查询员工表中年龄降序的前5条数据
select * from emp order by age desc limit 0,2;
2. 查询员工表中年龄降序的第3页的4条数据
select * from emp order by age desc limit 1,4;
3. 查询商品表中年龄升序的前10条数据
select * from emp order by age limit 0,10;
4. 查询员工中年龄大于22岁的第2页的3条数据
select * from t_item where price<100 limit 3,3;
###concat()函数
- 把concat内部的参数拼接到一起
1. 查询员工姓名和年龄，要求年龄单位是岁
select name, concat(age,'岁')from emp;
###数值计算 + - * / %
mod(7,2)等效 7%2 取余；
select mod(7,2); //1;###日期相关函数
1. 获取当前的 年/月/日/时/分/秒
select now();
2. 获取当前的日期 年/月/日
select curdate();
3. 获取当前的时间 时/分/秒
select curtime();
4. 从年月日时分秒中提取年月日
select date(now());
5. 从年月日时分秒提取时分秒
select time(now());
6. 从年月日时分秒中提取时间分量 年 月 日 时 分 秒
- extract(year from now())
- extract(month from now())
- extract(day from now())
- extract(hour from now())
- extract(minute from now())
- extract(second from now())
select extract(year from now());- 查询员工表中的所有员工姓名和入职的年份
例如：select ename,extract(year from hiredate) from emp;
7. 日期格式化 date_format(时间,格式)
- %Y:四位年 2018
- %y:两位年 18
- %m:两位月 05
- %c:一位月 5
- %d:日
- %H: 24小时
- %h: 12小时
- %i: 分
- %s: 秒
select date_format(now(),'%Y年%m月%d日 %H时%i分%s秒');
- 把非标准的日期字符串转成标准的时间格式 str_to_date(时间字符串,格式)
26.12.2018 17:19:10
select str_to_date('26.12.2018 17:19:10','%d.%m.%Y %H:%i:%s'); //结果为 2018-12-26 17:19:10
select str_to_date('26-12-2018 00:28:17','%d-%m-%Y %s:%i:%H'); //结果为 2018-12-26 17:28:00 select str_to_date('2018年12月28号','%Y年%m月%d号'); //2018-12-28
注：格式要一致它才能识别
###ifnull(x,y)函数
- age=ifnull(x,18) 如果x的值为null 则age=18 如果不为null则 age=x
1. 修改员工表中奖金为null的值为0
update emp set comm=ifnull(comm,0);
###聚合函数
- 用于对多条数据进行统计
1. 求和 sum(字段名)
- 查询emp中10号部门的工资总和
select sum(age) from emp where dept='取经部';
2. 平均值 avg(字段名)
- 查询emp表中所有员工的工资平均值
select avg(age) from emp;
3. 最大值 max(字段名)
- 查询30号部门的员工的最高奖金
select max(age) from emp where dept='取经部';
4. 最小值 min(字段名)
- 查询商品表中价格最便宜的商品的单价
select min(age) from emp;
5. 统计数量 count(字段名) 一般使用count(*)
- 统计30号部门有多少人
select count(*) from emp where dept is not null;
###和字符串相关函数
1. 获取字符串的长度 char_length(str);
获取所有员工的姓名和姓名的字符长度
select name,char_length(name) from emp;
2. 获取字符串在另外一个字符串中出现的位置 instr(str,substr)
select instr('abcdefg','d');
3. 插入字符串 insert(str,start,length,newStr)
select insert('abcdefg',3,2,'m'); //abmefg
4. 转大写 转小写
select upper('abc'),lower('NBA');
5. 左边截取和右边截取
select left('abcdefg',2) , right('abcdefg',2);
6. 去两端空白
select trim(' a b ');
7. 截取字符串
select substring('abcdefg',3,2);
8. 重复 repeat(str,count)
select repeat('ab',2);
9. 替换 replace(str,old,new)
select replace('This is mysql','my','your');
10. 反转 reverse(str)
select reverse('abc');
###总结：
1. is null 和 is not null
2. 别名
3. 去重 distinct
4. and 和 or
5. 比较运算符 > < >= <= = !=和<>
6. in
7. between x and y
8. like _单个未知 %0或多个未知
9. order by age asc/desc,sal
10. limit 12,3
11. concat(abc,mm)
12. 数值运算 + - * / %和 mod()
13. 日期 now() curdate() curtime() date(now()) time(now()) extract(year/month/day/hour/minute/second from now()) date_format(now(),'格式') Y y m c d H h i s , str_to_date(时间字符串，格式)
14. age=ifnull(x,y) 如果x=null age=y x！=null age=x
15. 聚合函数 sum() avg() max() min() count(*)
16. 字符串 char_length instr insert upper lower left right substring replace repeat reverse trim
