mysql查询指令执行顺序
where->group-by>having->order by->limit
更新时间2016年7月24日 20:47:28
阶段一
进入数据库 mysql –h服务器名称–u用户名称 –p密码;
[示例]mysql -hlocalhost -uroot -p123456;
显示数据库
[示例]show databases;
->进入某个数据库 use 数据库名;
[示例]use shop;
->查看数据库里面的表单列表
[示例]show tables;
列类型的概念
数值型
整型 tinyint smallint mediumint int bigint
unsigned
无符号，因为计算机记录数字是以补码(详见《计算机组成原理》)的形式保存的
所以当声明了无符号的时候，该数字记录的范围可以多一位了
比如,声明一个tinyint类型，它可记录一个字节,即，可以记录八位二进制数据
在未声明无符号的时候，它会使用第一位作为符号，范围就在-128~127之间
声明无符号位的时候，的范围就变为了0~255
zerofill
零填充,M宽度
浮点型/定点型
M为总位数,d代表小数位个数
float(M,d)与decimal(M,d)
无符号的时候decimal比float更精确，常用商城价格
字符型
char(M)与varchar(M)的区别
char实际占了M个字节(定长),存储的不够M则向右侧补空格,取出时取出右侧空格，限制 M<=255
varchar有1~2个字节来标记真实的长度(变长),限制 M<=65535
日期
常用TIMESTAMP current_timestamp直接记录时间
在开发中常用int类型来时间戳秒数，方便计算，显示时间的时候也方便格式化为不同的显示样式
阶段二
where
//取出account_log>4的数据 四个运算符 > = < !=
SELECT * FROM account_log where log_id >4;
//取出4到8直接的数据
SELECT * FROM account_log where log_id between 4 and 8;
（注意这里可以用 wherelog_id>=4 and log_id <=8来同样表达）
//取出4和8中的数据
SELECT * FROM account_log where log_id in (4,8);
//逻辑词汇三个 not and or，下面以not的用法为例子
SELECT * FROM account_log where log_id not in (4,8);
group by
通常与统计函数avg(),sum(),count(),min(),max()一起使用
【注意】当使用group的时候，count(*)函数得出的数是指的每个分组内的成员个数，
比如select count(*) as times from table1 group by id，
比如 id=1的个数就是times的值
把每一列都当作一个变量看,运算结果通过as作为别名输出
//查出一个用户所购买的所有东西的总价格
SELECT * sum( money * num) as total FROM account_log group by user_id;
where与having的区别
where查表中的原始数据有作用，having对表中没有的但对查询逻辑中有结果有效
//查询总成绩大于380的学生名字,与其对应的总分
SELECT name, (Chinese+Math+English)as score
FROM student
HAVING score>380
ORDER BYscore DESC;
//查询用户中，消费超过1000元的人中消费最低的两个人
SELECTuser_od,sum(user_money) as total
FROMaccount_log
Group BYuser_id
HAVINGtotal>1000
ORDER BY user_idASC
LIMIT 2;
//计算挂科两门及以上的学生的平均分,从高到低每页展示20个人的信息 column,name,score
SELECT avg(score) as avg_score,sum(score<60) as times
FROMstudent_score
GROUP BY name
HAVINGtimes>=2
ORDER BYavg_score DESC
LIMIT 20;
【注意】count()里面只取的是行数，给什么条件都没用，
sum() 如果里面是判断类型，结果就为真值的总个数；
如果是普通运算，结果就为运算的结果
order by
它可以多字段排序 orderby 字段1,字段2 asc
[示例]order by cat_idasc,shop_price desc;
//通过goods表建立临时表g2并把goods表中的数据传给g2,传入前，
//①将goods表的数据通过cat_id与shop_price排序（但在平时的操作中，
//②我们不用临时表，我们用子查询(查询结果当作表)）
CREATE TABLE g2 LIKE goods;
INSERT INTO g2 SELECT * FROM goods ORDER BY cat_id asc,shop_price desc;
Where From Exists子查询
>Where是把内部的查询结果，给外部查询使用
[常用场景=>查询最大商品、查询最贵商品][最新,即自增id为最大值的时候]
SELECT name FROM goods
WHERE name in(
SELECT name
FROM goods
ORDER BY user_id
);
>From查询结果当成一个临时表(表必须取一个别名)，再让外部进行一次sql查询使用
[常用场景=>查询每个栏目下的最新/最贵商品]
SELECT name FROM(
SELECT name
FROM goods
ORDER BY user_id
) as temp_table;
>Exists 把外层的查询结果拿到内层,看内部的查询结果是否成立
[常用场景=>查询有商品的栏目]
SELECT * FROM comment
WHERE EXISTS(
SELECT * FROM comment_inner
WHERE comment_inner.article_id=comment.article_id
);
//计算挂科两门及以上的学生的平均分 column,name,score
思路：分三次。第一次查询
① 筛选符合条件的人=>这时候会有一个人的名字多次出现的情形【WHERE子查询】
SELECT name,count(*) as result
FROM stu
WHERE score <60
GROUP BY name having result>1;
② 合并重复出现的名字【普通查询】
SELECT name
FROM temp
GROUP BY name;
③ 计算这些人的平均成绩【FROM子查询】
SELECT name,avg(score) as avg_score
FROM stu where name in②中的结果;
最终拼写出语句
SELECT name,avg(score) as avg_score
FROM stu where name in
(SELECT name
FROM (
SELECT name,count(*) as result
FROM stu
WHERE score <60
GROUP BY name having result>1
)as temp
GROUP BY name
);
更新时间2016年7月26日23:26:48
阶段三
union
>把2次或者多次查询结果合成一张表，要求其列数要一致
可以来源于多张表，可以进行多次sql语句查询，取出的列名可以不一致，此时以第一个sql的列名为准
1)如果不同的语句中取出的行，有完全相同(每个列的值都相同),
那么相同的行将会合并(去重复)
SELECTS * FROM ta
UNION
SELECT * FROM tb;
2)如果不去掉重复项，可以通过加all来搞定
SELECTS * FROM ta
UNION ALL
SELECT * FROM tb;
3)如果语句中有oder by,limit(在子句中order by配合limit才有意义，否则语法分析器，会在分析语法的时候，把order by去掉)
则需要将每个查询子句都包起来(但平日多用在总的结果后 排序)
//取第三个栏目前三高、第四个栏目前两高的商品，用union实现
(SELECT goods_id,cat_id,goods_name,shop_price
FROM goods
WHERE cat_id = 3
ORDER BY shop_price desc
LIMIT 3)
UNION
(SELECT goods_id,cat_id,goods_name,shop_price
FROM goods
WHERE cat_id =4
ORDER BY shop_price desc
LIMIT 2);
join
1) 左连接【这里以tb为基准 [意指它每项得应完全对齐] ：交集部分先拿出来。另外，若自己有没对齐的行，则把这些行的数据也拿出来。因为没对齐，所以这几行只有自身的数据，其他数据为NULL】
SELECT tb.level,tb.assignment,ta.name
FROM tb
LEFT JOIN ta
ON ta.level=tb.level
2) 右连接【这里以ta为基准，逻辑过程与左联接同理】
SELECT tb.level,tb.assignment,ta.name
FROM tb
RIGHT JOIN ta
ON ta.level=tb.level
3)内连接【查询左右表都有的数据，即左右链接的交集】
SELECT tb.level,tb.assignment,ta.name
FROM tb
INNER JOIN ta
ON ta.level=tb.level
//查出左右连接的并集，可以用UNION
//查出下面2016-08-08之后的所有比赛，并以形如下面的方式输出：
重庆力帆 0:0 上海申花 2016-08-09
SELECT a.matchResult,a.matchTime ,b.teamName as hName,c.teamName as gName
FROM `match` as a
LEFT JOIN `team` as b
On b.teamID=a.hostTeamID
LEFT JOIN `team` as c
On c.teamID=a.guestTeamID
WHERE a.matchTime>'2016-08-08'
更新时间2016年7月28日 10:27:44
view
>视图是由查询结果形成的一张虚拟表
视图的用处
>简化查询
>把表的权限封闭，但开放相应的视图权限，视图里只开放部分数据
>大数据分表可以用到.
视图的创建
create view 视图名 as select语句
视图的删除
drop view 视图名
视图的修改
Alter view as select xxx;
视图与表的关系
视图是表的查询结果,自然表的数据改变了，会影响视图的结果
视图改变了呢？
1:视图增删改也会影响表的吗
>视图的数据与表的数据 一一对应时，可以修改
2:视图总能增删改吗
>对于视图的insert还应该注意，视图必须包含表中没有默认值的列
大数据分表查询
当表超过200万行的时候，查询速度就会变慢，这时候可以通过分表查询的方法
[示例] 现在我把表通过 模4 的方法，把表分成4张视图
CREATE view g1 as select * from goods where goods_id % 4=0;
CREATE view g2 as select * from goods where goods_id % 4=1;
CREATE view g3 as select * from goods where goods_id % 4=2;
CREATE view g4 as select * from goods where goods_id % 4=3
以php查询为例
$tableFlag=$_GET['id']%4;
$tablename="g".$tableFlag;
$result=$pdo->("select * from $tablename");
此外，我们可以把多张表通过union合成一张新的视图
CREATE newTable as select * from t1 unionselect * from t2 ....;
视图的Algorithm
algorithm = merge / Temptable / undefined
Merge当引用视图的时候，视图语句与定义视图的语句合并只是为了形成一条select语句
Temptable 当引用视图时，根据视图的创建语句建立一个临时表多用于做子查询的结果，出现非常频繁的时候
Undefined未定义，让系统帮你选
而temptable是根据创建语句瞬间创建一张临时表,
然后查询视图的语句从该临时表查数据
create algorithm=Temptable view g2 as
select goods_id,cat_id,goods_name,shop_price from goods order by cat_idasc,shop_price desc;
查询语句为
select * from g2 group by cat_id; //最后执行的这个查询语句
然后取出结果，并放在临时表上
字符集与校对集
字符集
1.告诉服务器，我给你发送的数据是什么编码 Character_set_client
[示例]set character_set_client=utf8;
2.告诉转换器，转换成什么编码？Character_set_connection
[示例]set character_set_connection=utf8;
3.查询的结果是什么编码？Character_set_results
[示例]set character_set_results=utf8;
若三者统一编码，则可写为set names 字符集名
[示例]set names utf8;
校对集
>值字符集的排序规则[一个字符集可以有多种排序规则，如图所示]
声明校对规则
[示例]create table(xxxxx) Charset utf8 collate utf8_general_ci;
更新时间2016年7月29日 01:55:46
阶段四
触发器
四要素
监视地点 监视事件 触发事件 触发事件
MySQL包含对触发器的支持。触发器是一种与表操作有关的数据库对象，当触发器所在表上出现指定事件时，将调用该对象，即表的操作事件触发表上的触发器的执行。
创建触发器
在MySQL中，创建触发器语法如下：
CREATE TRIGGER trigger_name
trigger_time trigger_event ON tbl_name
FOR EACH ROW
trigger_stmt
其中：
trigger_name：标识触发器名称，用户自行指定；
trigger_time：标识触发时机，取值为 BEFORE 或 AFTER；
trigger_event：标识触发事件，取值为 INSERT、UPDATE 或 DELETE；
tbl_name：标识建立触发器的表名，即在哪张表上建立触发器；
trigger_stmt：触发器程序体，可以是一句SQL语句，或者用 BEGIN 和 END 包含的多条语句。
由此可见，可以建立6种触发器，即：BEFORE INSERT、BEFORE UPDATE、BEFORE DELETE、AFTER INSERT、AFTER UPDATE、AFTER DELETE。
另外有一个限制是不能同时在一个表上建立2个相同类型的触发器，因此在一个表上最多建立6个触发器。
trigger_event 详解
MySQL 除了对 INSERT、UPDATE、DELETE 基本操作进行定义外，还定义了 LOAD DATA 和 REPLACE 语句，这两种语句也能引起上述6中类型的触发器的触发。
LOAD DATA 语句用于将一个文件装入到一个数据表中，相当与一系列的 INSERT 操作。
REPLACE 语句一般来说和 INSERT 语句很像，只是在表中有 primary key 或 unique 索引时，如果插入的数据和原来 primary key 或 unique 索引一致时，会先删除原来的数据，然后增加一条新数据，也就是说，一条 REPLACE 语句有时候等价于一条。
INSERT 语句，有时候等价于一条 DELETE 语句加上一条 INSERT 语句。
INSERT 型触发器：插入某一行时激活触发器，可能通过 INSERT、LOAD DATA、REPLACE 语句触发；
UPDATE 型触发器：更改某一行时激活触发器，可能通过 UPDATE 语句触发；
DELETE 型触发器：删除某一行时激活触发器，可能通过 DELETE、REPLACE 语句触发。
BEGIN … END 详解
在MySQL中，BEGIN … END 语句的语法为：
BEGIN
[statement_list]
END
其中，statement_list代表一个或多个语句的列表，列表内的每条语句都必须用分号（;）来结尾。
而在MySQL中，分号是语句结束的标识符，遇到分号表示该段语句已经结束，MySQL可以开始执行了。因此，解释器遇到statement_list 中的分号后就开始执行，然后会报出错误，因为没有找到和 BEGIN 匹配的 END。
这时就会用到 DELIMITER 命令（DELIMITER 是定界符，分隔符的意思），它是一条命令，不需要语句结束标识，语法为：
DELIMITER new_delemiter
new_delemiter 可以设为1个或多个长度的符号，默认的是分号（;），我们可以把它修改为其他符号，如$：
DELIMITER $
在这之后的语句，以分号结束，解释器不会有什么反应，只有遇到了$，才认为是语句结束。注意，使用完之后，我们还应该记得把它给修改回来。
一个完整的创建触发器示例
假设系统中有两个表：
班级表 class(班级号 classID, 班内学生数 stuCount)
学生表 student(学号 stuID, 所属班级号 classID)
要创建触发器来使班级表中的班内学生数随着学生的添加自动更新，代码如下：
DELIMITER $
create trigger tri_stuInsert after insert
on student for each row
begin
declare c int;
set c = (select stuCount from class where classID=new.classID);
update class set stuCount = c + 1 where classID = new.classID;
end$
DELIMITER ;
变量详解
MySQL 中使用 DECLARE 来定义一局部变量，该变量只能在 BEGIN … END 复合语句中使用，并且应该定义在复合语句的开头，
即其它语句之前，语法如下：
DECLARE var_name[,...] type [DEFAULT value]
其中：
var_name 为变量名称，同 SQL 语句一样，变量名不区分大小写；type 为 MySQL 支持的任何数据类型；可以同时定义多个同类型的变量，用逗号隔开；变量初始值为 NULL，如果需要，可以使用 DEFAULT 子句提供默认值，值可以被指定为一个表达式。
对变量赋值采用 SET 语句，语法为：
SET var_name = expr [,var_name = expr] ...
Before 与 After的区别
After 是先完成数据的增删改，再触发，触发的语句晚于监视的增删改，无法影响前面的增删改动作
Before 是先完成触发，再增删改
修改一次触发器，就得把之前的触发器给删了，不然原来的触发器还会继续生效
delimiter $
create trigger t1
before insert on indent
for each row
begin
if new. buy_num > 5 then
set new.buy_num= 5;
end if;
update indent_details set num = num – buy_num where id =new.indent_id;
end$
delimiter ;
NEW 与 OLD 详解
上述示例中使用了NEW关键字，和 MS SQL Server 中的 INSERTED 和 DELETED 类似，MySQL 中定义了 NEW 和 OLD，用来表示
触发器的所在表中，触发了触发器的那一行数据。
具体地：
在 INSERT 型触发器中，NEW 用来表示将要（BEFORE）或已经（AFTER）插入的新数据；
在 UPDATE 型触发器中，OLD 用来表示将要或已经被修改的原数据，NEW 用来表示将要或已经修改为的新数据；
在 DELETE 型触发器中，OLD 用来表示将要或已经被删除的原数据；
使用方法：NEW.columnName （columnName 为相应数据表某一列名）
另外，OLD 是只读的，而 NEW 则可以在触发器中使用 SET 赋值，这样不会再次触发触发器，造成循环调用（如每插入一个学生前，都在其学号前加“2013”）。
查看触发器
和查看数据库（show databases;）查看表格（show tables;）一样，查看触发器的语法如下：
SHOW TRIGGERS [FROM schema_name];
其中，schema_name即 Schema 的名称，在 MySQL 中 Schema 和 Database 是一样的，也就是说，可以指定数据库名，这样就
不必先“USE database_name;”了。
删除触发器
和删除数据库、删除表格一样，删除触发器的语法如下：
DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name
触发器的执行顺序
我们建立的数据库一般都是 InnoDB 数据库，其上建立的表是事务性表，也就是事务安全的。这时，若SQL语句或触发器执行失败，MySQL 会回滚事务，有：
①如果 BEFORE 触发器执行失败，SQL 无法正确执行。
②SQL 执行失败时，AFTER 型触发器不会触发。
③AFTER类型的触发器执行失败，SQL 会回滚。
预计更新时间2016年7月29日晚
事务
ACID特性
原子性：数据库事务不可再分的原则一致性：要么一起执行，要么一起取消
隔离型：每个事务对其他事务是不可见的
持久性：当事务完成后，其影响会被保留，不能撤销
用法
start transaction;
sql语句1;
sql语句2;
...
若成功，则提交commit;
若失败，则回滚rollback;
[示例] HLZ给Leo转账,并成功start transaction;
update bank set money= money -100 where name='HLZ';
update bank set money= money+100 where name='Leo';
commit;
索引
——这部分是简单引入，下一篇为优化的正文，我将进一步谈谈各种索引
如果用btree(二叉树)索引 lb(N) 次
一般有索引的数据都比较大，所以，换服务器的时候，记得先把索引去掉，导入数据库后再统一设置索引
表的主关键字
什么情况下应不建或少建索引
表记录太少
经常插入、删除、修改的表
扫一扫
978
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
