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;