MySQL基础-SQL介绍

素颜马尾好姑娘i 2024-05-24 01:50 53阅读 0赞

> 本文介绍MySQL中SQL通用语法，包括：DDL，DML，DQL，DCL

#### 文章目录 ####

*  SQL分类
 *  DDL（数据定义语言）
 *   *  数据库操作
     *  表操作
 *  DML（数据操作语言）
 *   *  添加数据
     *  更新和删除数据
 *  DQL
 *   *  基础查询
     *  条件查询
     *  聚合查询（聚合函数）
     *  分组查询
     *  排序查询
     *  分页查询
 *  DCL（数据控制语言）
 *   *  管理用户
     *  权限控制

参考文档：

*  [链接1][1]
 *  [链接2][2]
 *  [视频链接][Link 1]

## SQL分类 ##

MySQL中SQL语句不区分大小写，关键字建议使用大写

<table> 
 <thead> 
  <tr> 
   <th>分类</th> 
   <th>全称</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>DDL</td> 
   <td>Data Definition Language</td> 
   <td>数据定义语言，用来定义数据库对象（数据库，表，字段）</td> 
  </tr> 
  <tr> 
   <td>DML</td> 
   <td>Data Manipulation Language</td> 
   <td>数据操作语言，用来对数据库表中的数据进行增删改</td> 
  </tr> 
  <tr> 
   <td>DQL</td> 
   <td>Data Query Language</td> 
   <td>数据查询语言，用来查询数据库中表的记录</td> 
  </tr> 
  <tr> 
   <td>DCL</td> 
   <td>Data Control Language</td> 
   <td>数据控制语言，用来创建数据库的用户以及权限的管理</td> 
  </tr> 
 </tbody> 
</table>

## DDL（数据定义语言） ##

### 数据库操作 ###

查询所有数据库：
    SHOW DATABASES;
    
    查询当前数据库：
    SELECT DATABASE();
    
    创建数据库：
    CREATE DATABASE [ IF NOT EXISTS ] 数据库名 [ DEFAULT CHARSET 字符集] [COLLATE 排序规则 ];
    
    删除数据库：
    DROP DATABASE [ IF EXISTS ] 数据库名;
    
    使用数据库：
    USE 数据库名;

### 表操作 ###

查询当前数据库所有表：
    SHOW TABLES;
    
    查询表结构：
    DESC 表名;
    
    查询指定表的建表语句：
    SHOW CREATE TABLE 表名;
    
    表修改操作：
    ALTERTABLE 表名 ADD/MODIFY/CHANGE/DROP/RENAME TO...;
    添加字段：ALTER TABLE 表名 ADD 字段名 类型(长度) [COMMENT 注释] [约束];
    修改数据类型：ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度);
    修改字段名和字段类型：ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新数据类型(长度) [COMMENT 注释] [约束];
    修改表名：ALTER TABLE 表名 RENAME TO 新表名;
    删除字段：ALTER TABLE 表名 DROP 字段名;
    
    删除表：
    DROP TABLE 表名;
    
    删除指定表，并重新创建该表：
    TRUNCATE TABLE 表名;

CREATE TABLE 表名称(
    		字段1 类型 [COMMENT 字段1注释],
    		字段2 类型 [COMMENT 字段2注释],
    		...
    		字段n 类型 [COMMENT 字段n注释]
    )[COMMENT 表注释];

## DML（数据操作语言） ##

### 添加数据 ###

指定字段插入数据：
    INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...);
    全部字段：
    INSERT INTO 表名 VALUES (值1, 值2, ...);
    
    批量添加数据：
    INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...);
    INSERT INTO 表名 VALUES (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...);

注意事项：

*  插入数据时，指定的字段顺序需要与值的顺序是一一对应的
 *  字符串和日期类型数据应该包含在引号中
 *  插入的数据大小应该在字段的规定范围内

### 更新和删除数据 ###

修改指定字段的数据：
    UPDATE 表名 SET 字段名1 = 值1, 字段名2 = 值2, ... [ WHERE 条件 ];
    比如：
    UPDATE emp SET name = 'Jack' WHERE id = 1;
    
    删除数据：
    DELETE FROM 表名 [ WHERE 条件 ];

注意：

*  修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的所有数据
 *  DELETE语句的条件可以有也可以没有，如果不加上WHERE的话，是删除整张表的数据。
 *  DELETE语句不能删除某一个字段的值，但可以使用UPDATE。

## DQL ##

语法：

SELECT
    	字段1,
    	字段2,
    	...
    FROM
    	表1,
    	表2,
    	...
    WHERE
    	条件1,
    	条件2,
    	...
    GROUP BY
    	分组字段1,
    	分组字段2,
    	...
    HAVING
    	分组后条件1,
    	分组后条件2,
    	...
    ORDER BY
    	排序字段1,
    	排序字段2,
    	...
    LIMIT
    	分页参数

### 基础查询 ###

查询多个字段：  
`SELECT 字段1, 字段2, 字段3, ... FROM 表名;`  
`SELECT * FROM 表名;`

设置别名：  
`SELECT 字段1 [ AS 别名1 ], 字段2 [ AS 别名2 ], 字段3 [ AS 别名3 ], ... FROM 表名;`  
`SELECT 字段1 [ 别名1 ], 字段2 [ 别名2 ], 字段3 [ 别名3 ], ... FROM 表名;`

去除重复记录：  
`SELECT DISTINCT 字段列表 FROM 表名;`

转义：  
`SELECT * FROM 表名 WHERE name LIKE '/_张三' ESCAPE '/'`  
/ 之后的\_不作为通配符

### 条件查询 ###

语法：  
`SELECT 字段列表 FROM 表名 WHERE 条件列表;`  
![在这里插入图片描述][cdd1fd0576364688a4d8833c40e15f6d.jpeg_pic_center]  
![在这里插入图片描述][36487db44eb64720b40fbd72731353b2.jpeg_pic_center]

-- 年龄等于30
    select * from employee where age = 30;
    
    -- 年龄小于30
    select * from employee where age < 30;
    
    -- 小于等于
    select * from employee where age <= 30;
    
    -- 没有身份证
    select * from employee where idcard is null or idcard = '';
    
    -- 有身份证
    select * from employee where idcard;
    select * from employee where idcard is not null;
    
    -- 不等于
    select * from employee where age != 30;
    
    -- 年龄在20到30之间
    select * from employee where age between 20 and 30;
    select * from employee where age >= 20 and age <= 30;
    
    -- 下面语句不报错，但查不到任何信息
    select * from employee where age between 30 and 20;
    
    -- 性别为女且年龄小于30
    select * from employee where age < 30 and gender = '女';
    
    -- 年龄等于25或30或35
    select * from employee where age = 25 or age = 30 or age = 35;
    select * from employee where age in (25, 30, 35);
    
    -- 姓名为两个字
    select * from employee where name like '__';
    
    -- 身份证最后为X
    select * from employee where idcard like '%X';

### 聚合查询（聚合函数） ###

语法：`SELECT 聚合函数(字段) FROM 表名;`

常见聚合函数：  
![在这里插入图片描述][1ccff2af5249413f84cf32bb72e657e2.jpeg_pic_center]

#统计该企业员工数量
    SELECT COUNT(*) FROM emp;
    #或
    SELECT COUNT(字段) FROM emp; 
    
    #统计该企业员工的平均年龄
    SELECT ROUND(AVG(age),0) FROM emp; #这里的ROUND是四舍五入，其中0是保留几位小数，从0开始。
    
    #统计该企业员工最大年龄
    SELECT MAX(age) FROM emp;
    
    #统计该企业员工最小年龄
    SELECT MIN(age) FROM emp;
    
    #统计北京市员工年龄之和以及平均值
    SELECT workaddress as '工作地址',SUM(age) as '北京地区年龄之和',AVG(age) as '北京地区年龄平均值' FROM emp WHERE workaddress = '北京市';

注意：

*  所有的NULL值不参与聚合函数的计算。

### 分组查询 ###

语法：`SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 字段名 [HAVING 过滤条件]`  
where 和 having 的区别：

*  执行时机不同：where是分组之前进行过滤，不满足where条件不参与分组；having是分组后对结果进行过滤。
 *  判断条件不同：where不能对聚合函数进行判断，而having可以。

例子：

-- 根据性别分组，统计男性和女性数量（只显示分组数量，不显示哪个是男哪个是女）
    select count(*) from employee group by gender;
    
    -- 根据性别分组，统计男性和女性数量
    select gender, count(*) from employee group by gender;
    
    -- 根据性别分组，统计男性和女性的平均年龄
    select gender, avg(age) from employee group by gender;
    
    -- 年龄小于45，并根据工作地址分组
    select workaddress, count(*) from employee where age < 45 group by workaddress;
    
    -- 年龄小于45，并根据工作地址分组，获取员工数量大于等于3的工作地址
    select workaddress, count(*) address_count from employee where age < 45 group by workaddress having address_count >= 3;

注意事项：

*  执行顺序：where > 聚合函数 > having
 *  分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义

### 排序查询 ###

语法：  
`SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1, 字段2 排序方式2;`

排序方式：

*  ASC: 升序（默认）
 *  DESC: 降序

注意：

*  如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序。

#根据年龄对员工进行升序排序
    SELECT * FROM emp ORDER BY age ASC;
    
    #根据入职时间，对员工进行降序排序
    SELECT * FROM emp ORDER BY entrydate DESC;
    
    #根据年龄对员工进行升序排序，年龄相同再根据入职时间降序排序
    SELECT * FROM emp ORDER BY age ASC, entrydate DESC;

### 分页查询 ###

语法：`SELECT 字段1, 字段2, ... FROM 表名 LIMIT 起始索引, 查询的记录条数;`  
注意：

*  起始索引从0开始，起始索引=（查询页码 - 1）\* 查询的记录条数。
 *  分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT。
 *  如果查询的是第一页的数据，则可以忽略起始索引。

例子：

#查询第16页员工数据，每页显示50条数据
    SELECT * FROM emp LIMIT 750, 50;
    
    #查询年龄为20，21，22，23岁的员工信息
    SELECT * FROM emp WHERE age BETWEEN 20 AND 23;
    
    #查询性别为男，年龄在30-50岁（含）且名字为三个字的员工
    SELECT name,age FROM emp WHERE age BETWEEN 30 AND 50 AND name LIKE '___';
    
    #统计员工表中，年龄小于50的男性员工和女性员工人数。
    SELECT gender,COUNT(*) FROM emp WHERE age < 50 GROUP BY gender; 
    
    #查询所有年龄小于等于35岁员工的姓名和年龄，并对查询结果根据年龄升序排序，如果年龄相同按入职时间降序排序
    SELECT name,age FROM emp WHERE age <= 35 ORDER BY age, entrydate DESC;
    
    #查询性别为男，且年龄在30-50岁（含）以内的前20个员工信息，对结果按年龄升序排序，如果年龄相同按入职时间升序排序
    SELECT * FROM emp WHERE age BETWEEN 30 AND 50 ORDER BY age, entrydate LIMIT 20;

执行顺序

*  编写顺序：`SELECT -> FROM -> WHERE -> GROUP BY -> HAVING -> ORDER BY -> LIMIT`
 *  执行顺序：`FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY -> LIMIT`

## DCL（数据控制语言） ##

### 管理用户 ###

查询用户：  
`USE mysql;`  
`SELECT * FROM user;`

创建用户:  
`CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';`

修改用户密码：  
`ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';`

删除用户：  
`DROP USER '用户名'@'主机名';`

例子：

-- 创建用户test，只能在当前主机localhost访问
    create user 'test'@'localhost' identified by '123456';
    
    -- 创建用户test，能在任意主机访问
    create user 'test'@'%' identified by '123456';
    create user 'test' identified by '123456';
    
    -- 修改密码
    alter user 'test'@'localhost' identified with mysql_native_password by '1234';
    
    -- 删除用户
    drop user 'test'@'localhost';

注意事项

*  主机名可以使用 % 通配

### 权限控制 ###

![在这里插入图片描述][95712a6cc9944dfb98fbdc79aac4aa37.jpeg_pic_center]  
权限-查询权限  
语法：`SHOW GRANTS FOR '用户名'@'主机名';`

权限-授予权限  
语法：`GRANT 权限 ON 数据库名.表名 TO '用户名'@'主机名';`

权限-撤销权限  
语法：`REVOKE 权限 ON 数据库名.表名 FROM '用户名'@'主机名';`

注意：

*  多个权限之间，使用逗号分割。
 *  授权时，数据库名和表名可以使用’\*\*'进行通配，代表所有。

[1]: https://dhc.pythonanywhere.com/entry/share/?key=12e4a7324f68371db3984d93e26e458962a4f0bc188ec23ec70637a4f3b4d58f#h1-u57FAu7840u7BC7
[2]: https://blog.csdn.net/u011044503/article/details/127038524
[Link 1]: https://www.bilibili.com/video/BV1Kr4y1i7ru/?vd_source=374cc4b4145f10da7f304372ec06ba1d
[cdd1fd0576364688a4d8833c40e15f6d.jpeg_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/24/3f811cf0f40f418f89f8c8d8a27c4969.jpeg
[36487db44eb64720b40fbd72731353b2.jpeg_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/24/38506bd62d434d759b2bda5c088983ad.jpeg
[1ccff2af5249413f84cf32bb72e657e2.jpeg_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/24/744af1bd97b9454485ff6010e1b8900d.jpeg
[95712a6cc9944dfb98fbdc79aac4aa37.jpeg_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/24/78efa3a4cef74b1c9b0846754b24409a.jpeg