黑马程序员
一、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 不产生传递依赖,表中每一列都直接依赖于主键。而不是通过其它列间接依赖于主键。