SQL数据库

Linux中安装MySQL设局库管理系统：
yum install mysql -y ===》 安装mysql客户端

yum install mysql-server -y ===》 安装mysql服务端

rmp -qa|grep ‘包名’：查看当前Linux系统中已经安装的工具包

mysql服务端：用来存放数据的仓库
mysql客户端：操作数据库的工具

启动MySQL服务（mysqld）的操作：
service mysqld start
service mysqld status
service mysqld stop
service mysqld restart

未设置MySQL数据库管理系统的root用户密码时，进入MySQL是不需要输入密码的
mysql -uroot -p

当页面行首为 mysql> 就表示已经进入了MySQL操作界面了，在里面的操作任何的Linux都是无效的在mysql> 页面中执行的是SQL语句，不管你输入了多少行内容，输入“；”才会执行

退出MySQL工具页面的方式：quit、exit、Ctrl+C

设置MySQL数据库的root用户的密码（通过Linux命令）：
mysqladmin -uroot password "123456"

后续要修改密码则要在MySQL数据库中进行修改

设置密码后的两种进入方式：
明文进入：mysql -uroot -p123456
密文进入：mysql -uroot -p （回车后输入）

基本操作：
show databases; ===》展示所有的数据库 ==》查看当前数据库操作系统中所有的数据库
use +库名; ==》进入对应的库中进行操作
show  tables; ==》展示 对应库中所有的表
create database +库名; ==》创建一个新的库
select database(); ==》查看当前正在操作哪个库
create table 表名(字段名1 字段属性,字段名2 字段属性,···); ==》创建表 命名为xx，里面包含着xx1字段属性是xxx，xx2字段属性是xxx，···
[img=620,0]C:\Users\Administrator\AppData\Local\YNote\data\qq3D0E7BFFDA5D9963AC34C0CAE9678F97\6dd88dd178cf4002ae2214ab590002ed\ed2{~wz%]v[]}(c[ookh}w6.png[/img]

常见的数据类型：
（1）数值型：
int：整型 整数型，保存整数数字的，只能保存4个字节，只能保存-2^31~2^31=2147483647，当存储的数据超过该值时，需要使用bigint
bigint：大整型，保存整型数字的，可以最多保存8个字节的数字
float：浮点数，可以保存小数，4个字节
double：浮点数，可以保存小数，8个字节

（2）字符串型：一个utf-8汉字字符占3个字节，一个GBK汉字字符占2个字节
char：定长字符串，固定占用磁盘空间的255个字节，数据的查询性能更好些，但是不节省资源
varchar：可变长字符串，占0~2^16个字节，节约资源，但是查询性能稍差

（3）日期
date：日期，固定格式YYYY-MM-DD

常见的约束：
primary key：主键约束（主键索引），特点：表中对应的主键字段里面的值是不能为空而且不能重复
auto_increment：自增长约束，只能在主键字段上使用，而且只能是数据类型为整型时使用
not null：非空约束，插入的数据永远最少会占用一个字节0的空间，数据 ‘ ’ 和数据 NULL区别 ：数据‘ ’表示一个字节为0的空字符，NULL完全不占用空间的
default：默认值约束，当对应的字段没有插入值时，会默认填入一个默认值

desc +存在的表名; ==》查看对应的表结构

修改表结构：
alter table（修改表）对表结构进行修改
alter table 表名 rename 新表名; ==》修改表名

alter table 表名 change 原字段名 新字段名 int(4); ==》改变表的字段的字段名和字段属性，并取消其他约束
alter table test add sex varchar(5); ==》添加字段，默认添加至最后
alter table test add class int(4) first; ==》添加字段，并放在第一位
alter table test add age int(4) after name; ==》添加字段，并放在某字段后
alter table test add(age int(4),class int(4)); ==》添加多个字段


alter table test drop sex; ==》 删除某字段
alter table test drop age,drop class; ==》删除多个字段

alter table test change age age int(4) after name; ==》改变字段属性
alter table test modify class bigint(8) after sid; ==》 单独修改字段属性

change与modify区别：
两者都是可以对表中字段的属性进行修改，但是使用change时要写两次字段名，modify是对单独的一个字段进行修改，只需要写一个
change可以重命名，modify不行

删除主键：一张表最多只有一个主键，直接使用drop primary key删除，会报错，主键字段包含自增长约束，自增长约束必须依赖主键约束，所以删除主键之前要先删除自增长约束
1、先删除自增长约束：alter table test modify sid int(4);
2、再删除主键：alter table test drop primary key;

添加主键
方式一：change modify修改已有字段为主键
方式二：（通过添加主键（add primary key()）到指定字段上）
alter table test add primary key(age); ==》添加主键属性到某字段上
alter table test add primary key(sid,name); ==》复合主键

----------------------------------------------------------------------------------
对表中数据进行操作----增删改查
增---insert into 往表中插入数据
方式一：插入完整的数据，每个字段都得有数据
1、插入的值的数量必须跟字段的数量一致
2、值的格式必须要一一对应
insert into 表名 values(字段1的值,字段2的值,···),(字段1的值,字段2的值,···),···;

方式二：对指定的字段中插入值
insert into 表名 (字段1,字段3,字段6,字段7,···) values (字段1的值,字段3的值,字段6的值,字段7的值,···);

字符串和日期类型的数据插入必须要加英文的引号，数值类型的数据加不加引号都可以

NULL和''==》NULL完全是空，完全不占系统的空间，"是占系统的一个字节0的空间
NULL和'NULL' ==》没引号NULL为空，有引号的'NULL'表示字符串'NULL'

查---select
select * from 表名; ==》查询对应表的所有字段的所有数据
select sid,name,age from emp; ===>查询多个字段的数据

条件查询，接where条件字句
等于：
select * from emp where name= '李四'; ==》查询所有字段的值当name为李四时

不等于： !=、<>
select * from emp where name != '李四';
select * from emp where name <> '李四';

*NULL是不参与等于或者不等于、大小匹配的
NULL值的匹配is，is not
select * from emp where class is null;
select * from emp where class is not null;

多条件查询：
and条件：要求两边同时满足
select name from emp where dept2 = 101 and incoming = 2000;

or条件：以or为界，两边只要满足一边
select name from emp where dept2 = 103 or incoming = 7500;

select * from emp where dept2=101 and
(incoming= 4000 or incoming= 3500);
select * from emp where dept2=101 and
incoming= 4000 or incoming= 3500;

大于小于的匹配
select * from emp where age >= 50 and age <= 60;

between xxx and xxx：闭区间
select * from emp where worktime_ start between '1980-01-01' and '1989-12-31';

in 与 not in （与or条件类似）
select * from emp where incoming in (4000,2000,7500);
select * from emp where incoming not in (2000,4000,7500);
select * from emp where dept2 not in (101,102);

Linux的模糊搜索符：？（单个字符） *（0个或多个字符）
MySQL中的模糊搜索符：’_‘（单个字符）'%'（0个或多个字符）
SQL语句中模糊搜索使用 like 字句
select * from emp where incoming like '%5%';
select * from test where name like 'xiao___';

time字段匹配2022年8月的数据
select * from test where time like ’2022-08%';

查询限定数量的数据 --- 分页查询limit，只能放在整条SQL的最后
计算机统计排序使用--索引，索引是从0开始的，例如索引8 表示 第9个
select * from emp limit 0,4; == select * from emp limit 4;

区别：Oracle数据库是没有limit做分页查询，首先要使用rowid的函数获取表格的伪行号然后通过between and的方式去查询

排序：order by
如果结合limit使用，limit放最后
升序使用 acs ，降序使用 desc
默认是升序
select * from emp order by incoming;
select * from emp order by incoming asc;
select * from emp order by incoming desc;
select * from emp order by incoming desc limit 3;

select * from emp where dept2 = 101 order by incoming desc limit 2;
同时出现条件过滤。排序以及分页查询时，要先做where条件过滤，再做order by排序，最后做limit限定查询

select * from emp order by dept2 desc,age asc;对多个字段进行同时排序，前者优先级更高

分组：group by
注意：
1、分组查询，最终结果是查询指定的字段。对什么字段进行了分组就查询什么字段。因为分组之后，只有分组字段可以正常匹配，其他非分组字段（普通字段）是随机出现（一般是表中分组字段对应的第一条）
2、如果希望在group by分组之后进行条件过滤，则使用having条件。where条件只能写在group by之前，之后只能用having进行条件过滤
3、group by 分组一般是结合聚合函数使用，where条件是不能接聚合函数的，having条件才可以接聚合函数

聚合函数：可以结合group by分组使用，也可以不结合
结合group by分组，对每个分组之后的组生效
不结合group by分组，对整张表生效
聚合函数字段是不能跟普通字段（非分组字段）进行使用的，普通字段也是随机匹配第一条的

count() 统计：
select count(*) from 表名; ==》统计表中有多少行
select count(1) from 表名; ==》同上

select count(class) from test; ==》 虽然整表数据有4条，但有一条数据的class数据为空，所以还是会显示3行

sum()：求和
select sum(incoming) from emp;
select dept2,sum(incoming) from emp group by dept2;

avg()：求平均
select avg(incoming) from emp;
select dept2,avg(incoming) from emp group by dept2;

max()：求最大值
select max(age) from emp;
select dept2,max(m'age) from emp group by dept2;

min()：求最小值
select min(age) from emp;

distinct()：去重复：
select distinct(dept2) from emp;

SQL中是可以进行四则运算的
select max(incoming)-min(incoming) from emp;
select max(incoming)-min(incoming)*10 from emp;
select (max(incoming)-min(incomingl)*10 from emp;

as：对查询出来的结果进行重命名
select dept2 as '部门编号',max(incoming) as '最高收入’ from emp group by dept2;


现在有个科目表，里面有两个字段:姓名、科目
求出表中选择科目大于2科的学生姓名
分组、聚合函数、分组之后的过滤
select 姓名 from 科目表 group by 姓名 having count(科目) >2;

改---update xxx set xxx
update 表名 set 字段名 = 新值 where 某字段 = 条件;
update test set class=2345 where sid=3;

update 表 set 字段=新值,字段=新值,字段=新值 where 限定条件
update test set class=1223, name = 'xiaohong',age=16 where sid=4;

删---delete
delete from 表名; ==》不限定条件删除表中所有数据
delete from 表名 where 限定条件; ===》按照条件删除指定的数据

面试题：
MySQL中删除数据有多少种方式？
1、delete：可以根据条件删除指定的数据，如果不接条件是，可以把表中所有的数据进行删除，有清空的作用，但是重新插入数据时，自增长字段还是会在原来最大值的基础上进行累计递增（只删除表中结构，保留表结构没有释放空间）
2、truncate：清空，truncate 表名; ==> 清空对应的表数据，不能接条件删除指定的数据，保留表结构，重新插入数据时，自增长会重新从1开始（清空表中数据并释放空间，保留表结构）
3、drop table +表名; ==>删除整张表，删除数据，表的定义也被删除（释放空间）


数据备份：
一：在数据库中对单个表进行备份操作；
1、先备份表结构：
create table 新表名 like 原表名;
2、再备份表中数据（把整张旧表的数据备份到新表中）
insert into 新表 select * from 旧表;

二、备份整个库（整个库中所有的表结构和表数据） ==》在Linux界面执行，通过Linux中MySQL客户端相关的指令进行：
1、备份数据库--mysqldump
mysqldump -uroot -p123456 需备份的库名 > 脚本文件.sql
2、还原备份的SQL脚本文件（必须要有对应的库）
mysql -uroot -p123456 存在的库名 < 脚本文件.sql

--------------------------------------------------------------------------------------------
MySQL数据库的权限操作：
必须进入mysql库 use mysql;

查看当前MySQL数据库管理系统有什么用户：
select host,user,password from user;


方式一：通过在user表中插入数据的方式创建（一般不会使用）
insert into user(host,user,password) values ('localhost', 'dcs45',password('123457'));
密码的插入是需要使用password()函数进行加密的
对数据库中任何的权限进行操作，都要进行权限刷新操作:
flush privileges;

增加权限：
grant insert,delete,update,select,create on *.* to 'dcs45'@'localhost' identified by '123457';
同样也要进行权限刷新:
flush privileges;

查看对应用户的权限情况:
show grants for 'dcs45‘@'localhost';

方式二:直接通过grant赋予权限的方式进行用户创建
grant all privileges on *.* to 'root'@'%' identified by '123456';
权限刷新:
flush privileges;

revoke all on *.* from '用户'@'网络位置’ ==》取消对应用户的权限
更改密码:
update mysql.user set password = password('123456') where user = 'dcs45' and host= 'localhost';

删除用户：
delete from mysql.user where host= 网络位置 and user = 用户名

===================================================================
navicat ===》一个连接数据库的图形化工具，收费的，业内大部分公司也是用破解版
DBeaver --- 开源的数据库连接工具，免费的，跟Navicat的操作是很类似，是java语言开发, 所以你的本地电脑有安装jdk

通过Navicat连接mysq|数据库服务器的步骤:
1、通过Navicat新建MySQL连接 (只要做一遍)
（1）填写对应数据库服务器的IP地址
（2）填写在MySQL中创建的具有%网络权限的mysql用户及密码
2、关闭Linux的防火墙（service iptables stop）---每次Linux虚拟机开关机之后都要执行
3、要确保服务器中的mysqld服务已经开启


1、内连接innerjoin：对两个表当中有字段的值相同就可以通过内连接进行连接，显示连接值相同的行结果
select * from aa inner join cc on aa. id=cc.sid;
select * from aa join cc on aa. id=cc. sid;
查询所有字段的值来源aa表内连cc表在aa表id和cc表sid的值相同时

2、左连接left join：以左边表为主，显示完整的左边表，右边的信息显示满足条件的，对于不符合条件的数据显示null   =====尽量小表驱动大表
select * from aa left join CC on aa. id=cc.sid;
查询所有字段的值来源aa表左连cc表在aa表id和cc表sid的值相同时

3、右连接right join：以右边表为主，显示完整的右边表，左边的信息显示满足条件的，对于不符合条件的数据显示null
select * from aa right join cc on aa. id=cc.sid;
查询所有字段的值来源aa表右连cc表在aa表id和cc表sid的值相同时
select * from cc right join aa on aa. id=cc.sid;
查询所有字段的值来源cc表右连aa表在aa表id和Cc表sid的值相同时

内连接和外连接：
内连接就是把满足条件以内的信息进行显示
外连接分为左外连接和右外连接
左外连接：把左边表整个信息进行显示，右边只显示满足条件的数据
右外连接：把右边表整个信息进行显示，左边只显示满足条件的数据
左连接的查询速度回比右连接快

4、基本连接：对于多张表当中有字段的值相同就可以通过基本连接进行连接，显示的结果也是连接条件相同的行（结果与内连接没区别，区别是在语法上）
select * from aa,cc where aa.id = cc.sid;
查询所有字段的值来源aa表和cc表当aa表的id=cc表的sid

内连接：
select * from aa inner join cc on aa. id=cc.sid and cc.score > 90;
基本连接：
select * from aa,cc where aa.id = cc.sid and cc.score > 90;

三表拼接
select * from aa inner join cc join grade on aa.id = cc.sid and aa.id = grade.sid;

5、硬连接（机械拼接、上下拼接）：拼接时，表的字段数目要一致
select * from aa union select * from cc union select * from grade；

多表查询的使用方法：
求张三的id
条件：姓名为张三  结果：id
1、连了再说--吧表拼接在一起后，当做一个单表进行查询
select * from aa inner join cc on aa.id=cc.sid where aa.name= 'zhangsan'
select * from aa left join cc on aa.id=cc.sid where aa.name= 'zhangsan'
select cc.score from cc right join aa on aa.id=cc.sid where aa.name= 'zhangsan'
select cc.score from aa,cc where aa.id=cc.sid and aa.name= 'zhangsan'

2、子句嵌套
（1）先通过一条SQL语句求出一个中间结果
先通过查询aa表获取张三的id
select id from aa where name = ’zhangsan‘ -- 本质上就是一个值

（2）把前面求出中间结果的SQL子句给到后面SQL作条件去使用
select score from cc where sid=(select id from aa where name= 'zhangsan' )

3、子表嵌套
求出grade表中每个班英语成绩最高的那个人的姓名和班级名称--每个班英语成绩最高
（1）先求grade表中每个班英语成绩最高的情况作为临时表t1
(select class ,max(english) from grade group by class) as t1

（2）把 grade 表 和临时表t1进行拼接，条件为两个表的班级相等(grade.class=t1.class)，Hgrade表的英 语成绩等于班级英语成绩最高(因为where条件后不能出现任何聚合函数，所以需要对临时表中的max(english)取别名ma)
select grade. class , grade .name from grade, (select class, max(english) as ma from grade group by class) as t1 where grade. class=
t1.class and grade . english=t1.ma;

索引 -- index
是一种数据结构，主要是添加在一些频繁被查询的数据字段（热字段）上，有点类似书本的目录，是可以有效的提高数据的查询速度
注意：
1、创建索引是在大量数据的前提下，添加索引可以提高查询速度
2、不是每个字段都要加索引的，如果每个字段都添加了索引就没有实际的意义，就算添加了也会导致消耗大量的系统资源
3、添加索引可以提高查询速度，但是添加索引之后都影响数据插入或者数据更新的速度
4、索引分为3种
（1）主键索引primary key PRI：一个表中只能存在一个主键索引，要求对应字段的值唯一且不能为空
（2）普通索引index MUL：表中是可以存在多个普通索引，对索引字段的值是没有要求的
（3）唯一索引unique index UNI：表中是可以存在多个唯一索引， 对索引字段的值要求不允许重复，允许为空
5、索引的优先级：PRI>UNI>MUL

普通索引 -- index
1、使用create index语句创建索引（只能创建普通索引）
给单个字段创建普通索引
create index aa on emp(name);
show index from emp;
select name from emp where name = 'zhansan'

给多个字段创建普通索引 -- 复合索引
create index bb on emp(age,incoming);
select age,incoming from emp;

2、通过alter table的方式创建索引
alter table emp add index bb(age);

删除索引：
alter table emp drop index bb;

唯一索引 -- unique index
1、通过create unique index创建
create unique index cc on emp(incoming);
不能创建在有重复的值的字段上

2、通过alter table创建
alter table emp add unique index ee(worktime_start);

删除唯一索引：
alter table emp drop index ee;
alter table emp drop index aa,drop index bb,drop index cc; -- 删除多个索引

create unique index ee on emp(name,dept2);
创建复合唯一索引ee，作用在emp表.上的name和dept2字段上，要求name和dept2字段的值组合后是唯一-，但允许为空

主键索引
在创建主键时，主键索引就同时创建成功
主键索引和主键索引其实是绑在一起的，如果有了主键索引，
那么必然会产生主键约束，或者说主键约束是主键索引的一个特征，

视图 -- view
视图是一个虚拟的表，由行和列组成的数据，但是它并不是用来存储数据，只是用来显示数据
视图是建立在实际的表之上，视图的数据是动态生成的（根据实际的表数据做实时计算得到的）

视图的作用：
1、为了安全，在公司中有些字段是保密字段，那么可以创建视图，限制对某些字段的查询
2、简化查询，因为有些查询可能很复杂(多表查询、分组查询)，而且这种查询经常被使用，为了避免重复编写SQL时的错误，通过把这些复杂的SQL创建为视图的方法，避免重复编写SQL

视图的特点:
1、视图是由实际的表产生的
2、视图的查询本质上是对实际表进行查询操作，所以使用视图性能相对差
3、视图的创建和删除是不会影响实际的基础表的，但是数据是会互相影响的
4、当一个视图的数据来源是复杂的SQL时，那么视图的数据是不允许修改的

创建视图（可以理解为对基础表中的数据进行复制）
create view dcs as(select class,name,age from grade);
show tables; 视图本质上也是一张表
select * from dcs;
show create view dcs; 查看视图表dcs的结构

基础表和视图的数据修改是可以互相影响的（只能是简单的SQL创建的视图表）
update grade set name = 'zhangsan' where name = 'zhansan'; 修改实际表中的数据
update dcs set age = 99 where name = 'lisi' 修改视图表中的数据

drop view dcs; 删除视图

show table status where comment = 'view'; 通过SQL的方式查看当前库中有哪些视图

create view deptemp as (select * from dept left join emp on dept.dept1=emp.dept2)
select * from deptemp

DDL（开发或者DBA平时使用） ==> 数据库定义语言 ==> 对表的定义、表字段进行操作 ==> create（创建）、alter table （修改表）、drop（删除）
DML（测试平时使用） ==> 数据库操作语言 ==> 对表中数据进行操作 ==> 增insert into、删delete、改update ··· set、查select

--------------------------------------------------------
表内约束有哪些
主键约束
非空约束
默认值约束
自增长约束

外键约束 foreign key 外码
约束的是表和表之间的内容
作用：为了避免一张表的数据太过冗余。就要对表进行拆分解耦，而且为了避免解耦之后数据不完整，所以要引入外键，约束数据的一致性和完整性，保持被约束的表数据的一致性，完整性

创建两张表dcs1和dcs2
把dcs1设计为主表，dcs2设计为从表
1、先创建dcs1表
create table dcs1(id int(4) primary key,name varchar(20)) engine = innodb;
因为MySQL普通的建表，数据引擎默认为myisam是不支持外键，支持外键约束的是innodb
2、创建dcs2表 -- 从表，加上外键，引用dcs1表中id字段
create table dcs2(sid int(4) primary key,sname varchar(20),constraint gz foreign key(sid) references dcs1(id)) engine=innodb;

constraint gz -- 创建约束名称为gz
foreign key(sid) -- 外键定义在sid字段上
references dcs1(id) --引用dcs1表中的id字段
当完成上述的建表语句之后，dcs2表中的sid字段的值就被dcs1表中的id字段约束着（当dcs1表中id字段没有x值时，那么dcs2的sid就不能存在x这个值）

3、往dcs1和dcs2分别插入数据
insert into dcs1 values(1,'xiaomai'),(2,'laomai');

insert into dcs2 values(1,'xxxxx'); -- 往dcs2表（主表）插入一条主表存在的数据

4、往dcs2表插入--条主表id字段不存在的数据
insert into dcs2 values(3,' yyyy '); 报错，3不存在主表的id字段中

5、把dcs1主表中已经被从表引用的数据进行更新或者删除
update dcs1 set id = 4 where name = ’xiaomai‘ 报错 因为id = 1的值被从表的sid引用着
delete from dcs1 where id=1; 报错 因为id = 1的值被从表的sid引用着

delete from dcs2 where sid = 1; 先把子表中的sid=1的值进行删除，后续就可以删除或者修改主表中对应的值

6、删除外检，只要对子表进行操作即可，因为外键是定义在子表上的
alter table dcs2 drop foreign key gz;

结论：当主表中被引用的字段没有x值时，那么从表的外键约束就不能存在x这个值
主表不存在的数据，在与之相关的从表是无法插入对应的数据
1、对从表插入数据时，如果主表没有对应的数据，则无法插入
2、要删除或者修改主表的数据，要先确认主表的数据有没有被引用，如果被引用要先删除从表中对应的数据