谭文颖年龄,仙剑奇侠传有几部,建筑电气与智能化专业
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
视图就是通过查询得到一张虚拟表，然后保存下来，下次直接使用即可。
如果要频繁使用一张虚拟表，可以不用重复查询
视图使用方法：
我要评论-- 将表1与表2通过on后面的条件进行内连接，产生的新表 就是我们创建的视图表
create view 视图表名 as
select * from 表1 inner join 表2
on 内连接条件
具体示例：
先建基础数据表及其记录（由于博客园暂找不到上传文件的地方，所以只能插入创建表的sql语句，将其复制粘贴到txt文档里面，最好是notpad++里面，然后存为sql文件，在navicat里面导入就行了）
/* navicat premium data transfer source server : sgt'mysql source server type : mysql source server version : 50726 source host : localhost:3306 source schema : day41 target server type : mysql target server version : 50726 file encoding : 65001 date: 17/05/2019 14:54:11 */ set names utf8mb4; set foreign_key_checks = 0; -- ---------------------------- -- table structure for class -- ---------------------------- drop table if exists `class`; create table `class` ( `cid` int(11) not null auto_increment, `caption` varchar(32) character set utf8 collate utf8_general_ci not null, primary key (`cid`) using btree ) engine = innodb auto_increment = 5 character set = utf8 collate = utf8_general_ci row_format = dynamic; -- ---------------------------- -- records of class -- ---------------------------- insert into `class` values (1, '三年二班'); insert into `class` values (2, '三年三班'); insert into `class` values (3, '一年二班'); insert into `class` values (4, '二年九班'); -- ---------------------------- -- table structure for course -- ---------------------------- drop table if exists `course`; create table `course` ( `cid` int(11) not null auto_increment, `cname` varchar(32) character set utf8 collate utf8_general_ci not null, `teacher_id` int(11) not null, primary key (`cid`) using btree, index `fk_course_teacher`(`teacher_id`) using btree, constraint `fk_course_teacher` foreign key (`teacher_id`) references `teacher` (`tid`) on delete restrict on update restrict ) engine = innodb auto_increment = 5 character set = utf8 collate = utf8_general_ci row_format = dynamic; -- ---------------------------- -- records of course -- ---------------------------- insert into `course` values (1, '生物', 1); insert into `course` values (2, '物理', 2); insert into `course` values (3, '体育', 3); insert into `course` values (4, '美术', 2); -- ---------------------------- -- table structure for score -- ---------------------------- drop table if exists `score`; create table `score` ( `sid` int(11) not null auto_increment, `student_id` int(11) not null, `course_id` int(11) not null, `num` int(11) not null, primary key (`sid`) using btree, index `fk_score_student`(`student_id`) using btree, index `fk_score_course`(`course_id`) using btree, constraint `fk_score_course` foreign key (`course_id`) references `course` (`cid`) on delete restrict on update restrict, constraint `fk_score_student` foreign key (`student_id`) references `student` (`sid`) on delete restrict on update restrict ) engine = innodb auto_increment = 53 character set = utf8 collate = utf8_general_ci row_format = dynamic; -- ---------------------------- -- records of score -- ---------------------------- insert into `score` values (1, 1, 1, 10); insert into `score` values (2, 1, 2, 9); insert into `score` values (5, 1, 4, 66); insert into `score` values (6, 2, 1, 8); insert into `score` values (8, 2, 3, 68); insert into `score` values (9, 2, 4, 99); insert into `score` values (10, 3, 1, 77); insert into `score` values (11, 3, 2, 66); insert into `score` values (12, 3, 3, 87); insert into `score` values (13, 3, 4, 99); insert into `score` values (14, 4, 1, 79); insert into `score` values (15, 4, 2, 11); insert into `score` values (16, 4, 3, 67); insert into `score` values (17, 4, 4, 100); insert into `score` values (18, 5, 1, 79); insert into `score` values (19, 5, 2, 11); insert into `score` values (20, 5, 3, 67); insert into `score` values (21, 5, 4, 100); insert into `score` values (22, 6, 1, 9); insert into `score` values (23, 6, 2, 100); insert into `score` values (24, 6, 3, 67); insert into `score` values (25, 6, 4, 100); insert into `score` values (26, 7, 1, 9); insert into `score` values (27, 7, 2, 100); insert into `score` values (28, 7, 3, 67); insert into `score` values (29, 7, 4, 88); insert into `score` values (30, 8, 1, 9); insert into `score` values (31, 8, 2, 100); insert into `score` values (32, 8, 3, 67); insert into `score` values (33, 8, 4, 88); insert into `score` values (34, 9, 1, 91); insert into `score` values (35, 9, 2, 88); insert into `score` values (36, 9, 3, 67); insert into `score` values (37, 9, 4, 22); insert into `score` values (38, 10, 1, 90); insert into `score` values (39, 10, 2, 77); insert into `score` values (40, 10, 3, 43); insert into `score` values (41, 10, 4, 87); insert into `score` values (42, 11, 1, 90); insert into `score` values (43, 11, 2, 77); insert into `score` values (44, 11, 3, 43); insert into `score` values (45, 11, 4, 87); insert into `score` values (46, 12, 1, 90); insert into `score` values (47, 12, 2, 77); insert into `score` values (48, 12, 3, 43); insert into `score` values (49, 12, 4, 87); insert into `score` values (52, 13, 3, 87); -- ---------------------------- -- table structure for student -- ---------------------------- drop table if exists `student`; create table `student` ( `sid` int(11) not null auto_increment, `gender` char(1) character set utf8 collate utf8_general_ci not null, `class_id` int(11) not null, `sname` varchar(32) character set utf8 collate utf8_general_ci not null, primary key (`sid`) using btree, index `fk_class`(`class_id`) using btree, constraint `fk_class` foreign key (`class_id`) references `class` (`cid`) on delete restrict on update restrict ) engine = innodb auto_increment = 17 character set = utf8 collate = utf8_general_ci row_format = dynamic; -- ---------------------------- -- records of student -- ---------------------------- insert into `student` values (1, '男', 1, '理解'); insert into `student` values (2, '女', 1, '钢蛋'); insert into `student` values (3, '男', 1, '张三'); insert into `student` values (4, '男', 1, '张一'); insert into `student` values (5, '女', 1, '张二'); insert into `student` values (6, '男', 1, '张四'); insert into `student` values (7, '女', 2, '铁锤'); insert into `student` values (8, '男', 2, '李三'); insert into `student` values (9, '男', 2, '李一'); insert into `student` values (10, '女', 2, '李二'); insert into `student` values (11, '男', 2, '李四'); insert into `student` values (12, '女', 3, '如花'); insert into `student` values (13, '男', 3, '刘三'); insert into `student` values (14, '男', 3, '刘一'); insert into `student` values (15, '女', 3, '刘二'); insert into `student` values (16, '男', 3, '刘四'); -- ---------------------------- -- table structure for teacher -- ---------------------------- drop table if exists `teacher`; create table `teacher` ( `tid` int(11) not null auto_increment, `tname` varchar(32) character set utf8 collate utf8_general_ci not null, primary key (`tid`) using btree ) engine = innodb auto_increment = 6 character set = utf8 collate = utf8_general_ci row_format = dynamic; -- ---------------------------- -- records of teacher -- ---------------------------- insert into `teacher` values (1, '张磊老师'); insert into `teacher` values (2, '李平老师'); insert into `teacher` values (3, '刘海燕老师'); insert into `teacher` values (4, '朱云海老师'); insert into `teacher` values (5, '李杰老师'); -- ---------------------------- -- view structure for teacher2course -- ---------------------------- drop view if exists `teacher2course`; create algorithm = undefined sql security definer view `teacher2course` as select `teacher`.`tid` as `tid`,`teacher`.`tname` as `tname`,`course`.`cid` as `cid`,`course`.`cname` as `cname`,`course`.`teacher_id` as `teacher_id` from (`teacher` join `course` on((`teacher`.`tid` = `course`.`teacher_id`))); set foreign_key_checks = 1;
-- 创建视图 create view teacher2course as select * from teacher inner join course on teacher.tid=course.teacher_id -- 查看创建的视图 select * from teacher2course
tid tname cid cname teacher_id
1 张磊老师 1 生物 1
2 李平老师 2 物理 2
3 刘海燕老师 3 体育 3
2 李平老师 4 美术 2
这里需要强调几点：
drop view teacher2course
思考：真实开发过程中是否会使用视图？
我们已经说过，视图是mysql的功能，这个功能主要用于查询，但是如果一个项目中使用了很多视图，那么如果项目某个功能需要修改的时候，就会需要对视图进行修改，这时候就需要在mysql端将视图进行修改，然后再去应用程序修改对应的sql语句，其实这就会导致一个跨部门沟通问题，部门与部门沟通并不是不可以，但是我们应该在软件代码层面上尽量减少这么沟通次数，因为一方面人与人之间的交往问题，另一方面也是项目扩展高效性的一方面考虑。一般程序扩展功能都是通过修改sql语句来完成的。（以上仅个人意见，欢迎交流）
-- 针对插入时触发sql代码...
create trigger tri_after_insert_t1 after insert on 表名 for each row -- 插入后触发
begin
sql代码...
end
create trigger tri_before_insert_t2 before insert on 表名 for each row -- 插入前触发
begin
sql代码...
end
-- ------------------------------------------------------------------------------
-- 针对删除时触发sql代码...
create trigger 触发器名 after delete on 表名 for each row -- 删除后触发
begin
sql代码...
end
create trigger 触发器名 before delete on 表名 for each row -- 删除前触发
begin
sql代码...
end
-- ------------------------------------------------------------------------------
-- 针对修改时触发sql代码...
create trigger 触发器名 after update on 表名 for each row -- 修改后触发
begin
sql代码...
end
create trigger 触发器名 before update on 表名 for each row -- 修改前触发
begin
sql代码...
end
以上触发器的创建代码格式比较固定，只是分了6种情况而已
-- 创建2张表
create table cmd(
id int primary key auto_increment,
user char(32),
priv char(10),
cmd char(64),
sub_time datetime,
success enum('yes','no')
);
create table errlog(
id int primary key auto_increment,
err_cmd char(64),
err_time datetime
);
-- 创建触发器
delimiter // -- 将mysql默认的结束符换成//
create trigger tri_after_insert_cmd after insert on cmd for each row
begin
if new.success='no' then -- 用new代表mysql捕获并封装成的新纪录对象
insert into errlog(err_cmd,err_time) values (new.cmd,new.sub_time);
end if; -- if语句结束语
end // -- 前面讲结束符改为//。这里写上//代表触发器创建完毕，结束
delimiter ; -- 结束后记得将结束符改回默认；
-- 插入数据；
insert into cmd(user,priv,cmd,sub_time,success) values
('王大锤','0755','ls -l /etc',now(),'yes'), -- now（）代表获取当前时间
('孙大炮','0755','cat /etc/passwd',now(),'no'),
('李大大','0755','useradd xxx',now(),'no'),
('赵州桥','0755','ps aux',now(),'yes');
-- 向cmd表插入数据时候，触发器触发，会根据触发器内if条件语句判断是否决定插入错误日志
-- 查询errlog表记录，看看是否触发了触发器
select * from errlog;
-- 结果：
-- id err_cmd err_time
-- 1 cat /etc/passwd 2019-05-17 16:03:23
-- 2 useradd xxx 2019-05-17 16:03:23
-- 删除触发器
drop trigger tri_after_insert_cmd;
create table user(
id int primary key auto_increment,
name char(32),
balance int
);
insert into user(name,balance) values
('李逍遥',1000),
('酒剑仙',1000),
('赵灵儿',1000);
-- 修改数据之前开启事务操作：
start transaction;
-- 修改操作
update user set balance=900 where id=1;-- 买支付100元
update user set balance=900 where id=2;-- 中介拿走10元
update user set balance=900 where id=3;-- 卖家拿到90元
-- 查看修改后的表：
select * from user;
-- 结果
-- id name balance
-- 1 李逍遥 900
-- 2 酒剑仙 900
-- 3 赵灵儿 900
-- 注意注意
-- 事务下的sql语句执行完毕后并没有最终对数据库数据产生实质性改变，如果要
-- 产生最终结果生效，也就是数据真正地刷新到硬盘，就必须要执行一段提交的语句
-- 在执行提交语句前，进行的修改还可以还原，也就是sql回滚语句
rollback;
-- 再查看表：
select * from user;
-- 结果（数据还原了）
-- id name balance
-- 1 李逍遥 1000
-- 2 酒剑仙 1000
-- 3 赵灵儿 1000
-- 再次执行修改操作并提交：
-- 修改操作
update user set balance=900 where id=1;-- 买支付100元
update user set balance=900 where id=2;-- 中介拿走10元
update user set balance=900 where id=3;-- 卖家拿到90元
commit;
select * from user;
-- 结果
-- id name balance
-- 1 李逍遥 900
-- 2 酒剑仙 900
-- 3 赵灵儿 900
-- 思考：
-- 如果站在python代码的角度，该如何实现检测事务内操作的全部成功性，不成功就回滚到前一个状态：
update user set balance=900 where id=1;-- 买支付100元
update user set balance=900 where id=2;-- 中介拿走10元
update user set balance=900 where id=3;-- 卖家拿到90元
if 检测到三方的余额都发生应该有的变化:
commint;
else:
rollblack;
delimiter //
create procedure p1(
in m int, -- in表示这个参数必须只能是传入不能被返回出去
in n int,
out res int) -- out表示这个参数可以被返回出去，还有一个inout表示传入传出可返回出去
begin
select tname from teacher where tid > m and tid < n;
set res = 0;
end //
delimiter ;
-- 直接在mysql中调用：
delimiter //
create procedure p1(
in m int, -- in表示这个参数必须只能是传入不能被返回出去
in n int,
out res int) -- out表示这个参数可以被返回出去，还有一个inout表示传入传出可返回出去
begin
select tname from teacher where tid > m and tid < n;
set res = 0;
end //
delimiter ;
-- 需要注意：存储过程在哪个数据库下创建的就必须只能在对应的数据库下面使用
-- 直接在mysql中调用
set @res=8 -- res是判断存储过程是否被执行成功的依据，在这里需要先定义一个变量@res=8
-- call p1(2,4,8); -- 如果直接传8则报错，必须传事先定义的变量@res，这是规定语法规范
call p1(2,4,@res);
-- tname
-- 刘海燕老师
--查看结果
select @res; -- res=0
-- 存储过程被调用，@res变量值发生变化
-- 在python程序中调用：
import pymysql
conn = pymysql.connect(
host='127.0.0.1',
port=3306,
user='root',
password='sgtkey',
database='day41',
charset='utf8'
)
cursor = conn.cursor(cursor=pymysql.cursors.dictcursor)
cursor.callproc('p1', (2, 4, 8)) # 这里就不需要提前定义个变量@res了，pymsql自动给你做了这件事
# 这里的内部原理：@_p1_0=2,@_p1_1=4,@_p1_2=8;
print(cursor.fetchall()) # 结果 [{'tname': '刘海燕老师'}]
cursor.execute('select @_p1_2')
print(cursor.fetchall()) # 结果 [{'@_p1_2': 0}]
-- 存储过程与事务使用的举例
delimiter //
create procedure p5( -- 创建存储过程p5
out p_return_code tinyint
)
begin
-- 这里表示如果捕获到异常，则执行下面set p_return_code = 1并且回滚操作
declare exit handler for sqlexception
begin
-- error
set p_return_code = 1;
rollback;
end;
-- 这里表示如果捕获sql警告，则执行下面set p_return_code = 2并且回滚操作
declare exit handler for sqlwarning
begin
-- warnings
set p_return_code = 2;
rollback;
end;
start transaction; # 开始启用事务
update user set balance=900 where id =1;
update user set balance=1010 where id = 2;
update user set balance=1090 where id =3;
commit; # 如果没有出现异常或者警告就会继续执行提交语句
-- success
set p_return_code = 0; -- 代表执行成功
end //
delimiter ;
注意！ 函数中不要写sql语句（否则会报错），函数仅仅只是一个功能，是一个在sql中被应用的功能 若要想在begin...end...中写sql，请用存储过程
一、数学函数
round(x,y)
返回参数x的四舍五入的有y位小数的值
rand()
返回０到１内的随机值,可以通过提供一个参数(种子)使rand()随机数生成器生成一个指定的值。
二、聚合函数(常用于group by从句的select查询中)
avg(col)返回指定列的平均值
count(col)返回指定列中非null值的个数
min(col)返回指定列的最小值
max(col)返回指定列的最大值
sum(col)返回指定列的所有值之和
group_concat(col) 返回由属于一组的列值连接组合而成的结果
三、字符串函数
char_length(str)
返回值为字符串str 的长度，长度的单位为字符。一个多字节字符算作一个单字符。
concat(str1,str2,...)
字符串拼接
如有任何一个参数为null ，则返回值为 null。
concat_ws(separator,str1,str2,...)
字符串拼接（自定义连接符）
concat_ws()不会忽略任何空字符串。 (然而会忽略所有的 null）。
conv(n,from_base,to_base)
进制转换
例如：
select conv('a',16,2); 表示将 a 由16进制转换为2进制字符串表示
format(x,d)
将数字x 的格式写为'#,###,###.##',以四舍五入的方式保留小数点后 d 位， 并将结果以字符串的形式返回。若 d 为 0, 则返回结果不带有小数点，或不含小数部分。
例如：
select format(12332.1,4); 结果为： '12,332.1000'
insert(str,pos,len,newstr)
在str的指定位置插入字符串
pos：要替换位置其实位置
len：替换的长度
newstr：新字符串
特别的：
如果pos超过原字符串长度，则返回原字符串
如果len超过原字符串长度，则由新字符串完全替换
instr(str,substr)
返回字符串 str 中子字符串的第一个出现位置。
left(str,len)
返回字符串str 从开始的len位置的子序列字符。
lower(str)
变小写
upper(str)
变大写
reverse(str)
返回字符串 str ，顺序和字符顺序相反。
substring(str,pos) , substring(str from pos) substring(str,pos,len) , substring(str from pos for len)
不带有len 参数的格式从字符串str返回一个子字符串，起始于位置 pos。带有len参数的格式从字符串str返回一个长度同len字符相同的子字符串，起始于位置 pos。 使用 from的格式为标准 sql 语法。也可能对pos使用一个负值。假若这样，则子字符串的位置起始于字符串结尾的pos 字符，而不是字符串的开头位置。在以下格式的函数中可以对pos 使用一个负值。
mysql> select substring('quadratically',5);
-> 'ratically'
mysql> select substring('foobarbar' from 4);
-> 'barbar'
mysql> select substring('quadratically',5,6);
-> 'ratica'
mysql> select substring('sakila', -3);
-> 'ila'
mysql> select substring('sakila', -5, 3);
-> 'aki'
mysql> select substring('sakila' from -4 for 2);
-> 'ki'
四、日期和时间函数
curdate()或current_date() 返回当前的日期
curtime()或current_time() 返回当前的时间
dayofweek(date) 返回date所代表的一星期中的第几天(1~7)
dayofmonth(date) 返回date是一个月的第几天(1~31)
dayofyear(date) 返回date是一年的第几天(1~366)
dayname(date) 返回date的星期名，如：select dayname(current_date);
from_unixtime(ts,fmt) 根据指定的fmt格式，格式化unix时间戳ts
hour(time) 返回time的小时值(0~23)
minute(time) 返回time的分钟值(0~59)
month(date) 返回date的月份值(1~12)
monthname(date) 返回date的月份名，如：select monthname(current_date);
now() 返回当前的日期和时间
quarter(date) 返回date在一年中的季度(1~4)，如select quarter(current_date);
week(date) 返回日期date为一年中第几周(0~53)
year(date) 返回日期date的年份(1000~9999)
重点:
date_format(date,format) 根据format字符串格式化date值
mysql> select date_format('2009-10-04 22:23:00', '%w %m %y');
-> 'sunday october 2009'
mysql> select date_format('2007-10-04 22:23:00', '%h:%i:%s');
-> '22:23:00'
mysql> select date_format('1900-10-04 22:23:00',
-> '%d %y %a %d %m %b %j');
-> '4th 00 thu 04 10 oct 277'
mysql> select date_format('1997-10-04 22:23:00',
-> '%h %k %i %r %t %s %w');
-> '22 22 10 10:23:00 pm 22:23:00 00 6'
mysql> select date_format('1999-01-01', '%x %v');
-> '1998 52'
mysql> select date_format('2006-06-00', '%d');
-> '00'
五、加密函数
md5()
计算字符串str的md5校验和
password(str)
返回字符串str的加密版本，这个加密过程是不可逆转的，和unix密码加密过程使用不同的算法。
六、控制流函数
case when[test1] then [result1]...else [default] end
如果testn是真，则返回resultn，否则返回default
case [test] when[val1] then [result]...else [default]end
如果test和valn相等，则返回resultn，否则返回default
if(test,t,f)
如果test是真，返回t；否则返回f
ifnull(arg1,arg2)
如果arg1不是空，返回arg1，否则返回arg2
nullif(arg1,arg2)
如果arg1=arg2返回null；否则返回arg1
七、控制流函数小练习
#7.1、准备表
/*
navicat mysql data transfer
source server : localhost_3306
source server version : 50720
source host : localhost:3306
source database : student
target server type : mysql
target server version : 50720
file encoding : 65001
date: 2018-01-02 12:05:30
*/
set foreign_key_checks=0;
-- ----------------------------
-- table structure for course
-- ----------------------------
drop table if exists `course`;
create table `course` (
`c_id` int(11) not null,
`c_name` varchar(255) default null,
`t_id` int(11) default null,
primary key (`c_id`),
key `t_id` (`t_id`)
) engine=innodb default charset=utf8;
-- ----------------------------
-- records of course
-- ----------------------------
insert into `course` values ('1', 'python', '1');
insert into `course` values ('2', 'java', '2');
insert into `course` values ('3', 'linux', '3');
insert into `course` values ('4', 'web', '2');
-- ----------------------------
-- table structure for score
-- ----------------------------
drop table if exists `score`;
create table `score` (
`id` int(11) not null auto_increment,
`s_id` int(10) default null,
`c_id` int(11) default null,
`num` double default null,
primary key (`id`)
) engine=innodb auto_increment=12 default charset=utf8;
-- ----------------------------
-- records of score
-- ----------------------------
insert into `score` values ('1', '1', '1', '79');
insert into `score` values ('2', '1', '2', '78');
insert into `score` values ('3', '1', '3', '35');
insert into `score` values ('4', '2', '2', '32');
insert into `score` values ('5', '3', '1', '66');
insert into `score` values ('6', '4', '2', '77');
insert into `score` values ('7', '4', '1', '68');
insert into `score` values ('8', '5', '1', '66');
insert into `score` values ('9', '2', '1', '69');
insert into `score` values ('10', '4', '4', '75');
insert into `score` values ('11', '5', '4', '66.7');
-- ----------------------------
-- table structure for student
-- ----------------------------
drop table if exists `student`;
create table `student` (
`s_id` varchar(20) not null,
`s_name` varchar(255) default null,
`s_age` int(10) default null,
`s_sex` char(1) default null,
primary key (`s_id`)
) engine=innodb default charset=utf8;
-- ----------------------------
-- records of student
-- ----------------------------
insert into `student` values ('1', '鲁班', '12', '男');
insert into `student` values ('2', '貂蝉', '20', '女');
insert into `student` values ('3', '刘备', '35', '男');
insert into `student` values ('4', '关羽', '34', '男');
insert into `student` values ('5', '张飞', '33', '女');
-- ----------------------------
-- table structure for teacher
-- ----------------------------
drop table if exists `teacher`;
create table `teacher` (
`t_id` int(10) not null,
`t_name` varchar(50) default null,
primary key (`t_id`)
) engine=innodb default charset=utf8;
-- ----------------------------
-- records of teacher
-- ----------------------------
insert into `teacher` values ('1', '大王');
insert into `teacher` values ('2', 'alex');
insert into `teacher` values ('3', 'egon');
insert into `teacher` values ('4', 'peiqi');
#7.2、统计各科各分数段人数.显示格式:课程id,课程名称,[100-85],[85-70],[70-60],[ <60]
select score.c_id,
course.c_name,
sum(case when num between 85 and 100 then 1 else 0 end) as '[100-85]',
sum(case when num between 70 and 85 then 1 else 0 end) as '[85-70]',
sum(case when num between 60 and 70 then 1 else 0 end) as '[70-60]',
sum(case when num < 60 then 1 else 0 end) as '[ <60]'
from score,course where score.c_id=course.c_id group by score.c_id;
--date_format的一些应用：
-- 准备表和记录
create table emp_info(
id int primary key auto_increment,
name varchar(32),
hiredate date);
insert into emp_info(name,hiredate) values
('sgt','2009-09-02'),
('wdc','2010-11-20'),
('qxl','2010-11-12'),
('ffz','2009-09-18'),
('yl','2014-06-16'),
('fqy','2014-06-05'),
('cnj','2012-04-28'),
('bvf','2009-09-18'),
('hge','2012-04-22');
-- 提取hire_date字段的值，按照格式化后结果年-月来分组。
select date_format(hiredate,'%y-%m'),count(id) from emp_info
group by date_format(hiredate,'%y-%m')
--结果
-- date_format(hiredate,'%y-%m') count(id)
-- 2009-09 3
-- 2010-11 2
-- 2012-04 2
-- 2014-06 2
-- 按时间分组，统计同一入职时间段的员工人数
http://www.cnblogs.com/linhaifeng/articles/7495918.html#_label2
# if条件语句
delimiter //
create procedure proc_if ()
begin
declare i int default 0;
if i = 1 then
select 1;
elseif i = 2 then
select 2;
else
select 7;
end if;
end //
delimiter ;
# while循环
delimiter //
create procedure proc_while ()
begin
declare num int ;
set num = 0 ;
while num < 10 do
select
num ;
set num = num + 1 ;
end while ;
end //
delimiter ;
-- 覆盖索引 select name from user where name = 'sgt'; 此语句叫做覆盖索引 只在辅助索引的叶子节点中就找到了我们想要的数据记录 -- 非覆盖索引 select age from user where name = 'sgt'; 此语句叫非覆盖索引，虽然查询的时候用的是name索引字段，但是要查询的是age字段。
#1. 准备表
create table s1(
id int,
name varchar(20),
gender char(6),
email varchar(50)
);
#2. 创建存储过程，实现批量插入记录
delimiter $$ #声明存储过程的结束符号为$$
create procedure auto_insert1()
begin
declare i int default 1;
while(i<3000000)do
insert into s1 values(i,'jason','male',concat('jason',i,'@oldboy'));
set i=i+1;
end while;
end$$ #$$结束
delimiter ; #重新声明 分号为结束符号
#3. 查看存储过程
show create procedure auto_insert1\g
#4. 调用存储过程
call auto_insert1();
# 表没有任何索引的情况下 select * from s1 where id=30000; # 避免打印带来的时间损耗 select count(id) from s1 where id = 30000; select count(id) from s1 where id = 1; # 给id做一个主键 alter table s1 add primary key(id); # 速度很慢 select count(id) from s1 where id = 1; # 速度相较于未建索引之前两者差着数量级 select count(id) from s1 where name = 'jason' # 速度仍然很慢 """ 范围问题 """ # 并不是加了索引，以后查询的时候按照这个字段速度就一定快 select count(id) from s1 where id > 1; # 速度相较于id = 1慢了很多 select count(id) from s1 where id >1 and id < 3; select count(id) from s1 where id > 1 and id < 10000; select count(id) from s1 where id != 3; alter table s1 drop primary key; # 删除主键 单独再来研究name字段 select count(id) from s1 where name = 'jason'; # 又慢了 create index idx_name on s1(name); # 给s1表的name字段创建索引 select count(id) from s1 where name = 'jason' # 仍然很慢！！！ """ 再来看b+树的原理，数据需要区分度比较高，而我们这张表全是jason，根本无法区分 那这个树其实就建成了“一根棍子” """ select count(id) from s1 where name = 'xxx'; # 这个会很快，我就是一根棍，第一个不匹配直接不需要再往下走了 select count(id) from s1 where name like 'xxx'; select count(id) from s1 where name like 'xxx%'; select count(id) from s1 where name like '%xxx'; # 慢 最左匹配特性 # 区分度低的字段不能建索引 drop index idx_name on s1; # 给id字段建普通的索引 create index idx_id on s1(id); select count(id) from s1 where id = 3; # 快了 select count(id) from s1 where id*12 = 3; # 慢了 索引的字段一定不要参与计算 drop index idx_id on s1; select count(id) from s1 where name='jason' and gender = 'male' and id = 3 and email = 'xxx'; # 针对上面这种连续多个and的操作，mysql会从左到右先找区分度比较高的索引字段，先将整体范围降下来再去比较其他条件 create index idx_name on s1(name); select count(id) from s1 where name='jason' and gender = 'male' and id = 3 and email = 'xxx'; # 并没有加速 drop index idx_name on s1; # 给name，gender这种区分度不高的字段加上索引并不难加快查询速度 create index idx_id on s1(id); select count(id) from s1 where name='jason' and gender = 'male' and id = 3 and email = 'xxx'; # 快了 先通过id已经讲数据快速锁定成了一条了 select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx'; # 慢了 基于id查出来的数据仍然很多，然后还要去比较其他字段 drop index idx_id on s1 create index idx_email on s1(email); select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx'; # 快 通过email字段一剑封喉
select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx'; # 如果上述四个字段区分度都很高，那给谁建都能加速查询 # 给email加然而不用email字段 select count(id) from s1 where name='jason' and gender = 'male' and id > 3; # 给name加然而不用name字段 select count(id) from s1 where gender = 'male' and id > 3; # 给gender加然而不用gender字段 select count(id) from s1 where id > 3; # 带来的问题是所有的字段都建了索引然而都没有用到，还需要花费四次建立的时间 create index idx_all on s1(email,name,gender,id); # 最左匹配原则，区分度高的往左放 select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx'; # 速度变快
慢查询日志
设定一个时间检测所有超出改时间的sql语句，然后针对性的进行优化！
如对本文有疑问，请在下面进行留言讨论，广大热心网友会与你互动！！ 点击进行留言回复
Python爬虫:Request Payload和Form Data的简单区别说明
浅谈Python中threading join和setDaemon用法及区别说明
Python3-异步进程回调函数(callback())介绍
python继承threading.Thread实现有返回值的子类实例
Python中使用threading.Event协调线程的运行详解
网友评论