首先，我们先聊一聊数据库的数据模型：
关系型数据库（RDBMS）
：建立在关系模型基础上，由多张相互连接的二维表组成的数据库。
那什么是二维表呢?
1.sql简介
1.1SQL通用语法
1、SQL语句可以单行或多行书写，以分号结尾
2、SQL语句可以使用空格/缩进来增强语句的可读性。
3、MySQL数据库的SQL语句不区分大小写。
4.注释：
单行注释： -- 或者 #
多行注释： /* 内容 */
1.2分类
SQL语句根据其功能被分为四类：DDL、DML、DQL、DCL
| 分类 | 全称 | 说明 |
|---|---|---|
| DDL | Data Definition Language | 数据定义语言，用来定义数据库对象(数据库，表，字段) |
| DML | Data Manipulation Language | 数据操作语言，用来对数据库表中的数据进行增删改 |
| DQL | Data Query Language | 数据查询语言，用来查询数据库中表的记录 |
| DCL | Data Control Language |
数据控制语言，用来创建数据库用户、控制数据库的访问权限 |
操作数据库的流程
-
数据库设计阶段
-
参照页面原型以及需求文档设计数据库表结构
-
-
数据库操作阶段
-
根据业务功能的实现，编写SQL语句对数据表中的数据进行增删改查操作
-
-
数据库优化阶段
-
通过数据库的优化来提高数据库的访问性能。优化手段：索引、SQL优化、分库分表等
-
2.数据库操作
DDL：数据操作语言
查询所有数据库
show databases;
查询当前数据库
select database();
创建数据库
语法：
create database [if not exists] 数据库名;
案例：创建一个itcast数据库
create database itcast;
注意：在同一个数据库服务器中，不能创建两个名称相同的数据库，否则将会报错
可以使用if not exists来避免这个问题
-- 数据库不存在,则创建该数据库；如果存在则不创建
create database if not extists itcast;
使用数据库:
语法：
use 数据库名;
删除数据库
drop database [if exists] 数据库名;
如果删除一个不存在的数据库，将会报错。
可以加上参数 if exists ，如果数据库存在，再执行删除，否则不执行删除。
上述语法中的database，也可以替换成 schema
表操作：
create table 表名（
字段1 字段1类型 [约束] [comment 字段1注释 ],
字段2 字段2类型 [约束] [comment 字段2注释 ],
......
字段n 字段n类型 [约束] [comment 字段n注释 ]
[ comment 表注释 ] ;
案例：创建tb_user表
建表语句：
create table tb_user (
id int comment 'ID,唯一标识', # id是一行数据的唯一标识（不能重复）
username varchar(20) comment '用户名',
name varchar(10) comment '姓名',
age int comment '年龄',
gender char(1) comment '性别'
) comment '用户表';
约束
概念：所谓约束就是作用在表中字段上的规则，用于限制存储在表中的数据。
作用：就是来保证数据库当中数据的正确性、有效性和完整性。（后面的学习会验证这些）
|
约束 | 描述 | 关键字 |
|---|---|---|
| 非空约束 | 限制该字段值不能为null | not null |
| 唯一约束 | 保证字段的所有数据都是唯一、不重复的 | unique |
| 主键约束 | 主键是一行数据的唯一标识，要求非空且唯一 | primary key |
| 默认约束 | 保存数据时，如果未指定该字段值，则采用默认值 | default |
| 外键约束 | 让两张表的数据建立连接，保证数据的一致性和完整性 | foreign key |
注意：约束是作用于表中字段上的，可以在创建表/修改表的时候添加约束。
案例：创建tb_user表
-
对应的结构如下：
在上述的表结构中:
-
id 是一行数据的唯一标识
-
username 用户名字段是非空且唯一的
-
name 姓名字段是不允许存储空值的
-
gender 性别字段是有默认值，默认为男
建表语句：
create table tb_user(
id int primary key auto_increment,
username varchar(20) not null unique ,
name varchar(10) not null ,
age int comment ,
gender char(1) default '男'
)
主键自增：auto_increment
-
每次插入新的行记录时，数据库自动生成id字段(主键)下的值
-
具有auto_increment的数据列是一个正数序列开始增长(从1开始自增)
数据类型：
|
类型 | 大小 | 有符号(SIGNED)范围 | 无符号(UNSIGNED)范围 | 描述 |
|---|---|---|---|---|
| TINYINT | 1byte | (-128，127) | (0，255) | 小整数值 |
| SMALLINT | 2bytes | (-32768，32767) | (0，65535) | 大整数值 |
| MEDIUMINT | 3bytes | (-8388608，8388607) | (0，16777215) | 大整数值 |
| INT/INTEGER | 4bytes | (-2147483648，2147483647) | (0，4294967295) | 大整数值 |
| BIGINT | 8bytes | (-2^63，2^63-1) | (0，2^64-1) | 极大整数值 |
| FLOAT | 4bytes | (-3.402823466 E+38，3.402823466351 E+38) | 0 和 (1.175494351 E-38，3.402823466 E+38) | 单精度浮点数值 |
| DOUBLE | 8bytes | (-1.7976931348623157 E+308，1.7976931348623157 E+308) | 0 和 (2.2250738585072014 E-308，1.7976931348623157 E+308) | 双精度浮点数值 |
| DECIMAL | 依赖于M(精度)和D(标度)的值 | 依赖于M(精度)和D(标度)的值 | 小数值(精确定点数) |
查询当前所有表
show tables;
查看指定表字段
desc 表名;
查询指定表的建表语句
show create table 表名;
修改
添加字段：
alter table 表名 add 字段名 类型（长度） [comment 注释] [约束];
案例： 为emp表添加字段qq，字段类型为 varchar(10)
alter table emp add qq varchar(10);
修改项目类型
alter table 表名 modify 字段名 新字段类型（长度）;
alter table 表名 change 旧字段名 新字段名 类型(长度) [comment 注释] [约束];
log:修改qq字段的字段类型，将其长度由10修改为12
alter table emp modify qq varchar(12);
修改qq字段名为 qq_num，字段类型varchar(13)
alter table emp change qq qq_num varchar(12);
删除字段
alter table 表名 drop 字段名;
案例：删除emp表中的qq_num字段
alter table emp drop qq_num;
修改表名
rename table 表名 to 新名;
案例：将当前的emp表的表名修改为emp01
rename table emp to emp01;
删除表语法：
drop table [if exists] 表名;
DML:数据定义语言
增加insert
向指定字段添加数据
insert into 表名 (字段名1, 字段名2) values (值1, 值2);
全部字段添加数据
insert into 表名 values (值1, 值2, ...);
批量添加数据（指定字段）
insert into 表名 (字段名1, 字段名2) values (值1, 值2), (值1, 值2);
批量添加数据（全部字段）
insert into 表名 values (值1, 值2, ...), (值1, 值2, ...);
修改update
update 表名 set 字段名1 = 值1 , 字段名2 = 值2 , .... [where 条件] ;
删除（delete）
delete from 表名 [where 条件] ;
DQL:数据查询语言
查询关键字：SELECT
语法：
select
字段列表
from
表名列表
where
条件列表
group by
分组字段列表
having
分组后条件列表
order by
排序字段列表
limit
分页参数
基本查询：
查询多个字段：
select 字段1, 字段2, 字段3 from 表名;
查询所有字段（通配符）
select * from 表名;
设置别名
select 字段1 [ as 别名1 ] , 字段2 [ as 别名2 ] from 表名;
去除重复记录
select distinct 字段列表 from 表名;
案例1：查询指定字段 name并返回
select name from emp;
案例2：查询返回所有字段
select * from emp;
案例3：查询已有的员工关联了哪几种职位job(不要重复)
select distinct job from emp;
条件查询
语法：
select 字段列表 from 表名 where 条件列表 ; -- 条件列表：意味着可以有多个条件
|
比较运算符 | 功能 |
|---|---|
| > | 大于 |
| >= | 大于等于 |
| < | 小于 |
| <= | 小于等于 |
| = | 等于 |
| <> 或 != | 不等于 |
| between ... and ... | 在某个范围之内(含最小、最大值) |
| in(...) | 在in之后的列表中的值，多选一 |
| like 占位符 | 模糊匹配(_匹配单个字符, %匹配任意个字符) |
| is null | 是null |
常用的逻辑运算符如下:
| 逻辑运算符 | 功能 |
|---|---|
| and 或 && | 并且 (多个条件同时成立) |
| or 或 || | 或者 (多个条件任意一个成立) |
| not 或 ! | 非 , 不是 |
聚合函数
select 聚合函数(字段列表) from 表名 ;
|
函数 | 功能 |
|---|---|
| count | 统计数量 |
| max | 最大值 |
| min | 最小值 |
| avg | 平均值 |
| sum | 求和 |
count ：按照列去统计有多少行数据。
-
在根据指定的列统计的时候，如果这一列中有null的行，该行不会被统计在其中。
sum ：计算指定列的数值和，如果不是数值类型，那么计算结果为0
max ：计算指定列的最大值
min ：计算指定列的最小值
avg ：计算指定列的平均值
分组排序
语法：
select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件];
案例1：根据性别分组 , 统计男性和女性员工的数量
select gender, count(*)
from tb_emp
group by gender; -- 按照gender字段进行分组
查询入职时间在 '2015-01-01' (包含) 以前的员工 , 并对结果根据职位分组 , 获取员工数量大于等于2的职位
select job, count(*)
from tb_emp
where entrydate <= '2015-01-01' -- 分组前条件
group by job -- 按照job字段分组
having count(*) >= 2; -- 分组后条件
注意事项:
• 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义
• 执行顺序：where > 聚合函数 > having
where与having区别
-
执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤。
-
判断条件不同：where不能对聚合函数进行判断，而having可以。
排序查询
select 字段列表
from 表名
[where 条件列表]
[group by 分组字段 ]
order by 字段1 排序方式1 , 字段2 排序方式2 … ;
排序方式：
asc:升序（默认值）
desc:降序
案例1：根据入职时间, 对员工进行升序排序
select id, username, password, name, gender, image, job, entrydate, create_time, update_time
from tb_emp
order by entrydate ASC; -- 按照entrydate字段下的数据进行升序排序
select id, username, password, name, gender, image, job, entrydate, create_time, update_time
from tb_emp
order by entrydate; -- 默认就是ASC（升序）
分页查询：
select 字段列表 from 表名 limit 起始索引, 查询记录数 ;
案例1：从起始索引0开始查询员工数据, 每页展示5条记录
select id, username, password, name, gender, image, job, entrydate, create_time, update_time
from tb_emp
limit 0 , 5; -- 从索引0开始，向后取5条记录
注意事项:
-
起始索引从0开始。 计算公式 ： 起始索引 = （查询页码 - 1）* 每页显示记录数
-
分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT
-
如果查询的是第一页数据，起始索引可以省略，直接简写为 limit 条数
多表查询
一对多（多对一）
多对多
一对一
外键约束：让两张表的数据建立连接，保证数据的一致性和完整性。
对应的关键字：foreign key
-- 创建表时指定
create table 表名(
字段名 数据类型,
...
[constraint] [外键名称] foreign key (外键字段名) references 主表 (主表列名)
);
-- 建完表后，添加外键
alter table 表名 add constraint 外键名称 foreign key(外键字段名) references 主表(主表列名);
一对一
一对一关系表在实际开发中应用起来比较简单，通常是用来做单表的拆分，也就是将一张大表拆分成两张小表，将大表中的一些基础字段放在一张表当中，将其他的字段放在另外一张表当中，以此来提高数据的操作效率。
多对多
多对多的关系在开发中属于也比较常见的。比如：学生和老师的关系，一个学生可以有多个授课老师，一个授课老师也可以有多个学生。在比如：学生和课程的关系，一个学生可以选修多门课程，一个课程也可以供多个学生选修。
-
关系：一个学生可以选修多门课程，一门课程也可以供多个学生选择
-
实现关系：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键
-- 学生表
create table tb_student(
id int auto_increment primary key comment '主键ID',
name varchar(10) comment '姓名',
no varchar(10) comment '学号'
) comment '学生表';
-- 学生表测试数据
insert into tb_student(name, no) values ('黛绮丝', '2000100101'),('谢逊', '2000100102'),('殷天正', '2000100103'),('韦一笑', '2000100104');
-- 课程表
create table tb_course(
id int auto_increment primary key comment '主键ID',
name varchar(10) comment '课程名称'
) comment '课程表';
-- 课程表测试数据
insert into tb_course (name) values ('Java'), ('PHP'), ('MySQL') , ('Hadoop');
-- 学生课程表（中间表）
create table tb_student_course(
id int auto_increment comment '主键' primary key,
student_id int not null comment '学生ID',
course_id int not null comment '课程ID',
constraint fk_courseid foreign key (course_id) references tb_course (id),
constraint fk_studentid foreign key (student_id) references tb_student (id)
)comment '学生课程中间表';
-- 学生课程表测试数据
insert into tb_student_course(student_id, course_id) values (1,1),(1,2),(1,3),(2,2),(2,3),(3,4);
1.多表查询
多表查询：查询时从多张表中获取所需数据
单表查询的SQL语句：select 字段列表 from 表名;
么要执行多表查询，只需要使用逗号分隔多张表即可，如： select 字段列表 from 表1, 表2;
查询用户表和部门表中的数据：
select * from emp dept;
员工表所有的记录(17行)与部门表所有记录(5行)的所有组合情况，这种现象称之为笛卡尔积。
笛卡尔积：笛卡尔乘积是指在数学中，两个集合(A集合和B集合)的所有组合情况。
在多表查询时，需要消除无效的笛卡尔积，只保留表关联部分的数据
在SQL语句中，如何去除无效的笛卡尔积呢？只需要给多表查询加上连接查询的条件即可。
select *
from emp, dept
where emp.dept_id=dept.id;
分类
多表查询可以分为：
连接查询
-
1.内连接：相当于查询A、B交集部分数据
-
2.外连接
-
左外连接：查询左表所有数据(包括两张表交集部分数据)
-
右外连接：查询右表所有数据(包括两张表交集部分数据)
-
-
3.子查询
内连接
内连接查询：查询两表或多表中交集部分数据。
内连接从语法上可以分为：
-
隐式内连接
-
显式内连接
隐式内连接语法：
select 字段列表 from 表1，表2 where 条件..;
显式内连接语法：
select 字段列表 from 表1 [inner] join 表2 on 连接条件 ...;
案例：查询员工的姓名及所属的部门名称
隐式内连接实现
select emp.name,dept.name -- 分别查询两张表中的数据
from emp,dept -- 关联两张表
where emp.dept_id=dept.id; -- 消除笛卡尔积
显式内连接实现
select emp.name,dept.name
from tb_emp inner join dept
on emp.dept_id=dept.id;
多表查询时可以给表起表名
table A as 别名1， table B as 别名2;
table A 别名1， table 别名2;
使用了别名的多表查询：
select e.name,d.name
from emp e inner join dept d
on e.dept_id=d.id;
注意事项:
一旦为表起了别名，就不能再使用表名来指定对应的字段了，此时只能够使用别名来指定字段。
外连接
外连接分为两种：左外连接 和 右外连接。
左外连接语法结构：
select 字段列表 from 表1 left [outer] join 表2 on 连接条件..;
左外连接相当于查询表1(左表)的所有数据，当然也包含表1和表2交集部分的数据。
右外连接语法结构：
select 字段列表 from 表1 right [outer] join 表2 on 连接条件...;
右外连接相当于查询表2(右表)的所有数据，当然也包含表1和表2交集部分的数据。
案例：查询员工表中所有员工的姓名, 和对应的部门名称
-- 左外连接
select emp.name , dept.name
from tb_emp AS emp left join tb_dept AS dept
on emp.dept_id = dept.id;
案例：查询部门表中所有部门的名称, 和对应的员工名称
-- 右外连接
select dept.name , emp.name
from tb_emp AS emp right join tb_dept AS dept
on emp.dept_id = dept.id;
注意事项：
左外连接和右外连接是可以相互替换的，只需要调整连接查询时SQL语句中表的先后顺序就可以了。而我们在日常开发使用时，更偏向于左外连接。
子查询
SQL语句中嵌套select语句，称为嵌套查询，又称子查询。
SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2 ... );
子查询外部的语句可以是insert / update / delete / select 的任何一个，最常见的是 select。
DCL：数据控制语言
MYSQL中有两种方式进行事务的操作：
-
自动提交事务：即执行一条sql语句提交一次事务。（默认MySQL的事务是自动提交）
-
手动提交事务：先开启，再提交
事务操作有关的SQL语句：
| SQL语句 | 描述 |
|---|---|
| start transaction; / begin ; | 开启手动控制事务 |
| commit; | 提交事务 |
| rollback; | 回滚事务 |
手动提交事务使用步骤：
-
第1种情况：开启事务 => 执行SQL语句 => 成功 => 提交事务
-
第2种情况：开启事务 => 执行SQL语句 => 失败 => 回滚事务
事务有哪些特性
-
原子性（Atomicity）：事务是不可分割的最小单元，要么全部成功，要么全部失败。
-
一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态。
-
隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
-
持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。
事务的四大特性简称为：ACID
3.索引
索引(index)：是帮助数据库高效获取数据的数据结构 。
-
简单来讲，就是使用索引可以提高查询的效率。
优点：
-
提高数据查询的效率，降低数据库的IO成本。
-
通过索引列对数据进行排序，降低数据排序的成本，降低CPU消耗。
缺点：
-
索引会占用存储空间。
-
索引大大提高了查询效率，同时却也降低了insert、update、delete的效率。
语法：
创建索引
create [ unique ] index 索引名 on 表名 (字段名,... ) ;
案例：为tb_emp表的name字段建立一个索引
create index idx_emp_name on tb_emp(name);
查看索引
show index from 表名;
案例：查询 tb_emp 表的索引信息
show index from tb_emp;
删除索引
drop index 索引名 on 表名;
案例：删除 tb_emp 表中name字段的索引
drop index emp_name on tb_emp
注意事项：
-
主键字段，在建表时，会自动创建主键索引
-
添加唯一约束时，数据库实际上会添加唯一索引
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
