MYSQL基础学习笔记

千里之行，始于足下

目录

• 关系型数据库与非关系型数据库
• MySQL服务端的登录、退出与使用：
• SQL语句的分类
  • 1、DQL（数据查询语言）：
  • 2、DML（数据操作语言）：
  • 3、DDL（数据定义语言）：
  • 4、TCL（事物控制语言）：
  • 5、DCL（数据控制语言）：
• SQL的基本使用：
• 一、基本的SELECT语句的使用：
• 二、WHERE子句的操作符：
• 三、WHERE子句中使用正则表达式（REGEXP）查询：
• 四、函数的使用：
• 五、连接查询的方式
• 六、建表常用的数据类型
• 七、SQL表中常见约束
• 事务的理解与事务的四大特性：
  • 1、事务：
  • 2、特性：
  • 3、四大隔离级别：
  • 4、并发事务处理带来的问题：
• 视图：
• 存储过程/存储函数/游标/触发器：
  • 1、理解：
  • 2、语法：
  • 3、案例：
  • 4、调用格式：
  • 5、存储函数：
  • 6、游标：
  • 7、触发器：
• Mysql的流程控制结构——实现多分支
  • 1、case结构
  • 2、if结构
  • 3、循环结构（存储过程中使用）

 

---

正文

 

回到顶部

关系型数据库与非关系型数据库

 

1、关系型数据库（SQL）：主要依据“一对一、一对多、多对多”等关系模型来创建的数据库。

2、非关系型数据库（NoSQL）：主要是基于“列模型、键值对模型、文档类模型”等非关系模型的数据库。

 

 

回到顶部

MySQL服务端的登录、退出与使用：

 

1、登录：mysql –h 主机名 –u用户名 –p密码

2、退出：exit

3、使用：

（1）、进入mysql，在命令行中输入：mysql –uroot –p密码

（2）、查看mysql中有哪些个数据库：show databases;

（3）、使用一个数据库：use 数据库名称;

（4）、新建一个数据库：CREATE DATABASE IF NOT EXISTS 数据库名

（5）、查看指定的数据库中有哪些数据表：show tables;

（6）、建表：CREATE TABLE [IF NOT EXISTS] t_表名｛字段名1 数据类型 [约束] , .... ｝

（7）、查看表的结构：desc 表名

（8）、清空表（不能回滚）：TRUNCATE TABLE 表名

（9）、删除表：DROP TABLE [IF EXISTS] 数据表1 [, 数据表2, …, 数据表n];

（10）、删除数据库：DROP DATABASE IF EXISTS 数据库名;

注：mysql不区分大小写

 

 

回到顶部

SQL语句的分类

 

回到顶部

1、DQL（数据查询语言）：

select语句

语法：

（1）、查询：

select <字段名> [as 别名] from <表或视图名>（ join <表名> on <连接条件> ） 
　　　　　　where <查询条件> group by <分组字段> having <额外添加字段条件> 
　　　　　　order by <字段名（升序asc / 降序desc）> limit（ startIndex ，length ）；

回到顶部

2、DML（数据操作语言）：

insert、update、delete语句

语法：

（2）、新增：

insert into  t_表名 （字段名1，字段名2...） values （值1，值2...）；

（3）、修改：

update t_表名 set 字段名1 = 值1 ，字段名2 = 值2 ...where 条件；

（3）、删除：

delete from t_表名 where 条件；

回到顶部

3、DDL（数据定义语言）：

drop、create、alter

语法：

（1）、创建：

create tables IF NOT EXISTS t_表名｛字段名1 数据类型 [约束]， 字段名2 数据类型 [约束] .... ｝；

（2）、删除：

drop table IF EXISTS t_表名；

回到顶部

4、TCL（事物控制语言）：

事务提交commit，事务回滚rollback

回到顶部

5、DCL（数据控制语言）：

grant授权，revoke撤销权限

 

回到顶部

SQL的基本使用：

回到顶部

一、基本的SELECT语句的使用：

1、使用关键字DISTINCT去除重复行：

SELECT DISTINCT name FROM t_users;

2、空处理函数IFNULL(expr1,expr2)过滤参与运算字段：

所有数据库规定只要有null参与的运算结果都为null；

IFNULL(expr1,expr2)：若expr1不是NULL，IFNULL()返回expr1，否则它返回expr2；

SELECT IFNULL(t_users.number,1) FROM t_users;

3、着重号（`  `）解决冲突：

保证表中的字段、表名等没有和保留字、数据库系统名或常用方法名冲突

SELECT * FROM `STUDENT`;

4、LEAST(值1，值2，...，值n)

两个或多个参数的情况下，返回最小值

5、GREATEST(值1，值2，...，值n)

两个或多个参数时，返回值为最大值。假如任意一个自变量为NULL，则GREATEST()的返回值为NULL

 

回到顶部

二、WHERE子句的操作符：

=	等于
<>（！=）	不等于
>	大于
>=	大于等于
<	小于
<=	小于等于
between A and B	在指定的两个值之间（包含边界）
is null （is not null）	空值（非空）
like（not like）	模糊查询： 1、%代表零个或多个字符(任意个字符) 2、_代表一个字符
in	等于值列表中的一个
and	逻辑并
or	逻辑或
not in	逻辑否

 

回到顶部

三、WHERE子句中使用正则表达式（REGEXP）查询：

1、查询以特定字符或字符串开头的记录：‘^’

在t_user表中，查询name字段以字母‘a’开头的记录

SELECT * FROM t_user WHERE name REGEXP '^a';

2、查询以特定字符或字符串结尾的记录：‘$’

在t_user表中，查询name字段以字母‘z’结尾的记录

SELECT * FROM t_user WHERE name REGEXP 'z$';

3、替代字符串中的任意一个字符： "."

在t_user表中，查询name字段值包含字母‘a’与‘z’且两个字母之间只有一个字母的记录

SELECT * FROM t_user WHERE name REGEXP 'a.z';

 

回到顶部

四、函数的使用：

MySQL常使用的函数大概有四类：时间函数、数学函数、字符函数、控制函数。参考

注：

1、分组函数：count（）、avg（）、sum（）、max（）、min（）

2、分组函数不能在where后面执行：分组函数通常与group by一起使用，在group by后面执行，而group by是在where后面执行的

 

回到顶部

五、连接查询的方式

1、内连接inner join on（无主副之分）：等值连接、非等值连接、自连接

2、外连接（有主副之分）：

　　（1）、left join on：左外连接（左边主表）、

　　（2）、right join on：右外连接（右边主表）

3、全连接full join on

4、合并查询结果

　　（1）、去除重复记录：UNION关键字

　　（2）、不去除重复记录：UNION ALL关键字

SELECT name,... FROM t_table1

UNION

SELECT name,... FROM t_table2

5、SQL99语法新特性

　　（1）、自然连接NATURAL join ：自动查询两张连接表中所有相同的字段

SELECT id, name FROM employees e NATURAL JOIN departments d;

　　（2）、JOIN...USING：指定数据表里的具体同名字段进行等值连接

SELECT employee_id, department_name FROM employees e

JOIN departments d

USING (department_id);

 

回到顶部

六、建表常用的数据类型

类型	描述
CHAR（长度）	定长字符串，存储空间大小固定，适合作为 主键或外键
VACHAR（指定长度）	变长字符串，存储空间等于实际数据空间
DOUBLIE(有效数字位数，小数位)	数值型
FLOAT(有效数字位数，小数位)	数值型
INT(或INTEGER) (长度)	整型
BIGINT(长度)	长整型
DECIMAL	定点数类型
DATE	日期型 年月日（格式'YYYY-MM-DD'）
DATETIME	日期型 年月日 时分秒 毫秒
TIME	日期型 时分秒
BLOB	Binary Large OBject（二进制大对象）
CLOB	Character Large OBject（字符大对象）
其它……

 

回到顶部

七、SQL表中常见约束

1、非空约束（not null）：规定某个字段不能为空

2、唯一约束（unique）：同一个表可以有多个唯一约束。

3、主键约束（primary key）：相当于唯一约束与非空约束的组合，不允许为空，每个表有且最多只允许一个主键约束；

4、外键约束（foreign key）：构建于一个表的两个字段或是两个表的两个字段之间的参照关系，保证一个或两个表之间的参照完整性；

5、检查约束（check）：MySQL 不支持 check 约束，但可以使用 check 约束，而没有任何效果；

6、缺省约束（Default）：在插入数据时某列如果没指定其他的值,那么会将默认值添加到新记录。

7、解释字段（COMMENT）：没有任何实际含义，专门用来解释字段的含义

 

回到顶部

事务的理解与事务的四大特性：

回到顶部

1、事务：

一组完整的DML业务逻辑单元

回到顶部

2、特性：

原子性、一致性、隔离性、持久性

（1）、原子性（Atomicity）

原子性是指事务是一个不可分割的工作单位，事务中的操作要么都成功，要么都失败。

（2）、一致性（Consistency）

事务必须使数据库从一个一致性状态变换到另外一个一致性状态。

（3）、隔离性（Isolation）

事务的隔离性是指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

（4）、持久性（Durability）

持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响。

回到顶部

3、四大隔离级别：

读未提交——READ UNCOMMITED、读已提交(Oracle默认)——READ COMMITED、

可重复读(MySQL默认)——REPEATABLE READ、串行化读——SERIALZABLE

（1）、查看当前的隔离级别:

SELECT @@tx_isolation;

（2）、设置当前 mySQL 连接的隔离级别：

set transaction isolation level 隔离级别；

（3）、设置数据库系统的全局的隔离级别：

set global transaction isolation level 隔离级别;

回到顶部

4、并发事务处理带来的问题：

更新丢失，脏读，不可重复读，幻读

 

回到顶部

视图：

1、视图是一个虚拟表（非真实存在），其本质是根据SQL语句获取动态的数据集，并为其命名，用户使用时只需使用名称即可获取结果集，并可以将其当作表来使用。

2、视图可以提高检索效率，隐藏表的实现细节，只对视图对象进行CRUD操作，保护数据，提高安全性；

（1）、创建视图：

create view 视图名 as select语句；

（2）、删除视图：

drop view if exists 视图名;

 

回到顶部

存储过程/存储函数/游标/触发器：

回到顶部

1、理解：

　　存储过程是一个预先编译过SQL语句的代码块，存储过程可以设置用户的操作权限，具有较强的安全性，并且每次使用时只需要调用存储过程，降低网络的通信量，提高通信速率，但存储过程的编码复杂度更高，可移植性较差，调试困难，不适合高并发的场景，只能说，针对某些项目比较好用。（阿里开发规范强制禁止使用）。

回到顶部

2、语法：

DELIMITER 新的结束标记符 #可省

CREATE PROCEDURE 存储过程名( IN | OUT | INOUT 参数名 参数类型，...)

[characteristics ...]

BEGIN

存储过程体

END 新的结束标记符

DELIMITER ；#恢复默认结束符

注：

（1）、参数

　　1）、IN ：表示输入参数。

　　2）、OUT ：表示输出参数。

　　3）、INOUT ：表示当前参数既可以为输入参数，也可以为输出参数。

（2）、characteristics包括

　　1）、LANGUAGE SQL：表示当前系统支持的语言为SQL。

　　2）、{ DETERMINISTIC  |  NOT DETERMINISTIC }：

DETERMINISTIC：表示相同的输入会得到相同的输出。

NOT DETERMINISTIC：表示相同的输入可能得到不同的输出。

　　3）、{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } ：指明子程序使用SQL语句的限制。

CONTAINS SQL：表示当前存储过程的子程序包含SQL语句，但是并不包含读写数据的SQL语句；

NO SQL：表示当前存储过程的子程序中不包含任何SQL语句；

READS SQL DATA：表示当前存储过程的子程序中包含读数据的SQL语句；

MODIFIES SQL DATA：表示当前存储过程的子程序中包含写数据的SQL语句。

　　4）、SQL SECURITY { DEFINER | INVOKER } ：执行当前存储过程的权限，即指明哪些用户能够执行当前存储过程。

DEFINER：表示只有当前存储过程的创建者或者定义者才能执行当前存储过程；

INVOKER：表示拥有当前存储过程的访问权限的用户能够执行当前存储过程。

　　5）、COMMENT '输入的字符串'

（3）、存储过程体

中间包含了多个语句，每个语句都以分号作为结束符。

　　1）、DECLARE：声明变量

　　2）、SET：对变量进行赋值。

　　3）、SELECT… INTO：把从数据表中查询的结果存放到变量中，也就是为变量赋值。

回到顶部

3、案例：

（1）、查看最高工资：

CREATE PROCEDURE show_max_salary()

LANGUAGE SQL

NOT DETERMINISTIC

CONTAINS SQL

SQL SECURITY DEFINER

COMMENT '查看最高薪资'

BEGIN

SELECT MAX(salary) FROM emps;

END //

DELIMITER ;

（2）、将最低薪资通过OUT参数“ms”输出：

DELIMITER //

CREATE PROCEDURE show_min_salary(OUT ms DOUBLE)

BEGIN

SELECT MIN(salary) INTO ms FROM emps;

END //

DELIMITER ;

（3）、实现累加运算，计算 1+2+…+n：

DELIMITER //

CREATE PROCEDURE `add_num`(IN n INT)

BEGIN

DECLARE i INT;

DECLARE sum INT;

SET i = 1;

SET sum = 0;

WHILE i <= n DO

SET sum = sum + i;

SET i = i +1;

END WHILE;

SELECT sum;

END //

DELIMITER ;

回到顶部

4、调用格式：

CALL 存储过程名(实参列表)

（1）、调用in模式的参数：

CALL add_num ('值');

（2）、调用out模式的参数：

SET @ms;

CALL show_min_salary (@ms);

SELECT @ ms;

（3）、调用inout模式的参数：

SET @name=值;

CALL sp1(@name);

SELECT @name;

注：

MySQL中的系统变量以两个“@”开头，用户变量以一个“@”开头

回到顶部

5、存储函数：

（1）、语法：

CREATE FUNCTION 函数名(参数名 参数类型,...)

RETURNS 返回值类型

[characteristics ...]

BEGIN

RETURN (函数体)   #函数体中肯定有 RETURN 语句

END

（2）、对比：

	关键字	调用语法	返回值
存储过程	PROCEDURE	CALL存储过程()	有0个或多个
存储函数	FUNCTION	SELECT函数()	只能是一个

回到顶部

6、游标：

（1）、理解：

对结果集中的每一条记录进行定位，充当指针的作用，mysql中游标只适用于存储过程以及函数。

（2）、语法：

　　1）、定义游标：

declare 游标名 cursor for select语句;

　　2）、打开游标：

open 游标名;

　　3）、获取结果：

fetch 游标名 into 变量名[,变量名];

　　4）、关闭游标：

close 游标名;

回到顶部

7、触发器：

确保操作的原子性（要么全部成功，要么全部失败），但存在最大问题是可读性差。

（1）、语法：

CREATE TRIGGER 触发器名称

{ BEFORE（注：事件前触发）| AFTER（注：事件后触发）}

{INSERT | UPDATE | DELETE触发事件} ON 表名

FOR EACH ROW

触发器执行的语句块;

 

回到顶部

Mysql的流程控制结构——实现多分支

回到顶部

1、case结构

（1）、语法一：

case 表达式或字段

when 值1 then 语句1

when 值2 then 语句2

…

else 语句n

end [case]

（2）、语法二：

case

when 条件1 then 语句1

when 条件2 then 语句2

…

else 语句n

end [case]

回到顶部

2、if结构

语法一：

if(条件表达式，表达式1，表达式2)；

如果条件表达式成立，返回表达式1，否则返回表达式2

语法二（存储过程中使用）：

if 条件1 then 语句1;

elseif 条件2 then 语句2;

…

else 语句n;

end if;

回到顶部

3、循环结构（存储过程中使用）

（1）、while： 类似java中的while循环

语法格式：

【名称】 while 循环条件 do

循环体;

end while 【名称】;

（2）、loop ：需要在循环体中添加结束的条件，不然就是死循环。

语法格式：

【名称】 loop

循环体;

end loop 【名称】;

 （3）、repeat：类似java中的do while

语法格式：

【名称】 repeat

循环体;

until 结束循环的条件

end repeat 【名称】；

注：

循环控制语句：

1）、iterate关键字：类似于java中的continue，结束本次循环，继续下一次循环。

2）、leave关键字：类似于java中的break，跳出循环，执行之后的语句。