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;#这个也能计算胜利，后果213SELECT 123 + '90';#后果是123SELECT 123 + 'abv';#后果是0SELECT 'abc' + 'abv';#后果为nullSELECT 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            Valuecharacter_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);    #后果为2SELECT floor(1.24);    #后果为1
  • truncate：截断
#从小数后1位截断，也就是1.6SELECT 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 salaryEND AS '新工资'FROM employees;-------------------------------------------------#另一个示例select case 3when 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~81
9~162
17~243
25~324
33~648
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【标签】;