MySQL 数据库和 SQL 的学习笔记

本文最后更新于：June 1, 2023 9:58 PM

SQL 的四种分类

  • DDL (Data Definition Language): 操作数据库
  • DML (Data Manipulation Language): 操作数据库中表的数据
  • DQL (Data Query Language): 查询数据库中表的数据
  • DCL (Data Control Language): 数据库的权限控制

DDL

操作数据库

  1. 创建

    • 创建数据库：CREATE　DATEBASE;

    • 创建数据库（判断，如果不存在则创建）：CREATE DATABASE IF NOT EXISTS 数据库名称;

  2. 查询：SHOW DATABASES;

  3. 使用数据库

    • 查看当前使用的数据库：SELECT DATABASE();

    • 使用数据库：USE 数据库名称;

  4. 删除

    • 删除数据库：DROP DATABASE 数据库名称;
    • 删除数据库：DROP DATABASE IF EXISTS 数据库名称;

操作表 （CRUD）

创建（Create）

1
2
3
4
5
6
7
CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
    ...
    columnn datatype
);

Example:

1
2
3
4
5
CREATE TABLE tb_user(
 	id int,
    username varchar(20), #最高20位的用户名
    password varchar(32)
);

查询（Retrieve)

  • 查询当前数据库下所有表名称：SHOW　TABLES;

  • 查询表结构：DESC 表名称;

    Example Output:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    mysql> desc tb_user;
    +----------+-------------+------+-----+---------+-------+
    | Field    | Type        | Null | Key | Default | Extra |
    +----------+-------------+------+-----+---------+-------+
    | id       | int(11)     | YES  |     | NULL    |       |
    | username | varchar(20) | YES  |     | NULL    |       |
    | password | varchar(32) | YES  |     | NULL    |       |
    +----------+-------------+------+-----+---------+-------+
    3 rows in set (0.00 sec)

修改（Update）

  1. 修改表名：ALTER TABLE 表名 RENAME TO 新的表名;
  2. 添加一行：ALTER TABLE 表名 ADD 列名 数据类型;
  3. 修改数据类型：ALTER TABLE 表名 MODIFY 列名 新数据格式;
  4. 修改列名和数据类型：ALTER TABLE 表名 CHANGE 列名 新列名 新数据格式;
  5. 删除列：ALTER TABLE 表名 DROP 列名;

删除（Delete）

  1. 删除表：DROP TABLE 表名;
  2. 删除表时判断表是否存在： DROP TABLE IF EXISTS 表名;

DML

添加（Insert）

  1. 给指定列添加数据：INSERT INTO 表名(列名1,列名,...) VALUES(制1,值2,...);
  2. 给全部列添加数据：INSERT INTO表名 VALUES(值1,值2,...);
  3. 批量添加数据：
    • INSERT INTO 表名(列名1,列名2,...) VALUES(值1,值2,...),(值1,值2,...),(值1,值2,...)...;
    • INSERT INTO 表名(值1,值2,...),(值1,值2,...),(值1,值2,...),...;

修改（Update）

  1. 修改列表数据：UPDATE 表名 SET 列名1=值1,列名2=制2,...[WHERE 条件];

删除（Delete）

  1. 删除数据：DELETE FROM 表名 [WHERE 条件]

DQL

查询语法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT
	字段列表
FROM
	表名列表
WHERE
	条件列表
GROUP BY
	分组字段
HAVING
	分组后的条件
ORDER BY
	排序字段
LIMIT
	分页限定

基础查询

  1. 查询多个字段

    SELECT 字段列表 FROM 表名;

    SELECT * FROM 表名; -- 查询所有数据

  2. 去除重复记录

    关键词：DISTINCT

    例：select DISTINCT address from stu;

  3. 别名

    关键词：AS （也可以省略）

    例：

    select name AS 姓名 from stu;
    select name 姓名 from stu;

条件查询（WHERE）

The following operators can be used in the WHERE clause:

Operator Description
= Equal
> Greater than
< Less than
>= Greater than or equal
<= Less than or equal
<> or != Not equal
BETWEEN … AND … Between a certain range
LIKE … Search for a pattern _ represents a single random character % represents multiple random characters
IN(…) To specify multiple possible values for a column
IS NULL Is null
AND or && And
OR or \ Or
NOT or ! not

排序查询（ORDER BY）

  1. 排序查询语法：SELECT FROM 字段列表 ORDER BY 排序字段名1 [排序方式1],排序字段名2 [排序方式2]...;

排序方式：

  • ASC：升序排列（默认）
  • DESC：降序排列

聚合函数

函数名 功能
count(列名) 统计数量（一般选用不为null的列）
max(列名) 最大值
min(列名) 最小值
sum(列名) 求和
avg(列名) 平均值

语法：SELECT 聚合函数名(列名) FROM 表;

分组查询（GROUP BY）

  1. 分组查询语法：SELECT 字段列表 FROM 表名 [WHERE 分组前条件限定] GROUP BY 分组字段名 [HAVING 分组后条件过滤];

分页查询（LIMIT）

  1. 分页查询语法：SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询条目组;

起始索引 = （当前页面 - 1）* 每页条数

约束（Constraints）

  • NOT NULL - Ensures that a column cannot have a NULL value
  • UNIQUE - Ensures that all values in a column are different
  • PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table
  • FOREIGN KEY - Prevents actions that would destroy links between tables
  • CHECK - Ensures that the values in a column satisfies a specific condition
  • DEFAULT - Sets a default value for a column if no value is specified
  • CREATE INDEX - Used to create and retrieve data from the database very quickly

外键约束

  1. 添加外键约束
1
2
3
4
5
6
-- 创建表时添加外键约束
CREATE TABLE 表名(
    列名 数据类型,
    …
    [CONSTRAINT] [外键名称] FOREIGN KEY(外键列名) REFERENCES 主表(主表列名)
);
1
2
-- 建完表后添加外键约束
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);
  1. 删除外键约束
1
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称

多表查询

内连接（A ∩ B）

1
2
3
4
5
-- 隐式内连接
SELECT 字段列表 FROM 表1,表2… WHERE 条件;

-- 显示内连接
SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 条件;

外链接（A ∪ B）

1
2
3
4
5
-- 左外连接
SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件;

-- 右外连接
SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件;

事务（Transaction）

1
2
3
4
5
6
7
8
9
10
-- 开启事务
START TRANSACTION;
-- 或者
BEGIN;

-- 提交事务
COMMIT;

-- 回滚事务
ROLLBACK;

事务的四大特征

  1. 原子性（Atomicity）: 事务是不可分割的最小操作单位，要么同时成功，要么同时失败
  2. 一致性（Consistency） :事务完成时，必须使所有的数据都保持一致状态
  3. 隔离性（Isolation） :多个事务之间，操作的可见性
  4. 持久性（Durability） :事务一旦提交或回滚，它对数据库中的数据的改变就是永久的