目录
一、SQL语言
1 数据库定义语言 DDL
CREATE
create database user;
ALTER
alter database
userdefault character set utf8 collate utf8_general_ci;
DROP
drop database user;
2 数据库操作语言 DML
INSERT
指定字段添加数据
INSERT INTO 表 名( 字段 名 1, 字段 名 2,…) VALUES( 值 1, 值 2,…);
不指定字段名
INSERT INTO 表 名 VALUES( 值 1, 值 2,…);
同时插入多条数据,逗号隔开
INSERT INTO 表 名[( 字段 名 1, 字段 名 2,…) ] VALUES( 值 1, 值 2,…),( 值 1, 值 2,…), … (值 1, 值 2,…);
UPDATE
UPDATE 表 名 SET 字段 名 1= 值 1[, 字段 名 2 = 值 2,…] [WHERE 条件 表达式]
DELETE
DELETE FROM 表 名 [WHERE 条件 表达式]
清除整个表
TRUNCATE [TABLE] 表 名
3 数据库查询语言 DQL - SELECT
简单查询
查询所有字段
SELECT * FROM 表 名;
查询指定字段
SELECT 字段 名 1, 字段 名 2,… FROM 表 名;
按条件查询
WHERE 条件 表达式
带关系运算符的的查询
带 IN 关键字
WHERE id NOT IN( 1, 2, 3);
带between…and 关键字 :大于等于 小于等于
带 null 关键字:空值查询(not)null
带 distinct 关键字: 返回唯一的不同值
带LIKE 关键字 :模糊查询
带 通配符 % 或 _
% 用于匹配多个字符;
_ 用于匹配单个字符;
要匹配 % 或 _ 用 "\" 来进行转义;
带AND/OR 关键字的多条件查询
where 表达式1 and 表达式2
AND 的 优先级 高于 OR
高级查询
聚合查询
COUNT(*)统计记录数 SELECT COUNT(*) FROM 表 名 S
SUM() 求和 SELECT SUM( 字段 名) FROM 表 名;
AVG() 函数 SELECT AVG( 字段 名) FROM student;
MAX() 最大值 SELECT MAX( grade) FROM student;
MIN() 最小值 SELECT MIN( grade) FROM student;
GROUP BY 和 HAVING 分组查询
SELECT sum( grade), gender FROM student GROUP BY gender HAVING SUM( grade) < 300;
HAVING 关键字 和 WHERE 关键字 的 作用 相同, 都用 于 设置 条件 表达式 对 查询 结果 进行 过滤, 两者 的 区别 在于, HAVING 关键 字后 可以 跟 聚合 函数, 而 WHERE 关键字不能。
对查询的结果进行排序 —— ASC升序 | DESC降序
默认是升序排列 SELECT * FROM student ORDER BY gender ASC, grade DESC;
limit : 限制查询结果的数量
SELECT 字段 名 1, 字段 名 2,…FROM 表 名 LIMIT [OFFSET,] 记录 数
取别名 查询
SELECT * FROM 表 名 [AS] 别名;
SELECT 字段名 [AS] 别名, 字段名 [AS] 别名,… FROM 表 名;
函数 查询
SELECT if(total_price % 2 = 1,total_price, 0) FROM tb_order;
4 数据库控制语言 DCL
数据库控制语言 是用来设置或者更改数据库用户或角色权限的语句
如 GRANT、COMMIT、ROLLBACK
二、其他基本操作语法
查看
1 查看数据库
show databases;
2 查看某个数据库/表的创建信息
show create database users;
show create table tb_teacher;
3 查看表的字段信息
DESC 表名;
表的约束
1 主键
单字段主键
CREATE TABLE example01( id INT PRIMARY KEY, name VARCHAR( 20), grade FLOAT);
多字段主键
CREATE TABLE example02( stu_ id INT, course_ id INT, grade FLOAT, PRIMARY KEY( stu_ id, course_ id) );
非空约束:NOT NULL;
唯一约束:UNIQUE;
设置表中字段自动增加:AUTO_ INCREMENT;
2 索引
普通索引
CREATE TABLE t1( id INT, name VARCHAR( 20), score FLOAT,name VARCHAR( 20), score FLOAT, INDEX (id) );
唯一索引
CREATE TABLE t2( id INT NOT NULL, name VARCHAR( 20) NOT NULL, score FLOAT, UNIQUE INDEX unique_ id( id ASC) );
全文索引
CREATE TABLE t5( id INT NOT NULL, name VARCHAR( 20) NOT NULL,score FLOAT, INDEX multi( id, name( 20)) );
多表操作
操作关联表
1、多对一(员工、部门)外键
2、多对多(课程、学生)关系表
3、一对一(什么证号)一般情况,加一列就可以
外键
外键是指引用另一个表中 的 一列 或 多列, 被 引 用的 列 应该 具 有主 键 约束 或 唯一 性 约束。
例如:学生 表( student) 中的 gid 是 学生 所在 的 班级 id, 是 引入 了 班级 表( grade) 中的 主 键 id。 那么 gid 就可以 作为 表 student 的 外 键。 被 引 用的 表, 即 表 grade 是 主表; 引用 外 键 的 表, 即表 student 是 从 表, 两个 表 是 主从 关系。
注意:
引入外键后,外键列的值只能是参照列的值,并且参照列中的值不能被删除。
1 添加外键约束
alter table 表 名 add constraint FK_ ID foreign key( 外 键 字段 名) REFERENCES 外表 表 名( 主 键 字段 名);
执行完毕后,可以使用show create table
表名的方式来看。
create table 表名(
字段描述,
constraint fk_name foreign key (外键字段名) references 外键表表名(主键字段)
)
注意:
1、建立外键的表必须是InnoDB型。
2、定义外键名时不能加引号。
添加外键约束参数
alter table 表 名 add constraint FK_ ID foreign key( 外 键 字段 名) REFERENCES 外表 表 名( 主 键 字段 名) [ON UPDATE {CASCADE | SET NULL | NO ACTION | RESTRICT}]
2 删除外键约束
alter table 表 名 drop foreign key 外 键名;
连接查询
交叉连接返回的是两个表行数的乘积(笛卡儿积);
SELECT * FROM department CROSS JOIN employee;
内连接
SELECT 查询 字段 FROM 表 1 [INNER] JOIN 表 2 ON 表 1. 关系 字段 = 表 2. 关系 字段
外连接
SELECT 所 查字 段 FROM 表 1 LEFT| RIGHT [OUTER] JOIN 表 2 ON 表 1. 关系 字段= 表 2. 关系 字段 WHERE 条件
外连接返回 查询 结果 中 不仅 包含 符合 条件 的 数据, 而且 还包括 左 表( 左 连接 或 左 外 连接)、 右 表( 右 连接 或 右 外 连接) 或 两个 表( 全 外 连接) 中的 所有 数据。
右外连接:右边数据是全的
子查询
子 查询 是指 一个 查询 语句 嵌套 在另 一个 查询 语句 内部 的 查询。
5.4.1 带IN关键字的查询
SELECT * FROM department WHERE id IN( SELECT id FROM employee WHERE age= 20);
5.4.2 exist
SELECT * FROM department WHERE id IN( SELECT id FROM employee WHERE age= 20);
5.4.3 带 ANY 关键字 的 子 查询
SELECT * FROM department WHERE id > any( select id from employee);
5.4.4 带 ALL 关键字 的 子 查询
SELECT * FROM department WHERE id > all( select id from employee);
5.4.5 带 比较 运算符 的 子 查询
SELECT * FROM department WHERE id=( select id from employee where name=' 赵 四');