【mySql 语句使用】

一、MySQL基础知识

• E-R模型

1.E：表示实体，R：表示关系
2.一个实体转换为数据库中的一个表
3.关系描述两个实体之间的对应规则，包括一对一、一对多、多对多
4.关心转换为数据库表中的一个列*在关系型数据库中一行就是一个对象

• 三范式

1.经过研究和对使用中问题的总结，对于设计数据库提出来一些规范，这些规范被称为范式
2.第一范式（1NF）：列不可拆分
3.第二范式（2NF）：唯一标识
4.第三范式（3NF）：引用主题
5.说明：后一个范式，都是在前一个范式的基础上建立得

• 字段类型

1.数字：int，decimal
    decimal(5,2)：数字长度为5，包含2位小数
2.字符串：char，varchar，text
    char(8)：存的字符固定的，长度为8，不够会在右边加上空格，超过自动补位
    varchar(16)：存的字符是可变的，长度为16且固定，超过16取前16个
    text：大文本
    编码：utf-8，gb-2312
3.日期：datetime
4.bit：布尔
    bit：默认为一个位
    bit(8)：存放8个二进制的位

• 约束

主键：primary key（不会重复，根据主键查数据很快，物理存储方式）
非空：not null
唯一：unique（值不能重复）
默认：default
外键：foreign key

• 逻辑删除-这点很重要，适用于重要数据

在表里面添加一栏为 ‘isDelete’，0表示未删除，1表示删除，不会从物理层面删除，在需要的时候吧0和1相互

二、数据库基本操作

• 进入数据库

进入数据库：mysql -uroot -p，密码直接回车
进入MySQL后输入：help可以查看更多命令
在MySQL输入：quit或者exit退出
在MySQL目录下输入：mysql --help 可以查看更多的命令
查看版本：select version();      要加分号
显示当前时间：select now();      要加分号

• 远程连接

mysql -h ip地址 -uroot -p
1.-h 后面写要连接的主机ip地址
2.-u 后面写连接的用户名
3.-p 回车后写密码

• 远程连接

mysql -h ip地址 -uroot -p
1.-h 后面写要连接的主机ip地址
2.-u 后面写连接的用户名
3.-p 回车后写密码

• 数据库操作

1.创建数据库：create database 数据库名 charset=utf8;   
2.删除数据库：drop database 数据库名;    
3.切换数据库，接下来的操作都是在这个数据库上进行：use 数据库名;        
4.查看当前选择的数据库：select database();        
5.查看当前所有数据库：show databases;

• 表操作

1.查看当前数据库中所有表：show tables;
    
2.创建表，auto_increment表示自动增长：create table 表名(列及类型...);
    create table students(
        id int auto_increment paimary key not null,
        name varchar(10) not null,
        gender bit default 1,
        birthday datetime,
        isDelete bit default 0
    );
    多个列用','隔开

3.修改表：alter table 表名 add|change|drop 列名 类型;
    alter table students add isDelete bit default 0;

4.查看当前表结构：desc students;    
5.删除表：drop table 表名;
6.查看表结构：desc 表名;
7.更改表名称：rename table 原表名 to 新表名;
8.查看表的创建语句：show create table '表名';

三、数据操作

• 查询

select * from 表名;

• 增加-插入的值的结构顺序要和原表一样

1.全列插入：insert into 表名 values(...);
    insert into students values(0,'郭靖','1','1990-1-1',0)

2.缺省插入：insert into 表名(列1，...) values(...);
    a.insert into students(name) values('黄蓉');
    b.insert into students(gender,name) values(0,'小龙女');
    
3.同时插入多条数据：insert into 表名 values(...),(...)...; 
             或 insert into 表名 values(值1,...),(值1,...)...;
    a.insert into students(name) values('杨过'),('雕'),('黄药师');

INSERT INTO account (id, code, pwd, name, nickname, address, city, birthday, age, avatar, sex)
VALUES
    (4, '2776', 'b123456', 'test', '测试人员', '香港', '东区', '2024-11-08', 25, NULL, 1),
    (5, '2777', 'c123456', 'test2', '测试人员2', '香港', '西区', '2024-11-09', 26, NULL, 1);

• 修改

update 表名 set 列1=值1,... where 条件;
实例：update students set birthday='1990-2-2' where id=2;

• 删除

delete from 表名 where 条件;
实例：delete from students where id=5;

• 逻辑删除-本质就是修改操作update

1.alter table students add isDelete bit dafault 0;
2.删除列：update students isdelete=1 where ...;

四、备份与恢复

• 数据备份

1.进入超级管理员：sudo -s
2.进入mysql目录：cd ../mysql
3.运行mysqldump命令：mysqldump -uroot -p 数据库名 > ~/存储位置/backup.sql

数据恢复

1.连接mysql，创建新的数据库
2.退出连接，执行如下命令： mysql -uroot -p 新的数据库名 < ~/存储位置/backup.sql

5、实例

• 1.设计班级表，与学生表关联，并进行查询

create table T_classes(
    id int auto_increment primary key not null,
    name varchar(10)
);

create table T_students(
    id int auto_increment primary key not null,
    name varchar(10),
    classid int,
    FOREIGN key(classid) references T_classes(id)
);

create view V_stu_cls as
select T_students.id, T_students.name, T_classes.name as title
from T_students inner join T_classes on T_classes.id=T_students.classid;

• 2.设计分类表，自关联，并进行查询

create table T_type(
    id int auto_increment primary key not null,
    name varchar(10),
    pid int,
    FOREIGN key(pid) references T_type(id)
);

create view V_type as 
select son.* from T_type as father 
inner join T_type as son on father.id=son.pid;

• 3.创建两个表，并关联两个表，向表中添加数据

create table rooms(
    id int primary key not null,
    title varchar(10)
);

create table stu(
    id int auto_increment primary key not null,
    name varchar(10),
    roomid int
);

alter table stu add constraint stu_room foreign key(roomid) references rooms(id);

insert into stu values(314,'聚义厅');
insert into stu values(0,'郭靖',314);

6、查询

• 查询的基本语法：select * from 表名;

1.from关键字后面写表名，表示数据来源于这张表
2.select后面写表中的列名，*表示在结果中显示表中所有的列
3.在select后面的列名部分，可以使用as为列起别名，这个别买出现在结果集中
4.如果要查询多个列，之间使用逗号分隔

实例：
use python3;    数据库名
show tables;    显示所有的表
select * from students;     查看students表
select id,name from students;   只查看id和name，得到结果集

• 消除重复行

在select后面列前使用distinct可以消除重复的行：
select distinct gender from students;
gender的结果只有1和0，结果只会一个1和一个0

select distinct id,gender from students;
id没有重复值，gender有重复值，以id为准

• 条件：使用where句子对标准化的数据进行帅选，结果为true的行会出现在结果集中
• 条件运算符：>,<,>=,<=,=,!=

select * from 表名 where 条件;
select * from students where id>3;      查询编号大于3的学生
select * from students where name!='黄蓉';    查询姓名不是'黄蓉'的学生
select * from students where idDelete=0;    查询没被删除的学生

• 逻辑运算符：and,or,not

select * from students where id>3 and gender=0;     查询编号大于3的女同学
select * from students whereid<4 or isDelete=0;     查询编号小于4或没被删除的学生

• 模糊查询

1.like
2.%表示任意多个任意字符
3._表示一个任意字符

select * from students where name like '黄_';        查询叫黄x的学生
select * from students where name like '%龙%';       查询名字里有龙的学生
select * from students where name like '黄%';        查询姓黄的学生
select * from students where name like '黄%' or name like '%靖%';     查询姓黄或者叫靖的学生

• 范围查询

1.in表示在一个非连续的范围内
select * from students where id in(1,3,8);      查询编号为1或3或8的学生

2.between...and...表示在一个连续的范围内
select * from students where id between 3 and 8;    查询学生id是3到8的学生
select * from students where id between 3 and 8 and gender=1;   查询id是3到8的男生

• 空判断

注意：null和''是不同的
1.判空：is null
select * from students where hometown is null;   查询没有填地址的学生
select * from students where birthday is null;   查询没有填写生日的学生

2.判非空：is not null
select * from students where hometown is not null;   查询填写地址的学生
select * from students where hometown is not null and gender=0;  查询谢了地址的女生

• 优先级

1.小括号，not，比较运算符，逻辑运算符
2.and比or西安运算，如果同时出现并希望先算or，需要结合()使用

• 聚合：为了快速得到统计数据，提供了5个聚合函数

1.count(*)表示计算总行数，括号中写*与列名，结果是相同的   
select count(*) from students;      查询学生总数 
select count(*) from students where isDelete=0;     查询未被删除的学生数量 

2.max(列)表示求此列的最大值
select max(id) from students where gender=0;    查询女生编号的最大值

3.min(列)表示求此列的最小值
select min(id) from students where isDelete=0;    查询未删除的学生的最小编号

4.sum(列)表示求此列的和
select sum(id) from students where gender=1;    查询男生的编号之和

5.avg(列)表示求此列的平均值
select avg(id) from students where isDelete=0 and gender=0;     查询未删除女生的编号平均值

• 分组

1.按照字段分组，表示此字段相同的数据会被放到一个组中
2.分组后，只能查询出相同的数据列，对于有差异的数据列无法出现在结果集中
3.可以对分组后的数据进行统计，做聚合运算
4.语法：select 列1，列2，聚合... from 表名 group by 列1,列2,列3...;

a.查询男女生总数
select gender as 性别,count(*) from students group by gender;

b.查询各城市人数
select hometown as 家乡,count(*) from students group by hometown;

• 分组后的数据筛选

语法：select 列1,列2,聚合... from 表名 group by 列1,列2,列3... having 列1,..聚合...;
having后面的条件运算符号与where的相同
    
1.查询男生总人数，两种方法操作的集不一样，a是在原始集上操作，b是在结果集上操作
a.select count(*) from students where gender=1;
b.select gender as 性别,count(*) from students group by gender having gender=1;

2.查询男生总人数
select gender,count(*) from students group by gender having gender=0;

3.查询同性别中大于2的人数
select gender,count(*) from students group by gender having count(*)>2;
select gender,count(*) as num from students group by gender having num>2;

where与having
1.where是对from后面指定的表进行数据筛选，属于对原始数据的筛选
2.having是对group by的结果进行筛选

• 排序

语法：select * from 表名 order by 列1 asc|desc,列2 asc|desc,...;
1.讲行数据按照列1进行排序，如果某些行列1的值相同时，则按照列2排序，以此类推
2.默认按照劣质从小到大排序
3.asc从小到大排列，即升序
4.desc从大到小排序，即降序

a.查询未删除男生学生信息，按学号降序
select * from students where gender=1 and isDelete=0 order by id desc;

b.查询未删除科目信息，按名称升序
先添加isDelete：alter table subjects add isDelete bit defaule 0;
再添加被删除的数据：update subjects set isDelete=1 where titlr in('linux','redis');
再进行查询：select * from subject where isDelete=0 order by title;

• 获取部分行：当数据量过大时，在一页中查看数据是意见非常麻烦的事情

语法：select * from 表名 limit start,count;
从start开始，获取count条数据，start索引从0开始
select * from students limit 2,1;   从2开始，查询后面1条数据
select * from students limit 1,3;   从1开始，查询后面3条数据

实例：分页
1.已知：每页显示m条数据，房钱显示第n页
2.求总页数：此段逻辑后面会在python中实现
    a.查询总条数p1
    b.使用p1除以m德奥p2
    c.如果整除则p2为总数页
    d.如果不整除则p2+1为总页数
3.求第n页的数据

select * from students where isDeletd=0 limit(n-1)*m,m;

• 总结

1.完整的查询语句
select distinct *
from 表名
where ...
group by ... having ...
oeder by ...
limit start,count;

2.执行顺序
    a.from 表名
    b.where ...
    c.group by ...
    d.select distinct *
    e.having ...
    f.order by ...
    g.limit start,count

3.实际使用中，知识语句中某些部分的组合，而不是全部语句

7、存储关系

• 简介

a.实体与实体之间有三种关系，这些关系也要存储下来
b.视图用于完成查询语句的封装
c.十五可以保证复杂的的增删改操作有效
d.当数据巨大时，为了提高查询速度可以通过索引实现

• 关系-不允许闭合，关系也是数据

创建成绩表scores，结构如下：
    id，学生，科目，成绩

学生列的数据不是在这里新建的，而应该从学生表引用过来，关系也是一条数据；根据范式要求应该存储学生的
编号，而不是学生的姓名等其他信息。同理科目表也是关系列，引用科目表中的数据。    
    学生表——班级表
    郭靖 Python 100
    students.name
    subjects.title
    scores.score    
实例：
create teble scores(
    id int primary key auto_increment not null,
    stuid int，
    subid int,
    score decimal(4,1),
    foreign key(stuid) references students(id),
    foreign key(subid) references subjects(id)
);
insert into scores values(0,100,1,1);
...  

• 外键的级联操作

a.在删除students表的数据时，如果这个id值在scores中已经存在，则会抛出异常
b.推荐使用逻辑删除，还可以解决这个问题
c.可以创建表时指定级联操作，也可以在创建表后再修改外键的级联操作

语法：alter table scores add constraint stu)sco foreign key(stuid) reference students();

d.级联操作的类型：
    restrict（限制）：默认值，抛异常
    cascade（级联）：如果主表的记录删掉，则从表中相关联的记录都将被删除
    set null：将外键设置为空
    no action：什么都不做

• 级联查询

select students.name,subjects.title,scores.score
from scores
inner join students on scores.stuid=students.id
inner join subjects on scores.subid=subjects.id;

另一种写法：
select students.name,subjects.titlr,scores.score
from students
inner join scores on scores.stuid=students.id
inner join subjects on scores.subid=subjects.id;

• 连接查询：

a.表A inner join 表B：表A与表B匹配的行会出现在结果中
b.表A left join 表B：表A与表B匹配的行会出现在结果中，外加表A中堵头的数据，未对应的数据使用null填充
c.表A right join 表B：表A与表B匹配的行会出现在结果中，外加表B中堵头的数据，未对应的数据使用null填充

e.在查询或条件中推荐使用‘表名’、‘列名’的语法
f.如果多个表中列名不重复可以省略‘表名’部分
g.如果表的名称太长，可以在表名后面使用‘as 简写名’或‘简写名’，为表起个临时的简写名称

总结：
select distinct 列*
from 表1 inner|left|right join 表2 on 表1与表2的关系
where ...
group by ... having ...
order by ... asc|desc
limit start,count;

• 关联查询

1.查询学生的姓名、平均分
select students.name,avg(scores.score)
from scores
inner join students on scores.stuid=students.id
group by students.name;

2.查询男生的姓名、平均分
select students.name,avg(scores.score)
from scores
inner join students on scores.stuid=students.id
where students.gender=1
group by students.name;

3.查询科目的名称、平均分
select subjects.title,avg(scores.score)
from scores
inner join subjects on scores.subid=subjects.id
group by subjects.title;

4.查询男生的姓名、总分
select name,sum(score)
from students
inner join scores on students.id=scores.stuid
where gender=1
group by students.id;

5.查询为身处科目的名称、最高分、平均分
select subjects.title,avg(scores.score),max(scores.score)
from scores
inner join subjects on scores.subid=subjects.id
where subjects.isDelete=0
group by subjects.title;

• 自关联

1.设计省信息的表结构provinces
    id
    title
2.设计市信息的表结构citys
    id
    title
    proid
3.citys表的proid表示城市所属的省，对应着provinces表的id值
4.将两个表合成一个表，可以定义表areas，结构如下:
    id
    title
    pid

5.实例
create table areas(
    id int primary key auto_increment not null,
    title varchar(10),
    pid int,
    foreign key(pid) references areas(id)
)

a.从sql文件中导入数据
source areas.sql

b.查询一共有多少个省
select count(*) from areas ehere pid is null;

c.查询名称为“山西省”的所有城市
select city.* from areas as city
inner join areas as province on city.pid=province.id
where province.title='山西省';

d.查询市的名称为“广州市”的所有区县
select dis.* from areas as dis
inner join areas as city on city.id=dis.id
where city.title='广州市';

e.查询山西省的所有市
select sheng.id as sid,sheng.title as stitle,
shi.id as shiid,shi.title as shititle
from areas as sheng
inner join areas as shi on sheng.id=shi.pid
where sheng.pid is null and sheng.titlr='山西省';

• 视图

1.将多个表进行连接查询
select * from scores
inner join students on scores.stuid=students.id
inner join subjects on scores.subid=subjects.id;

2.创建一个视图
create view v_stu_sub_sco as
select stu.*,sco.score,sub.title from scores as sco
inner join students as stu on sco.stuid=stu.id
inner join subjects as sub on sco.subid=sub.id
where stu.isDelete=0 and sub.isDelete=0;

八、事务

• 事务

1.当一个业务逻辑需要多个sql完成时，如果其中某条sql语句出错，则希望整个操作都退出
2.使用事务可以完成退回的功能，保证业务逻辑的正确性
3.事务四大特性
    a.原子性：事务中的全部操作在数据库中是不可分割的，要么全部完成，要么都不执行
    b.一致性：几个并行执行的事务，其执行结果必须与按某一顺序串执行的结果相一致
    c.隔离性：事务的执行不收其他事务的干扰，事务执行的中间结果对其他事务必须是透明的
    d.持久性：对于任意已提交事务，系统必须保证该事物对数据库的改变不被丢失，及时数据库出现故障
4.要求：表的类型必须是innodb或bdb类型，才可以对此使用事务
5.查看表的创建语句
    show create table students;
6.修改表的类型
    alter table '表名' engine=innodb
7.事务语句
    开启begin;
    提交commit;
    回滚follback;

8.实例
a.打开两个终端，连接mysql，使用同一个数据库，操作同一张表
    终端1：select * from students;
    终端2：begin;
          insert into students(name) values('张飞');
    
    终端1：select * from students;
    终端2：rollback;
          select * from students;

九、索引

• 思考：在图书馆中，如何找到一本书

1.一般的应用系统，读写比例在10:1左右，而且插入操作和更新操作很少出现性能问题，遇到最多的，也是最容易
  出问题的，还是一些复杂的查询操作，所以查询语句的忧患很重要
2.当数据库中的数据量很大时，查找数会变得很慢
3.索引能提高数据访问性能
4.主键和唯一索引，都是索引，可以提高查询速度

• 选择列的数据类型

1.越小的数据类型通常更好，他们所需要的空间也越小，便于处理
2.简单的数据类型更好，整型数据比起字符，其处理开销更小
3.尽量避免null，应该制定为not null，除非要存储null，在mysql中，含有空值的列很难进行查询优化，因为
  它们使得所有、索引的统计信息以及比较运算更加复杂，应该用0、一个特殊的值或者一个空串代替空值

• 索引操作

1.索引分单列索引和组合索引
    单列索引：即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引
    组合索引：即一个索引包含多个列
    
2.查看索引
show index from table_name;

3.创建索引
create index indexName on mytable(username(length));

4.删除索引
drop index [indexName] on mytable;

缺点：a.虽然索引大大提高了查询速度，同时会降低更新表的速度，如对标进行insert、update和delete。因为更新
     表时，mysql不仅要保存数据，还要保存索引文件
     b.同时建立索引会占用磁盘空间的索引文件
     
5.实例
a.开启运行时间检测
    set profiling=1;

b.执行查询语句
    select * from areas where a titlt='北京';

c.查看执行时间
    show profiles;
    
d.为表areas的title列创建索引
    create index titleIndex on areas(title(20));
    
e.执行查询语句
    select * from areas where title='北京市';
    
f.再次查看执行的时间
    show prodiles;

十、与python的交互

• Connection对象

1.用于建立与数据库的连接

2.创建对象：调用connect()方法
conn=connect(参数列表)

a.host:连接的mysql主机，如果本机则是'localhost'
b.port:连接的mysql主机的端口，默认是3306
c.db:数据库的名称
d.user:连接的用户名
e:passwd:连接的密码
f:charset:通信采用的编码方式，推荐使用utf8

3.对象的方法
a.close()关闭连接
b.commit()事务，所以需要提交才会生效
c.rollback()事务，放弃之前的操作
d.cursor()返回Cursor对象，用于执行sql语句并获得结果

• Cursor对象

1.执行sql语句
2.创建对象：调用Connection对象的cursor()方法
cursor1=conn.cursor()

3.对象的方法
a.close()关闭
b.execute(operation,[parameters])执行语句，返回受影响的行数
c.fectone()执行查询语句时，获取查询结果集的第一个行数据，返回一个元祖
d.next()执行查询语句时，获取当前行的下一行
e.fetchall()执行查询时，获取结果集的所有行，一行构成一个元祖，再将这些元祖装入一个元祖返回
f.scroll(value,[mode])将行指针移动到某个位置
    * mode表示移动的方式
    * mode的默认值为relative，表示基于当前行移动到value，value为正则向下移动，value为负
      则向上移动
    * mode的值为absolute，表示基于第一条数据的位置，第一条数据的位置为0

4.对象的属性
a.rowcount只读属性，表示最近一次execute()执行后受影响的行数
b.connection获得当前连接对象

• 实例1

import pymysql

conn = pymysql.connect(
    host='localhost',
    port=3306,
    db='test',
    user='root',
    passwd='mysql',
    charset='utf8'
)

# 获取游标
cursor = conn.cursor()

# 插入数据
sql = "insert into students (name,sex,age) values ('%s','%s',%d)"
data = ('张良', 'male', '32')
count = cursor.execute(sql % data)
print(count)
conn.commit()
print('successful insert data!')

# 修改数据
sql = "update students set age=%d where name='%s' "
data = (36, '李白')
cursor.execute(sql % data)
conn.commit()
print('successful change data!')

# 查询数据
sql = "select name,sex from students where age='%s'"
data = (36)
cursor.execute(sql % data)
for row in cursor.fetchall():
    print('Name:%s\t sex:%s\t' % row)
print('found the data!')

# 删除数据
sql = "delete from students where age='32' limit '%s'"
data = (32, 'male')
cursor.execute(sql % data)
conn.commit()
print('delete successful!')

# 事务处理
sql_1 = "update students set age=age+1 where name='李白'"
sql_2 = "update students set income=income+1000 where name='李白'"
sql_3 = "update students set expend=expend+10 where name='李白'"

try:
    cursor.execute(sql_1)
    cursor.execute(sql_2)
    cursor.execute(sql_3)
except Exception as e:
    conn.rollback()
    print('cursor failed', e)
else:
    conn.commit()
    print('cursor successful', cursor.rowcount)

cursor.close()
conn.close()

• 实例2

# 创建集合，当插入的数据大于size时，后面的数据会覆盖前面的数据
db.createCollection('sub',{capped:true,size:4})

# 插入第一条数据
db.sub.insert({title:'linux',count:10})
db.sub.find()

# 插入第二条数据
db.sub.insert({title:'web',count:15})
db.sub.find()

# 插入第三条数据
db.sub.insert({title:'sql',count:8})
db.sub.find()

# 插入第四条数据
db.sub.insert({title:'python',count:12})
db.sub.find()

# 插入第五条数据
db.sub.insert({title:'mongo',count:4})
db.sub.find()

# 插入第六条数据
db.sub.insert({title:'css',count:8})
db.sub.find()