MySQL（一）增删改查

MySQL

简单命令

RDBMS关系型数据库管理系统（Relational Database Management System）

RDBMS与MySQL的区别：SQL是结构化查询语言，是一种用来操作RDBMS的数据库语言。SQL是一个桥梁，是我们业务人员与数据库之间的桥梁，用于与数据库通信。

Nvicate充当了什么角色：（客户端与服务端）充当了客户端，通过客户端连接服务端。

新建连接：新建了一个与服务端的连接

utf8与utf8mb4：后者适用于社交，会增加一些emoji的表情
utf8_general_ci：通用排序规则
–链接数据库（终端）

mysql -u root -p mysql

– 退出数据库

exit

– 查看创建数据库

show databases ; # 必须要有s

– 查看当前正在使用的数据库

select database();  #null指的是空

– 使用某个数据库

use jingdong;

​ 注意：sql语句最后需要有分号;结尾

– 显示数据库版本

select version();

– 显示时间

select now

– 创建数据库

create database demo;

– 指定字符集（默认不是utf-8，如果没有指定字符集，最简单的就是把这个数据库删了，重新建）

create database demo charset = utf-8;

– 查看数据库的创建语句

show create database demo;

– 删除数据库

drop database demo;

数据表的操作

创建表

– 查看当前数据库中的所有表

show tables;

– 创建表

• **unsigned 没有符号, 没有负数 **
• auto_increment表示自动增长
• age不要用int，int是43亿，tinyint是-129–127
• 创建一个学生的数据表(id、name、age、high、gender、cls_id)
• 主键是能确定一条记录的唯一标识，比如，一条记录包括身份证号，姓名，年龄。身份证号是唯一能确定你这个人的，其他都可能有重复，所以，身份证号是主键。
• enum 表示枚举
• create table 数据表名字 (字段 类型 约束[, 字段 类型 约束]);
• 多个约束 不分先后顺序
• 最后一个字段不要添加逗号
• 默认从1开始, 枚举值(1,2,3)和原始值(‘男’,‘女’,)在使用上是等价的
• decimal(5,2) 五位数字，其中有两位是小数

create table students (字段名 字段类型 字段约束);

create table students (
    id int unsigned primary key auto_increment,
    name varchar(15) not null,
    age tinyint unsigned default 0,
    high decimal(5,2) default 0.0,
    gender enum('男','女','中性','保密') default '保密', 
    cls_id int unsigned not null
);

– 查看表的创建语句

show create table students;

– 查看表结构

desc students;

– 查看表内容

select * from classes;

– 修改表结构 alter add/modify（）/change（修改字段名字和类型）

– 修改表-添加字段

– alter table 表名 add 列名 类型/约束; 尽量少用alter

– 生日信息

alter table students add birthday datetime default "2011-11-11 11:11:11";

– 修改表-修改字段：不重命名版
– alter table 表名 modify 列名 类型及约束;

alter table students modify birthday date default "2011-11-11";

– 修改表-修改字段：重命名版
– alter table 表名 change 原列名 新列名 类型及约束;

alter table students change birthday birth date default "2011-11-11";

– 修改表，删除字段

drop table students

数据增删改查（curd）

1 增加insert 的三种方式

####1.1 全列插入，值和表的字段顺序一一对应

insert [into] 表名 values (值1,值2,...)

​ 我认为进行插入之前最好看一下目前表的内容和表结构

​ – 查看表内容

select * from students

​ – 查看表结构

desc students

全列插入在实际中用的不多 ，一旦表结构发生变化，全列插入就会报错。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-yDpdBkhY-1575857451244)(C:\Users\liulu\Desktop\临时截图\批注 2019-11-13 222449.png)]

# 小括号有且只能有六个值,id是自动递增的，可以不用管它，但是不管并不是说空着不写，可以使用占位符，只有主键字段才有占位符的概念 占位符可以使用0,default, NULL
insert into students values (0, '小乔', 18, 188.00, '女', 2);
insert into students values (0,'小乔', 18, 180.00, '女'); # 错误
insert into students values (default,'大乔', 19, 180.00, '女',2);

1.2 指定列插入，值和列一一对应

 insert into 表名 (列1,...) values(值1,...)

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Dc1RdTRh-1575857451246)(C:\Users\liulu\Desktop\临时截图\批注 2019-11-13 222449.png)]

insert into students (name, high, gender) values(“张飞”, 190, "保密")
# 报错了 Field 'cls_id' doesn't have a default value
#  cls_id不能为空,而且我们没有给他加上默认值
insert into students (name, high, gender, cls_id) values(“张飞”, 190, "保密", 1)

1.3 多行插入

• 多行插入，批量插入

• insert into 表名（列1，…）values(值1，…)，（值，…）

  insert into students values (0,'孙尚香', 18, 180.00, '女',2),(0,'甄姬', 20, 170.00, '女',3);
  insert into students (name, high, gender, cls_id) values ('张飞', 190.00, '保密', 1), ('关羽', 190.00, '男', 1);
  

2 修改 update

• where 表示修改的范围
• update 表名 set 列1=值1,列2=值2… [where 条件]
• 没有where 进行条件限制就是全表更新

update students set age = 30 where id = 4; -- sql中  通过一个等于号表示相等

3 删除 delete

• 物理删除
• DELETE FROM tbname [where 条件判断]

delete from students where id = 5;

4 查询

查询需要有数据的, 现在新创建一个数据库python_test_1,此数据库中有两个表students表,classes表

4.1 新建数据库

-- 创建数据库
create database python_test_1 charset=utf8;

-- 使用数据库
use python_test_1;

-- students 表
-- bit值保存为1/0，1代表true，0代表false
create table students(
	id int unsigned primary key auto_increment not null,
    name varchar(20) default '',
    age tinyint unsigned default 0,
    height decimal(5,2),
    gender enum('男', '女','中性','保密') default '保密',
    cls_id int unsigned default 0,
    is_delete bit default 0
);

-- classes表
create table classes(
	id int unsigned auto_increment primary key not null,
    name varchar(30) not null
);

-- 查看数据库中有哪些表
show tables;

4.2 添加数据

--向students表添加数据
insert into students values
(0,'小明',18,180.00,2,1,0),
(0,'小月月',18,180.00,2,2,1),
(0,'彭于晏',29,185.00,1,1,0),
(0,'刘德华',59,175.00,1,2,1),
(0,'黄蓉',38,160.00,2,1,0),
(0,'凤姐',28,150.00,4,2,1),
(0,'王祖贤',18,172.00,2,1,1),
(0,'周杰伦',36,NULL,1,1,0),
(0,'程坤',27,181.00,1,2,0),
(0,'刘亦菲',25,166.00,2,2,0),
(0,'金星',33,162.00,3,3,1),
(0,'静香',12,180.00,2,4,0),
(0,'郭靖',12,170.00,1,4,0),
(0,'周杰',34,176.00,2,5,0);

--向classes表添加数据
insert into classes values (0, 'python_01期'), (0, 'python_02期');

4.3 基础查询

• 查询所有字段-----select * from 表名;

select * from students;

• 查询指定字段-----select 列1,列2,…from 表名;

select name, height from students;

• 使用 as 给字段起别名，as 可以省略,不建议省略; select 字段 as 名字… from 表名;

select height as 身高, name as 姓名 from students;
-- as 可以不加,但建议加上
select name 名字, height 身高 from students;

• sql语句的完全形式

select name, height from students;
select students.name, students.height from students;
select python_test_1.students.name, python_test_1.students.height from students;

平常可以不用这么麻烦,但是多表查询的时候需要将表明带上,因为不同的表可能会有相同的字段

– select 表名.字段 … from 表名;

刚刚是as给字段取别名,也可以通过 as 给表起别名

• select 别名.字段 … from 表名 as 别名;
• 在当前的sql 语句中 临时的给students 起了一个别名叫做s
• s这个别名只在sql语句中是有效的

select students.name, students.height from students;
select s.name, s.height from students as s;  

• 消除重复行
• distinct 字段, 修饰所有需要查询的字段
• 查询班级学生的性别

select gender from students;

• 查询班级有多少种性别,对行进行去重

select distinct gender from students;

4.4 条件查询where

4.4.1 比较运算符

--查询大于18岁的信息
select * from students where age > 18;
--查询小于18岁的信息
select * from students where age < 18;
--查询不等于18岁的信息,<>或!=
select * from students where age != 18;
select * from students where age <> 18;

4.4.2 逻辑运算符 与(and)或(or)非(not)

-- and 必须同时满足所有的条件
select * from students where age > 18 and gender = '女';
select * from students where age > 18 and gender = '女' and height > 167;
-- or 只需要满足其中一个条件即可
select * from students where age > 18 or height > 180;
-- not 非
select * from students where age != 18;
select * from students where age <> 18;
select * from students where not age = 18;

4.5 模糊查询(like)

**% 表示可有可无 **

-- 查询姓名中 以 "小" 开始的名字
select * from students where name like '小%';
-- 名字中包含杰 的名字
select * from students where name like '%杰%';

_表示任意一个字符

-- 查询有2个字的名字
select * from students where name like "__";
-- 查询有3个字的名字
select * from students where name like "___";

sql支持使用正则表达式,有关正则表达式的知识需要额外学习,rlike, ^表示以什么开头

-- sql中可以使用正则表达式完成查询 rlike,^以什么开头
select * from students where name rlike "^周"; 

4.6 范围查询(in表示在一个非连续的范围)

-- 年龄不是 18、34岁的学生的信息
select * from students where age not in (18, 34);

-- 18 ~ 34
select * from students where age > 18 and age < 34;

-- between ... and ...表示在一个连续的范围内  两边都会包含
-- 查询 年龄在18到34之间的的信息
select * from students where age between 18 and 34;

-- not between ... and ...表示不在一个连续的范围内
-- 查询 年龄不在在18到34之间的的信息
select * from students where age not between 18 and 34;
select * from students where not age between 18 and 34;  # 取反


-- 空判断 null  不能够使用比较运算符
-- 查询身高为空的信息
select * from students where height = null; # 错误
select * from students where height is null; # 正确

-- 查询身高不为空的学生
select * from students where height is not null;
select * from students where height not is null; # 错误
select * from students where not height is null;

4.7 排序(order by ,默认asc为升序,降序使用desc)

-- asc升序
select * from students where age between 18 and 34 and gender = 1 order by age asc;

-- desc降序
-- 查询年龄在18到34岁之间的女性，身高从高到矮排序
select * from students where age between 18 and 34 and gender = 2 order by height desc;
select * from students order by height desc where age between 18 and 34 and gender = 2; # 错误

-- order by 多个字段 order by age asc, height desc
-- 查询年龄在18到34岁之间的女性，身高从高到矮排序, 如果身高相同的情况下按照年龄从小到大排序
select * from students where age between 18 and 34 and gender = 2 order by height desc, age asc;

-- 查询年龄在18到34岁之间的女性，身高从高到矮排序, 如果身高相同的情况下按照年龄从小到大排序, 如果年龄也相同那么按照id从大到小排序
select * from students where age between 18 and 34 and gender = 2 order by height desc, age asc, id desc;
-- 按照年龄从小到大、身高从高到矮的排序
select * from students order by age asc, height desc; -- asc 可以省略, 但是不建议省略

4.8 聚合函数,做统计

-- count(*) 以行单位来进行统计个数 按行统计
-- count(*) 效率更高, 效率略差:count(height)--> 获取对应的行--> 获取该行对应字段是否NULL
-- count( ) 某个字段只统计非空的,所以count(*)效率更高
-- 查询班级有多少人
select count(*) from students;
select count(id) from students;

-- 查询男性有多少人，女性有多少人 (通过一个sql语句完成这个需求 需要掌握分组操作才行)
select count(*) from students where gender = 1;
select count(*) from students where gender = 2;

-- 最大值: max()
-- 查询最大的年龄
select max(age) from students;

-- 查询女性的最高身高
select max(height) from students where gender = 2;

-- 查询最高身高的学生的名字
-- select name 14个结果
-- select max(height) 1
select name, max(height) from students; -- 不行,需要通过子查询

-- select 语句的嵌套  --> 子查询
-- 假设最高身高就是185
select name from students where height = 185;
select name from students where height = (select max(height) from students); -- ()提高sql语句执行的优先级
-- 最小值: min()

-- 平均值: avg()
-- 计算平均年龄
select avg(age) from students;

-- 计算所有人的平均年龄，保留2位小数
select avg(age) from students;
select round(avg(age),2) from students;

SQL 中的内置函数帮助文档的查看 ? functions; 查看有哪些类型的函数

ex: ? round;

? string functions;

? concat;

4.9 分组 group by, 分组的目的是为了做聚合统计(难点!分组聚合)

如果分组的目的不是为了做聚合统计,那么这个分组是没有意义的

-- 查询班级学生的性别
select gender from students;
-- 查看有哪几种性别
select distinct gender from students;

group by

-- 按照性别分组
select gender from students group by gender;  -- 只是拿到了分组的标签而已,并没有意义
-- 计算每种性别中的人数
select gender, count(*) from students group by gender;  -- 标签下的每组的人数
-- 它是先去进行group by的操作, 先去进行分组, count(*)聚合函数,它不会作用到原有的数据集上,他只会作用在分组上面去

group_concat -----按照分组后的字段统计其他字段, 查询分组之后的姓名

--比如如何按照性别统计姓名
select gender, name from students group by gender;  --这是错误的
select gender, group_concat(name) from students group by gender;  --正确

select gender, group_concat(name) as infos ,count(*) from students group by gender;

-- 计算男性的人数
select count(*) from students where gender = 1;
-- 通过分组来实现
select gender, count(*) from students group by gender;

如何在分组之后的数据做进一步的筛选工作

having条件筛选 ----------- 对分组之后的数据做进一步的筛选操作, 只能够是用having 做筛选 不能够使用where, where是对原数据做筛选的

-- 使用having 条件筛选 表示对于已经分组的数据做进一步的筛选
-- 对分组之后的数据做进一步的筛选操作, 只能够是用having 做筛选 不能够使用where
select gender, count(*) from students group by gender having gender = 1;
select gender, count(*) from students group by gender where gender = 1; 错误

-- 除了男生以外的分组的人数
select gender, count(*) from students group by gender having gender != 1;
select gender, count(*) from students group by gender having not gender = 1;

有having就一定有group by

有group by不一定有having—取决于你是否要对分组数据进行操作

– having 和 where 的区别
having 表示对于已经分组的数据做进一步的筛选, 有having就一定有group by, 有group by 不一定有 having
where 是对源数据做筛选操作

-- 查询每种性别中的平均年龄avg(age)
select gender, avg(age) from students group by gender;

-- 查询每种性别中的平均年龄avg(age), 最大年龄,平均身高,最高身高, 分组是为了更好的统计
select gender, avg(age),max(age), avg(height), max(height) from students group by gender;

-- 最好取一个别名

-- 查询平均年龄超过30岁的性别，以及姓名, 对分组之后的数据做进一步筛选操作 
select gender, name from students group by age having age > 30; --错误
select gender, group_concat(name), avg(age) from students group by gender having avg(age)>30;

此处插入一道练习题: 用一条sql语句查询每门课都大于80分的学生姓名

-- 插入表
create table info (id int unsigned primary key auto_increment,
                  name varchar(10) not null,
                  course varchar(5) not null,
                  score tinyint unsigned);
                  
-- 插入数据
insert into info (name, course, score) values
('张三', '语文', 81),
('张三', '数学', 75),
('李四', '语文', 78),
('李四', '数学', 81),
('王五', '语文', 81),
('王五', '数学', 100),
('王五', '英语', 90);

-- 查询
select name, group_concat(score) from info group by name having min(score)>80;

练习题: 在students表中查询各个性别下身高的前两位

这是一个非常高频的面试题, 现在学的知识有可能会做不出来:

首先肯定是要分组的

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-vVVfZZ6W-1575857451248)(C:\Users\liulu\Desktop\临时截图\批注 2019-12-07 195035.png)]

-- 第一步分组
select ... from students group by gender;
-- 把height拼接
select gender, group_concat(height) from students group by gender having
-- 对height字段排序
select gender, group_concat(height order by height desc) from students group by gender;
-- 查找一个字符串截取的方法
? string functions;
? substring_index;  
-- SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2); => 'www.mysql'
-- SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);=> 'mysql.com'

-- 现在进行截取
select gender,substring_index(group_concat(height order by height desc),',',2) from students group by gender ;

4.10 分页查询(网页经常一页一页的,比如淘宝京东) 关键字limit

– limit start, count
– start: 表示从哪里开始查询, start 默认值为0, 可以省略, start跳过多少条数据
– count: 查询多少条

问: stat是如何指定的呢? 它是ID?

答: 它与ID没有关系, 你可以认为它是跳过多少条数据 start = 0,表示跳过0条数据,向后查询count条

select * from students limit 0, 4;

练习: 需求 每页显示四个, 显示第三页的信息, 按照年龄从小到大排序

 select name from students order by age limit 8, 4;

4.11 完整的sql语句,命令的顺序

select distinct *
from 表名
where ...
group by ... having ...
order by ...
limit start,count

• 执行顺序:
  • from 表名
  • where…
  • group by…
  • select distinct *
  • having …
  • order by …
  • limit start, count
• 实际使用时, 只是语句中的某些组合,而不是全部

4.12 连接查询 (重点) 将两个表按照某种顺序连接到一起

​ 当你查找学生的名字以及对应班级的名字时,看起来简单,但是仔细看你的students表里并没有对应班级的名字,你的班级的名字在classes表里.

• 笛卡尔积查询

-- 查询学生的名字和学生对应的班级名字
-- 学生名字在学生表, 班级名字在班级表
select students.name,classes.name from students,classes;
select * from students,classes;  -- 这两条语句是一个笛卡尔乘积,是不符合的

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0JkRYbOd-1575857451250)(C:\Users\liulu\Desktop\临时截图\批注 2019-12-08 211133.png)]

只有表中两个ID相等的才是正确的答案: 因此可以加上条件where

select * from students,classes where students.cls_id = classes.id;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-nBQSGY4k-1575857451251)(C:\Users\liulu\Desktop\临时截图\批注 2019-12-08 211540.png)]

笛卡尔积查询的缺点: 有可能会产生很多无用的信息, 在实际中用的非常少.

• 连接查询

将两个表按照某种条件合并到一起,将两个表中的数据按照设置的连接条件进行筛选, 符合连接条件的数据才能够被筛选出来

– 内连接查询 inner join: 将满足连接条件的数据合成到一张表中

– table1 inner join table2 on 条件, 设置内连接条件 内连接查询

select students.name,classes.name from students inner join classes on students.cls_id = classes.id;
-- 可以将上面那条语句优化一下
select s.name,c.name from students as s inner join classes as c on s.cls_id = c.id;
-- 两个表按照ID连接起来
select s.*,c.* from students as s inner join classes as c on s.cls_id = c.id;

– 外连接查询: 分主表和次表,主表的数据全部显示

• 左外连接left join: 因为分主表和次表, 左外连接左表就是主表,左表数据全部显示(满足连接条件+不满足连接条件,不满足连接条件的数据以null填充)

select s.*,c.* from students as s left join classes as c on s.cls_id = c.id;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-YZn2mFa1-1575857451253)(C:\Users\liulu\Desktop\临时截图\批注 2019-12-08 211540.png)]

• 右外连接right join: 右边的表作为主表

  select s.*,c.* from students as s right join classes as c on s.cls_id = c.id;
  

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-OQgkVrn6-1575857451254)(C:\Users\liulu\Desktop\临时截图\批注 2019-12-08 211540.png)]

-- classes表添加一个班级,可以看看右连接时左边的表出现null
insert into classes values (6,'python8');
select s.*,c.* from students as s right join classes as c on s.cls_id = c.id;

一般右外连接用的不多,更多的使用左外连接,想要达到右外连接的效果可以把表翻一下就好

练习1:需求按照要求显示姓名、和学生对应的班级的名字(学生所在的班级)

select s.name,c.name from students as s inner join classes as c on s.cls_id = c.id;

练习2:在以上的查询中，将班级名字显示在第1列

select c.name,s.name from students as s inner join classes as c on s.cls_id = c.id;

练习3:查询 学生所在的班级, 按照班级进行排序

select c.name,s.name from students as s inner join classes as c on s.cls_id = c.id order by c.name;

• 扩充了解: 内外连接的其他写法

-- 内连接的其他写法
select s.*,c.* from students as s inner join classes as c on s.cls_id = c.id;
select s.*,c.* from students as s join classes as c on s.cls_id = c.id;
select s.*,c.* from students as s cross join classes as c on s.cls_id = c.id;

-- 外连接的其他写法
select s.*,c.* from students as s left outer join classes as c on s.cls_id = c.id;
select s.*,c.* from students as s right outer join classes as c on s.cls_id = c.id;

classes as c on s.cls_id = c.id;

**练习3:查询 学生所在的班级, 按照班级进行排序**

```sql
select c.name,s.name from students as s inner join classes as c on s.cls_id = c.id order by c.name;

• 扩充了解: 内外连接的其他写法

-- 内连接的其他写法
select s.*,c.* from students as s inner join classes as c on s.cls_id = c.id;
select s.*,c.* from students as s join classes as c on s.cls_id = c.id;
select s.*,c.* from students as s cross join classes as c on s.cls_id = c.id;

-- 外连接的其他写法
select s.*,c.* from students as s left outer join classes as c on s.cls_id = c.id;
select s.*,c.* from students as s right outer join classes as c on s.cls_id = c.id;