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 | |
默认值 | 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;