MySQL笔记

1. SQL 分类

SQL通用语法

• 分号结尾

• 关键字大写

• 注释 #

DDL

create database if not exists zpp default charset utfmb4;
drop database name;
select database()''

CREATE TABLE user (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '编号',
    employee_id VARCHAR(10) NOT NULL COMMENT '员工工号',
    name VARCHAR(10) NOT NULL COMMENT '员工姓名',
    gender CHAR(1) NOT NULL COMMENT '性别（男/女）',
    age TINYINT UNSIGNED NOT NULL COMMENT '年龄',
    id_card CHAR(18) NOT NULL COMMENT '身份证号',
    hire_date DATE NOT NULL COMMENT '入职时间'
) COMMENT='员工信息表';

alter table employee add nickname varchar(20) comment '昵称';
alter table employee change nickname username varchar(30) comment '用户名';
alter table employee modify username varchar(50);
alter table employee drop username;
alter table employee rename to emp;

drop table | if exists | tablename;
truncate table tablename;

DML

1. DML-介绍

DML英文全称是Data Manipulation Language（数据操作语言），用来对数据库中表的数据记录进行增删改操作。
- 添加数据（INSERT）
- 修改数据（UPDATE）
- 刪除数据（DELETE）

2. DML

添加数据

1. 给指定字段添加数据
INSERT INTO 表名（字段名1，字段名2，...） VALUES（值1，值2, ...）；

2. 给全部字段添加数据
INSERTINTO 表名 VALUES（值1，值2，…）；

3. 批量添加数据
INSERT INTO 表名（字段名1，字段名2，⋯）VALUES（值1，值2，），（值1，值2，…），（值1，值2，…）；
INSERT INTO 表名 VALUES（值1，值2，…），（值1，值2, ...），（值1，值2，...）；

修改数据

UPDATE 表名 SET 字段名1=值1，字段名2=值2，...［WHERE 条件］；

update emp set e_name = 'user', gender = '女' where id = 1

删除数据

DELETE FROM 表名 [WHERE 条件]

• DELETE语句的条件可以有，也可以没有，如果没有条件，则会删除整张表

• DELETE语句不能删除某一个字段的值(可以使用UPDATE)

DQL

• 基本查询SELECT 字段1，字段2，字段3, ... FROM 表名

# 去重
select distinct e_name as username from emp;

• 分组查询

where与having区别

• 执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤。

• 判断条件不同：where不能对聚合函数进行判断，而having可以。

DCL

1. 用户管理

CREATE USER‘用户名'@'主机名’IDENTIFIED BY‘密码’；
ALTER USER‘用户名”@'主机名’IDENTIFIED WITH mysqLnative_password BY "密码；
DROP USER‘用户名’@'主机名’

2. 权限控制

GRANT 权限列表 ON 数据库名.表名 TO‘用户名'@'主机名’；
REVOKE 权限列表 ON 数据库名.表名 FROM‘用户名'@主机名”；

2. 函数

# 字符串函数
SELECT CONCAT('Hello', ' ', 'World');  -- 输出: 'Hello World'
SELECT SUBSTRING('Hello World', 7, 5);  -- 输出: 'World'
SELECT REPLACE('Hello World', 'World', 'MySQL');  -- 输出: 'Hello MySQL'
SELECT TRIM('  Hello World  ');  -- 输出: 'Hello World'
SELECT LOWER('Hello World');  -- 输出: 'hello world'
SELECT UPPER('Hello World');  -- 输出: 'HELLO WORLD'
SELECT LPAD('123', 5, '0');  -- 输出: '00123'
SELECT RPAD('123', 5, '0');  -- 输出: '12300'
SELECT REVERSE('Hello');  -- 输出: 'olleH'
SELECT LOCATE('World', 'Hello World');  -- 输出: 7


# 数值函数
SELECT ABS(-10);  -- 输出: 10
SELECT CEIL(3.14);  -- 输出: 4
SELECT FLOOR(3.14);  -- 输出: 3
SELECT ROUND(3.14159, 2);  -- 输出: 3.14
SELECT TRUNCATE(3.14159, 2);  -- 输出: 3.14
SELECT MOD(10, 3);  -- 输出: 1
SELECT POW(2, 3);  -- 输出: 8
SELECT SQRT(16);  -- 输出: 4
SELECT GREATEST(10, 20, 30);  -- 输出: 30
SELECT LEAST(10, 20, 30);     -- 输出: 10
# 日期函数
update emp set e_no = lpad(e_no, '5', '0');
# 生成验证码
select lpad(round(rand()*1000000, 0), 6, '0');

select curdate();
select curtime();
select now();
select year(now());
select month(now());
select day(now());
SELECT DATE('2023-10-05 14:30:00');  -- 输出: 2023-10-05
SELECT YEAR('2023-10-05');  -- 输出: 2023
SELECT DATE_FORMAT('2023-10-05', '%Y年%m月%d日');  -- 输出: 2023年10月05日
SELECT DATE_ADD('2023-10-05', INTERVAL 1 MONTH);  -- 输出: 2023-11-05
SELECT DATEDIFF('2023-10-05', '2023-09-01');  -- 输出: 34
SELECT LAST_DAY('2023-10-05');  -- 输出: 2023-10-31
SELECT STR_TO_DATE('05-10-2023', '%d-%m-%Y');  -- 输出: 2023-10-05
SELECT UNIX_TIMESTAMP('2023-10-05 14:30:00');  -- 输出: 1696512600
SELECT FROM_UNIXTIME(1696512600);  -- 输出: 2023-10-05 14:30:00


# INTERVAL：关键字，表示后面的内容是时间间隔。
select date_add(now(), INTERVAL 90 DAY );

SELECT DATE_ADD('2023-10-05', INTERVAL 1 MONTH);  -- 输出: 2023-11-05
SELECT DATE_SUB('2023-10-05 14:30:00', INTERVAL 2 HOUR);  -- 输出: 2023-10-05 12:30:00
SELECT DATE_ADD('2023-10-05', INTERVAL 1 YEAR);  -- 输出: 2024-10-05
SELECT DATE_SUB('2023-10-05', INTERVAL 3 WEEK);  -- 输出: 2023-09-14

1. 字符串函数

2. 数值函数

3. 日期函数

4. 流程控制函数

SELECT IF(1 > 0, 'True', 'False');  -- 输出: 'True'
SELECT IFNULL(NULL, 'Default Value');  -- 输出: 'Default Value'
SELECT NULLIF(10, 10);  -- 输出: NULL
SELECT NULLIF(10, 20);  -- 输出: 10

SELECT 
    CASE 
        WHEN score >= 90 THEN 'A'
        WHEN score >= 80 THEN 'B'
        WHEN score >= 70 THEN 'C'
        ELSE 'D'
    END AS grade
FROM students;


SELECT COALESCE(NULL, NULL, 'First Non-NULL Value', 'Second Value');  -- 输出: 'First Non-NULL Value'

3. 约束

# 添加外键
alter table info add constraint dep_foreign_key FOREIGN KEY (status) references dep(id);


# 删除外键
alter table info drop foreign key dep_foreign_key;

详细说明

1. CASCADE：

   • 删除行为：如果父表中的记录被删除，子表中所有相关的记录也会被自动删除。

   • 更新行为：如果父表中的记录的主键被更新，子表中所有相关记录的外键列也会被自动更新。

   • 示例：

     CREATE TABLE orders (
         order_id INT PRIMARY KEY,
         user_id INT,
         FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE
     );

2. SET NULL：

   • 删除行为：如果父表中的记录被删除，子表中所有相关记录的外键列会被设置为 NULL。

   • 更新行为：如果父表中的记录的主键被更新，子表中所有相关记录的外键列会被设置为 NULL。

   • 示例：

     CREATE TABLE orders (
         order_id INT PRIMARY KEY,
         user_id INT,
         FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL ON UPDATE SET NULL
     );

3. RESTRICT：

   • 删除行为：如果子表中存在相关记录，阻止删除父表中的记录。

   • 更新行为：如果子表中存在相关记录，阻止更新父表中的记录的主键。

   • 示例：

     CREATE TABLE orders (
         order_id INT PRIMARY KEY,
         user_id INT,
         FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT ON UPDATE RESTRICT
     );

4. NO ACTION：

   • 删除行为：与 RESTRICT 相同，阻止删除父表中的记录。

   • 更新行为：与 RESTRICT 相同，阻止更新父表中的记录的主键。

   • 示例：

     CREATE TABLE orders (
         order_id INT PRIMARY KEY,
         user_id INT,
         FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE NO ACTION ON UPDATE NO ACTION
     );

5. SET DEFAULT：

   • 删除行为：如果父表中的记录被删除，子表中所有相关记录的外键列会被设置为默认值。

   • 更新行为：如果父表中的记录的主键被更新，子表中所有相关记录的外键列会被设置为默认值。

   • 注意：MySQL 不支持 SET DEFAULT 行为，但某些数据库（如 SQL Server）支持。

   • 示例（假设支持）：

     CREATE TABLE orders (
         order_id INT PRIMARY KEY,
         user_id INT DEFAULT 0,
         FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET DEFAULT ON UPDATE SET DEFAULT
     );