-- 查询语文成绩好于英语成绩的同学
select name,chinese,english from exam_result where chinese > english;
-- 查询总分在 200 分以下的同学
select name,chinese+math+english total from exam_result where chinese+math+english < 200;
AND与OR:
select * from exam_result where chinese > 80 and english > 80;
select * from exam_result where chinese > 80 or english > 80;
select * from exam_result where chinese > 80 or math > 70 and english > 80;
select * from exam_result where (chinese > 80 or math > 70) and english > 80;
范围查询:
1. BETWEEN … AND …
-- 查询语文成绩在 [80, 90] 分的同学及语文成绩
select name,chinese from exam_result where chinese between 80 and 90;
2. IN
-- 查询数学成绩是 58 或者 59 或者 98 或者 99 分的同学及数学成绩
select name,math from exam_result where math in(58,59,98,99);
模糊查询:LIKE
-- 匹配到孙悟空、孙权
select name from exam_result where name like '孙%';
--_匹配严格的一个任意字符 匹配到孙权
select name from exam_result where name like '孙_';
3.7、分页查询:LIMIT
语法:
-- 起始下标为 0
-- 从 0 开始,筛选 n 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n;
-- 从 s 开始,筛选 n 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT s, n;
-- 从 s 开始,筛选 n 条结果,比第二种用法更明确,建议使用
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n OFFSET s;
案例:按 id 进行分页,每页 3 条记录,分别显示 第 1、2、3 页
-- 第 1 页
select id,name,math,english,chinese from exam_result order by id limit 3 offset 0;
-- 第 2 页
select id,name,math,english,chinese from exam_result order by id limit 3 offset 3;
4、修改(Update)
语法:
UPDATE table_name SET column = expr [, column = expr ...]
[WHERE ...] [ORDER BY ...] [LIMIT ...]
案例:
update exam_result set math = 80 where name = '孙悟空';
-- 将曹孟德同学的数学成绩变更为 60 分,语文成绩变更为 70 分
update exam_result set math = 60,chinese = 70 where name = '曹孟德';
5、删除(Delete)
语法:
DELETE FROM table_name [WHERE ...] [ORDER BY ...] [LIMIT ...]
-- 删除孙悟空同学的考试成绩
delete from exam_result where name = '孙悟空';
三、MySQL表的增删改查进阶
1、数据库约束
1.1、约束类型
NOT NULL - 指示某列不能存储 NULL 值。
UNIQUE - 保证某列的每行必须有唯一的值。
DEFAULT - 规定没有给列赋值时的默认值。
PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列(或两个列多个列的结合)有唯一标 识,有助于更容易更快速地找到表中的一个特定的记录。
FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。
CHECK - 保证列中的值符合指定的条件。对于MySQL数据库,对CHECK子句进行分析,但是忽略 CHECK子句。
1.2、NULL约束
创建表时,可以指定某列不为空:
drop table if exists student;
create table student(
id int not NULL,
sn int,
name varchar(20),
qq_mail varchar(10)
);
1.3、UNIQUE:唯一约束
指定sn列为唯一的、不重复的:
drop table if exists student;
create table student(
id int not NULL,
sn int unique,
name varchar(20),
qq_mail varchar(10)
);
1.4、DEFAULT:默认值约束
指定插入数据时,name列为空,默认值unkown:
drop table if exists student;
create table student(
id int not NULL,
sn int unique,
name varchar(20) default 'unkown',
qq_mail varchar(10)
);
1.5、PRIMARY KEY:主键约束
指定id列为主键:
drop table if exists student;
create table student(
id int not NULL primary key,
sn int unique,
name varchar(20) default 'unkown',
qq_mail varchar(10)
);
对于整数类型的主键,常配搭自增长auto_increment来使用。插入数据对应字段不给值时,使用最大 值+1。
-- 主键是 NOT NULL 和 UNIQUE 的结合,可以不用 NOT NULL
id int primary key auto_increment,
1.6、FOREIGN KEY:外键约束
外键用于关联其他表的主键或唯一键,语法:
foreign key (字段名) references 主表(列)
创建班级表classes,id为主键:
-- 创建班级表,有使用MySQL关键字作为字段时,需要使用``来标识
drop table if exists classes;
create table classes(
id int primary key auto_increment,
name varchar(20),
`desc` varchar(100)
);
创建学生表student,一个学生对应一个班级,一个班级对应多个学生。使用id为主键, classes_id为外键,关联班级表id
drop table if exists student;
create table 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使用时不报错,但忽略该约束:
drop table if exists test_user;
create table test_user(
id int,
name varchar(20),
sex varchar(1),
check(sex = '男' or sex = '女')
);
2、表的设计
2.1、三大范式
一对一
一对多
多对多
创建课程表
drop table if exists test_user;
create table test_user(
id int,
name varchar(20),
sex varchar(1),
check(sex = '男' or sex = '女')
);
创建学生课程中间表,考试成绩表
drop table if exists score(
id int primary key auto_increment,
score decimal(3,1),
student_id,
course_id,
foreign key (student_id) references student(id),
foreign key (course_id) references course(id)
);
3,新增
插入查询结果
语法:
insert into table_name column select...
案例:创建一张用户表,设计有name姓名、email邮箱、sex性别、mobile手机号字段。需要把已有的 学生数据复制进来,可以复制的字段为name、qq_mail
-- 创建用户表
DROP TABLE IF EXISTS test_user;
CREATE TABLE test_user (
id INT primary key auto_increment,
name VARCHAR(20) comment '姓名',
age INT comment '年龄&