# sql学习

**Repository Path**: MirageClouds/sql

## Basic Information

- **Project Name**: sql学习
- **Description**: No description available
- **Primary Language**: Unknown
- **License**: Not specified
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 0
- **Forks**: 0
- **Created**: 2024-03-20
- **Last Updated**: 2024-04-12

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

# mysql学习

## sql通用语法

* SQL语句可以单行或多行书写，以分号结尾
* SQL语句可以使用空格/缩进来增强语句的可读性
* MySQL数据库的SQL语句不分大小写，关键字建议使用大写、
* 注释：
  * 单行注释 --注释内容 或 # 注释内容（MySQL特有）
  * 多行注释：/*注释内容*/
* SQL分类：
  * DDL：数据定义语言，用来定义数据库对象（数据库、表、字段）
  * DML：数据库操作语言，用来对数据表中的数据进行增删改
  * DQL：数据库查询语言，用来查询数据库中表的记录
  * DCL：数据控制语言，用来创建数据库用户、控制数据库访问权限

## DDL语句

### 数据库操作

* 查询：
  * 查询所有数据库：`SHOW DATABASES;`
  * 查询当前数据库：`SELECT DATABASE();`
* 创建：
  * `CREATE DATABASE [IF NOT EXISTS] 数据表名 [DEFAULT CHARSER 字符集][COLLATE 排序规则];`
  * `CREATE DATABSE 数据表名;`：这是最简单的创建数据库方式
  * IF NOT EXISTS ：如果这个数据库名称不存在，则创建，反正不创建（可省略）
  * DEFAULT CHARSER :指定数据库字符集（可省略，数据库有默认字符集）
  * COLLATE：指定数据库排序规则
* 删除：
  * `DROP DATABASE[IF EXISTS] 数据库名;`
  * IF EXISTA:如果存在即删除，反正不删除
* 使用:
  * `USE 数据库名;`

### 表操作（需要先通过USE指令进入数据库才能进行操作）

* 查询：

  * 查询当前数据库所有表：`SHOW TABLES;`
  * 查询表结构：`DESC 表名;`
  * 查询指定表的建表语句:`SHOW CREATE TABLE 表名;`

* 创建：

  * ~~~
    CREATE TABLE 表名(
    	字段1 字段1类型 [COMMENT 字段1注释],
    	字段2 字段2类型 [COMMENT 字段2注释],
    	字段3 字段3类型 [COMMENT 字段3注释]
    )[COMMENT 表注释];
    ~~~

* 操作：
  * 添加字段：`ALTER TABLE 表名 ADD 字段名 类型(长度)[COMMENT 注释][约束];`
  * 修改数据类型:`ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度);`
  * 修改字段名和字段类型：`ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度) [COMMENT 注释][约束];`
  
* 删除：

  * 删除字段：`ALTAR TABLE 表名 DROP 字段名;`

* 修改：
  
  * 修改表名：`ALEAR TABLE 表名 RENAME TO 新表名;`
* 删除表：
  * 删除表：`DROP TABLE[IF EXISTS]表名;`
  * 删除指定表，并重新创建该表：`TRUNCATE TABLE 表名;`
  * 在删除表时，表中的数据也会被删除

### DDL语句代码

~~~	sql
# 查询当前数据库
/*
    SHOW DATABASES;
*/
# 查询所有数据库
/*
    SELECT DATABASE();
 */
#创建一个简单的数据库
/*
    CREATE DATABASE IF NOT EXISTS demo;
    SHOW DATABASES;
 */
#删除一个数据库
/*
    DROP DATABASE demo;
    SHOW DATABASES;
*/
#进入一个数据库
/*
    USE demo;
    SELECT DATABASE();
 */
# 创建一个数据表
/*
    CREATE TABLE demo(
        demo INT COMMENT 'int测试',
        demo1 VARCHAR(64) COMMENT 'VARCHAR测试',
        demo2 INT COMMENT 'int测试',
        demo3 INT COMMENT 'int测试'
    );
*/
#查看数据表
/*
    SHOW TABLES;
 */
#向demo表中添加数据
/*
    ALTER TABLE demo ADD school VARCHAR(64);
    DESC demo;
*/
#修改demo表中的字段名
/*
    ALTER TABLE demo MODIFY school VARCHAR(255);
    DESC demo;
 */
#修改字段名和字段类型
/*
    ALTER TABLE demo CHANGE demo mirage VARCHAR(255) COMMENT '你好';
    DESC demo;
*/
#删除demo1字段
/*
    ALTER TABLE demo DROP demo1;
    DESC demo;
 */
#修改demo表的表名
/*
    ALTER TABLE demo RENAME TO clouds;
    DESC clouds;
*/
#删除clouds表，并重新创建该表
/*
    TRUNCATE TABLE clouds;
    DESC clouds;
 */
~~~

## DML语句

* 添加数据：
  * 给指定字段添加数据:`INSERT INTO 表名(字段1，字段2) VALUES(值1，值2);`
  * 给全部字段添加数据：`INSERT INFO 表名 VALUES(值1，值2);`
  * 给指定字段批量添加数据：`INSERT INFO 表名 (字段名1，字段名2) VALUES(值1，值2),(值1，值2);`
  * 给全部字段批量添加数据：`INSERT INFO 表名 VALUES(值1,值2)，(值1，值2);`
  * **注意**:
    * 插入数据时，指定的字段顺序需要与值的顺序
    * 字符串和日期型数据一个包含在引号中
    * 插入的数据大小，应该在字段的指定范围内
* 修改数据：
  * `UPDATA 表名 SET 字段名1 = 值1 ,字段名2 = 值2 [WHERE 条件];`
  * 注意：
    * 修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的数据
* 删除数据：
  * `DELETE FROM 表名 [WHERE 条件]`
  * 注意
    * DELETE 语句的条件可以有，也可以没有，如果没有条件，则会删除整张表中的所有数据
    * DELETE语句不能删除某一个字段的值(可以使用UPDATE)

### DML语句代码

~~~sql
USE demo;
#给mirage字段添加数据
/*
    INSERT INTO clouds(mirage, demo2, demo3, school)
    values ('1', 2, 3, '4');
*/
#修改mirage表中的数据
/*
    UPDATE demo.clouds SET mirage = 2 WHERE demo2 = 2;
 */
 #删除mirage表中的数据
/*
    DELETE FROM demo.clouds WHERE demo2 = 1;
 */
~~~

## DQL语句

* 语法：

  * 语法结构

    * ~~~sql
      SELECT
      	字段列表
      FROM
      	表名列表
      WHERE
      	条件列表
      GROUP BY
      	分组字段列表
      HAVNG
      	分组号条件列表
      ORDER BY
      	排序字段列表
      LIMIT
      	分页参数
      ~~~

* 基本查询：

  * 查询多个字段：
    * `SELECT 字段1，字段2，字段3 FROM 表名;`
    * `SELECT * FROM 表名;`
  * 设置别名：
    * `SELECT 字段1 [AS 别名] 字段2 [AS 别名] FROM 表名;`
  * 去除重复记录：
    * `SELECT DISTINCT 字段列表 FROM 表名;`

* 条件查询：

  * 语法：`SELECT 字段列表 FROM 表名 WHERE 条件列表`

  * 条件：

    * | 比较运算符 | 功能 |
      | ---------- | :--: |
      | >          | 大于 |
      | >=         |大于等于|
      | <          |小于|
      | <=         |小于等于|
      | =          |等于|
      | <>或!=     |不等于|
      |BETWEEN……AND……|在某个范围之内(含最小值、最大值)|
      |IN(……)|在in之后的列表中的值，多选一|
      |LINK 占位符|模糊匹配(_匹配单个字符，%匹配任意字符)|
      |IS NULL |是空|

    * | 逻辑运算符 | 功能                       |
      | :--------: | :------------------------- |
      | AND 或 &&  | 并且(多个条件同时成立)     |
      |  OR或\|\|  | 或者(多个条件任意一个成立) |
      |  NOT 或 !  | 非，不是                   |
  
* 聚合函数:

  * 介绍：将一列数据作为一个整体，进行纵向运算

  * 常见的聚合函数：

    * | 函数  | 功能     |
      | ----- | -------- |
      | count | 统计数量 |
      | max   | 最大值   |
      | min   | 最小值   |
      | avg   | 平均值   |
      | sum   | 求和     |

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

  * 注意：null不参与所有聚合运算

* 分组查询：

  * 语法：`SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组后过滤条件];`
  * where与having区别
    * 执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组;而having是分组后进行过滤的
    * 判断条件不同，where不能对聚合函数进行判断，而having可以
  * 注意：
    * 执行顺序：where>聚合函数>having
    * 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义
  
* 排序查询：

  * 语法：`SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1,字段2 排序方式2;`
  * 排序方式：
    * ASC:升序(默认)
    * DESC:降序
  * 注意：如果是多字段排序，当第一个字段相同时，才会根据第二个字段进行排序

* 分页查询：

  * 语法:`SELECT 字段列表 FROM 表名 LIMT 起始索引,查询记录数`
  * 注意：
    * 起始索引重零开始，起始索引 = (查询页码 - 1) * 每页显示记录数
    * 分页查询是数据库的方言，不同的数据库有不同的实现，MySQL是LIMIT
    * 如果查询的是第一页数据，起始索引可以省略。直接写成LIMIT 10

* 执行顺序：

  * FROM > WHERE > GROUP BY > SELECT >ORDER BY > LIMIT

### DQL语句代码

~~~mysql
/*use demo;*/
#查询全部字段
/*SELECT * FROM clouds;*/
#设置别名
/*SELECT mirage AS name FROM clouds;*/
#去除重复记录
/*SELECT DISTINCT *FROM clouds;*/
#条件查询
/*SELECT * FROM clouds WHERE demo3 > 1;*/
#聚合函数
/*SELECT COUNT(clouds.mirage) FROM clouds;*/
#分组查询
/*SELECT COUNT(*) FROM clouds GROUP BY mirage;*/
#排序查询
/*SELECT * FROM clouds ORDER BY mirage desc , demo2 desc ;*/
#分页查询
/*SELECT * FROM userinfo LIMIT 0,10;*/
~~~



## DCL语句

### 用户操作

* 查询用户：查询mysql数据库下的user表

  * ~~~mysql
    USE mysql;
    SELECT * FROM user;
    ~~~

* 创建用户：

  * 创建本地访问数据库：

    * ~~~mysql	
      CREATE USER '用户名'@'localhost' IDENTIFIED BY '密码';
      ~~~

  * 创建任意服务器访问该数据库：

    * ~~~mysql
      CREATE USER '用户名'@'%' IDENTIFIED BY '密码'
      ~~~

* 修改用户密码

  * ~~~mysql
    ALTER USER '用户名'@'localhost' IDENTIFIED WITH mysql_native_password BY '密码';
    ~~~

* 删除用户

  * ~~~mysql
    DROP USER '用户名'@'localhost';
    ~~~

### 权限控制

* 常用权限：

  * | 权限               | 说明               |
    | ------------------ | ------------------ |
    | ALL,ALL PRIVILEGES | 所有权限           |
    | SELECT             | 查询数据           |
    | INSERT             | 插入数据           |
    | UPDATA             | 修改数据           |
    | DELETE             | 删除数据           |
    | ALTER              | 修改表             |
    | DROP               | 删除数据库/表/视图 |
    | CREATE             | 创建数据库/表      |

* 查询权限：`SHOW GRANTS FOR '用户名'@'主机名';`

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

* 撤销权限:`REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';`

* 注意：

  * 多个权限直接使用逗号分隔
  * 授权时，数据库名和表名可以使用 * 进行通配，代表所有

### DCL语句代码

~~~mysql
#查询用户
USE mysql;
SELECT * FROM user;
#本机创建用户
CREATE USER 'mirage'@'localhost' IDENTIFIED BY '123456';
#任意服务器访问该数据库
CREATE USER 'clouds'@'%' IDENTIFIED BY '123456';
#修改用户密码
ALTER USER 'mirage'@'localhost' IDENTIFIED WITH mysql_native_password by '123456';
#删除用户
DROP USER 'mirage'@'localhost';
#查询权限
SHOW GRANTS FOR 'clouds'@'%';
#授予权限
GRANT ALL ON test.demo TO 'root'@'localhost';
#撤销权限
REVOKE ALL ON test.demo FROM 'root'@'localhost';
~~~

## 函数

### 字符串函数

* 常用函数

  * | 函数                     | 功能                                                      |
    | ------------------------ | --------------------------------------------------------- |
    | CONCAT(S1,S2,……,SN)      | 字符串拼接，将S1,S2,……,SN拼接成一个字符串                 |
    | LOWER(str)               | 将字符串str全部转成小写                                   |
    | UPPER(str)               | 将字符串str全部转成大写                                   |
    | LPAD(str,n,pad)          | 左填充，用字符串pad对str的左边进行填充，达到n个字符串长度 |
    | RPAD(str,n,pad)          | 右填充，用字符串pad对str的右边进行填充，达到n个字符串长度 |
    | TRIM(str)                | 去掉字符串头部和尾部的空格                                |
    | SUBSTRING(str,start,len) | 返回从字符串str从start位置起的len个长度的字符串           |

  * 使用方法:`select 函数(参数)`

### 数值函数

* 常见函数：

  * | 函数       | 功能                               |
    | ---------- | ---------------------------------- |
    | CEIL(X)    | 向上取整                           |
    | FLOOR(X)   | 向下取整                           |
    | MOD(X,Y)   | 返回x/y的模                        |
    | RAND()     | 返回0~1内的随机数                  |
    | ROUND(X,Y) | 求参数x的四舍五入的值，保留y位小数 |

### 日期函数

* 常见函数：

  * | 函数                             | 功能                                            |
    | -------------------------------- | ----------------------------------------------- |
    | CURDATE()                        | 返回当前日期                                    |
    | CURTIME()                        | 返回当前时间                                    |
    | NOW()                            | 返回当期日期和时间                              |
    | YEAR(date)                       | 返回date指定的年份                              |
    | MONTH(date)                      | 返回date指定的月份                              |
    | DAY()                            | 返回date指定的日期                              |
    | DATE_ADD(date,INTERVAL exp type) | 返回一个日期/时间值加上上一个时间间隔expr后的值 |
    | DATEDIFF(date1,date2)            | 返回起始时间date1和结束时间date2之间的天数      |

### 流程函数

* 常用函数：

  * | 函数                                                       | 功能                                                  |
    | ---------------------------------------------------------- | ----------------------------------------------------- |
    | IF(value,t,f)                                              | 如果value为true，则返回t，否则返回f                   |
    | IFNULL(value1,value2)                                      | 如果value不为空，返回value1，否则返回value2           |
    | CASE WHEN [val1] THEN [res1] ... ELSE [default] EDN        | 如果val1的值为true则返回res1，否则返回default默认值   |
    | CASE [expr] WHEN [val1] THEN [res1] ... ELSE [default] END | 如果expr的值等于val1，返回res1，否则返回default默认值 |

### 函数代码

~~~mysql
#使用CONCAT函数
select CONCAT('hello','world','mirage','clouds');
#使用LOWER函数
select LOWER('Hello');
#使用UPPER函数
select UPPER('hello');
#使用LPAD函数
select LPAD('mirage',12,'hello');
#使用RPAD函数
select RPAD('mirage',11,'hello');
#使用TRIM函数
select TRIM('   11  22  33    ');
#使用SUBSTRING函数
select SUBSTRING('MirageClouds',6,10);
#使用CEIL函数
select CEIL(1234.11);
#使用FLOOR函数
select FLOOR(1234.11);
#使用MOD函数
select MOD(1234,11);
#使用RAND函数
select RAND();
#使用ROUND函数
select ROUND(1234.5678,2);
#使用CURDATE函数
select CURDATE();
#使用CURTIME函数
select CURTIME();
#使用NOW函数
select NOW();
#使用YEAR函数
select YEAR(NOW());
#使用MONTH函数
select MONTH(NOW());
#使用DAY函数
select DAY(NOW());
#使用DATE_ADD函数
select DATE_ADD(NOW(),INTERVAL 70 DAY );
#使用DATEDIFF函数
select DATEDIFF(NOW(),DATE_ADD(NOW(),INTERVAL 70 DAY ));
#使用IF函数
select IF(true,'true','false');
select IF(false,'true','false');
#使用IFNULL函数
select IFNULL('true','false');
select IFNULL('','false');
select IFNULL(null,'false');
~~~