Mysql知识总结

Mysql语句

1.查询

1.简单查询

SELECT * FROM 表名

2.避免重复DISTINCT

SELECT DISTINCT post FROM 表名

3.通过四则运算查询

SELECT id*12 FROM 表名

4.定义显示格式

CONCAT() 函数用于连接字符串

SELECT CONCAT('姓名: ',name,' 年薪: ', salary*12) AS Annual_salary FROM employee

CONCAT_WS() 第一个参数为分隔符

 SELECT CONCAT_WS(':',name,salary*12) AS Annual_salary FROM employee

 

2.删除

DELETE FROM <表名> [WHERE 子句] [ORDER BY 子句] [LIMIT 子句]

3.修改

UPDATE <表名> SET 字段 1=值 1 [,字段 2=值 2… ] [WHERE 子句 ][ORDER BY 子句] [LIMIT 子句]

4.插入

INSERT INTO 表名称 VALUES (值1, 值2,....)

INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)

5.Where 约束

1. 比较运算符：> < >= <= <> !=

2. between 80 and 100 值在10到20之间

3. in(80,90,100) 值是10或20或30

4. like 'egon%'

    pattern可以是%或_，

    %表示任意多字符

    _表示一个字符

5. 逻辑运算符：在多个条件直接可以使用逻辑运算符 and or not

6.分组

首先明确一点：分组发生在where之后，即分组是基于where之后得到的记录而进行的 分组指的是：将所有记录按照某个相同字段进行归类，比如针对员工信息表的职位分组，或者按照性别进行分组等。

可以按照任意字段分组，如果想查看组内信息，需要借助于聚合函数，默认查出的内容都是一组，所以可以直接使用聚合函数。

ONLY_FULL_GROUP_BY：可以实现分组，不做过多了解

单独使用GROUP BY关键字分组 SELECT post FROM employee GROUP BY post;

GROUP BY关键字和GROUP_CONCAT()函数一起使用

GROUP BY与聚合函数一起使用

7.聚合函数

聚合函数聚合的是组的内容，若是没有分组，则默认一组

1.COUNT：统计行数量

2.SUM：获取单个列的合计值

3.AVG：计算某个列的平均值

4.MAX：计算列的最大值

5.MIN：计算列的最小值

8.HAVING过滤

执行优先级从高到低：where > group by > having Where 发生在分组group by之前，因而Where中可以有任意字段，但是绝对不能使用聚合函数(where后面不能接聚合函数) Having发生在分组group by之后，因而Having中可以使用分组的字段，无法直接取到其他字段,可以使用聚合函数

9.ORDER BY排序

使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。 默认情况下，它是按升序排列

10.限制查询记录数

LIMIT 0,5; #从第0开始，即先查询出第一条，然后包含这一条在内往后查5条

11.模糊查询

1、LIKE'Mc%' 将搜索以字母 Mc 开头的所有字符串（如 McBadden）。 

2、LIKE'%inger' 将搜索以字母 inger 结尾的所有字符串（如 Ringer、Stringer）。 

3、LIKE'%en%' 将搜索在任何位置包含字母 en 的所有字符串（如 Bennet、Green、McBadden）。 

4、LIKE'_heryl' 将搜索以字母 heryl 结尾的所有六个字母的名称（如 Cheryl、Sheryl）。 

5、LIKE'[CK]ars[eo]n' 将搜索下列字符串：Carsen、Karsen、Carson 和 Karson（如 Carson）。 

6、LIKE'[M-Z]inger' 将搜索以字符串 inger 结尾、以从 M 到 Z 的任何单个字母开头的所有名称（如 Ringer）。 

7、LIKE'M[^c]%' 将搜索以字母 M 开头，并且第二个字母不是 c 的所有名称（如MacFeather）

注：LIKE 子句与等号 = 的效果是一样的，LIKE 通常与 % 一同使用，类似于一个元字符的搜索

12.多表连接查询

交叉连接，内连接，外连接(左连接，右连接)

SELECT 字段列表FROM 表1 INNER|LEFT|RIGHT JOIN 表2 ON 表1.字段 = 表2.字段;

1.交叉连接

不适用任何匹配条件。生成笛卡尔积，第一张的数据乘以第二张表所有数据，行数n*m，列数为两张表列数相加

2.内连接(等值连接)

只连接匹配的行。找两张表共有的部分，相当于利用条件从笛卡尔积结果中筛选出了正确的结果

SELECT 字段列表FROM 表1 INNER JOIN 表2 ON 表1.字段 = 表2.字段;

相当于

SELECT 字段列表FROM 表1,表2 WHERE 表1.字段 = 表2.字段;

3.外链接之左连接：优先显示左表全部记录

在内连接的基础上增加左边有右边没有的结果

SELECT 字段列表FROM 表1 LEFT JOIN 表2 ON 表1.字段 = 表2.字段;

4.外链接之右连接：优先显示右表全部记录

在内连接的基础上增加右边有左边没有的结果

SELECT 字段列表FROM 表1 RIGHT JOIN 表2 ON 表1.字段 = 表2.字段;

5.全外连接：显示左右两个表全部记录

mysql不支持全外连接,mysql可以使用此种方式间接实现全外连接

SELECT 字段列表FROM 表1 LEFT JOIN 表2 ON 表1.字段 = 表2.字段;

union

SELECT 字段列表FROM 表1 RIGHT JOIN 表2 ON 表1.字段 = 表2.字段;

UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据

13.子查询

当一个查询是另一个查询的条件时，称之为子查询。子查询可以使用几个简单命令构造功能强大的复合命令

1.子查询是将一个查询语句嵌套在另一个查询语句中。 2.内层查询语句的查询结果，可以为外层查询语句提供查询条件。 3.子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字 4.还可以包含比较运算符：= 、 !=、> 、<等

注:EXISTS关字键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录。

而是返回一个真假值。True或False

当返回True时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询

如：

查询平均年龄在25岁以上的部门名

select id,name from department where id in (select dep_id from employee group by dep_id having avg(age) > 25);

先查询出另张员工表的大于平均年龄的员工所在部门id，通过这个id去部门表里查询部门名称，当然你也可以通过连接两张表然后找出部门名称。

子查询相当于把一件事事情拆分成两件分别做，如果是通过表的连接相当于一次性做完了

2.视图

视图是虚拟表，本身不存储数据，而是按照指定的方式进行查询，借助视图，来执行相同或相似的查询

CREATE VIEW 视图名(列1，列2...) AS SELECT (列1，列2...) FROM ...;

3.mysql 存储例程

存储例程是存储在服务器中的一组指定的 SQL 语句：客户机无需不断重新发出单个语句，而可以改为引用存储例程。是存储在数据库目录中的一段声明性SQL语句，方便其他应用程序可以调用。数据库中的存储过程可以看做是对编程中面向对象方法的模拟，能实现较快的执行速度

存储例程类型：

存储过程：通过 CALL 语句调用过程。这些过程可以使用输出变量或结果集传回值。

存储函数：在语句中调用函数。这些函数可返回标量值。

1.存储函数

CREATE FUNCTION func_name ( [IN] ) //括号是必须的，参数是可选的RETURNS type  [ characteristic ...] routine_body  

IN：表示输入参数；

param_name：表示参数的名称；

type：表示参数的类型，该类型可以是MySQL数据库中的任意类型；

RETURNS type：语句表示函数返回数据的类型

characteristic: 指定存储函数的特性，取值与存储过程时相同

routine_body是SQL代码内容，可以用BEGIN...END来表示SQL代码的开始与结束

具体语句：

DELIMITER //   
CREATE FUNCTION 名称(id INT)   
RETURNS VARCHAR(300) 
BEGIN    
RETURN(SELECT CONCAT(列名) FROM 表名 WHERE 条件);  
END// 
DELIMITER ; 

2.存储过程

1.创建存储过程

CREATE PROCEDURE 名称([参数类型]) [characteristics...] routine_body

参数列表的形式：[IN | OUT | INOUT]

IN：表示输入参数；

OUT：表示输出参数；

INOUT：表示既可以输入也可以输出；

具体语句

DELIMITER // 
create procedure 名称() 
begin 
select AVG(列名) as form 表名 
end// 
DELIMITER ;

2.调用存储过程

CALL 名称([parameter[,...]])

3.函数和存储过程的区别

1:存储函数与存储过程作用类同，不同的是存储过程只能使用CALL语句来调用存储过程,只能用来输出变量返回值，而存储函数可以从语句外调用(即通过引用函数名),也能返回标量值

存储过程中的语句功能更强大，存储过程可以实现很复杂的业务逻辑，而函数有很多限制，如不能在函数中使用insert,update,delete,create等语句；存储函数只完成查询的工作，可接受输入参数并返回一个结果，也就是函数实现的功能针对性比较强。

存储函数和存储过程统称为存储例程(store routine),存储函数的限制比较多,例如不能用临时表,只能用表变量,而存储过程的限制较少,存储过程的实现功能要复杂些,而函数的实现功能针对性比较强

存储过程可以调用存储函数。但函数不能调用存储过程

2:返回值不同

存储函数必须有返回值,且仅返回一个结果值

存储过程可以没有返回值,但是能返回结果集(out,inout)

3:调用时的不同

存储函数嵌入在SQL中使用,可以在select 存储函数名(变量值);

存储过程通过call语句调用 call 存储过程名

4:参数的不同

存储函数的参数类型类似于IN参数

存储过程的参数类型有三种

1: in 数据只是从外部传入内部使用(值传递),可以是数值也可以是变量

2: out 只允许过程内部使用(不用外部数据),给外部使用的(引用传递:外部的数据会被先清空才会进入到内部),只能是变量

3: inout 外部可以在内部使用,内部修改的也可以给外部使用,典型的引用 传递,只能传递变量

4.触发器

触发器是一种特殊的存储过程，它在插入，删除或改动特定表中的数据时触发运行，它比数据库本身标准的功能有更精细和更复杂的数据控制能力，触发器针对的目标是数据发生改变，对应的操作只有（增，删，改，查询不发生数据的改变，所以查询没有触发事件

CREATE TRIGGER 名称 BEFORE|AFTER INSERT|UPDATE|DELETE ON 表名 FOR EACH ROW 
SQL语句(运行多个语句，可使用BEGIN ... END复合语句结构)

对于具有同样触发程序动作时间和事件的给定表，不能有两个触发程序，一张表中最多的触发器只能有六个，new一般出现在插入，更新，old一般出现在更新，删除。

与存储过程的区别：触发器主要是通过事件进行触发而被执行的，而存储过程可以通过存储过程名字而被直接调用

5.事务

事务是一组SQL语句，要么全部执行成功，要么全部执行失败。通常一个事务对应一个完整的业务

事务的开启：BEGIN

事务的提交：COMMIT

事务的回滚：ROLLBACK

事务的关闭：CLOSE

默认情况下一个SQL语句为一个事务

SELECT @@AUTOCOMMIT;    查看MySQL是否自动提交事务  0表示手动提交事务    1表示自动提交事务

SET AUTOCOMMIT = 0;    设置事务提交方式  0表示手动提交事务    1表示自动提交事务

SELECT  @@TX_ISOLATION;查询事务的隔离级别

6.事件

一些对数据定时性操作不再依赖外部程序，而直接使用数据库本身提供的功能。可以实现每秒钟执行一个任务，这在一些对实时性要求较高的环境下就非常实用了，但是是定时触发，不可以调用，相当于就是定时器

CREATE [DEFINER = { user | CURRENT_USER }]

EVENT [IF NOT EXISTS] event_name

ON SCHEDULE schedule

[ON COMPLETION [NOT] PRESERVE]

[ENABLE | DISABLE | DISABLE ON SLAVE]

[COMMENT 'comment']

DO event_body;

默认是事件到期后会自动删除，想保留该事件使用ON COMPLETION PRESERVE，如果不想保留也可以设置ON COMPLETION [NOT] PRESERVE

具体使用，每天定时执行

DELIMITER // 
CREATE EVENT 事件名称 
ON SCHEDULE EVERY 1 DAY STARTS '2018-11-19 00:15:00' 
on COMPLETION PRESERVE 
DO 
BEGIN 
call 存储过程名称(); 
END // 
DELIMITER ;

7.游标

游标是一个存储在MySQL服务器上的数据库查询，它不是一条select语句，而是被该语句所检索出来的结果集

1、在能够使用游标前，必须先定义它。这个过程实际上是没有检索数据的，它只是定义要使用的select语句。

2、一旦你定义了游标后，必须打开游标以供使用。这个过程用前面定义的select语句把数据实际检索出来。即这个步骤之后，我们就可以操作游标中的数据了。

3、对于有数据的游标，根据需要取出各行的数据来进行一定的操作。

4、使用完游标后，一定要关闭游标。

具体过程：

DECLARE 名称 CURSOR FOR SQL语句

给结束标记：DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET 值1= -1.0 ;

open 名称;

取值(FETCH 表示游标向下走一步)：

FETCH 名称 INTO 值1;

WHILE 值1 != -1 DO

SET 值1=CONCAT(值1,';');

SET 值2=CONCAT(值2,值1);

FETCH 名称 INTO 值1;

END WHILE;

注：值1,值2要在前面声明declare，concat连接字符串，只要其中一个是NULL,那么将返回NULL，还有loop循环游标，repeat循环游标

close 名称;

8.索引

MySQL数据库几个基本的索引类型：普通索引、唯一索引、主键索引、全文索引

索引是对数据库表中一或多个列的值进行排序的结构，是帮助MySQL高效获取数据的数据结构

索引就是加快检索表中数据的方法。数据库的索引类似于书籍的索引。在书籍中，索引允许用户不必翻阅完整个书就能迅速地找到所需要的信息。在数据库中，索引也允许数据库程序迅速地找到表中的数据，而不必扫描整个数据库。

索引加快数据库的检索速度，索引降低了插入、删除、修改等维护任务的速度，唯一索引可以确保每一行数据的唯一性，通过使用索引，可以在查询的过程中使用优化隐藏器，提高系统的性能，索引需要占物理和数据空间

 

使用索引查询一定能提高查询的性能吗，索引需要空间来存储,也需要定期维护, 每当有记录在表中增减或索引列被修改时,索引本身也会被修改. 这意味着每条记录的INSERT,DELETE,UPDATE将为此多付出4,5 次的磁盘I/O. 因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢.使用索引查询不一定能提高查询性能,索引范围查询(INDEX RANGE SCAN)适用于两种情况:基于一个范围的检索,一般查询返回结果集小于表中记录数的30%，基于非唯一性索引的检索

9.变量声明

declare 名称 类型 default 默认值

分配变量值

set 名称 = 10 或者使用 select into语句将查询结果分配给一个变量，如select id into 变量名称 from 表名

以@符号开头为会话变量，直到会话结束前它可用和可访问

如果不设默认值，则变量的初始值为NULL

 

10.mysql常用命令

1、登录：mysql -uroot -p

2、连接到远程主机上的MySQL：mysql -h127.0.0.1 -uroot -p123456;

3、使用set passsword命令修改密码：set password for 用户名@localhost = password('新密码');或者mysqladmin -uroot -p123456 password 12345678;或者update user set password=password("新密码") where user="root";

4.输入mysqld -nt --skip-grant-tables 回车。--skip-grant-tables 的意思是启动MySQL服务的时候跳过权限表认证，记得关闭mysql服务，然后另起一个mysql窗口来输入命令。

5.退出mysql ：quit或者exit

6.查看库：show databases;

7.使用库：use databases

8.查看数据库中的表：show tables

9.查看表结构：desc  table_name;

10.启动和关闭服务：net start mysql和net stop mysql

11.设置远程登录权限  GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '数据库密码' WITH GRANT OPTION;