关于mysql:MySQL基础

0次阅读

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

留神：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

# 语法
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;

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 子句除外。

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

分类：

单行函数：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 '数字';

传一组值，变成一个值。

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

分组函数特点：

sum 和 avg 只反对数值型；
count、max、min 可用于解决任何类型；
所有分组函数均疏忽 null 值，间接这一列就不计算，比方计算平均值，100 集体有 20 集体有 null 值，那么计算 avg 除的是 80；
所有分组函数都能够和 distinct 搭配实现去重运算；
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;

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

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

# 查问部门的员工个数大于二的部门 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 子句反对单个字段分组，也反对多个字段分组。

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

SELECT `name`,boyname FROM beauty,boys;

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

所以条件如下：

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

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

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

分类：

内连贯
- 等值连贯
- 非等值连贯
- 自连贯
外连贯
- 左外连贯
- 右外连贯
- 全外连贯
穿插连贯

（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`;

# 语法：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;

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

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

分类

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

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

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

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

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

标量子查问：个别搭配着单行操作符：>、<、=、>=、<=、!=

# 谁的工资比阿贝尔高 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    )

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

# 查问每个部门的平均工资的工资等级 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;

SELECT EXISTS (SELECT employee_id from employees);

# 语法：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 。

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

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

# 语法：查问语句 1UNION 查问语句 2;

什么时候用呢？

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

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

根本插入语句：

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

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

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

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

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

两种形式比照

第一种形式反对多行插入，第二种不反对；
第一种形式反对子查问，第二种不反对；

INSERT INTO employees(employee_id,last_name,email) SELECT(100,'James','110101');# 第一种形式

批改单表中记录的语句：

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

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

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

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

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

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 删除能够回滚

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

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

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

# 语法：DROP DATABASE IF EXISTS 库名;

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

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

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

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

怎么设置数据类型有无符号：默认是有符号的。设置成无符号，创立表时在表字段前面加上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');

DESCRIBE 表名；

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

一共有 6 大束缚：

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

束缚增加的分类：

列级束缚：下面几个都能够写，语法都反对，但 外键束缚 没有成果；
表级束缚：除了非空和默认，其余的都反对；

# 列级束缚和表级束缚所在位置 CREATE TABLE 表名(字段名 字段类型 列级束缚,    字段名 字段类型,    表级束缚)

# 应用列级形式增加束缚 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,# 非空)

# 应用表级形式增加束缚 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，多了还是算反复	能够有多个	√

外键的特点

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

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

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

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

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

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

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

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

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

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

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

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

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

# 查看存储引擎 SHOW ENGINES;

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

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

# 敞开主动提交 SET AUTOCOMMIT = 0;

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

delete 和 truncate 事务的区别

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

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

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

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

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

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

# 语法 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;

视图其实就是虚构表，和一般表一样应用，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 '张 %';

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

视图的益处：

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

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

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

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

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

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

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

不过有非凡状况：

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

视图和表的区别

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

零碎变量：
- 全局变量
- 会话变量
自定义变量：
- 用户变量
- 局部变量

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

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

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

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

变量是本人定义的。

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

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

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

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

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

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

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

益处：

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

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

留神：

参数列表蕴含三局部：参数模式、参数名、参数类型

# 举例 IN stuname VARCHAR(20);

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

# 语法 DELIMITER 完结标记 #案例 DELIMITER $

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

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

一般类型参数的存储过程

# 往 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 $

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

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

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

存储过程和函数的区别：

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

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

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

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

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

SELECT 函数名(参数列表)

无参有返回的

# 返回公司的员工个数 #创立函数 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');

# 语法 SHOW CREATE FUNCTION 函数名;

# 语法 DROP FUNCTION 函数名;

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

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

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

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

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

状况 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 中。

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

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

分类：while、loop、repeat。

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

# 语法【标签：】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)$

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

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

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

正文完

mysql

发表至： mysql

2021-09-11

0

关于mysql:MySQL中的join查询

关于mysql:面试必问10-道经典-MySQL-面试题

关于mysql:MySQL-1022-cant-write-duplicate-key-in-table

关于mysql:面试官咱们来聊一聊mysql主从延迟

关于sap:SAP-Commerce-Cloud-Product-Review-的添加逻辑

关于mysql:MySQL基础

MySQL 根底

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

1、查问语句

1.1 根本语句

1.2 where——筛选

1.3 order by——排序

1.4 常见函数——单行函数

（1）字符函数

（2）数学函数

（3）日期函数

（4）其余函数

（5）流程管制函数

1.5 常见函数——分组函数

1.6 group by——分组

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

2、多表连贯查问

2.1 连贯条件及分类

2.2 sql92 语法

（1）等值连贯

（2）非等值连贯

（3）自连贯

2.3 sql99 语法

（1）内连贯：等值、非等、自连贯

（2）左外连贯和右外连贯

（3）全外连贯

（4）穿插连贯

3、子查问

3.1 子查问的分类

3.2 where 或 having 前面

3.3 select 前面

3.4 from 前面

3.5 exists 前面（相干子查问）

4、分页查问

5、联结查问

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

1、插入语句

2、批改语句

3、删除语句

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

1、简略介绍

2、库的治理

2.1 库的创立

2.2 库的批改

2.3 库的删除

3、表的治理

3.1 表的创立

3.2 表的删除

3.3 表的批改

3.4 表的复制

3.5 数据类型

3.6 显示表构造

4、束缚

4.1 简略介绍

4.2 增加列级束缚

4.3 增加表级束缚

4.4 批改表时增加束缚

4.5 批改表时删除束缚

5、标识列

5.1 创立表时创立标识列

5.2 设置步长

5.3 批改表时创立标识列

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

1、简略介绍

1.1 事务的 ACID 准则

2、事务的创立

2.1 应用事务的步骤

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

3.1 数据库的事务隔离级别

3.2 默认事务隔离级别

3.3 实际操作

3.4 保留点

五、视图

1、简略介绍

2、视图的创立

3、视图的批改

4、视图的删除

5、视图的查看

6、视图的更新

六、变量