MySQL(01)--基础练习

设计学生表和班级表

create table classes (
    id smallint unsigned not null auto_increment comment '主键ID',
    name char(20) not null comment '班级名称',
    
)

comment 表示字段的注释，方便后面加入项目开发的人员快速了解每一个字段的意思。

消除重复行

• 在select后面列前使用distinct可以消除重复的行
• distinct的使用需要放在第一个字段的位置，针对第一个字段进行去重。

select distinct 列1,... from 表名;
例：
select distinct gender from students;

例如，统计下在学生表的所有的学生班级

select distinct class from student;

where条件的运算符进阶

空判断

• 注意：null与''是不同的
• 判空is null

例1：查询没有填写个性签名的学生

select * from student where description is null;

• 判非空is not null

例2：查询填写了个性签名的学生

select * from student where description is not null;

例3：查询填写了身高的男生

select * from student where description is not null and sex=1;

运算符优先级

• 优先级由高到低的顺序为：小括号，not，比较运算符，逻辑运算符
• and比or先运算，如果同时出现并希望先算or，需要结合()使用

连接查询[连表查询、多表查询]

当查询结果的列来源于多张表时，需要将多张表连接成一个大的数据集，再选择合适的列返回

mysql支持三种类型的连接查询，分别为：

内连接查询(inner join)

查询的结果为两个表匹配到的数据

使用内连接，必须保证两个表都会对应id的数据才会被查询出来。

select 字段1,字段2... from 主表 inner join 从表 on 主表.主键=从表.外键

例如：查询学生的信息[ 成绩、名字、班级 ]

我们给学生表添加一个学生信息，然后使用该学生的主键id来连表查询成绩、名字和班级。

insert into student (name,sex,age,class,description) values ('刘德华',1,17,406,'');

select achievement,name,class 
from student as a 
inner join achievement as b 
on a.id=b.sid
where id=101;

# 上面语句因位该学生只在学生表student中有数据，而成绩表中没有数据，所以使用内连接，连表查询的结果是
Empty set (0.00 sec)

同样，如果从表有数据，而主表没有数据，则使用内连接查询一样无法查询到结果。

#例如，添加一个成绩记录，是不存在学生
insert into achievement (sid,cid,achievement) values (102,10,85);
 
 
select achievement,name,class 
from student as a 
inner join achievement as b 
on a.id=b.sid
where id=102;

右连接查询(right join)

只要从表有数据，不管主表是否有数据，都会查询到结果。[以从表的结果为主]

查询的结果为两个表匹配到的数据，右表特有的数据，对于左表中不存在的数据使用null填充

select 字段1,字段2... from 主表 right join 从表 on 主表.主键=从表.外键

例如，上面的成绩id为102的学生， 我们使用右连接查询。

select achievement,name,class 
from student as a 
right join achievement as b 
on a.id=b.sid;

左连接查询(left join)

只要主表有数据，不管从表是否有数据都会被查询出来。

查询的结果为两个表匹配到的数据，左表特有的数据，对于右表中不存在的数据使用null填充

语法

select * from 表1 left join 表2 on 表1.列 = 表2.列

例如，使用左连接查询学生表与成绩表，查询学生姓名及分数

select achievement,name,class 
from student as a 
left join achievement as b 
on a.id=b.sid;

等同于
select achievement,name,class 
from achievement as b 
right join student as a 
on a.id=b.sid;

总结：

三种连表查询，最常用的是 left join，然后inner join保证数据的一致性。右连接基本上都是使用左连接代替。

多表关联

select 表.字段1,表.字段2,表.字段3..... 
from 主表
left join 从表1 on 主表.主键=从表1.外键 
left join 从表2 on 主表.主键=从表2.外键  
     # 这里和从表2连接的on条件看实际情况，也会出现从表1.主键=从表2.外键的情况
left join 从表3 on 主表.主键=从表3.外键
     # 这里可以是(从表1或从表2).主键=从表2.外键的情况
left join ...

多表查询的缺点

多表查询的效率，性能比单表要差。

多表查询以后，还会带来字段多了会引起字段覆盖的情况、

主表student 从表1 achievement 从表2 course

name xxx name

上面三张表如果连表，则出现主表的name覆盖从表2的name这种情况。

上面两个问题：

1. 把多表查询语句可以替换成单表查询语句【需要优化的情况】
2. 把重复的字段名，分别使用as来设置成别的名称。

例如，查询白杨的班级、id、年龄和课程名称以及对应课程的成绩

select a.id,a.class,a.age,c.course,b.achievement 
from student as a
left join achievement as b on a.id=b.sid
left join course as c on c.id=b.cid
where a.name='白杨';

练习：

1. 查询id为20的学生的考试总分.

   ​```sql
   

   select sum(b.achievement) sum # 有时候as可以不写
   from student as a
   left join achievement as b on a.id=b.sid
   where a.id=20;
   ​ ```

2. 查询305班级所有学生的课程名称、课程成绩、以及对应课程的授课老师。

1. 先查305的学生信息
2. 再查305的学生成绩
3. 再查305的学生成绩对应的课程
4. 最后查305的学生成绩对应的课程的老师

select a.name,b.achievement,course,d.name
from student as a
left join achievement as b on a.id=b.sid 
left join course as c on b.cid=c.id 
left join lecturer as d on d.id=c.lecturer_id 
where a.class=305;

上面代码的效果：
+--------+-------------+----------------+--------+
| name   | achievement | course         | name   |
+--------+-------------+----------------+--------+
| 谭季同 |       100.0 | Photoshop      | 唐老师 |
| 谭季同 |        79.0 | 负载均衡       | 杜老师 |
| 谭季同 |        78.5 | Flask项目      | 白老师 |
| 白瀚文 |        73.0 | go             | 陈老师 |
| 白瀚文 |        65.0 | webpy          | 林老师 |
| 白瀚文 |        86.0 | 数据分析       | 郑老师 |
| 白瀚文 |        60.0 | API接口        | 宋老师 |
| 晁然   |         0.0 | Flask          | 陈老师 |
| 晁然   |        78.0 | Python网络编程 | 江老师 |
| 晁然   |        78.0 | HTML5          | 丘老师 |
| 白素欣 |        81.0 | Django项目     | 易老师 |
| 白素欣 |        90.0 | Python         | 黄老师 |
| 白素欣 |        39.0 | Nginx          | 曹老师 |
| 庄晓敏 |        82.5 | Nginx          | 曹老师 |
| 庄晓敏 |        68.0 | Python         | 黄老师 |
| 庄晓敏 |       100.0 | API接口        | 宋老师 |
+--------+-------------+----------------+--------+

单表的连表查询[自关联查询]

核心就是把一张表看做2张表来操作

# 建表：
create table area(
    id smallint not null auto_increment comment '主键ID',
    name char(30) not null comment '地区名称',
    pid smallint not null default 0 comment '父级地区ID',
    primary key (id)
) engine=innodb charset=utf8;

insert into area (name,pid) values ('广东',0),('深圳',1),('龙岗',2),('福田',2),('宝安',2);

格式：

select 字段1,字段2...
from 主表(当前表) as a
left join 从表(当前表) as b on a.主键=b.外键

查找深圳地区的子地区，SQL代码：

# 主表看成保存深圳的表，
# 从表看成保存深圳子地区的表

select b.id,b.name
from area as a
left join area as b on a.id=b.pid
where a.name='深圳';

子查询

在一个 select 语句中,嵌入了另外一个 select 语句, 那么被嵌入的 select 语句称之为子查询语句

格式：

select 字段 from 表名 where 条件(另一条查询语句)

主查询

主要查询的对象,第一条 select 语句

主查询和子查询的关系

• 子查询是嵌入到主查询中
• 子查询是辅助主查询的,要么充当条件,要么充当数据源
• 子查询是可以独立存在的语句,是一条完整的 select 语句

例如：查询406班上大于平均年龄的学生

使用 子查询：

1. 查询406班学生平均年龄
2. 查询大于平均年龄的学生

查询406班级学生的平均身高

select name,age from student where age > (select avg(age) as avg from student where class=406) and class=406;

having

group by 字段 having 条件；

过滤筛选，主要作用类似于where关键字，用于在SQL语句中进行条件判断，过滤结果的。

但是与where不同的地方在于having只能跟在group by 之后使用。

练习：查询301班级大于班上平均成绩的学生成绩信息(name，平均分，班级)。

# 先求301班的平均成绩
select avg(achievement) as achi from student as a
left join achievement as b on a.id=b.sid 
where class=301;

# 判断301中的每个人平均成绩大于上面的到的平均成绩
select name,avg(achievement) from student as a
left join achievement as b on a.id=b.sid
where class=301 group by name having avg(achievement) > (select avg(achievement) as achi from student as a
left join achievement as b on a.id=b.sid 
where class=301);

select查询语句的完整格式

select distinct 字段1,字段2....
from 表名 as 表别名
left join 从表1 on 表名.主键=从表1.外键
left join ....
where ....
group by ... having ...
order by ...
limit start,count

• 执行顺序为：
  • from 表名[包括连表]
  • where ....
  • group by ...
  • select distinct *
  • having ...
  • order by ...
  • limit start,count
• 实际使用中，只是语句中某些部分的组合，而不是全部

备份

• 运行mysqldump命令

mysqldump –uroot –p 数据库名 > python.sql;

# 按提示输入mysql的密码

恢复

• 连接mysql，创建新的数据库
• 退出连接，执行如下命令

mysql -uroot –p 新数据库名 < python.sql

# 根据提示输入mysql密码

我们之前学习的source也是一种恢复方式，但是两种使用有一个区别。就是

mysql 命令这种方式，可以远程 恢复，而source这种只能本地电脑恢复。

mysql -hIP地址 -uroot -p密码

Python操作mysql

pymysql 一般使用这个

MySQLDB

安装pymysql模块

pip install pymysql

使用pymysql模块操作数据库

import pymysql

# from pymysql import *

# 创建和数据库服务器的连接　　connection　
conn = pymysql.connect(host='localhost',port=3306,user='root',password='root123456',
                db='student',charset='utf8')

# 创建游标对象
cursor = conn.cursor()

# 中间可以使用游标完成对数据库的操作
sql = "select * from student;"

# 执行ｓｑｌ语句的函数　　返回值是该ＳＱＬ语句影响的行数
count = cursor.execute(sql)
print("操作影响的行数%d" % count)
# print(cursor.fetchone())   # 返回值类型是元祖，表示一条记录

# 获取本次操作的所有数据
for line in cursor.fetchall():
    print("数据是%s" % str(line))

# 关闭资源　先关游标
cursor.close()
# 再关连接
conn.close()

执行语句

#执行sql，更新单条数据，并返回受影响行数
result = cursor.execute("SQL语句")

#插入多条，并返回受影响的函数，例如批量添加
result2 = cursor.executemany("多条数据")
#获取最新自增ID
new_id = cursor.lastrowid

获取结果

#获取一行
result1 = cursor.fetchone()
#获取多行[参数可以设置指定返回数量]
result2 = cursor.fetchmany(整型)
#获取所有
result3 = cursor.fetchall()

操作数据

#提交，保存新建或修改的数据，如果是查询则不需要
conn.commit() # 写在execute()之后

练习：在python中操作pymysql，获取 301班所有女生的信息[id,name,sex,age]

转载于:https://www.cnblogs.com/pankypan/p/11209666.html