MySQL 笔记一

MySQL 笔记一

• MySQL 笔记
  • SQL 简介
  • SQL 语法
    • 最重要的 SQL 命令
  • 基础概念
    • SQL, DB, DBMS 分别是什么及他们之间的关系
    • 什么是表
    • 字段包括哪些属性
    • SQL 分类
    • 数据导入
    • 字符集
  • 基本操作
    • 库操作
      • 创建数据库
      • 选择数据库
      • 修改数据库
      • 删除数据库
  • 简单查看语句 DQL
    • 简单查询
    • 条件查询
      • 排序
    • 数据处理函数
    • 分组函数
      • 分组
      • HAVING 子句
      • 去重
    • 链接查询
    • 子查询
    • union 合并查询
    • limit 分页查询
    • DQL 语言总结

SQL 简介

• SQL 指结构化查询语言，全称是 Structured Query Language。
• SQL 让您可以访问和处理数据库，包括数据插入、查询、更新和删除。

• SQL 面向数据库执行查询
• SQL 可从数据库取回数据
• SQL 可在数据库中插入新的记录
• SQL 可更新数据库中的数据
• SQL 可从数据库删除记录
• SQL 可创建新数据库
• SQL 可在数据库中创建新表
• SQL 可在数据库中创建存储过程
• SQL 可在数据库中创建视图
• SQL 可以设置表、存储过程和视图的权限

SQL 语法

1. SQL 对大小写不敏感：SELECT 与 select 是相同的。
2. 分号是在数据库系统中分隔每条 SQL 语句的标准方法，这样就可以在对服务器的相同请求中执行一条以上的 SQL 语句。
   • 某些数据库系统要求在每条 SQL 语句的末端使用分号。

最重要的 SQL 命令

|      命令       |         含义         |
| :-------------: | :------------------: |
|     SELECT      |  从数据库中提取数据  |
|     UPDATE      |  更新数据库中的数据  |
|     DELETE      |  从数据库中删除数据  |
|   INSERT INTO   | 向数据库中插入新数据 |
| CREATE DATABASE |     创建新数据库     |
| ALTER DATABASE  |      修改数据库      |
|  CREATE TABLE   |       创建新表       |
|   ALTER TABLE   | 变更（改变）数据库表 |
|   DROP TABLE    |        删除表        |
|  CREATE INDEX   |  创建索引（搜索键）  |
|   DROP INDEX    |       删除索引       |

基础概念

SQL, DB, DBMS 分别是什么及他们之间的关系

• DB

  DataBase 数据库，在硬盘上以文件的形式存在

• DBMS

  DataBase Management System 数据库管理系统

• SQL

  结构化查询语言，是一门标准通用的语言

  • 数据查询语言 DQL
  • 数据操作语言 DML
  • 事务处理语言 TPL
  • 数据控制语言 DCL
  • 数据定义语言 DDL

他们之间的关系：DBMS -(执行)-> SQL -(操作)-> DB

什么是表

• 表：table, 是数据库的基本组成单位元

• 表包括行和列

  • 行：数据/记录 data
  • 列：字段 column

字段包括哪些属性

• 字段名
• 数据类型
• 相关的约束

SQL 分类

• 数据查询语言 DQL：查询语句 select ...
• 数据操作语言 DML：表数据的增删改 insert, delete, update ...
• 数据定义语言 DDL：表结构的增删改 create, drop, alter ...
• 事务处理语言 TPL：事务管理 commit, rollback ...
• 数据控制语言 DCL：权限 grant, revoke ...

数据导入

1. 登录

   前提：找到 mysql.exe

   • 输入对应服务器地址

     -h[IP地址/域名] 表示 host

   • 输入服务器中 MySQL 监听的端口

     -P[port] 端口号 默认：3306

   • 输入用户名

     -u[username] 用户名

   • 输入密码

     -p[password] 密码 一般不直接输入

   连接认证基本语法

    mysql.exe/mysql -h主机地址 -P端口 -u用户名 -p密码
   

   例如连接 127.0.0.1 端口 3306 的用户 root 密码 123456

    mysql.exe -h127.0.0.1 -P3306 -uroot -p123456
   

   注意：

   • -P 默认可以省略
   • 密码一般不直接输入

   例：

    mysql.exe -h127.0.0.1 -uroot -p
   

   退出命令【三种】

   exit;
   quit;
   \q
   

2. 查看数据库

    SHOW DATABASES;     # 属于 MySQL 的命令，不是 SQL 语句
   

   mysql> SHOW DATABASES;
   +--------------------+
   | Database           |
   +--------------------+
   | information_schema |
   | mysql              |
   | performance_schema |
   | test               |
   +--------------------+
   4 rows in set (0.00 sec)
   

3. 创建 test_learn 数据库

    CREATE DATABASE 数据库名称;   # 属于 MySQL 的命令，不是 SQL 语句
   

   
   mysql> CREATE DATABASE test_learn;
   Query OK, 1 row affected (0.00 sec)
   
   mysql> SHOW DATABASES;
   +--------------------+
   | Database           |
   +--------------------+
   | information_schema |
   | mysql              |
   | performance_schema |
   | test               |
   | test_learn         |
   +--------------------+
   4 rows in set (0.00 sec)
   

4. 使用 test_learn 数据库

    USE 数据库名称;   # 属于 MySQL 的命令，不是 SQL 语句
   

5. 查看当前数据库中有哪些表

    SHOW TABLES;    # 属于 MySQL 的命令，不是 SQL 语句
   

6. 初始化数据 xxx.sql 文件

    SOURCE 数据文件绝对路径;
   

   后缀为sql的文件是sql脚本文件，使用 SOURCE 可以执行sql脚本

DESC 查看表的结构;

    DESC 表名;  
    # 如果没有使用 USE 进入数据库，可以用 '数据库名.表名' 表示 '表名'
    DESC 数据库名.表名;

字符集

mysql.exe 与 mysqld.exe 之间处理关系一共分三层

• 客户端传入数据给服务端：client
• 服务端返回数据给客户端：server
• 客户端与服务端之间连接：collection

set names 字符集 作用是一次性打通三层关系

在系统有三个变量来记录着三个关系对应字符集：

show variables like 'character_set_%';

+--------------------------+----------------------------+
| Variable_name            | Meaning                    |
+--------------------------+----------------------------+
| character_set_client     | 客户端传入                 
| character_set_connection | 连接层                     
| character_set_database   | 当前数据库存储方式         
| character_set_filesystem | binary                     
| character_set_results    | 结果返回的字符集           
| character_set_server     | 服务端                    
| character_set_system     | 系统                    
| character_sets_dir       | 
+--------------------------+----------------------------+

基本操作

库操作

创建数据库

基本语法

CREATE DATABASE 数据库名称 [库选项];

• 库选项

  • 字符集 charset ；默认 DBMS
  • 校对集 collate

每个数据库 DB 都有一个配置文件 db.opt

选择数据库

要操作数据必须进入对应层次

USE 数据库名称;

修改数据库

修改数据库字符集

ALTER DATABASE 数据库名称 charset = 字符集;

删除数据库

基本语法

DROP DATABASE 数据库名字;

注意：确认数据无用才删除

简单查看语句 DQL

简单查询

SELECT 查看表的数据

SELECT * FROM 表名;                         # 查询全部，效率低

SELECT 字段名1,字段名2,... FROM 表名;       # 指定字段

SELECT 字段名 AS 别名 FROM 表名;            # 设置别名

注意：

• 其中字段可以参与数学运算
• AS 用于别名设置
• 字符串以单引号括起来

条件查询

SELECT
    字段,字段,...
FROM
    表名
WHERE
    条件;

允许条件

操作符	描述
=	等号
<> 或 !=	不等于
>	大于号
<	小于号
>=	大于等于号
<=	小于等于号
between ... and ...	在两个值之间
is null	为 null
and	与
or	或
not	非
in	存在
like	模糊查询, 类似正则表达式作用

注意：

• between ... and ... 数值需要左小右大，数值等价 >= and <= ，字符等价 >= and <

• NULL 值不能使用 = 比较，需要使用 is

• 模糊查询 like 可以使用两个特殊字符模糊取代其它字符

  • % 表示任意个字符
  • _ 表示一个字符
  • \ 转义字符

排序

使用 ORDER BY 进行排序

排序：根据校对规则进行排序

order by 字段 [asc|desc]

• asc 升序 默认
• desc 降序

SELECT
    字段,字段,...
FROM
    表名
ORDER BY
    字段 [asc|desc], 字段 [asc|desc], ... ;

数据处理函数

数据处理函数又称为 单行处理函数，与之对应的：多行处理函数

特点：一个输入对应一个输出

常见单行处理函数：

• lower 转换小写
• upper 转换大写
• trim 去空格
• length 长度
• concat 字符串拼接
• substr 取子串
• str_to_date 字符串转日期
• date_format 格式化日期
• rand 随机数
• format 设置千分位
• round 四舍五入
• ifnull 可以将 null 转换为具体值

substr 使用方法

substr(目标字符串, 起始下标, 截取长度)  # 起始下标从 1 开始

NULL 参与运算结果为 NULL

特殊语句

case 字段 ... 
    when 
        条件1
    then 
        满足条件1的操作
    when 
        条件2
    then 
        满足条件2的操作
    else 
        其它情况的操作
end

类似 Java 的 switch case 语句

分组函数

分组函数又称为 多行处理函数，使用前需要分组，否则默认一张表为一组

特点：多个输入对应一个输出

常见分组函数：

• count 计数
• sum 求和
• avg 求平均
• max 最大值
• min 最小值

注意：

• 自动忽略 NULL
• count(*) 与 count(具体字段) 区别在于 count(*) 统计总行数
• 不能直接使用 where 子句，涉及执行顺序的合理性

分组

使用 GROUP BY 进行分组

SELECT
    字段, 字段, ...
FROM
    表名
WHERE
    条件
GROUP BY
    字段, ...
ORDER BY
    字段 [asc|desc], 字段 [asc|desc], ... ;

其执行顺序

1. from
2. where
3. group by
4. select
5. order by

如果有 GROUP BY 那么 SELECT 后面只能跟参与分组的字段，以及分组函数

HAVING 子句

用于 GROUP BY 分组后的过滤，不能单独使用

SELECT
    字段, 字段, ...
FROM
    表名
WHERE
    条件
GROUP BY
    字段, ...
HAVING
    条件
ORDER BY
    字段 [asc|desc], 字段 [asc|desc], ... ;

其执行顺序

1. from
2. where
3. group by
4. having
5. select
6. order by

优化策略：where, having 优先 where 除非 where 无法完成

去重

使用 distinct 关键字

SELECT DISTINCT     # 出现在此处表示 '字段, 字段, ...' 联合去重
    字段, 字段, ...
FROM
    表名
WHERE
    条件
GROUP BY
    字段, ...
HAVING
    条件
ORDER BY
    字段 [asc|desc], 字段 [asc|desc], ... ;

也可以使用在 分组函数

链接查询

将多张表联合查询数据

分类：

• 年代分类

  • SQL92 基本淘汰
  • SQL99

• 连接方式

  • 内连接

    内连接 关键字 INNER

    • 等值连接

      # SQL92 语法
      SELECT  
          别名1.字段1, 别名3.字段2, ...       # 别名不是强制要求
      FROM
          表名1 AS 别名1, 表名3 AS 别名3, ...
      WHERE
          别名1.字段3 = 别名2.字段3;          # 判断条件看情况
      
      # SQL99 语法
      SELECT  
          别名1.字段1, 别名2.字段2, ...     
      FROM
          表名1 AS 别名1
      INNER JOIN
          表名2 AS 别名2
      ON
          别名1.字段3 = 别名2.字段3;         
      

      SQL92 结构不清晰，SQL99 优点 表连接的条件独立

    • 非等值连接

      条件不是一个等量关系

      SELECT  
          别名1.字段1, 别名2.字段2, ...     
      FROM
          表名1 AS 别名1
      INNER JOIN
          表名2 AS 别名2
      ON
          别名1.字段3 BETWEEN 别名2.字段3 AND 别名2.字段4;
      

    • 自连接

      一张表看成两张表

  • 外连接

    外连接关键字 outer 可以省略

    关键字 right 和 left 决定是 join 那边的表作为 主表

    主表将全部查询，次表数据是捎带

    • 左外连接

      SELECT  
          别名1.字段1, 别名2.字段2, ...     
      FROM
          表名1 AS 别名1
      LEFT OUTER JOIN
          表名2 AS 别名2
      ON
          别名1.字段3 = 别名2.字段3;  
      

    • 右外连接

      SELECT  
          别名1.字段1, 别名2.字段2, ...     
      FROM
          表名1 AS 别名1
      RIGHT OUTER JOIN
          表名2 AS 别名2
      ON
          别名1.字段3 = 别名2.字段3;  
      

    左外连接与右外连接可以互换

  • 全连接

直接进行联合查询会查询会存在笛卡尔积现象，解决添加条件

子查询

SELECT 语句嵌套

可以存在于

SELECT
    子句    # 只能返回一个结果，多于一条，就报错
FROM
    子句    # 此处子查询可以视为一张临时表
WHERE
    子句

union 合并查询

union 可以优化效率，在表连接中削弱笛卡儿积

union 合并表需要列数相同及列的数据类型相同

limit 分页查询

limit 取出部分数据，应用于分页查询

分页作用是减少数据载荷，提高用户体验

基本格式为：

• limit startIndex, length 表示从第 startIndex + 1 条数据开始，顺序往下查询 length 条数据
• limit M 表示查询前 M 条数据

注意：在MySQL中 limit 是在 order by 之后执行

通用分页

# 每页显示 pageSize 条数据

limit (pageNo - 1) * pageSize, pageSize     # 第 pageNo 页

DQL 语言总结

SELECT
    ...
FROM
    ...
WHERE
    ...
GROUP BY
    ...
HAVING
    ...
ORDER BY
    ...
LIMIT
    ...

执行顺序

1. from
2. where
3. group by
4. having
5. select
6. order by
7. limit ..