MySQL与Navicat的学习笔记

MySQL数据库

数据库--data base--DB

关系型数据库：在数据库里面，表和表是可以发生关系，可以通过某些列的数据关联进行拼接查询，可以直接通过数据库的内置指令或者语句进行表和表的关联操作

非关系型数据库：在数据库里面，表和表是独立的，但是可以通过一些外部代码让表和表发生关系

Redis数据库介绍：https://www.duoceshi.cn/information/Tech_article/343.html

hbase数据库介绍：https://www.duoceshi.cn/information/Tech_article/354.html

Linux端操作MySQL命令

启动mysql服务：service mysqld start

查看mysql服务状态：service mysqld status

重启mysql服务：service mysqld restart

停止mysql服务：service mysqld stop

设置数据库用户密码

安装好mysql后首次设置root用户的密码：

mysqladmin -uroot password '123456'

登录mysql

设置密码后登陆MySQL的两种方法：

密码以密文方式：mysql -uroot -p  (加回车键，再输入密码)

密码以明文方式：mysql -uroot -p123456

MySQL

数据类型

1，int 整型，只能保存4个字节的数据，保存的数字范围是-2^31~2^31-1  ±2147483647，超过这个值的数字需要保存要使用bigint型

2，bigint 大数字 保存的范围比int更大，可以保存8个字节的数据

3，float 浮点型，保存小数，4个字节

4，double 浮点型，保存小数，8个字节

5，varchar 可变长字符串，可变长度，最多可以占2^16字节，性能会差点，节约资源

6，char 字符串 保存固定长度的字符串，固定的占用255个字节，查询性能会好点，但是不节约资源

7，date 日期，固定的保存格式 YYYY-MM-DD

约束：

1，primary key 主键约束，不能重复，不能为空

2，auto_increment 自增长约束，可以自动的在对应列最大值+1插入数据，一般是结合主键去使用

3，not null 非空约束，必须填

4，default  默认值约束，可以定义默认值，当不输入时候自动填入默认值

常见的中文字符在计算机里的编码格式有GBK的汉字占2个字节位,UTF-8的汉字占3个字节位

数据库的操作步骤：

1，mysql -uroot -p  (加回车键，再输入密码)==》进入mysql

2，show databases；==查看当前数据库工具有什么数据库

3，use +库名；==进入数据库

4，create database +新库名；==创建一个新的数据库；

5，create table +新表名（字段名1及类型及约束，字段名2及类型及约束）；==创建一个新表，包含字段1和字段2

6，desc +表名 ；==查看表结构

7，show tables； ==》查看当前库有什么表

   *   select datebase(); ==》查看当前在哪个库操作

8，alter table test rename test1;==》将test表重命名为test1表

9，alter table test change id sid int(20);==》将test表的id字段更改为sid字段、数据类型int(20)，并去掉自增长约束

10，alter table test change sid sid int(20) auto_increment;==》修改test表，改变sid字段为sid字段、数据类型int（20），增加自增长约束

11，alter table test add class int(10);==》修改test表，在表结构的最后面增加class字段、数据类型int（10）

12，alter table test add sex varchar(5) first;==》修改test表，在表结构的最前面增加sex字段、数据类型varchar(5)

13，alter table test add age int(10) after sex;==》修改test表，在字段sex后面增加age字段、数据类型 int(10

14，alter table test drop sex;==》修改test表，删除sex字段

15，alter table test drop age,drop class;==》修改test表，删除age字段和class字段

16，alter table test add(class int(10),sex varchar(5));==》修改test表，增加age字段和class字段

17，alter table test modify sex char(5) after name;==》修改test表，改变sex字段的类型为char(5)，以及将sex字段移动至name字段后面

*modify和change的区别：

它们都是可以修改表字段的定义，但是change需要写两次字段名，change可以修改字段名，modify不行

删除主键：

Alter table test drop primary key;直接删除主键操作会报错

1、首先要先删除自增长约束alter table test modify sid int(20);

2、然后才可以删除主键alter table test drop primary key;

3、增加主键：alter table test change sid sid int(20) primary key;

注意：删除主键约束时不能通过修改字段属性进行，只能直接使用drop；添加主键约束则可以直接通过修改字段属性的方法进行

删除表：drop table +表名；   

通过绝对方式的方法操作其他库中表：alter table 库名.表名 +其他操作

alter table ===》改变表的结构

insert into   ===》插入数据

select * from   ==》查询数据

插入数据时，注意：

1，varchar、char是字符串数据类型，所以插入数据的时候需要加 '' 引号；

2，date 日期型也要加引号

3，非空约束中，类型为字符串的字段默认值为'  ',类型为数字的字段默认值是0，与其他没有非空约束的字段对比，其他字段的NULL是不占空间的，但是非空约束字段的值，默认是占一个字节0的空间

插入数据有三种方式

第一方式：插入数据时，完整的说明表中字段，值的位置要和说明字段的位置一一对应

Insert into test(sid,name,sex,score,phone,time,class) values(1,'xiaomai','228888,'2021-06-11',2130);

第二种方式：插入数据时，单独对某一个或者某多个字段插入，值的位置和说明字段的位置一一对应

insert into  test (sid) values (2);

insert into test(class,sid) values(88,2130);

第三种方式：插入数据时，不说明表中字段情况，默认的往所有的字段插入数据，而且字段的位置也是和表结构顺序一致，所以这种方式要求对表的结构非常熟悉

insert into test values(100,'damai','boy',90.12345,18999990000,'20210718',2130);

插入数据可以同时插入多行值：

insert into test(time) values('2021-06-11'),('20210611'),('2021/06/11'),('2021-6-1'),('2021/6/1'); ==》对test表中的time字段插入多行值

Insert into test values(100,'damai','boy',90.12345,18999990000,'20210718',2130),(99,'laomai','M',88.88888,13188884444,'20213345',13588886666);  ==》对test表中的所有字段插入两行值

删除表数据：

delete

delete from test1 where id=3;  ==》删除test1表中id=3的那一行

delete from test1 where phone=17688889999 and id=2;  ==》删除test1表中phone=17688889999且id=2的数据

更新（修改）表数据：

update

update test1 set phone=18899998888;

update test1 set phone=14466667777 where id=1;

删除表数据有三种方式：

第一种：delete from 表名；===》删除表中数据，但是不释放空间，保留表结构。自增长还是按照原来最大的值继续加一

delete from test1;删除test1表中的数据，但是不释放空间，保留表结构。自增长还是按照原来最大的值继续加一

第二种：truncate 表名； ===》删除表中数据，且释放空间，保留表结构。自增长会从头开始进行

truncate test1;  ==》删除test1表中数据，且释放空间，保留表结构。自增长会从头开始进行

第三种：drop table +表名；===》完全删除一张表，释放空间，且清除表结构

drop table test1;  ===》完全删除test1表，释放空间，且清除表结构

查询语句select

Select *from test2;  ==》查询test2表中所有字段的值

Select *from test2 where id=1;  ==》查询test2表中当id=1时所有字段的值

select name from test2;查询test2表中name字段的值

select name from test2 where id=6;  ==》查询当id=6时表test2表name字段的值

Select与and的混合查询语句

select name from test2 where phone=17688889999 and time='20210619';  ==》查询test2表中当phone=17688889999和time=20210619时name的值

Select与or的混合查询语句

select name,id,phone from test2 where id=1 or time='20210610';  ==》查询test2表中当id=1或者time=‘20210610’时name，id，phone的值

查询test2表中当id不等于1时，id，name字段的值

select id,name from test2 where id<>1;

select id,name from test2 where id!=1;

查询test2表中当score字段为空（null）时所有字段的值

select *from test2 where score is null;

查询test2表中当score字段不为空（null）时所有字段的值

select *from test2 where score is not null;

因为test2表中得name字段存着非空约束，它的NULL值是人为所加

select *from test2 where name is null;  ==》查询不到任何数据

select *from test2 where name='NULL';  ==》查询到name='NULL'的所有字段的值

select *from test2 where score>80;  ==》查询test2表中score>80时所有字段的值

select *from test2 where score<80;  ==》查询test2表中score<80时所有字段的值

select *from test2 where score<=78.65;   ==》查询test2表中score<=78.65时所有字段的值

select *from test2 where score>=78.65;   ==》查询test2表中score>=78.65时所有字段的值

查询test2表中score>=78.65和score<90时所有字段的值

select *from test2 where score>=78.65 and score<90;

select *from test2 where score between 78.65 and 90;

查询test2表中id=1,3,6时所有字段的值

select *from test2 where id=1 or id=3 or id=6;

select *from test2 where id in(1,3,6);

查询test2表中id!=1,3,6时所有字段的值

select *from test2 where id not in(1,3,6);

select *from test2 where phone=13188887777 or phone=17688889999 and time='20210610';

模糊匹配like

select *from test2 where name like '%o%';  ==》查询test2表中name字段中包含‘o’时所有字段的值

select *from test2 where name like 'x%';   ==》查询test2表中name字段中以‘x’开头时所有字段的值

select *from test2 limit 0,3;   ==》查询test2表中从第1行开始的3行所有字段的值

select *from test2 limit 2,3;  ==》查询test2表中从第3行开始的3行所有字段的值

按字段升序排序查询：order by 字段 asc

select *from test2 order by score;

select *from test2 order by score asc;

按字段降序排序查询：order by 字段 desc

select *from test2 order by score desc;

按字段非空值降序排序查询：order by 字段 desc

select *from test2 where score is not null order by score desc;

按字段非空值降序排序查询信息并且取第1行开始的2行值：order by 字段 desc limit 0,2;

select *from test2 where score is not null order by score desc limit 0,2;

面试题：现在有一张学生成绩表，里面有字段id、name、score、class，统计每个班级成绩合格的人数。

1，先过滤出成绩合格的数据

2，对第一步的结果进行分组

3，统计分组之后的人数情况

Select class,count（class） from  学生成绩表 where score>=60 group by class；

必须先过滤再分组

select *from test1 group by time;  ==>group by 分组之后，其他匹配出来的字段值，是随机匹配出来的，一般是表中匹配到的第一条

select *from test2 where score is not null group by time;  ==>mysql中允许使用where条件过滤数据之后，再进行group by分组

select *from test1 group by time where score is not null;

不允许先使用group by分组再使用where条件过滤，否则会执行报错

select *from test1 group by time having score is not null;

使用group by分组之后，需要进行条件过滤则使用having接条件

MySQL中的聚合函数

Count()：统计函数

统计整个test2表有多少行数据

select count(*) from test2;

select count(1) from test2;

select count(score) from test2;  ==》在test2表中，统计score字段有多少条数据

mysql> select count(score) from test2 where time='20210610';

MySQL中的求和函数

Sum()：求和函数

select sum(score) from test2 group by time;

select time,sum(score) from test2 group by time;

MySQL中的求平均值函数

avg( )：平均值函数

select avg(score) from test2;

max（）：最大值函数

min（）：最小值函数

select max(score) from test2;  ==》最大值

select min(score) from test2;  ==》最小值

MySQL中的去重函数

distinct()：去重函数

select distinct(time) from test2;

在test2表中查询字段score的最大值和最小值，以及最大值-最小值的分差并且取别名为fencha（as为取别名）

select max(score),min(score),max(score)-min(score) as fencha from test2;

在sql中进行四则运算：

select max(score)-min(score) from test2;  ==》查询test2字段score的表中最大值-最小值的值

select max(score),min(score),max(score)-min(score) as fencha from test2;  ==》查询test2表中段score的最大值、最小值，以及最大值 - 最小值的分差并且取别名为fencha（as为取别名）

select max(score),min(score),max(score)-min(score)*2 as fencha from test2； ==》查询test2表中段score的最大值、最小值，以及最大值 - 最小值*2的值并且取别名为fencha（as为取别名）

select max(score),min(score),(max(score)-min(score))*2 as fencha from test2;   ==》查询test2表中段score的最大值、最小值，以及(最大值 - 最小值）*2的值并且取别名为fencha（as为取别名）

select max(score),min(score),(max(score)-min(score))/2 as fencha from test2;   ==》查询test2表中段score的最大值、最小值，以及(最大值 - 最小值）/2的值并且取别名为fencha（as为取别名）

having后面可以接聚合函数，where后面不能接聚合函数

数据库备份的两种方式：

第一种方式：在数据库中进行备份

1，创建一个备份表，表结构与需要备份的表一致

create table 新表 like 旧表;

2，往新表插入旧表的所有数据

insert into 新表  select * from 旧表；

第二种方式：在数据库以外进行备份（在linux系统进行）

1，通过mysql数据库的备份指令进行备份数据库备份文件

mysqldump -uroot -p 需要备份的库 > ./sql脚本文件.sql

2，恢复还原备份

首先要在数据库里面新建一个库，用来存放稍后还原的数据

在linux执行：mysql -uroot -p123456 新库 < ./sql脚本文件.sql

MySQL数据库的权限管理

在Linux系统通过命令设置密码：

Mysqladmin -uroot password ‘123456’

MySQL的权限管理涉及到MySQL库，里面包含的表用来定义MySQL工具的一些配置、权限

管理用户权限都需要进入mysql库里面进行操作：

use mysql;

查询MySQL库里面user表的用户信息：

select host,user,password from user;

赋予 select,insert,delete,update权限在*.*所有的库里面的所有表 给到localhost的dcs30用户 识别密码为123456

grant select,insert,delete,update on *.* to 'dcs30'@'localhost' iden

tified by '123456';

flush privileges;  ==》权限刷新命令（每次更改权限之后都要进行权限刷新操作）

查看localhost的dcs30用户的权限情况

show grants fpr 'dcs30'@'localhost';

赋予 所有的权限 在所有库里的所有表 给'root'@'%' 识别密码为123456（'root'@'%'是一个具有远程操作权限root用户，%指远程操作权限）

grant all privileges on *.* to 'root'@'%' identified by '123456';

取消'dcs30'@'localhost';用户的所有权限

revoke all on *.* from 'dcs30'@'localhost';

在MySQL库的user表可以修改用户密码：

update user set password=password('12345')  where user='dcs30' and host='localhost'; ===》修改密码

delete from user where user='dcs30'; ==>可以直接在user表进行用户的删除，注意不要乱删root用户

Linux关闭防火墙命令：service iptables stop

使用Navicat连接数据库

Navicat是一个数据库连接工具，一个直接连接服务器里面的数据库管理工具，图形化界面

3306，是MySQL的默认端口号，一般是使用3306-3309

使用Navicat连接数据库需要注意服务器的MySQL服务有没有启动，以及防火墙有没有关闭

以下是在Navicat里面的查询写的笔记：

#运行方式有两种：

#1，直接点击运行

#2，选择sql语句，右击‘运行已选择的’

# “#”代表注释，被注释的内容不会被程序识别和执行

# 使用#号可以做单行注释

-- 单行注释也可以使用快捷键 ctrl+/  在所在行的行首增加--

-- 多行注释可以使用/* .....*/

/*

这是多行注释的内容

这是多行注释的内容

这是多行注释的内容

*/

--  只写一条sql语句可以不加分号，但是两条或以上就要加分号‘;’

--  这是一个英文的分号';'   这是一个中文的分号‘；’

-- 使用快捷键 ctrl+/进行注释的行，也可以使用快捷键 ctrl+/ 进行取消注释

/*

在Navicat中新建的查询时临时的，可以保存，是保存在Navicat这个工具里，并不是保存在MySQL

我们在Navicat中的查询写好的SQL语句记得进行保存，下次在进入Navicat时，就可以直接重复使用

*/

----------多表查询-讲解--------

select *from aa;

select *from cc;

1,内连接 inner join  对两张表当中有字段的值是相同的，就可以通过内连接进行连接

select * from aa inner join cc on aa.id=cc.s_id;

select * from aa join cc on aa.id=cc.s_id;   #这是一个内连接的简写

2，左连接  left join  以左边的表为主，显示完整的左边表，右边表只会展示匹配上的数据

select * from aa left join cc on aa.id=cc.s_id

3，右连接  right join 以右边的表为主，显示完整的右边表，左边表只会展示匹配上的数据

select * from aa right join cc on aa.id=cc.s_id

左连接和右连接的区别：

1，左连接展示完整的左边表信息，右连接展示完整的右边表信息

2，左连接的查询查询速度比右连接的查询速度要快

4，基本连接  对于多张表当中字段的值相同就可以通过基本连接的方法进行连接（结果类似于内连接，主要区别在于语法上）

select * from aa,cc,ff where aa.id=cc.s_id and ff.fid=aa.id

create table ff(fid int(10),class int(10));

insert into ff values(1001,2130);

select *from ff;

5，硬连接（机械拼接，做上下拼接的） union 主要是对字段相同的表进行拼接，要求拼接的表字段要一致,拼接出来的结果表字段名以第一个表为主

select * from aa union select sid,name from grade union select * from cc;

多表查询的一些常用方法：

上述的内连接，左连接，右连接，基本连接是常见的一些方法

还有临时表方法、嵌套方法

临时表方法

select *from aa inner join cc on aa.id=cc.s_id  ===这是一张临时表t

求张三的成绩：

select t.score from (select *from aa inner join cc on aa.id=cc.s_id) as t where t.name = 'zhangsan';

求张三的成绩：

嵌套方法： 把求出的结果的sql，给到下一个表的条件去使用

嵌套方法  ‘=’方法：

1，先求通过aa表求出zhangsan对应的id（先通过单表的方法求出一个结果）

select id from aa where name='zhangsan'   ===得出结果 1001

2，把前面步骤求出的结果作为下一个表的条件使用

select score from cc where s_id = (select id from aa where name='zhangsan')

嵌套方法  ‘in’方法：

1，先求通过aa表求出zhangsan对应的id（先通过单表的方法求出一个结果）

select id from aa where name='zhangsan'   ===得出结果 1001

2，把前面步骤求出的结果作为下一个表的条件使用

select score from cc where s_id in (select id from aa where name='zhangsan')

嵌套方法 的 ‘=’方法和‘in’方法的区别：

‘=’方法只能对一个值使用，'in'方法可以对一个值或多个值生效，所以直接用in方法即可

求出谁没有参加考试？

条件：没有参加考试（score为空）  结果：谁--名字name

方法一 基本连接+嵌套：

select name from aa,cc where aa.id=cc.s_id  ==求出参加考试的人

select name from aa where name not in (select name from aa,cc where aa.id=cc.s_id) ==求出不包括参加考试的人

方法二 左连接：

select name from aa left join cc on aa.id=cc.s_id where cc.score is null

方法三 右连接：

select name from cc right join aa on cc.s_id=aa.id where cc.score is null

方法四 临时表：

select *from aa left join cc on aa.id=cc.s_id  ===>临时表t

select name from (select *from aa left join cc on aa.id=cc.s_id) t where t.score is null