1、数据库结构

1、所谓安装Mysql数据库，就是在主机安装一个数据库管理系统(DBMS)，这个管理程序可以管理多个数据库。

2、DBMS(database manage system)一个数据库中可以创建多个表，以保存数据(息)。

3、数据在数据库中的存储方式

4、SQL语句分类

DDL : 数据定义语句[create表, 库…]
DML : 数据操作语句[增加 insert, 修改 update, 删除 delete]
DQL : 数据查询语句 [select]
DCL : 数据控制语匀[管理数据库 : 比如用户权限 grant revoke]

2、数据库增删改查

▶ 创建数据库
create database 数据库名
character set : 指定数据库采用的字符集, 如果不指定字符集默认utf8

collate : 指定数据库字符集的校对规则(常用的 utf8_bin [区分大小写]、utf8_general_ci [不区分大小写](注意默认是utf8_general_ci)

▶ 删除数据库
drop database 数据库名
▶ 显示数据库
#查看当前数据库服务器中的所有数据库 
show databases

#查看前面创建的 hsp_db01 数据库的定义信息 
show create database 数据库名
注意：在创建数据库,表的时候，为了规避关键字，可以使用反引号解决

▶ 备份数据库
#备份, 要在 Dos 下执行 mysqldump 指令，其实在 mysql 安装目录\bin 
#这个备份的文件，就是对应的 sql 语句 

mysqldump -u root -p -B 数据库1 数据2 > 文件名.sql
▶ 恢复数据库
#恢复数据库(注意：进入 Mysql 命令行再执行) 
source 文件名.sql 

#第二个恢复方法， 直接将 bak.sql 的内容放到查询编辑器中，执行
▶ 恢复数据库的表
mysqldump -u 用户名 -p密码 数据库 表1 表2... > 文件名.sql

二、增删改查语句

1、insert 添加数据
CREATE TABLE `goods` (
	id INT ,
	goods_name VARCHAR(10), 
	price DOUBLE NOT NULL DEFAULT 100 );

-- 添加数据
INSERT INTO `goods` (id, goods_name, price) 
	VALUES(10, '华为手机', 2000);

INSERT INTO `goods` (id, goods_name, price) 
	VALUES(20, '苹果手机', 3000);

SELECT * FROM goods;
▶ insert 语句的细节

插入的数据应与字段的数据类型相同。比如把 ‘abc’ 添加到 int 类型会错误

数据的长度应在列的规定范围内，例如：不能将一个长度为80的字符串加入到长度为40的列中。

在values中列出的数据位置必须与被加入的列的排列位置相对应。

字符和日期型数据应包含在单引号中。

列可以插入空值[前提是该字段允许为空]，如：insert into table value(null)

insert into 列名.. values (),(),() 形式添加多条记录

如果是给表中的所有字段添加数据，可以不写前面的字段名称

默认值的使用，当不给某个字段值时，如果有默认值就会添加默认值，否则报错。如果某个列没有指定 not null ,那么当添加数据时，没有给定值，则会默认给null，如果我们希望指定某个列的默认值，可以在创建表时指定。

2、update 修改操作
# 如果没有带where 条件，会修改所有的记录，因此要小心
UPDATE 表 SET 要修改的数据

# 加上条件
UPDATE 表
	SET 要修改的数据 
	WHERE 条件 

# 可以修改多个列的值
UPDATE 表
	SET 数据1 数据2...
	WHERE 条件
▶ 使用细节

UPDATE语法可以用新值更新原有表行中的各列。

SET子句指示要修改哪些列和要给予哪些值。

WHERE子句指定应更新哪些行。如没有WHERE子句，则更新所有的行(记录）

如果需要修改多个字段，可以通过 set字段1-值1,字段2-值2…

3、delete 删除操作
# 有条件的删除
DELETE FROM 表名
	WHERE 条件;

# 删除表中所有记录
DELETE FROM 表名;

# Delete语句不能删除某一列的值（可使用update 设为 null 或者 ''）
UPDATE 表名
    SET 要修改的列
    WHERE 条件;

# 要删除表
DROP TABLE 表名;
▶ 使用细节

如果不使用where子句，将删除表中所有数据。

Delete语句不能删除某一列的值(可使用update 设为null或者“)

使用delete语句仅删除记录, 不删除表本身。如要删除表，使用drop table语句。 drop table 表名。

4、select 查询操作
# 查询表中所有信息。
SELECT * FROM 表名;

# 查询表中某些信息。
SELECT 列名1 列名2.. FROM 表名;

# 过滤表中重复数据 distinct 。
SELECT DISTINCT 列名 FROM 表名;

# 要查询的记录，每个字段都相同，才会去重
SELECT DISTINCT 列名1 列名2... FROM 表名;
# 统计
SELECT COUNT(*) FROM 表名;

SELECT COUNT(*) FROM 表名
	WHERE 条件

# count(*) 和 count(列) 的区别：
#     count(*) 返回满足条件的记录的行数
#     count(列): 统计满足条件的某列有多少个，但是会排除 为null的情况
SELECT column_name as 别名 from 表名;
# sum 统计总和
SELECT SUM(列名) FROM 表名;
# avg 求平均数
SELECT AVG(列名) FROM 表名;
# max 找最大值
SELECT MAX(列名) FROM 表名;

# min 找最小值
SELECT MIN(列名) FROM 表名;
▶ where 语句常用运算符

▶ order by 排序

Order by指定排序的列，排序的列既可以是表中的列名,也可以是select语句后指定的列名。

Asc升序[默认]、Desc降序

ORDER BY子句应位于SELECT语句的结尾。
SELECT * FORM 表名
   wHERE 条件
   ORDER BY 列名 ASC/DESC
▶ group by 对列进行分组
SELECT 列名 FORM 表名
    GROUP BY 列名
▶ having 分组后过滤
SELECT 列名 FORM 表名
    GROUP BY 列名 HAVING
▶ 字符串相关函数

▶ 数学相关函数

▶ 日期函数

5、加密和系统函数

6、流程控制函数

三、表的增删改查

1、创建表

▶ 创建表
create table 表名 (
        field1 datatype,

        field2 datatype,

        field3 datatype

) character set 字符集 collate 校对规则 engine 存储引擎
▷ field : 指定列名

        ▷ datatype : 指定列类型(字段类型)

        ▷ character set : 如不指定则为所在数据库字符集
        ▷ collate : 如不指定则为所在数据库校对规则
        ▷ engine : 存储引擎
CREATE TABLE `user` ( 
    id INT, 
    `name` VARCHAR(255), 
    `password` VARCHAR(255), 
    `birthday` DATE) 
     CHARACTER SET utf8 COLLATE utf8_bin ENGINE INNODB;
▶ 常用数据类型

▶ 数值类型

▷ 如果没有指定 unsinged , 则 tinyint 就是有符号

▷ 如果指定
unsinged ,
则
tinyint
就是无符号
0-255

▶ 定义无符号数值
create table 表1 (id tinyint) //默认是有符号的

create table 表2 (id tinyint unsigned) //有符号的
▶ bit 的使用

▷ bit(m为指定位数) m范围在
1-64

▷ 添加数据范围按照你给的位数来确定，比如
m = 8
表示一个字节
0~255

▷ 显示按照
bit

▷ 查询时，仍然可以按照数来查询
CREATE TABLE t (num BIT(8));
INSERT INTO t VALUES(255);
SELECT * FROM t;
SELECT * FROM t WHERE num = 1;
▶ 小数的使用

FLOAT / DOUBLE [UNSIGNED] ：Float 单精度精度，Double 双精度
DECIMAL[M , D] [UNSIGNED] :
可以支持更加精确的小数位。M是小数位数(精度)的总数，D是小数点(标度）
后面的位数。如果D是0, 则值没有小数点或分数部分。M最大65。 D最大是30。如果D被省略，默认是0。如果M被省略, 默认是10。建议:如果希望小数的精度高, 推荐使用decimal。
▶ 字符串基本使用

▷ CHAR(size) ：固定长度字符串最大 255
字符

▷ VARCHAR(size) ：可变长度字符串，0~65535 字节

可变长度字符串最大 65532 字节【utf8 编码最大 21844 字符 1-3 个字节用于记录大小】

如果表的编码是 utf8 varchar(size)， size = (65535-3) / 3 = 21844

如果表的编码是 gbk varchar(size)， size = (65535-3) / 2 = 32766

▷ 使用细节：

char(4)：这个4表示字符数(最大255)，不是字节数，不管是中文还是字母都是放四个,按字符计算

varchar(4)：这个4表示字符数，不管是字母还是中文都以定义好的表的编码来存放数据
不管是中文还是英文字母，都是最多存放4个，是按照字符来存放的.

char(4)是定长(固定的大小)，就是说，即使你插入’aa’，也会占用分配的4个字符的空间；varchar(4) 是变长（变化的大小)，就是说，如果你插入了’aa’,实际占用空间大小并不是4个字符，而是按照实际占用空间来分配（varchar本身还需要占用1-3个字节来记录存放内容长度) ；[ L(实际数据大小)+(1-3)字节 ]

什么时候使用 char, 什么时候使用varchar : ①如果数据是定长，推荐使用char, 比如md5的密码，邮编，手机号，身份证号码等.char(32) ; ②如果一个字段的长度是不确定，我们使用varchar. 比如留言,文章。

查询速度: char > varchar

在存放文本时，也可以使用Text 数据类型.可以将TEXT列视为VARCHAR列，注意 Text不能有默认值.大小0-2*16字节如果希望存放更多字符，可以选择MEDIUMTEXT：0-2*24 或者 LONGTEXT：0～2*32 。

▶ 日期类型基本使用
# timestamp : 时间戳

CREATE TABLE birthday (
    t1 DATE, 
    t2 DATETIME,
    t3 TIMESTAMP NOT NULL DEFAULT
    CURRENT TIMESTAMP ON UPDATE
    CURRENT TIMESTAMP ); 


INSERT INTO birthday (t1,t2)
    VALUES( 2022-11-11  2022-11-11 10:10:10):
2、修改表
# 增加一个列，varchar 类型(要求在 resume 后面)。

ALTER TABLE 表名
    ADD 列名 VARCHAR(32) NOT NULL DEFAULT ''
    AFTER RESUME
# 显示表结构，可以查看表的所有列

DESC 表名
# 修改列，使其长度为 60。

ALTER TABLE 表名
    MODIFY 列名 VARCHAR(60) NOT NULL DEFAULT ''
# 删除列。

ALTER TABLE 表名
    DROP 列名
# 表名修改。

RENAME TABLE 原表名 TO 新表名
# 修改表的字符集

ALTER TABLE 表名 CHARACTER SET 字符集
# 修改列名

ALTER TABLE 表名
CHANGE 原列名 新列名 VARCHAR(64) NOT NULL DEFAULT ''
3、表的查询

▶ 表查询加强

% :
表示
0
到多个任意字符

_ :
表示单个任意字符
SELECT 列名 FROM 表名
    WHERE 列名 LIKE 'S%' -- 从列名中找 S 开头的数据

SELECT 列名 FROM 表名
    WHERE 列名 LIKE '__O%'  -- 从列中找 第二个字母为 O 的数据
▶ 分页查询
# 推导公式   
SELECT * FROM 表名
    ORDER BY 列名
    LIMIT 每页显示记录数 * (第几页 - 1) , 每页显示记录数
▶ 数据分组总结

    如果 select语句同时包含有 group by , having, limit，order by那么他们的顺序是group by, having , order by, limit

4、多表查询

▶ 基本说明

        多表查询是指基于两个或两个以上的表查询。在实际应用中，查询单个表可能不能满足你的需求。


多表查询的条件不能少于表的个数
-1,
否则会出现笛卡尔集。
SELECT 列名1，列名2.... 
    FROM 表1，表2.... 
    WHERE 查询条件  -- 多表查询的条件不能少于 表的个数-1
▶ 自连接

自连接是指在同一张表的连接查询，（将同一张表看做两张表来查询）

特点：①把同一张表当做两张表使用，②需要给表取别名， (表名 as 表别名)，③列名不明确，可以指定列的别名 (列名 as 列的别名)
SELECT 列名 AS 列的别名 , 列名 AS 列的别名
    FROM 表中的某列, 表中的某列
    WHERE 条件
▶ 表的子查询

  ▷ 基本介绍：子查询是指嵌入在其它 sql 语句中的 select 语句,也叫嵌套查询

        ▷ 单行子查询：是指只返回一行数据的子查询语句

        ▷ 多行子查询：指返回多行数据的子查询，使用关键字 in
SELECT * 
    FROM 表 
    WHERE 某个列 = ( -- 括号中为子查询 
        SELECT 列名
            FROM 表名
            WHERE 条件
        )
SELECT * 
    FROM 表 
    WHERE 某个列 in ( -- 多行子查询用关键字 in
        SELECT 列名
            FROM 表名
            WHERE 条件
        ) and 条件
▶ all 和 any 的使用
SELECT 列名...
    FROM 表名
    WHERE 列名 > ALL( -- 表示大于all子查询中的最大的
        SELECT 列名
            FROM 表名
            WHERE 条件
)
SELECT 列名...
    FROM 表名
    WHERE 列名 > ANY( -- 表示大于any子查询中的最小的
        SELECT 列名
            FROM 表名
            WHERE 条件
)
▶ 表的复制
INSERT INTO 表1 
    (列名1，列名2，列名3....) 
    SELECT 列名1，列名2，列名3.... FROM 表2;
# 自我复制
INSERT INTO 表1
    SELECT * FROM 表1;

SELECT COUNT(*) FROM 表1;
▶ 如何删除一张表的重复记录

先创建一张临时表 , 该表的结构和原表一样

把临时表的记录通过 distinct 关键字处理后把记录复制到临时表（即自我复制）

清除掉原表记录

把临时表的记录复制到原表

drop 掉临时表
▶ 合并查询
union all 就是将两个查询结果合并，不会去重
SELECT 列名.... FROM 表名 WHERE 条件 
UNION ALL 
SELECT 列名.... FROM 表名 WHERE 条件 
union 就是将两个查询结果合并，会去重
SELECT 列名.... FROM 表名 WHERE 条件 
UNION  
SELECT 列名.... FROM 表名 WHERE 条件
5、外连接

左外连接 (如果左侧的表完全显示我们就说是左外连接）
select.. from 表1 
    left join 表2 
    on 条件[表1:就是左表，表2:就是右表]
右外连接(如果右侧的表完全显示我们就说是右外连接）
select.... from 表1 
    right join 表2 
    on 条件[表1:就是左表，表2：就是右表]