MySQL
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
mysql事务:begin、rollback、commit MYSQL的事务处理主要有两种方法。 1、用begin,rollback,commit来实现 begin 开始一个事务 rollback 事务回滚 commit 事务确认 直接用set来改变mysql的自动提交模式 MYSQL默认是自动提交的,也就是你提交一个QUERY,它就直接执行!我们可以通过 set autocommit=0 禁止自动提交 set autocommit=1 开启自动提交 来实现事务的处理。 但注意当你用 set autocommit=0 的时候,你以后所有的SQL都将做为事务处理,直到你用commit确认或rollback结束,注意当你结束这个事务的同时也开启了个新的事务!按第一种方法只将当前的作为一个事务! 个人推荐使用第一种方法! MYSQL中只有INNODB和BDB类型的数据表才能支持事务处理!其他的类型是不支持的!(切记!) create table color1( nid int, name char(16) not null); insert into color1 (nid,name) values (1,"red"),(2,"blue"); #另一个mysql可以看到插入的结果; begin; insert into color1 (nid,name) values (3,"green"),(4,"yellow"); commit; #未commit前,本client可以查到配置结果,但是其他client查不到结果;此时client因意外退出,mysql会自动回滚 #结果不符合预期,那么执行 rollback操作来回滚事物
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
mysql数据库备份与还原 ========================= #备份数据库 mysqldump -h127.0.0.1 -uroot -p123456 库 >lianjia.sql mysqldump -h127.0.0.1 -uroot -p123456 lianjia >lianjia.sql #备份数据库表 mysqldump -h127.0.0.1 -uroot -p123456 库 表 >color1.sql mysqldump -h127.0.0.1 -uroot -p123456 lianjia color1 >color1.sql #删除了部分数据 delete from color1 where name = "green"; drop table color1; #导入数据 mysql -h127.0.0.1 -uroot -p123456 库 < color1.sql #导出时可以导出表,导入时直接选择库即可 mysql -h127.0.0.1 -uroot -p123456 lianjia < color1.sql #这一步相当于还原,而不是新增操作 #删除表,也可以直接按上面的操作,直接导入
1.连接客户端
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
客户端连接(启动客户端) 将mysql服务端制作成一个Windows服务 1.CMD命令:net start mysql net stop mysql 2.或者通过任务管理器打开:任务管理器-服务-服务-找到mysql 3.win启动:C:\mysql-5.7.16-winx64\mysql-5.7.16-winx64\mysql 4.mysql.server start #linux启动 /etc/init.d/mysql start 客户端连接(用户登录) C:\mysql-5.7.16-winx64\mysql-5.7.16-winx64\mysql -u root -p MySQL初始化 cd c:\mysql-5.7.16-winx64\bin 先创建一个data文件夹; cmd进入bin文件夹后, 1.mysqld --initialize-insecure#默认初始化命令,初始化了一个root用户,没有密码 2.mysqld #启动服务器端 再开一个cmd作为用户端,进入bin文件夹 Mysql -u root -p 连接至客户端 便捷操作方式一:增加环境变量 便捷操作方式二:将mysql服务端制作成一个windows服务 C:\Windows\System32>c:\mysql-5.7.21-winx64\bin\mysqld --install Net start mysql Net stop mysql 或者通过任务管理器打开win服务,效果等同 mysql -u username -h 127.0.0.1 -p#登录时,指定服务器ip
2.数据库操作
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1、显示数据库 show databases;#显示数据库 2、创建数据库 create database 数据库名称 default charset utf8 collate utf8_general_ci;#创建数据库 create database 数据库名称 default character set gbk collate gbk_chinese_ci; 3、使用数据库 use 数据库名称; #使用数据库 4、用户管理 create user '用户名'@'IP地址' identified by '密码'; #创建用户 drop user '用户名'@'IP地址'; #删除用户 rename user '用户名'@'IP地址'; to '新用户名'@'IP地址'; #修改用户 set password for '用户名'@'IP地址' = Password('新密码'); #修改密码 PS:用户权限相关数据保存在mysql数据库的user表中,所以也可以直接对其进行操作(不建议) 5、授权管理 show grants for '用户'@'IP地址' #查看权限 grant 权限 on 数据库.表 to '用户'@'IP地址' #授权 revoke 权限 on 数据库.表 from '用户'@'IP地址' #取消权限 通配符%,all privileges除grant外的所有权限,select仅查权限,select,insert查和插入权限 创建用户时,指定用户登录地址使用通配符%时,需要加上双引号“” 用户名@IP地址 用户只能在改IP下才能访问 用户名@192.168.1.% 用户只能在改IP段下才能访问(通配符%表示任意) 用户名@% 用户可以再任意IP下访问(默认IP地址为%) grant all privileges on db1.tb1 TO '用户名'@'IP' grant select on db1.* TO '用户名'@'IP' grant select,insert on *.* TO '用户名'@'IP' revoke select on db1.tb1 from '用户名'@'IP' 忘记密码 1启动免授权服务端 mysqld --skip-grant-tables 2客户端 mysql -u root -p 3修改用户名密码 update mysql.user set authentication_string=password('666') where user='root'; flush privileges;
3.数据表操作
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
是否可空,null表示空,非字符串 默认值,创建列时可以指定默认值,当插入数据时如果未主动设置,则自动添加默认值 自增,如果为某列设置自增列,插入数据时无需设置此列,默认将自增(表中只能有一个自增列) 注意:1、对于自增列,必须是索引(含主键)。2、对于自增可以设置步长和起始值 主键,一种特殊的唯一索引,不允许有空值,如果主键使用单个列,则它的值必须唯一,如果是多列,则其组合必须唯一。 外键,一个特殊的索引,只能是指定内容 create table 表名( 列名 类型 是否可以为空, 列名 类型 是否可以为空 )ENGINE=InnoDB DEFAULT CHARSET=utf8 create table 表名( 列名 int not null auto_increment primary key, 列名 varchar(20) not null defalut 2, 列名 int default 888)engine = innodb default charset=utf8; #自增可以设置步长和起始值 creat table color( nid int not null primary key, name char(16) not null); create table fruit( nid int not null primary key, smt char(32) null , color_id int not null, constraint fk_cc foreign key (color_id) references color(nid));设置外键 Show tables; Desc 表名; drop table 表名; 删除表 delete from 表名; 清空表,自增不清除 truncate table 表名;清空表,自增清除, 貌似效率更高? 添加列:alter table 表名 add 列名 类型 删除列:alter table 表名 drop column 列名 修改列:alter table 表名 modify column 列名 类型; -- 类型 #不修改列名,修改其他 alter table 表名 change 原列名 新列名 类型; -- 列名,类型#包含修改列名 添加主键:alter table 表名 add primary key(列名); 删除主键:alter table 表名 drop primary key; alter table 表名 modify 列名 int, drop primary key; 添加外键:alter table 从表 add constraint 外键名称(形如:FK_从表_主表) foreign key 从表(外键字段) references 主表(主键字段); 删除外键:alter table 表名 drop foreign key 外键名称 修改默认值:alter table testalter_tbl alter i SET DEFAULT 1000; 删除默认值:alter table testalter_tbl alter i DROP DEFAULT; MySQL的数据类型大致分为:数值、时间和字符串 数据类型: 二进制数:bit 整数:tinyint、int、bigint、 小数:decimal(准确)、float(不准确)、double(准确度比float高) 字符串:char(定长、占空间大、查找快) varchar(不定长,省空间,查找慢) text、mediumtext、longtext 枚举类型enum(创建时,设置enum值,多选一) 集合类型set(创建时,设置set值,多选N) DATE:YYYY-MM-DD(1000-01-01/9999-12-31) TIME:HH:MM:SS('-838:59:59'/'838:59:59') YEAR:YYYY(1901/2155) DATETIME:YYYY-MM-DD HH:MM:SS(1000-01-01 00:00:00/9999-12-31 23:59:59 Y) TIMESTAMP:YYYYMMDD HHMMSS(1970-01-01 00:00:00/2037 年某时)
4.表内容基本操作
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
增 insert into 表 (列名,列名...) values (值,值,值...) insert into 表 (列名,列名...) values (值,值,值...),(值,值,值...) insert into 表 (列名,列名...) select (列名,列名...) from 表#将另一张表的内容直接插入表中 删 delete from 表 delete from 表 where id=1 and name='alex' 改 update 表 set name = 'alex' where id>1 查 desc tb1;#查看表中的所有列名 select * from 表 select * from 表 where id > 1 select nid,name,gender as gg from 表 where id > 1 其他(与增删改查配合使用) a、条件 select * from 表 where id > 1 and name != 'alex' and num = 12; select * from 表 where id between 5 and 16; select * from 表 where id in (11,22,33) select * from 表 where id not in (11,22,33) select * from 表 where id in (select nid from 表) b、通配符 select * from 表 where name like 'ale%' - ale开头的所有(多个字符串) select * from 表 where name like 'ale_' - ale开头的所有(一个字符) c、限制(分页) select * from 表 limit 5; - 前5行 select * from 表 limit 4,5; - 从第4行开始的5行 select * from 表 limit 5 offset 4 - 从第4行开始的5行 效果同上一条,推荐使用这个 d、排序 select * from 表 order by 列 asc - 根据 “列” 从小到大排列 select * from 表 order by 列 desc - 根据 “列” 从大到小排列 select * from 表 order by 列1 desc,列2 asc - 根据 “列1” 从大到小排列,如果相同则按列2从小到大排序 e、分组 select num from 表 group by num select num,nid from 表 group by num,nid select num,nid from 表 where nid > 10 group by num,nid order nid desc select num,nid,count(*),sum(score),max(score),min(score) from 表 group by num,nid select num from 表 group by num having max(id) > 10#根据聚合函数的结果来筛选时,使用having,不能使用where 特别的:group by 必须在where之后,order by之前 f、连表 无对应关系则不显示 select A.num, A.name, B.name #没有where时,比如A.num有5组数据,B.name有3组数据,结果显示为3*5=15组数据 from A,B Where A.nid = B.nid left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录 right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录 inner join(等值连接) 只返回两个表中联结字段相等的行 区别在于inner join:无对应关系则不显示;left join:无对应关系,则值为null 无对应关系则不显示# select A.num, A.name, B.name 如果表1中的数据多,表2少,只显示都有的 from A inner join B 无对应关系则不显示 on A.nid = B.nid A表所有显示,如果B中无对应关系,则值为null select A.num, A.name, B.name 如果表1中的数据多,表2少,则显示为null from A left join B #貌似后面不能跟where 无对应关系,则值为null on A.nid = B.nid left join = left outer join B表所有显示,如果B中无对应关系,则值为null select A.num, A.name, B.name from A right join B 无对应关系,则值为null on A.nid = B.nid g、组合 组合,自动处理重合 select nickname from A union select name from B 组合,不处理重合 select nickname from A union all select name from B
示例及python操作MySQL示例
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
导出现有数据库数据: mysqldump -u用户名 -p密码 数据库名称 >导出文件路径 # 结构+数据 mysqldump -u用户名 -p密码 -d 数据库名称 >导出文件路径 # 结构 D:\>mysqldump -uroot -p test > 1.sql 导入现有数据库数据: mysqldump -uroot -p密码 数据库名称 < 文件路径 例子: C:\Windows\System32>mysqldump -u root -p test1 < d:\authdb.sql
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
Python操作MySQL https://pypi.python.org/pypi pip3 install pymysql # 下载本地 # 解压到执行目录 # python2,默认无pip命令 # python3,默认自带pip3命令 python3 -m pip install --upgrade pip yum install xxxx apt-get install ... brew install ... easyinstall … import pymysql conn = pymysql.connect(host = '127.0.0.1',port = 3306,user = 'root',passwd = '',db = 'test',charset = 'utf8') cursor = conn.cursor() #创建游标 # abc=input('输入你要插入的') # r = cursor.execute('insert into class(caption) values("%s")'%abc)#这样写会造成SQL注入,不使用这种方式 # r = cursor.execute('insert into class(caption) values(%s)',abc)#一定使用参数的这样形式 # cursor.execute('insert into student(gender,class_id,sname) values(%s,%s,%s)',('mail',1,'english'))#传入一组数据,使用executemany,可以包含多参数 # cursor.executemany('insert into class(caption) values(%s)',(('五年三班'),('范德萨班'),))#传入多组数据时,使用executemany,单组数据使用many会报错 # cursor.executemany('insert into student(gender,class_id,sname)values(%s,%s,%s)',(('女',1,'范德萨'),('女',2,'范德萨'),)) # conn.commit()#非增删改操作,即查操作时,可以不用commit r = cursor.execute('select * from student')#execute的返回值是受影响的条数 result = cursor.fetchall()#实际上在execute时,已经把所有的结果都存在了内存中。而fetchall,one,many只是去内存中拿指定条数而已 # result = cursor.fetchone() # result = cursor.fetchmany(3) # cursor.scroll(0,mode='absolute')#指针移动到绝对位置 ‘0’ # cursor.scroll(2,mode = 'relative')#指针移动到相对位置,数值2,则指针往下走2个;可以为负 cursor.close()#关闭游标 conn.close()#关闭连接 # cursor = conn.cursor(cursor = pymysql.cursors.DictCursor)#配置游标,返回值为一个包含列名的字典 # nid = cursor.lastrowid#获取最新数据的自增id SQL注入是比较常见的网络攻击方式之一,它不是利用操作系统的BUG来实现攻击,而是针对程序员编程时的疏忽,通过SQL语句,实现无帐号登录,甚至篡改数据库。 从理论上说,后台认证程序中会有如下的SQL语句: String sql = "select * from user_table where username='userName' and password='password'"; 当输入了上面的用户名和密码,上面的SQL语句变成: SELECT * FROM user_table WHERE username='’or 1 = 1 -- and password='’后面加两个-,这意味着注释. https://www.cnblogs.com/baizhanshi/p/6002898.html
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
2、查询“生物”课程比“物理”课程成绩高的所有学生的学号; 思路: 获取所有有生物课程的人(学号,成绩) - 临时表 获取所有有物理课程的人(学号,成绩) - 临时表 根据【学号】连接两个临时表: 学号 物理成绩 生物成绩 然后再进行筛选 select A.student_id,sw,ty from (select student_id,num as sw from score left join course on score.course_id = course.cid where course.cname = '生物') as A left join (select student_id,num as ty from score left join course on score.course_id = course.cid where course.cname = '体育') as B on A.student_id = B.student_id where sw > if(isnull(ty),0,ty); 3、查询平均成绩大于60分的同学的学号和平均成绩; 思路: 根据学生分组,使用avg获取平均值,通过having对avg进行筛选 select student_id,avg(num) from score group by student_id having avg(num) > 60 4、查询所有同学的学号、姓名、选课数、总成绩; select score.student_id,sum(score.num),count(score.student_id),student.sname from score left join student on score.student_id = student.sid group by score.student_id 5、查询姓“李”的老师的个数; select count(tid) from teacher where tname like '李%' select count(1) from (select tid from teacher where tname like '李%') as B 6、查询没学过“叶平”老师课的同学的学号、姓名; 思路: 先查到“李平老师”老师教的所有课ID 获取选过课的所有学生ID 学生表中筛选 select * from student where sid not in ( select DISTINCT student_id from score where score.course_id in ( select cid from course left join teacher on course.teacher_id = teacher.tid where tname = '李平老师' ) ) 7、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名; 思路: 先查到既选择001又选择002课程的所有同学 根据学生进行分组,如果学生数量等于2表示,两门均已选择 select student_id,sname from (select student_id,course_id from score where course_id = 1 or course_id = 2) as B left join student on B.student_id = student.sid group by student_id HAVING count(student_id) > 1 8、查询学过“叶平”老师所教的所有课的同学的学号、姓名; 同上,只不过将001和002变成 in (叶平老师的所有课) 9、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名; 同第1题 10、查询有课程成绩小于60分的同学的学号、姓名; select sid,sname from student where sid in ( select distinct student_id from score where num < 60 ) 11、查询没有学全所有课的同学的学号、姓名; 思路: 在分数表中根据学生进行分组,获取每一个学生选课数量 如果数量 == 总课程数量,表示已经选择了所有课程 select student_id,sname from score left join student on score.student_id = student.sid group by student_id HAVING count(course_id) = (select count(1) from course) 12、查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名; 思路: 获取 001 同学选择的所有课程 获取课程在其中的所有人以及所有课程 根据学生筛选,获取所有学生信息 再与学生表连接,获取姓名 select student_id,sname, count(course_id) from score left join student on score.student_id = student.sid where student_id != 1 and course_id in (select course_id from score where student_id = 1) group by student_id 13、查询至少学过学号为“001”同学所有课的其他同学学号和姓名; 先找到和001的学过的所有人 然后个数 = 001所有学科 ==》 其他人可能选择的更多 select student_id,sname, count(course_id) from score left join student on score.student_id = student.sid where student_id != 1 and course_id in (select course_id from score where student_id = 1) group by student_id having count(course_id) = (select count(course_id) from score where student_id = 1) 14、查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名; 个数相同 002学过的也学过 select student_id,sname from score left join student on score.student_id = student.sid where student_id in ( select student_id from score where student_id != 1 group by student_id HAVING count(course_id) = (select count(1) from score where student_id = 1) ) and course_id in (select course_id from score where student_id = 1) group by student_id HAVING count(course_id) = (select count(1) from score where student_id = 1) 15、删除学习“叶平”老师课的score表记录; delete from score where course_id in ( select cid from course left join teacher on course.teacher_id = teacher.tid where teacher.name = '叶平' ) 16、向SC表中插入一些记录,这些记录要求符合以下条件:①没有上过编号“002”课程的同学学号;②插入“002”号课程的平均成绩; 思路: 由于insert 支持 inset into tb1(xx,xx) select x1,x2 from tb2; 所有,获取所有没上过002课的所有人,获取002的平均成绩 insert into score(student_id, course_id, num) select sid,2,(select avg(num) from score where course_id = 2) from student where sid not in ( select student_id from score where course_id = 2 ) 17、按平均成绩从低到高 显示所有学生的“语文”、“数学”、“英语”三门的课程成绩,按如下形式显示: 学生ID,语文,数学,英语,有效课程数,有效平均分; select sc.student_id, (select num from score left join course on score.course_id = course.cid where course.cname = "生物" and score.student_id=sc.student_id) as sy, (select num from score left join course on score.course_id = course.cid where course.cname = "物理" and score.student_id=sc.student_id) as wl, (select num from score left join course on score.course_id = course.cid where course.cname = "体育" and score.student_id=sc.student_id) as ty, count(sc.course_id), avg(sc.num) from score as sc group by student_id desc 18、查询各科成绩最高和最低的分:以如下形式显示:课程ID,最高分,最低分; select course_id, max(num) as max_num, min(num) as min_num from score group by course_id; 19、按各科平均成绩从低到高和及格率的百分数从高到低顺序; 思路:case when .. then select course_id, avg(num) as avgnum,sum(case when score.num > 60 then 1 else 0 END)/count(1)*100 as percent from score group by course_id order by avgnum asc,percent desc; 20、课程平均分从高到低显示(现实任课老师); select avg(if(isnull(score.num),0,score.num)),teacher.tname from course left join score on course.cid = score.course_id left join teacher on course.teacher_id = teacher.tid group by score.course_id 21、查询各科成绩前三名的记录:(不考虑成绩并列情况) select score.sid,score.course_id,score.num,T.first_num,T.second_num from score left join ( select sid, (select num from score as s2 where s2.course_id = s1.course_id order by num desc limit 0,1) as first_num, (select num from score as s2 where s2.course_id = s1.course_id order by num desc limit 3,1) as second_num from score as s1 ) as T on score.sid =T.sid where score.num <= T.first_num and score.num >= T.second_num 22、查询每门课程被选修的学生数; select course_id, count(1) from score group by course_id; 23、查询出只选修了一门课程的全部学生的学号和姓名; select student.sid, student.sname, count(1) from score left join student on score.student_id = student.sid group by course_id having count(1) = 1 24、查询男生、女生的人数; select * from (select count(1) as man from student where gender='男') as A , (select count(1) as feman from student where gender='女') as B 25、查询姓“张”的学生名单; select sname from student where sname like '张%'; 26、查询同名同姓学生名单,并统计同名人数; select sname,count(1) as count from student group by sname; 27、查询每门课程的平均成绩,结果按平均成绩升序排列,平均成绩相同时,按课程号降序排列; select course_id,avg(if(isnull(num), 0 ,num)) as avg from score group by course_id order by avg asc,course_id desc; 28、查询平均成绩大于85的所有学生的学号、姓名和平均成绩; select student_id,sname, avg(if(isnull(num), 0 ,num)) from score left join student on score.student_id = student.sid group by student_id; 29、查询课程名称为“数学”,且分数低于60的学生姓名和分数; select student.sname,score.num from score left join course on score.course_id = course.cid left join student on score.student_id = student.sid where score.num < 60 and course.cname = '生物' 30、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名; select * from score where score.student_id = 3 and score.num > 80 31、求选了课程的学生人数 select count(distinct student_id) from score select count(c) from ( select count(student_id) as c from score group by student_id) as A 32、查询选修“杨艳”老师所授课程的学生中,成绩最高的学生姓名及其成绩; select sname,num from score left join student on score.student_id = student.sid where score.course_id in (select course.cid from course left join teacher on course.teacher_id = teacher.tid where tname='张磊老师') order by num desc limit 1; 33、查询各个课程及相应的选修人数; select course.cname,count(1) from score left join course on score.course_id = course.cid group by course_id; 34、查询不同课程但成绩相同的学生的学号、课程号、学生成绩; select DISTINCT s1.course_id,s2.course_id,s1.num,s2.num from score as s1, score as s2 where s1.num = s2.num and s1.course_id != s2.course_id; 35、查询每门课程成绩最好的前两名; select score.sid,score.course_id,score.num,T.first_num,T.second_num from score left join ( select sid, (select num from score as s2 where s2.course_id = s1.course_id order by num desc limit 0,1) as first_num, (select num from score as s2 where s2.course_id = s1.course_id order by num desc limit 1,1) as second_num from score as s1 ) as T on score.sid =T.sid where score.num <= T.first_num and score.num >= T.second_num 36、检索至少选修两门课程的学生学号; select student_id from score group by student_id having count(student_id) > 1 37、查询全部学生都选修的课程的课程号和课程名; select course_id,count(1) from score group by course_id having count(1) = (select count(1) from student); 38、查询没学过“叶平”老师讲授的任一门课程的学生姓名; select student_id,student.sname from score left join student on score.student_id = student.sid where score.course_id not in ( select cid from course left join teacher on course.teacher_id = teacher.tid where tname = '张磊老师' ) group by student_id 39、查询两门以上不及格课程的同学的学号及其平均成绩; select student_id,count(1) from score where num < 60 group by student_id having count(1) > 2 40、检索“004”课程分数小于60,按分数降序排列的同学学号; select student_id from score where num< 60 and course_id = 4 order by num desc; 41、删除“002”同学的“001”课程的成绩; delete from score where course_id = 1 and student_id = 2
5.表内容进阶操作
视图
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
视图是一个虚拟表(非真实存在),其本质是【根据SQL语句获取动态的数据集,并为其命名】,用户使用时只需使用【名称】即可获取结果集,并可以将其当作表来使用。 1、创建视图 --格式:CREATE VIEW 视图名称 AS SQL语句 CREATE VIEW v1 AS SELET nid, name FROM A WHERE nid > 4 2、删除视图 --格式:DROP VIEW 视图名称 DROP VIEW v1 3、修改视图 -- 格式:ALTER VIEW 视图名称 AS SQL语句 ALTER VIEW v1 AS SELET A.nid, B. NAME FROM A LEFT JOIN B ON A.id = B.nid LEFT JOIN C ON A.id = C.nid WHERE A.id > 2 AND C.nid < 5 4、使用视图 使用视图时,将其当作表进行操作即可,由于视图是虚拟表,所以无法使用其对真实表进行创建、更新和删除操作,仅能做查询用。 show tables;同时查看到列表和视图
触发器
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
触发器:对某个表进行【增/删/改】操作的前后如果希望触发某个特定的行为时,可以使用触发器,触发器用于定制用户对表的行进行【增/删/改】前后的行为。 1、创建基本语法 # 插入前 CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW BEGIN ... END # 插入后 CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW BEGIN ... END # 删除前 CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROW BEGIN ... END # 删除后 ... # 更新前 CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROW BEGIN ... END # 更新后 ... 示例: 插入前触发器 delimiter // --修改终止符为\\ CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW BEGIN IF NEW. NAME == 'alex' THEN INSERT INTO tb2 (NAME) VALUES ('aa') END END// delimiter ; --把终止符复原为; 插入前触发器 delimiter // CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW BEGIN IF NEW. num = 666 THEN INSERT INTO tb2 (NAME) VALUES ('666'), ('666') ; ELSEIF NEW. num = 555 THEN INSERT INTO tb2 (NAME) VALUES ('555'), ('555') ; END IF; END// delimiter ; 特别的:NEW表示即将插入的数据行,OLD表示即将删除的数据行。 2、删除触发器 DROP TRIGGER tri_after_insert_tb1; 3、使用触发器 触发器无法由用户直接调用,而是由于对表的【增/删/改】操作被动引发的。
存储过程
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
存储过程是一个SQL语句集合,当主动去调用存储过程时,其中内部的SQL语句会按照逻辑执行。 1、创建存储过程 -- 创建存储过程(无参数存储过程) delimiter // create procedure p1() BEGIN select * from t1; END// delimiter ; -- 执行存储过程 call p1() 对于存储过程,可以接收参数,其参数有三类: in 仅用于传入参数用 out 仅用于返回值用 inout 既可以传入又可以当作返回值 -- 创建存储过程(有参数存储过程) delimiter \\ create procedure p1( in i1 int, in i2 int, inout i3 int, out r1 int ) BEGIN DECLARE temp1 int; DECLARE temp2 int default 0; set temp1 = 1; set r1 = i1 + i2 + temp1 + temp2; set i3 = i3 + 100; end\\ delimiter ; -- 执行存储过程 set @t1 =4; #mysql的变量默认在前面要加@ set @t2 = 0; CALL p1 (1, 2 ,@t1, @t2); SELECT @t1,@t2; 以下有更多示例 http://www.cnblogs.com/wupeiqi/articles/5713323.html 2、删除存储过程 drop procedure proc_name; 3、执行存储过程 -- 无参数 call proc_name() -- 有参数,全in call proc_name(1,2) -- 有参数,有in,out,inout set @t1=0; set @t2=3; call proc_name(1,2,@t1,@t2) pymysql执行存储过程 #!/usr/bin/env python # -*- coding:utf-8 -*- import pymysql conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1') cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # 执行存储过程 cursor.callproc('p1', args=(1, 22, 3, 4)) # 获取执行完存储的参数 cursor.execute("select @_p1_0,@_p1_1,@_p1_2,@_p1_3") result = cursor.fetchall() conn.commit() cursor.close() conn.close() print(result)
内置函数、自定义函数
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
MySQL中提供了许多内置函数,例如: 详情见 http://www.cnblogs.com/wupeiqi/articles/5713323.html 1、自定义函数 delimiter \\ create function f1( i1 int, i2 int) returns int BEGIN declare num int; set num = i1 + i2; return(num); END \\ delimiter ; 2、删除函数 drop function func_name; 3、执行函数 # 获取返回值 declare @i VARCHAR(32); select UPPER('alex') into @i; SELECT @i; # 在查询中使用 select f1(11,nid) ,name from tb2;
事务
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
事务用于将某些操作的多个SQL作为原子性操作,一旦有某一个出现错误,即可回滚到原来的状态,从而保证数据库数据完整性。 支持事务的存储过程 delimiter \\ create PROCEDURE p1( OUT p_return_code tinyint ) BEGIN DECLARE exit handler for sqlexception BEGIN -- ERROR set p_return_code = 1; rollback; END; DECLARE exit handler for sqlwarning BEGIN -- WARNING set p_return_code = 2; rollback; END; START TRANSACTION; DELETE from tb1; insert into tb2(name)values('seven'); COMMIT; -- SUCCESS set p_return_code = 0; END\\ delimiter ; 使用该存储过程 set @i =0; call p1(@i); select @i;
索引
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
索引,是数据库中专门用于帮助用户快速查询数据的一种数据结构。类似于字典中的目录,查找字典内容时可以根据目录查找到数据的存放位置,然后直接获取即可。 普通索引(index):仅加速查询 唯一索引(unique):加速查询 + 列值唯一(可以有null) 主键索引(primary):加速查询 + 列值唯一 + 表中只有一个(不可以有null) 组合索引(index):多列值组成一个索引, 专门用于组合搜索,其效率大于索引合并 全文索引:对文本的内容进行分词,进行搜索
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
创建时,至少都有2种方法:1.在创建表时,直接注明索引列,索引类型。2.配置已经存在的表的列为索引,指定索引类型 建立索引 方法1 create table in1( nid int not null auto_increment primary key,#指定为主键索引,方式1 name varchar(32) not null, email varchar(64) not null, extra text, index ix_name (name) #指定name列为普通索引 unique ix_name (sid) #指定sid列为唯一索引 primary key(nid) #指定为主键索引,方式2(注意,一张表只能有一个主键,这里只是为了说明创建主键的方式而已) ) 方法2 create index index_name on table_name(column_name) #指定name列为普通索引 create unique index 索引名 on 表名(列名) #指定sid列为唯一索引 alter table 表名 add primary key(列名); #创建主键索引(和上面的表有区别,也就是说明主键索引其实并没有再其他表上配置数据?) 删除索引 drop index_name on table_name; #删除普通索引 drop unique index 索引名 on 表名 #删除唯一索引 alter table 表名 drop primary key; #删除主键索引,方式1 alter table 表名 modify 列名 int, drop primary key; #删除主键索引,方式2 查看索引 show index from table_name; 注意:对于创建索引时如果是BLOB 和 TEXT 类型,必须指定length。 create index ix_extra on in1(extra(32)); 组合索引 create table in3( nid int not null auto_increment primary key, name varchar(32) not null, email varchar(64) not null, extra text ) create index ix_name_email on in3(name,email);#创建组合索引 如上创建组合索引之后,查询: name and email -- 使用索引 name -- 使用索引 email -- 不使用索引 注意:对于同时搜索n个条件时,组合索引的性能好于多个单一索引合并。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
- 查看表结构 desc 表名 - 查看生成表的SQL show create table 表名 - 查看索引 show index from 表名 - 查看执行时间 set profiling = 1; SQL... show profiles;
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
有很多条件都会导致索引无效,要注意。建议还是另外找资料看看 注意:类型不一致可能会导致索引失效:如果查找列是字符串类型,传入条件是必须用引号引起来 当根据索引排序时候,选择的映射如果不是索引,则不走索引 - 避免使用select * - count(1)或count(列) 代替 count(*) - 创建表时尽量时 char 代替 varchar - 表的字段顺序固定长度的字段优先 - 组合索引代替多个单列索引(经常使用多个条件查询时) - 尽量使用短索引 - 使用连接(JOIN)来代替子查询(Sub-Queries) - 连表时注意条件类型需一致 - 索引散列值(重复少)不适合建索引,例:性别不适合
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
无论是否有索引,limit分页是一个值得关注的问题 每页显示10条: 当前 118 120, 125 倒序: 大 小 970 7 6 6 5 54 43 32 19 98 下一页: select * from tb1 where nid < (select nid from (select nid from tb1 where nid < 当前页最小值 order by nid desc limit 每页数据 *【页码-当前页】) A order by A.nid asc limit 1) order by nid desc limit 10; select * from tb1 where nid < (select nid from (select nid from tb1 where nid < 970 order by nid desc limit 40) A order by A.nid asc limit 1) order by nid desc limit 10; 上一页: select * from tb1 where nid < (select nid from (select nid from tb1 where nid > 当前页最大值 order by nid asc limit 每页数据 *【当前页-页码】) A order by A.nid asc limit 1) order by nid desc limit 10; select * from tb1 where nid < (select nid from (select nid from tb1 where nid > 980 order by nid asc limit 20) A order by A.nid desc limit 1) order by nid desc limit 10;
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
explain + 查询SQL - 用于显示SQL执行信息参数,根据参考信息可以进行SQL优化 查询顺序标识 如:mysql> explain select * from (select nid,name from tb1 where nid < 10) as B; +----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+ | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 9 | NULL | | 2 | DERIVED | tb1 | range | PRIMARY | PRIMARY | 8 | NULL | 9 | Using where | +----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+ 特别的:如果使用union连接气值可能为null select_type 查询类型 SIMPLE 简单查询 PRIMARY 最外层查询 SUBQUERY 映射为子查询 DERIVED 子查询 UNION 联合 UNION RESULT 使用联合的结果 ... table 正在访问的表名 type 查询时的访问方式,性能:all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const ALL 全表扫描,对于数据表从头到尾找一遍 select * from tb1; 特别的:如果有limit限制,则找到之后就不在继续向下扫描 select * from tb1 where email = 'seven@live.com' select * from tb1 where email = 'seven@live.com' limit 1; 虽然上述两个语句都会进行全表扫描,第二句使用了limit,则找到一个后就不再继续扫描。 INDEX 全索引扫描,对索引从头到尾找一遍 select nid from tb1; RANGE 对索引列进行范围查找 select * from tb1 where name < 'alex'; PS: between and in > >= < <= 操作 注意:!= 和 > 符号 INDEX_MERGE 合并索引,使用多个单列索引搜索 select * from tb1 where name = 'alex' or nid in (11,22,33); REF 根据索引查找一个或多个值 select * from tb1 where name = 'seven'; EQ_REF 连接时使用primary key 或 unique类型 select tb2.nid,tb1.name from tb2 left join tb1 on tb2.nid = tb1.nid; CONST 常量 表最多有一个匹配行,因为仅有一行,在这行的列值可被优化器剩余部分认为是常数,const表很快,因为它们只读取一次。 select nid from tb1 where nid = 2 ; SYSTEM 系统 表仅有一行(=系统表)。这是const联接类型的一个特例。 select * from (select nid from tb1 where nid = 1) as A; possible_keys 可能使用的索引 key 真实使用的 key_len MySQL中使用索引字节长度 rows mysql估计为了找到所需的行而要读取的行数 ------ 只是预估值 extra 该列包含MySQL解决查询的详细信息 “Using index” 此值表示mysql将使用覆盖索引,以避免访问表。不要把覆盖索引和index访问类型弄混了。 “Using where” 这意味着mysql服务器将在存储引擎检索行后再进行过滤,许多where条件里涉及索引中的列,当(并且如果)它读取索引时,就能被存储引擎检验,因此不是所有带where子句的查询都会显示“Using where”。有时“Using where”的出现就是一个暗示:查询可受益于不同的索引。 “Using temporary” 这意味着mysql在对查询结果排序时会使用一个临时表。 “Using filesort” 这意味着mysql会对结果使用一个外部索引排序,而不是按索引次序从表里读取行。mysql有两种文件排序算法,这两种排序方式都可以在内存或者磁盘上完成,explain不会告诉你mysql将使用哪一种文件排序,也不会告诉你排序会在内存里还是磁盘上完成。 “Range checked for each record(index map: N)” 这个意味着没有好用的索引,新的索引将在联接的每一行上重新估算,N是显示在possible_keys列中索引的位图,并且是冗余的。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
a、配置MySQL自动记录慢日志 slow_query_log = OFF 是否开启慢日志记录 long_query_time = 2 时间限制,超过此时间,则记录 slow_query_log_file = /usr/slow.log 日志文件 log_queries_not_using_indexes = OFF 为使用索引的搜索是否记录 b、查看MySQL慢日志 mysqldumpslow -s at -a /usr/local/var/mysql/MacBook-Pro-3-slow.log """ --verbose 版本 --debug 调试 --help 帮助 -v 版本 -d 调试模式 -s ORDER 排序方式 what to sort by (al, at, ar, c, l, r, t), 'at' is default al: average lock time ar: average rows sent at: average query time c: count l: lock time r: rows sent t: query time -r 反转顺序,默认文件倒序拍。reverse the sort order (largest last instead of first) -t NUM 显示前N条just show the top n queries -a 不要将SQL中数字转换成N,字符串转换成S。don't abstract all numbers to N and strings to 'S' -n NUM abstract numbers with at least n digits within names -g PATTERN 正则匹配;grep: only consider stmts that include this string -h HOSTNAME mysql机器名或者IP;hostname of db server for *-slow.log filename (can be wildcard), default is '*', i.e. match all -i NAME name of server instance (if using mysql.server startup script) -l 总时间中不减去锁定时间;don't subtract lock time from total time """
6.其他
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 ;
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 ;
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
delimiter \\ CREATE PROCEDURE proc_repeat () BEGIN DECLARE i INT ; SET i = 0 ; repeat select i; set i = i + 1; until i >= 5 end repeat; END\\ delimiter ;
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 loop_label; END
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
delimiter \\ DROP PROCEDURE IF EXISTS proc_sql \\ CREATE PROCEDURE proc_sql () BEGIN declare p1 int; set p1 = 11; set @p1 = p1; PREPARE prod FROM 'select * from tb2 where nid > ?'; EXECUTE prod USING @p1; DEALLOCATE prepare prod; END\\ delimiter ;
参考:
基础1: http://www.cnblogs.com/wupeiqi/articles/5713315.html
基础2: http://www.cnblogs.com/wupeiqi/articles/5713323.html
练习题:http://www.cnblogs.com/wupeiqi/articles/5729934.html
练习题答案: http://www.cnblogs.com/wupeiqi/articles/5748496.html
索引知识补充:http://www.cnblogs.com/wupeiqi/articles/5716963.html