mysql

黑马程序员

一、DQL查询语句

1.排序（order by ,只是显示方式，不改变数据库中数据的顺序）

desc:降序
asc:升序，默认

• 单列排序：select * from student order by age desc;
• 组合排序：select * from student order by age desc, math asc; – 在按年龄降序排后，若年龄相同再以数学成绩升序排。

2.聚合函数：纵向查询，对列值计算，返回一个结果值。会忽略空值null
max,min,max,avg,sum,count

select count(id) as 总人数 from student; -- 忽略id is null的列
select count(*) as 总人数 from student;
-- 不想忽略null
select ifnull(id,0) from student;
select count(ifnull(id,0)) from student; -- 统计个数

-- 查询数学成绩总分
select sum(math) 总分 from student;  -- 返回只有一个值
-- 查询数学成绩平均分
select avg(math) 平均分 from student;

3.分组 group by ，相同内容分为一组，且返回每组的第一条数据，常与聚合函数一起使用。

-- 按性别进行分组，求男生和女生数学的平均分
select sex, avg(math) from student3 group by sex;
-- 查询年龄大于 25 岁的人,按性别分组,统计每组的人数
select sex, count(*) from student3 where age > 25 group by sex ;
-- 查询年龄>25 岁，按性别分组，统计每组人数，并只显示性别人数>2 的数据。having对分组查询结果再过滤.
select sex,count(*) from student3 where age > 25 group by sex having count(*) > 2;

where和having的区别

• where 用于分组前就先把不符合条件的筛掉，后面不可使用sum，avg等等聚合函数。
• having 用于分组后在过滤数据，可跟聚合函数。

4.limit offset,length

offset：默认是零，从0开始计数
length：返回的行数

应用场景：一页显示固定的条数。

-- 查询学生表中数据，从第 3 条开始显示，显示 6 条。
select * from student3 limit 2,6;

二、数据库表的约束

1.主键约束：primary key：唯一表示数据库中的每一条数据
特点：非空唯一

 -- 1.创建表学生表 st5, 包含字段(id, name, age)将 id 做为主键
 CREATE TABLE st5(
	id INT PRIMARY KEY, -- id为主键
	NAME VARCHAR(20),
	age INT
 );
 DESC st5;
 SELECT * FROM st5;
 
 INSERT INTO st5 VALUES (1, '关羽', 30);
 INSERT INTO st5 VALUES (1, '关云长', 20); -- 报错，主键唯一
 INSERT INTO st5 VALUES (NULL, '关云长', 20); -- 报错，主键非空
-- 2.建表后增加主键约束
alter table st5 modify id int primary key; 
-- 3.删除主键约束
alter table st5 drop primary key;

-- 4.建表时 自增且指定起始值为 1000
create table st4 (
	 id int primary key auto_increment,
	 name varchar(20)
) auto_increment = 1000;

insert into st4 values (null, '孔明'); -- ？？主键非空，自增可以设置为null？？
select * from st4;
-- 5.创建好以后修改起始值
alter table set auto_increment = 2000;
insert into st4 values (null,'刘备');
-- 6.删除自动增长
alter table st4 modify id int;
-- 7.建表后增加自动增长
alter table st4 modify id int auto_increment; 

2.唯一约束：unique：某一列不能出现重复的值

-- 1创建表时添加唯一约束 
 CREATE TABLE st4(
	id INT,
	NAME VARCHAR(20) UNIQUE
 );
 INSERT INTO st4 VALUES (1,'张三');
 SELECT * FROM st4;
 INSERT INTO st4 VALUES (2,'张三'); -- 报错Duplicate entry '张三' for key 'st4.name'
-- 2建表后增加唯一约束
 alter table st4 modify name varchar(20) unique;
-- 3删除唯一约束
 alter table st4 modify name varchar(20);

3.非空约束：not null：某一列不能为null

 --1.创建表加非空约束
 CREATE TABLE st8(
	id INT,
	NAME VARCHAR(20) NOT NULL, 
	gender CHAR(1)
 
 );
 INSERT INTO st8 VALUES(1,'张三丰','男');
 SELECT * FROM st8;
 INSERT INTO st8 VALUES (2,NULL,'男'); -- 报错，not null
 -- 2.创建表后，添加非空约束
 alter table st8 modify name varchar(20) not null;
 -- 3. 删除name的非空约束
 alter table st8 modify name varchar(20);

4.外键约束：foreign key

-- 表冗余 员工可以通过 dep_id找到对应的部门
 -- 部门表（主表）
 --1. 创建表时增加主键约束，且自增
 CREATE TABLE department(
	id INT PRIMARY KEY  AUTO_INCREMENT,
	dep_name VARCHAR(20),
	dep_location VARCHAR(20)
 
 );
 -- 员工表
 CREATE TABLE  employee(
	id INT PRIMARY KEY  AUTO_INCREMENT,
	NAME VARCHAR(20),
	age INT,
	dep_id INT -- 外键对应主表的主键
 
 );
 
 INSERT INTO department VALUES (NULL,'研发部','广州'),(NULL, '销售部', '深圳');
 SELECT * FROM department;
 
 -- 添加员工
INSERT INTO employee (NAME, age, dep_id) VALUES ('张三', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('李四', 21, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('王五', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('老王', 20, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES ('大王', 22, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES ('小王', 18, 2);
SELECT * FROM employee;
INSERT INTO employee (NAME, age, dep_id) VALUES ('小王', 18, 5); -- dep_id=5不存在

副表、从表：使用别人的数据，别人约束，含外键
主表：约束别人，含主键

-- 1.建表时增加主外键约束
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)
)
-- 插入一系列数据insert into employee (NAME, age, dep_id) VALUES ('小王', 18, 2);
-- 插入不存在的部门 ，因为有外键约束会报错，如果不加外键约束，从表可以插入主表不存在的数据.

-- 2.在从表存在的情况下添加外键
alter table employee add constraint emp_depid_fk foreign key(dep_id) references department(id);
-- 3.删除从表中的emp_depid_fk外键
alter table employee drop foreign key emp_depid_fk;

5.级联操作：在对主表进行修改和删除操作时，同时也更新或删除副表的外键值，称为级联操作

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
);

三、范式

1.第一范式：每一列不可再分，原子性
2.第二范式: 一张表只描述一件事，表中每一列都完全依赖于主键

小结 范式 特点
1NF 原子性：表中每列不可再拆分。
2NF 不产生局部依赖，一张表只描述一件事情。
3NF 不产生传递依赖，表中每一列都直接依赖于主键。而不是通过其它列间接依赖于主键。