SQL 语句总结

关于数据库操作

创建数据库：create database 数据库名字  default character set utf8;
           create database 数据库名字 charset utf8; 同上,都可以设置编码
查询所有库：show databases; 
查询具体的数据库  show database 数据库名；
删除所有库   drop databases;  
删除具体的数据库  show database 数据库名；

关于表的操作

使用表： use 数据库名字;

新建表：create table 表名（主键 字段类型 primary key auto_increment,   //表示主键自增
           字段名称1 字段类型（字段长度），字段名称2 字段类型（字段长度））

在建好的表中插入一个新的列：alter table 表名 add column 列名 列名类型（长度）;
      
删除表：drop table 表名;

描述表：desc 表名;

关于表中字段的操作(单表)

查询表中所有内容 select * from 表名;
在表中插入具体的内容:insert into 表名 values(第一个字段,第二个字段,...);
                                              字符串用单引号或者双引号
修改表中字段：update 表名 set 字段名=新值;
删除数据：delete from 表名;

添加约束

唯一约束：unique   
给一张表的字段添加唯一约束:create table 表名 （字段名 字段类型(长度) unique）;
非空约束：not null  约束字段不为空
给表添加非空约束：create table 表名（字段名 字段类型(长度) not null）;
主键约束：primary key     自增:auto_increment
给表添加主键自增约束：create table 表名（字段 字段类型 primary key auto_increment）;
插入字段因为已经自增，所以直接设置null 
insert into 表名 values（null）;

mySQL基础函数

查询rest列的值   select rest from 表名;
给字段名设置别名  select myself m, youself y from 表名;
查询字段名的别名  select myself m from 表名;

基本函数：
#1.upper(m) 全转大写,m作为字段名称或者字符串
select 字段名,upper(字段名) from 表名; 先查询字段，把字段改为大写
#2.lower(m)全转小写,m是字段名称或者字符串
select 字段名,lower(字段名) from 表名; 先查询字段，把字段改为小写
#3.length (字段名称) 求长度，一个字母或者数字长度为1，汉字在UTF-8为三个字节，ASCLL为两个字节
select 字段名，length(字段名)，length(字段名2) from 表名;
#4.substr (a,b,c)截取 a是字段名称 b从第几个开始 c是要截取的长度
select 字段名,substr(字段名称，2，5) from 表名;
#5.concat (a,b,c)拼接，a是字段名称，b和c都是要拼接的数据
select 字段名称, concat(字段名称，康熙，貂蝉) from 表名;  拼接康熙和貂蝉
#6.replace (a,b,c) 替换，a是字段名称，b是要准备替换的  c是要最后替换的结果
select 字段名称 replace(字段名称，'可爱'，'甜美') from 表名;把可爱替换成甜美
#7.ifnull (a,b)如果是null就替换，a是字段名称,b是 如果是null就替换成b
select 字段名称，ifnull(字段名称，100 ) from 表名; b是100，所以不会为空
小数处理：
#round(a)四舍五入,把a的值取整
select 字段名称，round(字段名称)from 表名;
#round(a,b)四舍五入，把a的值保留b位小数
select 字段名称，round(字段名称，2) from 表名;
#ceil 直接向上取整  &  floor向下取整
select 字段名称，ceil(字段名称) from 表名;
select 字段名称，floor(字段名称) from 表名;

条件查询

#distinct 去重  where链接条件
-----------------------------
select distinct 需要去重的字段 from 表名;
#练习：去重：名字含有丽丽的19岁女孩，
select distinct name="丽丽" from user;

#练习
#查询二区的部门名称  部门名称作为主要查询条件，二区来确定条件
select dname from dept where loc="二区";
#多个条件查询：用 and / or 来连接
#查询二区一号部门的员工名字 where需要确定条件具体的表(两张不同的表)
select ename from emp,dept where dept.loc='二区' and dept.deptno=1;
#查询编号是1或者2的部门名称
select dname from dept where deptno=1 or deptno=2;

#根据条件修改具体的字段
#update 表名 set 字段名=字段值 where 根据的条件
#根据id把表中的电话号码改为123
update user set Tel=123 where id=2;

#delete 删除,根据ID删除用户
delete from user where id=5; 

#like和null  模糊查询
-------------
#liek 是模糊查询，%是通配符，—是通配符（一个字节）
#练习：查询名字中含有"李"的员工薪资和岗位
select sal,job from emp.sal and dept.job where ename like "%李%";
#null  
#练习 :查询没有奖金的员工的名字和岗位以及薪资
select ename,job,sal from emp and dept where comm is null;

between and & limit & order by
------------------------------
#between and 区间范围;
#查询工资在[5000~10000]员工姓名和入职日期
select ename,hiredate from emp where sal>=5000 and sal<=10000;  一般写法
select ename,hiredate from emp where BETWEEN 5000 AND 10000;
#练习: 查询在2022年入职的员工姓名;
select ename from emp where hiredate BETWEEN '2022-01-01' AND '2022-12-31';
select ename from emp where YEAR(hiredate)=2022;

#limit 分页查询数据
#查询表中前两条数据
#写法：select 字段名 from 表名 where LIMIT m,n;m和n均为数字，从第m+1条开始，取n条;
select * from emp where limit 2;  
select * from emp where limit 0,5; 从第0+1条开始，取5条

#order by 排序   asc升序   desc降序 
#练习：按照名字排序
select * from emp ORDER BY ename; 
#练习：按照最高薪的员工的名字和岗位
select * from emp ORDER BY sal desc 1;

e.g. 统计案例 

#练习：统计部门的工资支出
IFNULL基础函数，如果为空就用()里面的第二个字段来代替
select sal,comm sal+IFNULL(comm,0) from emp;#月薪 
select sal,comm sal+IFNULL(comm,0)*12 fromm emp;#年薪

聚合函数

#聚合函数 count 次数  max/min  sum和/avg平均
-------------------------------------------
#求一种事物的个数
select count(*/1) from 表名; *或者1都是高效写法，具体字段的话在这里反而是低效
#练习：统计2号部门员工人数
select count(1) from dept where loc=2;

#求一种具体的事物最大
select MAX(具体的字段)from 表名;


#求具体的事物最小
select MIN(具体的字段)from 表名;
#求二号部门薪资最少的员工名字
select MIN(sal) from emp where loc=2;

#求具体的事物和
select SUM(具体的字段)from 表名;
#求一年工资的和
select SUM

#求具体的事物平均
select AVG(具体的字段)from 表名;
##练习:求二号部门低于部门平均工资的员工姓名
SELECT ename FROM emp WHERE sal<           #第二步：工资低于某个限度的员工姓名
(SELECT AVG (sal) FROM emp WHERE deptno=2);#第一步：2号部门的平均工资

group by & having
-----------------
#出现聚合函数必须用分组 GROUP BY
#统计每个岗位的平均工资
select job,AVG(sal) from emp GROUP BY job;
#统计每个岗位的员工人数并排序
select  job,count(1) a from emp GROUP BY job ORDER BY desc;

#分组后过滤
#求每个部门总人数大于10的部门
select deptno,count(1) b from emp GROUP BY deptno HAVING b;

多表联查

#笛卡尔积（交叉连接）
--------------------
#语法：select 查询字段 from 表名1,表名2 where 表1.相同字段名=表2.相同字段名;
#练习：查询在一区办公的员工名字
select ename from emp,dept where emp.id=dept.id AND dept.loc="一区";

#练习：查询在好运来部门工作的员工的信息
select emp.*,count(1) from emp,dept 
where emp.id=dept.id  #描述两表之间的关系
AND dept.dname="好运来" 
GROUP BY emp.deptno

#连接查询
---------
#语法：多表之间用 JOIN　连接，用ON来表示两张表之间的关系，用WHERE来表示更多条件
select　要查询的字段　from　表名1 JOIN 表名2 
ON　表1.相同的字段=表2.相同的字段  
WHERE 更多条件;
#练习：查询在好日子部门工作的员工的信息
select emp.* from dept join emp on dept.id = emp.id where dept.dname="好日子";

1.内连接：inner join  取交集
2.左外连接：left join 左表的全部和右表满足条件的
3.右外连接：right join 右表的全部和左表满足条件的
#查询部门表和员工表的所有数据
select * from dept INNER　JOIN emp ON dept.id=emp.id;

#连接查询优化(左连接)：小表驱动大表 left join
select * from dept LEFT join emp on dept.id=emp.id;
 
#右连接 right join 
select * from emp right join dept on emp.id=dept.id;

#子查询（嵌套查询）
-----------------
#查询高于平均薪资的员工名字
select ename from emp where sal > (select avg(sal) from emp)