Mysql数据库

一.查询
查询==select
select *from+表名==》查询全表数据
select name from+表名==》查询单个字段值
select name，phone from+表名==》查询多个字段值
select * from test where name="xiaoliu"; ==》查询name为xiaoliu的所有数据

select * from test where name="xiaoliu" and class=1003; ==》查询name为xiaoliu并且class为1003的所有数据
select * from test where name="xiaoliu" or name="xiaoli"; ==》查询name为xiaoliu或者为xiaoli的所有数据
select * from test where phone in(13588888888,13566666666); ==》查询phone为13588888888或者为1356666666的数据
select * from test where phone not in(13588888888,13566);查询phone不在1358888888或13566里面的数据
select * from test where class is null; ==》查询class为空的数据
select * from test where class is not null; ==》查询class不为空的数据
0不等于null  null前面只能is null, is not null;

select * from test where id>2 and id<5; ==》查询id大于2和小于5的数据
select * from test where id>=2 and id<=5; ==》查询id大于等于2和小于等于5的数据
select * from test where id between 2 and 5; ==》查询id大于等于2和小于等于5的数据

select * from test where name !="xiaoliu";==》查询name不等于xiaoliu的所有数据
select * from test where name <> "xiaoliu";==》查询name不等于xiaoliu的所有数据
select * from  test  where name like "%li%"; ==》查询name字段值有包含li的所有数据
select * from test where name like "lao%"; ==》查询name字段值以lao开头的数据
select * from test where name like "%liu";==》查询name字段值以liu结尾的数据
select * from test  name limit 1,4;== 》查询2到5行数据
select * from test id limit 0,5; ==查询前5行数据
limit m，n(m为下标，n为查询行数)
排序：
从小到大--升序 asc
select *from math order by phone asc;
从大到小--降序 desc
select *from math order by phone desc;

分组
select *from math group by class;

select class,count(*) from test group by class; ==》通过class字段分组然后求出每组对应人数
select class,count(*) from test group by class having class is not null; ==》通过class字段进行分组，然后加条件class 不为null

常用的聚合函数
count(*)--统计
select count(*)from math;
select class,count(*)from math group by class;
select class,count(*)from math group by class having class is not null;



sum(字段）--求和
select sum(id)from math where name="xiaozhou";
select sum(class)from math where name="xiaozhou";

avg(字段）--求平均值
select avg(class)from math where name="xiaozhou";
select avg(id)from math where name="xiaozhou";

max(字段）--求最大值
select max(phone)from math;
select max(id)from math;
select max(class)from math;

min(字段）--求最小值
select min(phone)from math;
distinct(字段）--去重
select distinct(name)from math;
备份表
create table +新表+备份表名
例：create table math1 like math;==》创建math1表，表结构跟math一样
insert into 表名 select *from 表名
例： insert into math1 select *from math;
insert into+表1(字段1，字段2）select 字段1，字段2 from 表2
例：insert into math2 (name,class)select name,class from math1;
注意点：1.插入的表必须存在 2.插入的表是新表，没有数据

备份库
mysqldump -uroot -p123456 dcs9>/dcs9/dcs9.sql==》把dcs9这个库备份放到dcs9目录下的dcs9.sql文件中
mysqldump -uroot -p123456 dcs9_bak注意：dcs9_bak要先存在，不然会找不到库