SQL语句分类

Structured Query Language：结构化查询语言 其实就是定义了操作所有关系型数据库的规则。每一种数据库操作的方式存在不一样的地方，称为“方言”。

  • SQL通用语法
  1. 1. SQL 语句可以单行或多行书写，以分号结尾。
  2. 2. 可使用空格和缩进来增强语句的可读性。
  3. 3. MySQL 数据库的 SQL 语句不区分大小写，关键字建议使用大写。
  4. 4. 3 种注释
  5.     * 单行注释: -- 注释内容 或 # 注释内容(mysql 特有)
  6.     * 多行注释: `/* 注释 */`
  • SQL语句分类
  1. 1) DDL(Data Definition Language)数据定义语言
  2.     用来定义数据库对象：数据库，表，列等。关键字：create, drop,alter 等
  3. 2) DML(Data Manipulation Language)数据操作语言
  4.     用来对数据库中表的数据进行增删改。关键字：insert, delete, update 等
  5. 3) DQL(Data Query Language)数据查询语言
  6.     用来查询数据库中表的记录(数据)。关键字：select, where 等
  7. 4) DCL(Data Control Language)数据控制语言(了解)
  8.     用来定义数据库的访问权限和安全级别，及创建用户。关键字：GRANT， REVOKE 等

20211210141658.png

DDL数据库操作（重点）

操作数据库无非就有四种方式 C(Create 创建数据库)、R(Retrieve 查询数据库)、U(Update 修改数据库)、D(Delete 删除数据库)

查询数据库

  1. -- 查询所有数据库的名称
  2. show databases;
  3. -- 查询某个数据库的字符集
  4. show create database 数据库名称;

创建数据库

  1. -- 创建数据库基本格式
  2. create database 数据库名称;
  3. -- 创建数据库，判断不存在，再创建
  4. create database if not exists 数据库名称;
  5. -- 创建数据库，并指定字符集 sql格式
  6. create database 数据库名称 character set 字符集名;

修改数据库

  1. -- 修改数据库的字符集
  2. alter database 数据库名称 character set 字符集名称;
  3. -- 修改db1数据库 的字符集为 utf8
  4. alter database db1 character set utf8;

删除数据库

  1. -- 删除指定数据库
  2. drop database 数据库名称;
  4. -- 判断数据库存在，才删除
  5. drop database if exists 数据库名称;

使用数据库

  1. -- (切换)当前使用的数据库
  2. use 数据库名称;
  4. -- 查询当前正在使用的数据库名称
  5. select database();

DDL数据表操作

DDL是对数据表的操作，其实也是CRUD的四种操作。

查询数据表（了解）

  1. -- 查询所有数据表
  2. show tables;
  3. -- 查询表结构
  4. desc 表名;
  5. -- 查询表状态
  6. SHOW TABLE STATUS FROM mysql LIKE '表名称';

创建数据表（重点）

  • 创建表语法
  1. -- 创表格式,最后一列不要加逗号
  2. create table 表名(
  3.     列名1 数据类型1,
  4.     列名2 数据类型2,
  5.     列名3 数据类型2
  6. );
  • 创建表举例
  1. -- 创建一个product商品表(商品编号、商品名称、商品价格、商品库存、上架时间)
  2. CREATE TABLE product(
  3.     id INT,
  4.     NAME VARCHAR(20),
  5.     price DOUBLE,
  6.     stock INT,
  7.     insert_time DATE
  8. );

MySQL常见数据类型

关键字 数据类型 备注
int 整数类型
double 小数类型
date 日期 只包含年月日，yyyy-MM-dd
datetime 日期 包含年月日时分秒 yyyy-MM-dd HH:mm:ss
timestamp 时间戳类型 包含年月日时分秒 yyyy-MM-dd HH:mm:ss,不赋值默认为系统时间毫秒值
varchar 字符串 姓名最大20个字符

修改数据表（了解）

修改表主要是对表结构的修改，如修改表名、修改表的字符集、修改表的字段(列)等

  • 修改表名称
  1. -- 修改表名
  2. alter table 表名 rename to 新的表名;
  • 修改表字符集
  1. -- 修改表的字符集格式
  2. alter table 表名 character set 字符集名称;
  • 添加表字段(列)
  1. -- 添加表字段(列)
  2. alter table 表名 add 列名 数据类型;
  • 修改表字段(列)
  1. -- 修改列名以及数据类型格式
  2. alter table 表名 change 列名 新列别 新数据类型;
  4. -- 修改指定列数据类型
  5. alter table 表名 modify 列名 新数据类型;
  • 删除表字段
  1. -- 删除表字段(列)
  2. alter table 表名 drop 列名;
  • 删除表
  1. -- 删除表格式
  2. drop table 表名;
  3. -- 先判断存在，再删除表
  4. drop table  if exists 表名 ;

DML表记录操作(重点)

添加表数据

  • 添加表数据语法
  1. -- 给指定列添加数据
  2. INSERT INTO 表名(列名1,列名2,...) VALUES (值1,值2,...);
  3. -- 给所有列添加数据
  4. INSERT INTO 表名 VALUES (值1,值2,...);
  • 添加表数据举例
  1. -- 向product表添加一条数据
  2. INSERT INTO product (id,NAME,price,stock,insert_time) VALUES (1,'手机',1999.99,25,'2020-02-02');
  5. -- 向product表添加指定列数据
  6. INSERT INTO product (id,NAME,price) VALUES (2,'电脑',3999.99);
  8. /*
  9.     给全部列添加数据
  10.     标准语法：
  11.         INSERT INTO 表名 VALUES (值1,值2,值3,...);
  12. */
  13. -- 默认给全部列添加数据
  14. INSERT INTO product VALUES (3,'冰箱',1500,35,'2030-03-03');
  16. /*
  17.     批量添加所有列数据
  18.     标准语法：
  19.         INSERT INTO 表名 VALUES (值1,值2,值3,...),(值1,值2,值3,...),(值1,值2,值3,...);
  20. */
  21. -- 批量添加数据
  22. INSERT INTO product VALUES (4,'洗衣机',800,15,'2030-05-05'),(5,'微波炉',300,45,'2030-06-06');

删除表数据

  • 删除表数据语法
  1. DELETE FROM 表名 [WHERE 条件];
  • 删除表数据举例
  1. -- 删除product表中的微波炉信息
  2. DELETE FROM product WHERE NAME='微波炉';
  4. -- 删除product表中库存为10的商品信息
  5. DELETE FROM product WHERE stock=10;

修改表数据

  • 修改表数据语法
  1. UPDATE 表名 SET 列名1 = 值1,列名2 = 值2,... [where 条件];
  • 修改表数据举例
  1. -- 修改手机的价格为3500
  2. UPDATE product SET price=3500 WHERE NAME='手机';
  4. -- 修改电脑的价格为1800、库存为36
  5. UPDATE product SET price=1800,stock=36 WHERE NAME='电脑';

DQL语句(重点)

DQL是对数据表中的数据进行查询操作，我们先准备一张数据表。

准备表数据

  1. -- 创建db1数据库
  2. CREATE DATABASE db1;
  4. -- 使用db1数据库
  5. USE db1;
  7. -- 创建数据表
  8. CREATE TABLE product(
  9.     id INT,            -- 商品编号
  10.     NAME VARCHAR(20),    -- 商品名称
  11.     price DOUBLE,        -- 商品价格
  12.     brand VARCHAR(10),    -- 商品品牌
  13.     stock INT,        -- 商品库存
  14.     insert_time DATE        -- 添加时间
  15. );
  17. -- 添加数据
  18. INSERT INTO product VALUES 
  19. (1,'华为手机',3999,'华为',23,'2088-03-10'),
  20. (2,'小米手机',2999,'小米',30,'2088-05-15'),
  21. (3,'苹果手机',5999,'苹果',18,'2088-08-20'),
  22. (4,'华为电脑',6999,'华为',14,'2088-06-16'),
  23. (5,'小米电脑',4999,'小米',26,'2088-07-08'),
  24. (6,'苹果电脑',8999,'苹果',15,'2088-10-25'),
  25. (7,'联想电脑',7999,'联想',NULL,'2088-11-11');

查询所有列

  1. -- 查询表中所有数据
  2. SELECT * FROM 表名;

指定列查询

  1. -- 查询指定列的记录
  2. select 列名1,列名2,... from 表名；
  3. -- 查询所有列的数据
  4. select * from 表名;

去重复查询

  1. -- 标准语法
  2. SELECT DISTINCT 列名1,列名2,... FROM 表名;

计算列查询

  1. -- 运算符可以是+ - * /
  2. SELECT 列名1 运算符 列名2 FROM 表名;
  3. -- 如果某一列为null，可以将其替换为0 
  4. ifnull(列名,0)
  1. -- 查询商品名称和库存，库存数量在原有基础上加10。进行null值判断
  2. SELECT NAME,IFNULL(stock,0)+10 as 别名 FROM product;

起别名查询

  1. SELECT 列名1 AS 别名 FROM 表名;

按条件查询

  • 条件查询语法
  1. SELECT 列名列表 FROM 表名 WHERE 条件;
  • 条件查询语句
  1. -- 查询库存大于20的商品信息
  2. SELECT * FROM product WHERE stock > 20;
  4. -- 查询品牌为华为的商品信息
  5. SELECT * FROM product WHERE brand='华为';
  7. -- 查询金额在4000 ~ 6000之间的商品信息
  8. SELECT * FROM product WHERE price >= 4000 and price <= 6000;
  9. SELECT * FROM product WHERE price BETWEEN 4000 AND 6000;
  11. -- 查询库存为14、30、23的商品信息
  12. SELECT * FROM product WHERE stock=14 OR stock=30 OR stock=23;
  13. SELECT * FROM product WHERE stock IN(14,30,23);
  15. -- 查询库存为null的商品信息
  16. SELECT * FROM product WHERE stock IS NULL;
  17. -- 查询库存不为null的商品信息
  18. SELECT * FROM product WHERE stock IS NOT NULL;
  20. -- 查询名称以小米为开头的商品信息
  21. SELECT * FROM product WHERE NAME LIKE '小米%';
  22. -- 查询名称第二个字是为的商品信息
  23. SELECT * FROM product WHERE NAME LIKE '_为%';
  24. -- 查询名称为四个字符的商品信息
  25. SELECT * FROM product WHERE NAME LIKE '____';
  26. -- 查询名称中包含电脑的商品信息
  27. SELECT * FROM product WHERE NAME LIKE '%电脑%';

聚合查询

  • 聚合查询语法
  1. SELECT 函数名(列名) FROM 表名 [WHERE 条件];
  • 聚合查询举例
  1. -- 计算product表中总记录条数
  2. SELECT COUNT(*) FROM product;
  4. -- 获取最高价格
  5. SELECT MAX(price) FROM product;
  7. -- 获取最低库存
  8. SELECT MIN(stock) FROM product;
  11. -- 获取总库存数量
  12. SELECT SUM(stock) FROM product;
  15. -- 获取品牌为苹果的总库存数量
  16. SELECT SUM(stock) FROM product WHERE brand='苹果';
  18. -- 获取品牌为小米的平均商品价格
  19. SELECT AVG(price) FROM product WHERE brand='小米';

排序查询

  • 排序查询语法
  1. SELECT 列名 FROM 表名 [WHERE 条件] ORDER BY 列名1 排序方式1,列名2 排序方式2;
  • 排序查询举例
  1. -- 按照库存升序排序
  2. SELECT * FROM product ORDER BY stock ASC;
  4. -- 查询名称中包含手机的商品信息。按照金额降序排序
  5. SELECT * FROM product WHERE NAME LIKE '%手机%' ORDER BY price DESC;
  7. -- 按照金额升序排序，如果金额相同，按照库存降序排列
  8. SELECT * FROM product ORDER BY price ASC,stock DESC;

分组查询

  • 分组查询语法
  1. SELECT 列名1,列名2 
  2. FROM 表名
  3. WHERE 分组前条件
  4. GROUP BY 分组列名
  5. HAVING 分组后条件
  6. ORDER BY 排序列名 排序方式;
  • 分组查询举例
  1. -- 按照品牌分组，获取每组商品的总金额
  2. SELECT brand,SUM(price) FROM product GROUP BY brand;
  4. -- 对金额大于4000元的商品，按照品牌分组,获取每组商品的总金额
  5. SELECT brand,SUM(price) FROM product WHERE price > 4000 GROUP BY brand;
  7. -- 对金额大于4000元的商品，按照品牌分组，获取每组商品的总金额，只显示总金额大于7000元的
  8. SELECT brand,SUM(price) getSum FROM product WHERE price > 4000 GROUP BY brand HAVING getSum > 7000;
  10. -- 对金额大于4000元的商品，按照品牌分组，获取每组商品的总金额，只显示总金额大于7000元的、并按照总金额的降序排列
  11. SELECT brand,SUM(price) getSum FROM product 
  12. WHERE price > 4000 
  13. GROUP BY brand 
  14. HAVING getSum > 7000 
  15. ORDER BY getSum DESC;

分页查询

  • 分页查询格式
  1. SELECT 列名 FROM 表名 
  2. [WHERE 条件] 
  3. [GROUP BY 分组列名]
  4. [HAVING 分组后条件过滤] 
  5. [ORDER BY 排序列名 排序方式] 
  6. LIMIT 当前页数,每页显示的条数; 
  8. -- 公式：当前页数 = (当前页数-1) * 每页显示的条数
  • 分页查询举例
  1. -- 第1页  当前页数=(1-1) * 3 = 0
  2. SELECT * FROM product LIMIT 0,3;
  4. -- 第2页  当前页数=(2-1) * 3 = 3
  5. SELECT * FROM product LIMIT 3,3;
  7. -- 第3页  当前页数=(3-1) * 3 = 6
  8. SELECT * FROM product LIMIT 6,3;

查询语句标准格式

  1. select     查询的字段列表
  3. from    表名1 as 别名1,表名2 as 别名2
  5. where    查询的条件
  7. group by 分组的列
  9. having 分组后的条件
  11. order by 排序条件
  13. limit    分页

MySQL约束(重点)

主键约束

  1. -- 创建学生表(编号、姓名、年龄)  编号设为主键
  2. CREATE TABLE student(
  3.     id INT PRIMARY KEY,
  4.     NAME VARCHAR(30),
  5.     age INT
  6. );
  8. -- 查询学生表的详细信息
  9. DESC student;
  11. -- 添加数据
  12. INSERT INTO student VALUES (1,'张三',23);
  13. INSERT INTO student VALUES (2,'李四',24);
  15. -- 删除主键
  16. ALTER TABLE student DROP PRIMARY KEY;
  19. -- 建表后单独添加主键约束
  20. ALTER TABLE student MODIFY id INT PRIMARY KEY;

主键约束自增

  1. -- 创建学生表(编号、姓名、年龄)  编号设为主键自增
  2. CREATE TABLE student(
  3.     id INT PRIMARY KEY auto_increment,
  4.     NAME VARCHAR(30),
  5.     age INT
  6. );
  8. -- 查询学生表的详细信息
  9. DESC student;
  11. -- 添加数据
  12. INSERT INTO student VALUES (NULL,'张三',23),(NULL,'李四',24);
  14. -- 删除自增约束
  15. ALTER TABLE student drop primary key;
  16. INSERT INTO student VALUES (NULL,'张三',23);
  18. -- 建表后单独添加自增约束
  19. alter table product add primary key(id);
  20. ALTER TABLE student MODIFY id INT AUTO_INCREMENT;

唯一约束

  1. -- 创建学生表(编号、姓名、年龄)  编号设为主键自增，年龄设为唯一
  2. CREATE TABLE student(
  3.     id INT PRIMARY KEY AUTO_INCREMENT,
  4.     NAME VARCHAR(30),
  5.     age INT UNIQUE
  6. );
  8. -- 查询学生表的详细信息
  9. DESC student;
  11. -- 添加数据
  12. INSERT INTO student VALUES (NULL,'张三',23);
  13. INSERT INTO student VALUES (NULL,'李四',23);
  15. -- 删除唯一约束
  16. ALTER TABLE student DROP INDEX age;
  18. -- 建表后单独添加唯一约束
  19. ALTER TABLE student MODIFY age INT UNIQUE;

非空约束

  1. -- 创建学生表(编号、姓名、年龄)  编号设为主键自增，姓名设为非空，年龄设为唯一
  2. CREATE TABLE student(
  3.     id INT PRIMARY KEY AUTO_INCREMENT,
  4.     NAME VARCHAR(30) NOT NULL,
  5.     age INT UNIQUE
  6. );
  8. -- 查询学生表的详细信息
  9. DESC student;
  11. -- 添加数据
  12. INSERT INTO student VALUES (NULL,'张三',23);
  14. -- 删除非空约束
  15. ALTER TABLE student MODIFY NAME VARCHAR(30);
  16. INSERT INTO student VALUES (NULL,NULL,25);
  18. -- 建表后单独添加非空约束
  19. ALTER TABLE student MODIFY NAME VARCHAR(30) NOT NULL;

外键约束

当一个表中需要使用到另一个表的的数据时，但是又要保证数据的正确性，可以是用外键约束。如下图。

  • 外键约束语法
  1. CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主表主键列名)
  • 创建表示添加外键约束
  1. -- 创建user用户表(主表)
  2. CREATE TABLE USER(
  3.     id INT PRIMARY KEY AUTO_INCREMENT,    -- id
  4.     NAME VARCHAR(20) NOT NULL             -- 姓名
  5. );
  6. -- 添加用户数据
  7. INSERT INTO USER VALUES (NULL,'张三'),(NULL,'李四');
  1. -- 创建orderlist订单表(从表)
  2. CREATE TABLE orderlist(
  3.     id INT PRIMARY KEY AUTO_INCREMENT,    -- id
  4.     number VARCHAR(20) NOT NULL,          -- 订单编号
  5.     uid INT,                  -- 外键列
  6.     CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id)
  7. );
  8. -- 添加订单数据
  9. INSERT INTO orderlist VALUES (NULL,'hm001',1),(NULL,'hm002',1),
  10. (NULL,'hm003',2),(NULL,'hm004',2);
  • 创建表后删除外键
  1. alter table 表名 drop foreign key 外键名称;
  • 创建表后添加外键
  1. alter table 表名 add constraint 外键名称 foreign key(外键列) references 主表(主键);
  3. alter table emp add constraint fk foreign key(dept_id) references dept(id);
  • 外键级联操作

外键让多个表之间产生了关系，但是如果主表中的数据所有改变，从表中关联的数据也必须跟着改变。这叫做外键级联操作。

  1. -- 添加外键级联更新、级联删除
  2. -- ON UPDATE CASCADE 级联更新
  3. -- ON DELETE CASCADE 级联删除
  4. ALTER TABLE 从表 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键列) REFERENCES 主表(主键) ON UPDATE CASCADE ON DELETE CASCADE;