目录
一、初阶
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 为降序(从大到小)-- 默认为 ASCNULL 数据排序,视为比任何值都小,升序出现在最上面,降序出现在最下面
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='英文';