6月11mysql

6月11日mysql笔记

1、删除主键：

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;

file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml11684\wps1.jpg

2、增加主键：

alter table test change sid sid int(20) primary key;===直接通过修改字段属性的方法增加，用change。

file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml11684\wps2.jpg

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

3、删除表：drop table +表名；  

file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml11684\wps3.jpg

4、通过绝对方式的方法操作其他库中表：表名前要加“所在库名.

”

alter table 库名.表名  add 字段；

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

5、插入、删除数据：

insert into   ===》插入数据

select * from   ==》查询数据

6、插入数据时，注意：

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

（2）date 日期型也要加引号，date 日期型只要插入数据时是常见的日期格式均可插入成功，结果展示统一为yyyy-mm-d格式；

file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml11684\wps4.jpg

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

file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml11684\wps5.jpg

file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml11684\wps6.jpg

file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml11684\wps7.jpg

6、插入数据有三种方式

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

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

=====分别往test表的各字段中插入数据

file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml11684\wps8.jpg

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

insert into  test (sid) values (2);

=====往test表的sid字段插入数据

file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml11684\wps9.jpg

insert into test(class,sid) values(88,2130);====往test表的class与sid字段插入数据

file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml11684\wps10.jpg

file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml11684\wps11.jpg

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

insert into test values(100,'damai','boy',90.12345,18999990000,'20210718',2130);======默认在表内按照字段顺序插入信息

file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml11684\wps12.jpg

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

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表的所有字段同时插入多组数据

7、删除表数据：

delete指令

delete from test1 where id=3;

=====删除来自于test1表且当id=3的数据

file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml11684\wps13.jpg

delete from test1 where phone=17688889999 and id=2;

=====删除来自于test1表 当phone=17688889999且id=2的数据

file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml11684\wps14.jpg

delete from test1;

=====删除表test1

8、更新（修改）表数据：

update

update test1 set phone=18899998888;

=====更新表test1，修改phone字段为18899998888

file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml11684\wps15.jpg

update test1 set phone=14466667777 where id=1;

=====更新test1表设置phone=14466667777，当id=1时

file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml11684\wps16.jpg

9、删除表数据有三种方式：

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

file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml11684\wps17.jpg

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

file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml11684\wps18.jpg

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

10、查询表数据：

select

select *from test1;

======查询test1表的所有字段的值

file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml11684\wps19.jpg

select * from test1 where id=1;

======查询test1表中当id=1时的所有字段的值

file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml11684\wps20.jpg

select name from test1;

======查询test1表中name字段的值

file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml11684\wps21.jpg

select name from test1 where id=6;

=====查询test1表中当id=6时的name字段的值

file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml11684\wps22.jpg

select name from test1 where phone=17688889999 and time='20210619';

=====查询test1表中当phone=17688889999且time='20210619'时的name字段的值

file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml11684\wps23.jpg

select name,id,phone from test1 where id=1 or time='20210610';

=====查询test1表中当id=1或者time='20210610'时的name,id,phone字段的值

file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml11684\wps24.jpg

select name,id,phone from test1 where id=1 or id=6 or id=10;

=====查询test1表中当id=1或者id=6 或者id=10时的name,id,phone字段的值

file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml11684\wps25.jpg

file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml11684\wps26.jpg

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

=====查询test1表中当id不为1时的id,name字段的值

file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml11684\wps27.jpg

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

======查询test1表中当id不为1时的id,name字段的值

file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml11684\wps28.jpg

select * from test1 where score is null;

=====查询test1表中当score字段值为空时的所有字段的值

file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml11684\wps29.jpg

select *from test1 where score is not NULL;

=====查询test1表中当score字段值不为空时的所有字段的值

file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml11684\wps30.jpg

select * from test1 where name = 'NULL';

=====查询test1表中当name = 'NULL'时的所有字段的值（这里的NULL是字符串）

file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml11684\wps31.jpg

select * from test1 where score >80;

=====查询test1表中当score >80时的所有字段的值

select * from test1 where score <80;

=====查询test1表中当score <80时的所有字段的值

file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml11684\wps32.jpg

select *from test1 where score <=78.65;

=====查询test1表中当score <=78.65时的所有字段的值

select *from test1 where score >=78.65;

=====查询test1表中当score >=78.65时的所有字段的值

file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml11684\wps33.jpg

select * from test1 where score between 78.65 and 90;

=====查询test1表中当78.65<=score<=90时的所有字段的值

file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml11684\wps34.jpg

select *from test1 where id in (1,6,10);

=====查询test1表中id满足(1,6,10)的所有字段的值

file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml11684\wps35.jpg

select * from test1 where id not in (1,6,10);

=====查询test1表中id不满足(1,6,10)的所有字段的值

file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml11684\wps36.jpg

select *from test1 where phone =13188887777 or phone=17688889999 and time='20210619';

=====查询test1表中满足（条件一phone=13188887777）或者（条件二phone=17688889999 and time='20210619'）的所有字段的值

file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml11684\wps37.jpg

select * from test1 where name like '%o%';

=====查询查询test1表中当name字段包含“o”的所有字段的值

select * from test1 where name like 'x%';

=====查询查询test1表中当name字段值为x开头的所有字段的值

file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml11684\wps38.jpg

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

select * from test1 limit 0,3;

=====查询查询test1表从第一列开始的三行的所有字段的值

select * from test1 limit 3,2;

=====查询查询test1表从第四列开始两行的所有字段的值

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

file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml11684\wps39.jpg

select *from test1 order by score;

select * from test1 order by score asc;

=====通过order by对字段进行升序

file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml11684\wps40.jpg

select * from test1 order by score desc;

=====通过order by对字段进行降序

file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml11684\wps41.jpg

select *from test1 where score is not null order by score desc limit 0,2；

=====先过滤出score不为空的数据，再对score字段进行降序，再取前两条

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

=====先过滤出score不为空的数据，再对score字段进行降序

file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml11684\wps42.jpg

select *from test1 group by time;  

=====test1表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条件过滤，否则会执行报错

file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml11684\wps43.jpg

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

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

file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml11684\wps44.jpg

面试题：现在有一张学生成绩表，里面有字段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;

=====统计整个表中一共有多少行数据

file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml11684\wps45.jpg

select count(score) from test1;

=====统计在test1中score字段有多少条数据

select count(score) from test1 where time = '20210610';

=====过滤time = '20210610'字段后后再统计test1表中有多少数据

file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml11684\wps46.jpg

sum（）：求和函数

select sum(score) from test1;

=====求分数的总和

select phone,sum(score) from test1 group by phone;

=====分组再求和

file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml11684\wps47.jpg

avg（）：求平均函数

select avg(score) from test1;

=====求test1表score字段平均值

file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml11684\wps48.jpg

max（）：最大值函数

select max(score) from test1;

=====score字段的最大值

min（）：最小值函数

select min(score) from test1;

=====score字段的最小值

file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml11684\wps49.jpg

distinct（）：去重函数

select distinct(time) from test1;

=====对time字段去重

file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml11684\wps50.jpg

在sql中进行四则运算：

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

=====score字段最大值与最小值的差

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

=====score字段最大值与最小值的差

file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml11684\wps51.jpg

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 旧表;

file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml11684\wps52.jpg

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

insert into 新表  select * from 旧表；

file:///C:\Users\ADMINI~1\AppData\Local\Temp\ksohtml11684\wps53.jpg

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

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

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

2，恢复还原备份

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

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