数据库--data base--DB mysql操作笔记

数据库--data base--DB

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

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

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

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

启动mysql服务：service mysqld start

查看mysql服务状态：service mysqld status

重启mysql服务：service mysqld restart

停止mysql服务：service mysqld stop

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

mysqladmin -uroot password '123456'

退出使用quit，Exit，或者Ctrl+C

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

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

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

数据类型

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

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

3，float 浮点型，保存小数，4个字节默认保存6位精度（包括小数位和整数位）Float(20,2)指小数点后面2位

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  默认值约束，可以定义默认值，当不输入时候自动填入默认值

5， foreign key 外键约束 用于表与表建立关系模型，使表与表紧密的结合起来。

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

数据库的操作步骤：

1，进入mysql：mysql -uroot -p  是连接数据库服务器的命令。要求你输入自己连接数据库的用户名和密码

2，show databases；==查看当前数据库工具有什么数据库，mysql的指令通常是以“；”结束，所以命令长的时候可以分行写。

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

4，create database +新库名；——创建一个新的数据库；       drop database +库名；——删除库

5，create table +新表名（字段名1及类型及约束，字段名2及类型及约束）；==创建一个新表，包含字段1和字段2  作用在整数类型，字段默认从1开始自增

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

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

7，desc +表名 ；==查看表结构 显示字段名称，数据类型，约束，备注

8，alter table test rename test1;——修改表名称 alter table +表名 rename + 新表名

9，alter table test change id sid int(20);—— 修改表字段 alter table +表名 change +原字段名 +新字段名 数据类型

10，alter table test change sid sid int(20) auto_increment;—— 修改表字段 ——alter table +表名 change +原字段名 +新字段名 数据类型，约束

11，alter table test add class int(10);——添加表字段，默认在最后新增一行alter table +表名 add +新字段名 数据类型

12，alter table test add sex varchar(5) first;——添加表字段，并放到最前面alter table +表名 add +新字段名 数据类型 约束 first

13，alter table test add age int(10) after sex;——添加表字段，并放到某个字段的后面 alter table +表名 add +字段名 数据类型 约束 after+添加的新字段

14，alter table test drop sex;——删除表字段 alter table +表名 drop + 字段

15，alter table test drop age,drop class;——删除表两个字段 alter table +表名 drop 字段1,drop 字段2

16，alter table test add(class int(10),sex varchar(5));——添加表两个字段 alter table +表名 add（字段1数据类型，字段2数据类型）；

17，alter table test modify sex char(5) after name;——修改表字段 alter table +表名 modfy字段名+新数据类型

*modify和change的区别：它们都是可以修改表字段的定义，但是change需要写两次字段名，change可以修改字段名，modify不行，只能修改字段的定义。

删除主键：

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(20);——通过修改sid字段的属性，删除自增长约束

2，然后才可以删除主键：

alter table test drop primary key;——删除主键

3，增加主键：

alter table test change sid sid int(20) primary key;——alter table 表名 change 新字段名 原字段名 新属性 +主键属性；

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

删除表：drop table +表名；   删除后表格式与表数据全部都没有

通过绝对方式的方法操作其他库中表：

alter table 库名.表名  add  。。。。。

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);——插入数据，在名为test的表的sid，name,sex,score,phone,time,class字段添加对应值为‘xiaomai’,‘man’,99,18922228888,‘man’99,18922228888,‘20210611’,2130

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

insert into  test (sid) values (2)；——在表格的sid字段插入2的数据

insert into test(class,sid) values(88,2130);——在名为test表格的class sid字段插入对应的数据

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

insert into test values(100,'damai','boy',90.12345,18999990000,'20210718',2130);在test 表格依次加入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;——删除表中id字段为3的数据

delete from test1 where phone=17688889999 and id=2;——删除表中phone字段为17688889999并且id为2的数据

更新（修改）表数据：

update

update test1 set phone=18899998888;——更新表中ohone字段所有的数据为新数据

update test1 set phone=14466667777 where id=1;——更新表中ohone字段且id字段为1的数据为新数据

删除表数据有三种方式：

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

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

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

查询表数据：

select

select *from test1;——查询所有在test1的表中的所有数据

select * from test1 where id=1;——查询在test1的表中id字段为1的数据

select name from test1;——查询在test1的表中所有name字段的数据

select name from test1 where id=6;——查询在test1的表中name字段且id字段为1的数据

select name from test1 where phone=17688889999 and time='20210619';——查询在test1的表中phone字段为17688889999且time字段为20210619的数据

select name,id,phone from test1 where id=1 or time='20210610';——查询在test1的表中name，idphone字段为且id=1或者time是20210610的数据

select name,id,phone from test1 where id=1 or id=6 or id=10;——查询在test1的表中name，idphone字段为当id=1，6,10的数据

select id,name from test1 where id<>1;——查询在test1的表中name字段的所有值并且id字段不等于1

select id,name from test1 where id != 1;——查询在test1的表中name字段的所有值并且id字段不等于1

select * from test1 where score is null;——查询在test1的表中score字段为空的数据

select *from test1 where score is not NULL;——查询在test1的表中score字段不为空的数据

select * from test1 where name = 'NULL';——查询在test1的表中name字段为NULL的数据

select * from test1 where score >80;——查询在test1的表中score字段小于80的数据

select * from test1 where score <80;——查询在test1的表中score字段大于80的数据

select *from test1 where score <=78.65;——查询在test1的表中score字段小于等于78.65的数据

select *from test1 where score >=78.65;——查询在test1的表中score字段大于等于78.65的数据

select * from test1 where score between 78.65 and 90;——查询在test1的表中score字段大于等于78.65小于等于90的数据

select *from test1 where id in (1,6,10);——查询在test1的表中id为（1，6,10）的数据

select * from test1 where id not in (1,6,10);——查询在test1的表中id不在（1，6,10）的数据

select *from test1 where phone =13188887777 or phone=17688889999 and time='20210619'; ——在表中查找满足条件一或者条件二的数据

select * from test1 where name like '%o%';——在表中查找name字段中包含“o”的数据

select * from test1 where name like 'x%';——在表中查找name字段中包含“x”的数据

%号在mysql里面的作用：匹配0个或者多个字符  （有点像Linux的*号）

select * from test1 limit 0,3;——查看从1到3行的数据

select * from test1 limit 3,2;——查看从第3两行开始的后两行数据

*在生活中，排序都是从1开始的，但是在计算机程序里是从0开始

select *from test1 order by score;——在表格里的score字段按从小到大的顺序排列

select * from test1 order by score asc;——在表格里的score字段按从小到大的顺序排列

select * from test1 order by score desc;——在表格里的score字段按从大到小的顺序排列

select *from test1 where score is not null order by score desc limit 0,2；——在表格里的score字段中选择不为空的数据在筛选前两条

select *from test1 where score is not null order by score desc;——在表格里的score字段中选择不为空的数据从大到小排序

select *from test1 group by time;  

group by 分组之后，其他匹配出来的字段值，是随机匹配出来的，一般是表中匹配到的第一条

select *from test1 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把time字段分组之后在选择scere字段不为空的数据，需要进行条件过滤则使用having接条件

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

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

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

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

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

mysql中的聚合函数

count（）：统计函数

select count(*) from test1;——统计表中一共有多少数据

select count(1) from test1;——统计表中一共有多少数据

select count(score) from test1;——统计表中score字段所有数据

select count(score) from test1 where time = '20210610';——计表中过滤time字段为20210601后score字段所有数据

sum（）：求和函数

select sum(score) from test1;——表中score字段的数据总和

select phone,sum(score) from test1 group by phone;——表中phone字段分组后score字段的数据总和

avg（）：求平均函数

select avg(score) from test1;——表中score字段的平均值

max（）：最大值函数

select max(score) from test1;——表中score字段的最大值

min（）：最小值函数

select min(score) from test1;——表中score字段的最小值

distinct（）：去重函数

select distinct(time) from test1;——对表中time字段去掉重复数据

在sql中进行四则运算：

select max(score)-min(score) from test1;

select max(score),min(score),max(score)-min(score) as jg from test1;

select max(score),min(score),max(score)-min(score)*2 as jg from test1;

select max(score),min(score),(max(score)-min(score))*2 as jg from test1;

select max(score),min(score),(max(score)-min(score))/2 as jg from test1;

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