Mysql2

1.排序 

1)单列排序

-- 查询所有数据,使用年龄降序排序 
select * from student order by age desc; 

2)组合排序 

-- 查询所有数据,在年龄降序排序的基础上，如果年龄相同再以数学成绩升序排序 
select * from student order by age desc, math asc; 

2.聚合函数 

-- 查询学生总数 
select count(id) as 总人数 from student;  
select count(*) as 总人数 from student;

-- IFNULL()函数，如果记录为 NULL，给个默认值，这样统计的数据就不会遗漏 
select count(ifnull(id,0)) from student; 

-- 查询年龄大于20的总数 
select count(*) from student where age>20; 
 
-- 查询数学成绩总分 
select sum(math) 总分 from student; 
 
-- 查询数学成绩平均分 
select avg(math) 平均分 from student; 
 
-- 查询数学成绩最高分 
select max(math) 最高分 from student; 
 
-- 查询数学成绩最低分 
select min(math) 最低分 from student; 

3.分组 

-- 按性别进行分组，求男生和女生数学的平均分 
select sex, avg(math) from student3 group by sex;

-- 查询男女各多少人 
select sex, count(*) from student3 group by sex; 

-- 查询年龄大于 25 岁的人,按性别分组,统计每组的人数 
select sex, count(*) from student3 where age > 25 group by sex ; 

-- 查询年龄大于 25 岁的人，按性别分组，统计每组的人数，并只显示性别人数大于 2 的数据 
-- 对分组查询的结果再进行过滤 
SELECT sex, COUNT(*) FROM student3 WHERE age > 25 GROUP BY sex having COUNT(*) >2; 

4.limit语句 

--公式：开始的索引 = （当前的页码 - 1） * 每页显示的条数
-- 查询学生表中数据，从第3条开始显示，显示6条 
select * from student3 limit 2,6; 

-- 如果第一个参数是0可以省略写
select * from student3 limit 5; 
 
-- 最后如果不够5条，有多少显示多少 
select * from student3 limit 10,5; 

5.数据库备份和还原 

-- 备份day21数据库中的数据到d:day21.sql文件中 
mysqldump -uroot -proot day21 > d:/day21.sql 

--还原 （删除 day21 数据库中的所有表) 
use day21; 
source d:/day21.sql; 

6.主键约束 

-- 创建表学生表st5, 包含字段(id, name, age)将id做为主键 
create table st5 (  
  id int primary key,  -- id为主键  
  name varchar(20),  
  age int
 ) 

-- 删除st5表的主键 
alter table st5 drop primary key;

-- 添加主键 
alter table st5 add primary key(id); 

7.主键自增 

-- 指定起始值为1000 
create table st4 (   
   id int primary key auto_increment, 
   name varchar(20) 
 ) auto_increment = 1000; 
 
insert into st4 values (null, '孔明'); 
select * from st4; 

-- 创建好以后修改起始值 
alter table st4 auto_increment = 2000; 

8.唯一约束 

-- 创建学生表st7, 包含字段(id, name),name这一列设置唯一约束,不能出现同名的学生 create table st7 (  
  id int, 
  name varchar(20) unique
 )

9.非空约束 

-- 创建表学生表st8, 包含字段(id,name,gender)其中name不能为NULL 
create table st8 (
  id int,
  name varchar(20) not null,
  gender char(1) 
) 

10.默认值 

-- 创建一个学生表 st9，包含字段(id,name,address)， 地址默认值是广州 
create table st9 (
   id int,
   name varchar(20),
   address varchar(20)  default '广州' 
) 
 

11.外键约束 

-- 创建部门表(id,dep_name,dep_location) 
-- 一方，主表 
create table department( 
 id int primary key auto_increment,
  dep_name varchar(20),
  dep_location varchar(20)
 ); 
 
-- 创建员工表(id,name,age,dep_id)
-- 多方，从表 
create table employee(
  id int primary key auto_increment,
  name varchar(20),
  age int,
  dep_id int  -- 外键对应主表的主键
 )

-- 1) 删除副表/从表 
employee drop table employee; 
-- 2) 创建从表 employee 并添加外键约束emp_depid_fk 
-- 多方，从表
create table employee(
  id int primary key auto_increment,
  name varchar(20),
  age int,
  dep_id int,  -- 外键对应主表的主键
  -- 创建外键约束  
constraint emp_depid_fk foreign key (dep_id) references department(id)
 ) 

-- 删除employee表的emp_depid_fk外键
alter table employee drop foreign key emp_depid_fk; 

-- 在employee表存在的情况下添加外键 
alter table employee add constraint emp_depid_fk  foreign key (dep_id) references department(id); 

12.外键的级联 

-- 删除employee表，重新创建employee表，添加级联更新和级联删除  
drop table employee;    
create table employee( 
  id int primary key auto_increment,
  name varchar(20),
  age int,
  dep_id int,  -- 外键对应主表的主键
  -- 创建外键约束  constraint emp_depid_fk foreign key (dep_id) references     department(id) on update cascade on delete cascade
)