数据库---SQL

目录

基本概念

数据库（Database）

数据库管理系统（DBMS）

数据库基本数据类型

 数据库操作

数据库操作

 1、查询数据库

 2、创建数据库​

​ 3、使用数据库

 4、删除数据库

表 

1、创建表

 2、察看表结构

3、删除表

4 、插入记录

5 、查找

 6、指定字段起别名

 7、去重

 8、排序（对临时表的排序）

9、条件查询

 10、分页查询

 11、添加字段

12、修改字段数据类型

 13、删除字段

约束 

实体之间的关系

一对一

一对多

多对多

聚合查询

分组查询 

联合查询

内连接

外连接

自连接

子查询

标量子查询：子查询的结果为单值

列子查询：子查询的结果为单列

行子查询：子查询的结果为单行

表子查询：子查询的结果为多行多列

合并查询 ---union，union all

 mysql的内置函数

字符串函数

​ 数值函数

日期函数

流程控制函数

---

数据库是存储各种数据的软件：一个购物网站，商品的各种信息，对应评论……这些各种信息就是数据，数据库用于存储这些大量的数据。 

基本概念

数据库（Database）

简称DB，是长期存储在计算机内，有组织的，可共享的大量数据的集合。

基本特征

 数据库的信息存储：

 数据库的数据存储在硬盘上存储，也就是存放在外存，数据断电后不会消失

数据库管理系统（DBMS）

操作和管理数据库的软件

• 关系型数据库：建立在关系模型的基础上，由多张相互联系的二维表所构成

什么是二维表：

通过表格来记录信息，一个表存储学生信息，其中存储了学号，又可以通过学号来查找对应学生的成绩，关系型数据库就是由多张相互联系的二维表所构成。

 常用的关系型数据库软件：

 

• 非关系型数据库（NoSQL）：例如键值Key-Value存储数据库，列存储数据库，文档型数据库，图形Graph数据库 ……。一般不借用SQL语言实现
• SQL-操作关系型数据库的语言

数据库基本数据类型

 例如：89.8，就是decimal（3，1），3表示有效数字的个数，1表示小数位数

char (1）表示字符串输入1个字符，长度不可变

varchar(10) ，表示字符串最多输入10个字符

varchar和char的区别：varchar通过计算输入长度来判断是否超过指定长度，长度可变

 数据库操作

数据库操作

 1、查询数据库

 2、创建数据库

字符集：使用数字来表示汉字，从而形成的对应关系表格，例如utf-8

数据库也可使用utf8mb4，比utf-8表示的内容多一些

 3、使用数据库

 4、删除数据库

表 

1、创建表

create table 表名(列表1，列表2……）

 2、察看表结构

desc 表名

3、删除表

drop table [表名]

4 、插入记录

insert （into) 表名 values(……)

into 可省略 

插入日期：两种方式，字符串插入和借助now（）函数 

指定某列插入

 多行插入

一个表的查找内容插入另外一个表

insert 目标表 select （来源表的列） from 来源表

5 、查找

全列查找

select * from 表名

数据库存放在硬盘中，也就是在外存，但是 select得到的表格是在内存中，所以select得到的是临时表，打印后就消失了，不会影响硬盘中的数据。

 指定列查找

select  列名 from 表名

 指定列为表达式（列和列的计算）

 6、指定字段起别名

select 列名 as 新的别名 from 表名

 7、去重

select distinct 列名 from 表名

 8、排序（对临时表的排序）

select 列名 from 表名 order by 列名 asc/desc

asc表示升序，desc表示降序

 多个列排列

9、条件查询

=表示相等，但是null=null的结果是null，结果假

<=>表示相等，null<=>null的结果是null，结果真

select  列名 from 表名 where 条件

< <= > >=	小于，小于等于，大于，大于等于
in （a，b……）	是a，b……中的人任意一个就是真
like	模糊匹配
A and  B	A B同时为真
A or B	A B之间有一个为真

 and的优先级高于or

• 小于 

•  and

• in 

• like 

   李%： 表示name必须是“李……”的格式，其中……可以是空，类似于李a，李an这种形式

   %李%：表示name必须是“……李……”的格式，其中……可以是空（必须包含一个“李”字）

_ 李%  ：第二个字是李,不要求字数

_ 李  ：第二个字是李，且只会有两个字

_ _李%  ：第三个字是李，不要求字数

_ _李  ：第三个字是李，且只会有三个字

李_ ：只会有两个字，第一个字是李

李_% ：第一个字是李，不要求字数

• null空值比较

 10、分页查询

数据过多，一页显示不全就会使用分页查询

 11、添加字段

12、修改字段数据类型

 13、删除字段

 14、修改内容

 例如：将总成绩前三名的数学成绩+10分

 修改所有人的数学成绩*2

15、删除数据

delete from 表名 where……

用户管理

•  查询用户

•  创建用户

create user 'zhangsan'@'localhost' identified by '123';
-- 创建用户，zhangsan，密码是123，它只能在当前主机上连接数据库
select  * from user;

create user 'lisi'@'%' identified by '123';
-- 创建用户lisi，密码123，他可以在任何主机上连接数据库

• 修改用户密码

alter user 'lisi'@'%' identified with mysql_native_password by '1234';
-- 修改list用户的密码成为1234

• 删除用户

drop user 'lisi'@'%' ;

 权限控制

创建好的用户没有权限，需要给定权限

•  查询权限

show grants for 'zhangsan'@'localhost';

•  授予权限

grant all privileges on grade.grade to 'zhangsan'@'localhost';
-- zhangsan这个用户获得了 对grade数据库中的grade表 进行操作的所以权限
show grants for 'zhangsan'@'localhost';

• 撤回权限

revoke all privileges on grade.grade from 'zhangsan'@'localhost';
-- 撤回zhangsan这个用户 对grade这个数据库的grade表 的所有权限

约束 

对数据库数据的限制性要求

• not null ：数据不为空

• unique：数据唯一

id 被unique约束，那么赋值时已经出现过的id就不能继续插入

• default:设置默认值

• primary key：主键约束（完整性约束），not null和unique的结合

任何一张表有且只有一个主键

 主键的用法：字段唯一且不为空，可以实现序号增加（1，2，3……），称之为自增主键

mysql中设置了自增主键

• foreign key：外键约束

alter table class add constraint fk foreign key (id) references student(id);-- 增设外键
alter table class drop foreign key fk;-- 删除外键

 成绩表依赖于信息表，两张表的学号相关联，借助学号将两个表进行关联：

父表中关联的字段设置为主键约束，子表中关联的字段设置外键约束

 子表插入信息： 

成绩表依赖于信息表，子表中依赖于父表的信息必须在父表存在

例如：成绩表中插入学号99999，父表中不存在这个学号，就会出错

父表删除信息：

 成绩表依赖于信息表，子表中依赖于父表的信息必须在父表存在，所以父表不能删除/修改已经被子表使用的信息

 如何正确对外键约束进行删除或者更新

 

•  默认使用no action 或者restrict方式

• cascade方式

在创建表时增设条件

create table class(id int,class_id int,foreign key (id) references student(id) on update cascade );

create table class(id int,class_id int,foreign key (id) references student(id) on update cascade );
-- 在父表更新数据时，可以更改（联通子表一起更改）
update  student set   id=20200710  where id=20200709;
select * from student;
select * from class;

实体之间的关系

一对一：

一个人只有一个成绩，一个成绩对应了一个人

 创建方式：

一对多

一个学生只能有一个班级，一个班级有多个学生

创建方式 

多对多

一个学生可以选多门课，一个课可以有多个学生

 创建方式：

聚合查询

将表的行相关联

常见的聚合函数：

1、 count函数

null不计算

 

2、max---找到行的最大值

null不计算

 3、min---某一行的最小值

null不计算

4、sum---行求和

null不计算

注意： 字符串类型 不能求和

  5、avg---平均值 

求和中null不计算，行数null也不计算

分组查询 

分组之前的结果进行筛选，需要使用where； 分组之后的结果进行筛选，需要使用having 

分组查询一般和聚合函数相关联

 1、 按照性别分组，查看男，女各有多少人

 分析：按照性别分组，会划分为两组：

对于分组查询，查找除了聚合函数以外的字段没有任何实际意义，例如打印姓名，只会分别打印两个组的第一个人的信息，没有任何实际意义

 2、-- 按照性别分组，查看平均年龄

 3、-- 年龄小于50的人中，按照性别分组，查看在西安的人数

  4、年龄小于50的人中，按照地址分组，获取所在地址处员工人数超过1的地址

联合查询

将两个表结合起来---使用笛卡尔积

 创建结果：新表的行数=两表行数乘积，新表的列数=两表列数之和

select （列） from 第一个表名，第二个表名 where 条件

或 select （列） from 第一个表名 join 第二个表名 on 条件

 但是这些内容有的是并不匹配的

增设where条件筛选正确信息 

 创建表：

 1、查询java101所有学生选的课程

java101：在班级表里，学生课程在学生选课表里，两个表可以通过学生表连接

-- 1、查询java101所有学生选的课程 java101：在班级表里，学生课程在学生选课表里，两个表可以通过学生表连接
  -- 第一步： 查询学生表里，java101的学生
select student.name, class.class_name
from student,class where class.class_name="java101" and student.class_id=class.class_id;

-- 第二步：在第一步的基础上， 查询学生选课表中，java101的学生选的课程序列号
select student.name, stu_cou.couse_id from student,class ,stu_cou
where class.class_name="java101" and student.class_id=class.class_id and student.stu_id=stu_cou.stu_id;

-- 第三步，在第二步的基础上，找到课程名称
select    student.name, stu_cou.couse_id, couses.couse_name from student,class ,stu_cou,couses
where class.class_name="java101" 
  and student.class_id=class.class_id
  and student.stu_id=stu_cou.stu_id 
  and couses.couse_id=stu_cou.couse_id  ;

或者：使用join on 

select    student.name, stu_cou.couse_id, couses.couse_name from student join class join stu_cou join couses
 on  class.class_name="java101"
  and student.class_id=class.class_id
  and student.stu_id=stu_cou.stu_id
  and couses.couse_id=stu_cou.couse_id  ;

2、统计男生选了多少门课，女生选了多少门课

-- 2 男生选了多少们门课，女生选了多少门课
-- 性别在学生表，选课在学生选课表
-- 第一步 两个表多表查询
select * from student,stu_cou  where  stu_cou.stu_id=student.stu_id  ;
-- 第二步，根据性别分组
select sex,count(sex) from student,stu_cou  where  stu_cou.stu_id=student.stu_id group by sex;

3、统计每一个人选了多少门课

select student.name ,count(student.stu_id ) as counts from student,stu_cou
  where  stu_cou.stu_id=student.stu_id group by student.stu_id ;

内连接

内连接：取两个表的公共部分

select * from a,b where a.id=b.id;

 相当于取两个集合的交集：取绿色部分

 还有一种内连接的写法：使用join on

select * from a join b on a.id=b.id;

外连接

分为左外连接和右外连接

 左外连接：保留全部左边的信息，对于左边独有的信息，右边自动补null

select * from a  left join  b on a.id=b.id;

  右外连接：保留全部右边的信息

select * from a  right join  b on a.id=b.id;

自连接

自连接可以是内连接也可以是外连接

一张表和自己连接

内连接：

select * from a as a1 ,a as a2  where a1.id=a2.id;

左外连接：

select * from a as a1  left join  a as a2 on a1.id=a2.id;

子查询

标量子查询：子查询的结果为单值

-- 查找财务部人员的姓名
  -- 第一步:在b表中找到财务部的id号
  -- 第二步:在a表中找到财务部id所对应的信息
select id from dep where posi="财务部";  -- 结果2
select * from emp where id =2;
 -- 使用子查询:
 select * from emp where id =(select id from dep where posi="财务部");-- ()内部的查询语句是一个数字，称之为标量子查询

列子查询：子查询的结果为单列

-- 查询人事部和财务部的人员信息
  -- 查询人事部和财务部的id
select  id from dep where posi="人事部" or posi="财务部";
-- 根据id 查找信息
select * from emp where id=2 or id =110 or id =3;

select  * from emp where id in(select  id from dep where posi="人事部" or posi="财务部");

行子查询：子查询的结果为单行

-- 查询和 王五 部门和性别相同的人员信息
  -- 查询 王五 部门和性别
select  id,gender from emp where name="王五";

select *from emp where id=1 and gender="男" ;
-- 也可以写为
select *from emp where (id,gender)=(1,"男" );

select *from emp where(id ,gender)= (select  id,gender from emp where name="王五");

表子查询：子查询的结果为多行多列

-- 查询和 鲁智深 部门和 薪资相同的人员信息
  -- 查询  鲁智深 部门和 薪资
select  id,salary from emp where name="鲁智深";

select *from emp where id=1 and salary=88 or salary=108;


select *from emp where(id ,salary) in (select  id,salary from emp where name="鲁智深");

合并查询 ---union，union all

-- 查找姓张的人
select * from emp where name like "张%";
-- 男性
select * from emp where gender="男";

-- 将这两张表拼接起来
select * from emp where name like "张%"
union all
select * from emp where gender="男";

 union ：拼接出来的表，不会重复某一行

 union  all：直接拼接，不去重

-- 将这两张表拼接起来
select * from emp where name like "张%"
union 
select * from emp where gender="男";

 mysql的内置函数

select 函数

字符串函数

select concat("hello","world");

select upper("Hello");

select lpad("hello",9,"_");-- 使用_左填充字符串hello,填充完之后一个是9个长度

 select trim(" hell o ");-- 去除前后空格

 select substring("hello",1,3);

 数值函数

 

生成六位随机验证码：

select rand();-- 生成0~1的随即小数 0.6381639108417219，
select rand() *1000000;-- 例如：638163.9108417219，要取整数
-- 小数向上取整 例如：312190
select ceil(rand()*1000000 );
-- 或者保留0位小数
select round(rand()*1000000 ,0);

-- 但是有一个问题：例如生成的小数是0.011113
select 0.011113*1000000;-- 11113是五位小数 ->解决办法：补0
select lpad(11113,6,0);-- 前面补0
select rpad(11113,6,0);-- 后面补0

-- 最终形式
select rpad(round(rand()*1000000,0),6,0);

日期函数

select curdate();-- 返回当前日期 年-月-日
select curtime();-- 返回当前时间  时：分：秒
select now();-- 返回当前日期和时间 年-月-日 时：分：秒
select year("2022-09-07");-- 返回年份
select month("2022-09-07");-- 返回月份
select day("2022-09-07");-- 返回日期

select date_add("2022-09-07",INTERVAL 30 day );-- 从指定日期加30天 -》2022-10-07
select date_add("2022-09-07 00:00:00",INTERVAL 24 hour );-- 从指定时间加24小时 -》2022-09-08 00:00:00
select date_add("2022-09-07 00:00:00",INTERVAL 60 second );-- 从指定时间加60秒 -》2022-09-07 00:01:00

select date_add("2022-09-07 00:00:00",INTERVAL -60 second );-- 从指定时间减去60秒 -》2022-09-06 23:59:00
select date_add("2022-09-07 00:00:00",INTERVAL -1 month );-- 从指定时间减去一个月 -》2022-08-07 00:00:00

select datediff("2022-09-07  00:00:00","2022-08-07  00:00:00");-- 返回两个日期的相差天数 31
select datediff("2022-08-07  00:00:00","2022-09-07  00:00:00");-- 返回两个日期的相差天数 -31

流程控制函数

select if(1>0,1,0);-- 1
select ifnull(null,0);-- 0
select ifnull("ok",0);-- ok

select *,
      ( case when (id=1 and  salary>100) then "高薪资"
                    when (id =2 and salary>90) then "高薪资"
                    when (id =3 and salary>80) then "高薪资"
else "低薪资" end)
from emp;

 对于成绩表操作

-- 将 数学成绩是100的人 的 数学成绩标记为均值
select name,
       (case   when math =100 then "均值" end) as "math"
from grade;

select name,
( case  when  English>125 then "优秀"  when  English>110 then "良好" when English>95 then "及格"else "不及格" end)as "English",
( case  when  math>125 then "优秀"  when  math>110 then "良好" when math>95 then "及格"else "不及格" end)as "math" ,
 (case  when  chinese>125 then "优秀"  when  chinese>110 then "良好" when chinese>95 then "及格"else "不及格" end)as "chinese"
from grade;