后端_数据库_Mysql

数据库概览

什么是数据库

按照数据结构来组织,储存和管理数据的仓库,每个数据库都有不同的API用于创建,访问,管理,搜索和复制所保存的数据,存储在表的行和列中.

基本概念

SQL：结构化查询语言。用于存取数据以及查询、更新和管理关系数据库系统,也是数据库脚本文件的扩展名。
主键：唯一标识每行的列。外键：列A在表1，表2中，如果列A在表1中是主键，A为表2的外键。
DCL:数据库控制语言,是用来设置或更改数据库用户或角色权限的语句，如grant,deny,revoke等。
DDL:数据库定义语言,主要是用在定义或改变表的结构，数据类型，表之间的链接和约束等初始化工作上，如CREATE、ALTER、DROP等。
DML:数据操纵语言：主要用来对数据库的数据进行增删改查操作，如 SELECT、UPDATE、INSERT、DELETE 。

常见的数据库

MYSQL,MongoDB,ORacle,SQL Server,Reids,memcache

MYSQL8.0安装(win10 x64):

方式一:(下面的方法可能不准确).
先下载解压:https://cdn.mysql.com//Downloads/MySQLInstaller/mysql-installer-community-8.0.19.0.msi
环境变量path添加一个指向mysql的bin目录.
在根目录添加my.ini,再管理员权限进入bin $mysqld --initialize --console注意保存密码
再$mysqld --install [服务名] 然后$net start mysql     

Navicat:

一套数据库开发管理工具,支持链接MySQL,Oracle等多种数据库,可以快速轻松地创建,管理数据和维护数据库.
Windows 10环境搭建: https://www.jianshu.com/p/5f693b4c9468?mType=Group   软件+注册机

MYSQL

Mysql概览

什么是Mysql

属于Oracle公司,是一个开源的关系型数据库管理系统,分为社区版本和企业版.

基本目录

 --bin目录,存储可执行文件.
 --data目录,存储数据文件.
 --docs, 文档
 --include目录,储存包含的头文件
 --lib目录,储存库文件
 --share,错误消息和字符集文件.

数据库的基本操作

$net start mysql      启动MYSQL服务
$net stop mysql       关闭服务器
$mysql -uroot -proot -P3306 -h127.0.0.1 输入密码 登录        
$exit; $quit; $\q;    退出
$mysql -uroot -proot --prompt   修改提示符
$SELECT VERSION();    查看服务器版本   
$SELECT NOW();        查看当前日期 
$SELECT USER();       显示当前用户; 
$CREATE DATABASE dabasename;   创建数据库  
$DROP DATABASE DATABASENAME;   删除数据库
$SHOW DATABASES;       查看所有数据库 
$use databasename       使用某数据库
$SHOW WARINGS;        查看警告   
$show status          查看状态
$show grants          查看授权用户的安全权限
$show errors           查看警告
$show create database databasename  查看创建数据库的语句
$show create table tablename    查看创建数据表的语句

数据库的数据类型

 数据库数据类型的整型,浮点型,日期时间型,字符型.
 整型的数据类型:  TINYINT,   SMALLINT,    MEDIUMINT,   INT,    BIGINT.
 浮点型的数据类型: FLOAT,    DOUBLE.
 日期时间型的数据类型:YEAR   TIME   DATE  DATETIME   TIMESTAMP,
 字符型的数据类型:CHAR,   VARCHAR, TINYTEXT,  TEXT,  MEDIUMTEXT,  LONGTEXT,  ENUM,  SET.

DML

数据表的增删改查

#数据库名为soapeye,表为user
-----
#查看表
$use soapeye;
$show tables;

#查看表定义
$desc emp
#获取表中所有列的信息
$show columns from soapeye /describe tablename

创建表

$CREATE TABLE table_name ( 
a VARCHAR(20),
b FLOAT(8,2) 
UNSIGNED);

$create table user {
  cust_id INT NOT NULL AUTO_INCREMENT,
  cust_name CHAR(50) NOT NULL,
  PRIMARY KEY(cust_id)
  }ENGINE=INNODB;

允许NULL值，则说明在插入行数据时允许不给出该列的值，而NOT NULL则表示在插入或者更新该列数据，必须明确给出该列的值；
DEFAULT表示该列的默认值，在插入行数据时，若没有给出该列的值就会使用其指定的默认值；
PRIMARY KEY用于指定主键，主键可以指定一列数据，而可以由多列数据组合构成，如`PRIMARY KEY(cust_id,cust_name)；
ENGINE用于指定引擎类型。常见的引擎类型有这些：（1）InnoDB是一个支持可靠的事务处理的引擎，但是不支持全文本搜索；（2）MyISAM是一个性能极高的引擎，它支持全文本搜索，但是不支持事务处理；（3）MEMORY在功能上等同于MyISAM，但由于数据存储在内存中，速度很快（特别适合于临时表）；
在创建表的时候可以使用FOREIGN KEY来创建外键，即一个表中的FOREIGN KEY指向另一个表中PRIMARY KEY。外键FOREIGN KEY用于约束破坏表的联结动作，保证两个表的数据完整性。同时也能防止非法数据插入外键列，因为该列值必须指向另一个表的主键.

#删除表的全部数据,不删除表
$truncate table user;

#删除表的制定数据
$delete from user name = '李华';

#删除表与其数据
$drop table user;

#重命名表
rename table1 to table,table2 to oldtable;

#修改表
#添加列
$alter table user add liename varchar(20);

#删除列
alter table user drop column liename;

#修改列
alter table user moddify column lieming comment 'xiugai';

#表字段的修改,添加,删除,改名
#修改表字段
$alter table tablename modify newname varchar(20);
#添加表字段
$alter table tablename add column age int(3)
#删除表字段
$alter table tablename drop column age
#字段改名
$alter table table change age age1 int(4)

#添加主键
 alter table user add primary key(moc);
 
#删除主键
#alter table usera drop primary key;

#添加外键
ALTER TABLE '从表' ADD constraint '外键名称\_FK\_从表\_主表' FOREIGN KEY '从表'('外键字段') REFERENCES '主表'('主键字段');

#删除外键
ALTER TABLE databases\_example.user\_info DROP FOREIGN KEY '外键名称';

#修改默认值
ALTER TABLE databases_example.user_info ALTER add_col SET DEFAULT 1000;

#删除默认值
ALTER TABLE databases_example.user_info ALTER add_col DROP DEFAULT;


#查看数据表结构
$ SHOW COLUMNS FROM user;

#插入记录 
$INSERT user VALUES('字段1','字段2');

#插入指定记录
$INSERT table_name  (字段) VALUES('s');

#查看记录 
$SELECT * FROM a;

#插入条件记录
NULL,字段值可以为空,NOT NULL,字段值禁止为空.  $name VARCHAR(20) NOT NULL,自动编号: AUTO_INCREMENT 自动编号且必须与主键组合使用,默认情况下,起始值为1,每次的增量为1.保持唯一性.

#主键约束
      每张数据表只能存在一个主键,主键保证机理的唯一性,主键自动为NOT NULL
添加主键约束 alert TABLE

数据表的查询

基本查询语句

*   根据过滤条件查询表中的单列或者多列或者全部列的信息**SELECT FROM WEHERE**：`SELECT cust_id,cust_name FROM customers WHERE cust_id=10086`；其中过滤条件操作符有：=，<>,!=,<,<=,>,>=,BETWEEN AND,IS NULL；
*   为查询出的某一列信息去重**DISTINCT**：`SELECT DISTINCT cust_name FROM customers`；
*   限制单列查询结果的行数：`SELECT cust_name FROM customers LIMIT 5`；LIMIT后跟一个数值，表示从第0行开始取，共取5行数据；如果LIMIT 5，5表示从第5行（数据库中实际第6行记录）开始取，共取5行数据。注意：数据是从第0行开始计数的；
*   **ORDER BY**子句取一个或者多个列，据此对输出进行排序：`SELECT cust_id,cust_name FROM customers ORDER BY cust_id DESC, cust_name`;
*   **IN**操作符用来指定条件范围，范围中的每个条件都可以进行匹配：`SELECT cust_id, cust_name FROM customers WHERE cust_id IN (1000,2000)`。另外，NOT操作符可以和IN操作符配合使用，用于表示检索出不符合条件的所有数据；
*   **LIKE**操作符用来表明模糊查询，与之配合使用的通配符有\*\*%\*\*，%表示任何字符出现任何次数；`_`，`_`表示只能匹配一个字符：`SELECT cust_id,cust_name FROM customers WHERE cust_name LIKE '%happy%'`；
*   使用分组查询并可以满足一定的分组过滤条件**GROUP BY HAVING**。如检索总计订单金额大于等于50的订单号和订单总金额，并按总金额进行排序：`SELECT order_num,SUM(quantity*item_price) AS order_total FROM orderitems GROUP BY order_num HAVING SUM(quantity*item_price)>=50 ORDER BY order_total`
*   **WHERE和HAVING的比较**。WHERE是行级过滤，而HAVING是组级过滤。被WHERE过滤掉的数据不会出现在分组中。WHERE中通配符以及多个WHERE子句的连接同样适用于HAVING子句；
*   GROUP BY的使用注意事项： （1）GROUP BY子句中可以嵌套分组（即通过多个列进行分组`GROUP BY cust_id, cust_name`），但是进行数据汇总时，是在最后规定的分组上进行；（2）GROUP BY子句中列出的每个列都必须是检索列或者是有效的表达式。（3）如果有NULL值，将值NULL作为一个分组进行返回，如果有多行NULL值，它们将分为一组
*   嵌套其他查询中的查询，称之为**子查询**。执行过程由里向外，里层查询结果作为外层查询的条件：`SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id = 'happy')`。当然，多表的查询可以是用联结查询。

联合查询

*   内联结用又称之为内部联结，是基于两个表 之间的的相等测试。如果不加过滤条件，会造成“笛卡尔积”。`SELECT vend_name,prod_name,prod_price FROM vendors INNER JOIN products ON vendors.vend_id=products.vend_id`；同样可以使用WHERE进行多表联结查询，但是更推荐使用INNER JOIN等联结方式；
*   外部联结包括左外联结**LEFT JOIN**和右外联结**RIGHT JOIN**和全连接**FULL JOIN**。例如查询每个客户的订单数：`SELECT customers.cust_id,orders.orders_num FROM customers LEFT JOIN orders ON orders.cust_id =customers.cust_id`；LEFT JOIN 会全部返回左表数据，RIGHT JOIN会全部返回右表数据，FULL JOIN会将左右两个表的数据全部返回；
*   联结查询与聚集函数一起使用。如查询每个客户的订单数：`SELECT customers.cust_name,customers.cust_id,COUNT(orders.order_num) AS num_ord FROM customers INNER JOIN orders ON customers.cust_id=orders.cust_id GROUP BY customers.cust_id`；

组合查询

*   多个查询（SELECT）可以使用UNION将多个查询结果进行合并成一个结果集返回，**UNIO**N必须包含两个及两个以上的SELECT查询，并且每个传必须包含相同的列、表达式或聚集函数，数据类型不必完全相同，MySQL会进行隐式的类型转换。`SELECT vend_id,prod_id,prod_price FROM products WHERE prod_price>5 UINON SELECT vend_id,prod_id,prod_price FROM products WHERE vend_id IN (1001,1002)`;
    
*   **UNION**返回的是去重后的结果，如果不需要去重则可以使用**UNION ALL**；
    
*   可以多组合查询使用ORDER BY进行排序，但是是针对的最终的结果集进行排序，而不是其中单个SELECT查询进行排序，因此对于组合查询来说ORDER BY子句只有一个。`SELECT vend_id,prod_id,prod_price FROM products WHERE prod_price>5 UINON SELECT vend_id,prod_id,prod_price FROM products WHERE vend_id IN (1001,1002) ORDER BY vend_id`

使用函数对数据处理

*   拼接列名：`SELECT Concat (vendName,'(',vendCountry,')') FROM vendors ORDER BY vendName`；
    
*   执行算术表达式计算：`SELECT prodId, quantity,price, quantity`\*`price AS expandedPrice FROM orderItems`；
    
*   文本处理函数如Upper(),LTrim(),RTrim()等函数。比如使用Upper函数将文本转换成大写：`SELECT vendName, Upper(vendName) FROM vendors ORDER BY vendName`；
    
*   时间和日期处理函数，如Date(),Day()等。`SELECT custId, orderNum FROM orders WHERE Date(orderDate)='2015-09-01'`；
    
*   数值处理函数，如Abs(),Cos()等；
    
*   常用的聚集函数。如AVG(),COUNT(),MAX(),MIN()以及SUM()。`SELECT COUNT(*) AS numbers, MIN(prod_price) AS price_min, MAX(prod_price) AS price_max,AVG(prod_price) AS price_avg FROM products`；

插入表数据

*   向表中插入行数据可以使用INSERT INTO子句，更安全的方式是指定列名。`INSERT INTO customers (cust_name, cust_email) VALUES('happy','[e-mail]')`；在INSERT INTO子句中能够省略列名的前提条件是：该列可以允许定义为NULL值或者在定义该列时给出去了默认值；
*   如果插入多行数据可以将多组值用逗号进行分隔即可。`INSERT INTO customers (cust_name, cust_email) VALUES('happy','[e-mail]'),('smart','[e-mail]')`；
*   将查询出来的数据插入表中，可以使用INSERT SELECT语句。`INSERT INTO customers(cust_id,cust_contact) SELECT cust_id, cust_contact FROM customers WHERE cust_id>5`；其中SELECT中可以带WHERE过滤条件；INSERT SELECT通常被用于复制表数据

更新表数据

*   如果要更新表数据的话，使用UPDATE子句：`UPDATE customers SET cust_name ='happy',cust_email='[e-mail]' WHERE cust_id = 1001`；
*   **注意：如果不加WHERE条件指定到某一行的话，会更新表中某一列全部的数据**。

删除表数据

*   如果从表中删除数据的话，可以使用DELETE子句。`DELETE FROM customers WHERE cust_id = 10086`；删除的数据必定是表中行数据，而不是某一列。因此，与UPDATE子句相比，DELETE子句并不需要指定是哪一列，而仅仅只需要指定具体的表名即可；
*   **注意：如果不添加WHERE指定条件的话，会将整个表中所有行数据全部删除。另外，DELETE只是删除表中的数据，而不会删除表结构信息；**
*   如果想删除表中全部的数据，可以使用**TRUNCATE**，比DELETE删除效率更高；

SQL中关键字执行顺序

在SQL语句中每个关键字都会按照顺序往下执行，而每一步操作，会生成一个虚拟表，最后产生的虚拟表会作为执行的最终结果返回。下面的是常用的关键字的执行顺序：

(8)SELECT (9)DISTINCT<select_list>
(1)FROM <left_table>
(3)<join_type> JOIN <right_table>
(2)            ON <join_condition>
(4)WHERE <where_condition>
(5)GROUP BY<group_by_list>
(6)WITH{CUBE|ROLLUP}
(7)HAVING<having_condition>
(10)ORDER BY<order_by_list>
(11)LIMIT<limit_number>

• FROM：对FROM左边的表和右边的表计算笛卡尔积，产生虚表VT1；
• ON：对虚拟表VT1进行ON筛选，只有那些符合<join_condition>条件的行才会被记录在虚拟表VT2中；
• JOIN：如果是OUT JOIN，那么将保留表中（如左表或者右表）未匹配的行作为外部行添加到虚拟表VT2中，从而产生虚拟表VT3；
• WHERE:对虚拟表VT3进行WHERE条件过滤，只有符合<where_condition>的记录才会被放入到虚拟表VT4；
• GROUP BY：根据GROUP BY子句中的列，对虚拟表VT4进行分组操作，产生虚拟表VT5；
• CUBE|ROLLUP：对虚拟表VT5进行CUBE或者ROLLUP操作，产生虚拟表VT6；
• HAVING：对虚拟表VT6进行HAVING条件过滤，只有符合<having_condition>的记录才会被插入到虚拟表VT7中；
• SELECT：执行SELECT操作，选择指定的列，插入到虚拟表VT8中；
• DISTINCT：对虚拟表VT8中的记录进行去重，产生虚拟表VT9；
• ORDER BY：将虚拟表VT9中的记录按照<order_by_list>进行排序操作，产生虚拟表VT10；
• LIMIT：取出指定行的记录，产生虚拟表VT11，并将结果返回。

1

**选择：**select \* from table1 where 列\=“abc”

**插入：**insert into table1(field1,field2) values(value1,value2)

**删除：**delete from table1 where 范围

**更新：**update table1 set field1=value1 where 范围

**查找：**select \* from table1 where field1 like '%value1%'

**排序：**select \* from table1 order by field1,field2 \[desc\]

**总数：**select count as totalcount from table1

**求和**：select sum(field1) as sumvalue from table1

**平均：**select avg(field1) as avgvalue from table1

**最大：**select max(field1) as maxvalue from table1

**最小：**select min(field1) as minvalue from table1

FROM：指定了要查询的表

where:过滤表中数据的条件

group by:对过滤的数据进行分组

having:对上面已经分组的数据进行过滤的条件

select:查看结果集中的哪个列，或列的计算结果

order by :对查询结果进行排序

limit:取出指定行的数据,返给用户

比较运算符包括: = ,>,<,>=,<=,<>

逻辑运算符:NOT,AND,OR

确定范围运算符：BETWEEN AND, NOT BETWEEN AND

![1.png](/img/bVbEak5)

查询语句基本格式：SELECT 列名/\* FROM 表名 （WHERE 条件组合 ）:

查询所有学生: select \* from student;

查询部分条件: select Age,Name from student;

单一条件查询: select \* from student where Age>16;

区间查询:select \* from student where Age between 16 and 20;

集合方式查询:select \* from student where Age in (‘18’,’20’);

多条件查询: select \* from student where Sex=”男” and Age > 20;

分组查询: select Sex,count(\*) from student group by Sex;

分组后筛选查询: select Sex,count(\*) as Class from student group by Sex having Class >= 大一;

结果排序: select \* from student order by Class; 默认为正序排列(asc),倒序（desc）

子条件查询: select \* from student where Age=(select max(Age) from student);

分页查询: select \* from student order by Age limit0,2;

模糊查询: select \* from student where Name like ‘%a%’; like 关键字表示模糊，后面跟'单引号' %模糊的内容% '%a%’表示只要该字段含有a字 ‘a%’表示要模糊的字必须处于该字段的首位 ‘%a’表示要模糊的字必须处于该字段末位

存储过程

什么是存储过程？

存储过程简单来说，就是为了复用性或者实现复杂的业务功能，而保存的一条或多条MySQL语句的集合，可将其视为批文件；

为什么使用存储过程？

（1）通过把处理封装在容易使用的单元中，简化复杂的操作；（2）由于不要求反复建立一系列处理步骤，这保证了数据的完整性，如果所有的开发人员和应用程序都使用同一存储过程，则所使用的代码都是相同的；（3）简化对变动的管理。如果表名、列名或业务逻辑有变化，只需要更改存储过程的代码，使用它的开发人员甚至不需要知道这些变化，也就是具备了安全性；（4）提高了性能，因为使用存储过程比单独使用SQL语句要快；（5）存储过程可用来编写功能更灵活的代码。因此，存储过程的具备三个特性:**简单可复用、安全以及高性能**；

存储过程的缺点？

1.存储过程编写比基本的SQL语句更加复杂，需要更高的技能；（2）可能没有创建存储过程的权限，数据库管理员可能会限制创建存储过程的权限，允许用户使用存储过程，而不允许用户自由创建存储过程；

创建存储过程

创建存储过程。如需要统计用户订单总金额，如果该用户需要交税的话，订单总金额则需要再加上税费

 DELIMITER //
 CREATE PROCEDURE ordertotal(
 IN custid INT,
 IN taxable BOOLEAN,
 OUT ototal DECIMAL(8,2)
 )COMMENT 'obtain total order price'
 
 BEGIN
 
 /*declare variable for total*/
 DECLARE total DECIMAL(8,2);
 DECLARE taxrate INT DEFAULT 6;
 
 
 /*get the order total*/
 
 SELECT SUM(item_price*item_quantity) INTO total
 FROM customers
 WHERE cust_id = custid;
 
 /*is this taxable?*/
 
 IF taxable THEN
     SELECT total+(total/100*taxrate) INTO total;
 END IF;
 SELECT total INTO ototal;
 END //

有这样一些细节： 1. 使用CREATE PROCEDURE语句进行创建，（）圆括号中为存储过程的参数，其中参数类型有：1. IN类型，表示传递给存储过程；2. OUT 类型，表示存储过程返回的结果，在调用存储过程时需要传入@开始的变量；3. INOUT类型，表示在存储过程中可以传入和传出； 2. DECLARE用来声明一个变量，如这里的total，taxrate。注意MySQL中定义变量时都是变量名在前，数据类型在后。 3. 存储过程具体逻辑写在BEGIN END之间； 4. 将值赋给变量使用INTO关键字； 5. 由于存储过程中每个SQL语句中用；作为分隔符，会和单个SQL造成冲突，因此可使用DELIMITER重新定义分类符，如该例子中定义//为分隔符，自然存储过程结尾就用END //结尾，而不再是END。同时，分隔符//成对出现后，恢复到默认的";"作为分隔符；

执行存储过程

使用CALL子句执行存储过程，CALL子句接受存储过程的名称以及需要传递的参数

 CALL ordertotal(1,TRUE,@total);
 SELECT @total;

如果存储过程中定义了OUT类型的输入参数，那么在执行存储过程时需要传入变量，如这里@total，并且变量都是用@开始的。如果存储过程中没有参数的话，就用空圆括号表示即可，CALL ordertotal()；

删除存储过程

删除存储过程，可以使用DROP PROCEDURE子句。如DROP PROCEDURE ordertotal；

查询存储过程

• 显示创建一个存储过程的语句，可以使用SHOW CREATE PROCEDURE。如SHOW CREATE PROCEDURE ordertotal；
• 查询所有存储过程的状态，如果在定义存储过程中使用COMMENT添加注释，可以查看。同时可以LIKE进行过滤结果。如SHOW PROCEDURE STATUS LIKE '%order%';

视图操作

视图就是一张虚拟表（非真实存在），其本质是 sql 语句获取动态的数据集并为其命名，由于视图是虚拟表，无法对进行更新和删除，仅能做查询操作。

查询试图

--  语法格式：CREATE VIEW '视图名称' AS 'SQL语句'
CREATE VIEW user_id AS
    SELECT uid FROM databases_example.user_info;

视图使用

-- 由于视图是虚拟表，无法对进行更新和删除，仅能做查询操作
SELECT * FROM databases_example.user_id;

删除视图

-- 语法格式：DROP VIEW '视图名称'
DROP VIEW databases_example.user_id;

修改视图

-- 语法格式：DROP VIEW '视图名称'
DROP VIEW databases_example.user_id;

事务处理

什么是事务？

事务处理是用来维护数据库的完整性，它保证成批的MySQL操作要么完全执行，要么完全不执行。.
事务：是指一组SQL语句. 回退：是指撤销指定的SQL语句的过程；
提交：指将未存储的SQL语句的结果写入数据库表中；
保留点：指事务处理中设置的临时占位符，可以对它发布回退

创建执行事务

 START TRANSACTION;
 INSERT INTO customers (cust_name,item_price,item_quantity) VALUES ('1',5,18);
 SELECT * FROM customers;
 SAVEPOINT insertinto;
 INSERT INTO customers (cust_name,item_price,item_quantity) VALUES ('2',5,18);
 ROLLBACK TO insertinto;

执行结果为：插入数据('1',5,18)有效，因为，只会从保留点SAFEPOINT之后开始回退，也就是说保留点SAFEPOINT之前的SQL语句执行的结果仍然有效。

• STAET TRANSACTION用来表示下面的SQL语句集为一段事务；
• SAFEPOINT 用于指定保留点insertinto；
• ROLLBACK TO表示从指定保留点开始回退，也就是说保留点之前的SQL语句执行结果依然有效。如果仅仅使用ROLLBACK进行回退的话就表示从STAET TRANSACTION之后所有的SQL语句执行效果都会撤销；

MySQL提交（写或保存）操作是自动进行的，这称之为隐含提交。但是在事务处理块中，提交不会隐含进行，要使用COMMIT子句进行提交。如：

 START TRANSACTION;
 INSERT INTO customers (cust_name,item_price,item_quantity) VALUES ('1',5,18);
 INSERT INTO customers (cust_name,item_price,item_quantity) VALUES ('2',5,18);
 COMMIT;

采用COMMIT提交事务，如果两条SQL语句都执行成功，才会将数据都写入表中。

触发器

当某条SQL语句发生时，自动执行某些其他的SQL语句的时候就需要使用到触发器。触发器只能响应：DELETE，INSERT,UPDATE这三个特定操作。

创建触发器

创建触发器的四条信息：1.全局唯一的触发器名；2.触发器关联的表；3.触发器在何时执行（操作执行之前或者之后）4.触发器应该响应的活动（DELETE, INSERT或者UPDATE）；

创建Insert触发器

注意：1.在INSERT触发器代码内，可以引用一个名为NEW的虚拟表，可以用NEW来访问刚插入的行数据；2.在BEFORE INSERT触发器中，NEW中的值可以被更新；3.对于AUTO_INCREMENT列，NEW在INSERT执行之前包含0，在INSERT执行之后包含新的自定生成值。

-- create trigger    触发器名    触发时间(BEFORE|AFTER)    触发事件    on 表名    for each row
--      触发器触发的具体事务
-- 执行过程为：执行触发事件作用在某个表上前或后，触发器执行具体的事物
CREATE TRIGGER name BEFORE INSERT ON biaoname FOR EACH ROW
    BEGIN    SELECT user_id into @newuserid;

#执行触发器
INSERT INTO biaoname(uid, name, sex) VALUES('20','25',17);
select @newuserid;

创建delect触发器

DELETE触发器在DELETE语句执行之前或者之后，需要知道以下两点：

1. 在DELETE触发器代码内，可以引用一个名为OLD的虚拟表，来访问被删除的行；
2. OLD表中的数据只能读，不能被更新，而在INSERT触发器中，就可以通过NEW来更新被插入的行数据；

#针对user表，当删除一行数据时，返回被删除数据的`user_id`以及`user_name`
#基本上与创建INSERT触发器一样，只不过在DELETE触发器中只能使用OLD来访问被删除的行数据。
create trigger insertuser after delete on user
for each row
begin
   select old.user_name into @deleteusername;
   select old.user_id into @deleteuserid;

#调用
delete from user where user_id = 3;
select @deleteusername;
select @deleteuserid;

update触发器

UPDATE触发器在UPDATE语句执行之前或者之后执行，需要知道一下几点：

1. 在BEFORE UPDATE触发器中可以使用NEW和OLD来访问数据，而在AFTER UPDATE触发器中使用NEW来访问数据会报错，只能使用OLD来访问数据；
2. 在BEFORE UPDATE触发器中，NEW中的值可以被改变，即允许更改将用于UPDATE的数据；
3. OLD中的行数据只能读，不能被更新；

 /*创建UPDATE触发器*/
 
 DELIMITER //
 CREATE TRIGGER insertcustomers BEFORE UPDATE ON customers
 FOR EACH ROW 
 BEGIN 
 
 SELECT NEW.cust_name INTO @beforeupdate;
 SET NEW.cust_name = 'reset_name';
 SELECT OLD.cust_name INTO @afterupdate;
 END //
 
 /*调用UPDATE触发器*/
 UPDATE customers SET cust_name = 'happy' WHERE cust_id = 5;
 SELECT @beforeupdate;
 SELECT @afterupdate;

输出为@beforeupdate为‘happay’，而@afterupdate为'reset_name'。有这样一些细节：

1. NEW虚拟表中的数据可以更改，如这里采用 SET NEW.cust_name = 'reset_name';，将待更新的cust_name由“happy”变成了“reset_name”；
2. 在BEFORE UPDATE触发器中可以使用NEW和OLD来访问数据，而在AFTER UPDATE触发器中使用NEW来访问数据会报错；

删除触发器

删除触发器，可以使用 DROP TRIGGER语句，比如DROP TRIGGER chufaqiname;。触发器不能更新或者覆盖，如果要修改触发器，必须删除这个触发器。

索引

索引用于快速找出数据表中某一列中有一特定值的行。不使用索引，MySQL必须从第一条记录开始读完整个表，直到找出相关的行，表越大查询的时间相应就越长。索引的建立就是快速达到一个位置去搜索数据文件，而不必查看所有数据，将会节省大量时间。

普通索引

普通索引仅有一个功能：加速查询

-- 创建表的时候创建索引
USE databases_example;
CREATE TABLE index_table(
    nid INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
    name VARCHAR(20) NOT NULL ,
    email VARCHAR(30) NOT NULL ,
    -- 注意：对于创建索引时如果是 BLOB 和 TEXT 类型，必须指定length。
    -- create index ix_extra on in1(extra(32));
    INDEX ix_name(name)
);
-- 令一种创建索引的方式
CREATE INDEX ix_nid ON index_table(nid);
-- 删除索引
DROP INDEX ix_name ON index_table;
-- 查看创建的索引
SHOW INDEX FROM databases_example.index_table;

唯一索引

唯一索引有两个功能：加速查询 和 唯一约束（可含NULL）;也就是说表中只有一个索引，说明一下自增键也是索引的一种。

USE databases_example;
CREATE TABLE unique_table(
    nid INT NOT NULL AUTO_INCREMENT ,
    name VARCHAR(20) NOT NULL ,
    email VARCHAR(20) NOT NULL ,
    extra TEXT ,
    UNIQUE ix_nid(nid)

);
-- 另一种创建唯一索引的方法
CREATE UNIQUE INDEX ix_nid ON databases_example.unique_table(nid) ;
-- 查看创建的唯一索引
SHOW INDEX FROM databases_example.unique_table;
-- 删除唯一索引
DROP INDEX ix_name ON databases_example.unique_table;

主键索引

主键索引有两个功能：加速查询 和 唯一约束(不可含null)。

USE databases_example;
CREATE TABLE primary_table(
    nid INT NOT NULL AUTO_INCREMENT ,
    name VARCHAR(20) NOT NULL ,
    email VARCHAR(30) NOT NULL ,
    PRIMARY KEY (nid) ,
    INDEX ix_nid(name)
);
-- 查看创建的主键索引
SHOW INDEX FROM databases_example.primary_table;
-- 另一种创建方法
CREATE INDEX ix_name ON databases_example.primary_table(name);
-- 删除主键索引
ALTER TABLE databases_example.primary_table DROP PRIMARY KEY;
ALTER TABLE databases_example.primary_table MODIFY INT, DROP PRIMARY KEY;

组合索引

组合索引时将n个列组合成一个索引，其应用场景为：频繁的同事使用n列来进行查询，如：WHERE n1 = 'ALEX' AND n2 = 666。

USE databases_example;
CREATE TABLE mixture_table(
    nid INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
    name VARCHAR(20) NOT NULL ,
    email VARCHAR(20) NOT NULL
);
-- 创建索引
CREATE INDEX ix_nid_name ON mixture_table(nid, name);
-- 查看组合索引
SHOW INDEX FROM mixture_table;
-- 删除组合索引
DROP INDEX ix_nid_name ON mixture_table;

索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，而组合索引，即一个索引包含多个列。

创建索引

创建索引有两种方式，一种是直接利用CREATE INDEX进行创建，另外一种则是通过修改表结构来进行添加，则是利用ALTER TABLE语句。
使用CREATE INDEX

  CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
  [USING index_type]
  ON table_name (index_col_name,...)

UNIQUE|FULLTEXT|SPATIAL]分别表示唯一索引、全文索引、空间索引三种不同的索引类型。如果我们不指定任何关键字，则默认为普通索引。
index_name表示索引的名称，由用户自行定义，以便于以后对该索引进行修改等管理操作。
index_type表示索引的具体实现方式，在MySQL中，有两种不同形式的索引——BTREE索引和HASH索引。在存储引擎为MyISAM和InnoDB的表中只能使用BTREE，其默认值就是BTREE；在存储引擎为MEMORY或者HEAP的表中可以使用HASH和BTREE两种类型的索引，其默认值为HASH。
index_col_name表示需要创建索引的字段名称，我们还可以针对多个字段创建复合索引，只需要在多个字段名称之间以英文逗号隔开即可。此外，对于CHAR或VARCHAR类型的字段，我们还可以只使用字段内容前面的一部分来创建索引，只需要在对应的字段名称后面加上形如(length)的指令即可，表示只需要使用字段内容前面的length个字符来创建索引。在这里，我们以customers表的cust_name字段(类型为VARCHAR(50))为例，使用cust_name字段的6个字符前缀来创建索引。
CREATE INDEX idx_cust_name ON user (cust_name(6));
使用ALTER TABLE

 ALTER TABLE table_name
 ADD [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name 
 (index_col_name,...) [USING index_type]

删除索引

删除指定表中指定名称的索引，语法为：

ALTER TABLE table_name
DROP INDEX index_name;

例如删除名称为idx_cust_name的索引，其SQL语句为：

ALTER TABLE customers
DROP INDEX idx_cust_name;

修改索引

在MySQL中并没有提供修改索引的直接指令，一般情况下，我们需要先删除掉原索引，再根据需要创建一个同名的索引，从而变相地实现修改索引操作。

--先删除
ALTER TABLE user
DROP INDEX idx_user_username;
--再以修改后的内容创建同名索引
CREATE INDEX idx_cust_name ON customers (cust_name(8));

查看索引

在MySQL中，要查看某个数据库表中的索引也非常简单，只需要使用以下两个命令中的任意一种即可。

--如果查看索引前，没有使用user db_name等命令指定具体的数据库，则必须加上FROM db_name
SHOW INDEX FROM table_name [FROM db_name]
--如果查看索引前，没有使用user db_name等命令指定具体的数据库，则必须加上db_name.前缀
SHOW INDEX FROM [db_name.]table_name

函数

自定义函数

DELIMITER //
CREATE FUNCTION f1(
    i1 INT,
    i2 INT
)
RETURNS INT
BEGIN
    DECLARE num INT;
    SET num = i1 + i2;
    RETURN(num);
END //
DELIMITER ;

执行函数

SELECT f1(2,3) AS add_result;

删除函数

DROP FUNCTION f1;

语句控制

if语句

-- 1.1 if语句
CREATE PROCEDURE pro_fi()
BEGIN
    DECLARE i INT DEFAULT 0;
    IF i = 1 THEN
        SELECT i;
    ELSEIF i = 2 THEN
        SELECT 2;
    ELSE
        SELECT 22;
    END IF ;
END ;
CALL pro_fi();

case语句

-- 1.2 case语句
SELECT CASE WHEN uid = 11 THEN 'A'
            WHEN uid = 12 THEN 'B'
            WHEN uid = 13 THEN 'C'
        ELSE 'X' END 
        AS 'test'
FROM databases_example.user_info;

while循环

-- 2.1 while循环
USE databases_example;
DELIMITER //
CREATE PROCEDURE pro_while()
BEGIN
   DECLARE num INT;
   SET num = 0;
   WHILE num < 100 DO
       SELECT num;
       SET num = num + 20;
   END WHILE;
END //
DELIMITER ;
CALL pro_while();

repeat循环

USE databases_example;
DELIMITER //
CREATE PROCEDURE pro_repeat()
BEGIN
    DECLARE i INT;
    SET i=0;
    REPEAT
        SELECT i;
        SET i = i + 1;
    UNTIL i<= 5
    END REPEAT;
END //
DELIMITER ;
CALL pro_repeat();

loop循环

USE databases_example;
DELIMITER //
CREATE PROCEDURE pro_loop()
BEGIN
  DECLARE i INT DEFAULT 0;
  loop_label: LOOP
    SET i = i +1;
    IF i < 8 THEN
      ITERATE loop_label;
    END IF;

    IF i >= 10 THEN
      LEAVE loop_label;
    END IF;

    SELECT i;
  END LOOP;
END ;
DELIMITER ;
CALL pro_loop();

字符相关

查看字符集

show variables like 'character%'

创建数据库时指定字符集：

create database test2 character set utf8

时区相关

查看当前时区（UTC为世界统一时间，中国为UTC+8）：

show variables like "%time_zone%"

修改mysql全局时区为北京时间，即我们所在的东8区：

set global time_zone = '+8:00';

修改当前会话时区：

set time_zone = '+8:00'
flush privileges

杂症

delete、truncate 和 drop 的区别?

Delete 命令用来删除表的全部或者一部分数据行，执行 delete 之后，用户需要提交(commmit)或者回滚(rollback) 事务来执行删除或者撤销删除，delete 命令会触发这个表上所有的 delete 触发器。Truncate 删除表中的所有数据，这个操作不能回滚，也不会触发这个表上的触发器，truncate 比 delete更快，占用的空间更小。Drop 命令从数据库中删除表，所有的数据行，索引和权限也会被删除，所有的 DML 触发器也不会被触发，这个命令也不能回滚。

group by 和 order by 会使查询变慢?

group by 和 order by 操作通常需要创建一个临时表来处理查询的结果，所以如果查询结果很多的话会严重影响性能。

索引的理解?

索引是若干数据行的关键字的列表，查询数据时，通过索引中的关键字可以快速定位到要访问的记录所在的数据块，从而大大减少读取数据块的 I/O 次数，因此可以显著提高性能。

对事务的理解?

答:  在默认情况下，每执行一条SQL语句，都是一个单独的事务。如果需要在一个事务中包含多条SQL语句，那么需要开启事务和结束事务。 开启事务：start transaction，结束事务：commit或rollback。在执行SQL语句之前，先执行start transaction，这就开启了一个事务（事务的起点），然后可以去执行多条SQL语句，最后要结束事务，commit表示提交，即事务中的多条SQL语句所做出的影响会持久化到数据库中。rollback表示回滚，即回滚到事务的起点，之前做的所有操作都被撤消了。

存储过程和函数的区别：

存储过程是用户定义的一系列sql语句的集合，涉及特定表或其它对象的任务，用户可以调用存储过程，而函数通常是数据库已定义的方法，它接收参数并返回某种类型的值并且不涉及特定用户表。

简述

Mysql服务器的默认端口是3306。与Oracle相比，Mysql优势是Mysql是开源软件，随时可用，无需付费。

Mysql是便携式的，带有命令提示符的GUI。使用Mysql查询浏览器支持管理

导入数据库的SQL语句：

导入方法一：mysql -uroot -p123456 ceshi(数据库名)< ceshi.sql

导入方法二： use ceshi; source /root/ceshi.sql

导出数据库的SQL语句是：mysqldump -u 用户名 -p 数据库名 \*\*>\*\* 导出的文件名

如何查看mysql的警告信息：mysql> show warnings; 查看最近一个 sql 语句产生的错误警告

获取当前的Mysql版本：SELECT VERSION();用于获取当前Mysql的版本。

在linux服务器中配置mysql的慢查询：

\# vim /etc/my.cnf

\[mysqld\]

slow\_query\_log = 1 #开启慢查询日志

slow-query-log-file=/var/lib/mysql/slow.log #这个路径对 mysql 用户具有可写权限

long\_query\_time=5 #查询超过 5 秒钟的语句记录下来

主键和候选键有什么区别

表格的每一行都由主键唯一标识,一个表只有一个主键。主键也是候选键。按照惯例，候选键可以被指定为主键，并且可以用于任何外键引用。

常用的索引有哪些种类：

  普通索引: 即针对数据库表创建索引 唯一索引: 与普通索引类似，不同的就是：MySQL 数据库索引列的值必须唯一，但允许有空值 主键索引: 它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引 组合索引: 为了进一步榨取 MySQL 的效率，就要考虑建立组合索引。即将数据库表中的多个字段联合起来作为一个组合索引。

Mysql

查询不区分大小

LIKE和REGEXP操作有什么区别？

LIKE和REGEXP运算符用于表示^和％。
SELECT \* FROM employee WHERE emp\_name REGEXP “^b”;SELECT \* FROM employee WHERE emp\_name LIKE “%b”;

InnoDB是什么：

InnoDB，是MySQL的数据库引擎之一，它的特性是支持事务，并且采用多版本并发控制的方式来提高并发度，当一个事务全部完成，才会执行update．如果一段代码没有完成（及一个事务操作没有完成）它是不会update的，例如：银行转帐，一 笔业务没有完成的时候，突然的断电，或是，网络，系统的原因使你无法完成此交易的话，这个事务是要回滚的此交易之前的状态的，没有完成交易，你银行的 money是不会减少的！缺点是，innoDB的表执行起来速度较慢，但是安全！

输入字符为十六进制数字：

 如果想输入字符为十六进制数字，可以输入带有单引号的十六进制数字和前缀（X），或者只用（Ox）前缀输入十六进制数字。如果表达式上下文是字符串，则十六进制数字串将自动转换为字符串。

如何显示前50行：

 在Mysql中，使用以下代码查询显示前50行：SELECT FROM LIMIT 0,50;

mysql中 varchar 与 char 的区别以及 varchar(50)中的50代表的涵义：

varchar 与 char 的区别: char 是一种固定长度的类型，varchar 则是一种可变长度的类型.
varchar(50)中 50 的涵义 : 最多存放 50 个字节
int（20）中 20 的涵义: int(M)中的 M indicates the maximum display width (最大显示宽度)for integer types. The maximum legal display width is 255.

什么是通用SQL函数

CONCAT(A, B) – 连接两个字符串值以创建单个字符串输出。通常用于将两个或多个字段合并为一个字段。
FORMAT(X, D)- 格式化数字X到D有效数字。 CURRDATE(), CURRTIME()- 返回当前日期或时间。
NOW（） – 将当前日期和时间作为一个值返回。 MONTH（），DAY（），YEAR（），WEEK（），WEEKDAY（） – 从日期值中提取给定数据。
HOUR（），MINUTE（），SECOND（） – 从时间值中提取给定数据。DATEDIFF（A，B） – 确定两个日期之间的差异，通常用于计算年龄SUBTIMES（A，B） – 确定两次之间的差异。 FROMDAYS（INT） – 将整数天数转换为日期值。

1000w条数据，使用limit offset 分页时，为什么越往后翻越慢？如何解决？

先查主键，在分页。select \* from tb where id in（select id from tb where limit 10 offset 20）

Mysql有哪几种锁？

MyISAM支持表锁，InnoDB支持表锁和行锁，默认为行锁
表级锁：开销小，加锁快，不会出现死锁。锁定粒度大，发生锁冲突的概率最高，并发量最低
行级锁：开销大，加锁慢，会出现死锁。锁力度小，发生锁冲突的概率小，并发度最高

mysql的复制原理以及流程

Mysql 内建的复制功能是构建大型，高性能应用程序的基础。将 Mysql 的数据分布到多个系统上去，这种分布的机制，是通过将 Mysql 的某一台主机的数据复制到其它主机（slaves）上，并重新执行一遍来实现的。 \* 复制过程中一个服务器充当主服务器，而一个或多个其它服务器充当从服务器。主服务器将更新写入二进制日志文件，并维护文件的一个索引以跟踪日志循环。这些日志可以记录发送到从服务器的更新。 当一个从服务器连接主服务器时，它通知主服务器在日志中读取的最后一次成功更新的位置。从服务器接收从那时起发生的任何更新，然后封锁并等待主服务器通知新的更新。 过程如下 1. 主服务器把更新记录到二进制日志文件中。 2. 从服务器把主服务器的二进制日志拷贝到自己的中继日志（replay log）中。 3. 从服务器重做中继日志中的时间，把更新应用到自己的数据库上。