MySQL 数据库和 SQL 的学习笔记

本文最后更新于:June 6, 2022 11:45 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 字段列表 FROM1,表2WHERE 条件;

-- 显示内连接
SELECT 字段列表 FROM1 [INNER] JOIN2 ON 条件;

外链接(A ∪ B)

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

-- 右外连接
SELECT 字段列表 FROM1 RIGHT [OUTER] JOIN2 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) :事务一旦提交或回滚,它对数据库中的数据的改变就是永久的