MySQL数据库笔记

1. 概念

数据库：存储数据的文件系统

数据库软件：软件，用来管理数据库（如mysql）

数据库的应用：开发中，一般一个应用会创建一个数据库

数据库里可以创建多张表，一般一个实体一张表

一个表可以插入很多条记录，一般一个对象的实例 一条记录

1.1 SQL

1）概念

结构化查询语句，对数据库进行增删改查

2）分类

• DDL 数据库定义语句
• DML 数据库操作语句（增删改）
• DQL 数据库查询语句（查）
• DCL 数据库控制语句（权限）

2. 🟡数据类型 ⭕应用

类型	名称	说明	无符号范围	有符号范围
整型	tinyint	微整型, 占8位二进制	0~255	-127~128
	smallint	小整型, 占16位二进制	0~65535(6万)	-32767~32768
	int	整型	0~42亿
	bigint	大整型	0~200亿亿
小数	float(m,d)	m(精度), 表示总位数 d(标度), 表示小数点后的位数
	double(m,d)
	decimal(m,d)
字 符 型	char(M)定长	无论使用几个字符都占满全部, 范围0~255字符
	varchar(M)变长	用几个字符 占几个, 理论范围0~65535字符
	text	允许长度 0~65535 字节
	enum(集合)	枚举, 集合表示选项, 以逗号隔开
日期	date	范围1000-01-01~9999-12-31
	datetime	范围1000-01-01 00:00:00~9999-12-31 23:59:59
	timestamp	从1970年开始至今的秒数

3. 🟡字段属性 单表约束（建表）

字段属性	写法	说明
能否为空	null not null
默认值	default
主键	primary key	非空且唯一 主键与自增一般连用
自增	auto_increment	添加数据时 不写会自增。写了按写的来 序号不可重复使用（删除了也不行）
注释	comment '注释
唯一	unique

4. 🟢建表demo

-- CREATE TABLE `表名` (
--   字段1 字段类型1 字段属性1 字段属性2 字段属性3,
--   ...
-- );

-- 文章表
CREATE TABLE `blog` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `title` varchar(255) NOT NULL DEFAULT '' COMMENT '标题',
  `content` text NOT NULL COMMENT '内容',
  `author` varchar(32) NOT NULL DEFAULT '' COMMENT '作者',
  `click` int(11) NOT NULL DEFAULT '0' COMMENT '浏览数',
  `comment` smallint(6) NOT NULL DEFAULT '0' COMMENT '评论数',
  `publish_time` date DEFAULT NULL COMMENT '发布日期',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

5. 操作

5.1 库

-- 创建一个数据库
CREATE DATABASE db02;

-- 使用数据库
USE db02;

5.2 表

1）建表

-- 创建用户表
CREATE TABLE user(
	id int PRIMARY KEY auto_increment,
	username varchar(20) NOT NULL,
	password varchar(20) not null
)

2）增

-- 插入一条语句 完整写法
insert into user (id, username, password) values (null,'张三','123')
-- 插入一条语句 简单写法
insert into user  values (null, '李四', '123')
-- 插入多条语句
insert into user values (null, '王五', '123'),(null, '王五', '123')
-- 插入部分字段
insert into user (username, password) values ('项羽', '666')

3）删

delete from where username = '王五'

4）改

update user set age = '23' where username = '卫鞅'

5）查

select * from product;

6. 查询

6.1 基本查询

1）*

查询全部字段

2）别名

• SELECT `name` as `username` FROM `t1` as stu;
• as可省略

3）🟡where条件

运算符	说明
>, <, <=, >=, =, <>	<>表示不等于, 也可以使用!=
BETWEEN...AND	在某个范围内, between 100 and 200, 包含100和200, 闭区间[100, 200]
IN (1,2, ... n)	集合表示多个值，使用逗号分隔
LIKE	模糊查询 where name like '_为%'
is null	查询某一列为null
is not null	查询某一列不为null

逻辑运算符	说明
and 或者 &&	与， SQL 中建议使用前者，后者并不通用
or 或者 ||	或
not 或者 !	非

通配符	说明
%	匹配任意多个字符
_	匹配一个字符

> <                     select * from product where stock > 20

=                           select * from product where brand = '华为'

is null                     where stock is null

is not null              where stock is not null

between 与           where price BETWEEN 4000 and 6000

        where price >= 4000 && price <= 6000

in 或                      where stock = 14 || stock = 30 || stock = 23

        where stock in (14,30,23)

like + （模糊查询）         where name like '_为%'

4）去重

        select distinct dep_id from employee

6.2 聚合函数查询

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

• 总记录条数         select count(*) from product
• 最大值                select max(price) from product
• 最小值                select min(IFNULL(stock,0)) from product
• 求和                    select sum(stock) from product where brand = '苹果'
• 平均值                select avg(price) from product where brand = '小米'

6.3 排序查询

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

• 升序        select * from product ORDER BY stock asc
• 降序 desc        select * from product ORDER BY price desc
• 先金额升序，相同再 库存降序        select * from product ORDER BY price asc ,stock desc

6.4 分组查询

标准语法（分组后的查询字段，是分组字段 或 聚合函数（单值））

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

举例：对金额大于4000元的商品，按照品牌分组，获取每组商品的总金额，只显示总金额大于7000元的、并按照总金额的降序排列

select brand,sum(price) as getSum from product where price>4000 group by brand having getSum > 7000 order by getSum desc

6.5 分页查询

语法：LIMIT 当前页起始索引,页显示条数;

        当前页起始索引 = (当前页数-1) * 每页显示的条数

举例：一页3条，第3页         select * from product limit 6,3;

6.6 书写顺序

SELECT 列名/列运算表达式 别名, 列2 别名 FROM 表名

where              分组前过滤条件

group by          分组列名

having             分组后过滤条件

order by          排序列名 排序方式

limit                 当前页起始索引, 页显示条数;

6.7 执行顺序

from > where > group by > having > 【select】 > order by > limit

7. 多表

7.1 多表约束

通过【外键】关联

添加外键（员工表指向）（修改表结构alter，而非数据update）多的加外键，指向1的一方

alter table employee add constraint fk_001 foreign key(dep_id) references department(id)

员工表的部门 添加外键 指向部门表的 id（主键）

查看  -> 新建查询  ->  查询创建工具

7.2 多表关系

1）一对一

        任意一方添加外键，指向另一方主键，让外键唯一

        数据库优化分表（1表分2表）

2）一对多

        多的一方加外键，指向一的一方的主键

3）多对多

        中间表

        联合主键

        2个外键，指向多对多的两张表的主键

7.3 查询

1）交叉查询（笛卡尔值，两表所有组合）

        select * from employee,department;

2）内连接

隐式内连接（没 inner join关键字） 表1, 表2 where 条件

        select * from employee,department where employee.dep_id = department.id

显式内连接

        select * from employee inner join department on employee.dep_id = department.id;

3）外连接

左外连接 表1 left join 表2 on 条件

左表全部及右表关联的部分

        select * from employee left join department on employee.dep_id = department.id;

右外连接

4）内外连接区别

        内连接是全部筛选，外连接是左右为主

5）自关联查询（同一表，用别名区分）

        select e1.name,e2.name from emp e1 left join emp as emp2 on e1.mgr = e2.id;

6）子查询

单行单列

        查年龄为最大年龄的人

                select * from employee where age = (select max(age) from employee);

多行单列

in、any、all

-- in
    where dep_id in (select id from depa where dep_name in ('研发部','学工部'))
-- 查询部门id大于任意一个员工所在部门的部门信息
    where id > any(select distinct dep_id from employee)
-- 大于所有
    where id > all(select dep_id from employee)

distinct 去重

        select * from department where id > any(select distinct dep_id from employee)

        exists 只要查询有结果，前面语句就执行

        select * from department where exists (select * from employee where name = '卫鞅');

多行多列

        员工年龄大于20的员工信息和所在部门信息

        select * from department, employee where employee.dep_id = department.id and employee.age > 20;
        select * from department t1, (select * from employee where age > 20) t2 where t1.id = t2.dep_id;

8. E-R实体关系模型