python笔记(MySQL）

详细了解

一、MySQL
1、是用于管理文件的一个软件
服务端软件：socket服务端，本地文件操作，解析指令
客户端软件（各种各样）：socket客户端，发送命令，解析指令【SQL语句】
PS:DBMS数据库管理系统
2、技能：安装服务端和客户端，连接，学习SQL语句规则，指示服务端做任何操作
3、其他类似软件：
（1）、关系型数据库： aqllite,db2,oracle,access,sql,server,MYSQL
（2）、非关系型数据库：MongoDB，redis
3、安装：

1. 初始化服务端 … --initialize-insecure（默认用户root,无密码）
2. 启动服务端 … mysqld
3. 客户端连接： … -u root -p
4. 发送指令：show databases (查看目录)
5. create database db1(创建一个名为 db1的文件夹)

4、环境变量的配置：
windows服务：

1）. … --install
2）. … --remove
3）. net start MySQL
4）. net stop MySQ

二、关于连接
文件夹【数据库】
---------文件【表】
-------------- 数据行【行】
1、连接：
-------默认：用户root
-------创建用户：
1）----------create user 'xiaolonglong'@'192.168.1.1' identified by '123123';(用户名，IP号，密码)
2） ---------create user 'xiaolonglong'@'192.168.1.%' identified by '123123';(用户名，IP号，密码)
3） ---------create user 'xiaolonglong'@'192.168.%' identified by '123123';(用户名，IP号，密码)
2、 授权：
------权限 ： 人
1）-----------grant select,insert,update on db1.t1 to '%';(查，插入，更新的权限)
2）-----------grant all privileges on db1.t1 to 'alex'@'%';(给所有的权限)

三、查看数据库
1、show databases;(查看数据库)
2、use db1;(进入db1)
3、show tables;(查看文件)
4、select * from user;(查看文件的哪一列)

四、学习SQL语句规则
1、操作文件夹
1）-------create database db2;(创建文件夹db2)
2）-------create database db2 default charset utf8;(创建文件夹db2,规定其编码方式) <-----
3）-------show databases;(查看文件夹列表)
4）-------drop database db2;(删除db2文件夹)
2、操作文件：
1、show tables(查看文件)
2、create table t1(id int,name char(10));（创建一个名为t1的表，第一列为id为（数字）类型，第二列为name为（字符串）类型而且不能超过十个字符）
3、create table t1(id int,name char(10)) default charset=utf8;(…有编码格式)
4、create table t1(id int,name char(10)) engine=innodb default charset=utf8;(innodb:支持事务（原子性操作），还有myisam等等引擎）
5、

create table t1(   
                 列名 类型 null,:可以为空
                 列名 类型 not null,:不能为空
                 列名 类型 auto_increment primary key,

（auto_increment:表示自增 primary key:约束，不能重复且不能为空，也有加速的作用）
6、一般形式：

  create table t1(id int not null auto_increment primary key,name char(10))engine=innodb default charset=utf8;

7、清空
1）----------delete from t1;清空t1(原来自增的ID还是接着上一次的)
2）----------truncate table t1;清空t1(原来自增的ID重新从1开始)
8、 删除： drop table t1; 删除t1
9、操作文件里的内容
1）---------insert into t1(id,name) values(2,'liyouxiu');（向表里插入数据）
2）---------select * from t1;(查看表t1里的内容)
3）---------delete from t1 where id<6;(删除后面条件的内容)
4）---------update t1 set age=18;（将age那一列全修改为18）
5）---------update t1 set age=18 where age=17;（将age那一列为17的全修改为18）

8、外键：

create table userinfo(
                uid bigint auto_increment primary key,
                name varchar(32),
                department_id int constraint 
                fk_user_depar foreign key(department_id) references department(id)
                )engine=innodb default charset=utf8;

9、基本数据类型
1）-----------数字：bit,tinyint,int,bigint,float,double,decimal(a,b):<a:前面整数部分的长度，b:后面小数部分的长度>
2）-----------字符串：char(a),vachar(b)<若没占满a,会在后边补0，若没占满b，不会补0>，text,mediumtext
--------------------------PS:把定常的放前面，变长的放后边；上传文件：文件存硬盘，数据库放路径。
3）-----------时间：date:年月日,time:时分秒,year:年,datetime:年月日，时分秒,timestamp:年某时
4）-----------枚举：enum('a','b',......)只能拿其中一个
5）-----------集合：set('a','b','c'.......)可以拿任意组合
10、其他操作
1）show create table t1 查看你是怎么创建t1表的
2）desc t1 查看表t1的信息
3）alter table t1 set auto_increment=3; 下次从3开始
MYSQL:
----------自增步长：基于会话级别
------------------set session auto_increment_increment=3;设置步长为3
------------------show session variables like 'auto_inc%'查看全局变量
-------------------------基于全局级别
------------------------------set gloab auto_increment_increment=3;设置步长为3
------------------------------show gloab variables like 'auto_inc%'查看全局变量

SqlServer:
----------自增步长：基础表级别

10、唯一索引(约束不能重复，加速查找，主键也不能重复)

create table t1(
               id int ....,
               num int,
               xx  int,
               unique uq1 (num,xx)  #联合唯一
)

11、外键的变种
1）------------ 一对一
2）------------多对多
3）------------一对多

一张表只能有一个主键，一个主键不一定是一列可由多列组成

12、SQL语句数据行操作补充
增

   insert into t1(name,age) values('li',12),('root',13) #一次可插入多条数据
   insert into t1(name,age) select name,age from t2;    #将t2中的name,age插入到t1中

删

   delete from t1;
   delete from t1 where id=2 and name='li';可有多个条件（!=和<>都是不等于）
   truncate table t1;

改

update t1 set name='youxiu' where id=12 and name='li';可有多个条件

查

   select * from t1 where id > 10 and name = 'li';可加条件
   select name as cname from t1;将name起个别名为cname
   select name，age,1 from t1;看的时候可新增一列全为1
   select * from t1 where id in (1,4,9);查看id在(1,4,9)中的数据
   select * from t1 where id between 5 and 12;查看id在5~12间的数据（包含两边）
   select * from t1 where id in (select id from t2)查看id在t2中的数据
   select * from t1 where name like "a%";查看name中以a为开头的数据，%a%(含有a),%a(以a结尾)
   select * from t1 where name like "a_";查看name中以a为开头后边匹配一个字符数据
   select * from t1 limit 5;查看前5条
   select * from t1 limit 5,10;从多少条开始取几条
   select * from t1 order by id desc;从大到小排查看
   select * from t1 order by id asc; 从小到大排查看
   select * from t1 order by id1 asc,id2 desc; 从小到大排查看
   select * from t1 order by id asc limit 10; 取后10条从小到大排查看

   分组：
         select part_id,max(id) from t1 group by part_id;将part_id相等的，留下id数最大的那个
         select part_id,min(id) from t1 group by part_id;将part_id相等的，留下id数最小的那个
         select part_id,count(id) from t1 group by part_id;记下part_id相等的数
         select part_id,avg(id) from t1 group by part_id;part_id相等对应id的平均值
         select part_id,sum(id) from t1 group by part_id;part_id相等对应id的和
         如果对于聚合函数结果进行二次筛选时必须使用having
            select part_id,count(id) from t1 group by part_id having count(id) > 1;#筛选出计数大于1的
   连表操作：（重点）
         select * from t1,t2 where t1.id=t2.cid;将t1中的id,与t2中的cid联表显示
         select * from t1 left join t2 on t1.id=t2.cid;与上面一样（推荐使用）PS：左边全显示
         select * from t1 innder join t2 on t1.id=t2.cid;把含NULL那一项隐藏
 select distinct student_id from  score where num<60（distinct去重）
 select course_id,min(number),max(number),
case when min(number)<10 then 0 else min(number) from score  
group by course_id（ 可加case when ... then ... else ... end）
select  if(isnull(score.number),0,score.number)  三目运算：如果score.number为空就让它为0，否则就为它

13、临时表（保存一些临时的表 (select * from tb where id>10) as A 起个别名）
五、总结
1、MySql:文件管理的软件
2、三部分：服务端，SQL语句，客户端
3、客户端：mysql,navicat
4、授权操作：用户操作，授权操作
5、SQL语句：数据库操作(create database xx default charset utf8,drop database xx)，
--------------------数据表(create table xx(id int auto_increment primary key,name varchar(10))engine=innodb default charset=utf8)，
---------------------主键索引，唯一索引，外键
--------------------数据行。
6、pymysql模块：
---------python模块：对数据库进行操作（SQL语句）
六、导出数据库
1、mysqldump -u root db1 > db1.sql -p （将root的db1数据库转储<数据表结构+数据>）
2、mysqldump -u root -d db1 > db1.sql -p （将root的db1数据库转储<数据表结构+数据>）
3、mysqldump -u root -d db2 < db1.sql -p （将db1.sql数据库文件导入db2）
七、练习题

• http://images2015.cnblogs.com/blog/425762/201608/425762-20160803224643778-2071849037.png

• http://www.cnblogs.com/wupeiqi/articles/5729934.html
  1、自行创建测试数据
  
  
  
  
  
  2、查询“自控”课程比“物理”课程成绩高的所有学生的学号；

  select A.student_id from
   (
   select score.sid,score.student_id,course.cname,score.number from score left
   join course on score.course_id=course.cid
   where course.cname='自控'
   ) as A
   inner join
   (
   select score.sid,score.student_id,course.cname,score.number from score left
   join course on score.course_id=course.cid
   where course.cname='物理'
   ) as B
   on A.student_id=B.student_id
   where A.number>B.number;
  

3、查询平均成绩大于60分的同学的学号和平均成绩；

select B.student_id,student.sname,B.avg from
   (
      select student_id,avg(number) as avg from score
      group by student_id
      having avg(number)>60
      ) as B
left join student on B.student_id=student.sid;

4、查询所有同学的学号、姓名、选课数、总成绩；

  select student_id,sname,count(course_id),sum(number) from score
  left join student on score.student_id=student.sid
  group by student_id;

6、查询没学过“李明”老师课的同学的学号、姓名；

select student.sid,student.sname from student where sid not in
    (
    select student_id from score where course_id in
       (
        select course.cid from course
        left join teacher on course.teacher_id=teacher.tid
        where teacher.tname='李明'
        )
    group by student_id
    )

7、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；

select score.student_id,student.sname from score
left join student on student.sid=score.student_id
where course_id=1 or course_id=2
group by student_id having count(course_id)>1

8、查询学过“叶平”老师所教的所有课的同学的学号、姓名；

select student_id from score where course_id in
    (
    select course.cid from course
    left join teacher on course.teacher_id=teacher.tid
    where teacher.tname='李明'
    )
    group by student_id
    having count(course_id) =
    (
    select count(cid) from course
    left join teacher on course.teacher_id=teacher.tid
    where teacher.tname='李明'
    group by teacher_id
    )

11、查询没有学全所有课的同学的学号、姓名；

select student_id,student.sname,count(1) from score
left join student on score.student_id=student.sid
group by student_id
having count(1) < (select count(cid) from course)

12、查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名；

select score.student_id,student.sname 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 score.student_id

16、向SC表中插入一些记录，这些记录要求符合以下条件：①没有上过编号“002”课程的同学学号；②插入“002”号课程的平均成绩

insert into tb
select student_id,2,(select avg(num) from score where course_id=2) from score
where course_id !=2

17、按平均成绩从低到高显示所有学生的“自控”、“高数”、“物理”三门的课程成绩，按如下形式显示： 学生ID,语文,数学,英语,有效课程数,有效平均分

select
  student_id,
 (select number from score as s2 where s2.student_id=s1.student_id and course_id=1) as 自控 ,
 (select number from score as s2 where s2.student_id=s1.student_id and course_id=2) as 物理 ,
 (select number from score as s2 where s2.student_id=s1.student_id and course_id=3) as 高数,
 avg(s1.number)
 from score as s1 group by student_id

18、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；

select course_id,min(number),max(number),
       case when min(number)<70 then 60 else min(number) end from score 
       group by course_id;

19、按各科平均成绩从低到高和及格率的百分数从高到低顺序；

select course_id,avg(number),
sum(case when number<60 then 0 else 1 end)/sum(1) as pass from score 
group by course_id
order by avg(number) asc,pass desc;

20、课程平均分从高到低显示（现实任课老师）；

(1)、
    select score.course_id,avg(number),teacher.tname from score 
    left join course on score.course_id=course.cid 
    left join teacher on course.teacher_id=teacher.tid
    group by score.course_id
    order by avg(number) asc
(2)、
    select score.course_id,avg(if(isnull(score.number),0,score.number)),teacher.tname from score 
    left join course on score.course_id=course.cid 
    left join teacher on course.teacher_id=teacher.tid
    group by score.course_id
    order by avg(number) asc

21、查询各科成绩前三名的记录:(不考虑成绩并列情况)

  select * from
      (
      select 
        student_id,
        number,
        (
        select number from score as s2 
        where s2.course_id=s1.course_id 
        group by number 
        order by number desc 
        limit 0,1
        ) as aa,
        (
        select number from score as s2 
        where s2.course_id=s1.course_id 
        group by number 
        order by number desc 
        limit 3,1
        ) as cc
      from score as s1
      ) as B
  where B.number>B.cc;      

22、查询每门课程被选修的学生数

select course_id,count(student_id) from score group by course_id

23、查询出只选修了一门课程的全部学生的学号和姓名；

select student.sid,student.sname,count(1) from score 
right join student on score.student_id=student.sid
group by course_id

34、查询不同课程但成绩相同的学生的学号、课程号、学生成绩

select student_id from score as s1,score s2
where s1.sid!=s2.sid and s1.course_id!=s2.course_id and s1.number=s2.number;