ToB企服应用市场:ToB评测及商务社交产业平台

标题: MySql中常用的sql语句大全(工作常用篇) [打印本页]

---

作者: 西河刘卡车医    时间: 2024-8-23 20:50
标题: MySql中常用的sql语句大全(工作常用篇)
1. DDL

1.1 操纵数据库

1. --创建库
2. create database 库名;
4. --创建库时判断库是否存在，不存在则创建
5. create database if no exists 库名;
7. --查看所有数据库
8. show databases;
10. --使用指定数据库
11. use 库名;
13. --查看当前指定数据库包含的数据表
14. show tables;
16. --查看数据库的结构定义信息
17. show create database 库名;
19. --删除数据库
20. drop database 库名;
22. --修改数据库的字符集为utf8
23. alter database 库名 character set utf8;

复制代码

1.2 操纵数据表

1. --创建表
2. create table 表名(
3.         字段1 类型1，
4.     字段2 类型2，
5.     字段3 类型3，
6.     ......
7. );
9. --查看表结构
10. desc 表名；
12. --查看创建表的SQL语句
13. show create table 表名；
15. --修改表名
16. alter table 表名 rename to 新的表名；
18. --添加一个新的字段
19. alter table 表名 add 字段；字符类型；
21. --修改字段名
22. alter table 表名 rename column 字段名 to 新的字段名；
24. --修改字段类型(注意类型修改前后数据是否兼容)
25. alter table 表名 modify column 字段名 新的字段类型；
27. --删除一个字段
28. alter table 表名 drop 字段名；
30. --删除表
31. drop table 表名；
33. --删除表时判断表是否存在,若存在则删除
34. drop table 表名 if exists 表名；

复制代码

2. DML

2.1 插入数据(insert into)

• 全字段插入数据（有两种方法，推荐第一种方法）
  
  1. --有多少个字段,就要写多少个值,且是一一对应的
  2. insert into 表名 values(值1，值2...值n);
  4. --此方法要写出所有字段,并一一对应插入值
  5. insert into 表名(字段1，字段2...字段n) values(值1，值2...值n);

  复制代码
• 部分字段插入数据
  
  1. --部分字段插入数据，只写需要插入数据的字段名
  2. insert into 表名(字段1，字段2...) values (值1，值2...);

  复制代码

2.2 删除数据(delete / truncate)

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

复制代码

2.3 修改数据(update)

1. --无限制条件的修改,会修改整张表
2. update 表名 set 字段 = 值；
4. --有限制条件的修改,只修改特定记录
5. update 表名 set 字段 = 值 where 条件(字段 = 值)；

复制代码

3. DCL

3.1 管理用户

• 添加用户
  
  1. create user '用户名'@'主机名' identified by '密码';

  复制代码
• 删除用户
  
  1. drop user '用户名'@'主机名';

  复制代码

3.2 权限管理

• 查询权限
  
  1. show grants for '用户名'@'主机名';

  复制代码
• 授予权限
  
  1. --语法
  2. grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';
  4. --授予faker用户所有权限，在任意数据库任意表上
  5. grant all on *.* to 'faker'@'localhost';

  复制代码
• 撤销权限
  
  1. --语法
  2. revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';
  4. --撤销faker用户对test数据库中city数据表的权限
  5. revoke update on test.city from 'faker'@'localhost';

  复制代码

4. DQL

4.1 无条件查询

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

复制代码

4.2 查询在…到…之间(between and / && / and)

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

复制代码

4.3 指定条件查询

• 单个条件(or / in)
  
  1. --查询users表中年龄为18,20,25岁的记录
  2. --方式1 or
  3. select * from users where age=18 or age=20 or age=25;
  5. --方式2 in
  6. select * from users where age in (18,20,25);

  复制代码
• 多个条件(and)
  
  1. --查询users表中年龄为23，性别为女，名字为小楠的记录
  2. select * from users where age=23 and gender='女' and name='小楠';

  复制代码

4.4 查询不为NULL值(is not null),为NULL值(is null)

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

复制代码

4.5 含糊查询（like）

1. _:单个任意字符
2. %:多个任意个字符
3. --查询users表中姓名第一个字为李的记录
4. select * from users where name = '李%';
6. --查询users表中姓名第二个字为李的记录
7. select * from users where name = '_李%';
9. --查询users表中姓名含有李字的记录
10. select * from users where name = '%李%'；
12. --查询users表中姓名是两个字的记录
13. select * from users where name = '__';

复制代码

4.6 去除重复记录查询（distinct）

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

复制代码

4.7 排序查询（order by）

• 单个条件
  
  1. --查询users表中记录，并以年龄升序排序
  2. select * from users order by age;
  4. --查询users表中记录，并以年龄降序排序
  5. select * from users order by age desc;

  复制代码
• 多个条件
         注意：多个排序条件时，只有当第一个排序条件值一样，才会执行第二个排序条件，以此类推
     
  1. --查询users表中记录，并体育成绩降序，年龄降序
  2. select * from users order by PE desc, age desc;

  复制代码

4.8 聚合函数

• 计算和（sum）
  
  1. select sum(字段) (as sumvalue) from 表名;

  复制代码
• 计算最大值（max）
  
  1. select max(字段) (as maxvalue) from 表名;

  复制代码
• 计算最小值（min）
  
  1. select min(字段) (as minvalue) from 表名;

  复制代码
• 计算均匀值（avg）
  
  1. select avg(字段) (as avgvalue) from 表名；

  复制代码
• 计算个数（count）
  
  1. select count(字段)(as totalcout) from 表名；

  复制代码

4.9 分组查询（group by）

1. --查询users表中的记录，按照性别分组，查询男，女的体育成绩平均分
2. select gender,avg(PE) from users group by gender;
4. --查询users表中的记录，按照性别分组,分别查询男、女的体育成绩平均分,人数
5. select gender,avg(PE),count(id) from users group by gender;
7. --查询users表中的记录, 按照性别分组,分别查询男、女的体育成绩平均分,人数 要求:分数低于60分的人，不参与分组
8. select gender,avg(PE),count(id) from users where PE>60 group by gender;
10. --查询users表中的记录,按照性别分组,分别查询男、女的体育成绩平均分,人数 要求:分数低于60分的人，不参与分组,分组之后,人数要大于2个人
11. select gender,avg(PE),count(id) from users where PE>60 group by gender having count(id)>2;

复制代码

4.10 分页查询（limit）

   注意：第一条记录的索引是0

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

复制代码

4.11 内毗连查询

如果查询数据的来源来自多张表，则必须对这些表进行毗连查询，毗连是把差别表的记录连到一起的最普遍的方法，通过毗连查询可将多个表作为一个表进行处理，毗连查询分为内毗连和外毗连
语法格式

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

复制代码

e.g 有两张表：user表和city表
user表:
idname001小红002小蓝003小白004小黄005小绿006小青 city表：
idaddress001深圳002广州003北京004上海005汕头006潮州007揭阳 重合的部分就叫做内毗连查询,比方下面过滤条件指的就是当两个表的id相等时才符合毗连查询的条件

• 隐式内毗连
  
  1. select user.name,city.address
  2. from user,city
  3. where user.id = city.id;

  复制代码
  结果为：
     nameaddress小红深圳小蓝广州小白北京小黄上海效率汕头小青潮州
  
• 显式内毗连
  
  1. select user.name,city.address
  2. from user inner join city
  3. on user.id = city.id;

  复制代码

4.12 外毗连查询

外毗连查询分为左外毗连查询和右外毗连查询
语法

1. --左外连接
2. select 字段1，字段2..
3. from 表1 left outer join 表2 on 过滤条件;
5. --右外连接
6. select 字段1，字段2..
7. from 表1 right outer join 表2 on 过滤条件；

复制代码

  左外毗连和右外毗连有一点区别：
  左外毗连：是表1和表2的交集再并上表1的其他数据
  右外毗连：是表1和表2的交集再并上表2的其他数据
  e.g: 上面两张表的左外链接结果

1. select user.name,city.address
2. from city left outer join user
3. on user.id = city.id;

复制代码

结果为:
nameaddress小红深圳小蓝广州小白北京小黄上海效率汕头小青潮州NULL揭阳 简朴点说就是求交集之后并上city的其他数据，没有匹配的为NULL
右外毗连结果：

1. select user.name,city.address
2. from city right outer join user
3. on user.id = city.id;

复制代码

结果为：
nameaddress小红深圳小蓝广州小白北京小黄上海效率汕头小青潮州 简朴点说就是求交集之后并上user的其他数据，没有匹配的为NULL
4.13 子查询

   当我们进⾏语句查询的时候，总会遇到我们需要的条件需要通过另⼀个查询语句查询出来后才能进⾏，就是说A 查询语句需要依靠B 查询语句的查询结果，B 查询就是⼦查询，A 查询语句就是主查询，⼀个SQL语句可以包罗多个⼦查询。
  语法

1. select username
2. from user
3. where age =(
4.         select avg(age)
5.     from userInfo
6. )

复制代码

比方：要查询工资大于10号部门的均匀工资的非10号部门的员工信息
查询10号部门的均匀工资

1. select avg(sal) from emp where deptno = 10;

复制代码

那么工资大于10号部门的均匀工资的非10号部门的员工信息为

1. select * from emp
2. where deptno!=10 and sal>(
3.         select avg(sal)
4.     from emp
5.     where deptno = 10;
6. )

复制代码

一些子查询的实例
查询在2022年8月9日贩卖的产物信息

1. select *
2. from dbo.product
3. where pno in (
4.         select pno from dbo.prd
5.     where odate = '2022/'
6. )

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。

---

欢迎光临 ToB企服应用市场:ToB评测及商务社交产业平台 (https://dis.qidao123.com/)

Powered by Discuz! X3.4