关于mysql:MySQL基础

52次阅读

共计 21734 个字符，预计需要花费 55 分钟才能阅读完成。

MySQL 根底

一、查问的 SQL 语句（DQL 语言的一部分）

1、查问语句

1.1 根本语句

留神：sql 语句对大小写不敏感，所以创立表时 id int，Id varchar 这样的不能同时呈现。

查问能够查：常量、表达式、函数；

SELECT 100;

SELECT 100-92;

SELECT VERSION();

如果想查的字段蕴含反复的，本人想去重，那么能够应用distinct。

# 显示有哪些部门编号
SELECT DISTINCT department_id FROM employees;

+的作用：java 中有拼接的性能，而 mysql 中不是，只有运算的性能；比方两个运算，如果间接能算，那么就计算，如果不能那么会试图把他们转换成数值型再计算，如果转换失败就会把它们转换为 0，如果其中有一方为 null，那么后果是 null；

# 查问员工姓和名，拼接成姓名    上面这个是错的
SELECT first_name+last_name`departments` '姓名' FROM employees;

#下面这个拼接能够用 CONCAT，CONCAT(str1,str2......)函数
SELECT CONCAT(first_name,last_name) AS '姓名' FROM employees;

#这个也能计算胜利，后果 213
SELECT 123 + '90';

#后果是 123
SELECT 123 + 'abv';

#后果是 0
SELECT 'abc' + 'abv';

#后果为 null
SELECT 90 + null;

如果某个字段中有 null 值，拼接会为 null，那么就须要应用函数 IFNULL()了。

IFNULL(` 字段名 `，0);    =># 这示意把某个字段的 null 值变为 0

1.2 where——筛选

# 语法
SELECT 查问的列表 FROM 表名 WHERE 筛选条件;

筛选条件分为几类

  • 按条件表达式筛选：=、<、>、<=、>=、<>
  • 按逻辑表达式筛选：&&（与）、||（或）、！（非）===>and、or、not
  • 含糊查问：like、between…and、in、is null；like 含糊查问其中 % 示意任意多个字符，_示意任意一个字符；between…and 前后数不能调换，in 中的值类型必须对立
  • is null 和 <=>：is null 只能判断 null 值，<=> 除了判断 null 值，还能够判断别的值，举荐应用 is null 判断 null 值，因为可读性高
# 比方 like 的字段蕴含特殊字符须要本义，查问名第二个字符为_的
SELECT * FROM employees WHERE last_name LIKE '_\_%';

#判断某个列值为 null 的用户信息，能够应用 is null，也能够用 <=>，这里举荐应用 is null，因为可读性高
SELECT last_name,job_id,commission_pct FROM employees WHERE commission_pct <=> NULL;

1.3 order by——排序

order by 前面反对别名。上面这个查问完年薪起别名，能够按别名排序。

# 查问所有员工信息、年薪，并按年薪高下排序
SELECT *,salary * 12 * (1 + IFNULL(commission_pct,0)) 年薪 FROM employees ORDER BY 年薪;

#当有两个及以上的列排序时，间接写即可
SELECT salary,employee_id FROM employees ORDER BY salary,employee_id DESC;

order by 子句前面能够跟单个字段、多个字段、表达式、函数、别名。

order by 子句个别放在查问语句最初面，limit 子句除外。

1.4 常见函数——单行函数

程序帮咱们封装了，这样咱们就能够间接应用，进步了代码的可复用性。

分类：

  • 单行函数：concat、length、ifnull 等
  • 分组函数：又称为统计函数、聚合函数、分组函数、组函数
（1）字符函数
  • length：示意获取参数值的字节数；

默认 utf- 8 字符集：一个字母占 1 个字节、一个汉字占 3 个字节，不同的字符集占字节数不雷同。

# 如何查看以后字符集
SHOW VARIABLES LIKE '%char%';
#能够看到后果中有
Variable_name            Value
character_set_client     utf8

#那么如下后果
select length('张三丰 loveyou');
#下面这个后果是：3 个汉字 3 *3 = 9，7 个字母为 7，长度是 9 + 7 = 16
  • concat：拼接字符串
# 拼接能够用 CONCAT，CONCAT(str1,str2......)函数
SELECT CONCAT(first_name,last_name) AS '姓名' FROM employees;
  • upper 和 lower：转换为大写和小写
SELECT UPPER('zhangSanfEng');
SELECT LOWER('zhangSanfEng');
  • substr、substring：从索引开始截取字符串，索引是从 1 开始的，和 java 不一样
# 从下标为 6 的中央开始截取：后果为慕容复
SELECT SUBSTR('张三丰大战慕容复',6) 'winner';

#从下标为 4 的中央向后截取两位：后果为大战
SELECT SUBSTR('张三丰大战慕容复',4,2) 'winner';

综合练习：姓名首字母大写，其余字母小写

SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),LOWER(SUBSTR(last_name,2))) FROM employees;
  • instr：查问某个字符串 A 在字符串 B 的索引地位，找不到返回 0 
# 查问哈哈哈在张三丰哈哈哈的什么地位
SELECT INSTR('张三丰哈哈哈','哈哈哈');
  • trim：去掉前后的字符，上面是去掉前后的‘’空格
# 后果为张三丰
SELECT LENGTH(TRIM('' FROM'    张三丰     '));

#后果为张三丰 aaaaaaa 张三疯
SELECT TRIM('a' FROM 'aaaaaaaaaa 张三丰 aaaaaaa 张三疯 aaaaaaaaaa');
  • lpad：左填充指定长度的指定字符；rpad 同理：右填充
# 这个示意初始字段是‘刘备’，而后用大萌萌填充到 10 个字符，所以最初后果是：大萌萌大萌萌大萌刘备
SELECT LPAD('刘备',10,'大萌萌');
  • replace：替换
# 把玄德换成黄书，变成：刘黄书，我是刘黄书，刘黄书就是我，天下英雄只有刘黄书和曹操
SELECT REPLACE('刘玄德，我是刘玄德，刘玄德就是我，天下英雄只有刘玄德和曹操','玄德','黄书')；
（2）数学函数
  • round：四舍五入
# 能够放数值，这种是四舍五入
SELECT ROUND(-11.24);

#还能够放两个数，示意取到第几位
SELECT ROUND(122.237323,2);
  • ceil：向上取整；floor：向下取整
SELECT ceil(1.24);    #后果为 2

SELECT floor(1.24);    #后果为 1
  • truncate：截断
# 从小数后 1 位截断，也就是 1.6
SELECT TRUNCATE(1.65,1);
  • mod 取余
SELECT MOD(10,3);    #后果为 1
（3）日期函数
  • now：返回以后零碎日期，含工夫
SELECT NOW();
  • curdate：返回零碎以后日期，不含工夫
SELECT CURDATE();
  • curtime：返回零碎以后工夫，不含日期
SELECT CURTIME();
  • 获取指定的局部，年、月、日、小时、分钟、秒等
SELECT CONCAT(YEAR(NOW()),'年',MONTH(NOW()),'月',DAY(NOW()),'日',HOUR(NOW()),'时',MINUTE(NOW()),'分',SECOND(NOW()),'秒');
  • str_to_date: 将日期格局的字符转换成指定格局的，格局不只有这一种
SELECT STR_TO_DATE('1994-10-10','%Y-%m-%d');
  • date_format：将日期转换成字符
SELECT DATE_FORMAT(NOW(),'%y 年 %m 月 %d 日') as out_put;
  • date_diff：计算日期相差多少天
SELECT DATEDIFF(MAX(hiredate),MIN(hiredate)) FROM employees;

SELECT DATEDIFF('2021-09-04','1998-10-02');
（4）其余函数
  • version：版本号
  • database：select database();
  • user：select user();
（5）流程管制函数
  • if：相似 if else
# 相似三元运算符
SELECT IF(10 < 5,'大','小');
  • case：第一种形式：switch case 的成果
# 看部门，不同的部门薪水的比重不一样
SELECT salary,department_id,
CASE department_id
    WHEN 30 THEN salary * 1.1
    WHEN 40 THEN salary * 1.2
    WHEN 50 THEN salary * 1.3
    ELSE salary
END AS '新工资'
FROM employees;

-------------------------------------------------
#另一个示例
select 
case 3
when 3 then '这是 3'
WHEN 4 then '这是 4'
WHEN 5 then '这是 5'
else '这个我不意识'
end as '数字';

1.5 常见函数——分组函数

传一组值，变成一个值。

  • sum：求和
  • avg：平均数
  • max：最大值
  • min：最小值
  • count：计算个数

分组函数特点：

  1. sum 和 avg 只反对数值型；
  2. count、max、min 可用于解决任何类型；
  3. 所有分组函数均疏忽 null 值，间接这一列就不计算，比方计算平均值，100 集体有 20 集体有 null 值，那么计算 avg 除的是 80；
  4. 所有分组函数都能够和 distinct 搭配实现去重运算；
  5. count(*)和 count(列名)，然而 count(列名)如果为 null 不计算，SELECT COUNT(1) FROM employees;等于增加了一列 1 值，而后计算 1 的数量，等于计算了总个数，然而简直不必；

在 myisam 存储引擎下，count(*)的效率最高，因为它外面蕴含了计数器，能够间接返回个数；

在 innodb 存储引擎下，count(*)和 count(1)的效率差不多，然而比 count(列名)高一些，因为 count(列名)要进行一次 null 值判断

留神：和分组函数一起查问的字段是有限度的

SELECT AVG(salary),employee_id from employees;

下面一个查的是一行，一个查的是多行，所以查出来的是有问题的，必定不对。

1.6 group by——分组

# 查问每个工种的最高工资 SELECT MAX(salary),job_id FROM employees GROUP BY job_id;

1.7 having——查问后果作为查问的条件

# 查问部门的员工个数大于二的部门 SELECT COUNT(*) AS 数量,department_id FROM employees GROUP BY department_id HAVING 数量 > 2;# 查问每个工种有奖金的员工的最高工资 >12000 的工种编号和最高工资

本人了解：这个查问如果应用 where 数量 > 2 必定是不行的，因为 where 子句只能查问以后表，然而以后表中查不到数量字段，那么想应用查问后果作为一张表查问的能够应用 having 字句。

having 的分组条件

还能够按表达式或函数分组或别名（废话）：

SELECT COUNT(*),LENGTH(last_name) len  FROM employees GROUP BY LENGTH(last_name) HAVING COUNT(*) > 5;SELECT COUNT(*)AS 数量,LENGTH(last_name) len  FROM employees GROUP BY LENGTH(last_name) HAVING 数量 > 5;

group by 子句反对单个字段分组，也反对多个字段分组。

2、多表连贯查问

2.1 连贯条件及分类

从 beauty 的某女连贯 boys 的某男，应用如下 sql 语句：

SELECT `name`,boyname FROM beauty,boys;

这样查问语法上是没故障的，然而会产生笛卡尔积景象；A 表的每条数据都要和 B 表白的每条数据进行连贯，从而产生不是咱们想要的数据后果。须要加上相应的查问条件能力防止笛卡尔积。

所以条件如下：

SELECT     `name`,boyname FROM     beauty,boys WHERE     boyfriend_id = boys.id;

增加无效的连贯条件就能够防止，那么增加条件能够分为：

  1. sql92 规范：仅反对内连贯和外连贯。
  2. sql99 规范：内连贯，外连贯（左外 + 右外），穿插连贯。

分类：

  • 内连贯

    • 等值连贯
    • 非等值连贯
    • 自连贯

  • 外连贯

    • 左外连贯
    • 右外连贯
    • 全外连贯
  • 穿插连贯

2.2 sql92 语法

（1）等值连贯

多表等值连贯为多表的交加局部，n 表连贯至多要 n - 1 个连贯条件。

# 员工名和部门名对应查问 SELECT     last_name,department_name FROM     employees,departments WHERE     employees.`department_id` = departments.`department_id`;    #查问员工名，工种号，工种名 SELECT     last_name,employees.job_id,job_title #这里有歧义，用表名. 字段名来示意 FROM     employees,jobsWHERE    employees.`job_id` = jobs.`job_id`;

如果表连贯字段总是反复，两表容易有歧义，所以能够应用别名。

SELECT     e.last_name,e.job_id,j.job_titleFROM     employees e,jobs jWHERE    e.`job_id` = j.`job_id`;
（2）非等值连贯

也就是下面的 等于 条件变为 不等于 条件。

# 查问员工的工资和工资等级 SELECT     e.`salary`,j.`grade_level`FROM    employees e,job_grades jWHERE     e.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal`;
（3）自连贯

连贯的就是本人。

# 查问员工名和下级员工名 SELECT     e.`last_name`,f.`last_name`FROM     employees e,employees fWHERE     e.`manager_id` = f.`employee_id`;

2.3 sql99 语法

# 语法：SELECT     查问列表 FROM     表 1 别名 join     表 2 别名 on     连贯条件 where    筛选条件；

下面可读性变高了，因为以前连贯条件和筛选条件都是在一起的，而当初则拆散了，比方以前的 e.manager_id = f.employee_id; 是连贯条件却放在了 where 中。

以前学过了 sql99 蕴含很多种连贯，每种的语法都不太一样，在 join 加上不同的阐明即可：

内连贯（inner）、左外连贯（left【outer】outer 可省略）、右外连贯（right【outer】outer 可省略），全外连贯（full【outer】outer 可省略），穿插连贯（cross）。

（1）内连贯：等值、非等、自连贯
# 查问员工名和下级员工名 SELECT     e.`last_name`,f.`last_name`FROM    employees eINNER JOIN     employees fON    e.`manager_id` = f.`employee_id`;
（2）左外连贯和右外连贯
# 查问员工名和下级员工名 #左外连贯 SELECT     e.`last_name`,f.`last_name`FROM    employees eLEFT JOIN     employees fON    e.`manager_id` = f.`employee_id`;    #右外连贯    SELECT     e.`last_name`,f.`last_name`FROM    employees eRIGHT JOIN     employees fON    e.`manager_id` = f.`employee_id`;

拿 A 匹配 B，也就是 B join A，那么 A 表为主表，B 表为从表，A 中所有的数据都会显示，从表中匹配不到的用 null 去匹配。

left join：右边的是主表，左边的是从表；

right join：左边的是主表，右边的是从表；

（3）全外连贯

mysql 这里不反对。只是演示一下语法。

SELECT     b.*,bo.*FROM     beauty b,FULL OUTER JOIN     boys boON    b.`boyfriend_id` = bo.id;

就是把两个表全副数据都查出来，查不到的用 null 填充。

（4）穿插连贯
SELECT b.*,bo.*FROM beauty bCROSS JOIN boys bo;

有点相似不加条件的查问，蕴含笛卡尔乘积。

3、子查问

3.1 子查问的分类

嵌套查问，内部查问语句叫主查问或外查问，外部查问语句叫子查问或内查问。

分类

依照子查问呈现的地位能够分为：

  • 在 select 前面：仅仅反对标量子查问
  • from 前面：反对表子查问
  • where 或 having 前面：反对标量子查问、列子查问、也反对行子查问（呈现的较少）
  • exists 前面（相干子查问）：反对的是表子查问

依照后果集的行列数不同：

  • 标量子查问（后果集只有一行一列）
  • 列子查问（后果集有一列多行）
  • 行子查问（后果集有一行多列）
  • 表子查问（后果集有多行多列）

3.2 where 或 having 前面

特点：都放在小括号内，都放在条件的右侧。

  • 标量子查问：个别搭配着单行操作符：>、<、=、>=、<=、!=
# 谁的工资比阿贝尔高 SELECT * FROM employees WHERE salary > (SELECT salary FROM employees WHERE last_name = 'Abel');
salary
11000.00

select 子查问的后果如上，的确是一行一列。

  • 列子查问：个别搭配着多行操作符：IN、ANY、SOME、ALL

    a > any(10,20,30) ==> a > min(10,20,30)

    a > all(10,20,30) ==> a > max(10,20,30)

# 返回 location_id 是 1400 或 1700 的部门中所有员工的姓名 SELECT     last_nameFROM     employeesWHERE     department_id IN(SELECT DISTINCT             department_id        FROM             departments        WHERE             location_id IN (1400,1700))
  • 行子查问：一行多列或多行多列
# 查问员工编号最小且工资最高的员工信息 #列子查问形式 SELECT     *FROM    employeesWHERE    employee_id = (SELECT             MIN(employee_id)        FROM            employees    )AND    salary = (SELECT            MAX(salary)        FROM            employees    )    #行子查问形式 SELECT     *    FROM    employeesWHERE    (employee_id,salary) =(SELECT             MIN(employee_id),MAX(salary)        FROM             employees    )

3.3 select 前面

# 查问每个部门的员工个数 SELECT d.*,(SELECT COUNT(*) FROM employees e WHERE e.department_id = d.`department_id`) '个数'FROM departments d;

3.4 from 前面

# 查问每个部门的平均工资的工资等级 SELECT     ag_dep.*,g.`grade_level`FROM E    SELECT         AVG(salary) ag,department_id     FROM         employees     GROUP BY         department_id) ag_depINNER JOIN     job_grades gON    ag_dep.ag BETWEEN lowest_sal AND highest_sal;

3.5 exists 前面（相干子查问）

SELECT EXISTS (SELECT employee_id from employees);

4、分页查问

# 语法：SELECT     查问列表 FROM     表 #可能有连贯【join 子句】#on 连贯条件 WHERE     筛选条件 GROUP BY     分组字段 HAVING     分组后的筛选 LIMIT offset，size；#offsize：要显示条目标起始索引（起始索引从 0 开始）#size：示意要显示的条目个数
# 显示前五条员工信息 SELECT * FROM employees LIMIT 0,5;

公式：要显示的页数为page: 每页条目数为size。

那么起始索引为：(page-1)×size。

也就是：limit (page-1)×size,size 。

5、联结查问

将多个查问语句的后果合并为一个后果。

# 查问部门编号 >90 或邮箱蕴含 a 的员工信息 SELECT * FROM employees WHERE department_id > 90UNION(SELECT * FROM employees WHERE email LIKE '%a%');
# 语法：查问语句 1UNION 查问语句 2;

什么时候用呢？

比方查问的后果来自不同的表，而且这几个表没有分割，然而所查问的信息统一的时候，能够用连贯查问；而且这两个查问的后果列数须要雷同 。不然会报错，为了满足查问后果合乎本人需要， 查问的两个后果集的列程序应统一。

union 关键字默认是去重的，如果应用的是 union all 能够蕴含反复项。

二、增删改的 SQL 语句（DML 的一部分）

1、插入语句

根本插入语句：

# 语法：INSERT INTO 表名（列名,...）VALUES (列值,...);

对于像二进制类型等在 sql 中没法直接插入，须要应用 jdbc 等 java 技术转换类型能力插入的，能够设置字段类型为 nullable，这样，就能够不插入了。

可为空的字段，不想插入值的话，要么值写 null，要么字段都不加上。

插入值不太多的时候能够用上面这个语句：

# 语法：INSERT INTO 表名 SET 列名 = 值，列名 = 值...;

两种形式比照

  • 第一种形式反对多行插入，第二种不反对；
  • 第一种形式反对子查问，第二种不反对；
INSERT INTO employees(employee_id,last_name,email) SELECT(100,'James','110101');# 第一种形式

2、批改语句

批改单表中记录的语句：

# 语法：UPDATE 表名 SET 列 = 新值, 列值 = 新值...WHERE 筛选条件;

批改多表中记录的语句（sql92 语法）：

UPDATE 表 1 别名，表 2 别名 SET 列 = 值，...WHERE 连贯条件 AND 筛选条件;

批改多表中记录的语句（sql99 语法）：

UPDATE 表 1 别名 INNER|LEFT|RIGHT| JOIN 表 2 别名 ON 连贯条件 SET 列 = 值,...WHERE 筛选条件;

3、删除语句

  • delete 删除（单表删除）：
DELETE FROM 表名 WHERE 筛选条件;
  • delete 删除（多表删除 sql92 语法）：
DELETE 表 1 别名，表 2 别名 FROM 表 1 别名，表 2 别名 WHERE 连贯条件 AND 筛选条件;
  • delete 删除（多表删除 sql99 语法）：
DELETE 表 1 别名，表 2 别名 FROM 表 1 别名 INNER|LEFT|RIGHT| JOIN 表 2 别名 ON 连贯条件 WHERE 筛选条件;

这里须要晓得多表删除就是想删哪张表就 delete 哪个表，另一张表 join 进来。

不加筛选条件整张表都删掉了，须要谨慎。

  • truncate 清空数据：
TRUNCATE TABLE 表名;

这样就是整张表的数据记录都删了，不能复原，须要十分谨慎。

delete 和 truncate

  • delete 能够加条件，truncate 是间接删除不保留
  • truncate 效率高一点点
  • 如果说要删除的表中有自增长列，用 delete 删除后，再插入值，自增长列的值从断点开始；truncate 从 1 开始（废话）
  • truncate 删除没有返回值，delete 删除有返回值（几行受影响）
  • truncate 删除不能回滚，delete 删除能够回滚

三、库和表的 SQL 语句（DDL 的一部分）

1、简略介绍

  • 创立：create
  • 批改：alter
  • 删除：drop

2、库的治理

2.1 库的创立

# 语法：CREATE DATABASE 库名;# 可加上条件 CREATE DATABASE IF NOT EXISTS 库名;

2.2 库的批改

# 批改库名 RENAME DATABASE 库名 TO 新库名;# 当初曾经不反对了 #更改库的字符集 ALTER DATABASE 库名 CHARACTER SET 新字符集    #默认是 utf8

2.3 库的删除

# 语法：DROP DATABASE IF EXISTS 库名;

3、表的治理

3.1 表的创立

# 语法：CREATE TABLE 数据库表名(列名 1 数据类型 1 其余要求【束缚】，列名 2 数据类型 2 其余要求【束缚】... ...);

3.2 表的删除

DROP TABLE 数据库表名;DROP TABLE IF EXISTS 数据库表名;

3.3 表的批改

# 增加列 ALTER TABLE 数据库表名 ADD 列名 1 数据类型 1 其余要求；#能够在其余要求的地位增加 after 来设置 l# 批改列的数据类型、尺寸默认值 ALTER TABLE 数据库表名 MODIFY 列名 1 数据类型 1 其余要求；# 删除列 ALTER TABLE 数据库表名 DROP 列名 1；# 重命名列 ALTER TABLE 数据库表名 CHANGE 旧列名 新列名；

3.4 表的复制

# 仅仅复制表的构造 CREATE TABLE 新表名 LIKE 旧表名;# 复制表构造 +  数据 CREATE TABLE 新表名 SELECT * FROM 旧表名;# 只复制局部数据 CREATE TABLE 新表名 SELECT 本人须要的局部;# 只复制局部表构造 CREATE TABLE 新表名 SELECT 本人须要的局部数据 WHERE 1 = 2;# 局部构造和数据都复制过去，不想要数据，那就让 where 始终不满足即可

3.5 数据类型

怎么设置数据类型有无符号：默认是有符号的。设置成无符号，创立表时在表字段前面加上UNSIGNED。

（1）整型

MySQL 数据类型 含意（有符号如下，无符号的数据比上面大一倍）
tinyint(m) 1 个字节 范畴(-128~127)
smallint(m) 2 个字节 范畴(-32768~32767)
mediumint(m) 3 个字节 范畴(-8388608~8388607)
int(m) 4 个字节 范畴(-2147483648~2147483647)
bigint(m) 8 个字节 范畴(+-9.22*10 的 18 次方)

（2）浮点型

默认数值精度会依照首次插入精度来确定。

MySQL 数据类型 含意
float(m,d) 单精度浮点型 8 位精度(4 字节) m 总个数，d 小数位
double(m,d) 双精度浮点型 16 位精度(8 字节) m 总个数，d 小数位

（3）定点型

decimal(m,d) 参数 m <65 是总个数，d<30 且 d<m 是小数位。默认m=10，d=0。

（4）字符串

MySQL 数据类型 含意
char(n)，其中 n 示意字符数，n 默认为 1，可省略 固定长度，最多 255 个字符
varchar(n)，其中 n 示意字符数，不能够省略 n 固定长度，最多 65535 个字符
tinytext 可变长度，最多 255 个字符
text 可变长度，最多 65535 个字符
mediumtext 可变长度，最多 2 的 24 次方 - 1 个字符
longtext 可变长度，最多 2 的 32 次方 - 1 个字符

char 比 varchar 消耗空间，然而效率更高一点。

（5）二进制数据

1._BLOB 和_text 存储形式不同，_TEXT 以文本形式存储，英文存储辨别大小写，而_Blob 是以二进制形式存储，不分大小写。

2._BLOB 存储的数据只能整体读出。

3._TEXT 能够指定字符集，_BLO 不必指定字符集。

（6）日期工夫类型

MySQL 数据类型 含意
date 日期 ‘2008-12-2’
time 工夫 ’12:25:36′
datetime 日期工夫 ‘2008-12-2 22:06:44’
timestamp 主动存储记录批改工夫
year 只记录年份

timestamp 反对的范畴比拟小，也就到 1970-2038 年的某个工夫，而 datetime 的取值能够从 1000-9999 年。

timestamp 和理论的时区无关，更能反馈出理论的工夫，而 datetime 只能反映出当地时区。（倡议这里能够本人测试一下）

（7）枚举类型

又称为枚举类型哦，要求插入的值必须属于列表中指定的值之一。

如果列表成员为 1~255，则须要 1 个字节存储；如果列表成员为 255~65535，则须要 2 个字节存储，最多须要 65535 个成员！

（8）Set 类型

和 Enum 类型相似，外面能够保留 0~64 个成员。和 Enum 类型最大的区别是：Set 类型一次能够选取多个成员，而 Enum 只能选一个依据成员个数不同，存储所占的字节也不同。

成员数 字节数
1~8 1
9~16 2
17~24 3
25~32 4
33~64 8 
CREATE TABLE table_set(s1 SET('a','b','c','d')    );# 能够像上面这样插入值，插入的不只一个 INSERT INTO table_set VALUES('a');INSERT INTO table_set VALUES('a,b');INSERT INTO table_set VALUES('a,c,d');

3.6 显示表构造

DESCRIBE 表名；

<img src=”https://gitee.com/yzdswzt/cloudimages/raw/master/img/image-20210902102020957.png” alt=”image-20210902102020957″ style=”zoom:80%;” />

4、束缚

4.1 简略介绍

一种限度，用于限度数据，保障其精确牢靠。

一共有 6 大束缚：

  • NOT NULL：非空束缚，保障字段值不为空，比方姓名、学号等；
  • DEFAULT：默认束缚，保障该字段有默认值，比方性别；
  • PRIMARY KEY：主键束缚，保障该字段值的唯一性，并且非空，比方学号、员工编号等；
  • UNIQUE：惟一束缚，保障该字段的值具备唯一性，但能够为空，比方座位号等；
  • CHECK：查看束缚，【mysql 中不反对，语法没错，然而 mysql 中不起作用】，比方年龄等，能够用查看束缚限度范畴；
  • FOREIGN KEY：外键束缚，用于限度两个表的关系的，保障该字段值必须来自于主表的关联列的值，在从表中增加外键束缚，用于引入主表的值，比方业余编号、员工部门编号等等；

束缚增加的分类：

  • 列级束缚：下面几个都能够写，语法都反对，但 外键束缚 没有成果；
  • 表级束缚：除了非空和默认，其余的都反对；
# 列级束缚和表级束缚所在位置 CREATE TABLE 表名(字段名 字段类型 列级束缚,    字段名 字段类型,    表级束缚)

4.2 增加列级束缚

# 应用列级形式增加束缚 CREATE TABLE students(id INT PRIMARY KEY,# 主键    stuName VARCHAR(20) NOT NULL,# 非空    gender CHAR(1) CHECK(gender='男'  OR gender='女'),# 查看束缚    seat INT UNIQUE,# 惟一    age INT DEFAULT 18,# 默认束缚    majorId INT FOREIGN KEY REFERENCES major(id),# 外键, 当然这里是不反对的，下面说了列级束缚外键没成果)CREATE TABLE major(id INT PRIMARY KEY,# 主键     majName VARCHAR(20) NOT NULL,# 非空)

4.3 增加表级束缚

# 应用表级形式增加束缚 CREATE TABLE students(id INT,    stuName VARCHAR(20),    gender CHAR(1),    seat INT,    age INT,    majorId INT,        CONSTRAINT pk PRIMARY KEY(id),# 给 id 增加主键    CONSTRAINT uq UNIQUE(seat),# 惟一键    CONSTRAINT ck CHECK(gender='男' OR gender='女'),# 查看束缚    CONSTRAINT fk_stuinfo_major FOREIGN KEY(major_id) REFERENCE major(id),# 增加外键)

主键和惟一的比照：

保障唯一性 是否容许为空 一个表能够有几个 是否容许组合
主键 √ × 至少有多个 √，但示意两个字段组合成一个主键
惟一 √ √，不过只能有一个为 null，多了还是算反复 能够有多个 √

外键的特点

  1. 要求在从表设置外键关系
  2. 从表的外键列的类型和主表的关联列的类型要求统一或兼容
  3. 主表中的关联列必须是一个 key（个别是主键或惟一键）

4.4 批改表时增加束缚

# 增加列级束缚 ALTER TABLE 表名 MODIFY COLOMN 字段名，字段类型 新束缚;# 增加表级束缚 ALTER TABLE 表名 ADD [CONSTRAINT 束缚名] 束缚类型(字段名) [外键的援用];

4.5 批改表时删除束缚

# 删除非空束缚 ALTER TABLE 表名 MODIFY COLUMN 字段名 字段类型 NULL;# 删除默认束缚 ALTER TABLE 表名 MODIFY COLUMN 字段名 字段类型;# 删除主键 ALTER TABLE 表名 DROP PRIMARY KEY;# 删除惟一束缚 ALTER TABLE 表名 DROP INDEX 字段名;# 删除外键 ALTER TABLE 表名 DROP FOREIGN KEY 字段名;

5、标识列

又称为自增长列，，能够不必手动插入值，零碎提供默认的序列值。

一个表最多只能有一个标识列。

标识列必须和主键搭配吗，不肯定，然而要求是一个 key。

标识列的类型只能是数值型。

5.1 创立表时创立标识列

CREATE TABLE tab_identity(id INT PRIMARY KEY AUTO_INCREMENT,# 改为自增长    NAME VARCHAR(20),)

5.2 设置步长

# 能够先查问一下步长 SHOW VARIABLES LIKE '%auto_increment%';# 设置步长 SET auto_increment_increment = 3;

5.3 批改表时创立标识列

ALTER TABLE 表名 MODIFY COLUMN 列名 INT PRIMARY KEY AUTO_INCREMENT;

四、事务的 SQL 语句（TCL 的一部分）

1、简略介绍

TCL：Transaction Control Language 事务管制语言。

事务：一组 sql 语句要么都执行，要么都不执行（回滚）。

Innodb 反对事务，Myisam、memory 不反对事务。

# 查看存储引擎 SHOW ENGINES;

1.1 事务的 ACID 准则

  • 原子性（Atomicity）：原子性是指事务是一个 不可分割 的工作单位，事务中的操作要么都产生，要么都不产生。
  • 一致性（Consistency）：事务必须使数据库从一个 一致性状态 变换到另外一个一致性状态。
  • 隔离性（Isolation）：事务的隔离性是指一个事务的执行 不能被其余事务烦扰，即一个事务外部的操作及应用的数据对并发的其余事务是隔离的，并发执行的各个事务之间不能相互烦扰。
  • 持久性（Durability）：持久性是指一个事务一旦被提交，它 对数据库中数据的扭转就是永久性的，接下来的其余操作和数据库故障不应该对其有任何影响。

2、事务的创立

  • 隐式事务：没有显著的开启和完结的标记，比方 delete、insert、update 语句；每一条语句相当于一条事务；
  • 显式事务：有显著的开启和完结的标记，必须先设置下面这种主动提交性能为禁用，这样能力保障多条 sql 语句同时执行时，中途呈现问题，不会一条一条提交；
# 敞开主动提交 SET AUTOCOMMIT = 0;

2.1 应用事务的步骤

# 步骤 1：开启事务 SET AUTOCOMMIT = 0;START TRANSACTION;# 这条语句不是必须的，因为敞开了主动提交性能就等同于开启了事务 #步骤 2：编写事务中的 sql 语句#语句 1；#语句 2；#步骤 3：完结事务 COMMIT;#提交事务 ROLLBACK;# 回滚事务;

delete 和 truncate 事务的区别

delete 删除能够回滚，truncate 不能够，删了就没了。

3、并发状况下的事务问题

同时运行多个事务拜访同一个数据资源时，可能会导致并发问题：

  • 脏读：两个事务 T1、T2，T1 批改了数据但未提交就被 T2 读取到了，如果 T1 回滚，T2 读到的数据就是有效的了；（字段）
  • 不可反复读：两个事务 T1、T2，T1 读取了一个字段 T2 更新了，T1 就读不到以前的那个值了；
  • 幻读：对两个事务 T1、T2，T1 从一个表中读取了一个字段，而后 T2 在该表中插入了新行，T1 再读这个表会多出几行；（插入行）

3.1 数据库的事务隔离级别

  • Read uncommitted

读未提交，顾名思义，就是一个事务能够读取另一个未提交事务的数据。

事例：老板要给程序员发工资，程序员的工资是 3.6 万 / 月。然而发工资时老板不小心按错了数字，按成 3.9 万 / 月，该钱曾经打到程序员的户口，然而事务还没有提交，就在这时，程序员去查看本人这个月的工资，发现比平常多了 3 千元，认为涨工资了非常高兴。然而老板及时发现了不对，马上回滚差点就提交了的事务，将数字改成 3.6 万再提交。

剖析：理论程序员这个月的工资还是 3.6 万，然而程序员看到的是 3.9 万。他看到的是老板还没提交事务时的数据。这就是脏读。

  • Read committed

读提交，顾名思义，就是一个事务要等另一个事务提交后能力读取数据。

事例：程序员拿着信用卡去享受生存（卡里当然是只有 3.6 万），当他埋单时（程序员事务开启），免费零碎当时检测到他的卡里有 3.6 万，就在这个时候！！程序员的妻子要把钱全副转出充当家用，并提交。当免费零碎筹备扣款时，再检测卡里的金额，发现曾经没钱了（第二次检测金额当然要期待妻子转出金额事务提交完）。程序员就会很郁闷，明明卡里是有钱的…

剖析：这就是读提交，若有事务对数据进行更新（UPDATE）操作时，读操作事务要期待这个更新操作事务提交后能力读取数据，能够解决脏读问题。但在这个事例中，呈现了一个事务范畴内两个雷同的查问却返回了不同数据，这就是不可反复读。

  • Repeatable read

反复读，就是在开始读取数据（事务开启）时，不再容许批改操作

事例：程序员拿着信用卡去享受生存（卡里当然是只有 3.6 万），当他埋单时（事务开启，不容许其余事务的 UPDATE 批改操作），免费零碎当时检测到他的卡里有 3.6 万。这个时候他的妻子不能转出金额了。接下来免费零碎就能够扣款了。

剖析：反复读能够解决不可反复读问题。写到这里，应该明确的一点就是，不可反复读对应的是批改，即 UPDATE 操作。然而可能还会有幻读问题。因为幻读问题对应的是插入 INSERT 操作，而不是 UPDATE 操作。

  • Serializable 序列化

Serializable 是最高的事务隔离级别，在该级别下，事务串行化程序执行，能够防止脏读、不可反复读与幻读。然而这种事务隔离级别效率低下，比拟耗数据库性能，个别不应用。

3.2 默认事务隔离级别

  • Oracle 反对的 2 种事务隔离级别：Read committed, Serializable。Oracle 默认的事务隔离级别为: Read committed
  • Mysql 反对 4 种事务隔离级别. Mysql 默认的事务隔离级别为:Repeatable read

3.3 实际操作

# 查看以后事务隔离级别 SELECT @@_tx——isoliation;# 设置以后 mySQL 连贯的隔离级别 SET SESSION TRANSACTION ISOLATION LEVEL【某个隔离级别】;# 设置数据库系统的全局的隔离级别 SET GLOBAL SESSION TRANSACTION ISOLATION LEVEL;

3.4 保留点

# 语法 SAVEPOINT 节点名;

演示如何应用 savepoint;

SET AUTOCOMMIT = 0;START TRANSACTION;DELETE FROM account WHERE id = 25;# 设置保留点 SAVEPOINT a;DELETE FROM account WHERE id = 28;# 回滚到 a 保留点 ROLLBACK TO a;

五、视图

1、简略介绍

视图其实就是虚构表，和一般表一样应用，MySQL5.1 版本呈现的新个性，是通过表 动静生成 的数据。

舞蹈班和普通班：其实没有舞蹈班，只有领导来了当前，才长期搭建的舞蹈班，领导走了当前主动遣散，普通班不是这样的。

示例（没用视图之前）：

# 查问姓张的学生名和业余名 SELECT stuname,majornameFROM stuname sINNER JOIN major mON s.`majorid` = m.`id`WHERE s.`stuname` LIKE '张 %';

示例（有了视图）：

# 还是得先查问一遍，封装成视图 v1CREATE VIEW v1ASSELECT stuname,majornameFROM stuname sINNER JOIN major mON s.`majorid` = m.`id`WHERE s.`stuname` LIKE '张 %';# 再次查问 v1 即可 SELECT * FROM v1 WHERE stuname LIKE '张 %';

2、视图的创立

# 语法 CREATE VIEW 视图名 AS 要查问的语句;

视图的益处：

  • 重用 sql 语句
  • 简化简单的 sql 操作，不用晓得它的查问细节
  • 爱护数据，进步安全性

3、视图的批改

# 形式一：CREATE OR REPLACE VIEW 视图名 AS 查问语句;
# 形式二：ALTER VIEW 视图名 AS 查问语句;

4、视图的删除

DROP VIEW 视图名 1, 视图名 2;

5、视图的查看

DESC 视图名;# 这个也行，不过不举荐 SHOW CREATE VIEW 视图名;

6、视图的更新

用于更改视图中的数据。其实和表的更新一样。

INSERT INTO 视图名 (...) VALUES (...);# 其余也一样

不过有非凡状况：

  • 蕴含以下关键字的 sql 语句：分组函数、distinct、group by、having、union 或者 union all;
  • 常量视图；
  • Select 中蕴含子查问；
  • join；
  • from 一个不能更新的视图；
  • where 子句的子查问援用了 from 子句中的表；

视图和表的区别

  • 一个是 create view，一个是 create table
  • 是否理论占用物理空间？视图只占用了一小部分，表占用了物理空间
  • 视图个别不能增删改（有限度），表能够增删改查

六、变量

1、变量的分类

  • 零碎变量：

    • 全局变量
    • 会话变量

  • 自定义变量：

    • 用户变量
    • 局部变量

2、零碎变量

由零碎提供，不是用户定义，属于服务器层面的。

# 查看所有的零碎变量，什么都不写默认是 SESSION，上面一样 SHOW GLOBAL|【SESSION】VARIABLES;# 查看局部零碎变量 SHOW GLOBAL|【SESSION】VARIABLES LIKE 查问条件;# 查看某个零碎变量 SELECT @@GLOBAL|【SESSION】零碎变量名;# 为某个零碎变量赋值 #形式一 SET GLOBAL|【SESSION】零碎变量名 = 值;# 形式二 SET @@GLOBAL|【SESSION】. 零碎变量名 = 值;

服务器每次启动都会为全局变量赋初始值，针对于所有的会话都无效，但不能跨重启，如果想真正重启也批改，就须要批改配置文件了。

会话变量的作用域针对于以后会话（连贯），换一个连贯就不一样了。

3、自定义变量

变量是本人定义的。

3.1 用户变量

作用域：针对于以后会话无效的，换一个连贯就有效了，和 Session 的作用域一样。

应用在任何中央，begin end 外面或里面都行。

# 申明并初始化 用 = 或:= 赋值 SET @用户变量名 = 值;# 另一种形式 SET @用户变量名:= 值;# 另一种形式 SELECT @用户变量名:= 值;# 更新用户变量的值 #形式一：同上，既是申明又是更新赋值 SET @用户变量名 = 值;# 另一种形式 SET @用户变量名:= 值;# 另一种形式 SELECT @用户变量名:= 值;# 形式二：SELECT INTO 来赋值 SELECT 字段 INTO @变量名 FROM 表;# 应用 SELECT @用户变量名;

3.2 局部变量

作用域：仅仅在部分无效，也就是 begin end 里无效。

# 申明 DECLARE 变量名 类型;DECLARE 变量名 类型 DEFAULT 值;# 赋值 #形式一 SET 局部变量名 = 值;# 另一种形式 SET 局部变量名:= 值;# 另一种形式 SELECT @局部变量名:= 值;# 形式二：SELECT INTO 来赋值 SELECT 字段 INTO 变量名 FROM 表;# 应用 SELECT 局部变量名;

七、存储过程和函数

1、简略介绍

存储过程和函数相似 Java 的办法。

存储过程和函数定义：当时通过编译并存储在数据库中的一段 sql 语句的汇合。

益处：

  • 简化利用开发人员的很多工作
  • 缩小数据在数据库和应用服务器之间的传输
  • 进步了数据处理的效率

2、存储过程

2.1 创立存储过程

CREATE PROCEDURE 存储过程名(参数列表)BEGIN    存储过程体（一组 SQL 语句）;END;

留神：

  • 参数列表蕴含三局部：参数模式、参数名、参数类型
# 举例 IN stuname VARCHAR(20);

  • 参数模式：

    • IN：该参数能够作为输出，也就是须要调用方传入值
    • OUT：该参数能够作为输入，也就是该参数能够作为返回值
    • INOUT：该参数既能够作为输出，又能够作为输入，既须要调用方传入值，又能够作为返回值
  • 如果存储过程体仅仅只有一句话，BEGIN END 能够省略，存储过程体种的每条 sql 语句都须要加分号结尾，存储过程的结尾能够应用 delimiter 从新设置
# 语法 DELIMITER 完结标记 #案例 DELIMITER $

2.2 调用存储过程

# 语法 CALL 存储过程名(实参列表);

2.3 案例

有点难，案例记录一下！！

  • 一般类型参数的存储过程
# 往 admin 表里插入五条记录 #写存储过程 DELIMITER $CREATE PROCEDURE myp1()BEGIN    INSERT INTO admin(username,`password`) VALUES         ('zhangsan1','001'),        ('zhangsan2','002'),        ('zhangsan3','003'),        ('zhangsan4','004'),        ('zhangsan5','005');END $# 应用存储过程 CALL myp1()$
  • 带 in 模式参数的存储过程
# 依据女神名，查问对应的男神信息 #写存储过程 DELIMITER $CREATE PROCEDURE myp2(IN beautyName VARCHAR(20))BEGIN     SELECT bo.*    FROM boys bo    RIGHT JOIN beauty b ON bo.id = b.boyfriend_id    WHERE b.name = beautyName;END $# 应用存储过程 CALL myp2('柳岩')$
# 用户是否登录胜利 DELIMITER $CREATE PROCEDURE myp3(IN username VARCHAR(20),IN `password` VARCHAR(20))BEGIN    DECLARE result INT(20) DEFAULT '';# 申明并初始化        SELECT count(*) INTO result #赋值    FROM admin    WHERE admin.username = username    AND admin.password = `password`;        SELECT IF(result>0,' 胜利 ',' 失败)';# 判断是否胜利 END $# 调用 CALL myp3(' 张三 ','0001')$
  • 带 out 模式参数的存储过程
# 依据女神名，查问对应的男神信息 #写存储过程 DELIMITER $CREATE PROCEDURE myp5(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20))BEGIN     SELECT bo.boyName INTO boyName    FROM boys bo    RIGHT JOIN beauty b ON bo.id = b.boyfriend_id    WHERE b.name = beautyName;END $# 应用存储过程 SET @bName$CALL myp5('柳岩',@bName)$SELECT @bName$
  • 带 inout 模式参数的存储过程
# 传入 a 和 b，最终 a 和 b 都翻倍并返回 #写存储过程 CREATE PROCEDURE myp8(INOUT a INT,INOUT b INT)BEGIN    SET a = a*2;    SET b = b*2;END $# 应用存储过程 SET @m=10$SET @n=20$CALL myp8(@m,@n)$# 查问后果 SELECT @m，@n $

2.4 删除存储过程

不能进行批改存储过程外面的内容，如果想批改，那就删掉原来的，再创立新的。

# 语法 DROP PROCEDURE 存储过程名;# 一次只能删除一个

2.5 查看存储过程

# 语法 SHOW CREATE PROCEDURE 存储过程名;

3、函数

存储过程和函数的区别：

  • 函数只能有一个返回，存储过程能够又任意个，0 个也行，多个也行
  • 增删改比拟适宜存储过程，查问一个值比拟适宜函数

3.1 创立函数

# 创立语法 CREATE FUNCTION 函数名(函数名) RETURNS 返回类型 BEGIN    函数体 END

参数列表蕴含两局部：参数名、参数类型；函数更靠近于 Java 中的办法。

函数体：必定有 return 语句，如果没有会报错，如果 return 语句没放在函数体最初也不报错，然而没任何意义。

如果存储过程体仅仅只有一句话，BEGIN END 能够省略，存储过程的结尾能够应用delimiter。

3.2 调用语法

SELECT 函数名(参数列表)

3.3 案例

  • 无参有返回的
# 返回公司的员工个数 #创立函数 CREATE FUNCTION myf1() RETURNS INTBEGIN    DECLARE c INT DEFAULT 0;    SELECT COUNT(*) INTO c    FROM employees;    RETURN c;END $# 调用函数 SELECT myf1()$
  • 有参有返回的
# 依据员工名返回它的工资 CREATE FUNCTION myf2(empName VARCHAR(20)) RETURNS DOUBLEBEGIN    SET @sal=0;# 定义一个用户变量    SELECT salary INTO @sal# 赋值    FROM employees    WHERE last_name = empName;        RETURN @sal;END $# 调用函数 SELECT myf2('k_ing');

3.3 查看函数

# 语法 SHOW CREATE FUNCTION 函数名;

3.4 删除函数

# 语法 DROP FUNCTION 函数名;

八、流程控制结构

1、构造分类

程序构造：程序从上向下顺次执行；

分支构造：程序能够从多条门路中抉择一条去执行；

循环构造：程序在满足肯定条件的根底上，反复执行一段代码；

2、分支构造

2.1 if 函数

能够实现简略的双分支。能够利用在任何中央。

#语法 IF(表达式 1，表达式 2，表达式 3)# 如果 表达式 1 成立，返回表达式 2 的值，否则返回表达式 3 的值（三目运算符）

2.2 case 构造

状况 1：相似于 switch 语句，个别用于等值判断

# 语法 CASE 变量 | 表达式 | 字段 WHEN 要判断的值 THEN 返回的值 1[或语句 1;]WHEN 要判断的值 THEN 返回的值 2[或语句 2;]...ELSE 要返回的值 n[或语句 n;]END CASE;

状况 2：相似于多重 if，个别用于实现区间判断，看到底在哪个区间

# 语法 CASE WHEN 要判断的条件 1 THEN 返回的值 1[或语句 1;]WHEN 要判断的条件 2 THEN 返回的值 2[或语句 2;]...ELSE 要返回的值 n[或语句 N;]END CASE;

可作为表达式，嵌套在其余语句中应用，能够放在任何中央，begin end 中或 begin end 的里面；也能够作为独立的语句中应用，只能放在 begin end 中。

2.3 if 构造

实现多重分支。只能利用在 begin end 中。

# 语法 IF 条件 1 THEN 语句 1;ELSEIF 条件 2 THEN 语句 2;...【ELSE 语句 n;】END IF;

3、循环构造

3.1 简略介绍

分类：while、loop、repeat。

循环管制：iterate 相似于 Java 中的 continue，完结以后循环，进行下一次循环；leave 相当于 Java 中的 break

3.2 while 循环

# 语法【标签：】WHILE 循环条件 DO     循环体;END WHILE【标签】;

案例

# 批量插入，插入到 admin 表多条记录 CREATE PROCEDURE pro_while1(IN insertCount INT)BEGIN    DECLARE i INT DEFAULT 1;    a:WHILE i <= insertCount DO    INSERT INTO admin(username,`password`) VALUES(CONCAT('Jame'+i),'666');    SET i = i + 1;    END WHILE a;END $CALL pro_while1(100)$

含 leave 的语句

# 批量插入，插入到 admin 表多条记录, 插 20 条完结 CREATE PROCEDURE pro_while1(IN insertCount INT)BEGIN    DECLARE i INT DEFAULT 1;    a:WHILE i <= insertCount DO        INSERT INTO admin(username,`password`) VALUES(CONCAT('Jame'+i),'666');        IF i>=20 THEN LEAVE a;        END IF;        SET i = i + 1;    END WHILE a;END $CALL pro_while1(100)$

3.3 loop 循环

# 语法【标签：】LOOP    循环体;END LOOP【标签】;

能够用来模仿简略的死循环。

3.4 repeat 循环

# 语法【标签：】REPEAT 循环体；UNTIL 完结循环的条件 END REPEAT【标签】;

正文完
mysql
发表至： mysql
2021-09-11
 0
关于java:我还不懂什么是分布式事务
关于mysql:MySQL-主从复制
关于mysql:DTCC-2021-黄东旭从-DB-到-DBaaS数据库技术的当前和未来
关于mysql:mysql分库分表