快速入门MySQL，实例分析SQL常用语句

命令提示符模式下进入数据库

mysql -u root -p

退出mysql

quit

操作数据库

# 查看所有数据库
SHOW DATABASES;

# 创建数据库
CREATE DATABASE temp;

# 查看创建数据库信息
SHOW CREATE DATABASE temp;

# 查看当前正在使用的数据库
SELECT DATABASE();

# 使用数据库
USE temp;

# 修改数据库
ALTER DATABASE temp CHARACTER SET UTF8;

# 删除数据库
DROP DATABASE temp;

创建表

CREATE TABLE person (
    pid INT PRIMARY KEY,
    pname VARCHAR(30) NOT NULL,
    page INT NOT NULL
);

增删改

# 插入数据
INSERT INTO `user`(id, `name`, age) VALUES(3, '老三', 18);

# 多行插入
INSERT INTO `user` (id , `name`, age)
VALUES(4, '老四', 17),(5, '老五', 16),(6, '老六', 15);

# 可以不带列
INSERT INTO `user` VALUES(7, '老七', 14);

# 修改数据
UPDATE `user` SET `name`='嘿嘿' WHERE id=5;

# 删除一行数据（如若不加WHERE条件，删除的是整张表的数据）
DELETE FROM `user` WHERE id = 7;

# 清空表
TRUNCATE TABLE `user`;

# 添加一列
ALTER TABLE subjects ADD num INT;

# 修改列类型
ALTER TABLE subjects MODIFY sname VARCHAR(10);

# 删除一列
ALTER TABLE subjects DROP num;

# 修改一列
ALTER TABLE subjects CHANGE shour shours VARCHAR(20);

# 修改表名
ALTER TABLE temp RENAME temp2; 

# 删除表
DROP TABLE temp2;

查询

# 查询所有数据库
show databases;

# 查询所有表
show tables;

基本查询

# 查询员工所有信息
SELECT * FROM t_employees;

# 查询员工表中所有员工的编号、名字、年薪
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY*12 FROM t_employees;

# 拼接字符串
SELECT CONCAT(EMPLOYEE_ID,'的姓名是',FIRST_NAME,' 年薪是',SALARY * 12) FROM t_employees ;

# 别名查询
SELECT 
  EMPLOYEE_ID AS '编号',
  FIRST_NAME AS '名字',
  SALARY * 12 AS '年薪' 
FROM
  t_employees ;

# 去重查询 distinct
SELECT DISTINCT SALARY FROM t_employees;

# 排序查询 默认升序asc，降序desc
SELECT EMPLOYEE_ID, SALARY FROM t_employees ORDER BY SALARY DESC;

# 条件查询
SELECT EMPLOYEE_ID,FIRST_NAME,SALARY FROM t_employees WHERE SALARY = 11000;

# 模糊查询
SELECT EMPLOYEE_ID,FIRST_NAME,SALARY FROM t_employees WHERE SALARY = 11000 AND FIRST_NAME LIKE '%en';

# 分组查询
SELECT DEPARTMENT_ID, SUM(SALARY) FROM t_employees GROUP BY DEPARTMENT_ID;

# 分组查询，限制条件
SELECT DEPARTMENT_ID, SUM(SALARY) FROM t_employees GROUP BY DEPARTMENT_ID HAVING SUM(SALARY) > 50000;

# 限制查询
SELECT FIRST_NAME, SALARY FROM t_employees ORDER BY SALARY LIMIT 0, 2;

子查询

当子查询结果集形式为多行单列时可以使用ANY或ALL关键字。

# 查询工资大于Bruce的员工信息:
SELECT FIRST_NAME, SALARY FROM t_employees
WHERE SALARY > (SELECT SALARY FROM t_employees WHERE FIRST_NAME = 'Bruce');

# 查询与名为'King'同一部门的员工信息:
SELECT EMPLOYEE_ID, FIRST_NAME, DEPARTMENT_ID FROM t_employees
WHERE DEPARTMENT_ID IN (SELECT DEPARTMENT_ID FROM t_employees WHERE LAST_NAME = 'King');

# 查询高于60部门所有人的工资的员工信息（高于所有）:
SELECT FIRST_NAME, SALARY FROM t_employees
WHERE SALARY > ALL(SELECT SALARY FROM t_employees WHERE DEPARTMENT_ID = 60);

连接查询

# 合并查询：合并结果的两张表，列数必须相同，列的数据类型可以不同
# 如果不加 ALL ，则会去除重复的
SELECT id, `name` FROM `user` UNION ALL SELECT pid, pname FROM person;

# 交叉查询（笛卡尔积）
# 传统方式(有重复)
SELECT * FROM t_employees, t_departments;
# 标准写法
SELECT * FROM t_employees CROSS JOIN t_departments;

# 内连接查询 MySQL传统方式 (求交集)
SELECT * FROM t_employees, t_jobs WHERE t_employees.`JOB_ID` = `t_jobs`.`JOB_ID`;
# 内连接查询 SQL标准方式 INNER JOIN
SELECT * FROM t_employees INNER JOIN t_jobs ON t_employees.`JOB_ID` = t_jobs.`JOB_ID`;

# 左外连接查询 LEFT JOIN 左表为主，向右匹配
SELECT * FROM t_employees LEFT OUTER JOIN t_jobs ON t_employees.`JOB_ID` = t_jobs.`JOB_ID`;
# 右外连接查询 RIGHT JOIN 右表为主，向左匹配
SELECT * FROM t_employees RIGHT OUTER JOIN t_jobs ON t_employees.`JOB_ID` = t_jobs.`JOB_ID`;

左外连接查询：先查询出左表所有的数据，然后右表对应的也查询出来，左表有的右表没有的列，就补齐为NULL，如果右表数据比左表数据多，则多出数据不显示，右外连接与此相反。

聚合函数

SELECT SUM(SALARY), AVG(SALARY), MAX(SALARY), MIN(SALARY), COUNT(SALARY) FROM t_employees;

时间函数

# 当前系统时间
SELECT SYSDATE();

# 当前时间
SELECT NOW();

# 当前日期
SELECT CURDATE();

# 当前时间
SELECT CURTIME();

# 获取当前日期是一年中的第几周
SELECT WEEK('2020-8-19');

# 获取指定日期的年份
SELECT YEAR('2020-8-19');

# 查询员工从入职到现在的天数
SELECT DATEDIFF(NOW(), HIRE_DATE) FROM t_employees;

约束条件

• NOT NULL（非空约束）
• PRIMARY KEY（主键约束）
• UNIQUE（唯一约束）
• DEFAULT（默认约束）
• FOREIGN（外键约束）
• CHECK（检查约束）使用方法：例：CHECK(列>‘0’);
• AUTO_INCREMENT（自动增长列）

外键的使用

• CONSTRAINT 引用名 FOREIGN KEY(列名）REFERENCES 被引用表名(列名)

事务

概念

• 事务是一个原子操作。是一个最小执行单元，由一个或多个SQL语句组成。
• 在同一个事务当中，所有的SQL语句都成功执行时，整个事务成功。
• 有一个SQL语句执行失败，整个事务都执行失败。

原理

• 数据库会为每一个客户端都维护一个空间独立的缓存区（回滚段）
• 一个事务中所有的增删改语句的执行结果都会缓存在回滚段中
• 当事务中所有SQL语句均正常结束(commit)，才会将回滚段中的数据同步到数据库
• 否则无论因为哪种原因失败，整个事务将回滚（rollback)

事务的特性

Atomicity(原子性)

• 表示一个事务内的所有操作是一个整体，要么全部成功，要么全部失败

Consistency(一致性)

• 表示一个事务内有一个操作失败时，所有的更改过的数据都必须回滚到修改前状

lsolation(隔离性)

• 事务查看数据操作时数据所处的状态，要么是另一并发事务修改它之前的状态
• 要么是另一事务修改它之后的状态，事务不会查看中间状态的数据

Durability(持久性)

• 持久性事务完成之后，它对于系统的影响是永久性的

事务的使用

# 开启事务
START TRANSACTION
# SETAUTOCOMMIT=0; 禁止自动提交，也就是开启事务

# 转账
UPDATE bank SET money = money - 100 WHERE id = 1;

UPDATE bank SET money = money + 100 WHERE id = 2;

# 回滚
ROLLBACK

# 提交事务
COMMIT

START TRANSACTION 和 SETAUTOCOMMIT=0 的区别

两者都是开启事务，但START TRANSACTION在提交事务或回滚事务之后，事务就关闭了，而 SETAUTOCOMMIT=0 不会关闭，事务会一直开启，直到执行SETAUTOCOMMIT=1

权限管理

# 创建用户
CREATE USER bao IDENTIFIED BY '125888'; 

# 授权
# 将bank这个表的权限授予bao这个用户
GRANT ALL ON study.`bank` TO bao;

# 撤销权限
REVOKE ALL ON study.`bank` FROM bao;

# 删除用户
DROP USER bao

视图

概念

• 视图即虚拟表，从一个表或多个表中查询出来的表，作用和真实表一样

作用

• 作用和真实表一样，包含一系列带有行和列的数据
• 视图中，用户可以使用SELECT语句查询数据，也可以使用DML操作记录
• 视图可以使用户操作方便，并保障数据库系统安全

优点

• 简单化，数据所见即所得
• 安全性，用户只能查询或修改他们所能见到得到的数据

缺点

• 性能相对较差，简单的查询也会变得稍显复杂
• 修改不方便，特变是复杂的聚合视图基本无法修改

使用

# 创建视图
CREATE VIEW view_bank AS SELECT `name`, money FROM bank;

# 执行视图
SELECT * FROM view_bank;

# 修改视图
ALTER VIEW view_bank AS SELECT id, `name`, money FROM bank;

# 删除视图
DROP VIEW view_bank;

视图的删除不会影响原表