MySQL基础知识

数据库相关概念

1. DB：数据库，保存一组有组织的数据的容器。

2. DBMS：数据库管理系统，又称为数据库软件（产品），用于管理DB中的数据。

3. SQL：结构化查询语言，用于和DBMS通信的语言。

数据类型

整型

插入时默认是有符号的，如果要插入无符号，后面加UNSIGNED关键字 如 t2 INT UNSIGEND

浮点型

定点型

浮点型在数据库中存放的是近似值，而定点型在数据库存放的是精确值，不会丢失精度。定点数以字符串形式存储。

decimal(m,d) 参数m是总个数，d是小数位。

字符串

查询速度: char > varchar > text

char：定长，效率高。一般用于固定长度的表单提交数据存储，例如：身份证号，手机号，电话，密码等。char长度不足时，在右边使用空格填充，而varchar值保存时只保存需要的字符数。

varchar：不定长，效率偏低，内容开头用1到2个字节表示实际长度(长度超过255时需要2个字节)，因此最大长度不能超过65535。

nvarchar (存储的是Unicode数据类型的字符)不管是一个字符还是一个汉字，都存为2个字节，一般用作中文或者其他语言输入，这样不容易乱码； varchar存储汉字是2个字节，其他字符存为1个字节varchar适合输入英文和数字。

text：不需要指定存储长度，能用varchar就不用text。

二进制数据（BLOB）

二进制数据类型可存储任何数据，如图像、多媒休、文档等。

BLOB和TEXT存储方式不同，TEXT以文本方式存储，英文存储区分大小写；而Blob是以二进制方式存储，不区分大小写。

日期时间类型

若定义一个字段为timestamp，这个字段里的时间数据会随其他字段修改的时候自动刷新。所以这个数据类型的字段可以存放这条记录最后被修改的时间。

SQL的语言分类

• DQL（Data Query Language）：数据查询语言。如：select

• DML（Data Manipulate Language）：数据操作语言。如insert、update、delete

• DDL（Data Define Language）：数据定义语言。如：create、drop、alter

• TCL（Transaction Control Language）：事务控制语言。如：commit、rollback

基本命令

启动

启动服务：service mysqld start

关闭服务：service mysqld stop

启动客户端： mysql -uroot -p

数据库操作

SHOW DATABASES;
CREATE DATABASE db_name;
USE db_name;
DROP DATABASE db_name; 

表操作

创建表：create table user（id int,name varchar(10)）;

清空表数据：truncate table user;

查看表结构：desc table_name

查询

SELECT DISTINCT vent_id
FROM products;

SELECT prod_name
FROM products
LIMIT 0,5; #返回从0行开始的5行数据

SELECT pro_name
FROM products
ORDER BY prod_name DESC; #DESC降序 默认ASC升序

过滤

SELECT vend_id,prod_name
FROM products
WHERE vend_id <> 1004;

SELECT prod_name,prod_price
FROM products
WHERE prod_price BETWEEN 5 AND 10;

SELECT prod_name
FROM products
WHERE pro_name IS NULL;

SELECT prod_name,prod_price
FROM products
WHERE vend_id=1002 OR vend_id=1003 AND prod_price>=10;
#AND 优先级大于 OR

SELECT prod_name,product_price
FROM products
WHERE vend_id IN (1001,1002)
ORDER BY prod_name;

IN操作符效率比OR操作符高。IN最大的用处是可以包含其他SELECT语句。

%：任意多个字符  _：任意单个字符
SELECT prod_id,product_name
FROM products
WHERE prod_name LIKE '%jet%';

如果要查询第二个字符为_的字段，需要使用转义字符 ：

LIKE '_\_%'; 或者

LIKE '_任意字符_%' ESCAPE '任意字符';

正则表达式

https://www.runoob.com/mysql/mysql-regexp.html

函数

字符串函数

• concat（） 拼接字符

• substr（） 截取子串 如 ：截取从索引开始的字符串（包括索引本身） 截取从索引开始后的x位字符（包括索引本身） SUBSTR('WWSSXX',1,3) -->WWS

• instr（） 返回子串第一次出现的索引找不到返回0 如：INSTR('wwxxx','x') -->3

• upper（） 将字符转换成大写

• lower（） 将字符转换成小写

• trim（） 去前后指定的空格或字符 如： TRIM('AA' FROM 'AAAAAwangAAAzichaoAAAA') -->AwangAAAzichao

• ltrim（） 去左边空格

• rtrim（） 去右边空格

• replace（） 替换 如：REPLACE('今天吃馒头馒头','馒头'，'鸡腿') --> 今天吃鸡腿鸡腿

• lpad（） 左填充 如： LAPD('yinsusu',10,'*') --> ***yinsusu

• rpad（） 右填充

• length（） 获取字节个数 （utf-8一个英文字母占1字节，一个汉字占3字节）

注意：MySQL索引从1开始

数学函数

• round（） 四舍五入

• rand（） 生成0~1之间的随机数

• floor（） 向下取整

• abs（） 返回一个数的绝对值

• ceil（） 向上取整 如： CEIL(-1.001) -->-1 CEIL(1.001)-->2

• mod（） 取余

• truncate（） 小数点后保留x位 如：TRUNCATE(1.69999,1) -->1.6

日期函数

• now（） 当前系统日期+时间 如： 2022-02-16 15:36:16

• curdate（） 当前系统日期 如： 2022-02-16

• curtime（） 当前时间 如： 15:36:48

• str_to_date（） 将字符转换成日期 如：STR_TO_DATE('9-13-1999','%m-%d-%Y') --> 1999-09-13

• date_format（） 将日期转换成字符 如：SELECT DATE_FORMAT('2018-06-06','%Y年%m月%d日');

• YEAR,MONTH,DAY... 获取指定部分的年 月 日... 如： select year('2022-06-06'); --》2022

• DATEDIFF('参数1'，'参数2') 返回参数2和参数1的相差天数 如：SELECT DATEDIFF('2008-12-30','2008-12-29') --->1

流程控制函数

• if

IF(10>5,'大','小') --> 大

• case

case 要判断的字段或表达式

when 常量1 then 要显示的值1或语句1

when 常量2 then 要显示的值2或语句2

else 要显示的值n或语句n

end

聚集函数

• sum（） 求和

• max（） 最大值

• min （）最小值

• avg （）平均值

• count（） 计数

补充

分组

单独使用group by 没有意义，它只能显示出每组记录的第一条，需要搭配聚集函数使用

SELECT vend_id,COUNT(*) 
FROM products
GROUP BY vend_id;

除了聚集函数，SELECT语句中的每个列必须在GROUP BY字句中给出。

特点：

• 可以按单个字段分组；也可以按多个字段分组，多个字段之间用逗号隔开（相当于把多个字段值相同作为一组）

• 分组筛选分为两种情况：

  • 分组前筛选 ： 针对原始表，在group by的前面，使用where+group by关键字。

  • 分组后的筛选 ：针对分组查询后的结果集，使用having 关键字。

• 可以支持排序

SELECT vend_id,COUNT(*) AS num_prods
FROM products
WHERE prod_price>=10
GROUP BY vend_id
HAVING COUNT(*)>=2;

sql语句的执行顺序：

1. from 表1

2. 连接类型 join 表2 (即先同表1进行笛卡尔乘积)

3. on 连接条件（在上一步基础上进行筛选）

4. where语句对筛选后的表用条件加以筛选

5. 分组查询（group by，having）

6. select语句

7. order by 排序

8. limit 分页显示

多表连接查询

• 内连接：等值连接、非等值连接、自连接（把自己的表当多张表使用）

• 外连接：左外连接（左边为主表）、右外连接（右边为主表）、全外连接

• 交叉连接：笛卡尔乘积（如果连接条件省略或无效 则会出现笛卡尔乘积）

子查询

一条查询语句中又嵌套了另一条完整的select语句，其中被嵌套的select语句，称为子查询或内查询；在外面的查询语句，称为主查询或外查询。

1、子查询都放在小括号内
2、子查询放在条件的右侧
3、子查询优先于主查询执行，主查询使用了子查询的执行结果
4、子查询根据查询结果的行数不同分为以下两类：
    ① 标量子查询
      结果集只有一行一列
      一般搭配单行操作符使用：> < = <> >= <= 
  
    ② 列子查询
      结果集有一列多行
      一般搭配多行操作符使用：any、all、in、not in

分页查询

1.起始条目索引从0开始

2.limit子句放在查询语句的最后

3.公式：select * from  表 limit （（page-1）*sizePerPage,sizePerPage）;

(当每页条目数为10,page=1,第一页起始索引为0，page=2,第二页起始索引为10，page=3,起始索引为20)
假如:
每页显示条目数sizePerPage
要显示的页数 page

联合查询（适用值没有关联的表连接）

select 字段|常量|表达式|函数 【from 表】 【where 条件】 union 【all】
select 字段|常量|表达式|函数 【from 表】 【where 条件】 union 【all】
select 字段|常量|表达式|函数 【from 表】 【where 条件】 union  【all】
.....
select 字段|常量|表达式|函数 【from 表】 【where 条件】

1、多条查询语句的查询的列数必须是一致的

2、多条查询语句的查询的列的信息类型几乎相同

3、union代表去重，union all代表不去重(有可能表中有李雷,男;李雷,male，那么默认结果会去掉第二个李雷)

例子：查询中国用户中男性的信息以及外国用户中男性的用户信息

select id,cname,csex from t_ca where csex='男'

union

select t_id,tName,tGender from t_ua where tGender='male'

分析：结果是两张表的合并，需要用到连接，虽然两张表各个字段虽然信息类型一样，但是值并没有任何关联的地方，所以只能用联合查询。

列操作

插入数据

MySQL用单条INSERT语句处理多个插入比使用多条INSERT语句快。

INSERT操作当有很多索引需要更新，会很耗时，并且它会降低等待处理SELECT语句的性能。我们可以通过降低INSERT语句的优先级：INSERT LOW_PRIORITY INTO

INSERT INTO customers(cust_name,cust_city)
VALUES('JHON','TOM'),('GZ','WZC');

更新数据

如果用UPDATE语句更新多行，并且在更新这些行中的某一行出错，则整个UPDATE操作取消。可以使用IGNORE关键字使发生错误继续更新：UPDATE IGNORE ..

UPDATE customers
SET cust_city=NULL
WHERE cust_id=1001;

删除数据

DELETE FROM customers
WHERE cust_id=1006; #表使用别名时，要在delete和from加上别名

如果要删除表中所有数据，应该使用TRUNCATE TABLE语句，它比DELETE更快，

表操作

创建表

CREATE TABLE IF NOT EXISTS stuinfo(
	stuId INT NOT NULL AUTO_INCREMENT,
	stuName VARCHAR(20),
	gender CHAR,
	bornDate DATETIME);

修改表

ALTER TABLE 表名 ADD（表级约束）|MODIFY（列级约束）|DROP|CHANGE（修改字段名） COLUMN 字段名 【字段类型】;

语法：ALTER TABLE 表名 ADD|MODIFY|DROP|CHANGE COLUMN 字段名 【字段类型】;
	
	1.修改表名
	ALTER TABLE stuinfo RENAME  studentinfo;
	
	2.修改字段名
	ALTER TABLE studentinfo CHANGE COLUMN sex（旧字段） gender（新字段） CHAR;  要加上新字段名的数据类型
	
    3.修改字段类型和添加列级约束
	ALTER TABLE studentinfo MODIFY COLUMN borndate DATE ;
	
	4.添加字段和表级约束（外键约束和主键约束是表级约束）
	ALTER TABLE studentinfo ADD COLUMN email VARCHAR(20);
	
	5.删除字段
	ALTER TABLE studentinfo DROP COLUMN email;
  
    6.删除约束
    ALTER TABLE products DROP FOREIGN KEY vend_id;

删除表

DROP TABLE [IF EXISTS] studentinfo;

复制表

	1.仅仅复制表的结构
		CREATE TABLE copy1 LIKE author;
    
	2.复制表的结构+数据
		CREATE TABLE copy2 
		SELECT * FROM author;
    
	3.只复制部分数据
		CREATE TABLE copy3
		SELECT id,au_name
		FROM author
		WHERE nation='中国';
    
	4.只复制表的某些字段
		CREATE TABLE copy4
		SELECT id
		FROM author
		WHERE 1=2;  不满足所以数据不会跟过去

常用约束

NOT NULL 非空
DEFAULT 默认，保证该字段有默认值
UNIQUE  保证该字段具有唯一性，但可以为null，但是只能有一个null，null也是唯一值。
PRIMARY KEY 主键，保证该字段具有唯一性，且非空的。
FOREIGN KEY 外键，用于限制两个表的关系，保证该字段的值必须来自主表的相关关联列的值。/*外键一定是其它表的主键*/
        可以分为列级约束和表级约束，表级约束就是在最后一行写上约束。
        除了not null和default不支持，其它约束支持。
        FOREIGN KEY只能添加表级约束才有用。
	CREATE TABLE stuinfo(
	id INT,
	stuname VARCHAR(20),
	gender CHAR(1),
	age INT,
	PRIMARY KEY(id),
	CONSTRAINT uq UNIQUE(stuname),#CONSTRAINT:自定义索引名，但是主键自定义不了索引名
	CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)
	);
	主键和唯一的区别：都能保证唯一性；
				   都能进行组合；两个字段作为整体-->主键|唯一
				   主键不会允许有空值，唯一允许；
				   主键一个表只能由1个，唯一可以有多个；
	外键特点：1.要求在从表设置外键关系
			 			2.从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称则无要求（连接是值有关联且类型要一致或兼容，名称也是无要求）
			 			3.主表的关联列必须是主键或唯一(建议少用)
			 			4.插入数据时，先插入主表，再插入从表
			   			删除数据时，先删除从表，再删除主表

视图（呈现数据，安全）

视图（View）是一种虚拟存在的表。视图并不在数据库中实际存在，他是通过一张或者多张基表进行关联查询后组成一个虚拟的逻辑表。查询视图，本质上是对表进行关联查询，视图只是一个查询结果，当基表的数据发生变化时，视图里面的数据也会跟着发生变化。

视图的应用

• 重用SQL语句

• 保护数据。可以给用户授予表的特定部分访问权限而不是整张表的访问权限。

• 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。

视图的限制

• 与表一样，视图必须唯一命名

• 视图不能创建索引，也不能有关联的触发器或默认值

• 禁止在查询中使用ORDER BY子句

语法

CREATE VIEW  视图名 ：创建视图
SHOW CREATE VIEW viewname ：查看创建视图的语句
DROP VIEW viewname ： 删除视图
SELECT 字段/* FROM viewname(WHERE 筛选条件) ： 查看视图的数据

视图逻辑的更新：
CREATE OR REPLACE VIEW test_v7
AS
	SELECT last_name FROM employees
	WHERE employee_id>100;

视图数据的更新同时会对原始表数据更新！最好不要更新视图的数据，这样就违背了创造视图的本意，语法上和sql语句没区别

例子

CREATE VIEW productcustomers AS
SELECT cust_name,orders
FROM customers,order
WHERE orders.cust_id=customers.cust_id;

存储过程（处理数据）

存储过程（Stored Procedure）是在大型 数据库系统中，一组为了完成特定功能的SQL 语句集，它存储在数据库中，一次编译后永久有效，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。

应用

• 把复杂处理进行封装，简化复杂的操作

• 提高性能，存储过程比单独SQL语句更快

1、无返回无参

2、仅仅带in类型。该参数可以作为输入，也就是该参数需要调用方传入值。

3、仅仅带out类型，该参数可以作为输出，也就是该参数可以作为返回值。

4、带inout，该参数既可以作为输入也可以作为输出。

注意：in、out、inout都可以在一个存储过程中带多个

存储过程更新

• 创建存储过程

MySQL支持IN (传递给存储过程)、OUT (从存储过程传出)和INOUT (对存储过程传入和传出)类型的参数。

• 删除存储过程

drop procedure 存储过程名（一次删除一个）

• 查看存储过程信息

SHOW CREATE PROCEDURE 存储过程名

• 调用存储过程

call 存储过程名(参数值)

注意：存储过程体中可以有多条sql语句，每条sql语句的结尾必须加分号，如果仅仅一条sql语句，则可以省略begin end

#查看所有全局变量
SHOW GLOBAL VARIABLES;
#查看满足条件的部分系统变量
SHOW GLOBAL VARIABLES LIKE '%char%';
#查看指定的系统变量的值
SELECT @@global.autocommit;
#为某个系统变量赋值
SET @@global.autocommit=0;
SET GLOBAL autocommit=0;

#查看所有会话变量
SHOW SESSION VARIABLES;
#查看满足条件的部分会话变量
SHOW SESSION VARIABLES LIKE '%char%';
#查看指定的会话变量的值
SELECT @@autocommit;
SELECT @@session.tx_isolation;
#为某个会话变量赋值
SET @@session.tx_isolation='read-uncommitted';
SET SESSION tx_isolation='read-committed';

方式一:声明并赋值自动初始化
SET @变量名=值;

方式二:一般用于赋表 中的字段值
SELECT 字段名或表达式 INTO 变量
FROM 表;

使用:
select @变量名;

#声明：
declare 变量名 类型 【default 值】;（要加类型！！）

#赋值：
方式一：一般用于赋简单的值
SET 变量名=值;
SET 变量名:=值;

方式二：一般用于赋表 中的字段值
SELECT 字段名或表达式 INTO 变量
FROM 表;

游标

在存储过程和函数中可以使用光标对结果集进行循环的处理。光标的使用包括光标的声明、OPEN、FETCH 和 CLOSE，其语法分别如下。

• 声明光标：（使用关键字  FOR）

DECLARE cursor_name CURSOR FOR sql语句 ;

• OPEN 光标：（使用光标得先打开）

OPEN cursor_name ;

• FETCH 光标：（拿一次光标就会抓取当前行的数据赋值给变量，然后光标向下移动一行。）

FETCH cursor_name INTO var_name [, var_name] ...

• CLOSE 光标：（使用完光标得关闭）

CLOSE cursor_name ;

create procedure pro_test12()
begin
  DECLARE id int(11);
  DECLARE name varchar(50);
  DECLARE age int(11);
  DECLARE salary int(11);
  DECLARE has_data int default 1;
  
  DECLARE emp_result CURSOR FOR select * from emp;
  DECLARE EXIT HANDLER FOR NOT FOUND set has_data = 0;#当拿不到数据触发句柄-->将has_data设置为0并退出
  
  open emp_result;
  
  repeat
    fetch emp_result into id , name , age , salary;
    select concat('id为',id, ', name 为' ,name , ', age为 ' ,age , ', 薪水为: ', salary);
    until has_data = 0#循环直到has_data为0   ----没有分号
  end repeat;
  
  close emp_result;
end;

触发器

触发器是与表有关的数据库对象，指在 insert/update/delete 之前或之后，触发并执行触发器中定义的SQL语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性 , 日志记录 , 数据校验等操作 。

创建

create trigger trigger_name 

before/after  insert/update/delete

on tbl_name 

for each row   -- 行级触发器(只支持)

begin
	trigger_stmt ; #触发器的逻辑
end;

创建 update 型触发器，完成更新数据时的日志记录 :

create trigger emp_logs_update_trigger
after update 
on emp 
for each row 
begin

  insert into emp_logs (id,operation,operate_time,operate_id,operate_params) values(null,'update',now(),new.id,concat('修改前(id:',old.id,', name:',old.name,', age:',old.age,', salary:',old.salary,') , 修改后(id',new.id, 'name:',new.name,', age:',new.age,', salary:',new.salary,')'));                                                                      
end;

数据库事务

操作步骤

1、开启事务

2、编写事务的一组逻辑操作单元（多条sql语句）

3、提交事务或回滚事务

事务的分类

1. 隐式事务，没有明显的开启和结束事务的标志

比如
insert、update、delete 每条语句本身就是一个事务,因为开启自动提交事务功能。

2. 显式事务，具有明显的开启和结束事务的标志

	1、开启事务
	即取消自动提交事务的功能
	set autocommit=0
	
	2、编写事务的一组逻辑操作单元（多条sql语句组成一个事务）
	select
	insert
	update
	delete...
	3、提交事务或回滚事务（回滚事务！=回滚事务后自动提交事务！）
	commit;提交事务
	rollback;回滚事务

使用到的关键字

savepoint 节点名；设置保存点
rollback to 节点名；回滚到保存点
	
  SET autocommit=0;
	DELETE FROM admin where id=2;
	SAVEPOINT a;
	DELETE FROM admin WHERE id=1;
	ROLLBACK to a;

索引

MySQL官方对索引的定义为：索引（index）是帮助MySQL高效获取数据的数据结构（有序）。

1. 普通索引

2. 主键索引

3. 唯一索引

4. 组合索引

5. 全文索引

语法

• 用ALTER TABLE 用来创建普通索引、唯一索引或主键索引

如：ALTER TABLE table_name ADD INDEX index_name(字段名);

• 用CREATE INEDX创建普通索引唯一索引

如：CREATE INDEX index_name ON table_name(字段名);

• 用DROP删除索引

如：DROP INDEX index_name ON table_name;

• 用SHOW查看索引

如：SHOW INDEX FROM table_name;

左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址（注意逻辑上相邻的记录在磁盘上也并不是一定物理相邻的）。为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找快速获取到相应数据。

一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。索引是数据库中用来提高性能的最常用的工具