mysql 基础学习笔记

不才 等级 394 0 0

安装

MacOS

Windows 10

Centos 7

SQL规范

  1. 不区分大小写,但是建议大写关键词,小写表名列名
  2. 每条SQL建议分号结尾
  3. 每条SQL根据需要进行换行缩进
  4. 注释: 单行:# -- 多行:/* */

类型

数值:
    整型:
    小数:
        定点数
        浮点数
字符:
    短字符: char varcahr 
    长文本: text blob
日期:
    date        2020-02-03
    datetime    2020-02-02 02:02:02
    timesiamp   1594279093389
    time        02:02:02
    year        2020

常用SQL

use test; -- 选中 数据库
show tables; -- 现实当前选中的库的所有表

show tables from mysql; # 查询mysql下的tables

SHOW INDEX FROM stuinfo; # 显示当前的索引

select database(); # 查看当前库

/* create table table1(
 id int,
 name varchar(24)
); */

desc table1; -- 查看表结构

select * from table1;

insert into table1 (id,name) values(1,'测试'); -- 插入

update table1 set name='我靠' where name='ces'; -- 修改

update table1 set id=0 where name='我靠'; -- 修改

delete from table1 where name='我靠'; -- 删除


常见函数

单行函数

处理

字符函数

SELECT LENGTH('我是谁'); -- 根据当前字符集 得到当前字节长度

SELECT CONCAT('我','是','谁呀'); -- 拼接字符串

SELECT UPPER('Abc'); -- 转换成大写字符

SELECT LOWER('Abc'); -- 转换成小写

SELECT SUBSTR('abc123一二三',4,3); -- 从4开始截取3个 包含4 索引从1开始

SELECT SUBSTRING('abc123一二三',4,3); -- 从4开始截取3个 包含4 索引从1开始

SELECT INSTR('01234556','234'); -- 查找字符串出现的位置 没找到就是0

SELECT TRIM(' A B C D '); -- 去除前后空格

SELECT TRIM('a' FROM 'aaaaA B  CaaaDaaaa' ); -- 去除前后的a

SELECT LPAD('abc123一二三',20,'*'); -- 左填充/保留左边的
SELECT RPAD('abc123一二三',20,'*'); -- 右填充/保留左边的

数学函数

SELECT ROUND(0.4); -- 四舍五入
SELECT ROUND(0.5);  -- 四舍五入
SELECT ROUND(-0.4);  -- 四舍五入
SELECT ROUND(-0.5);  -- 四舍五入

SELECT CEIL(0.2); -- 向上取整
SELECT FLOOR(0.9); -- 向下取整

SELECT RAND(); -- 随机数

SELECT TRUNCATE(0.2345,3); -- 保留多少位小数 不进行处理

SELECT MOD(10,3); -- 取余

日期函数

SELECT NOW(); -- 返回当前的日期时间

SELECT CURDATE(); -- 返回当前的日期

SELECT CURTIME(); -- 返回当前时间

SELECT YEAR(NOW()) as `year`, MONTH(NOW()) as `month`, DAY(NOW()) as date as `day`; -- 年/月/日

SELECT STR_TO_DATE('2020-03-23 22:32:12','%Y-%m-%d %H:%i:%s'); -- 将字符串解析成时间

SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s'); -- 格式化时间

其他函数

SELECT VERSION(); -- 查看版本号

SELECT DATABASE(); -- 查看当前的库
 
SELECT USER(); -- 当前用户

流程控制函数

SELECT IF(10<5,'大','小'); -- if

SELECT `last_name`, IF(`commission_pct` IS NULL,TRUE,FALSE) AS isPct from `employees` ORDER BY `isPct` DESC; -- if 例子

# case 
SELECT `salary`,`department_id`,
CASE department_id
 WHEN 80 THEN
  salary * 1.2
  WHEN 40 THEN
  salary * 1.9
 ELSE
  salary * 0
END AS newMoney 
FROM `employees`
ORDER BY department_id DESC;

统计函数

统计

SELECT COUNT(*) FROM `employees`; -- 数量统计
SELECT SUM(`salary`) FROM `employees`; -- 相加和
SELECT AVG(`salary`) FROM `employees`; -- 平均值
SELECT MAX(`salary`) FROM `employees`; -- 最大值
SELECT MIN(`salary`) FROM `employees`; -- 最小值

SELECT COUNT(*) AS `count`, SUM(`salary`) AS `sum`, AVG(`salary`) AS `avg`, MAX(`salary`) as `max`, MIN(`salary`) as `min`
FROM `employees`;

# 注意
/*

*/

常见约束

一种限制,用于限制表中的数据,用来保证表中数据的准确和可靠性


分类:
    六大约束:
        NOT NULL: 非空,用于保证该字段的值不能为空
        DEFAULT: 默认值
        PRIMARY KEY: 主键,用于保证该字段具有唯一性(非空)
        UNIQUE: 唯一(可空)
        CHECK: 检查 (mysql 不支持)
        FOREIGN KEY: 外键,用于限制两个表的关系,用于保证该字段必须来自关联表的主键
    添加约束的时机:
        1. 创建表
        2. 修改表
    约束的添加分类:
        列级约束:
            六大约束语法上都支持,外键约束无效
        表级约束:
            除了非空和默认其他都支持

主键和唯一的区别: 主键:唯一、非空、只能一个 唯一:唯一、可空、多个

外键: 1. 从表设置外键关系 2. 主从表类型一致/兼容 3. 主表关联键一般为主键或唯一 4. 必须对应主表数据,删除先删除从表再删除主表

DQL 数据查询语言

常量、表达式、函数

SELECT 1; -- 常量值
SELECT 10*20; -- 表达式
SELECT VERSION(); -- 函数

别名

SELECT 1+2 as number;

去重

SELECT DISTINCT 
    `name`
FROM 
    `table`

+号

SELECT 1+2; -- 数字相加
SELECT 1+'123'; -- 字符串会强转成数字非数字转为0
SELECT 1 + Null; -- 与Null返回Null

字符串连接 concat

SELECT CONCAT('a','b','c'); -- 字符串拼接
SELECT CONCAT(`first_name`,`last_name`) as `name` FROM `employees`; -- 拼接字段

条件查询

条件表达式

< > >= <= != <> <=>

# 等于
SELECT 
    CONCAT(`first_name`,`last_name`) as `name` 
FROM `employees` 
WHERE 
    `first_name`='Bruce'; 

# 安全等于 可查 Null

SELECT 
    CONCAT(`first_name`,`last_name`) as `name` 
FROM `employees` 
WHERE 
    `first_name`<=>'Bruce'; 

# 大于
SELECT 
 *
FROM `employees` 
WHERE 
 `department_id` > 60;

# 小于
SELECT 
 *
FROM `employees` 
WHERE 
 `department_id` <= 60;
 
# 不等于 
# != 不建议
SELECT 
 *
FROM `employees` 
WHERE 
 `department_id` <> 60;
逻辑表达式

&& || !
AND OR NOT

# 且查询
# 不建议 &&
SELECT 
CONCAT(`first_name`,`last_name`) as `name`
FROM `employees` 
WHERE 
    `first_name`='Bruce' 
    AND 
    `last_name`='Ernst';

# 或
SELECT 
 CONCAT(`first_name`,`last_name`) as `name` 
FROM `employees` 
WHERE 
 `first_name`='Bruce' 
 OR 
 `last_name`='K_ing';
 
# 非
SELECT 
 CONCAT(`first_name`,`last_name`) as `name` 
FROM `employees` 
WHERE 
    NOT
 `first_name`='Bruce' 
模糊查询
like 模糊查询

%:任意多个字符 _: 任意单个字符 \: 转义

# 包含 a
SELECT * FROM `employees` 
WHERE 
    `first_name` like '%a%'; 

# 定义转义字符
SELECT * FROM `employees` 
WHERE 
    `last_name` like '%$_%' ESCAPE '
between and 范围

在什么到什么之间,包含前后

# 查找100 到200 之间的数据
SELECT * FROM `employees` 
WHERE 
    `employee_id` BETWEEN 100 AND 200;
in 属于

查询是否属于某些列表中的某一个

# 查询是否属于某些列表中的某一个
SELECT * FROM `employees` 
WHERE 
    `job_id` IN ('SH_CLERK','AD_ASST','AD_VP');
is null or is not null 是否为Null
# 查询字段为空的
SELECT * FROM `employees`
WHERE
    `commission_pct` IS NULL;

# 查询不为空的
SELECT * FROM `employees`
WHERE
    NOT `commission_pct` IS NULL;

SELECT * FROM `employees`
WHERE
    `commission_pct` IS NOT NULL;
<=> 安全等于

既可以判断NULL 又可以判断数值

SELECT * FROM `employees`
WHERE
    `commission_pct` <=> NULL;
order by 排序

ASC升序 DESC降序 默认 ASC

SELECT * FROM `employees` ORDER BY `salary` ASC; -- 升序
SELECT * FROM `employees` ORDER BY `salary` DESC; -- 降序

SELECT `salary` * IFNULL(`commission_pct`,0) + IFNULL(manager_id,0) as money,`salary`  FROM `employees` ORDER BY `money`; -- 表达式别名降序

SELECT LENGTH(`last_name`) as len FROM `employees` ORDER BY len; -- 按函数

SELECT * FROM `employees` ORDER BY `salary` DESC, `employee_id` ASC; -- 多个排序条件

分组查询


# 每个工种的总工资
SELECT SUM(`salary`) AS `money`,`job_id` FROM `employees` GROUP BY `job_id` ORDER BY `money`;

# 每个工种的最高工资
SELECT MAX(`salary`) as `max`,`job_id` FROM `employees` GROUP BY `job_id` ORDER BY `max`;

# 查询邮箱包含a的工种的最高工资
SELECT MAX(`salary`) as `max`, `job_id`
FROM `employees`
WHERE email LIKE '%a%' 
GROUP BY `job_id` 
ORDER BY `max`;

# 查询邮箱包含a的工种的最高工资大于1万的   HAVING
SELECT MAX(`salary`) as `max`, `job_id` 
FROM `employees`
WHERE email LIKE '%a%' 
GROUP BY `job_id`
HAVING `max` > 10000
ORDER BY `max`;

# 查询名称长度一样的大于5个人的数据
SELECT LENGTH(`first_name`) AS `name`, COUNT(1) AS `count`
FROM `employees`
GROUP BY `name`
HAVING `count` > 5;

# 多字段分组
SELECT AVG(`salary`) AS `avg`,`department_id`,`job_id`
FROM `employees`
GROUP BY `department_id`,`job_id`
ORDER BY `department_id`,`job_id`

连接查询

分类:
    按年代划分:
        sql92标准:只支持内连接
        sql99标准【推荐】:支持内连接+外来连接(左外、右外)+交叉连接
    按功能划分:
        内连接:
            等值连接
            非等值连接
            自连接
        外连接:
            左外连接
            右外连接
            全外连接
        交叉连接

sql92标准

# 等值连接
SELECT e.`first_name`,j.`job_title`,`j`.`job_id`
FROM `employees` as e,`jobs` as j
WHERE `e`.`job_id` = `j`.`job_id`;

# 非等值连接

# 自连接
SELECT e.last_name,m.employee_id,e.manager_id,m.last_name 
FROM `employees` AS e, `employees` AS m
WHERE `e`.employee_id = `m`.manager_id

sql99标准

# 等值连接
SELECT `last_name`,`department_name`
FROM employees
INNER JOIN departments
ON employees.`department_id` = departments.`department_id`

# 复杂的等值连接
SELECT department_name,COUNT('*') AS count,MAX(`salary`) AS max,min(`salary`) AS min
FROM employees
INNER JOIN departments
ON employees.department_id = departments.department_id
WHERE last_name LIKE '%o%'
GROUP BY department_name
HAVING `count` BETWEEN 2 AND 10
ORDER BY `count`

# 多表等值连接
SELECT last_name,department_name,job_title
FROM employees
INNER JOIN departments
ON departments.department_id =employees.department_id
INNER JOIN jobs
ON employees.job_id = jobs.job_id;

子查询

分类:
    按出现位置:
        select 后面:
            只支持标量子查询
        form 后面
            表子查询
        where 和having后面 *
            标量子查询  *
            列子查询    *
            行子查询
        exists后面
            表子查询

按结果集的行列数不同:
    标量子查询(结果集只有一行一列)
    列子查询(结果集只有一列多行)
    行子查询(结果集只有多列一行)
    表子查询(结果集一般多行多列)

where 和having后面

特点: 1. 放在小括号内 2. 放在条件右侧 3. 标量子查询:配合单行操作符。列子查询:配合多行操作符。


# 查询工资比Abel高的人
SELECT * FROM employees 
WHERE salary > (
 SELECT salary FROM employees 
 WHERE last_name = 'Abel'
);

# job_id与141号员工一样且工资大于143号员工的员工
SELECT last_name,salary,employee_id FROM employees
WHERE job_id = (
 SELECT job_id FROM employees WHERE employee_id = 141
) AND salary > (
 SELECT salary FROM employees WHERE employee_id = 143
)

# 查询比50号部门最低工资高的部门的最低工资
SELECT department_id, COUNT(*),MIN(salary) AS minsalary FROM employees 
GROUP BY department_id
HAVING minsalary > (SELECT MIN(salary) AS minsalary FROM employees WHERE department_id = 50)
ORDER BY minsalary DESC;

分页查询

SELECT *
FROM `employees` 
LIMIT (page-1)*size,size;

SELECT *
FROM `employees` 
LIMIT 10,10;

联合查询

# 无

DML 数据操作语言

插入

INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES('p_a','捡垃圾1',200,6000);

INSERT INTO jobs 
VALUES('p_a1','捡垃圾1',200,6000),
('p_a2','捡垃圾2',200,6000);

INSERT INTO jobs SET job_id = 'ces',job_title="123"

INSERT INTO jobs SELECT 'ces1','444',200,6000

修改

# 简单修改
UPDATE jobs
SET job_title = '2222'
WHERE job_id = 'ces1'

# 修改多表
UPDATE jobs
INNER JOIN employees
ON employees.job_id = jobs.job_id
SET jobs.job_title = CONCAT(jobs.job_title,'22222'),employees.job_id = 'ces1'
WHERE jobs.job_title = 'Public Accountant12322222'

删除

# 单表删除
DELETE FROM jobs
WHERE job_id = 'ces'

# 清空整表
TRUNCATE TABLE ttt;

# 多表删除
DELETE employees
FROM employees
INNER JOIN jobs
ON jobs.job_id = employees.job_id
WHERE jobs.job_id = 'SA_MAN'

DDL 数据定义语言

1. 库的管理
    创建\修改\删除
2. 表的管理
    创建\修改\删除

创建: create 修改: alter 删除: drop

库管理

# 创建 IF NOT EXISTS 进行容错 CHARACTER SET  字符集
CREATE DATABASE IF NOT EXISTS books CHARACTER SET utf8;

# 修改 不能该了
RENAME DATABASE books TO newbook;

# 更改字符集
ALTER DATABASE books CHARACTER SET gbk;

# 库的删除
DROP DATABASE IF EXISTS books;

表的管理

创建表

USE books;
# 表的创建
/*
CREATE TABLE 表名(
 列名 列类型([长度]) [约束],
 列名 列类型([长度]) [约束],
 ...
)
*/
CREATE TABLE book (
 id INT,
 b_name VARCHAR(20),
 price DOUBLE,
 author_id INT,
 publish_date DATETIME
);

DESC book;

CREATE TABLE author(
 id INT,
 au_name VARCHAR(20),
 nation VARCHAR(10)
);

DESC author;

表的修改

/*
修改列:
ALTER TABLE 表名 [CHANGE|MODIFY|ADD|DROP] COLUMN 列名 类型|约束;
修改表名:
ALTER TABLE 表名 RENAME TO 新表名;
*/
# 修改列名
ALTER TABLE book CHANGE COLUMN publish_date publishDate DATETIME;
# 修改列类型约束
ALTER TABLE book MODIFY COLUMN publishDate TIMESTAMP;
# 添加新列
ALTER TABLE author ADD COLUMN annual DOUBLE;
# 删除
ALTER TABLE author DROP COLUMN annual;
# 修改表名
ALTER TABLE author RENAME TO authers;

表的删除

DROP TABLE IF EXISTS authers;

表的复制

# 复制表结构
CREATE TABLE copy LIKE author;

# 复制整个表
CREATE TABLE copy2 
SELECT * FROM author;

约束

添加表时添加约束

# 列级约束
CREATE TABLE stuinfo(
 id INT PRIMARY KEY, # 主键
 stu_name VARCHAR(20) NOT NULL, # 非空
 gender CHAR(1) DEFAULT '男', # 默认
 seat INT UNIQUE # 唯一
);

# 表级约束
CREATE TABLE stuinfo(
 id INT, # 主键
 stu_name VARCHAR(20), # 非空
 gender CHAR(1) , # 默认
 seat INT, # 唯一
 majorid INT,
 
 CONSTRAINT pk PRIMARY KEY(id),# 主键
 CONSTRAINT uq UNIQUE(seat),# 唯一
 CONSTRAINT fk_stuiinfo_major FOREIGN KEY(majorid) REFERENCES major(id)# 外键
);

# 通用建议约束
DROP TABLE  IF EXISTS stuinfo;
CREATE TABLE stuinfo(
 id INT PRIMARY KEY, # 主键
 stu_name VARCHAR(20) NOT NULL, # 非空
 gender CHAR(1) DEFAULT '男', # 默认
 seat INT UNIQUE, # 唯一
 majorid INT,
 -- 表级约束
 CONSTRAINT fk_stuiinfo_major FOREIGN KEY(majorid) REFERENCES major(id)# 外键
);


修改表时的约束

# 修改列约束
ALTER TABLE stuinfo MODIFY COLUMN stu_name VARCHAR(20) NOT NULL; 
# 表级约束
ALTER TABLE stuinfo ADD PRIMARY KEY(id);
ALTER TABLE stuinfo ADD FOREIGN KEY(majorid) REFERENCES major(id);
# 删除外键
# ALTER TABLE stuinfo DROP PRIMARY KEY;

标识列(自增长列)

AUTO_INCREMENT

CREATE TABLE stuinfo(
 id INT PRIMARY KEY AUTO_INCREMENT, # 主键
 stu_name VARCHAR(20) NOT NULL, # 非空
 gender CHAR(1) DEFAULT '男', # 默认
 seat INT UNIQUE, # 唯一
 majorid INT,
 -- 表级约束
 CONSTRAINT fk_stuiinfo_major FOREIGN KEY(majorid) REFERENCES major(id)# 外键
);

TCL 事务控制语言

一个或一组sql语句组成一个执行单位,要么全部执行,要么全部失败。

ACID: A: 原子性 C: 一致性 I: 隔离性 D: 持久性

# 提交表示完成,回滚代表异常
set autocommit = 0; -- 关闭自动事务
START TRANSACTION; -- 开始事务
UPDATE stuinfo SET stu_name = '12232' WHERE id = 3;
SAVEPOINT a; -- 保存节点
UPDATE stuinfo SET stu_name = '12332' WHERE id = 5;
ROLLBACK; -- 回滚事务
ROLLBACK TO a; -- 回滚事务到指定节点
COMMIT; -- 提交事务 

本文使用 mdnice 排版

;
between and 范围

在什么到什么之间,包含前后

# 查找100 到200 之间的数据
SELECT * FROM `employees` 
WHERE 
    `employee_id` BETWEEN 100 AND 200;
in 属于

查询是否属于某些列表中的某一个

# 查询是否属于某些列表中的某一个
SELECT * FROM `employees` 
WHERE 
    `job_id` IN ('SH_CLERK','AD_ASST','AD_VP');
is null or is not null 是否为Null
# 查询字段为空的
SELECT * FROM `employees`
WHERE
    `commission_pct` IS NULL;

# 查询不为空的
SELECT * FROM `employees`
WHERE
    NOT `commission_pct` IS NULL;

SELECT * FROM `employees`
WHERE
    `commission_pct` IS NOT NULL;
<=> 安全等于

既可以判断NULL 又可以判断数值

SELECT * FROM `employees`
WHERE
    `commission_pct` <=> NULL;
order by 排序

ASC升序 DESC降序 默认 ASC

SELECT * FROM `employees` ORDER BY `salary` ASC-- 升序
SELECT * FROM `employees` ORDER BY `salary` DESC-- 降序

SELECT `salary` * IFNULL(`commission_pct`,0) + IFNULL(manager_id,0as money,`salary`  FROM `employees` ORDER BY `money`-- 表达式别名降序

SELECT LENGTH(`last_name`as len FROM `employees` ORDER BY len-- 按函数

SELECT * FROM `employees` ORDER BY `salary` DESC`employee_id` ASC-- 多个排序条件

分组查询


# 每个工种的总工资
SELECT SUM(`salary`AS `money`,`job_id` FROM `employees` GROUP BY `job_id` ORDER BY `money`;

# 每个工种的最高工资
SELECT MAX(`salary`as `max`,`job_id` FROM `employees` GROUP BY `job_id` ORDER BY `max`;

# 查询邮箱包含a的工种的最高工资
SELECT MAX(`salary`as `max``job_id`
FROM `employees`
WHERE email LIKE '%a%' 
GROUP BY `job_id` 
ORDER BY `max`;

# 查询邮箱包含a的工种的最高工资大于1万的   HAVING
SELECT MAX(`salary`as `max``job_id` 
FROM `employees`
WHERE email LIKE '%a%' 
GROUP BY `job_id`
HAVING `max` > 10000
ORDER BY `max`;

# 查询名称长度一样的大于5个人的数据
SELECT LENGTH(`first_name`AS `name`COUNT(1AS `count`
FROM `employees`
GROUP BY `name`
HAVING `count` > 5;

# 多字段分组
SELECT AVG(`salary`AS `avg`,`department_id`,`job_id`
FROM `employees`
GROUP BY `department_id`,`job_id`
ORDER BY `department_id`,`job_id`

连接查询

分类:
    按年代划分:
        sql92标准:只支持内连接
        sql99标准【推荐】:支持内连接+外来连接(左外、右外)+交叉连接
    按功能划分:
        内连接:
            等值连接
            非等值连接
            自连接
        外连接:
            左外连接
            右外连接
            全外连接
        交叉连接

sql92标准

# 等值连接
SELECT e.`first_name`,j.`job_title`,`j`.`job_id`
FROM `employees` as e,`jobs` as j
WHERE `e`.`job_id` = `j`.`job_id`;

# 非等值连接

# 自连接
SELECT e.last_name,m.employee_id,e.manager_id,m.last_name 
FROM `employees` AS e, `employees` AS m
WHERE `e`.employee_id = `m`.manager_id

sql99标准

# 等值连接
SELECT `last_name`,`department_name`
FROM employees
INNER JOIN departments
ON employees.`department_id` = departments.`department_id`

# 复杂的等值连接
SELECT department_name,COUNT('*'AS count,MAX(`salary`AS max,min(`salary`AS min
FROM employees
INNER JOIN departments
ON employees.department_id = departments.department_id
WHERE last_name LIKE '%o%'
GROUP BY department_name
HAVING `count` BETWEEN 2 AND 10
ORDER BY `count`

# 多表等值连接
SELECT last_name,department_name,job_title
FROM employees
INNER JOIN departments
ON departments.department_id =employees.department_id
INNER JOIN jobs
ON employees.job_id = jobs.job_id;

子查询

分类:
    按出现位置:
        select 后面:
            只支持标量子查询
        form 后面
            表子查询
        where 和having后面 *
            标量子查询  *
            列子查询    *
            行子查询
        exists后面
            表子查询

按结果集的行列数不同:
    标量子查询(结果集只有一行一列)
    列子查询(结果集只有一列多行)
    行子查询(结果集只有多列一行)
    表子查询(结果集一般多行多列)

where 和having后面

特点: 1. 放在小括号内 2. 放在条件右侧 3. 标量子查询:配合单行操作符。列子查询:配合多行操作符。


# 查询工资比Abel高的人
SELECT * FROM employees 
WHERE salary > (
 SELECT salary FROM employees 
 WHERE last_name = 'Abel'
);

# job_id与141号员工一样且工资大于143号员工的员工
SELECT last_name,salary,employee_id FROM employees
WHERE job_id = (
 SELECT job_id FROM employees WHERE employee_id = 141
AND salary > (
 SELECT salary FROM employees WHERE employee_id = 143
)

# 查询比50号部门最低工资高的部门的最低工资
SELECT department_id, COUNT(*),MIN(salary) AS minsalary FROM employees 
GROUP BY department_id
HAVING minsalary > (SELECT MIN(salary) AS minsalary FROM employees WHERE department_id = 50)
ORDER BY minsalary DESC;

分页查询

SELECT *
FROM `employees` 
LIMIT (page-1)*size,size;

SELECT *
FROM `employees` 
LIMIT 10,10;

联合查询

# 无

DML 数据操作语言

插入

INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES('p_a','捡垃圾1',200,6000);

INSERT INTO jobs 
VALUES('p_a1','捡垃圾1',200,6000),
('p_a2','捡垃圾2',200,6000);

INSERT INTO jobs SET job_id = 'ces',job_title="123"

INSERT INTO jobs SELECT 'ces1','444',200,6000

修改

# 简单修改
UPDATE jobs
SET job_title = '2222'
WHERE job_id = 'ces1'

# 修改多表
UPDATE jobs
INNER JOIN employees
ON employees.job_id = jobs.job_id
SET jobs.job_title = CONCAT(jobs.job_title,'22222'),employees.job_id = 'ces1'
WHERE jobs.job_title = 'Public Accountant12322222'

删除

# 单表删除
DELETE FROM jobs
WHERE job_id = 'ces'

# 清空整表
TRUNCATE TABLE ttt;

# 多表删除
DELETE employees
FROM employees
INNER JOIN jobs
ON jobs.job_id = employees.job_id
WHERE jobs.job_id = 'SA_MAN'

DDL 数据定义语言

1. 库的管理
    创建\修改\删除
2. 表的管理
    创建\修改\删除

创建: create 修改: alter 删除: drop

库管理

# 创建 IF NOT EXISTS 进行容错 CHARACTER SET  字符集
CREATE DATABASE IF NOT EXISTS books CHARACTER SET utf8;

# 修改 不能该了
RENAME DATABASE books TO newbook;

# 更改字符集
ALTER DATABASE books CHARACTER SET gbk;

# 库的删除
DROP DATABASE IF EXISTS books;

表的管理

创建表

USE books;
# 表的创建
/*
CREATE TABLE 表名(
 列名 列类型([长度]) [约束],
 列名 列类型([长度]) [约束],
 ...
)
*/

CREATE TABLE book (
 id INT,
 b_name VARCHAR(20),
 price DOUBLE,
 author_id INT,
 publish_date DATETIME
);

DESC book;

CREATE TABLE author(
 id INT,
 au_name VARCHAR(20),
 nation VARCHAR(10)
);

DESC author;

表的修改

/*
修改列:
ALTER TABLE 表名 [CHANGE|MODIFY|ADD|DROP] COLUMN 列名 类型|约束;
修改表名:
ALTER TABLE 表名 RENAME TO 新表名;
*/

# 修改列名
ALTER TABLE book CHANGE COLUMN publish_date publishDate DATETIME;
# 修改列类型约束
ALTER TABLE book MODIFY COLUMN publishDate TIMESTAMP;
# 添加新列
ALTER TABLE author ADD COLUMN annual DOUBLE;
# 删除
ALTER TABLE author DROP COLUMN annual;
# 修改表名
ALTER TABLE author RENAME TO authers;

表的删除

DROP TABLE IF EXISTS authers;

表的复制

# 复制表结构
CREATE TABLE copy LIKE author;

# 复制整个表
CREATE TABLE copy2 
SELECT * FROM author;

约束

添加表时添加约束

# 列级约束
CREATE TABLE stuinfo(
 id INT PRIMARY KEY# 主键
 stu_name VARCHAR(20NOT NULL# 非空
 gender CHAR(1DEFAULT '男'# 默认
 seat INT UNIQUE # 唯一
);

# 表级约束
CREATE TABLE stuinfo(
 id INT# 主键
 stu_name VARCHAR(20), # 非空
 gender CHAR(1) , # 默认
 seat INT# 唯一
 majorid INT,
 
 CONSTRAINT pk PRIMARY KEY(id),# 主键
 CONSTRAINT uq UNIQUE(seat),# 唯一
 CONSTRAINT fk_stuiinfo_major FOREIGN KEY(majorid) REFERENCES major(id)# 外键
);

# 通用建议约束
DROP TABLE  IF EXISTS stuinfo;
CREATE TABLE stuinfo(
 id INT PRIMARY KEY# 主键
 stu_name VARCHAR(20NOT NULL# 非空
 gender CHAR(1DEFAULT '男'# 默认
 seat INT UNIQUE# 唯一
 majorid INT,
 -- 表级约束
 CONSTRAINT fk_stuiinfo_major FOREIGN KEY(majorid) REFERENCES major(id)# 外键
);


修改表时的约束

# 修改列约束
ALTER TABLE stuinfo MODIFY COLUMN stu_name VARCHAR(20NOT NULL
# 表级约束
ALTER TABLE stuinfo ADD PRIMARY KEY(id);
ALTER TABLE stuinfo ADD FOREIGN KEY(majorid) REFERENCES major(id);
# 删除外键
# ALTER TABLE stuinfo DROP PRIMARY KEY;

标识列(自增长列)

AUTO_INCREMENT

CREATE TABLE stuinfo(
 id INT PRIMARY KEY AUTO_INCREMENT, # 主键
 stu_name VARCHAR(20NOT NULL# 非空
 gender CHAR(1DEFAULT '男'# 默认
 seat INT UNIQUE# 唯一
 majorid INT,
 -- 表级约束
 CONSTRAINT fk_stuiinfo_major FOREIGN KEY(majorid) REFERENCES major(id)# 外键
);

TCL 事务控制语言

一个或一组sql语句组成一个执行单位,要么全部执行,要么全部失败。

ACID: A: 原子性 C: 一致性 I: 隔离性 D: 持久性

# 提交表示完成,回滚代表异常
set autocommit = 0-- 关闭自动事务
START TRANSACTION-- 开始事务
UPDATE stuinfo SET stu_name = '12232' WHERE id = 3;
SAVEPOINT a; -- 保存节点
UPDATE stuinfo SET stu_name = '12332' WHERE id = 5;
ROLLBACK-- 回滚事务
ROLLBACK TO a; -- 回滚事务到指定节点
COMMIT-- 提交事务 

本文使用 mdnice 排版

收藏
评论区

相关推荐

学完了C++语法之后该学什么??(数据库篇)
数据库与中间件 主要是MySQL、MongDB、Redis、Nginx等; 在大学的课程里,一般都会开设一门数据库的课程,不过这门数据库是没有针对某一种数据库语言的(例如 MySQL、SQlite)。不过我这里只讲 MySQL,因为最频繁。数据库不在多。 把MySQL学好,还是特别重要的,千万不能停留在会用的层面上,而是应该
postgresql和mysql哪个好
postgresql和mysql都是免费且功能强大的开源数据库,很多用户面对这两个库都会有一个问题,那就是哪一个才是最好的开源数据库,MySQL还是PostgreSQL呢?该选择哪一个开源数据库呢? postgresql和mysql哪个好 一.PostgreSQL相对于MySQL的优势 1、在SQL的标准实现上要比MySQL完善,而且功能实现比较严谨;
[DB]PostgreSQL 与 MySQL 相比,优势何在?
PostgreSQL 与 MySQL 相比,优势何在? 数据库 知乎 Pg 没有 MySQL 的各种坑 MySQL 的各种 text 字段有不同的限制, 要手动区分 small text, middle text, large text... Pg 没有这个限制, text 能支持各种大小. 按照 SQL 标准, 做 null 判断不能用
mysql8.0.20安装配置教程
mysql配置安装教程1、下载mysql8.0.20安装包 下载地址: https://dev.mysql.com/downloads/mysql/. 找到安装包后下载。(官网为英文,如果看不懂的小伙伴可以将网站复制到谷歌进行翻译) 点击跳过登录,直接下载到本地。安装mysql
MySQL 8.0 创建 user 及允许远程连接
进入 mysql 命令行模式mysql h127.0.0.1 uroot p 查看当前 mysql 版本select version(); 查看当前 mysql 用户列表信息select host, user, authentication_string, plugin from user; 执行完上面的命令后会显示一个表格 查看表格
MySQL 5.7新特性:并行复制原理
众所周知,MySQL的复制延迟是一直被诟病的问题之一,在MySQL 5.7版本已经支持“真正”的并行复制功能,官方称为为enhanced multithreaded slave(简称MTS),因此复制延迟问题已经得到了极大的改进。总之,MySQL 5.7版本后,复制延迟问题永不存在。一、MySQL 5.6并行复制架构从MySQL 5.6.3版本开始
21分钟 MySQL 入门教程
21分钟 MySQL 入门教程 目录 一、MySQL的相关概念介绍(c1) 二、Windows下MySQL的配置(c2) 配置步骤(d1) MySQL服务的启动、停止与卸载(d2) 三、
MySQL(一)MySQL基础介绍
最近的学习内容是数据库相关的一些知识,主要以MySQL为主,参考书籍——《MySQL必知必会》MySQL学习及下载地址:https://dev.mysql.com/MySQL学习使用注意事项:1、必须访问一个已有的MySQL服务器,需要一个服务器账号(一个登录名和一个口令)2、MySQL运行在所有主要平台上,包括Windows、Linux、Solaris、M
Mysql Workbench使用教程
<1 MySQL WorkbenchMySQL Workbench 为数据库管理员、程序开发者和系统规划师提供可视化的Sql开发、数据库建模、以及数据库管理功能。 <2.MySQL Workbench 的下载和安装 (1)安装最新MySql时,有是否安装MySql Workbench的选项,可选择安装。 (2)可以独立安装MySql Workbench。
什么是索引?Mysql目前主要的几种索引类型
一、索引MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度。打个比方,如果合理的设计且使用索引的MySQL是一辆兰博基尼的话,那么没有设计和使用索引的MySQL就是一个人力三轮车。索引分单列索引和组合索引。单列索引,即一个索引只包含单个列,一个表可以有多个单列索引,但这不是组合索引。组合索引,即一个索引包含多个列。创
解决mysql ERROR 1045 (28000)-- Access denied for user
解决mysql ERROR 1045 (28000) Access denied for user问题,出现以下问题D:\develop\ide\mysql\mysql5.7\bin mysql u root p Enter password: ERROR 1045 (28000): Access denied for user 'ODBC'@'localh
一文读懂一条 SQL 查询语句是如何执行的
2001 年 MySQL 发布 3.23 版本,自此便开始获得广泛应用,随着不断地升级迭代,至今 MySQL 已经走过了 20 个年头。为了充分发挥 MySQL 的性能并顺利地使用,就必须正确理解其设计思想,因此,了解 MySQL 的逻辑架构是必要的。本文将通过一条 SQL 查询语句的具体执行过程来详细介绍 MySQL 架构中的各个组件。MySQL 逻辑架构
MySQL最全整理,1200页文档笔记,从高级到实战讲的太清楚了
闲话作为一名编程人员,对MySQL一定不会陌生,尤其是互联网行业,对MySQL的使用是比较多的。对于求职者来说,MySQL又是面试中一定会问到的重点,很多人拥有大厂梦,却因为MySQL败下阵来。实际上,MySQL并不难,今天这份最全的MySQL总结,一共1200页,几乎涵盖了MySQL的所有知识,尤其突出了实战技能和高级知识点,无论是工作还是面试看完这篇就足
阿里Java架构师谈:2021年最新Java面试经历
第一家是美团美团的话,三面下来,设计的内容知识也是挺广的吧,有MySQL、Redis、Kafka、线程、算法、+、volatile、线程、并发、设计模式等等... 一面问题:MySQL+Redis+Kafka+线程+算法 mysql知道哪些存储引擎,它们的区别 mysql索引在什么情况下会失效 mysql在项目中的优化场景,慢查询解决等 my
超详细图解!【MySQL进阶篇】存储过程,视图,索引,函数,触发器
@ 1.1 下载Linux 安装包下载地址: 1.2 安装MySQLcAPACHE1). 卸载 centos 中预安装的 mysql rpm qa | grep i mysql rpm e mysqllibs5.1.711.el6.x8664 nodeps2). 上传 mysql 的安装包 alt + p put E:/test/MySQL5.6.221