day01——1.MySQL基础

SQL语句分类

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

• SQL通用语法

1. SQL 语句可以单行或多行书写，以分号结尾。
2. 可使用空格和缩进来增强语句的可读性。
3. MySQL 数据库的 SQL 语句不区分大小写，关键字建议使用大写。
4. 3 种注释
    * 单行注释: -- 注释内容 或 # 注释内容(mysql 特有)
    * 多行注释: `/* 注释 */`

• SQL语句分类

1) DDL(Data Definition Language)数据定义语言
    用来定义数据库对象：数据库，表，列等。关键字：create, drop,alter 等(数据库和数据表)
2) DML(Data Manipulation Language)数据操作语言
    用来对数据库中表的数据进行增删改。关键字：insert, delete, update 等(表中数据)
3) DQL(Data Query Language)数据查询语言
    用来查询数据库中表的记录(数据)。关键字：select, where 等(表中数据)
4) DCL(Data Control Language)数据控制语言(了解)
    用来定义数据库的访问权限和安全级别，及创建用户。关键字：GRANT， REVOKE 等

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-D7zi5iNj-1612715937575)(assets/image-20201219170211897.png)]

DDL语句

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

查询数据库

-- 查询所有数据库的名称
show databases;
-- 查询某个数据库的字符集(查询数据库的创建语句)
show create database 数据库名称;

创建数据库

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

修改数据库

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

删除数据库

-- 删除指定数据库
drop database 数据库名称;

-- 删除数据库(判断,如果存在则删除)
drop database if exists 数据库名称;

使用数据库

-- (切换)当前使用的数据库
use 数据库名称;

-- 查询当前正在使用的数据库名称
select database();

DDL语句

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

查询数据表

-- 查询所有数据表
show tables;
-- 查询表结构
desc 表名;
-- 查询表状态
SHOW TABLE STATUS FROM 数据库名称 LIKE '表名称';

创建数据表

• 创建表语法

-- 创表格式,最后一列不要加逗号
create table 表名(
    列名1 数据类型1 约束,
    列名2 数据类型2 约束,
    ....
    列名n 数据类型n 约束
);

• 创建表举例

-- 创建一个product商品表(商品编号、商品名称、商品价格、商品库存、上架时间)
CREATE TABLE product(
	id INT,
	NAME VARCHAR(20),
	price DOUBLE,
	stock INT,
	insert_time DATE
);

MySQL常见数据类型

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

修改数据表

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

• 修改表名称

-- 修改表名
alter table 表名 rename to 新的表名;

• 修改表字符集

-- 修改表的字符集格式
alter table 表名 character set 字符集名称;

• 添加表字段(列)

-- 添加表字段(列)
alter table 表名 add 列名 数据类型;

• 修改表字段(列)

-- 修改列名以及数据类型格式
alter table 表名 change 列名 新列名 新数据类型;

-- 修改指定列数据类型
alter table 表名 modify 列名 新数据类型;

• 删除表字段

-- 删除表字段(列)
alter table 表名 drop 列名;

• 删除表

-- 删除表格式
drop table 表名;
-- 先判断存在，再删除表
drop table  if exists 表名 ;

DQL语句

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

准备表数据

-- 创建db1数据库
CREATE DATABASE db1;

-- 使用db1数据库
USE db1;

-- 创建数据表
CREATE TABLE product(
	id INT,			-- 商品编号
	NAME VARCHAR(20),	-- 商品名称
	price DOUBLE,		-- 商品价格
	brand VARCHAR(10),	-- 商品品牌
	stock INT,		-- 商品库存
	insert_time DATE        -- 添加时间
);

-- 添加数据
INSERT INTO product VALUES 
(1,'华为手机',3999,'华为',23,'2088-03-10'),
(2,'小米手机',2999,'小米',30,'2088-05-15'),
(3,'苹果手机',5999,'苹果',18,'2088-08-20'),
(4,'华为电脑',6999,'华为',14,'2088-06-16'),
(5,'小米电脑',4999,'小米',26,'2088-07-08'),
(6,'苹果电脑',8999,'苹果',15,'2088-10-25'),
(7,'联想电脑',7999,'联想',NULL,'2088-11-11');

查询所有列

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

指定列查询

-- 查询指定列的记录
select 列名1,列名2,... from 表名；

去重复查询

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

计算列查询

-- 运算符可以是+ - * /
SELECT 列名1 运算符 列名2 FROM 表名;
-- 如果某一列为null，可以将其替换为0 
ifnull(列名,0)

-- 查询商品名称和库存，库存数量在原有基础上加10。进行null值判断
SELECT NAME,IFNULL(stock,0)+10 FROM product;

起别名查询

SELECT 列名1 AS 别名 FROM 表名;

按条件查询

• 条件查询语法

SELECT 列名列表 FROM 表名 WHERE 条件;

• 条件查询语句

-- 查询库存大于20的商品信息
SELECT * FROM product WHERE stock > 20;


-- 查询品牌为华为的商品信息
SELECT * FROM product WHERE brand='华为';

-- 查询金额在4000 ~ 6000之间的商品信息
SELECT * FROM product WHERE price >= 4000 AND price <= 6000;
SELECT * FROM product WHERE price BETWEEN 4000 AND 6000;


-- 查询库存为14、30、23的商品信息
SELECT * FROM product WHERE stock=14 OR stock=30 OR stock=23;
SELECT * FROM product WHERE stock IN(14,30,23);

-- 查询库存为null的商品信息
SELECT * FROM product WHERE stock IS NULL;

-- 查询库存不为null的商品信息
SELECT * FROM product WHERE stock IS NOT NULL;


-- 查询名称以小米为开头的商品信息
SELECT * FROM product WHERE NAME LIKE '小米%';

-- 查询名称第二个字是为的商品信息
SELECT * FROM product WHERE NAME LIKE '_为%';

-- 查询名称为四个字符的商品信息
SELECT * FROM product WHERE NAME LIKE '____';

-- 查询名称中包含电脑的商品信息
SELECT * FROM product WHERE NAME LIKE '%电脑%';

聚合查询

• 聚合查询语法

SELECT 函数名(列名) FROM 表名 [WHERE 条件];

• 聚合查询举例

-- 计算product表中总记录条数
SELECT COUNT(*) FROM product;

-- 获取最高价格
SELECT MAX(price) FROM product;


-- 获取最低库存
SELECT MIN(stock) FROM product;


-- 获取总库存数量
SELECT SUM(stock) FROM product;


-- 获取品牌为苹果的总库存数量
SELECT SUM(stock) FROM product WHERE brand='苹果';

-- 获取品牌为小米的平均商品价格
SELECT AVG(price) FROM product WHERE brand='小米';

排序查询

• 排序查询语法

SELECT 列名 FROM 表名 [WHERE 条件] ORDER BY 列名1 排序方式1,列名2 排序方式2;

• 排序查询举例

-- 按照库存升序排序
SELECT * FROM product ORDER BY stock ASC;

-- 查询名称中包含手机的商品信息。按照金额降序排序
SELECT * FROM product WHERE NAME LIKE '%手机%' ORDER BY price DESC;

-- 按照金额升序排序，如果金额相同，按照库存降序排列
SELECT * FROM product ORDER BY price ASC,stock DESC;

分组查询

• 分组查询语法

SELECT 列名 
FROM 表名
WHERE 分组前条件
GROUP BY 分组列名
HAVING 分组后条件
ORDER BY 排序列名 排序方式;

• 分组查询举例

-- 按照品牌分组，获取每组商品的总金额
SELECT brand,SUM(price) FROM product GROUP BY brand;

-- 对金额大于4000元的商品，按照品牌分组,获取每组商品的总金额
SELECT brand,SUM(price) FROM product WHERE price > 4000 GROUP BY brand;

-- 对金额大于4000元的商品，按照品牌分组，获取每组商品的总金额，只显示总金额大于7000元的
SELECT brand,SUM(price) getSum FROM product WHERE price > 4000 GROUP BY brand HAVING getSum > 7000;

-- 对金额大于4000元的商品，按照品牌分组，获取每组商品的总金额，只显示总金额大于7000元的、并按照总金额的降序排列
SELECT brand,SUM(price) getSum FROM product 
WHERE price > 4000 
GROUP BY brand 
HAVING getSum > 7000 
ORDER BY getSum DESC;

分页查询

• 分页查询格式

SELECT 列名 FROM 表名 
[WHERE 条件] 
[GROUP BY 分组列名]
[HAVING 分组后条件过滤] 
[ORDER BY 排序列名 排序方式] 
LIMIT 当前页数,每页显示的条数;

-- 公式：当前页数 = (当前页数-1) * 每页显示的条数

• 分页查询举例

-- 第1页  当前页数=(1-1) * 3 = 0
SELECT * FROM product LIMIT 0,3;

-- 第2页  当前页数=(2-1) * 3 = 3
SELECT * FROM product LIMIT 3,3;

-- 第3页  当前页数=(3-1) * 3 = 6
SELECT * FROM product LIMIT 6,3;

MySQL约束

主键约束

-- 创建学生表(编号、姓名、年龄)  编号设为主键
CREATE TABLE student(
	id INT PRIMARY KEY,
	NAME VARCHAR(30),
	age INT
);

-- 查询学生表的详细信息
DESC student;

-- 添加数据
INSERT INTO student VALUES (1,'张三',23);
INSERT INTO student VALUES (2,'李四',24);

-- 删除主键
ALTER TABLE student DROP PRIMARY KEY;


-- 建表后单独添加主键约束
ALTER TABLE student MODIFY id INT PRIMARY KEY;

主键约束自增

-- 创建学生表(编号、姓名、年龄)  编号设为主键自增
CREATE TABLE student(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(30),
	age INT
);

-- 查询学生表的详细信息
DESC student;

-- 添加数据
INSERT INTO student VALUES (NULL,'张三',23),(NULL,'李四',24);

-- 删除自增约束
ALTER TABLE student MODIFY id INT;
INSERT INTO student VALUES (NULL,'张三',23);

-- 建表后单独添加自增约束
ALTER TABLE student MODIFY id INT AUTO_INCREMENT;

唯一约束

-- 创建学生表(编号、姓名、年龄)  编号设为主键自增，年龄设为唯一
CREATE TABLE student(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(30),
	age INT UNIQUE
);

-- 查询学生表的详细信息
DESC student;

-- 添加数据
INSERT INTO student VALUES (NULL,'张三',23);
INSERT INTO student VALUES (NULL,'李四',23);

-- 删除唯一约束
ALTER TABLE student DROP INDEX age;

-- 建表后单独添加唯一约束
ALTER TABLE student MODIFY age INT UNIQUE;

非空约束

-- 创建学生表(编号、姓名、年龄)  编号设为主键自增，姓名设为非空，年龄设为唯一
CREATE TABLE student(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(30) NOT NULL,
	age INT UNIQUE
);

-- 查询学生表的详细信息
DESC student;

-- 添加数据
INSERT INTO student VALUES (NULL,'张三',23);

-- 删除非空约束
ALTER TABLE student MODIFY NAME VARCHAR(30);
INSERT INTO student VALUES (NULL,NULL,25);

-- 建表后单独添加非空约束
ALTER TABLE student MODIFY NAME VARCHAR(30) NOT NULL;

外键约束

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

• 外键约束语法

CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主表主键列名)

• 创建表示添加外键约束

-- 创建user用户表(主表)
CREATE TABLE USER(
	id INT PRIMARY KEY AUTO_INCREMENT,    -- id
	NAME VARCHAR(20) NOT NULL             -- 姓名
);
-- 添加用户数据
INSERT INTO USER VALUES (NULL,'张三'),(NULL,'李四');

-- 创建orderlist订单表(从表)
CREATE TABLE orderlist(
	id INT PRIMARY KEY AUTO_INCREMENT,    -- id
	number VARCHAR(20) NOT NULL,          -- 订单编号
	uid INT,			      -- 外键列
	CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id)
);
-- 添加订单数据
INSERT INTO orderlist VALUES (NULL,'hm001',1),(NULL,'hm002',1),
(NULL,'hm003',2),(NULL,'hm004',2);

• 创建表后删除外键

alter table 表名 drop foreign key 外键名称;

• 创建表后添加外键

alter table 表名 add constraint 外键名称 foreign key(外键列) references 主表(主键);

• 外键级联操作

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

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