乐趣区

关于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_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【标签】;
退出移动版