MySQL数据库复杂操作:数据库约束,查询/连接表操作

MySQL复杂操作

============================================================================================================

*数据库约束

  • NOT NULL - 指示某列不能存储 NULL 值。
  • UNIQUE - 保证某列的每行必须有唯一的值。
  • DEFAULT - 规定没有给列赋值时的默认值。
  • PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列(或两个列多个列的结合)有唯一标识,有助于更容易更快速地找到表中的一个特定的记录。
  • FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。
  • CHECK - 保证列中的值符合指定的条件。对于MySQL数据库,对CHECK子句进行分析,但是忽略CHECK子句。
create table class(
    id int primary key auto_increment,
    name varchar(20)
);

create table student (
    id int unique,
    sn int primary key auto_increment,
    name varchar(20) not null,
    mail varchar(20) default 'qq',
    class-id int,
    foreign key (class_id) references class(id),
    sex varchar(1) comment '性别', -- 字段说明
    check (sex='男' or sex='女')  
    -- MySQL使用时不报错,但忽略该约束
);

MySQL数据库复杂操作:数据库约束,查询/连接表操作

*表的设计

  • 三大范式:一对一、一对多、多对多

*新增(create)——插入查询结果

insert into table1_name (column 1,2,....) select (column 1,2,....) from table2_name ;

// 创建一张用户表,设计有name姓名、email邮箱、sex性别、mobile手机号字段——可以将学生表student中的已知数据-查询相同数据-再插入到创建的用户表中;
MySQL数据库复杂操作:数据库约束,查询/连接表操作
MySQL数据库复杂操作:数据库约束,查询/连接表操作
MySQL数据库复杂操作:数据库约束,查询/连接表操作

*查询(Retrieve)

  • 查询语句中关键字的顺序: from > on> join > where > group by > with > having >select > distinct > order by > limit

1.聚合查询

聚合函数count、sum、avg、max、min

MySQL数据库复杂操作:数据库约束,查询/连接表操作
  ①每个组函数接收一个参数;
  ②默认情况下,组函数忽略列值为null的行不参与计算
  ③有时,会使用关键字distinct剔除字段值重复的条数;
// 注意
  1)当使用组函数的select语句中没有group by子句时,中间结果集中的所有行自动形成一组,然后计算组函数;
  2)组函数不允许嵌套,例如:count(max(…));
  3)组函数的参数可以是列或是函数表达式;
  4)一个SELECT子句中可出现多个聚集函数。

应用示例:

//成绩
create table exam(
    id int,
    name varchar(20),
    chinese decimal(3,1),
    math decimal(3,1),
    english decimal(3,1)
);

// 计算学生人数(数据行数)

select count(*) from exam;

MySQL数据库复杂操作:数据库约束,查询/连接表操作
// 计算这个9个人的数学总分和数学平均分

select sum(math) from exam;
select avg(math) from exam;

MySQL数据库复杂操作:数据库约束,查询/连接表操作
// 求语文最高分和最低分

select max(chinese),min(chinese) from exam;

MySQL数据库复杂操作:数据库约束,查询/连接表操作

GROUP BY子句 ——分组查询

  • GROUP BY 子句可以对指定列进行分组查询;
  • 条件:SELECT 指定的字段必须是“分组依据字段”,其他字段若想出现在SELECT 中则必须包含在聚合函数中。
	select column1, sum(column2), .. from table group by column1,column3;

// 应用示例:

create table emp(
    id int primary key auto_increment,
    name varchar(20) not null,
    role varchar(20) not null,
    salary numeric(11, 2)
);
insert into emp(name, role, salary)
values ('老马哥', '服务员', 1000.20),
    ('化腾', '游戏陪玩', 2000.99),
    ('孙悟空', '游戏角色', 999.11),
    ('猪无能', '游戏角色', 333.5),
    ('沙和尚', '游戏角色', 700.33),
    ('老王', '董事长', 12000.66);

MySQL数据库复杂操作:数据库约束,查询/连接表操作

HAVING

  • GROUP BY 子句进行分组以后,需要对分组结果再进行条件过滤时,不能使用 WHERE 语句,而需要用HAVING;
    // 最分组后的结果再进行筛选,得到平均工资不低于1500的角色、max(salary),min(salary),avg(salary);
    MySQL数据库复杂操作:数据库约束,查询/连接表操作

2. 联合查询

  • 关联查询可以对关联表使用别名。
    // 应用示例:
    MySQL数据库复杂操作:数据库约束,查询/连接表操作
    MySQL数据库复杂操作:数据库约束,查询/连接表操作

内连接

	select 字段 from 表1 别名1 [inner] join 表2 别名2 on 连接条件 and 其他条件;
	select 字段 from 表1 别名1,表2 别名2 where 连接条件 and 其他条件

例1: 得出李四同学的班级名称(学生表、班级表)
MySQL数据库复杂操作:数据库约束,查询/连接表操作
例2: 查询王五同学的各科成绩 (学生表、成绩表、课程表)

	select sco.score,cou.name from student stu 
		join score scoon stu.id=sco.student_id 
		join course cou on sco.course_id=cou.id
		and stu.name='王五';

MySQL数据库复杂操作:数据库约束,查询/连接表操作
例3:查询三个学生的总成绩(学生表、成绩表)

SELECT
	 stu.sn,
	 stu.name,
	 sum( sco.score )
FROM
 	student stu join score sco on stu.id=sco.student_id
GROUP BY
 	sco.student_id;

MySQL数据库复杂操作:数据库约束,查询/连接表操作

外连接

  • 外连接分为左外连接和右外连接。如果联合查询,左侧的表完全显示我们就说是左外连接;右侧的表完全显示我们就说是右外连接。
-- 左外连接,表1完全显示
select 字段名  from 表名1 left join 表名2 on 连接条件;
-- 右外连接,表2完全显示
select 字段 from 表名1 right join 表名2 on 连接条件;

例: 查询所有同学的成绩,如果该同学没有成绩也需要显示出来
MySQL数据库复杂操作:数据库约束,查询/连接表操作

自连接

  • 自连接:同一张表连接自身进行查询;

例1:查询某同学Java的成绩比计算机基础分数低的分数情况

SELECT
 	s1.*,s2.score
FROM
 	score s1
	 JOIN score s2 ON s1.student_id = s2.student_id
	 AND s1.score < s2.score
	 AND s1.course_id = 1
	 AND s2.course_id = 3;

MySQL数据库复杂操作:数据库约束,查询/连接表操作
例2:查询Java比计算机基础分数低的同学及分数

	select stu.name,s1.score java,s2.score 计算机基础
		from score s1
		join score s2 on s1.student_id=s2.student_id
		join student stu on stu.id=s1.student_id
		join course c1 on c1.id=s1.course_id
		join course c2 on c2.id=s2.course_id
		and s1.score<s2.score and c1.name='java'
		and c2.name='计算机基础';

MySQL数据库复杂操作:数据库约束,查询/连接表操作

子查询/嵌套查询

  • 子查询:指一个查询语句包含其他查询语句;limit关键字不能再子查询中使用;

1)在where条件语句中使用子查询:
例1:查询和王五同班的同学;
例2:查询Java和C语言课程的成绩;
MySQL数据库复杂操作:数据库约束,查询/连接表操作
// 使用 in 关键字 就也可以使用not in 关键字查询
// exists关键字——将主查询的数据,放到子查询中做条件验证,根据验证结果(TRUE 或 FALSE)来决定主查询的数据结果是否得以保留。
MySQL数据库复杂操作:数据库约束,查询/连接表操作
// 带 ANY或 ALL的子查询语句

  • <ANY 子查询(匹配任一返回真) :表示条件满足 小于 子查询中任何一个值 就会返回emp的一条记录,相当于筛选出小于子查询最大值的记录。
  • <ALL 子查询(匹配所有返回真) :表示条件满足 小于 子查询中所有的值 才会返回emp的一条记录,相当于筛选出子查询最小值的记录。其他操作符功能类似(=,<>,<,>,<=,>=)。
    MySQL数据库复杂操作:数据库约束,查询/连接表操作
    2)在from语句中使用子查询——把一个子查询当作一个临时表使用:
    所有创建通过查询得出的结果表都是临时表,如:计算实验班学生的平均成绩
    MySQL数据库复杂操作:数据库约束,查询/连接表操作
    MySQL数据库复杂操作:数据库约束,查询/连接表操作
    // 通过查询的临时表 从from中再次查询比临时表成绩高的成绩数据
    MySQL数据库复杂操作:数据库约束,查询/连接表操作

合并查询

  • 在实际应用中,为了合并多个select的执行结果,可以使用集合操作符 union,union all。使用UNION和UNION ALL时,前后查询的结果集中,字段需要一致。
  • union:用于取两个结果集的并集:
    //会自动去掉结果集中的重复行

    MySQL数据库复杂操作:数据库约束,查询/连接表操作
  • union all:该操作符用于取得两个结果集的并集。当使用该操作符时,不会去掉结果集中的重复行;
    MySQL数据库复杂操作:数据库约束,查询/连接表操作
上一篇:MYSQL查询语句2——子查询


下一篇:关于python __str__ and __repr__