文章目录
1.数据类型
1.1整数类型
1.2 小数类型
- M 称为精度,表示总共的位数
- D 称为标度,表示小数的位数
- DECIMAL 类型不同于 FLOAT & DOUBLE,DECIMAL 实际是以字符串存放的,它的存储空间并不固定,而是由精度 M 决定的
1.3 日期与时间类型
- DATETIME 的系统默认值是 NULL, 而 TIMESTAMP(时间戳) 的系统默认值是当前时间 NOW();
- DATETIME 存储的时间与时区无关,而 TIMESTAMP 与时区有关
create database day17;
use day17;
create table t_time (
a datetime,
b timestamp
);
insert into t_time(a) values(now());
insert into t_time(b) values(now());
select * from t_time;查看表的数据
# 2. DATETIME 存储的时间与时区无关,而 TIMESTAMP 与时区有关。
set time_zone='+10:00';
select * from t_time;
set time_zone='+8:00';
select * from t_time;
1.4字符串类型
# 字符串类型
# CHAR(M), VARCHAR(M), TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT, ENUM, SET
char(10): 'abc', 'abcd'#固定长度,无论存多大都是M个字节
varchar(10): 'abc', 'abcd'#变长字符串,M为最大长度,多一个字节来存储长度
# tinytext = varchar(255)
#枚举
ENUM 类型总有一个默认值,当ENUM 列声明为NULL,则默认值为NULL。如果 ENUM 列被声明为 NOT NULL,则其默认值为列表的第一个元素
CREATE TABLE t_enum(
a enum('first', 'second', 'third'),
b enum('first', 'second', 'third') not null
);
insert into t_enum(a) values('first');
# insert into t_enum(a) values('forth');
# insert into t_enum(a) values('first,second');#不可以插入两个元素
insert into t_enum(b) values('second');
select * from t_enum;
#集合
create table t_set(
a set('a', 'b', 'c', 'd')
);
insert into t_set values('a');
# insert into t_set values('x');
insert into t_set values('a,b');
insert into t_set values('a,a,c,b,b');#相同的只会存储一个,且按照定义的顺序排序
#insert into t_set values('a,a,c,b,x');
select * from t_set;
1.5 二进制类型
- 字符串类型存储的字符串(字符)
- 二进制类型存储的是二进制数据(字节)
1.6 常见运算符
################################## 常见的运算符 #################################
# 1.算术运算符: + - * / %
# 2. 比较运算符
# 比较 = 和 <=>
# 查看没有辅助角色的英雄有哪些?
select name from heros where role_assist = null; # =无法比较null值
select name from heros where role_assist <=> null;#可以判断null
select name from heros where role_assist is null;
select name from heros where role_assist is not null;
select null = null;#null
select null <=> null;#1
# BETWEEN AND, IN
# 查询最大生命值在[8000, 9000]的英雄有哪些?
select name from heros where hp_max >= 8000 and hp_max <= 9000;
select name from heros where hp_max between 8000 and 9000;
# 查询主要角色定位为法师和战士的英雄有哪些?
select name from heros where role_main = '法师' or role_main = '战士';
select name from heros where role_main in ('法师', '战士');
# LIKE 模糊查询,一般与通配符一起使用。
# 在 MySQL 中, %可以匹配任意个字符, 包括0个字符,_可以匹配一个字符。
# 在不同的 DBMS 中,通配符可能不一样。*, ?
# 查询名字中包含'太'的英雄有哪些?
select name from heros where name like '%太%';
# 查询名字中从第二种字符开始到最后的字符中包含'太'字的英雄有哪些?
select name from heros where name like '_%太%';
# 3.逻辑运算符 AND(&&) OR(||) NOT(!)
# 4.位运算符 & | ~ ^ << >>
2 DMl(数据操作语言)
2.1 添加insert
- 插入的数据应与字段的数据类型相同
- 数据的大小应在列的规定范围内,例如:不能将一个长度为80的字符串加入到长度为40的列中
- 在values中列出的数据
- 字符串和日期型数据应包含在单引号中
- 插入空值 insert into table value(null)
# 增
# 语法:insert into table_name [(column_name, ...)] values (value, ...);
create table t_student (
id int,
name varchar(20),
age int, # 默认值是null
gender enum('female', 'male')
);
create table t_teacher (
id int,
name varchar(20),
age int, # 默认值是null
gender enum('female', 'male')
);
insert into t_student values (1, '刘亦菲', 16, 'female');#省略类型名则按默认顺序插入
insert into t_student (gender, name, id) values ('female', '白秀珠', 2);#指定类名
insert into t_student (id, name) values (3, '赵灵儿'), (4, '王语嫣'), (5, '小龙女');#一次插入多个值
#表移植
insert into t_teacher (select * from t_student);
2.2 改update
UPDATE
- UPDATE语法可以用新值更新原有表行中的各列
- SET子句指示要修改哪些列和要给予哪些值
- WHERE子句指定应更新哪些行。如没有WHERE子句,则更新所有的行
# 改 update
# 语法:update table_name set column_name=value, ... [where条件];
# 注意事项:如果没有where子句,则会更新所有记录
update t_student set age=17 where name='白秀珠';
select * from t_student;#只改白秀珠的年龄
update t_student set age=17;
select * from t_student;#修改了所有age列
update t_student set age=16, gender='female' where name='赵灵儿';#多列修改
delete
- 如果不使用where子句,将删除表中所有数据
- Delete语句不能删除某一列的值(可使用update)删除的单位是行(行)
- 使用delete语句仅删除记录,不删除表本身如要删除表,使用drop table语句
# 删 delete
# 语法:delete from table_name [where条件]
# 注意事项:
# 1. delete删除的单位是记录,如果要删除某个字段的值,应该用update进行更新。
# 2. 如果没有where子句,则会删除表中的所有记录。
# 3. delete不会删除表,只会删除记录。如果要删除表, 应该用drop table
delete from t_student where name='小龙女';#只删这一行
delete from t_student;#全部删除
select * from t_student;
show tables;
数据的的导入和导出(备份和恢复)
############################ 数据库的导出和导入 ###################################
create database honor_of_kings;
# 导入
# 语法1:mysql -u $user -p db_name < 文件(cmd)
# 语法2:source 文件路径(要求登录MySQL) 不需要加分号
# 导出
# 语法:mysqldump -u $user -p db_name > 文件 (cmd)
3 DQl(数据查询语言)
3.1计算表达式和函数的值
# 1. 计算表达式和函数的值
select 2 * 3;
select now();
select version();
select substring('abcd',2, 3); # mysql的索引是从1开始的 #输出bc
select concat('ab', 'cd', 'ef');#拼接
3.2 查询表中字段
# 2. 查询表中的字段
# 查询单个字段的值,比如:查询 heros 表中所有英雄的名字。
select name from heros;
# 查询多个字段的值,多个字段之间用 `,` 分隔。
# 查询 heros 表中所有英雄的名字,最大生命值,最大法力值以及主要角色定位。
select name, hp_max, mp_max, role_main from heros;
# 还可以用 `*` 代指所有字段。比如:查询 heros 表中所有数据。
select * from heros;
注意:在生产环境中,尽量不要使用 * 通配符。因为查询不必要的数据会降低查询和应用程序的 效率!
3.3 where过滤语句
# 3. 使用 WHERE 子句过滤记录
# 查询主要角色定位为'法师'的英雄有哪些?
select name from heros where role_main='法师';
# 查询最大生命值在[7000,8000]范围内的英雄有哪些?
select name from heros where hp_max between 7000 and 8000;
# 查询姓'张'的英雄有哪些?
select name from heros where name like '张%';
3.4 给字段起名
# 4. 给字段起别名
# `AS` 可以给字段起别名。
select name as '名字', hp_max as '最大生命值' from heros;
注意:① AS 关键字可以省略,但是不推荐这样做。② AS 关键字不仅仅可以给字段起别名,还可以给表起别名
3.5 去除重复行
# 5. 去除重复行
# 查看英雄的攻击范围有哪些?
select distinct attack_range from heros;
select distinct role_main, attack_range from heros;
注意:① DISTINCT 是对所有查询字段的组合进行去重,也就是说每个字段都相同,才认为两条
记录是相同的。② DISTINCT 关键字必须放在所有查询字段的前面
3.6 排序
# 6. 排序
# `ORDER BY` 可以对结果集进行排序。`ASC` 表示升序,`DESC` 表示降序,默认情况为升序。
SELECT name, hp_max FROM heros ORDER BY hp_max; #默认升序
SELECT name, hp_max FROM heros ORDER BY hp_max ASC; #指定升序
SELECT name, hp_max FROM heros ORDER BY hp_max desc; #指定降序
# 还可以对多个字段进行排序。即先按照第一个字段排序,当第一个字段相同时,再按照第二个字段排序,依此类推。
SELECT name, hp_max, mp_max FROM heros ORDER BY hp_max, mp_max DESC;
# `ORDER BY` 可以对非选择字段进行排序,也就是说排序的字段不一定要在结果集中
SELECT name, hp_max FROM heros ORDER BY hp_max, mp_max DESC;
# 甚至,我们还可以对计算字段进行排序。
SELECT name, hp_max FROM heros ORDER BY (hp_max + mp_max) DESC;
3.7 限制结果集的数量
# 7. 限制结果集的数量
# `LIMIT` 可以限制结果集的数量。它有两种使用方式:`LIMIT offset, nums` 和 `LIMIT nums OFFSET offset`。
# 我们想查询最大生命值最高的5名英雄
select name from heros order by hp_max desc limit 0, 5;
select name from heros order by hp_max desc limit 5 offset 0; # 推荐使用
# 当 `OFFSET` 为 0 的时候,我们可以将其省略。
select name from heros order by hp_max desc limit 5;
# 使用 `LIMIT` 可以很方便地实现分页查询。 # rows, page_num
# limit rows offset (page_num - 1) * rows
3.8 计算字段
# 8. 计算字段
# 计算字段并不实际存在于数据库表中,它是由表中的其它字段计算而来的。一般我们会给计算字段起一个的别名。
SELECT name, hp_max + mp_max AS total_max FROM heros;
3.9 聚合函数
- 聚合函数是对某个字段的值进行统计的(一列),而不是对某条记录进行统计。如果想计算某个学生各科成绩的 总分,那么你应该使用计算字段
- 聚合函数往往是搭配分组使用的。如果没有分组,那么聚合函数统计的是整个结果集的数据;如果分组 了,那么聚合函数统计的是结果集中每个组的数据
- SQL 中一共有 5 个聚合函数。分别为 COUNT() , SUM() , AVG() , MAX() , MIN()
# 9. 聚合函数
# COUNT(), SUM(), AVG(), MIN(), MAX()
# COUNT
# `COUNT(*)` 可以统计记录数。
# 查看heros表中有多少条记录, 可以包含null行。
select count(*) from heros;
create table tmp(
a int
);
insert into tmp values (1),(2),(null);
select * from tmp;
select count(*) from tmp;
# `COUNT()` 作用于某个具体的字段,可以统计这个字段的非 `NULL` 值的个数
select count(a) from tmp;
# SUM() `SUM()` 用于统计某个字段非 `NULL` 值的和。
SELECT SUM(hp_max) FROM heros;
select sum(a) from tmp;
select 1 + 2 + null;#null
# `AVG()` 用于统计某个字段非 `NULL` 值的平均值。
SELECT AVG(hp_max) FROM heros;
select round(avg(hp_max), 2) from heros;#round四舍五入
select avg(a) from tmp; # 1.5,会自动忽略bull行
# `MAX()` 用于统计某个字段非 `NULL` 值的最大值
select max(hp_max) from heros;
select max(a) from tmp;
# `MIN()` 用于统计某个字段非 `NULL` 值的最小值。
select min(hp_max) from heros;
select min(a) from tmp;
# 我们还可以对字段中不同的值进行统计。先用 `DSITINCT` 去重,再用聚合函数统计。
select count(hp_max) from heros;
select count(distinct hp_max) from heros;
select sum(hp_max) from heros; # 454053
select sum(distinct hp_max) from heros; # 405884
3.10 分组
# 10.分组
# `GROUP BY` 可以对记录进行分组。
# a.搭配聚合函数使用
# 按照主要角色定位进行分组,并统计每一组的英雄数目。
select role_main, count(*) from heros group by role_main;
# 按照次要角色定位进行分组,并统计每一组的英雄数目。
select role_assist, count(*) from heros group by role_assist;
# b. GROUP_CONCAT
# 按照主要角色定位进行分组,并查看每一组的英雄名字?
# select role_main, name from heros group by role_main;
select role_main, group_concat(name) from heros group by role_main;
# c. 我们可以对多个字段进行分组。也就是说,每个字段的值都相同的记录为一组。
SELECT COUNT(*) AS num, role_main, role_assist
FROM heros
GROUP BY role_main, role_assist
ORDER BY num DESC;
# d. HAVING 过滤分组,先分组后过滤
# 我们想要按照英雄的主要角色定位,次要角色定位进行分组,
# 并且筛选分组中英雄数目大于 5 的组,最后根据每组的英雄数目从高到低进行排序.
select count(*) as num, role_main, role_assist
from heros
group by role_main, role_assist
having num > 5
order by num desc;
WHERE 和 HAVING 的区别:WHERE 和 HAVING 都可以用来过滤数据
但是两者有着很明显的区别
WHERE 是分组前用来过滤记录的
HAVING 是分组后用来过滤分组的
# 筛选最大生命值大于6000的英雄,按照主要角色定位,次要角色定位分组,
# 并且筛选英雄数目大于 5 的分组,最后按照英雄数目从高到低进行排序。
select count(*) as num, role_main, role_assist
from heros
where hp_max > 6000
group by role_main, role_assist
having num > 5
order by num desc;
select hp_max from heros
group by hp_max;
虽然 DBMS 实现的时候,往往会对分组进行排序。但是如果没有明确的 ORDER BY 子句, 我们就不应该假定结果集是有序的
3.11 Select顺序
语法中关键字的顺序
SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT
语句的执行顺序
FROM --> WHERE --> GROUP BY --> HAVING --> SELECT --> DISTINCT --> ORDER BY -->LIMIT
SQL执行顺序
- 首先是从 FROM 开始执行的。在这个阶段,如果是多表连接查询,还会经历以下几个步骤:
1.1 通过 CROSS JOIN 求得笛卡尔乘积,得到虚拟表 vt1-1;
1.2 通过 ON 进行连接,在 vt1-1 的基础上进行筛选,得到虚拟表 vt1-2;
1.3 如果是外连接,还会在 vt1-2 的基础上添加外部行,得到虚拟表 vt1-3;
1.4 如果连接的表不止两张,还会重复上面步骤,直到所有表都处理完成。这个过程完成之后,我 们就得到了虚拟表 vt-1,也就是我们的原始数据。 - WHERE 会在 vt-1 的基础上进行筛选,得到虚拟表 vt-2。
- GROUP BY 会在 vt-2 的基础上进行分组,得到虚拟表 vt-3
- HAVING 会在 vt-3 的基础上对分组进行筛选,得到虚拟表 vt-4
- SELECT 会在 vt-4 的基础上提取想要的字段,得到虚拟表 vt-5
- DISTINCT 会在 vt-5 的基础上,去掉重复行,得到虚拟表 vt-6
- ORDER BY 会按照指定的字段对 vt-6 进行排序,得到虚拟表 vt-7
- LIMIT 会在 vt-7 的基础上提取指定的记录,得到虚拟表 vt-8
- 当然,我们在写 SQL 语句的时候不一定存在所有的关键字,那么相应的阶段就会省略