MySQL大总结_mysql总结

-- 查询语文成绩好于英语成绩的同学
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 '年龄&