个人主页: 再无B~U~G-CSDN博客
目标:
1.学习MySQL约束
2.增删查改(CRUD)
3.两种查询方式
1. 数据库约束
1.1 约束类型
NOT NULL - 指示某列不能存储 NULL 值。(not null)
UNIQUE - 保证某列的每行必须有唯一的值。 (unique)DEFAULT - 规定没有给列赋值时的默认值。 (default)PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列(或两个列多个列的结合)有唯一标识,有助于更容易更快速地找到表中的一个特定的记录。 (primary key)FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。 (foreign key)CHECK - 保证列中的值符合指定的条件。对于 MySQL 数据库,对 CHECK 子句进行分析,但是忽略 CHECK子句 (check)
1.2 NULL约束
比如说,设置一个学生表
DROPTABLE IF EXISTS student;CREATETABLE student (
id INT NOT NULL,
sn INT,
name VARCHAR(20),
qq_mail VARCHAR(20)
);
1.3 UNIQUE:唯一约束(unique)
DROPTABLE IF EXISTS student;CREATETABLE student (
id INTNOTNULL,
sn INT UNIQUE,//表示值是唯一的
name VARCHAR(20),
qq_mail VARCHAR(20)
);
1.4 DEFAULT:默认值约束(default)
-- 重新设置学生表结构
DROPTABLE IF EXISTS student;
CREATETABLE student (
id INT NOT NULL,
sn INT UNIQUE,
name VARCHAR(20) DEFAULT 'unkown',
qq_mail VARCHAR(20)
);
1.5 PRIMARY KEY:主键约束(primary key)
指定id列为主键:
-- 重新设置学生表结构
DROPTABLE IF EXISTS student;
CREATETABLE student (
id INT NOT NULL PRIMARY KEY,
sn INT UNIQUE,
name VARCHAR(20) DEFAULT 'unkown',
qq_mail VARCHAR(20)
);
对于整数类型的主键,常配搭自增长 auto_increment 来使用。插入数据对应字段不给值时,使用最大值+1。
-- 主键是 NOT NULL 和 UNIQUE 的结合,可以不用 NOT NULLid INT PRIMARY KEY auto_increment,
1.6 FOREIGN KEY:外键约束
外键用于关联其他表的主键或唯一键,语法:
格式:
foreign key (字段名) references 主表(列)
案例:
-- 创建班级表,有使用MySQL关键字作为字段时,需要使用``来标识DROPTABLE IF EXISTS classes;
CREATETABLE classes (
id INT PRIMARY KEY auto_increment,
name VARCHAR(20),
`desc` VARCHAR(100)
);
-- 重新设置学生表结构
DROPTABLE IF EXISTS student;
CREATETABLE student (
id INT PRIMARY KEY auto_increment,
sn INT UNIQUE,
name VARCHAR(20) DEFAULT 'unkown',
qq_mail VARCHAR(20),
classes_id int,
FOREIGN KEY (classes_id) REFERENCES classes(id)//重要
);
1.7 CHECK约束(了解)
MySQL使用时不报错,但忽略该约束:
droptable if exists test_user;createtable test_user (
id int,
name varchar(20),
sex varchar(1),
check (sex ='男'or sex='女')
);
2. 表的设计
三大范式:
2.1 一对一
2.2 一对多
2.3 多对多
创建课程表
-- 创建课程表
DROPTABLE IF EXISTS course;
CREATETABLE course (
id INT PRIMARY KEY auto_increment,
name VARCHAR(20)
);
-- 创建课程学生中间表:考试成绩表
DROPTABLE IF EXISTS score;
CREATETABLE score (
id INT PRIMARY KEY auto_increment,
score DECIMAL(3, 1),
student_id int,
course_id int,
FOREIGN KEY (student_id) REFERENCES student(id),
FOREIGN KEY (course_id) REFERENCES course(id)
);
3. 新增
INSERTINTOtable_name [( column [, column ...])] SELECT ...
-- 创建用户表
DROPTABLE IF EXISTS test_user;
CREATETABLE test_user (
id INT primary key auto_increment,
name VARCHAR(20) comment '姓名',
age INT comment '年龄',
email VARCHAR(20) comment '邮箱',
sex varchar(1) comment '性别',
mobile varchar(20) comment '手机号'
);
-- 将学生表中的所有数据复制到用户表
insertinto test_user(name, email) select name, qq_mail from student;
4. 查询
4.1 聚合查询
4.1.1 聚合函数
常见的统计总数、计算平局值等操作,可以使用聚合函数来实现,常见的聚合函数有:
函数
| 说明 |
COUNT([DISTINCT] expr)
|
返回查询到的数据的数量
|
SUM([DISTINCT] expr)
|
返回查询到的数据的总和,不是数字没有意义
|
AVG([DISTINCT] expr)
|
返回查询到的数据的平均值,不是数字没有意义
|
MAX([DISTINCT] expr)
|
返回查询到的数据的最大值,不是数字没有意义
|
MIN([DISTINCT] expr)
|
返回查询到的数据的最小值,不是数字没有意义
|
COUNT
-- 统计班级收集的 qq_mail 有多少个,qq_mail 为 NULL 的数据不会计入结果SELECTCOUNT(qq_mail) FROM student;
SUM
-- 统计数学成绩总分
SELECTSUM(math) FROM exam_result;
-- 不及格 < 60 的总分,没有结果,返回 NULL
SELECTSUM(math) FROM exam_result WHERE math < 60;
AVG
-- 统计平均总分
SELECTAVG(chinese + math + english) 平均总分FROM exam_result;
MAX
-- 返回英语最高分
SELECTMAX(english) FROM exam_result;
MIN
-- 返回 > 70 分以上的数学最低分
SELECTMIN(math) FROM exam_result WHERE math > 70;
4.1.2 GROUP BY子句(分组查询)
select column1, sum(column2), .. fromtablegroupby column1,column3;
createtable emp(
id int primary key auto_increment,
name varchar(20) notnull,
role varchar(20) notnull,
salary numeric(11,2)
);
insertinto emp(name, role, salary) values ('马云','服务员', 1000.20),
('马化腾','游戏陪玩', 2000.99),
('孙悟空','游戏角色', 999.11),
('猪无能','游戏角色', 333.5),
('沙和尚','游戏角色', 700.33),
('隔壁老王','董事长', 12000.66);
select role,max(salary),min(salary),avg(salary) from emp groupby role;
4.1.3 HAVING (having)
elect role,max(salary),min(salary),avg(salary) from emp groupby role having avg(salary)<1500;
4.2 联合查询
实际开发中往往数据来自不同的表,所以需要多表联合查询。多表查询是对多张表的数据取笛卡尔积:
4.2.1内连接
语法:
select 字段 from 表 1 别名 1 [inner] join 表 2 别名 2 on 连接条件 and 其他条件 ;[inner]表示可以省略的意思。select 字段 from 表 1 别名 1, 表 2 别名 2 where 连接条件 and 其他条件 ;
案例:
(1)查询“张三”同学的 成绩
第一步进行笛卡尔积:
第二步筛选:
命令:
select*from student,score where student.id <=> score.student_id and student.name <=> '张三';
筛选一步一步来:
让两张表的id一一对应,去掉废数据-》
添加条件and只查找名字等于张三的,但是这里有两张表,要使用表名.列名的方式查询,要不然系统不知道你要查询那个表的数据就会报错。
4.2.2 外连接
-- 左外连接,表 1 完全显示select 字段名 from 表名 1 left join 表名 2 on 连接条件 ;-- 右外连接,表 2 完全显示select 字段 from 表名 1 right join 表名 2 on 连接条件 ;
左外连接(left join):
演示:
右外连接 (right join):
4.2.3自连接
比如说把student表自己连接起来:
也可以使用join on
4.2.4 子查询(待补充)
子查询是指嵌入在其他sql语句中的select语句,也叫嵌套查询
- 单行子查询:返回一行记录的子查询
查询与“张三” 同学,类似于:
select * from student where classes_id=( select classes_id from student wherename= '张三 ' );
4.2.5 合并查询
union
该操作符用于取得两个结果集的并集。当使用该操作符时,会自动去掉结果集中的重复行。