MySQL表的增删改查

目录

一、初阶

1.新增CREATE

2.插入INSERT

2.1全列插入

2.2指定列插入 

3.查询SELECT

3.1全列查询

3.2指定列查询

3.3 查询字段为表达式

3.4 指定列起别名

3.5去重DISTINCT

3.6 排序ORDER BY  ASC DESC

3.7条件查询WHERE

3.8 分页查询LIMIT

4.修改UPDATE...SET

 5.删除DELETE

二、进阶

1.数据库约束

1.1 NULL约束

 1.2UNIQUE：唯一约束

1.3DEFAULT：默认值约束

1.4 PRIMARY KEY：主键约束

1.5 FOREIGN KEY：外键约束

1.6CHECK：限制范围约束

2.表的设计

3查询

3.1聚合查询

 3.2联合查询

---

一、初阶

1.新增CREATE

CREATE TABLE student;

2.插入INSERT

2.1全列插入

INSERT INTO student VALUES (101, 10001, '孙悟空', '11111');

2.2指定列插入 

INSERT INTO student (id, sn, name) VALUES
 (102, 20001, '曹孟德'),
 (103, 20002, '孙仲谋');

3.查询SELECT

3.1全列查询

SELECT * FROM exam_result;

3.2指定列查询

SELECT id, name, english FROM exam_result;

3.3 查询字段为表达式

SELECT id, name, chinese + math + english FROM exam_result;

3.4 指定列起别名

SELECT column [AS] alias_name [...] FROM table_name;

3.5去重DISTINCT

SELECT DISTINCT math FROM exam_result;

3.6 排序ORDER BY  ASC DESC

-- ASC 为升序（从小到大）

-- DESC 为降序（从大到小）

-- 默认为 ASC

NULL 数据排序，视为比任何值都小，升序出现在最上面，降序出现在最下面

SELECT name, qq_mail FROM student  ORDER BY qq_mail DESC;

3.7条件查询WHERE

1. WHERE 条件可以使用表达式，但不能使用别名。

2. AND 的优先级高于 OR ，在同时使用时，需要使用小括号 () 包裹优先执行的部分

SELECT * FROM exam_result WHERE (chinese > 80 or math>70) and english > 70;
SELECT name FROM exam_result WHERE name LIKE '孙_';
SELECT name, qq_mail FROM student WHERE qq_mail IS NOT NULL;

3.8 分页查询LIMIT

-- 从 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;

4.修改UPDATE...SET

UPDATE exam_result SET chinese = chinese * 2;

 5.删除DELETE

DROP TABLE IF EXISTS for_delete;

二、进阶

1.数据库约束

NOT NULL - 指示某列不能存储 NULL 值。

UNIQUE - 保证某列的每行必须有唯一的值。

DEFAULT - 规定没有给列赋值时的默认值。

PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标

识，有助于更容易更快速地找到表中的一个特定的记录。

FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。

CHECK - 保证列中的值符合指定的条件。对于 MySQL 数据库，对 CHECK 子句进行分析，但是忽略 CHECK子句。

1.1 NULL约束

CREATE TABLE student (
   id INT NOT NULL,
   sn INT,
   name VARCHAR(20),
   qq_mail VARCHAR(20)
);

 1.2UNIQUE：唯一约束

CREATE TABLE student (
   id INT NOT NULL,
   sn INT UNIQUE,
   name VARCHAR(20),
   qq_mail VARCHAR(20)
);

1.3DEFAULT：默认值约束

指定插入数据时， name 列为空，默认值 unkown ：

CREATE TABLE student (
   id INT NOT NULL,
   sn INT UNIQUE,
   name VARCHAR(20) DEFAULT 'unkown',
   qq_mail VARCHAR(20)
);

1.4 PRIMARY KEY：主键约束

 指定id列为主键：

CREATE TABLE student (
   id INT NOT NULL PRIMARY KEY,
   sn INT UNIQUE,
   name VARCHAR(20) DEFAULT 'unkown',
   qq_mail VARCHAR(20)
);

1.5 FOREIGN KEY：外键约束

外键是在其他表中充当主键的唯一键。

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.6CHECK：限制范围约束

CHECK约束：如果对某一列进行约束，表示某一列只能取特定范围的值。

create table test_user (
   id int,
   name varchar(20),
   sex varchar(1),
   check (sex ='男' or sex='女')
);

2.表的设计

一对一、一对多、多对多。

三大范式

 三大范式包括第一范式（1NF）、第二范式（2NF）、第三范式（3NF）。

第一范式：满足数据库中的每一列的字段都是单一属性、不可再分且每一字段都是由基本的数据类型构成。

第二范式：在满足1NF的基础上再满足依赖性的两个约束：一张表必须有一个主键；非主键类必须完全依赖于主键，而不能只依赖主键的一部分。

第三范式：在满足2NF的基础上，另外再满足一个条件：非主键列必须直接依赖于主键，不能存在传递依赖。

3查询

3.1聚合查询

3.1.1聚合函数

SELECT SUM(math) FROM exam_result WHERE math < 60;

SELECT AVG(chinese + math + english) 平均总分 FROM exam_result;

SELECT MAX(english) FROM exam_result;

SELECT MIN(math) FROM exam_result WHERE math > 70;

 3.1.2GROUP BY子句

SELECT 中使用 GROUP BY 子句可以对指定列进行分组查询。需要满足：使用 GROUP BY 进行分组查 询时，SELECT 指定的字段必须是 “ 分组依据字段 ” ，其他字段若想出现在 SELECT 中则必须包含在聚合函数中。

 

select role,max(salary),min(salary),avg(salary) from emp group by role;
select role,max(salary),min(salary),avg(salary) from emp group by role 
having avg(salary)<1500;

3.1.3HAVING 

GROUP BY 子句进行分组以后，需要对分组结果再进行条件过滤时，不能使用 WHERE 语句，而需要用 HAVING

 

select role,max(salary),min(salary),avg(salary) from emp group by role 
having avg(salary)<1500;

 3.2联合查询

多表查询是对多张表的数据取笛卡尔积：

3.2.1内连接

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

select sco.score from student stu inner join score sco on stu.id=sco.student_id
and stu.name='许仙';
-- 或者
select sco.score from student stu, score sco where stu.id=sco.student_id and
stu.name='许仙';

3.2.2外连接

外连接分为左外连接和右外连接。如果联合查询，左侧的表完全显示我们就说是左外连接；右侧的表完全显示我们就说是右外连接。（对于左外连接来说，当左侧的表的行数并没有右侧的表多时，最后左外连接的行数仍然以左侧表为准。）

-- 左外连接，表1完全显示
select 字段名  from 表名1 left join 表名2 on 连接条件;
-- 右外连接，表2完全显示
select 字段 from 表名1 right join 表名2 on 连接条件;

 3.2.3自连接

自身与自身的连接

SELECT
 s1.* 
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;

3.2.4子查询

嵌套在查询里的查询

 1.(NOT)IN关键字

-- 使用IN
select * from score where course_id in (select id from course where
name='语文' or name='英文');
-- 使用 NOT IN
select * from score where course_id not in (select id from course where
name!='语文' and name!='英文');
比

2.（NOT）EXISTS关键字

-- 使用 EXISTS
select * from score sco where exists (select sco.id from course cou 
where (name='语文' or name='英文') and cou.id = sco.course_id);
-- 使用 NOT EXISTS
select * from score sco where not exists (select sco.id from course cou 
where (name!='语文' and name!='英文') and cou.id = sco.course_id);

3.2.5合并查询

在实际应用中，为了合并多个 select 的执行结果，可以使用集合操作符 union ， union all 。使用 UNION 和UNION ALL 时，前后查询的结果集中，字段需要一致。

-- union --

该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行。

 

select * from course where id<3
union
select * from course where name='英文';
-- 或者使用or来实现
select * from course where id<3 or name='英文';

 -- union all --

该操作符用于取得两个结果集的并集。当使用该操作符时，不会去掉结果集中的重复行.

-- 可以看到结果集中出现重复数据Java
select * from course where id<3
union all
select * from course where name='英文';