MySQL的笔记
ySQL数据库服务端：用来存放数据的仓库MySQL数据库客户端：操作数据库的工具
MySQL服务(mysqld)的操作：
service mysqld start
service mysqld status
service mysqld stop
service mysqld restart
未设置MySQL数据库管理系统的root用户密码时，进入MySQL是不需要输入密码的
mysql -uroot-p
在 mysql> 页面中执行的是SQL语句，不管你输入了多少行内容，当碰见了‘;’时SQL语句就才结束
退出MySQL工具的方式：quit、exit、Ctrl+C
首次设置MySQL数据库的root用户的密码（通过Linux命令，只能执行一遍）：
mysqladmin -uroot password "123456"
后续要修改密码则要在MySQL数据库中进行修改
基本操作：
show databases; ===>查看当前数据库操作系统中所有的数据库
当页面行首为mysql>就表示已经进入了MySQL操作界面了,每一条SQL语句都是以英文的‘;’结尾的
DDL (平常由开发或者DBA去操作的) ===> 数据库定义语言
create(创建)、alter table(修改表)、drop(删除)、show(查看)
DML(测试平常操作数据库) ===> 数据库操作语言
insert into(增)、delete(删)、select(查)、update ... set...(改)
删除表的数据用的是delete
删除表或者表里某个字段用drop
基本操作：
show databases; ===>展示 所有的数据库
show tables; ===》展示 对应库中所有的表
use +库名; ==》进入对应的库中进行操作
select database()； ==》查看当前正在操作哪个库
select version();==>查看MySQL版本
select now(); ==>查看当前服务器时间
select user; ==> 查看当前操作的用户是谁
create database +新库名； ==》创建一个新的库
create table 表名(字段名1 字段属性,字段名2 字段属性,字段名3 字段属性,字段名4 字段属性,......); ==>创建表
desc +表名; ==>查看表结构
-------------------------------------------------------------------
常见的数据类型：
（1）数值型：
int：整型整数型，保存整数数字的，只能保存4个字节的数字，只能保存-2^31~2^31=±2147483647，当存储的数据超过该值时需要使用bigint
bigint：大整型，保存整数数字的，可以最多保存8个字节的数字
float：浮点数，可以保存小数，4个字节
double：浮点数，可以保存小数，8个字节
1byte=8bit=1个字节
（2）字符串型：一个utf-8汉字字符占3个字节，一个GBK汉字字符占2个字节
char：定长字符串，固定占用磁盘空间的255个字节，数据的查询性能更好些，但是不节省资源
varchar：可变长字符串，占0~2^16次方个字节，节省资源，但是查询性能稍差
（3）日期
date：保存日期，固定格式YYYY-MM-DD
------------------------------------------------------------
常见的约束：
primary key：主键约束（主键索引），特点：表中对应的主键字段里面的值是不能为空而且不能重复，一个表中只能又一个主键索引
auto_increment：自增长约束，只能在主键字段上使用，而且只能是数据类型为整型的去使用。例如不输入id时，就会在原来的最大值进行+1再新增
not null：非空约束，插入的数据永远最少会占用一个字节0的空间
（数据‘ ’和数据NULL区别：数据‘ ’表示占用一个字节为0的空字符，NULL完全不占用空间的）
default：默认值约束，当对应的字段没有插入值时，会默认填入一个默认值
-------------------------------------------------------------------
desc 表名; ==>查看对应的表结构
修改表结构：alter table （修改表，对表的结构进行修改）
1. alter table .... rename...===> 修改表名
2. alter table .... change...===> 修改表里字段属性，能改字段名，也能添加主键索引
3. alter talbe .... modify ... ===> 修改表里字段属性，不能改字段名，也能添加主键索引
4. alter table .... drop .... ====> 删除表里字段，也能删除主键索引（primary key）
5. alter table .... add ....====> 在表里添加字段，也能添加主键索引
alter table test rename test1; ==>修改表 test表 重命名 为test1；
alter table test1 rename test; ==>修改表 test1表 重命名 为test；
alter table test change id sid int(4);==>修改表 test表 改变 id字段 为sid字段，属性为int(4)，并取消自增长约束
alter table test change sid sid int(4) auto_increment; ==>修改表 test表 改变 sid字段 为sid字段，属性为int(4)，并增加自增长约束
alter table test add class int(4) first;==>修改表 test表 添加 class字段，属性为int(4)，放在第一位（first）；
alter table test add age int(4) after name; 修改表 test表 添加age字段，属性为int(4)，放在name字段后面；
alter table test drop sex; ==》修改表 test表 丢弃 sex字段；
alter table test drop age,drop class;==>修改表 test表 丢弃age字段，丢弃class字段
alter table test add(age int(4),class int(4));==>修改表 test表 添加 age字段和class字段
alter table test change age age int(4) after name;==>修改表 test表 改变 age字段 为 age字段属性为int(4) 放在name字段后面
alter table test modify class bigint(8) after sid;==> 修改表 test表 修改class字段的属性为bigint(8) 放在sid字段后面
change和modify的区别：
两者都是可以对表中字段的属性进行修改，但是使用change时是要写两次字段名，modify只要写一次。change可以对字段名进行重命名，modify就不行
删除主键：因为一张表中最多是只有一个主键，可以直接通过drop primary key的方式进行删除
mysql> alter table test drop primary key;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
直接删除主键是会报错的，因为这个表中的主键字段包含自增长约束，自增长约束是必须依赖主键约束，所以删除主键之前要先删除自增长约束
1，先删除自增长约束：alter table test modify sid int(4);
2，再删除主键约束： alter table test drop primary key;
添加主键
方式一：(通过修改表中字段属性的方式，change和modify都可以)
alter table test change sid sid int(4) primary key;
方式二：（通过添加主键（add primary key）到指定字段上）
alter table test add primary key(age);==》修改表 test表 添加 主键 到age字段上
alter table test add primary key(sid,name); ==>修改表 test表 添加 主键 到sid和name字段上（复合主键，两个字段的值拼接在一起，不能为空且唯一）
-----------------------------------------------------------------
对表中数据进行操作----增删改查
增---insert into 往表中插入数据
方式一：要求表中的字段不多，对表结构要熟悉
1，插入的值的数量必须要跟字段的数量一致
2，值的格式必须要一一对应上字段要求的格式
insert into 表名 values (字段1的值，字段2的值，字段3的值,....),(字段1的值，字段2的值，字段3的值,....),(字段1的值，字段2的值，字段3的值,....);
方式二：对表中指定的字段插入值
1，值的顺序和数量要与声明的字段顺序和数量是一致
2，值的格式必须要对应上字段要求的格式
insert into 表名(字段2，字段3，字段7，字段1)values(字段2的值，字段3的值，字段7的值，字段1的值),(字段2的值，字段3的值，字段7的值，字段1的值);
字符串和日期类型的数据插入必须要加英文的引号，数值类型的数据加不加引号都可以
NULL 和 ' '==》NULL完全是空，完全不占系统的空间，''是占系统的一个字节0的空间
NULL 和 'NULL' ==》 没引号NULL为空，有引号的 'NULL' 表示字符串'NULL'
查---查询select
select * from 表名;===>查询 所有字段（*）的值 来源 xx表;
select sid,name,age from emp; ===>查询 sid,name,age字段的值 来源 emp表；
条件查询，接where条件子句
等于：
select * from emp where name='李四';==》查询 所有字段的值 来自于 emp表 当 name为李四 时；
不等于：!= 、<>
select*from emp where name != '李四'; ==》查询 所有字段的值 来自于 emp表 当 name不为李四 时；
select *from emp where name <> '李四'; ==》查询 所有字段的值 来自于 emp表 当 name不为李四 时；
*NULL值是不参与等于或者不等于、大小匹配的
*NULL值的匹配 is，is not
select * from test where class is null; ==》查询 所有字段的值 来自于 test表 当 class字段值为空 时；
select * from test where class is not null; ==》查询 所有字段的值 来自于 test表 当 class字段值不为为空 时；
多条件查询：
and条件：要求两边同时满足
select name from emp where dept2=101 and incoming=2000;==》查询dept2是101，incoming为2000的那个人员的姓名
or条件：以or为界，两边只要满足一边
select * from emp where dept2=103 or incoming=7500; ==》查询 所有字段的值 来自于 emp表 当 dept2=103 或者 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。。。and。。。。：介于，是包含着等于的闭区间
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 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___';
select * from test where time like '2022-08%';
查询限定数量的数据 ---分页查询limit，只能放在整条SQL的最后
格式：limit 下标,数量
简写：limit 数量 ===》简写后 下标默认为0，从第一行开始
计算机统计排序使用--下标，下标是从0开始的（表示人工的第一个）， 例如下标8 表示人工的第九个
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 desc;
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关键字之前
3，group by分组一般是结合 聚合函数 使用，where条件是不能接聚合函数 ，having条件才可以接聚合函数
select dept2 from emp group by dept2;
select dept2,count(*) from emp group by dept2;
查询emp表中，部门人数大于1的部门编号：
select dept2,count(*) from emp group by dept2 having count(*)>1;
聚合函数： 可以结合group by分组使用，也可以不结合group by分组使用
结合group by分组，对每个分组之后的组生效
不结合group by分组，对整张表生效
where是不能接聚合函数
count() 统计：
select count(*) from 表;==》统计表中有多少行
select count(1) from 表;==》统计表中有多少行
select count(字段) from 表;==》统计表中对应的字段有多少条数据
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(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(incoming))*10 from emp;
as：对需要被查询的字段进行取别名
select dept2 as '部门编号' ,max(incoming) as '最高收入' from emp group by dept2;
珠海工行必问SQL题：
现在有个科目表，里面有两个字段：姓名、科目
求出 表中选择科目大于2科的学生姓名
分组、聚合函数、分组之后的过滤
select 姓名 from科目表 group by 姓名 having count（科目）>2
改---update。。。set 。。。
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 +表名;==>删除整张表，删除数据，表的定义也被删除（释放空间）
-----------------------------------------
数据备份：
一：在数据库中对单个表进行备份操作（在MySQL中执行SQL语句进行备份）
1，先备份表结构：
create table 新表名 like原有表;
2，再备份表中数据(把整张旧表的数据备份到新表中)
insert into 新表 select * from 旧表;
二：备份整个库（整个库中所有的表结构和表数据）==》在Linux界面执行，通过Linux中mysql客户端相关的指令进行
1，备份数据库--mysqldump
mysqldump -u数据库用户 -p密码 需要备份的库 > 脚本文件.sql
2，还原备份的SQL脚本文件（必须要有对应的库）
mysql -u数据库用户 -p密码 必须存在的一个库< 脚本文件.sql
---------------------------------------------------
MySQL数据库的权限操作：
必须进入mysql库中==》use mysql;
查看当前MySQL数据库管理系统有什么用户：
select host,user,password from user;
创建MySQL用户：
方式一：通过在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';==》赋予 所有的权限 在所有库中所有表 给 远程网络权限（%）的root用户，密码为123456
也要进行权限刷新：
flush privileges;
revoke all on *.* from '用户'@'网络位置' ==》取消对应用户的权限
更改密码：
update mysql.user set password=password('123456') where user='dcs45' and host='localhost';
删除用户：
delete from msyql.user where host=网络位置 and user =用户名;
也要进行权限刷新：
flush privileges;
**权限更改后都要刷新权限（flush privilege）
---------------------------------------------------------------------
数据库管理工具（Navicat, DBeaver）
Navicat 是一个连接数据库的图形化管理工具，收费
DBeaver 是免费的，是用JAVA语言开发的。
使用Navicat连接时，需要启动MySQL以及关闭防火墙
---------------------------------------------------------
多表查询
表的拼接方法有5种
1. inner join （内连接）
把不同表中相匹配的数据显示出来
2. left join（左外连接）
显示左表所有内容，右表显示匹配数据，如果没记录显示Null
3. right join（右外链接）
显示右表所有内容，左表显示匹配数据，如果没有记录显示null
4.基本链接
与inner join 相似
5.union（硬连接）
把表进行上下拼接
如果要显示表中所有数据，要使用外连接，即左外连接（left join）或者右外连接（right join），因为内连接只会把表中相匹配的内容进行显示。
左外连接比右外连接的查询速度快，因为左连接是显示左表中的所有内容，当查询时不会遇到Null。
---------------------------------------------------------------
索引 ---- index
索引是一种数据结构，只要是添加在一些被频繁查询的数据字段上，可以有效的提高查询速度。
索引注意点：
1. 创建索引时在大量数据的前提下，添加索引可以提高查询速度
2. 添加索引可以提高查询速度，但是会影响数据插入或数据更新的速度
3. 不是每个字段都要添加索引
4. 索引分为3中
（1）主键索引（primary key，PRI）
一个表中只能存在一个，要求对应字段的值 唯一且不能为空
（2）唯一索引 （unique key, UNI）
一个表中可以存在多个，要求对应字段的值 唯一，但可以为空
（3）普通索引（index, MUL）
一个表中可以存在多个，对索引字段的值没要求
5. 索引优先级：PRI > UNI> MUL
show index from 表名 ===> 查看表中的索引信息
普通索引：
1. 使用create index创建索引
create index 索引名 on表名(字段名)
create index 索引名 on表名（字段名1，字段名2）--复合索引
复合索引：把索引添加在字段上，当两个字段同时被操作才会激活索引
create unique index 索引名===> 创建唯一索引
create unique index 索引名 on表名（字段名1，字段名2）==> 复合索引
删除索引（普通索引和唯一索引一样）：
alter table表名drop index索引名
alter table表名drop index索引名1, drop index索引名2, drop index索引名3 ...
2. 使用alter table 创建索引
alter table 表名 add index 索引名(字段名)
alter table 表名 add unique index 索引名(字段名)===>唯一索引
--------------------------------------------------------------------------
视图（view）
视图是一个虚拟的表，由行和列组成的数据，但是它不是用来存储数据，而是用来显示数据的。
视图是建立在实际的表之上，视图的数据是动态生成的（根据实际的表数据做实时计算得到的）
视图的作用
1. 为了安全，在公司中有些字段是保密字段，可以创建视图，限制对某些字段的查询
2. 简化查询，因为有些查询可能很复杂（多表查询、分组查询），而且这种查询经常被使用，为了避免重复编写SQL语句是的错误，可以通过把这些复杂的SQL语句创建为视图的方法，避免重复编写。
视图的特点
1. 视图是由实际的表产生的
2. 视图的查询本质上是对实际表进行查询操作，所以使用视图查询性能相对差
3. 视图的创建和删除是不会影响实际的基础表的，但是数据是会相互影响的
4. 当一个视图的数据来源是复杂的SQL时，那么视图的数据是不允许修改的
视图的操作
create view视图名 as (select 字段名1，字段名2from表名)===> 创建视图
show create view 视图名 === >查看视图表的结构
show table status where comment = 'view';===> 查看当前库中有哪些视图
drop view 视图名===>删除视图
**对视图数据的增删改查操作，与实际表一样
**对视图数据的增删改查操作是会影响实际表的，实际表修改数据也会影响视图
-------------------------------------------------------------------------
外键约束foreign key
用于约束表和表之间的内容
作用：为了避免一张表的数据太过冗余，就要对表进行拆分解耦，而且为了避免解耦之后数据不完整，所以要引入外键，约束数据的一致性和完整性，保持被约束的表数据的一致性和完整性。
创建表时需要加上数据引擎innodb，MySQL的表创建时会默认为myISAM是不支持外键的。
eg.
create table dcs1 (id int(4) primary key, name varchar(20)) engine = innodb
create tabledcs2 (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字段
删除外键，只要对子表进行操作就可以了
alter table 表名 drop foreign key 外键名；
*当主表中被引用的字段没有X值时，那么从表的外键约束字段就不能存在X值
*对从表插入数据时，如果主表没有对应的数据，则无法插入
*要删除或者修改主表的数据，要先确认主表的数据没有被引用，如果被引用要先删除从表中对应的数据
页:
[1]