MySQL

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操作来回滚事物

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.连接客户端

客户端连接（启动客户端）
    将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.数据库操作

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.数据表操作

是否可空，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.表内容基本操作

增
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示例

导出现有数据库数据：
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

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

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.表内容进阶操作

视图

视图是一个虚拟表（非真实存在），其本质是【根据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;同时查看到列表和视图

触发器

触发器:对某个表进行【增/删/改】操作的前后如果希望触发某个特定的行为时，可以使用触发器，触发器用于定制用户对表的行进行【增/删/改】前后的行为。
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、使用触发器
触发器无法由用户直接调用，而是由于对表的【增/删/改】操作被动引发的。

 存储过程

存储过程是一个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)

 内置函数、自定义函数

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;

 事务

事务用于将某些操作的多个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;

 索引

索引，是数据库中专门用于帮助用户快速查询数据的一种数据结构。类似于字典中的目录，查找字典内容时可以根据目录查找到数据的存放位置，然后直接获取即可。

普通索引（index）：仅加速查询
唯一索引（unique）：加速查询 + 列值唯一（可以有null）
主键索引（primary）：加速查询 + 列值唯一 +　表中只有一个（不可以有null）
组合索引（index）：多列值组成一个索引，
              专门用于组合搜索，其效率大于索引合并
全文索引：对文本的内容进行分词，进行搜索 

创建时，至少都有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个条件时，组合索引的性能好于多个单一索引合并。

- 查看表结构
    desc 表名
- 查看生成表的SQL
    show create table 表名
- 查看索引
    show index from  表名
- 查看执行时间
    set profiling = 1;
    SQL...
    show profiles;

有很多条件都会导致索引无效，要注意。建议还是另外找资料看看
注意：类型不一致可能会导致索引失效：如果查找列是字符串类型，传入条件是必须用引号引起来
当根据索引排序时候，选择的映射如果不是索引，则不走索引
- 避免使用select *
- count(1)或count(列) 代替 count(*)
- 创建表时尽量时 char 代替 varchar
- 表的字段顺序固定长度的字段优先
- 组合索引代替多个单列索引（经常使用多个条件查询时）
- 尽量使用短索引
- 使用连接（JOIN）来代替子查询(Sub-Queries)
- 连表时注意条件类型需一致
- 索引散列值（重复少）不适合建索引，例：性别不适合

无论是否有索引，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;

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列中索引的位图，并且是冗余的。
 

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.其他

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 ;

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 ;

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 ;

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

 

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