MySQL基本语法

前言

• MySQL的注释：
  单行注释： --或者#
  多行注释： /**/

• 备注：
  [ ]表示可选择，可有可没有

分类	全称	说明
DDL	Data Definition Language	数据定义语言，用来定义数据库对象
DML	Data Manipulation Language	数据操作语言，用来对数据库表中的数据进行增删改
DQL	Data Query Language	数据查询语言，用于查询数据库中表的信息
DCL	Data Control Language	数据控制语言，用于创建数据库用户、控制数据库的访问权限

DDL

DDL:数据定义语言，用于对数据库中的数据对象的组成和结构进行定义。

操作数据库

查询数据库

#1. 查询M有SQL中所有的数据库
SHOW DATABASES;
#2. 查询当前正在使用的数据库
SELECGT DATABASE();

创建数据库

#1. 无需判断的创建（如果出现同名的数据库，则会报错）
CREATE DATABASE EXAMPLE;
#2. 判断并创建（如果不存在，则创建；反之，不创建）
CREATE DATABASE IF NOT EXISTS EXAMPLE;
#3. 创建数据库，并指定字符集
CREATE DATABASE iteima default charset utf8mb4;

删除数据库

#1. 无需判断的删除(如果数据库存在，则删除；反之报错)
DROP DATABASE EXAMPLE;
#2. 判断并删除（如果存在，则删除；反值，不操作）
DROP DATABASE IF EXISTS EXAMPLE;

使用数据库

# 切换到数据库中的某一个
USE EXAMPLE;

操作TAbLE

查询表

#1. 查询当前数据库中的所有表
SHOW TABLES;
#2. 查询表的结构（表的详细字段信息）
DESC table_name;
#3. 查询建表语句
SHOW CREATE TABLE table_name;

创建表

#1. 创建一个简单的的表
CREATE TABLE table_name{
    column1 datatype,
    column2 datatype,
    ······
    columen datatype #最后一个不能逗号
};

#2. 创建一个学生信息表
CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    age INT,
    enrollment_date DATE
);

对上述关键词的解释：
AUTO_INCREMENT: 每次插入一条新记录，这个值就会自动递增。如果起始位1，那么第二个加1。
PRIMARY KEY: 主键，表示唯一性，并且不能为NULL，作为唯一记录标识符。
NOT NULL: 这个不能为空。

修改表

#1. 修改表名
ALTER TABLE table_name_old RENAME TO table_name_new;
#2. 添加一列
ALTER TABLE table_name ADD colume_name datatype;
#3. 修改现有列的数据类型
ALTER TABLE table_name MODIFY COLUMN column_name new_type;
#4. 重命名表中的列
ALTER TABLE table_name CHANGE colume_name_old column_name_new datatype;
#5. 删除某列
ALTER TABLE table_name DROP COLUMN column_name;

删除表

#1. 无需判断的删除（如果不存在，则报错）
DROP TABLE table_name;
#2. 判断并删除(如果存在，则删除；反之，不操作)
DROP TABLE IF EXISTS table_name;
#3. 删除指定表并且创建表（等同与删除表中的所有data）
TRUNCATE TABLE table_name;

DML

DML: 数据操作语言，用作对数据库中表的数据进行增删改

添加数据

#1. 给指定列添加数据
INSERT INTO table_name (column1, column2)
VALUES (value1, value2);

# example:
INSERT INTO students (name, age)
VALUES ('AFEI', 18)
# 插入了一个名字为AFEI的人，年龄为18，enrollment_data列会被设置为NULL.

#2. 给所有列添加数据
INSERT INTO table_name [所有列名]
VALUES (value1，value2，······);

# example:
INSERT INTO students (id, name, age, enrollment_date)
VALUES (1, 'AFEI', 18, '2022-08-26')
# 插入一条名字为AFEI的人，年龄为18，id为1，入学时间为2022-8-26.

INSERT INTO students 
VALUES (1, 'AFEI', 18, '2022-08-26')
# 同上，不建议这么做。默认的属性插入为列表的顺序。

#3. 批量添加数据
INSERT INTO 表名(列名1, [列名2], [...])
VALUES (值1, 值2, ...), [(值1, 值2, ...)], [...];

#example：
INSERT INTO students (name, age)
VALUES ('David', 21),
       ('Eva', 23),
       ('Frank', 20);

#4. 批量添加所有数据
INSERT INTO 表名 [所有列名]
VALUES (值1, 值2, ...), (值1, 值2, ...), ...;

# example:
INSERT INTO students (id, name, age, enrollment_date)
VALUES (3, 'Gr', 18, '2024-08-26'),
       (4, 'He', 22, '2024-08-27'),
       (5, 'Is', 21, '2024-08-28');

修改数据

# 修改数据，如果不用WHERE条件，则将表中所有数据修改
UPDATE table_name
SET column1 = value1, column2 = value2, ·····
[WHERE CONDITION];
# example:
UPDATE students
SET age = 21, enrollment_date = 2023-08-26
WHERE id = 1;
# 将id为1的学生的age修改为21,enrollment_date修改为2023-08-26；

删除数据

# 删除数据。如果不用WHERE条件，则数据全部删除。
DELETE FROM table_name
[WHERE CONDITION];

# example：
DELETE FROM students
WHERE ID = 1;

DQL

DQL:数据查询语言，用于查询数据库的数据

基本语法

#1. 基本某一列
SELECT column1, column2,······
FROM table_name
[WHERE CONDITION];

# example:
SELECT name,age
FROM students;
# 返回所有学生的name和age;

#2. 查询全部列
SELECT * 
FROM table_name 
WHERE age > 20;
# 返回所有学生age大于20 的所有列

使用AND 和 OR 组合条件

SELECT column1, column2,······
FROM table_name
WHERE condition1 [AND|OR] condition2;

排序查询

SELECT column1, column2,····
FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC]，····；
# ASC 升序， DESC 降序；
# 当有多个排序条件时候，先看第一个排序，如果相同，则按照之后的排序 

分页查询

SELECT column1， column2
FROM table_name
LIMIT offset, row_count;
# offset 从那一行开始， row_count 指定返回的行数

# example：
SELECT name,age
FROM students
LIMIT 0,5;
# 返回前五个学生的姓名和年龄

聚合函数查询

FUNCTION	功能
COUNT(column)	计算指定列的非空记录数量
SUM(column)	计算指定列数值的总和
AVG(column)	计算指定列的平均值
[MAX/MIN(column)]	计算指定列的最值

SELECT FUNCTION(column)
FROM table_name;

# example:
SELECT AVG(age)
FROM students;
# 返回所有学生年龄的平均值

# example:
SELECT COUNT(*)
FROM students;
# 返回学生的数量

分组查询(GROUP BY)

# 以column1 分组，汇总数据
SELECT column1, FUNCTION(column2)
FROM table_name
GROUP BY column1;

# example：
# 按照age分组，计算每个年龄的人的数量
SELECT age,COUNT(*)
FROM students
GROUP BY age;

过滤分组结果(HAVING)

SELECT column1, FUNCTION(column2)
FROM table_name
GROUP BY column1
HAVING condition;

# example:
SELECT age, COUNT(*)
FROM students
GROUP BY age
HAVING COUNT(*) > 1;
# 按照age分组，并且过滤得到数量大于1的汇总

WHERE 和 HAVING 的区别

• 判断时间不同：WHERE是分组前先过滤，如果不满足，则不参与分组。 HAVING是分组后再过滤。

多表连接查询

SELECT column1, column2,···
FROM table_name1
JOIN table_name2
ON table_name1.column = table_name2.column;
# 将两个或者多个表中的数据通过指定的条件链接在一起

常见的连接方式：

类型	解释
INNER JOIN	仅返回两表匹配的记录
LEFT JOIN	返回左表所有记录和右表匹配的记录，如果右表无匹配，返回NULL
RIGHT JOIN	返回右表所有记录和左表匹配的记录，如果左表无匹配，返回NULL
FULL JOIN	返回两表的全部记录，不匹配，返回NULL

# example
# 假设有一个`COURSE`表和一个`enrollments`表，查询所有学生以及其选课名称、
SELECT student.name, courses.course_name
FROM students
INNER JOIN enrollments ON students.id = enrollments.student_id
INNER JOIN courses ON enrollments.course_id = courses.id;

子查询

SELECT column1, column2
FROM table_name
WHERE column OPERATOR (SELECT column FROM table_name WHERE condition)
# 使用一个查询的结果作为另一个查询的条件

# example
# 查询比`students`表中大于平均age的学生：
SELECT name, age
FROM studens
WHERE age > (SELECR AVG(age) FROM students);

DCL

DCL(Data Control Language): 数据控制语言，可以创建和管理数据库用户、授予或者撤销权限，以保证数据库的安全和访问控制

创建用户

# 创建一个新的数据库用户
CREATE USER 'username'@'host' IDENTIFIED BY 'password';

# 创建一个用户名为'newuser',密码为'user_password'的用户，
# 并且限制其在本地访问
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'user_password';

# 创建一个从任意主机访问的用户
CREATE USER 'newuser'@'%' INDETIFIED BY 'user_password';

授予权限

GRANT privilege_list ON database_name.table_name TO 'username'@'host';
# 向指定用户授予数据库或者表的访问权限;
# privilege_list: 要授予的权限列表，可以是 ALL PRIVILEGES 或具体的权限（如 SELECT, INSERT, UPDATE, DELETE 等）。
# database_name.table_name: 指定权限应用的数据库和表，*.* 表示所有数据库和表。
# username: 接受权限的用户。

撤销权限

REVOKE privilege_list ON database_name.table_name FROM 'username'@'host';

查看用户权限

SHOW GRANTS FOR 'username'@'host';

删除用户

DROP USER 'username'@'host';

以上为MYSQL的一些简单的语法。