MySQL之SQL语句(排序,分组,分页,约束)

单表查询

排序

关键字	含义
order by	排序查询
asc	升序(默认)
desc	降序

--语法
select 字段名称列表(字段名1,字段名2...) from 表名 order by 排序的字段名 排序规则
--示例  查找员工表的信息按照工资升序排序
select * from emp order by salary asc(可省略);
--查找名字  按照年龄降序
select name from student order by age desc;

多个字段排序
多个字段同时排序:优先按照第一个字段的排序规则,
第一个字段排序值如果相同,这个时候再按照第二个字段规则排序

--查找学生表的信息,数学成绩升序,英语成绩降序
select name,math,english from student order by math asc,english desc;

分组查询
分组查询:查询的是哪些字段
查询的字段:可以是分组字段,使用聚合函数
如果带有条件进行分组查询,要先去判断条件,在进行分组先where,后group by

关键字	含义
group by	分组查询
having	筛选条件

select 字段名称列表 from 表名 where 条件 group by 分组的字段
--示例  查询班级里男生的平均分和女生的平均分
select avg(score),sex from student  group by sex;
--查询班级里男生的平均分和女生的平均分,70分以下的人不参与分组
select avg(score0,sex from student where score>70 group by sex;
--查询平均工资大于5000的部门,筛选条件 部门人数>3
--牵扯到了多表查询
SELECT AVG(emp.salary),dept.dname,COUNT(emp.dept_id) 
	FROM emp,dept 
	WHERE emp.dept_id=dept.id 
	AND emp.salary>5000 
	GROUP BY emp.dept_id 
	HAVING COUNT(emp.dept_id)>3;

having和group by的区别
group by是在where条件后面用, 不能使用聚合函数的!但是查询的时候可以查询聚合函数
having是在group by 后面使用 ,后面使用聚合函数
分页查询

关键字	含义
limit	分页查询

起始行数=(当前页码数-1)*每页显示的条数,

--语法
select 字段名称列表 from 表名 limit 起始行数,每页展示的行数;
--示例
--每页两条数据  第一页
select * from student limit 0,2;
--第二页
select * from student limit 2,2;

约束

约束:通过一些特定的关键字 保证数据的安全性,正确性和有效性!

关键字	含义
default	默认约束
not null	非空约束
unique	唯一约束
auto_increment	自增长约束
check	检查约束(mysql检查约束使用不了,暂且不讲)
primary key	主键约束
foreign key	外键约束

1.default 默认约束
是当某个字段没有赋值的时候起作用,如果赋值了,那么按照实际值 赋值即可!

--建表时创建默认约束
create table student(
	id int,
	name varchar(20),
	sex varchar(5) default '男' --默认性别为男
)

2.not null非空约束
顾名思义,不允许null,若一个字段含有非空约束
则插入数据时,必须增加该字段的值,否则插入不了;

--建表时创建非空约束
create table student(
	id int,
	name varchar(20) not null --非空约束
)
--创建表后再添加非空约束
alter table student modify name varchar(20) not null;
--删除name的非空约束
alter table student modify name varchar(20);

3.unique 唯一约束
值不能重复

--创建表时添加约束
create table student(
	id int,
	number varchar(20) unique
);
--创建表中添加唯一约束
alter table student modify number varchar(20) unique;
--删除唯一约束
alter table student drop index number;

4.primary key主键约束

• 若某一列添加了该约束，则代表了非空，且唯一；
• 一张表只能有一个字段为主键；
• 主键就是表中记录的唯一标识；
• 主键设为非业务字段(除了标识没有其他业务上的意义),常与自增长字段一起使用

--创建表时创建主键约束
create table student(
	id int primary key auto_increment,
	name varchar(20) not null
)
--创建表后添加主键
alter table student modify id int primary key;
--删除主键约束
alter table student drop primary key;

5.auto_increment自增长约束

• AUTO_INCREMENT可以约束任何一个字段，该字段不一定是PRIMARY KEY字段，也就是说自增的字段并不等于主键字段。
• 当插入第一条记录时，自增字段没有给定一个具体值，可以写成DEFAULT/NULL，那么以后插入字段的时候，该自增字段就是从1开始，没插入一条记录，该自增字段的值增加1。当插入第一条记录时，给自增字段一个具体值，那么以后插入的记录在此自增字段上的值，就在第一条记录该自增字段的值的基础上每次增加1。

--创建表时  添加自增长约束
--创建自增长约束时需要给 该字段设置一个键 key
CREATE TABLE stu(
	id INT AUTO_INCREMENT,
	NAME VARCHAR(20),
	KEY(id)
)
-- 删除自增长约束
alter table student modify id int;
--创建表之后添加自增长约束
ALTER TABLE student MODIFY id INT AUTO_INCREMENT;

6.foreign key外键约束

外键约束:让多个表之间产生一种关系,来保证数据的安全性和有效性!

如图 出现了数据的冗余 为了解决可以拆表


员工表中的dept_id对应部门表的id

但是 如图所示 部门表不存在 id为50 的部门
这条数据是非法数据
为了限制该这种数据的存在 出现了外键约束
外键:4为了防止非法数据,加入一种潜在关系 外界约束

• 外键，就是从表中与主表主键对应的那一列，如：员工表中的dep_id，其中，主表是一方，用来约束别人的表，从表可以是多方，被别人约束的表。

注意：外键可以为NULL，但是不能是不存在的外键值。
创建表时 创建外键

--语法
 create table 表名(
                   ...
                   外键列
                   constraint 外键名称 foreign key (外键列名称) references 主表名称(主表主键名称)
        );

示例

--部门表  主表
CREATE TABLE dept(  
	id INT PRIMARY KEY AUTO_INCREMENT,
	dname VARCHAR(20),
	loc VARCHAR(20) 
); 

-- 创建员工表(id,name,age,dep_id) 
-- 多方，从表 
CREATE TABLE emp(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20),
		dept_id INT, -- 外键对应主表的主键
        CONSTRAINT emp_dept_fk FOREIGN KEY (dept_id) REFERENCES dept(id)
); -- 外键对应主表的主键  

创建表后对外键的操作

--在创建表后添加外键：
ALTER TABLE 表名称 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);
--示例
ALTER TABLE emp ADD CONSTRAINT emp_dep_fk FOREIGN KEY (dept_id) REFERENCES dept(id);

--删除外键
ALTER TABLE 表名称 DROP FOREIGN KEY 外键名称;
--示例
ALTER TABLE emp DROP FOREIGN KEY emp_dep_fk;

级联操作
如上图,想要删除/修改部门表中的教研部
必须先删除/修改员工表中,dept_id为1的人,这无疑是很不方便的

• 所以加入级联操作----修改或者删除主表 (从表的数据会随着主表的操作而变化!)

关键字	含义
on delete cascade	级联删除
on update cascade	级联修改

ALTER TABLE emp ADD  
	CONSTRAINT emp_dep_fk FOREIGN KEY (dept_id) 
	REFERENCES dept(id) 
	ON DELETE CASCADE  ON UPDATE CASCADE ;

数据库的备份和还原

备份

• 命令行的方式
  1.进入dos
  2.备份的语法格式 mysqldump -u用户名 -p密码 数据库的名称 > 保存的路径
• 用推行界面化的方式来进行数据库的备份
  1.进入到sqlyog工具中— 在数据库的上面右键—选择备份

还原

• 命令行的方式
  1.登录的msyql数据库中
  2.登录成功之后,删除当前要还原的这个数据库
  3.source 之前备份的sql文件路径-- 回车
• 用推行界面化的方式来进行数据库的备份
  1.在sqlyog中删除当前mydb_04数据库,然后右键选择 以sql的转存文件来导入…