数据库常用语法总结-1

1.  create database cgb2110;    创建名为cgb2110的数据库
    //create database cgb2110 DEFAULT CHARACTER SET utf8;
    //create database cgb2110 charset utf8;
    
2.  show databases;        展示数据库列表

3.  drop database cgb2110;        删除cgb数据库

4.  use cgb2110;    使用数据库cgb2110

5.  create table student();        创建名为student的列表

6.  show tables;    展示表格列表

7.  desc user;        展示user表格各字段

8.  select *from user；        展示user表格所有数据

9.  alter table user add column age int(3);        向user表格添加字段

10. insert into user values(100,20,"吊毛",10);        向表格添加一行数据

11. update user set age=33,pwd=999;        修改user一列或者几列select字段值

12. delete from user where id=2;    删除user表格中id等于2 的数据

13. set names gbk;        临时语句，固定命令，用于防止中文乱码

主键约束(唯一且非空)
14. create table teacher(id int unique);        唯一约束，列表中字段值不能重复

15. create table teacher(name varchar(10) not null);        非空约束，列表中name字段值不能为空

16. create table teacher(id int primary key auto_increment);        主键自增唯一且非空
    insert into teacher values(null);不需要设置值 
    
    
数据更新及格式转换
17. select ename,job from emp;        查询两列数据--字段ename和字段job

18. select ename a from emp;        查询一列数据用别名a表示--字段ename

19. SELECT ename,UPPER('abc'),UPPER('ename') FROM emp;        字母转大写
    SELECT ename,lower('ABC'),UPPER('ename') FROM emp;        字母转小写
    
20. SELECT ename,LENGTH(ename),LENGTH(job) FROM emp;        求字段ename长度--字母或数字为1，汉字为3

*21. SELECT ename,SUBSTR(ename,2) FROM emp;        从字段ename第二位开始截取到最后
    SELECT ename,SUBSTR(ename,2，2) FROM emp;        从字段ename第二位开始截取两位
    
*22. SELECT ename,CONCAT(ename,123,'hello') FROM emp;        给字段ename拼接字符串123和hello

*23. SELECT ename,REPLACE(ename,'a','b') FROM emp;     replace(ename,'a','b')替换字符，字段ename，将a替换成b

24. SELECT comm,IFNULL(comm,100) FROM emp;         ifnull如果是null，comm字段将null换成100

*25. select comm,round(comm) from emp;         round(a)四舍五入，把a的值取整
    select comm,round(comm,2) from emp;     round(a,b)四舍五入，把a的值保留b位小数
    
*26. select comm,ceil(comm),floor(comm) from emp;     ceil向上取整，floor向下取整

27. select now(),YEAR('2021-11-25'),YEAR(NOW()),month(now());         获取当前日期 + 解析年份的两种方式 + 。。。
    //now \year \month \day \hour \minute \second 
    
28. select 'xi\'an';     转义字符'，\作为转义的格式
    //select "xi'an";    也是可以的
    
条件查询(where过滤)
29. SELECT DISTINCT loc FROM dept;         distinct数据去重

30. select * from dept where loc = '一区';        满足条件才查询（格式）：select 字段名 from 表明 where 字段名 = 字段值

and/or连接查询条件
31. SELECT dname FROM dept WHERE loc = '二区' AND deptno = 3;//并且
    SELECT dname FROM dept WHERE loc = '二区' or deptno = 3;//或者
    特殊情况下的or可以用in转换
    SELECT dname FROM dept WHERE deptno = 2 OR deptno = 3;
    SELECT dname FROM dept WHERE deptno IN (1,2);
    当约束条件不同时，则不可以转换
    SELECT dname FROM dept WHERE loc = '一区' OR deptno = 3;
    
where增删改数据
UPDATE USER SET pwd = 9999999 WHERE id = 3;
    
like模糊查询--%是通配符（0~n个字符），_是通配符（1个字符）
32. select ename,job from emp where ename like 't%'    查询名字是以t开头的员工姓名和岗位
    select ename,job from emp where ename like '%o%'    查询名字包含o的员工姓名和岗位
    
对null特殊处理
33. elect ename,job from emp where comm is null;     NULL不用=，而是用is
    SELECT ename,job FROM emp WHERE comm IS NOT NULL;
    //select ename,job from emp where comm = null; 
    
取值区间between
34. select ename,hiredate from emp where sal > 5000 and sal <10000;
    select ename,hiredate from emp where sal between 5000 and 10000;
    
    select ename,hiredate from emp where hiredate between '2019-1-1' and '2019-12-31';
    select ename,hiredate from emp where year(hiredate) = 2019;
    
分页limit（限制）
35. select * from emp limit 2;         只展示前两条员工信息,从第0+1 条数据开始，共取出2条数据
    select * from emp limit a,b;         展示中间两条员工信息,从第a+1 条数据开始，共取出b条数据
    select * from emp limit (pageNumber-1)*pageSize , pageSize        limit用于分页 -- 页码，页面包含数据量

排序order by (默认升序) + desc(降序)
37. select * from emp order by sal;
    select * from emp order by empno;
    select * from emp order by empno desc;
    按照字母排序
    select * from emp order by ename;         26 英文字母/字典顺序
    按照日期排序
    select * from emp order by hiredate;         数字排序
    按照中文排序
    select * from emp order by job;         预先设定好的对应汉字编码表
    
38. select sal,comm,sal+ifnull(comm,0) from emp;         月薪

39. select * from emp where year(hiredate)<2019;
    select * from emp where hiredate<'2019-1-1';
    
聚合函数aggregation--max min sum avg count 
40. select max(sal) from emp;         求最大值 
    select min(sal) from emp;         求最小值 

41. select sum(sal) from emp;         求总和
    select avg(sal) from emp;        求平均数
    select count(sal) from emp;        计数总记录数（不推荐），不统计null
    select count('*/1') from emp;        推荐

非聚合函数--len(长度)、abs(绝对值)等
    
当查询结果中，出现了混合列（聚合及非聚合）时，必须分组
42. //select job,avg(sal) from emp;        报错
分组--group by 分组合理需求-job deptno hiredate
    select job,avg(sal) from emp group by job;         统计每个岗位的平均薪资
    select job,count(1) a from emp group by job order by a;        统计每个岗位的员工人数并排序 
    

多表联查
查询方式：1.笛卡尔积 -- 集合A * 集合B
          2.链接查询 -- join on描述表关系，where表示更多的关系
          3.嵌套查询 -- 子查询
          
笛卡尔积
select distinct * from dept,emp    效率低，出现重复无效数据
where emp.deptno = dept.deptno  
where ：添加查询条件，表明两张表的关系

SELECT DISTINCT emp.ename FROM dept,emp WHERE emp.deptno = dept.deptno AND dept.loc='二区'
SELECT DISTINCT emp.* FROM emp,dept WHERE emp.deptno = dept.deptno AND dname = 'research'
SELECT COUNT(1) FROM emp,dept WHERE emp.deptno = dept.deptno AND dname = 'research' GROUP BY emp.deptno

链接查询 -- join on描述表关系，where表示更多的关系
select * from emp join dept on emp.deptno = dept.deptno 
select emp.ename from emp join dept on dept.deptno = emp.deptno where dept.loc = '二区'
select emp.* from emp join dept on emp.deptno = dept.deptno where dname = 'research'
select count(1) from emp join dept on emp.deptno = dept.deptno where dname = 'research' group by emp.deptno

链接查询的原则：左表（小）查所有（简单结构），右表（大）查条件 -- 小表驱动大表
三种连接方式 -- inner join，left join，right join
左外连接：取到左表的所有和右表满足条件的，不满足的用null填充
右外连接：取到右表的所有和左表满足条件的，不满足的用null填充
SELECT * FROM dept inner JOIN emp ON dept.deptno = emp.deptno 
SELECT * FROM dept left JOIN emp ON dept.deptno = emp.deptno 
SELECT * FROM dept RIGHT JOIN emp ON dept.deptno = emp.deptno 
嵌套查询 
select * from emp where deptno = (select deptno from dept where dname = 'accounting')
SELECT * FROM dept WHERE deptno = (SELECT deptno FROM emp WHERE ename = 'tonv')
SELECT ename FROM emp WHERE emp.deptno IN (SELECT deptno FROM dept WHERE loc = '二区')         deptno 的值不能等于两个值，而是用in
SELECT * FROM emp WHERE sal>(SELECT AVG(sal) FROM emp)

拓展：索引 -- 快速查找数据
单值索引 -- 单字段，唯一索引 -- 单字段且字段值不相同，复合索引 -- 多字段
查看索引：
show index from dept
创建索引 -- 经常查的字段加索引 - 一般是名称用的比较多+索引
create index loc_index on dept(loc)
查看SQL的执行计划、性能 -- explain
explain select * from dept where dname = 'research'
创建唯一索引 -- 字段的值必须唯一
create unique index 索引名 on 表名（字段名）
create unique index name_index on dept(dname)
创建复合索引 -- 可能会产生索引失效的问题 - 最左特性：必须包含最左边的元素
create index 索引名 on 表名（字段名1，字段名2） 
create index fuhe_index on emp(job,deptno)
explain
#SELECT * FROM emp WHERE job = '员工' AND deptno = 2//可以
#SELECT * FROM emp WHERE job = '员工'//可以
#SELECT * FROM emp WHERE deptno = 2//不可以
#SELECT * FROM emp WHERE deptno = 2 AND job = '员工'//可以
SELECT * FROM emp WHERE deptno = 2 or job = '员工'//不可以
删除索引
alter table 表名 drop index 索引名 

创建视图 - - create view 视图名 as SQL 语句
*create view ename_view as
selete * from emp where ename like '%a%'
查视图，当做表来查
select * FROM ename_view
explain用来监测查询
   
批量插入：
insert into dept values(,,)   
insert into dept(dname,loc) values(,)     只给指定字段设置值   
insert into dept(dname) values()     只给指定字段设置值   
insert into dept(dname) values(),(),()     批量插入多条数据，优化了事务管理，只需要开启和关闭一次事务

事物特性：四大特性：A原子性、C一致性、I隔离性、D持久性
隔离级别：读未提交、读已提交、可重复读、串行化

拓展优化；
1.用字段名代替*
2.where里，能用=，就不用！=，能用and就不用or，用= 就不用in
3.字段的类型，能用varchar ,就不用char，字段的值能用数字就不用字符串
4.单表的索引最多5个
5.varchar 变长  char定长

6.sql全连接（除了MySQL）
full [outer] join

7.区别
union (或称为联合)的作用是将多个结果合并在一起显示出来。.
union和union all的区别是,union会自动压缩多个结果集合中的重复结果，
而union all则将所有的结果全部显示出来，不管是不是重复。.
Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；
Union All：对两个结果集进行并集操作，包括重复行，不进行排序；
8. Intersect：对两个结果集进行交集操作，不包括重复行，同时进行默认规则的排序；. 
Minus：对两个结果集进行差操作，不包括重复行，同时进行默认规则的排序。. 
可以在最后一个结果集中指定Order by子句改变排序方式。.

9.SQL调优步骤（思路）：重点；-- 建立索引、创建事务、创建视图等