Mysql

SQL通用语法

1.SQL语句可以单行或多行书写, 以分号结尾
2.MySQL数据库的SQL语句不区分大小写, 关键字建议使用大写
3.SQL语句的注释:

-- 注释1 记得要加一个空格才是注释

/*
注释2 这是MySQL特有的注释 
别的数据库不能这么操作
*/

SQL分类

DDL 数据定义语言, 用来定义数据库对象:数据库, 表, 列等
DML 数据管理语言, 用来对数据库中表的数据进行增删查
DQL 数据查询语言, 用来查询数据库中表的数据
DCL 数据控制语言, 用来定义数据库的访问权限和安全级别, 及创建用户

DDL操作数据库

1.查询
SHOW DATABASES;
2.创建
CREATE DATABASE 数据库名称;
判断是否存在, 如果不存在在创建:
CREATE DATABASE IF NOT EXISTS 数据库名称;
3.删除
DROP DATABASE 数据库名称;
判断是否存在, 如果存在再删除
DROP DATABASE IF EXISTS 数据库名称;
4.使用数据库
查看当前使用的数据库
SELECT DATABASE();
使用数据库
USE 数据库名称;

DDL操作表

查询表:
1.查询当前数据库下所有表名称
SHOW TABLES;
2.查询表结构
DESC 表名;

创建表:

CREATE TABLE 表名 (
            字段名1 数据类型1,
            字段名2 数据类型2,
            ...
            字段名n 数据类型n
);
-- 注意:最后一行末尾不能加逗号!!

删除表:
1.简单删除表
DROP TABLE 表名;
2.删除前判断表是否存在
DROP TABLE IF EXISTS 表名;

修改表:
1.修改表名:
ALTER TABLE 原表名 RENAME TO 新表名;
2.添加一列:
ALTER TABLE 表名 ADD 列名 数据类型;
3.修改数据类型:
ALTER TABLE 表名 MODIFY 列名 新数据类型;
4.修改列名和数据类型:
ALTER TABLE 表名 CHANGE 原列名 新列名 新数据类型;
5.删除列:
ALTER TABLE 表名 DROP 列名;

数据类型

DML操作表中数据

添加数据:
1.给指定列添加数据:
INSERT INTO 表名(列名1, 列名2, ...) VALUES(值1, 值2, ...);
2.给全部列添加数据:
INSERT INTO shit VALUES(值1, 值2, 值3);
3.批量添加数据:
INSERT INTO 表名(列名1, 列名2) VALUES(值1, 值2), (值1, 值2), (值1, 值2);
INSERT INTO 表名 VALUES(值1, 值2, 值3), (值1, 值2, 值3), (值1, 值2, 值3);

删除数据
1.删除数据: DELETE FROM 表名 WHERE 条件;
删除语句中如果不加条件会把所有语句都删掉!!

DQL查询表中数据

1.查询语法:

基础查询
SELECT id AS 账号,name AS 姓名 FROM shit;
去除重复记录:DISTINCT SELECT DISTINCT gender from shit;

条件查询
条件大全:

SELECT * FROM stu WHERE age = 18 OR age = 20 OR age = 22;
-- 可以化简成下面:
SELECT * FROM stu WHERE age in (18, 20, 22);

模糊查询
用LIKE代替where条件中的=
通配符: _ 代表任意单个字符, % 代表任意多个字符(可以是0个)

排序查询

SELECT * FROM stu ORDER BY math;

排序方式: ASC升序排列(默认值) DESC降序排列
多字段排序:

SELECT * from stu ORDER BY math DESC, english ASC;

聚合函数
将一列数据作为一个整体, 进行纵向计算

null值不参与所有聚合函数运算
聚合函数语法: SELECT 聚合函数名(列名) FROM表

分组查询
分组查询语法:

SELECT sex, AVG(math), COUNT(*) FROM stu
WHERE math > 70 - where用来在分组前筛选参与分组的元素
GROUP BY sex
HAVING COUNT(*) > 2; - having用来在分组后对结果进行过滤
-- 执行顺序: where > 聚合函数 >having

分组之后, 查询的字段必须是聚合函数和参与分组的字段,查询其他字段毫无意义
where 和 having 区别:
执行时机不一样:where是分组之前进行限定，不满足where条件，则不参与分组，而having是分组之后对结果进行过滤。
可判断的条件不一样:where不能对聚合函数进行判断，having可以。

分页查询

SELECT * FROM stu
LIMIT 6, 3;
-- LIMIT 起始索引, 查询条目数(起始索引从0开始)

计算公式: 起始索引 = (当前页码 - 1) * 每页显示的条数
tips:分页查询limit是MySQL的方言, oracle用的是rownumber, sql sever用的是top

约束

约束是作用于表中列上的规则, 用于限制加入表中的数据
约束的存在保证了数据库的数据的正确性, 有效性和完整性

约束的分类

tips:MySQL没有检查约束

-- 建表时添加约束
CREATE TABLE orders (
    order_id INT PRIMARY KEY,  -- 主键约束
    order_date DATE NOT NULL DEFAULT CURRENT_DATE,  -- 默认值约束
);

建完表添加非空约束:
ALTER TABLE 表名 MODIFY 字段名 数据类型 NOT NULL;
删除约束:
ALTER TABLE 表名 MODIFY 字段名 数据类型

外键约束

-- 创建外键约束
CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50) NOT NULL
);

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(50) NOT NULL,
    department_id INT,
    CONSTRAINT fk_emp_dep FOREIGN KEY (department_id) REFERENCES departments(department_id)-- 最好给约束起个名字 因为删除外键约束需要这个名字
);

-- 建完表添加外键
ALTER TABLE employees
ADD CONSTRAINT fk_emp_dep
FOREIGN KEY (department_id) REFERENCES departments(department_id);

-- 建完表删除外键
ALTER TABLE employees
DROP FOREIGN KEY fk_emp_dep;

数据库设计:

表关系之一对多
如:部门表 和 员工表
一个部门对应多个员工, 一个员工对应一个部门

实现方式: 在多的一方建立外键, 指向一的一方的主键

表关系之多对多
如:订单 和 商品
一个商品对应多个订单, 一个订单包含多个商品

实现方式: 建立第三张表, 中间表至少包含两个外键, 分别关联两方主键

表关系之一对一
如: 用户和用户详情
一对一关系多用于表拆分, 将一个实体中经常使用的字段放一张表, 不经常使用的字段放另一张表, 用于提升查询性能
实现方式: 在任意一方加入另一方主键作为外键, 并设置外键为唯一(UNIQUE)

多表查询

隐式内连接

-- 隐式内连接
SELECT emp.name, emp.gender, dept.dname
FROM emp, dept
WHERE emp.dep_id = dept.did;

显式内连接

-- 显式内连接
SELECT emp.name, emp.gender, dept.dname
FROM emp
JOIN dept ON emp.dep_id = dept.did;

左外连接

-- 左外连接
SELECT emp.name, emp.gender, dept.dname
FROM emp
LEFT JOIN dept ON emp.dep_id = dept.did;

右外连接

-- 右外连接
SELECT emp.name, emp.gender, dept.dname
FROM emp
RIGHT JOIN dept ON emp.dep_id = dept.did;

一般都用左外连接 因为左外连接调转连接顺序可以达到和右外连接一样的效果

-- 一般用左外查询 因为左外查询可以直接转换成和右外查询一样的效果
SELECT emp.name, emp.gender, dept.dname
FROM dept
LEFT JOIN emp ON emp.dep_id = dept.did;

子查询

单行单列的子查询

SELECT AVG(成绩) FROM 成绩表 WHERE 学号 = (SELECT 学号 FROM 学生表 WHERE 姓名 = '张三');

多行单列的子查询

-- 多行单列的子查询
SELECT * 
FROM emp
WHERE dep_id in (
SELECT did
FROM dept
WHERE dname = '财务部' OR dname = '市场部'
);

多行多列的子查询 (当虚表用)

--多行多列的子查询(当虚表用)
SELECT *
FROM (SELECT *
FROM emp
WHERE join_date > '2011-11-11'
) t1
JOIN dept ON t1.dep_id = dept.did;

事务

数据库的事务(Transaction)是一种机制、一个操作序列，包含了一组数据库操作命令
事务把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么同时成功，要么同时失败
事务是一个不可分割的工作逻辑单元
事务的操作

-- 开启事务
START TRANSACTTION;
-- 或者
BEGIN;

-- 提交事务
COMMIT;

-- 回滚事务
ROLLBACK;

MySQL里没手动开启事务的话会自动提交事务

-- 查看事务的默认提交方式
SELECT @@autocommit

-- 1自动提交 0手动提交

-- 修改事务提交方式
set @@autocommit = 0;

事务的四大特征
原子性(Atomicity):事务是不可分割的最小操作单位，要么同时成功，要么同时失败
一致性(Consistency):事务完成时，必须使所有的数据都保持一致状态
隔离性(lsolation):多个事务之间，操作的可见性
持久性(Durability):事务一旦提交或回滚，它对数据库中的数据的改变就是永久的