mysql详解（2)

高级查询（重要）

排序（重要）

#2.排序查询(将查询出来的数据 进行排序)
#desc 降序 asc 升序 默认值 可以不写
#语法: select * from 表名 order by 排序的字段 排序的方式,排序的字段 排序的方式
#	2.1 查询所有数据,使用年龄降序排序
SELECT * FROM student3 ORDER BY age DESC;

#	2.2 查询所有数据,在年龄降序排序的基础上，如果年龄相同再以数学成绩降序排序
SELECT * FROM student3 ORDER BY age DESC , math DESC;

聚合函数（重要）

#3.聚合查询 , 聚合函数 : 出现的位置在字段的位置上 ,聚合函数不允许出现在条件上
#select 聚合函数 from　表名　where　条件 
#count(字段) 计数 
#avg(字段) 平均数 
#sum(字段) 总和 
#min(字段) 最小 
#max(字段) 最大
#	3.1查询学生总数
SELECT COUNT(*)  FROM student3; # 初学者使用* 
SELECT COUNT(NAME)  FROM student3;
SELECT COUNT(english)  FROM student3;

#	3.2查询年龄大于40的总数
SELECT COUNT(*) FROM student3 WHERE age>40;

#	3.3查询数学成绩平均分
SELECT SUM(math)/8 FROM student3;
SELECT AVG(math) FROM student3;

#	3.4查询数学成绩最高分
SELECT MAX(math) FROM student3;

#	3.5查询数学成绩最低分
SELECT MIN(math) FROM student3;

分组和过滤（重要）

#4.分组查询
SELECT * FROM student3;
#	4.1按性别分组
SELECT COUNT(*) FROM student3 WHERE sex='男';
SELECT COUNT(*) FROM student3 WHERE sex='女';
#	4.2查询男女各多少人
#语法:  select 分组的字段信息 || 或者聚合函数 from 表名 group by 分组的字段
#分组:将有特性的数据分为一组 , 分组后不考虑个人信息 而是总体的信息
SELECT * FROM student3;
SELECT sex,COUNT(*) FROM student3 GROUP BY sex;

#	4.3查询年龄大于25岁的人,按性别分组,统计每组的人数
SELECT sex,COUNT(*) FROM student3 WHERE age > 25 GROUP BY sex;

#	4.4查询年龄大于25岁的人,按性别分组,统计每组的人数,并只显示性别人数大于2的数据
# 先执行 where后的条件
#select 分组的字段信息 || 或者聚合函数 from 表名 group by 分组的字段 having 分组条件 
SELECT sex,COUNT(*) FROM student3 WHERE age > 25 AND COUNT(*) > 2 GROUP BY sex ;
SELECT sex,COUNT(*) FROM student3 WHERE age > 25 GROUP BY sex HAVING COUNT(*) > 2 ;

#having和where对比
#select 字段 from 表名 where 条件 group by 分组字段 having 条件
#区别1:执行时机不同  where查询表后立即执行，　在分组前进行条件的筛选过滤	
#having 在分组的基础上 进行筛选

分页（重要）

#5.limit  方言(每个数据库有自己特定的语法 , 只在mysql特有)
#	数据准备
	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 * FROM student3;
#查询的数据过多 希望进行分页
#语法:select * from 表名 limit startIndex , pageSize
#startIndex : 开始的索引
#pageSize : 表示每页 显示的个数
#展示第1页的2条数据
SELECT * FROM student3 LIMIT 0,2;
#展示第2页的2条数据
SELECT * FROM student3 LIMIT 2,2;
#展示第3页的2条数据
SELECT * FROM student3 LIMIT 4,2;
#展示第4页的2条数据
SELECT * FROM student3 LIMIT 6,2;
#展示第N页的pageSize条数据  后面具体的案例演示
SELECT * FROM student3 LIMIT  (n-1)*pageSize,pageSize;

约束概述

主键约束

非空默认唯一约束

#数据从来哪来,用户填写的(自己输入)  , 但是如果我们对数据有一定的要求?
#约束: 约定数据如何添加 , 必须满足约束 才可以操作数据库

#主键是唯一+非空(其他特点) 但是唯一+非空 不一定是主键
#主键(primary key): 唯一+非空   (可以加入自增)
#非空(not null): 要求数据不能为空
#唯一(unique): 该字段必须唯一
#默认(default 值): 默认值约束(用户没有书写的时候 默认值是什么)
#约束的创建有好几种方式 1.创建表的时候 就加约束(常用) 2.创建表以后 修改表加约束
/*
CREATE TABLE 表名(
字段 类型 [约束] [约束],
字段 类型 [约束]
)
*/
CREATE TABLE student4(
 id INT PRIMARY KEY, #区分数据  PRIMARY KEY 主键
 username VARCHAR(32) UNIQUE, #用户名 UNIQUE 唯一
 cardId VARCHAR(32) NOT NULL, #身份证号
 gender VARCHAR(32) DEFAULT '男', #性别  DEFAULT NULL 默认情况下 数据库给我们添加的约束
 email VARCHAR(32) DEFAULT NULL #邮箱
);

CREATE TABLE `student4` (
  `id` INT(11) NOT NULL,
  `username` VARCHAR(32) DEFAULT NULL,
  `cardId` VARCHAR(32) NOT NULL,
  `gender` VARCHAR(32) DEFAULT '男',
  `email` VARCHAR(32) DEFAULT NULL,
  PRIMARY KEY (`id`),  #主键约束
  UNIQUE KEY `username` (`username`) #唯一约束
);

#主键自增
#主键: 唯一+非空(加入自增长  auto_increment )   唯一+非空!= 主键
CREATE TABLE `student5` (
  `id` INT(11) PRIMARY KEY AUTO_INCREMENT ,
  `username` VARCHAR(32) 
) ;
#AUTO_INCREMENT 自动增长 使用数字类型
#使用了自增长 :自增长的值不需要关心 直接赋值为null即可
#写NULL 的含义是我们不参与主键的设计  由数据库自己处理
INSERT INTO student5(id , username ) VALUES(NULL , "张三");

表关系

关系型数据库: 数据库中存在着很多的表, 表和表之间是有一定关系存在的
关系:
一对一: 老公老婆
一对多: 部门和员工, 分类和商品
多对多: 老师和学生 , 学生和课程

一对多关系说明（重要）

建表的原则：在多方创建一个字段用于指向主表的主键

一对多关系建立（重要）

-- 先创建部门表
CREATE TABLE department (
	id INT PRIMARY KEY AUTO_INCREMENT,
	dep_name VARCHAR(20),
	dep_location VARCHAR(20)
);
-- 添加2个部门
INSERT INTO department (dep_name, dep_location) VALUES ('研发部', '广州'), ('销售部', '深圳');

-- 然后创建员工表,添加外键约束
CREATE TABLE employee (
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20),
	age INT,
	dep_id INT
);
INSERT INTO employee (NAME, age, dep_id) VALUES
('张三', 20, 1),
('李四', 21, 1),
('王五', 20, 1),
('老王', 20, 2),
('大王', 22, 2),
('小王', 18, 2);
#添加约束: FOREIGN KEY  外键
#alter table 从表名 add [constraint] [外键的名称] foreign key (从表的某个字段) references 主表(主表的字段)
ALTER TABLE employee ADD FOREIGN KEY (dep_id) REFERENCES department(id);

#删除外键(了解)
#ALTER TABLE 从表 drop foreign key 外键名称;
ALTER TABLE  employee DROP FOREIGN KEY employee_ibfk_1;

多对多关系说明（重要）

建表原则: 创建一张中间表, 表中至少有两个字段 用于指向两边主表的主键

多对多关系建立（重要）

#学生表
CREATE TABLE stu(
   sid INT  PRIMARY KEY AUTO_INCREMENT,
   `name` VARCHAR(32)
);
#课程表
CREATE TABLE class(
   cid INT  PRIMARY KEY AUTO_INCREMENT,
   className VARCHAR(32) #课程名称
);
SELECT * FROM stu;
SELECT *FROM class;

#创建中间表 中间表的外键类型尽量一致
CREATE TABLE stu_class(
  sid INT , 
  cid INT 
);
#添加学生表数据
INSERT INTO stu(`name`) VALUES('张三'),('李四'),('王五');
#添加课程表数据
INSERT INTO class(`className`) VALUES('语文'),('数学'),('英语');

#中间表添加数据
INSERT INTO stu_class(sid , cid) VALUES(1,1);
INSERT INTO stu_class(sid , cid) VALUES(1,2);
INSERT INTO stu_class(sid , cid) VALUES(1,3);
INSERT INTO stu_class(sid , cid) VALUES(2,2);
INSERT INTO stu_class(sid , cid) VALUES(2,3);
INSERT INTO stu_class(sid , cid) VALUES(3,1);
INSERT INTO stu_class(sid , cid) VALUES(3,3);

SELECT * FROM stu_class

note: 如果想删除主表数据, 先将从表与之有关的数据先删除 再删主表