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

待我称王封你为后i 2022-10-19 14:58 313阅读 0赞

### 文章目录 ###

*   *  一、DQL(数据查询语言)
     *   *  1、基本查询
         *   *  （1）查询所有列
             *  （2）查询指定列
             *  （3）WHERE 子句条件查询
             *   *  1） 使用 like 操作符
                 *  2） 在 where 条件中使用 in
                 *  3） 查询字段内容为空/非空的语句
                 *  4） 使用逻辑操作符号
             *  （4）字段控制查询
             *   *  1） 去重（DISTINCT）
                 *  2） 相加或拼接（+）
                 *  3） 别名
             *  （5）排序（ORDER BY）
             *  （6）聚合函数
             *  （7）分组（GROUP BY）
             *  （8）限制结果范围（LIMIT、OFFSET）
             *   *  1） LIMIT 可单独使用，如：
                 *  2） LIMIT 和 OFFSET 一起使用（Mysql5 以后版本）
         *  2、多表查询
         *   *  （1）合并结果集（纵向合并）
             *  （2）连接查询（横向扩展）
             *   *  1）内连接（INNER JOIN）
                 *  2）外连接（OUTER JOIN）
                 *  3）交叉连接（笛卡尔积）
                 *  4）自然连接（NATURAL JOIN）
             *  （3）子查询（嵌套查询）
     *  二、DML(数据操作语言)
     *   *   *  （1）插入数据
             *  （2）更新数据
             *  （3）删除数据
     *  三、DDL(数据定义语言)
     *   *  1、库
         *   *  （1）查看数据库
             *  （2）切换数据库
             *  （3）创建数据库
             *  （4）删除数据库
             *  （5）修改数据库字符集
         *  2、表
         *   *  （1）创建表
             *  （2）查看表
             *  （3）查看表结构
             *  （4）删除表
             *  （5）修改表
             *   *  1） 添加列
                 *  2） 修改列数据类型
                 *  3） 修改列名及列类型
                 *  4） 删除列
                 *  5） 修改表名
     *  四、DCL(数据控制语言)
     *   *   *  （1）使用 root 管理员登陆 mysql
             *  （2）创建新用户：
             *  （3）修改密码
             *  （4）给用户添加权限
             *  （5）撤销授权：
             *  （6）删除用户：
             *  （7）使生效：
             *  （8）查看某个用户授权
             *  （9）查询用户授权

## 一、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） #####

1.  **内连接之等值连接**

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

1.  **内连接之非等值连接**

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

1.  **内连接之自连接**

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后面出现的子查询(只需了解)**

说明：

*  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 子句中的比较运算符有：

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

## 三、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");

*例子:*

1.  **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;