一、数据场景查询
1.1 排序
SELECT 字段名 FROM 表名 WHERE 字段=值 ORDER BY 字段名 [ASC|DESC]; ASC: 升序,默认值 DESC: 降序
单列排序, 什么是单列排序:只按某一个字段进行排序,单列排序。
练习:使用年龄降序排序
组合排序,同时对多个字段进行排序,如果第 1 个字段相等,则按第 2 个字段排序,依次类推。
-- 组合排序的语法: SELECT 字段名 FROM 表名 WHERE 字段=值 ORDER BY 字段名 1 [ASC|DESC], 字段名 2 [ASC|DESC];
练习:查询所有数据,在年龄降序排序的基础上,如果年龄相同再以数学成绩升序排序
1.2 聚合函数
-- 语法 SELECT 聚合函数(列名) FROM 表名;
练习:查询学生总数
IFNULL(列名,默认值) 如果列名不为空,返回这列的值。如果为 NULL,则返回默认值。
练习:查询 id 字段,如果为 null,则使用 0 代替
SELECT 字段 1,字段 2... FROM 表名 GROUP BY 分组字段 [HAVING 条件];
练习:
练习:Orders 表数据如下所示,执行如下 SQL 语句,运行结果是?
INSERT INTO student3(id,NAME,age,sex,address,math,english) VALUES (9,‘唐僧‘,25,‘男‘,‘长安‘,87,78), (10,‘孙悟空‘,18,‘男‘,‘花果山‘,100,66), (11,‘猪八戒‘,22,‘男‘,‘高老庄‘,58,78), (12,‘沙僧‘,50,‘男‘,‘流沙河‘,77,88), (13,‘白骨精‘,22,‘女‘,‘白虎岭‘,66,66), (14,‘蜘蛛精‘,23,‘女‘,‘盘丝洞‘,88,88);
SELECT *|字段列表 [as 别名] FROM 表名 [WHERE 子句] [GROUP BY 子句][HAVING 子句][ORDER BY 子 句][LIMIT 子句];
练习:查询学生表中数据,从第 3 条开始显示,显示 6 条。
二、数据库表的约束
2.1 主键
-- 在创建表的时候给字段添加主键 字段名 字段类型 PRIMARY KEY -- 在已有表中添加主键 ALTER TABLE 表名 ADD PRIMARY KEY(字段名); -- 删除主键 alter table 表名 drop primary key;
练习:
1.创建一个学生st1表 字段id,name,age,把id设置为主键
2.添加相同主键看是否插入
3.删除主键
注:主键数在一个表中,只能有一个。不能出现多个主键。
2.2 主键自增
AUTO_INCREMENT 表示自动增长(字段类型必须是整数类型)
-- 创建表时指定起始值 CREATE TABLE 表名( 列名 int primary key AUTO_INCREMENT ) AUTO_INCREMENT=起始值; -- 指定起始值为 1000 -- 创建好以后修改起始值 ALTER TABLE 表名 AUTO_INCREMENT=起始值;
注:DELETE:删除所有的记录之后,自增长没有影响。TRUNCATE:删除以后,自增长又重新开始。
2.3 唯一约束
-- 唯一约束的基本格式 建表 字段名 字段类型 UNIQUE -- 建完表添加 alter table 表名 add unique(字段名)
注:null不存在数据,不属于重复问题
2.4 非空约束
-- 非空约束的基本语法格式 字段名 字段类型 NOT NULL
2.5 默认值
在表中插入一条新记录时,如果没有为某个字段赋值,系统就会自动为这个字段插入默认值。
字段名 字段类型 DEFAULT 默认值
2.6 外键约束
CREATE TABLE emp ( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(30), age INT, dep_name VARCHAR(30), dep_location VARCHAR(30) ); -- 添加数据 INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES (‘张三‘, 20, ‘研发部‘, ‘广州‘); INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES (‘李四‘, 21, ‘研发部‘, ‘广州‘); INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES (‘王五‘, 20, ‘研发部‘, ‘广州‘); INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES (‘老王‘, 20, ‘销售部‘, ‘深圳‘); INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES (‘大王‘, 22, ‘销售部‘, ‘深圳‘); INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES (‘小王‘, 18, ‘销售部‘, ‘深圳‘);
-- 解决方案:分成 2 张表 -- 创建部门表(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 -- 外键对应主表的主键 ) -- 添加 2 个部门 insert into department values(null, ‘研发部‘,‘广州‘),(null, ‘销售部‘, ‘深圳‘); select * from department; -- 添加员工,dep_id 表示员工所在的部门 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;
-- 创建约束的语法 -- 新建表时增加外键: [CONSTRAINT] [外键约束名称] FOREIGN KEY(外键字段名) REFERENCES 主表名(主键字段名) -- 已有表增加外键: ALTER TABLE 从表 ADD [CONSTRAINT] [外键约束名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主键字段名);
-- 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) ) -- 3) 正常添加数据 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; -- 4) 部门错误的数据添加失败 -- 插入不存在的部门 -- Cannot add or update a child row: a foreign key constraint fails INSERT INTO employee (NAME, age, dep_id) VALUES (‘老张‘, 18, 6);
删除外键
ALTER TABLE 从表 drop foreign key 外键名称;
然后在alter添加.....
外键的级联
-- 删除 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 ) -- 再次添加数据到员工表和部门表 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); -- 删除部门表?能不能直接删除? drop table department; -- 把部门表中 id 等于 1 的部门改成 id 等于 10 update department set id=10 where id=1; select * from employee; select * from department; -- 删除部门号是 2 的部门 delete from department where id=2;
三 、多表查询
数据准备
-- 创建部门表 create table dept( id int primary key auto_increment, name varchar(20) ) insert into dept (name) values (‘开发部‘),(‘市场部‘),(‘财务部‘); -- 创建员工表 create table emp ( id int primary key auto_increment, name varchar(10), gender char(1), -- 性别 salary double, -- 工资 join_date date, -- 入职日期 dept_id int, foreign key (dept_id) references dept(id) -- 外键,关联部门表(部门表的主键) ) insert into emp(name,gender,salary,join_date,dept_id) values(‘孙悟空‘,‘男‘,7200,‘2013-02-24‘,1); insert into emp(name,gender,salary,join_date,dept_id) values(‘猪八戒‘,‘男‘,3600,‘2010-12-02‘,2); insert into emp(name,gender,salary,join_date,dept_id) values(‘唐僧‘,‘男‘,9000,‘2008-08-08‘,2); insert into emp(name,gender,salary,join_date,dept_id) values(‘白骨精‘,‘女‘,5000,‘2015-10-07‘,3); insert into emp(name,gender,salary,join_date,dept_id) values(‘蜘蛛精‘,‘女‘,4500,‘2011-03-14‘,1);
-- 需求:查询所有的员工和所有的部门 select * from emp,dept;
-- 设置过滤条件 Column ‘id‘ in where clause is ambiguous select * from emp,dept where id=5; select * from emp,dept where emp.‘dept_id‘ = dept.‘id‘;
-- 查询员工和部门的名字 select emp.`name`, dept.`name` from emp,dept where emp.‘dept_id‘ = dept.‘id‘;
3.1 内连接
-- 隐式内连接:看不到 JOIN 关键字,条件使用 WHERE 指定 SELECT 字段名 FROM 左表, 右表 WHERE 条件 -- 显示内连接:使用 INNER JOIN ... ON 语句, 可以省略 INNER SELECT 字段名 FROM 左表 [INNER] JOIN 右表 ON 条件
3.2 外连接
-- 左外连接:使用 LEFT OUTER JOIN ... ON,OUTER 可以省略 SELECT 字段名 FROM 左表 LEFT [OUTER] JOIN 右表 ON 条件
-- 右外连接:使用 RIGHT OUTER JOIN ... ON,OUTER 可以省略 SELECT 字段名 FROM 左表 RIGHT [OUTER ]JOIN 右表 ON 条件
SELECT 查询字段 FROM 表 WHERE 字段=(子查询);
练习:
1.查询工资最高的员工是谁?
2.查询工资小于平均工资的员工有哪些?
3.3.2 子查询结果是多行单列的时候
SELECT 查询字段 FROM 表 WHERE 字段 IN (子查询);
练习:
1.查询工资大于 5000 的员工,来自于哪些部门的名字?
2.查询开发部与财务部所有的员工信息
3.3.3 子查询的结果是多行多列
SELECT 查询字段 FROM (子查询) 表别名 WHERE 条件;
注:子查询作为表需要取别名,否则这张表没有名称则无法访问表中的字段
练习:
1.查询出 2011 年以后入职的员工信息,包括部门名称