锐客网

  1. 首页 > it技术 > >

MySQL基础

MySQL基础
文章图片

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_nameValue character_set_clientutf8#那么如下结果 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); #结果为2SELECT 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) lenFROM employees GROUP BY LENGTH(last_name) HAVING COUNT(*) > 5; SELECT COUNT(*)AS 数量,LENGTH(last_name) lenFROM 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 FROMbeauty,boys WHEREboyfriend_id = boys.id;

添加有效的连接条件就可以避免，那么添加条件可以分为：
  1. sql92标准：仅支持内连接和外连接。
  2. sql99标准：内连接，外连接（左外 + 右外），交叉连接。
分类：
  • 内连接
    • 等值连接
    • 非等值连接
    • 自连接
  • 外连接
    • 左外连接
    • 右外连接
    • 全外连接
  • 交叉连接
2.2 sql92语法 （1）等值连接 多表等值连接为多表的交集部分，n表连接至少要n-1个连接条件。
#员工名和部门名对应查询SELECTlast_name,department_name FROMemployees,departments WHEREemployees.`department_id` = departments.`department_id`; #查询员工名，工种号，工种名SELECTlast_name,employees.job_id,job_title #这里有歧义，用表名.字段名来表示FROMemployees,jobsWHEREemployees.`job_id` = jobs.`job_id`;

如果表连接字段总是重复，两表容易有歧义，所以可以使用别名。
SELECTe.last_name,e.job_id,j.job_titleFROMemployees e,jobs jWHEREe.`job_id` = j.`job_id`;

（2）非等值连接 也就是上面的等于条件变为不等于条件。
#查询员工的工资和工资等级SELECTe.`salary`,j.`grade_level`FROMemployees e,job_grades jWHEREe.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal`;

（3）自连接 连接的就是自己。
#查询员工名和上级员工名SELECTe.`last_name`,f.`last_name`FROMemployees e,employees fWHEREe.`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）内连接：等值、非等、自连接
#查询员工名和上级员工名SELECTe.`last_name`,f.`last_name`FROMemployees eINNER JOINemployees fONe.`manager_id` = f.`employee_id`;

（2）左外连接和右外连接
#查询员工名和上级员工名#左外连接SELECTe.`last_name`,f.`last_name`FROMemployees eLEFT JOINemployees fONe.`manager_id` = f.`employee_id`; #右外连接SELECTe.`last_name`,f.`last_name`FROMemployees eRIGHT JOINemployees fONe.`manager_id` = f.`employee_id`;

拿A匹配B，也就是B join A，那么A表为主表，B表为从表，A中所有的数据都会显示，从表中匹配不到的用null去匹配。
left join：左边的是主表，右边的是从表；
right join：右边的是主表，左边的是从表；
（3）全外连接 mysql这里不支持。只是演示一下语法。
SELECTb.*,bo.*FROMbeauty b,FULL OUTER JOINboys boONb.`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的部门中所有员工的姓名SELECTlast_nameFROMemployeesWHEREdepartment_id IN(SELECT DISTINCTdepartment_idFROMdepartmentsWHERElocation_id IN (1400,1700))

  • 行子查询：一行多列或多行多列
#查询员工编号最小且工资最高的员工信息#列子查询方式SELECT*FROMemployeesWHEREemployee_id = (SELECTMIN(employee_id)FROMemployees)ANDsalary = (SELECTMAX(salary)FROMemployees)#行子查询方式SELECT*FROMemployeesWHERE(employee_id,salary) =(SELECTMIN(employee_id),MAX(salary)FROMemployees)

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

3.4 from后面
#查询每个部门的平均工资的工资等级SELECTag_dep.*,g.`grade_level`FROM ESELECTAVG(salary) ag,department_idFROMemployeesGROUP BYdepartment_id) ag_depINNER JOINjob_grades gONag_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 表名;

【MySQL基础】这样就是整张表的数据记录都删了，不能恢复，需要非常慎重。
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且 dm=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 表名；

MySQL基础
文章图片

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 是最高的事务隔离级别，在该级别下，事务串行化顺序执行，可以避免脏读、不可重复读与幻读。但是这种事务隔离级别效率低下，比较耗数据库性能，一般不使用。
MySQL基础
文章图片

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()BEGININSERT 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))BEGINSELECT bo.*FROM boys boRIGHT JOIN beauty b ON bo.id = b.boyfriend_idWHERE b.name = beautyName; END $#使用存储过程CALL myp2('柳岩')$

#用户是否登录成功DELIMITER $CREATE PROCEDURE myp3(IN username VARCHAR(20),IN `password` VARCHAR(20))BEGINDECLARE result INT(20) DEFAULT ''; #声明并初始化SELECT count(*) INTO result #赋值FROM adminWHERE admin.username = usernameAND admin.password = `password`; SELECT IF(result>0,'成功','失败)'; #判断是否成功END $#调用CALL myp3('张三','0001')$

  • 带out模式参数的存储过程
#根据女神名，查询对应的男神信息#写存储过程DELIMITER $CREATE PROCEDURE myp5(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20))BEGINSELECT bo.boyName INTO boyNameFROM boys boRIGHT JOIN beauty b ON bo.id = b.boyfriend_idWHERE 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)BEGINSET 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 INTBEGINDECLARE c INT DEFAULT 0; SELECT COUNT(*) INTO cFROM employees; RETURN c; END $#调用函数SELECT myf1()$

  • 有参有返回的
#根据员工名返回它的工资CREATE FUNCTION myf2(empName VARCHAR(20)) RETURNS DOUBLEBEGINSET @sal=0; #定义一个用户变量SELECT salary INTO @sal#赋值FROM employeesWHERE 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)BEGINDECLARE i INT DEFAULT 1; a:WHILE i <= insertCount DOINSERT 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)BEGINDECLARE i INT DEFAULT 1; a:WHILE i <= insertCount DOINSERT 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【标签】;

    推荐阅读


    上一篇：spring JsonView解决循环对象引用遇到报错

    下一篇：聊聊|聊聊 SAP 产品 UI 上的消息显示机制