【建议收藏！！！】—SQL常用基本语句总结

最新推荐文章于 2024-04-18 15:35:27 发布

置顶 Gin_2000

最新推荐文章于 2024-04-18 15:35:27 发布

阅读量1w

点赞数 22

分类专栏： MySQL 数据库文章标签： mysql 数据库

本BLOG上原创文章未经本人许可，不得用于商业用途。转载请注明出处，否则保留追究法律责任的权利。

本文链接：https://blog.csdn.net/Gin_2000/article/details/118660273

版权

MySQL 同时被 2 个专栏收录

7 篇文章 1 订阅

订阅专栏

数据库

2 篇文章 0 订阅

订阅专栏

文章目录

一、DQL(数据查询语言)

DQL：Data Query Language
数据查询语言（凡是带有select关键字的都是查询语句）
select…

1、基本查询

（1）查询所有列

SELECT * FROM emp;

（2）查询指定列

SELECT empmo, ename, mgr FROM emp;

（3）WHERE 子句条件查询

SELECT * FROM emp WHERE job='CLERK';

说明：上节所述运算符都可在 WHERE 子句中使用,举例如下：

1）使用 like 操作符

%表示一个或多个字符，
_ 表示一个字符，
[charlist]表示字符列中的任何单一字符，
[^charlist]或者[!charlist]不在字符列中的任何单一字符。

SELECT * FROM emp WHERE ename like '[^SAD]__T%';

2）在 where 条件中使用 in

SELECT * FROM emp WHERE job IN ('CLERK','ANALYST');

3）查询字段内容为空/非空的语句

SELECT * FROM emp WHERE mgr IS NULL | IS NOT NULL;

4）使用逻辑操作符号

SELECT * FROM emp WHERE (sal>500 or job='MANAGE') and ename like 'J%';

（4）字段控制查询

1）去重（DISTINCT）

SELECT DISTINCT sal FROM emp;

2）相加或拼接（+）

用在数值型上为相加，用在字符串型上为拼接

SELECT sal+comm FROM emp;

3）别名

SELECT *,ifnull(sal+comm) as totalFROM emp as e;

说明：用在字段和表名上，as 可省略。

（5）排序（ORDER BY）

SELECT * FROM emp ORDER BY deptno, sal DESC;

注：asc 升序；desc 降序

（6）聚合函数

聚合函数是用来做纵向运算的函数。有 count（个数）、sum（求和）、avg（平均数）、max（最大值）、min（最小值）等，如：

SELECT COUNT(*) FROM emp;

（7）分组（GROUP BY）

SELECT deptno,SUM(sal) FROM emp WHERE sal>1500 group by deptno HAVING SUM(sal)<9000;

说明：WHERE 是对分组前记录的过滤，如果某行记录不符合条件，则改行记录不会参与分组；而 HAVING 是对分组后记录的过滤。

（8）限制结果范围（LIMIT、OFFSET）

1） LIMIT 可单独使用，如：

从第一条（起始行 0 开始）开始，查询 5 条数据：

SELECT * FROM emp LIMIT 0,5;

2） LIMIT 和 OFFSET 一起使用（Mysql5 以后版本）

从第三条（起始行 0 开始）开始，查询 5 条数据：

SELECT * FROM emp LIMIT 5 OFFSET 2;

等同于：

SELECT * FROM emp LIMIT 2,5;

注意：不是LIMIT 5,2

2、多表查询

（1）合并结果集（纵向合并）

用于把两个SELECT语句的查询结果合并到一起。

UNION ：去掉重复记录
UNION ALL ：不去除重复记录

SELECT * FROM t1 UNION SELECT * FROM t2;

要求：t1、t2 列数，列类型必须相同。

（2）连接查询（横向扩展）

SELECT * FROM emp e,dept d where e.deptno=d.deptno;

这其实是内连接查询，等同于下面内连接的示例：

1）内连接（INNER JOIN）

内连接之等值连接

select 
	e.ename,d.dname
from
	emp e
inner join
	dept d
on
	e.deptno = d.deptno;
								// 条件是等量关系，所以被称为等值连接。
								//inner可以省略（带着inner可读性更好！一眼就能看出来是内连接）

内连接之非等值连接

select 
	e.ename, e.sal, s.grade
from
	emp e
join
	salgrade s
on
	e.sal between s.losal and s.hisal; 
												// 条件不是一个等量关系，称为非等值连接。

内连接之自连接

select 
	a.ename as '员工名', b.ename as '领导名'
from
	emp a
join
	emp b
on
	a.mgr = b.empno; //员工的领导编号 = 领导的员工编号

2）外连接（OUTER JOIN）

<1> 左连接（LEFT JOIN）

left join,（或left outer join:在 Mysql 中两者等价，推荐使用 left join.）。左连接从左表(A)产生一套完整的记录,与匹配的记录(右表(B)) .如果没有匹配,右侧将包含 null。

SELECT * FROM a LEFT JOIN b ON a.id=b.aid;

<2> 右连接（RIGHT JOIN）

同left join相反。

SELECT * FROM a RIGHT JOIN b ON a.id=b.aid;

<3> 全连接（FULL JOIN）

全连接产生的所有记录（双方匹配记录）在表 A 和表 B。如果没有匹配,则对面将包含 null。 Mysql 不支持全连接，但是可以通过left join + union + right join模拟实现：

SELECT * **FROM** a **LEFT** JOIN b **ON** a.id = b.aid

UNION

SELECT * FROM a RIGHT JOIN b ON a.id= b.aid;

3）交叉连接（笛卡尔积）

交叉连接，得到的结果是两个表的乘积，即笛卡尔积；

笛卡尔（Descartes）乘积又叫直积。假设集合 A={a,b}，集合 B={0,1,2}，则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1), (b,2)}。可以扩展到多个集合的情况。类似的例子有，如果 A 表示某学校学生的集合，B 表示该学校所有课程的集合，则 A 与 B 的笛卡尔积表示所有可能的选课情况。

SELECT * FROM A CROSS JOIN B;

4）自然连接（NATURAL JOIN）

使用 NATURAL JOIN 时，MySQL 将表中具有相同名称的字段自动进行记录匹配，而这些同名字段类型可以不同。因此，NATURAL JOIN 不用指定匹配条件。 NATURAL JOIN 默认是同名字段完全匹配的 INNER JOIN，也可以使用 LEFT JOIN 或 RIGHT JOIN。

SELECT * FROM A NATURAL JOIN B;

SELECT * FROM A NATURAL LEFT JOIN B;

SELECT * FROM A NATURAL RIGHT JOIN B;

（3）子查询（嵌套查询）

1) 什么是子查询？

select语句中嵌套select语句，被嵌套的select语句称为子查询。

2) 子查询都可以出现在哪里呢？

select
	..(select).
from
	..(select).
where
	..(select).

3) where子句中的子查询

案例：找出比最低工资高的员工姓名和工资？
	select 
		ename,sal
	from
		emp 
	where
		sal > min(sal);

	ERROR 1111 (HY000): Invalid use of group function
	where子句中不能直接使用分组函数。

实现思路：
	第一步：查询最低工资是多少
		select min(sal) from emp;
		+----------+
		| min(sal) |
		+----------+
		|   800.00 |
		+----------+
	第二步：找出>800的
		select ename,sal from emp where sal > 800;
	
	第三步：合并
		select ename,sal from emp where sal > (select min(sal) from emp);
		+--------+---------+
		| ename  | sal     |
		+--------+---------+
		| ALLEN  | 1600.00 |
		| WARD   | 1250.00 |
		| JONES  | 2975.00 |
		| MARTIN | 1250.00 |
		| BLAKE  | 2850.00 |
		| CLARK  | 2450.00 |
		| SCOTT  | 3000.00 |
		| KING   | 5000.00 |
		| TURNER | 1500.00 |
		| ADAMS  | 1100.00 |
		| JAMES  |  950.00 |
		| FORD   | 3000.00 |
		| MILLER | 1300.00 |
		+--------+---------+

4) from子句中的子查询

注意：from后面的子查询，可以将子查询的查询结果当做一张临时表。（技巧）

案例：找出每个岗位的平均工资的薪资等级。

第一步：找出每个岗位的平均工资（按照岗位分组求平均值）
	select job,avg(sal) from emp group by job;
	+-----------+-------------+
	| job       | avgsal      |
	+-----------+-------------+
	| ANALYST   | 3000.000000 |
	| CLERK     | 1037.500000 |
	| MANAGER   | 2758.333333 |
	| PRESIDENT | 5000.000000 |
	| SALESMAN  | 1400.000000 |
	+-----------+-------------+t表

第二步：克服心理障碍，把以上的查询结果就当做一张真实存在的表t。
mysql> select * from salgrade; s表
+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+
t表和s表进行表连接，条件：t表avg(sal) between s.losal and s.hisal;
	
	select 
		t.*, s.grade
	from
		(select job,avg(sal) as avgsal from emp group by job) t
	join
		salgrade s
	on
		t.avgsal between s.losal and s.hisal;
	
	+-----------+-------------+-------+
	| job       | avgsal      | grade |
	+-----------+-------------+-------+
	| CLERK     | 1037.500000 |     1 |
	| SALESMAN  | 1400.000000 |     2 |
	| ANALYST   | 3000.000000 |     4 |
	| MANAGER   | 2758.333333 |     4 |
	| PRESIDENT | 5000.000000 |     5 |
	+-----------+-------------+-------+

5) select后面出现的子查询(只需了解)

案例：找出每个员工的部门名称，要求显示员工名，部门名？
	select 
		e.ename,e.deptno,(select d.dname from dept d where e.deptno = d.deptno) as dname 
	from 
		emp e;
+--------+--------+------------+
| ename  | deptno | dname      |
+--------+--------+------------+
| SMITH  |     20 | RESEARCH   |
| ALLEN  |     30 | SALES      |
| WARD   |     30 | SALES      |
| JONES  |     20 | RESEARCH   |
| MARTIN |     30 | SALES      |
| BLAKE  |     30 | SALES      |
| CLARK  |     10 | ACCOUNTING |
| SCOTT  |     20 | RESEARCH   |
| KING   |     10 | ACCOUNTING |
| TURNER |     30 | SALES      |
| ADAMS  |     20 | RESEARCH   |
| JAMES  |     30 | SALES      |
| FORD   |     20 | RESEARCH   |
| MILLER |     10 | ACCOUNTING |
+--------+--------+------------+

//错误：ERROR 1242 (21000): Subquery returns more than 1 row
select 
	e.ename,e.deptno,(select dname from dept) as dname
from
	emp e;

注意：对于select后面的子查询来说，这个子查询只能一次返回1条结果，
多于1条，就报错了。！

说明：

ALL：满足所有
ANY：满足任意一个

SELECT * FROM emp WHERE (job,sal) IN

(SELECT job,sal FROM emp WHERE ename='JAMES');

二、DML(数据操作语言)

DML: Data Manipulation Language

数据操作语言（凡是对表当中的数据进行增删改的都是DML）
insert、delete、update
insert 增
delete 删
update 改

（1）插入数据

insert into 表名(字段名1,字段名2,字段名3...) values(值1,值2,值3);

（2）更新数据

没有条件限制会导致所有数据全部更新。

update 表名 set 字段名1=值1,字段名2=值2,字段名3=值3...

满足条件更新

update 表名 set 字段名1=值1,字段名2=值2,字段名3=值3... where 条件;

（3）删除数据

注意：没有条件，整张表的数据会全部删除！

delete from 表名;

满足条件删除

delete from 表名 where 条件;

注：条件必须是一个 boolean 类型的值或表达式，如

delete from t_user where id = 2;

可用在 where 子句中的比较运算符有：

运算符	说明	运算符	说明
=	等于	!= 或 <>	不等于
>	大于	>=	大于等于
<	小于	<=	小于等于
is null	为空，注：不能使用=null 判断是否为空	is not null	非空，注：不能使用!=null 判断是否非空
and	且	or	或
not	非	in(…)	在…中；加 not，相反
between … and …	在…和…之间；加 not，相反	like	模糊匹配，加 not 相反
REGEXP	正则表达式

三、DDL(数据定义语言)

DDL 定义数据库库、表、表结构、表字段等。

数据定义语言
凡是带有create、drop、alter的都是DDL。
DDL主要操作的是表的结构。不是表中的数据。不是表中的数据。不是表中的数据。
create：新建，等同于增
drop：删除
alter：修改
这个增删改和DML不同，这个主要是对表结构进行操作。

1、库

（1）查看数据库

show databases;

（2）切换数据库

use <dbname>;

（3）创建数据库

create databases 数据库名;

或

create database if not exists 数据库名 CHARACTER SET utf8mb4;

（4）删除数据库

drop database <dbname>;

或

drop database if exists <dbname>;

（5）修改数据库字符集

alter database <dbname> CHARACTER SET utf8mb4;

2、表

（1）创建表

create table <tablename> (<column1Name> <column1Type>, <column2Name column2Type>, …);

create table 表名(字段名1 数据类型, 字段名2 数据类型, 字段名3 数据类型);
//表名：建议以t_ 或者 tbl_开始，可读性强。见名知意。
//字段名：见名知意。
//表名和字段名都属于标识符。

（2）查看表

show tables;

（3）查看表结构

desc <tablename>;

（4）删除表

drop table <tablename>;

（5）修改表

1）添加列

alter table <tablename> add(<columnName> <columnType>);

2）修改列数据类型

alter table <tablename> modify <columeName> <newColumnType> default <defaultValue>;

3）修改列名及列类型

alter table <tablename> change <oldColumnName> <newColumnName> <newColumnType>;

4）删除列

alter table <tablename> drop <columnName>;

5）修改表名

alter table <oldTablename> rename to <newTablename>;

四、DCL(数据控制语言)

DCL 定义数据访问权限和安全级别，如对用户的创建及授权等。

DCL：data control language
是数据控制语言。
例如：授权grant、撤销权限revoke…

（1）使用 root 管理员登陆 mysql

mysql -uroot -proot;

不安全，推荐：mysql -uroot -p 回城，再输密码

（2）创建新用户：

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

说明：@后可指定：

‘%’ – 所有情况都能访问
‘localhost’ – 本机才能访问
‘123.126.24.254’ – 指定 ip 才能访问

（3）修改密码

命令:

SET PASSWORD FOR 'username'@'host' = **PASSWORD**('newpwd');

如果是当前登陆用户用:

SET PASSWORD = PASSWORD("newpwd");

例子:

SET PASSWORD FOR ‘admin’@’%’ = PASSWORD(“admin123”);

（4）给用户添加权限

GRANT ALL PRIVILEGES ON dbname.* TO ‘username’@’%’;

说明：

all 可以替换为 insert,select,delete,update,create,drop 等细粒度权限；
dbname.*:数据库.表，这里指数据库 dbname 的所有表，可以指定具体表，如 hiov.device；
‘username’@’%’：用户名@访问地址，这里指 username 在所有 IP 地址都可访问。 GRANT ALL PRIVILEGES ON dbname.* TO ‘username@’%’ WITH GRANT OPTION;
WITH GRANT OPTION：权限传递，使用这个子句时将允许用户 username @’%’将其权限分配给他人。

（5）撤销授权：

方法一：

revoke create,alter,drop on dbname.* from ‘username’@’%’;

方法二：将 username 用户可访问地址修改为 localhost

update mysql.user set host='localhost' where user='username';

（6）删除用户：

方法一：

DELETE FROM mysql.user WHERE user = ’username’;

方法二：

DROP user username@ip;

（7）使生效：

FLUSH PRIVILEGES;

（8）查看某个用户授权

show grants for username@localhost;

username@localhost：不加@localhost，默认为 root@%

（9）查询用户授权

SELECT user,host FROM mysql.user;

user:用户名；host:所属地址；SELECT *可以查出所有权限信息）

Gin_2000

关注

22
点赞
踩
25

收藏

觉得还不错? 一键收藏
15
评论
【建议收藏！！！】—SQL常用基本语句总结

文章目录一、DQL(数据查询语言)1、基本查询（1）查询所有列（2）查询指定列（3）WHERE 子句条件查询1）使用 like 操作符2）在 where 条件中使用 in3）查询字段内容为空/非空的语句4）使用逻辑操作符号（4）字段控制查询1）去重（DISTINCT）2）相加或拼接（+）3）别名（5）排序（ORDER BY）（6）聚合函数（7）分组（GROUP BY）（8）限制结果范围（LIMIT、OFFSET）1） LIMIT 可单独使用，如：2） LIMIT 和 OFFSET 一起使用（M
复制链接

扫一扫