目录
一、SQL数据库的基本类型
1、基础知识
创建数据库
| show databases | 查看所有数据库 |
| show create database 数据库名 | 查看数据库创建规则 |
| create database 数据库名 | 创建数据库名 |
| create database 数据库名 character set 字符集 | 创建一个指定字符集的数据库 |
| create database 数据库名 character set 字符集 collate 字符集_bin | 创建一个使用字符集并校对规则的数据库 |
删除数据库
| drop database 数据库名 | 删除数据库 |
修改数据库字符集
| alter database 数据库名 character set 字符集 | 修改数据库的字符集 |
| alter database 数据库名称 character set 字符集 collate 比较规则 | 修改数据库编码集 |
切换数据库
| select database（）； | 查询正在使用的数据库 |
| use 数据库名 | 切换数据库 |
约束的目的：只是为了保证存储在数据表中的数据完整性和有效性
唯一约束unique 该字段的值不允许重复 非空约束not null 该字段的值不能为空 主键约束 primary key
- 主键只能表示数据表中的某一列，用来区分此列和其它数据的不同
- 效果=唯一 + 非空
- 主键的选型：不建议使用和业务密切相关的字段充当，创建一个与业务无关的字段充当
- 主键可以有多个：如果有一个主键，称为唯一主键，如果有多个主键，称为联合主键
创建表
| show tables | 查看该数据库中所有表 |
| create table 表名（列名 类型（长度）。。。） | 在数据库中创建表 |
| desc 表名 | 查看表结构 |
| show create table 表名 | 查看数据表的创建信息 |
| drop table表名 | 删除表 |
单表创建时约束
| 列名 列的类型 unique | 该列的值不允许重复（一个表中唯一约束的列可以有很多列） |
| 列名 列的类型 not null | 该字段的值不能为空 |
| 列名 列的类型 primary key | 将该列设置为主键 |
| 列名 列的类型 auto_increment | 该列自增长（无需指定值） |
修改表的结构alter
增加 add
修改现有列的类型、长度和约束 modify
修改列名 change
删除 drop
| alter table 表名 增/删/改 列名 类型（长度） 约束； | |
| alter table 表名 add 列名 类型（长度） 约束 | 增加列 |
| alter table 表名 modify 列名 类型（长度） 约束 | 修改现有列类型、长度和约束语法 |
| alter table 表名 change 旧列名 新列名 类型（长度） 约束 | 修改现有列名 |
| alter table 表名 drop 列名 | 删除列 |
| rename table 旧表名 to 新表名 | 修改表名 |
数据表的增删改查（CRUD）
| insert into 表名（列名，列名。。。）values（值，值，。。。） | 向表中插入数据 |
| insert into 表名 value（值，值。。。） | 向表中所有列插入数据，可以省略列名 |
| insert into 表名（列名，列名） values（，值） | 若当前字段有默认值，在插入是可以省略 |
| select * from 表名 where 条件语句； | 根据条件查询表中数据 |
| select 列名，列名 from 表名 where 条件语句 | 根据条件查询指定列的所有数据 |
| update 表名 set 列名=值，列名=值。。。where 条件语句 | 数据记录修改 |
| delete from 表名 where 条件语句 | 数据库记录的删除（若没有where条件，将删除表中的所有数据） |
| select * from 表名 order by 列名 asc|desc | asc升序排列，desc降序排列 |
| select 列名 as 别名，列名 as 别名 from 表名 where 条件语句 | 可以对查询出来的列名起别名（在使用别名时，as可以省略） |
| select distinct 列名 from 表名 where 条件语句 | 重复的数据只查询一次 |
| select * from 表名 limit n，m； | 截取查询从下标n开始，m结束 |
数据表中的默认值：
- 如果某个字段被设置为了主键约束，那么则不可以设置默认值。
- 如果某个字段被设置为了唯一约束，那么第一个插入的数据可以不用赋值，结果使用默认值，但是第二个插入数据在赋值时，一定要重新赋值，因为唯一约束不允许出现重复的值。
字符集：
- MySQL有六处使用了编码表，分别为：client、connection、database、results、server、system
2、面试题
delete与drop的关系：
- delete from person：把person表中的所有数据删除，但是person表还在
- drop table person：将person数据表从数据库中删除
delete与truncate的区别：
- delete删除表中的数据是按照逐行删除，效率低
- cruncat它是先把表删除掉，再把表创建出来，效率高
二、MySql运算符
| = | 相等 |
| <> | 不等 |
| and | 逻辑与 |
| or | 逻辑或 |
| not | 逻辑非 |
| between...and... | 在两者之间取值 |
| in（值，值，值） | 在指定值中取一个 |
| like 'pattern' | 模糊查询 like '张%' 查询姓张的，字数不限 like '张_'查询名字两个字，姓张的 |
| null非空判断 |
|
三、SQL中的函数
1、基础知识
函数：
聚合函数，分组函数，聚集函数，内建函数，合计函数......
SQL语言中定义了部分函数：
count（）函数：统计个数
sum（）函数：求和
avg（）函数：求平均值
max（）、min（）函数：求最大值和最小值
count函数：
select count（*）|count（列名） from 表名 where 条件；
#count（*）：统计某一列的数据，包含null
#count（列名）：统计指定列的数据，不包含null
sum函数：
select sum（列名）from 表名 where 条件；查询某列的总和
select sum（列1 + 列2） from 表名；多列求总和
使用上述语句发现统计的和值结果是不正确的，原因是如果使用sum() 多列进行求和的时候，如果某一列中的值有null，这一列所在的行中的数据结果为0，null和任何数据相加都等于0。
可以使用mysql数据库提供的函数：ifnull（列名，值）
select sum（ifnull（列1，值）+ifnul（列2，值））from 表名
avg函数：
select avg（列名）from 表名 where 条件；
max，min函数：
select max（列名），min（列名）from 表名 where 条件；
group by 分组函数：它可以根据指定列对数据进行归类，如果这列中有重复数据会被合并成一个
select * from 表名 group by 列名；
where和having的区别：
聚合函数：SQL中一种特殊的函数，例如：sum、max、min、avg、count等。与其它函数的根本区别是——它们一般作用在多条记录上。
where：约束声明，是在返回之前起作用，where中不能使用聚合函数。
having：过滤声明，是在查询返回结果集以后对查询的结果进行过滤，在having中可以使用聚合函数。
用having就一定要和group by连用，
用group by不一定有having（它只是一个筛选条件）
只要条件里面的字段，不是原表里面原先有的字段就需要用having。SQL在查询表的时候先把查询的字段放到了内存里，而where查询的时候是从表里面查的。
2、练习
#注这里的表名必须是 orders，不能写成 order（命令的意思）
create table orders(
id int,
product varchar(20),
price float
);
#插入数据（插入有省略写法）
#插入非省略写法
insert into orders(id,product,price) values(1,'电视',900);
#插入省略写法，需要注意省略列名后，列对应的值必须全部按顺序写
insert into orders values(2,'洗衣机',100);
insert into orders values(3,'洗衣粉',90);
insert into orders values(4,'桔子',9);
insert into orders values(5,'洗衣粉',90);
#1、对订单表中商品归类，显示每一类商品的总价
select product,sum(price) from orders group by product;
#2、查询购买商品价格总和超过150的商品
select product,sum(price) from orders group product where sum(price) >150;//where 后面不能跟聚合函数，此 sql 会报错
select product,sum(price) from orders group by price having sum(price) >150;3、面试题
where 和 having 都可以完成数据的条件判断，但是 having 后面可以跟上述的函数。
四、设计表和多表操作
实际开发中，一个项目中的数据，一般都会保存在一个数据库中，但是不能所有的数据都保存在一张数据表中，不同类型的数据需要保存在不同的数据表中。
可是有时不同的数据之间会有一定的关系，所以把不同的数据保存在不同的数据表中之后，还需要维护这些数据之间的关系。这时就要求设计表的人员，设计不同表之间的互相关系。
1、外键约束
外键：在一个表中去引用另外一张表的主键作为该表的字段，这个字段被称为外键。一旦有了外键，我们就可以理解为表与表之间产生了外键约束。
作用：减少数据的冗余，维护多表之间的数据的完整性，减少垃圾数据。、
主表：主键被应用的表。
从表：存在外键的表。
#表已经存在添加外键语法
alter table 表名 add foreign key(列名） references 被引用表名(引用表的列)；
#创建表时添加外键语法
foreign key(当前表中的列表) references 被引用的表名（被引用表的列名）;2、外键练习
#准备员工表单
create table emp(
id int primary key auto_increment,
ename varchar(30) not null,
did int not null
);
#插入数据
insert into emp values(null,'张三',1);
insert into emp values(null,'李四',1);
insert into emp values(null,'王五',2);
insert into emp values(null,'赵六',11);
#准备部门表单
create table depa(
id int primary key auto_increment,
dname varchar(30) not null,
starttime date not null
);
#插入数据
insert into depa(dname,starttime) vlaues('技术部','2020-10-01');
insert into depa(dname,starttime) vlaues('产品部','2020-09-18');
#可以看到上述示例中无外键约束，两个表之间没有任何的联系,这里需要添加外键约束
alter table emp add foreign key(did） references depa(id);如果添加的外键的值在主表中不存在的，则无法添加成功，要先添加主表数据，再添加从表数据。
如果从表的外键还在引用者主表的数据，则主表该数据不能被删除，要先删除从表数据后，才可以删除主表的数据。
#给 emp（从表） 表中添加数据
insert into emp values(6,'张三丰',3);//did 对应的主键中并没有 3 这个值，无法添加
insert into emp values(6,'张三丰',1);//主键的值必须要存在3、一对多关系
一对多的关系在开发系统中比较常见的一种数据关系，例如员工与部门之间的关系。
员工和部门：
一个员工只能入职到一个部门，一个部门可以有很多的员工
作者和作品：
一个作者可以写多部作品，一部作品只能有一个作者。
4、多对多关系
多对多的关系在实际的项目开发中非常常见，比如程序员和项目的关系，顾客和商品，学生和老师的关系等。
程序员和项目：
一个程序员可以参与多个项目的开发，一个项目可以由多个程序员来开发。
顾客和商品：
一个顾客可以购买多件商品，同一类商品，可以被多个顾客购买。
老师和学生：
一个老师可以教多个学生，一个学生可以被多个老师教。
5、多对多实例
案例一：程序员与项目
#创建程序员表
create table coder(
id int primary key auto_increment,
salary double,
name varchar(30)
);
#创建项目表
create table project(
id int primary key auto_increment,
name varchar(100)
);
#创建第三张关系表，维护程序员和项目之间的关系
create table coder_project(
c_id int,
p_id int
);
#添加外键约束
alter table coder_project add foreign key(c_id) reference coder(id);
alter table coder_project add foreign key(p_id) reference coder(id);实例二：学生成绩表设计案例
需求：设计学生成绩管理系统数据表
1、每个教师可以教多门课程
2、每个课程由一个老师负责
3、每门课程可以由多个学生选修
4、每个学生可以选修多门课程
5、学生选修课程要有成绩
分析：
需求中存在的实体：学生、课程、老师
学生与课程之间：多对多关系，一个学生可以选择多门课程，一门课程可以被多个学生选修。
课程与老师之间：一对多，一个老师可以代多门课程，一门课程只能由一个老师代课。
课程的成绩不可以放在学生表中，因为学生是选修课程，如果成绩放入学生表中会造成数据冗余；同理，课程的成绩也不能放在课表中。
#创建学生表
create table student(
id int primary key auto_increment,
name varchar(40)
);
#创建教师表
create table teacher(
id int primary key auto_increment,
name varchar(40)
);
#创建课程表
create table course(
id int primary key auto_increment,
name varchar(50),
#需要在课程表中添加教师的主键作为当前表的外键
t_id int,
#使用外键约束
foreign key(t_id) references teacher(id)
);
#创建第三张关系表，维护学生与课程之间的关系
create table student_course(
s_id int,
c_id int,
score double,
#添加外键约束
foreign key(s_id) references student(id),
foreign key(c_id) references course(id)
);五、数据库设计三大范式
第一范式
要求表的每个字段是不可分割的独立单元。
第二范式
在第一范式的基础上，要求每张表只能表达一个意思，表的每个字段和表的主键有依赖。
第三范式
在第二范式的基础上，要求每张表的主键之外的其它字段都只能和主键有直接决定依赖关系，这里讲的就是外键的使用。
六、多表查询
# 表A保存水果的名称
create table A(
A_id int primary key auto_increment,
A_name varchar(20) not null
);
insert into A vlaues(1,'苹果');
insert into A values(2,'橘子');
insert into A vlaues(3,'香蕉');
# 表B保存水果的价格
create table B(
B_id int primary key auto_increment,
B_price double
);
insert into B values(1,2.30);
insert into B values(2,3.50);
insert into B values(4,null);笛卡尔积：
把两个（多个）表的结果集相乘，A表中的每一条数据与B表中每一条数据进行匹配并呈现，数量级就是两个表的乘积，属性为列相加。
1、内连接查询
语法一：select * from 表名1，表名2 where 表名1.列名 = 表名2.列名；
语法二：select * from 表名 inner join 表名 on 条件；
2、外连接查询
外连接分为：左外连接、右外连接、全（外）连接、自连接
左外连接
用左边的表去右边的表中查询对应记录，不管是否找到，都将显示左边表中的全部记录。
语法：select * from 表1 left outer join 表2 on 条件；
右外连接
用右表的表去坐标表查询对应记录，不管是否找到，右表表全部记录都将显示 。
语法：select * from 表1 right outer join 表2 on 条件;
全外连接
左外连接和右外连接的结果合并，但会去掉重复的记录。
语法：
# mysql 数据库不支持此语法
select * from 表1 full outer join 表2 on 条件;
#在 sql 语句全连接，其实就是左外连接和右外连接之和，并使用 union 去掉重复的数据
select * from 表1 left outer join 表2 on 条件 union all select * from 表2 right outer join 表2 on 条件
3、SQL 关联子查询
子查询：把一个 SQL 语句的查询结果作为另外一个 SQL 语句的查询的参数存在。
例如：查询价格最贵的水果名称——>数据表A水果表，数据表B价格表。
#分析：
#B表保存价格，在B表中查询出最高价格
select max(b_price) from B;
#通过最高价格，找到对应的id
select b_id from B where b_price = (select max(b_price) from B);
#通过 b_id，找到表A中水果的名字。
select a_name from A where a_id =
(select b_id from B where b_price =
(select max(b_price) from b)
);
2257
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
