目录
一、相关概述
1、你知道的储存数据的方式有哪些?
变量，数组等：方便储存但储存时间短
文件：储存时间长但不方便查询
数据库：既方便查询，储存时间也长
2、DBMS 和 数据库的关系?
DBMS：database management system，俗称数据库管理系统，指的是管理数据的软件。
而数据库就是存储数据的地方
3、什么是关系型数据库, 什么是非关系型数据库?
数据库可以分为两大类：关系型数据库和非关系型数据库。
关系型数据库(RDBMS, Relation DataBase Management System)指的是表的每列之间的数据有关联，之间的联系是数据库。如：MySQL
非关系型数据库(NoSQL, Not Only SQL)值得是数据之间没有联系，常用键值对来绑定
4、你知道的常用的数据库有哪些?
关系型: MySQL, Oracle, DB2, SQLServer, SyBase, SQLite.
非关系型: Redis, HBase, MongoDB
二、SQL语法介绍
1、通用语法
1.可以写一行，多行，分号结尾即可
2.阅读性：可以空格，换行，\t
3.不区分大小写
4.注释写法：
- -- 注释
- # 注释
- /* 注释 */
2、常用数据类型
- 整数型：int
- 浮点型：float， double
- 字符型：vatrchar
- 日期：date， datetime
3、语言类型
1.DDL：数据定义语言
主要是操作数据库，数据表，字段的增删改查(CURD)
关键字：create 增，drop 删，alter 改，show 查
2.DML：数据操作语言
主要是操作表数据的增删改
关键字：insert 增，delete 删，update 改
3.DQL：数据查询语言
主要是对表数据进行查询
关键字：select，from，update
4.DCL：数据定义语言
主要是创建用户，更改用户权限
三、DDL语句
1、操作数据库
增：create database [if not exists] 数据库名 [charset ‘码表名’];
例如：create database if not exists student charset ‘utf8’;
删：drop database 数据库名;
例如：drop database student;
改：alter database 数据库名 charset ‘码表名’;
例如：alter database student charset ‘gbk’;
查：show databases;
show create database 数据库名 [charset ‘码表名’]; # 查看建库过程
select database();
例如：show databases;
show create database student charset ‘gbk’;
select database();
切库：use 数据库名
例如：use student;
2、操作数据表
增：create table [if not exist] 数据表名(
字段01 数据类型 [约束],
字段02 数据类型 [约束]
);
例如：create table if not exist student(
sid int primary key auto_increment, # 此处为主键约束+自增，后续章节会有解答
name varchar(20),
address varchar(20)
);
删：drop table 数据表名;
例如：drop table student;
改：alter table 旧数据表名 rename 新数据表名;
rename table 旧数据表名 to 新数据表名; # 与上面同理
例如：alter table student rename student_01;
rename table student to student_01;
查：show tables;
show create table 数据表名;
desc 数据表名;
例如：show tables;
show create table student;
desc student;
3、操作字段
增：alter table 数据表名 add 字段 数据类型 [约束];
例如：alter table student add address varchar(20) not null；
删：alter table 数据表名 drop 旧字段;
例如：alter table student drop name varchar(20);
改：alter table 数据表名 modify 数据类型 [约束];
alter table 数据表名 change 旧字段 新字段 数据类型 [约束];
例如：alter table student modify int;
alter table student change phone `desc` varchar(20) not null; # 当改后的词为关键词时，需要用反引号框起来（esc下面那个）
查：desc 数据表名;
例如：desc student;
四、DML语句
1、操作表数据
增：
单条：insert into 数据表名 values(值01, 值02, 值03); # 数值需要和字段数量一致
Insert into 数据表名(字段01, 字段02) values(值01, 值02); # 数值需要和前面需要的字段数量一致
多条：insert into 数据表名 values(值01, 值02, 值03),(值01, 值02, 值03),(值01, 值02, 值03);
例如：insert into student values(01, ‘张三’, ‘翻斗花园’); # 单条添加
Insert into student(name, address) values(‘李四’, ‘斯卡罗布集市’); # 指定元素添加
Insert into student values
(null, ‘王五’, ‘月球’),
(null, ’赵六’, ‘M78’);
注意：upsert语句
当我向表中插入一条数据时，如果主键ID重复就会报错，但是我就是要更新这条数据，又不希望删除原始数据，此时可以使用语句更新数据。
格式: insert into 表名(列名1, 列名2...) values(值1, 值2...) on duplicate key update 列名1=值1, 列名2=值2
例如:insert into hero values(15, '张三', 11) on duplicate key update hname='张学友', kongfu_id=20; # 假设主键id已经有过15了，那么此时加数据就会将15号这行数据从原本的改为’张学友’,20;
删：delete from 数据表名 where 条件;
truncate 数据表名;
例如：delete from student where sid > 2; # 会删除sid为2以上的所有数据
truncate student; # 会清除该表的所有表数据
注意：delete与truncate的区别
Delete删除只删除数据，序列不会清除，一般结合事务使用
Truncate删除是直接摧毁当前表，重新创建一个表，序列会重新开始排序，一般不结合事务
改：update 数据表名 set 列名=列值, 列名=列值 where 条件;
例如：update student set name=’小明’, address=’赛博坦’ where id = 2; # 第二条数据的name和address会被改成目标值
2、如何备份表数据
备份表存在:update table 新数据表名 select * from 旧数据表名 where 条件;
例如：update table student_1 select * from student where sid > 1;
备份表不存在:create table 新数据表名 select * from 旧数据表名;
例如：create table student_1 select * from student;
3、单表约束
主键约束：primary key
/*
值唯一，且不为空，通常后面加auto_increment来实现值的自增，因此数据类型只能是整数
主键约束通常对数据本身来说没有任何意义，只作为一组数据的唯一标识
一般是将id设置为主键约束，并设置自增来对数据进行标识
例如：create table teacher(
tid int primary key auto_increment;
name varchar(10);
);
此时我们在插入数据的时候就可以在第一位写null了，tid会自己以之前的最大值+1对新一组数据进行赋值
例如：insert into teacher values(null, ‘卡卡西’);
*/
唯一性：unique
/*
该值在表内唯一存在，不能有重复，但是可以是null
例如：create table teacher(
tid int primary key auto_increment;
phone varchar(11) unique;
);
此时限制了phone的值不能重复，如果重复则建表失败
insert into teacher values(null, ‘1651321’);
*/
非空：not null
/*
该值在表内不能为空，但是可以重复
例如：create table teacher(
tid int primary key auto_increment;
name varchar(10) not null;
);
此时限制了name的值不能为空，如果为空则建表失败
insert into teacher values (null, ‘小埋’);
*/
默认约束：default
/*
如果不填写该值，则默认为默认值
例如：create table teacher(
tid int primary key auto_increment;
address varchar(10) default ‘北京’;
);
此时address如果不填值，则默认为北京，注意！null是空值不是默认，如果需要默认则不填值
例如：insert into teacher(tid) values(10);
此时默认10号老师的address为北京
*/
五、DQL语句--单表查询（重要）
/*
一个完整的单表查询语法格式如下:
select
[distinct] 列名1, 列名2 as 别名, 列名3...
from
数据表名
where
组前筛选
group by
分组字段1, 分组字段2...
having
组后筛选
order by
排序的列1, 列2 [asc | desc]
limit
起始索引, 数据条数;
*/
1、简单查询
select * from 数据表名;
例如：
# 1. 查看学生表中, 所有的数据.
select * from student;
# 2. 查询学生名 和 学生地址.
select stu_name, stu_address from student;
# 3. 起别名, 查询.
select stu_name as ‘学生姓名’, stu_address as ‘学生地址’;
# 4. 查询结果是表达式(即: 算术运算), 即: 将所有缴费的价格 + 10进行显示.
select *, price + 10 from student;
# 5. 去重查询.
# 需求: 查询所有的班级.
select distinct class from student;
2、条件查询
select * from 数据表名 where 条件;
/*
格式:
select * from 数据表名 where 条件;
where后的条件可以写什么:
比较运算符: >, >=, <, <=, =, !=, <>
范围筛选:
in (值1, 值2...);
not in (值1, 值2...);
between 值1 and 值2; # 包左包右
模糊查询:
like '_值'; _ 只能代表任意的1个字符
like '%值'; % 可以代表任意的多个字符.
非空查询:
is null; # 是否为空
is not null; # 是否不为空.
逻辑运算符:
and 逻辑与, 并且的意思, 要求条件都要满足, 有False则整体为False.
or 逻辑或, 或者的意思, 只要满足任意1个条件即可, 有True则整体为True.
not 逻辑非, 取反的意思, True => False, False => True
*/
例如：
# 1.查询商品名称为“花花公子”的商品所有信息：
select * from product where pname = ‘花花公子’;
# 2.查询价格为800商品
Select * from product where price = 800;
Select * from product where price in(800);
# 3.查询价格不是800的所有商品
Select * from product where price != 800;
Select * from product where price <> 800;
# 4.查询商品价格大于60元的所有商品信息
Select * from product where price > 60;
# 5.查询商品价格小于等于800元的所有商品信息
Select * from product where price <= 800;
# 6. 查询商品价格在200到800之间所有商品
Select * from product where price between 200 and 800;
Select * from product where price > 200 and price < 800;
# 7. 查询商品价格是200或800的所有商品
Select * from product where price = 200 or price = 800;
Select * from product where price in (200, 800);
# 8.查询以'香'开头的所有商品
Select * from product where pname like ‘香%’;
# 9.查询第二个字为'想'的所有商品
Select * from product where pname like ‘_想%’;
# 10.查询没有分类的商品
Select * from product where category_id is null;
# 细节: 空表示什么都没有, 所以无法直接通过 = 来判断.
# 11.查询有分类的商品
Select * from product where category_id is not null;
# 12. 查询所有的分类.
Select distinct category_id from product;
3、排序查询
Select * from 数据表名 order by 字段名[asc | desc],字段名[asc | desc]...;
其中asc表示升序查询，desc表示降序查询，默认情况下是升序查询
优先级以填写字段顺序为准
4、聚合查询
Count(),max(),min(),sum(),avg()--聚合函数
例如：select count(*) as ‘数据个数’ from product; # 计算数据个数
Select max(price) as ‘最高价格’ from product; # 计算最高价格
Select min(price) as ‘最低价格’ from product; # 计算最低价格
Select sum(price) as ‘总价’ from product; #计算商品总价
Select avg(price) as ‘平均价格’ from product; # 计算平均价格，不过默认是很多小数
Select round(avg(price), 2) as ‘平均价格’ from product;# Round(数据, 小数位数)
问题：count(*),count(1),count(列名)有什么区别?
1.count(*)和count(1)计算null值，count(列名)不计算null值，也就是说，如果一列数据中有null值，count(*)的数字会大于count(列名)
2.因为不需要额外统计null值，所以count(*)和count(1)的运行效率大于等于count(列名)
而count(*)会统计所有数据，count(1)只统计是否有数据，所以count(1)运行效率大于count(*)，最快的count(主键列)，因为主键列底层逻辑是索引。
运行效率count(主键列) > count(1) > count(*) > count(普通列)
5、分组查询
select
Distinct列名1, 列名2...
from
数据库名
where
组前筛选
group by
分组字段（一般是列名）
having
组后筛选
order by
排序的列 asc
limit
开始的数据, 取多少个;
注意:
1.分组查询的 查询列, 只能出现: 分组字段 或者 聚合函数
2.where是组前筛选, having是组后筛选
3.分组查询一般要结合聚合函数一起使用, 否则没有意义
问题:where和having的区别是什么?
Where:组前查询,后面不能跟聚合函数,因为where是在所有操作之前,先对数据进行筛选
Having:组前查询,后面可以跟聚合函数
例如:统计每类商品的总价格, 只统计单价在500以上的商品信息, 且只显示总价在 2000 以上的分组信息, 然后按照总价升序排列, 求出价格最低的那个分类信息
select
category_id, # 在商品种类里面筛选
sum(price) as total_price # 求商品总和并重命名为total_price
from
product # 表名
where
price > 500 # 组前筛选,只统计单价在500以上的
group by
category_id # 在商品种类组里
having
total_price > 2000 #组后筛选,只统计单价在2000以上的
order by
total_price asc #按照总价升序排序
limit
0,1; #从第一组数据开始取,取一组数据
6、分页查询
数据量过多需要分页显示时，使用该方法进行分页显示
语法格式:limit 起始索引, 数据条数;
起始索引: 表示从索引为几的数据行, 开始获取数据. 数据表中每条数据都有自己的索引, 索引是从0开始的
数据条数: 表示获取几条数据
数据总条数: select count(1) from 表名;
每页的数据总量: 产品经理决定
每页的起始索引: (当前页 - 1) * 每页的数据条数;
总页数: (总条数 + 每页的数据条数 - 1) / 每页的数据条数;
# 从商品表中,按照5条/页的方式获取数据
Select * from product limit 0, 5;
Select * from product limit 5, 5;
Select * from product limit 10, 5;
两种去重方式:
1.distinct
Select distinct category_id, price from product;
2.分组方式
Select category_id from product group by category_id;
六、DQL语句--多表查询(非常重要!!!)
！！！多表查询的精髓就是将多张表合成一张表查询！！！
学会这句话就等于会了一半的多表查询
1、交叉查询
取笛卡尔积，会产生大量的脏数据
格式:select * from 表A, 表B;
2、连接查询
1.场景一:内连接,连接查询,表的交集
格式一:显示内连接,select * from A inner join B on 关联条件 where ...;
Select * from hero inner join kuangfu on hero.kungfuid = kungfu.kid;
Select * from hero join kuangfu on hero.kungfuid = kungfu.kid; # inner可以省去
格式二:隐式内连接,select * from A, B where 关联条件,...;
#当两张表的元素相同时,关联条件需要明确是哪张表的元素 表示方式:表名.列名
Select * from hero,kungfu where hero.kungfu = kungfu.kid;
2.场景二:外连接
格式一:左外连接,select * from A left outer join B on 关联条件 where ..;
结果:左表全集 + 交集
Select * from hero left outer join kungfu on hero.kungfu = kungfu.kid;
Select * from hero left join kungfu on hero.kungfu = kungfu.kid; # outer可以省去
格式二:右外连接,select * from A right outer join B on 关联条件 where...;
结果:右表全集 + 交集
Select * from hero right outer join kungfu on hero.kungfu = kungfu.kid;
3、子查询
一个SQL语句的查询条件，需要依赖另一个SQL语句的查询结果
嵌套在外层的查询叫父查询，内层的查询叫子查询
格式：select * from (select * from 数据表名 where 条件...) where 条件...
使用场景：
(1).当数据量很大时，先查看再筛选会很耗时,可以尝试用子查询先筛选一遍,来达到减少数据量的目的
(2)当一次筛选很难达到目的时,可以用子查询先初步筛一遍数据,再用子查询的结果筛选一遍
注意:子查询嵌套最好不要超过3层,否则运行效率会大大降低
4、自关联（自连接查询）
需要的数据都在一个表内,可以通过自己和自己连接来查询所需数据,多用于查询地址信息时使用,例如某省某市某区,信息都在同一列,就可以通过前后的邮编来连接,进而达到查询目的
七、建表
学习之前可以先了解一下数据库NP,正常使用到达3NP即可
如何判断是什么类型的对应关系？
假如现在有两个表,老师和学生,分别位于左边和右边,先从老师的数据出发到学生的数据,一个老师可以对应多个学生(1 -> n),再从学生的数据出发到老师的数据,一个学生可以对应多个老师(n <- 1),此时对左边两个数据取交集(注意不是数学上的交集,是字符串交集),发现没有交集,此时我们取n,同理对右边两个数据取交集得到n,所以老师和学生是多对多的关系
可以用这个方式分析一下人员表和性别表,会得出结果为一对多,读者可以自己尝试分析
1、一对多建表
当建表时需要将两个表连接,此时需要用到一个约束,叫做外键约束(foreign key),是用来描述多表关系的,一般是在从表中做限定,从表一遍设定为数据较多的一方
在多表关系中,有外键的表被外表(从表),有主键的表成为主表
外键约束特点是外表的外键列不能出现主表的主键列没有的数据
格式:
1.建表时创建外键约束
create table example(
sid int primary key auto_increment
[constraint 外键约束名] foreign key(外键列名) references 主表名(主键列名)
);
2.建表后创建外键约束--常用
alter table 表名 add [constraint 外键约束名] foreign key(外键列名) references 主表名(主键列名);
3.删除外键约束
alter table 表名 drop foreign key 外键约束名;
2、多对多建表
建表原则:新建中间表,这个表至少有两列来充当外键列,分别关联目标两张表的主键列
其中两个外键约束和一对多相同,不再过多赘述,需要强调的是,因为这个中间表是联系两边表的,所以中间表需要避免在对应的时候出现重复数据,以免出现大量脏数据,想要数据不出现重复就需要加约束,于是使用联合主键约束去解决这个问题
格式:
1.建表时创建联合主键约束
create table stu_cur(
id int not null unique auto_increment, # 中间表, 自身id, 伪主键(唯一, 非空, 自增)
外键1名 int, # 学生id
外键2名 int # 选课id
primary key(外键1, 外键2...) # 联合主键, 建表时添加.
)
2.建表后创建联合主键约束--常用
alter table 表名 add primary key(外键1, 外键2....);
3.删除联合主键
alter table 表名 drop primary key;
3、一对一建表
一般有三个方法:主键对应,唯一外键关联和直接创建到一张表中
多用最后一种,将多张一对一的表创建成一张表
4万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
