操作数据库
语句分类
-
DDL 定义库、表
- create 、drop、alter
-
DML 更新数据
- insert 、update、delete
-
DQL 查询
- select
-
DCL 控制
- grant、deny、revoke
-
TCL 事务
- commit、rollback
DDL示例
创建数据库 : create database [if not exists] 数据库名;
删除数据库 : drop database [if exists] 数据库名;
查看数据库 : show databases;
使用数据库 : use 数据库名;
创建表
create table [if not exists] `表名`(
'字段名1' 列类型 [属性][索引][注释],
'字段名2' 列类型 [属性][索引][注释],
#...
'字段名n' 列类型 [属性][索引][注释]
)[表类型][表字符集][注释];
说明 : 反引号用于区别MySQL保留字与普通字符而引入的 (键盘esc下面的键)
DML示例
通过DML语句管理数据库数据
添加数据
INSERT INTO 表名[(字段1,字段2,字段3,...)] VALUES('值1','值2','值3',...)
注意 :
- 字段或值之间用英文逗号隔开
- ' 字段1,字段2...' 该部分可省略 , 但添加的值务必与表结构,数据列,顺序相对应,且数量一致 .
- 可同时插入多条数据 , values 后用英文逗号隔开 .
修改数据
UPDATE 表名 SET column_name=value [,column_name2=value2,...] [WHERE condition];
注意 :
- column_name 为要更改的数据列
- value 为修改后的数据 , 可以为变量 , 具体指 , 表达式或者嵌套的SELECT结果
- condition 为筛选条件 , 如不指定则修改该表的所有列数据
删除数据
DELETE FROM 表名 [WHERE condition];
以上方法不重置自增计数器
TRUNCATE [TABLE] 表名;
truncate截断，相当于删表重建，自增计数器重置。
DQL查询
语法：
SELECT [ALL | DISTINCT]
{* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,...]]}
FROM table_name [as table_alias]
[left | right | inner join table_name2] -- 联合查询
[WHERE ...] -- 指定结果需满足的条件
[GROUP BY ...] -- 指定结果按照哪几个字段来分组
[HAVING] -- 过滤分组的记录必须满足的次要条件
[ORDER BY ...] -- 指定查询记录按一个或多个条件排序
[LIMIT {[offset,]row_count | row_countOFFSET offset}];
-- 指定查询的记录从哪条至哪条
说明:
-
distinct 去重
-
as 语句可作别名
- 用于数据列、表名、计算结果
指定查询字段
-- 查询所有学生信息
SELECT * FROM student;
-- 查询指定列(学号 , 姓名)
SELECT studentno,studentname FROM student;
where条件语句
作用：用于检索数据表中 符合条件 的记录
搜索条件可由一个或多个逻辑表达式组成 , 结果一般为真或假.
-- 查询考试成绩在95-100之间的
SELECT Studentno,StudentResult
FROM result
WHERE StudentResult>=95 AND StudentResult<=100;
-- AND也可以写成 &&
模糊查询
包含between and、like、in、null的查询
-- 查询姓刘的同学的学号及姓名
SELECT studentno,studentname FROM student
WHERE studentname LIKE '刘%';
-- 查询姓名中含有 嘉 字的
SELECT studentno,studentname FROM student
WHERE studentname LIKE '%嘉%';
-- 查询学号为1000,1001,1002的学生姓名
SELECT studentno,studentname FROM student
WHERE studentno IN (1000,1001,1002);
-- 查询出生日期没有填写的同学
-- 不能直接写=NULL , 这是代表错误的 , 用 is null
SELECT studentname FROM student
WHERE BornDate IS NULL;
-- 注意空字符串'' 和 null 不等价
连接查询
| 操作符名称 | 描述 |
|---|---|
| INNER JOIN | 如果表中有至少一个匹配，则返回行 |
| LEFT JOIN | 即使右表中没有匹配，也从左表中返回所有的行 |
| RIGHT JOIN | 即使左表中没有匹配，也从右表中返回所有的行 |
左连接，保留左表所有数据；右连接，保留右表所有数据。
3模式扩展7模式
-- 内连接
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
-- 等值连接
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s , result r
WHERE r.studentno = s.studentno
-- 查一下缺考的同学(左连接应用场景)
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
LEFT JOIN result r
ON r.studentno = s.studentno
WHERE StudentResult IS NULL
-- 左连接保留所有学生信息，筛选成绩为空，即为缺考的同学
-- 自连接，数据表与自身进行连接
INSERT INTO `category` (`categoryid`, `pid`, `categoryName`)
VALUES('2','1','信息技术'),
('3','1','软件开发'),
('4','3','数据库'),
('5','1','美术设计'),
('6','3','web开发'),
('7','5','ps技术'),
('8','2','办公信息');
SELECT a.categoryName AS '父栏目',b.categoryName AS '子栏目'
FROM category AS a,category AS b
WHERE a.`categoryid`=b.`pid`
子查询
套娃：在查询语句中的WHERE条件子句中,又嵌套了另一个查询语句，
子查询返回的结果一般都是集合,故而建议使用IN关键字;
-- 查询课程为 高等数学-2 且分数不小于80分的学生的学号和姓名
SELECT studentno,studentname FROM student WHERE studentno IN(
SELECT studentno FROM result WHERE StudentResult>=80 AND subjectno=(
SELECT subjectno FROM `subject` WHERE subjectname = '高等数学-2'
)
)
最难的就是相关子查询！现阶段并未讲解
排序分页
查询得到结果集后，还可以进行一些操作。
ORDER BY 语句
- 用于根据指定的列对结果集进行排序
- 默认按照ASC升序对记录进行排序
- DESC 关键字降序
-- 查询 数据库结构-1 的所有考试结果(学号 学生姓名 科目名称 成绩)
-- 按成绩降序排序
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname='数据库结构-1'
ORDER BY StudentResult DESC
LIMIT 语句
SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset
-
用于强制 SELECT 语句返回指定的记录数
-
offset指定第一个返回记录行的偏移量，不输入则默认为0
- LIMIT n 等价于 LIMIT 0,n。
-
rows 指定返回记录行的最大数目
-- 查询 JAVA第一学年 课程成绩前10名的学生信息(学号,姓名,课程名,分数)
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname='JAVA第一学年'
ORDER BY StudentResult DESC
LIMIT 0,10 -- LIMIT 10
DCL实例
权限控制
授权
GRANT 权限列表 ON 表名 TO 用户名
撤销
REVOKE 权限列表 ON 表名 FROM 用户名
用户管理
/* 用户和权限管理 */ ------------------
用户信息表：mysql.user
-- 刷新权限
FLUSH PRIVILEGES
-- 增加用户 CREATE USER kuangshen IDENTIFIED BY '123456'
CREATE USER 用户名 IDENTIFIED BY [PASSWORD] 密码(字符串)
- 必须拥有mysql数据库的全局CREATE USER权限，或拥有INSERT权限。
- 只能创建用户，不能赋予权限。
- 用户名，注意引号：如 'user_name'@'192.168.1.1'
- 密码也需引号，纯数字密码也要加引号
- 要在纯文本中指定密码，需忽略PASSWORD关键词。要把密码指定为由PASSWORD()函数返回的
混编值，需包含关键字PASSWORD
-- 重命名用户 RENAME USER kuangshen TO kuangshen2
RENAME USER old_user TO new_user
-- 设置密码
SET PASSWORD = PASSWORD('密码') -- 为当前用户设置密码
SET PASSWORD FOR 用户名 = PASSWORD('密码') -- 为指定用户设置密码
-- 删除用户 DROP USER kuangshen2
DROP USER 用户名
-- 分配权限/添加用户
GRANT 权限列表 ON 表名 TO 用户名 [IDENTIFIED BY [PASSWORD] 'password']
- all privileges 表示所有权限
- *.* 表示所有库的所有表
- 库名.表名 表示某库下面的某表
-- 查看权限 SHOW GRANTS FOR root@localhost;
SHOW GRANTS FOR 用户名
-- 查看当前用户权限
SHOW GRANTS; 或 SHOW GRANTS FOR CURRENT_USER; 或 SHOW GRANTS FOR
CURRENT_USER();
-- 撤消权限
REVOKE 权限列表 ON 表名 FROM 用户名
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 用户名 -- 撤销所有权限
TCL 事务
什么是事务
- 事务就是将一组SQL语句放在同一批次内去执行
- 如果一个SQL语句出错,则该批次内的所有SQL都将被取消执行
- MySQL事务处理只支持InnoDB和BDB数据表类型
遵循ACID原则
-
原子性(Atomic)
- 要么全部完成，要么全部不完成
-
一致性(Consist)
- 最终一致性，总量守恒
-
持久性(Durable)
- 未提交，回滚
- 已提交，保存到数据库
- 一旦提交就不可逆！
-
隔离性(Isolated)
-
多个用户并发访问数据库时，为每个用户开启事务，不能被其他事务干扰的事务之间要互相隔离
-
隔离级别
- 脏读：一个事务读取另一个事务未提交的数据
- 不可重复读：一次事务内，同一个数据，多次读取结果不一致
- 虚读：在一个事务内，读取到另个事务插入的数据
-
事务实现
-- 使用set语句来改变自动提交模式
SET autocommit = 0; /*关闭*/
SET autocommit = 1; /*开启*/
-- 注意:
--- 1.MySQL中默认是自动提交
--- 2.使用事务时应先关闭自动提交
-- 开始一个事务,标记事务的起始点
START TRANSACTION
-- 提交一个事务给数据库
COMMIT
-- 将事务回滚,数据回到本次事务的初始状态
ROLLBACK
-- 还原MySQL数据库的自动提交
SET autocommit =1;
-- 保存点
SAVEPOINT 保存点名称 -- 设置一个事务保存点
ROLLBACK TO SAVEPOINT 保存点名称 -- 回滚到保存点
RELEASE SAVEPOINT 保存点名称 -- 删除保存点