Mysql语句大全

显示数据库: show databases;
进入数据库: use 数据库名字
显示全部表名字: show tables
查看表内数据类型: desc 表名

数据定义语言(DDL): 对数据库管理系统里的数据库和表进行新增,修改和删除
数据操作语言(DML): 对数据库里的表数据进行新增,修改和删除
数据控制语言(DCL): 对数据库的用户进行授权和回收权限
数据查询语言 (DQL): 对数据进行查询

DDL 语句 对数据库管理系统里的数据库和表进行新增,修改和删除

数据库操作

创建数据库:
creat database 数据库名字 character set 设置字符集(如utf8,ascii)
删除数据库:
drop database 数据库名字
修改数据库的字符集:
alter database 数据库名字 character set设置新字符集(如utf8,ascii)

表操作

创建表:
creat table 表名(
字段1 数据类型1,
字段2 数据类型2 …)
删除表:
drop table 表名
修改表: alter table 表名 rename 新表名
表里字段内容操作
增: alter table 表名 add 字段 数据类型
删: alter table 表名 drop 字段
改: alter table 表名 change 旧字段 新字段 新数据类型
删除表内全部数据 truncate table 表名
truncate与delete区别:

---------------------------------------- 区别:
(1) delete属于DML ,truncate属于DDL
(2) delete可以带where子句,只删除满足条件数据行
truncate不能带where子句,无条件清空整张表
(3) delete性能要比truncate要慢
(4) delete删除后,再插入数据自增列不会复位
truncate会使用自增列复位

DML 语句 对数据库里的表数据进行新增,修改和删除

[ ]中可根据需要选择写入,也可以不写
复制表结构:
create table 新表 like 旧表
复制表结构+数据:
create table 新表 as select * from 旧表

增:
insert into 表名[(字段1,字段2,字段3…)] values(值1,值2,值3) 字段跟值要对应
删:
delete from 表名 [where 条件]
改: update 表名 set 字段1= 新值1,[字段2 = 新值2…] [where 条件]

DQL语句 对数据进行查询

万能语句
select 结果 from 表 where 字段 比较运算符(like) 值 and/or 条件 group by 分组字段 having 字段 比较运算符(like) 值 order by 排序字段 排序规则 limit m,n; m, 起始行, n, 数据总行数

对字段起别名
select 字段1 (as) 别名1,字段2 (as) 别名2 from表名

对表起别名
select * from 表名 (as) 别名;

部分行(分页,限制)查询
语法:
select 结果 from 表名 limit m,n; m:起始行-1 n:查询行数

排序查询
语法:
select 结果 from 表 order by 字段 排序规则(asc正序,desc倒序);
排序对多个字段进行排序(前面的排序字段要有相同的值)
语法:
select 结果 from 表 order by 字段1 排序规则(asc正序,desc倒序),(字段2 排序规则);

运算符
1.算术运算符(字段结果处) + - * /
2.比较运算符(where条件) > < >= <= = !=(<>)
3.逻辑运算符(where条件) and与 or或 not非
条件1 and 条件2 结果既要满足条件1 且 满足条件2
条件1 or 条件2 结果只需要满足条件1 或者 满足条件2 中的某一个即可
not 条件 显示不满足条件的结果

模糊条件查询
作用:
查询满足条件的结果,条件是模糊的
语法:
select 结果 from 表 where 字段 like ‘占位符+值’;
占位符: % 占0-n位 , _ 占1位

去重查询
语法:
select distinct 结果 from 表;

集合查询
语法:
select 结果 from 表 where 字段 in (值1,值2,值3…);
作用:
查询满足多个条件的结果

聚合函数 函数:xxx()
作用:具备一定功能的小工具
求和 sum(字段)
作用:求字段里的所有值的和 计数
count(字段)
作用:求字段里的所有值的个数
最大值 max(字段)
作用:求字段里的所有值的最大那个
最小值 min(字段)
作用:求字段里的所有值的最小那个
平均值 avg(字段)
作用:求字段里的所有值的平均值
语法:select 聚合函数() from 表;
聚合函数作为条件时，一定放在having后，有having必定有分组,有分组就可以从结果那里看谁是分组字段

分组查询
语法:select 结果(分组字段,聚合函数) from 表 [where 条件] group by 分组字段1,(分组字段2) [having 条件];
作用:对某个字段进行分组显示

DQL进阶查询

where 字段 is null 与 is not null;
闭区间查询
where 字段 between x and y;

子查询

单表子查询
语法:
select 结果 from 表1 where 字段 =/>/</>=/<=/!= in (select 结果 from
表1…);
如果括号里的返回的数据是多行的话 in
举例:
列出工资高于在部门30工作的所有员工的工资的员工姓名和工资。
1.select ename,sal from emp where sal >
(select max(sal) from emp where deptno = 30);
2.select ename,sal from emp where sal >
all (select sal from emp where deptno = 30);
3.select ename,sal from emp where sal > (select
max(s.sal) from ( select sal from emp where deptno = 30 ) as s);
列出工资高于在部门30工作的任意员工的工资的员工姓名和工资
1 select ename,sal from emp where sal > any (select sal from emp where deptno = 30);
2 select ename,sal from emp where sal > (select min(sal) from emp where deptno = 30);

关键字 all 所有 any 任意一个

多表子查询
语法:
select 结果 from 表1 where 两个表的共同字段 =/>/</>=/<=/!= in (select 两个表的共同字段 from 表2…);

联合查询
语法:
select 语句 union select 语句
作用:
把2个select语句的结果上下放
union 自带去重功能
union all 不去重

笛卡尔积
语法:
select 结果 from 表1,表2… where 表1.共同字段 = 表2.共同字段…;
条件的个数 = 表的个数 - 1
作用:
把2张表关联在一张表里,选择需要的数据

笛卡尔积适用范围
1.子查询能干的事情,笛卡尔积都能干
2.笛卡尔积能干的事情,子查询不一定能干

在MySQL里如果一个sql语句出现相同的表名,一定需要对表起别名 使用 表.字段 指定是哪张表下的那个字段 列
列出工资比"刘一"多的所有员工信息。
1.select * from emp where sal > (select sal from emp where ename = ‘刘一’);
2.select emp.*,e.sal from emp,emp e where e.ename = ‘刘一’ and emp.sal > e.sal;

连接查询

内连接查询

select * from 表1 [inner] join 表2 on 条件;

查询效率比笛卡尔积高

把两张表内联，结果为符合条件的数据

语法不一样 笛卡尔积:表1,表2 筛选条件使用的是where 内连接:表1 join 表2 on

内连接的查询速度比笛卡尔积快

外连接查询

左外联查询：A表左外联B表，符合条件的数据正常显示，A表中不符合查询条件的数据也显示出来，同时B表对应的列以空值替代

语法:
select 结果 from 表1 left join 表2 on 表1.共同列=表2.共同列 left join 表3 on 表2.共同列=表3.共同列;

右外联查询：
A表右外联B表，符合条件的数据正常显示，B表中不符合查询条件的数据也显示出来，同时A表对应的列以空值替代

语法:
select 结果 from 表1 right join 表2 on 表1.共同列=表2.共同列 right join 表3 on 表2.共同列=表3.共同列;

万能公式

select distinct 结果列(聚合函数) from 表1 left/right join 表2 ON 条件(也可以是表1,表2)
where 条件 group by 分组列 having 条件 order by 排序列 limit m,n; m, 起始行, n,
数据总行数

在使用left/right join 时(内连接 (inner) join,on和where没有区别),on和where条件的区别如下:

1.on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中未匹配的数据的记录。
2.where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left(right) join的含义，条件不为真的就全部过滤掉。

case when 查询

类似于Java中 switch…case 结构 switch(表达式){ case 1:
…
break; case 2:
…
break;
default:
… }
Java中的switch-case只能做等值条件判断
而sql中case…when既能用于等值判断,也能用于区间范围的判断,类似于if-else if结构

• 语法1: 等值判断 case 列
  when 值1 then 表达式1
  when 值2 then 表达式2
  when 值3 then 表达式3
  …
  [else 表达式N] end

• 语法2: 区间范围的判断 case when 列>值1 then 表达式1
  when 列>值2 then 表达式2
  when 列>值3 then 表达式3
  …
  [else 表达式N] end
  例如

SELECT 
    st.s_id'学号'
    ,st.s_name'姓名'
    ,SUM(CASE WHEN c.c_name='语文' THEN sc.s_score else null end)语文 
    ,SUM(CASE WHEN c.c_name='数学' THEN sc.s_score else null end)数学
    ,SUM(CASE WHEN c.c_name='英语' THEN sc.s_score else null end)英语
    ,AVG(sc.s_score)平均分
-- 使用SUM是为了提取聚合值（case when之后的值是多个，但是又很多空值，使用聚合函数提取一个值）
FROM student as st
INNER JOIN score as sc ON st.s_id=sc.s_id
INNER JOIN course as c ON sc.c_id=c.c_id  
GROUP BY st.s_id,st.s_name
ORDER BY AVG(sc.s_score) DESC
-- 这个是数据库多个表连接，并按照平均值降序排列


select ss.等级,count(*) '人数' from
(select case
when score>=90 then '优秀'
when score>=70 then '良好'
when score>=60 then '优秀'
else '不及格' end as '等级'
from sc )as ss
group by ss.等级;

select
sum(case ssex when ssex='男' then 1 else 0 end )as '男生人数',
count(case ssex when ssex='女' then 1 end) 
as '女生人数',
count(*) '总人数' 
from student;

DCL语句 对数据库的用户进行授权和回收权限

查询用户 语法：selct * from user;
MySQL用户信息默认存储在mysql数据库中的user表中，所以只需要查询mysql数据库中的user表即可

创建用户 语法：create user ‘用户名’@‘主机名’ identified by ‘密码’;
注意：当我们希望任意主机都能访问MySQL，只需要把主机名的值写为%即可 注意：这样创建的用户只有登录MySQL的权限，没有操作数据库的权限

修改用户密码 语法：alter user ‘用户名’@‘主机名’ identifided with mysql_native_password by ‘新密码’;

删除用户 语法：drop user ‘用户名’@‘主机名’;

权限控制 在MySQL中常用的权限有以下几种：
ALL,ALL PRIVILEGES 所有权限
SELECT查询数据 INSERT插入数据
UPDATE修改数据 DELETE删除数据
ALTER修改表
DROP删除数据库/表视图
CREATE创建数据库/表
注意：
多个权限之间，使用逗号分隔； 授权时，数据库名和表名可以使用*进行通配，代表所有

查看用户权限 语法：SHOW GRANTS FOR ‘用户名’@‘主机名’;

授予权限 语法：GRANT 权限列表 ON 数据库名.表名 TO ‘用户名’@‘主机名’;
给用户user授予test数据库中的students数据表全部权限
GRANT ALL ON test.students TO ‘user’@‘%’;

撤销权限 语法：
REVOKE 权限列表 ON 数据库名.表名 FROM ‘用户名’@‘主机名’;
用户user撤销test数据库中的students数据表全部权限
REVOKE ALL ON test.students FROM ‘user’@‘%’;