SQL常用语句(基础)大全---(持续更新)

--创建库
create database 库名;
--创建库时判断库是否存在，不存在则创建
create database if not exists 库名;
--查看所有数据库
show databases;
--使用指定数据库
use 库名;
--查看当前指定数据库包含的数据表
show tables;
--查看数据库的结构定义信息
show create database 库名;
--删除数据库
drop database 库名;
--修改数据库的字符集为utf8
alter database 库名 character set utf8;

2、操作数据表

--创建表
create table 表名(
字段1 类型1,
字段2 类型2,
字段3 类型3,
...........
);
--查看表结构
desc 表名;
--查看创建表的SQL语句
show create table 表名;
--修改表名
alter table 表名 rename to 新的表名;
--添加一个新的字段
alter table 表名 add 字段; 字段类型;
--修改字段名
alter table 表名 rename column 字段名 to 新的字段名;
--修改字段类型(注意类型修改前后数据是否兼容)
alter table 表名 modify column 字段名 新的字段类型;
--删除一个字段
alter table 表名 drop 字段名;
--删除表
drop table 表名;
--删除表时判断表是否存在,若存在则删除
drop table if exists 表名;

二、DML(数据操作语言)

DML是数据操作语言，主要是对数据表的操作。DML操作包括插入数据（insert）、删除数据（delete/truncate）、修改数据（update）。

1.插入数据 insert inot

--有多少个字段,就要写多少个值一一对应的
insert into 表名 values(值1,值2,值3...值n);
--此方法需要写出所有要插入的字段,并一一对应插入值
insert into 表名(字段1,字段2...字段n) values(值1,值2...值n);

2.删除数据 delete / truncate

--删除表中所有数据
delete from 表名;
--删除表中指定的数据
delete from 表名 where 字段 = 值;
--删除表中所有数据(先删除整张表,然后创建一张一样的空表，此方法更高效)
truncate table 表名;

3.修改数据 update set

--无限制条件的修改,会修改整张表
update 表名 set 字段 = 值;
--有限制条件的修改,只修改特定数据
update 表名 set 字段 = 值 where 字段 = 值;

三、DQL(数据查询语言)

DQL是数据查询语言，主要就是select配合其他限制条件的关键字进行查询。DQL操作包括无条件查询、指定条件查询（如between、in、like）、排序查询、聚合函数（如sum、max）、分组查询、分页查询、连接查询（内连接、外连接）及子查询。

1.无条件查询

--查询表中所有数据
select *from 表名;

2.查询(什么开始，到什么结束)

--查询users表中年龄在18~30岁之间的记录
--方式1 between..and..
select * from users where age between 18 and 30;
--方式2 &&
select * from users where age>=18 && age<=30;
--方式3 and
select * from users where age>=18 and age<=30;

3.指定条件查询

3-1.单个条件 ro in

--查询users表中年龄为18,20,30岁的记录
--方式1 or
select *from users where age=18 or age=20 or age=30;
--方式2 in
select *from users where age in(18,20,30);

3-2.多个条件 and

--查询users表中年龄为17，性别为男，名字为王冬的记录
select * from users where age=17 and sex='男' and name='王冬';

3-3.查询不为NULL值 is not null ,为NULL值 is null

--查询users表中序号不为空的记录
select * from users where id is not null;
--查询user表中序号为空的记录
select *from users where id is null;

3-4.模糊查询 like

--查询users表中姓名第一个字为李的记录
select *from users where name like '李%';

--查询users表中姓名第二个字为李的记录
select *from users where name like '_李%';

--查询users表中姓名含有李字的记录
select *from users where name like '%李%';

--查询users表中姓名是两个字的记录
select *from users where name like '__';

3-5.去除重复记录查询 distinct

--查询users表中所在城市不相同的记录
--select distinct 字段 from 表名;

select distinct city from users;

3-6.排序查询 order by

3-6-1.单条件

--查询users表中记录，并以年龄升序排序
select * from users order by age; 默认升序
 
--查询users表中记录，并以年龄降序排序  desc降序
select * from users order by age desc;

3-6-2.多条件

--查询users表中记录，并体育成绩降序，年龄降序
select *from users order by PE desc,age desc;

3-7.聚合函数

3-7-1.计算和 sum

select sum(字段) as sum from 表名;

3-7-2.计算最大值 max

select max(字段) as max from 表名;

3-7-3.计算最小值 min

select min(字段) as min from 表名;

3-7-4.计算平均值 avg

select avg(字段) as avg from 表名;

3-7-5.计算个数 count

select count(字段) as count from 表名;

3-8.分组查询 group by

--查询users表中的记录，按照性别分组，查询男，女的考试成绩平均分

select sex,avg(result) from users group by sex;

--查询users表中的记录，按照性别分组,分别查询男、女的考试成绩平均分,人数

select sex, avg(result),count(id) from users group by sex;

--查询users表中的记录, 按照性别分组,分别查询男、女的成绩成绩平均分,人数 要求:分数低于60分的人，不参与分组
select sex, avg(result),count(id) from users where result> 60 group by sex; 

 --查询users表中的记录,按照性别分组,分别查询男、女的考试成绩平均分,人数 要求:分数低于60分的人，不参与分组,分组之后,人数要大于2个人

select sex,avg(result),count(id) from users where result > 60 group by sex having count(id)>2;

3-9.分页查询

--查询users表中的前10行条记录
select * from users limit 10;
--查询users表中第2~11条记录 (从第5条记录开始累加10条记录)
select * from users limit 4,10;
--查询users表中第5~17条记录 (从第5条记录开始累加13条记录)
select * from users limit 4,13;

3-10.内连接查询

如果查询数据的来源来自多张表，则必须对这些表进行连接查询

--语法1 （隐式内连接）
select 字段1,字段2...
from 表1,表2...
where 过滤条件;
 
--语法2 （显式内连接）
select 字段1,字段2...
from 表1 inner join 表2 ...
on 过滤条件;

3-11.外连接查询

左外连接：是表1和表2的交集再并上表1的其他数据

右外连接：是表1和表2的交集再并上表2的其他数据

--左外连接
select 字段1,字段2..
from 表1 left (outer) join 表2 on 过滤条件;
--右外连接
select 字段1,字段2..
from 表1 right (outer) join 表2 on 过滤条件;

3-12.子查询

查询语句中嵌套查询语句

-- 查询最大的年龄，左边条件是什么右边必须返回一样的
select * from user where age=(select max(age) from user);

四、DCL(数据控制语言)

DCL是数据控制语言：用来授权或回收访问数据库的某种特权，并控制数据库操纵事务发生的时间及效果，能够对数据库进行监视。DCL操作包括用户管理（添加、删除用户，修改密码，查询用户）和权限管理（授予权限、撤销权限）。

1.管理用户

1-1.添加用户

create user '用户名'@'主机名' identified by '密码';

1-2.删除用户

drop user '用户名'@'主机名';

1-3.修改密码

--切换到mysql数据库
USE mysql;
--修改密码
UPDATE USER SET PASSWORD=PASSWORD(‘密码’) WHERE User=’用户名’ and Host=’IP’;
--重新加载权限表
FLUSH PRIVILEGES;
--将root的密码改为root
update user set password=password('root') where user='root' and host=’localhost’;
--重新加载权限表
FLUSH PRIVILEGES;

1-4.查询所有用户

-- 查询用户
-- 1、切换到mysql数据库
USE mysql;
-- 2、查询user表
SELECT *FROM USER;

2.权限管理

2-1.查看现有权限

show grants for '用户名'@'主机名';

2-2.授予权限

--语法
grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';

--授予faker用户所有权限，在任意数据库任意表上
grant all on *.* to 'faker'@'localhost';

2-3.撤销权限

--语法
revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';
--撤销faker用户对tests数据库中city数据表的权限
revoke update on tests.city from 'faker'@'localhost';

2-4.权限列表

2-4-1. 数据库/表级常用权限

权限名称	说明
`ALL PRIVILEGES`	授予所有权限（慎用）。
`SELECT`	允许查询数据。
`INSERT`	允许插入数据。
`UPDATE`	允许更新数据。
`DELETE`	允许删除数据。
`CREATE`	允许创建表/数据库（需数据库级权限）。
`ALTER`	允许修改表结构（如添加/删除列）。
`DROP`	允许删除表/数据库。
`INDEX`	允许创建/删除索引。
`REFERENCES`	允许创建外键约束（通常与 `CREATE` 和 `ALTER` 配合使用）。
`CREATE VIEW`	允许创建视图。
`SHOW VIEW`	允许查看视图定义。
`TRIGGER`	允许操作触发器。
`EXECUTE`	允许执行存储过程。

2-4-2. 管理类权限

权限名称	说明
`GRANT OPTION`	允许用户将自己的权限授予其他用户（高危权限）。
`PROCESS`	允许查看正在运行的查询（如 `SHOW PROCESSLIST`）。
`RELOAD`	允许执行 `FLUSH` 操作（如刷新权限表）。
`REPLICATION CLIENT`	允许查询主从复制状态（如 `SHOW REPLICA STATUS`）。
`REPLICATION SLAVE`	允许从服务器从主服务器读取二进制日志（用于主从复制）。
`CREATE USER`	允许创建/删除用户。

2-4-3. 特殊权限

权限名称	说明
`FILE`	允许读写服务器文件（如 `LOAD DATA INFILE`，高危权限）。
`SUPER`	允许执行管理命令（如 `KILL` 线程、修改全局变量，高危权限）。

2-4-4.查看所有支持的权限

--查看所有支持的权限
SHOW PRIVILEGES;

注意：授权后需刷新权限

--刷新权限
FLUSH PRIVILEGES;