MySql最全命令！！！

1.安装mysql服务端

1.1MySql服务端软件安装

在Ubuntu中打开终端，输入下面的命令:

sudo apt-get install mysql-server

查看MySQL服务状态:

sudo service mysql status

停止MySQL服务:

sudo service mysql stop

启动MySQL服务:

sudo service mysql start

重启MySQL服务:

sudo service mysql restart

MySQL配置文件的介绍:
配置文件路径为: /etc/mysql/mysql.conf.d/mysqld.cnf

port表示端口号，默认为3306bind-address表示服务器绑定的ip，默认为127.0.0.1datadir表示数据库保存路径，默认为/var/lib/mysqllog_error表示错误日志，默认为/var/log/mysql/error.log

1.2MySql客户端软件安装

常用的MySQL数据库客户端软件有

1. 图形化界面客户端Navicat
2. 命令行客户端mysql

此处演示用命令行方式安装mysql的客户端软件（Ubuntu中）
输入下面命令：

sudo apt-get install mysql-client

MySQL客户端的使用:

MySQL客户端连接MySQL服务端命令

mysql -u root -p

2.命令行客户端MySQL的使用

登录成功后, 输入如下命令查看效果：

显示当前时间select now();

登出(退出)数据库:quit 或 exit 或 ctrl + d

2.1数据库操作的SQL语句

1. 查看所有数据库

show databases;

2. 创建数据库

create database 数据库名 charset=utf8;例：create database python charset=utf8;

3. 使用数据库

use 数据库名;

4. 查看当前使用的数据库

select database();

5. 删除数据库

drop database 数据库名;例：drop database python;

2.2表结构操作的SQL语句

1. 查看当前数据库中所有表

show tables;

2. 创建表
   格式如下：

create table 表名(字段名称 数据类型可选的约束条件,column1 datatype contrai,...);

比如：

create table students(id int unsigned primary key auto_increment not null,name varchar(20) not null,age tinyint unsigned default 0,height decimal(5,2),gender enum('男','女','人妖','保密'));

3. 修改表-添加字段
   例如下面的这个例子：增加一列，类型为datetime，列名为birthday

alter table 表名 add 列名 类型 约束;例：alter table students add birthday datetime;

4. 修改表-修改字段类型

alter table 表名 modify 列名 类型 约束;例：alter table students modify birthday date not null;

注意：modify: 只能修改字段类型或者约束，不能修改字段名。如果说要修改字段名和字段的类型的话，应该用如下方式：

5. 修改表-修改字段名和字段类型

alter table 表名 change 原名 新名 类型及约束;例：alter table students change birthday birth datetime not null;

ps：change: 既能对字段重命名又能修改字段类型还能修改约束

6. 修改表-删除字段

alter table 表名 drop 列名;例：alter table students drop birthday;

7. 查看创表SQL语句（意思是说查看已经创建的表的结构）

show create table 表名;例：show create table students;

8. 查看创库SQL语句

show create database 数据库名;例：show create database mytest;

9. 删除表

drop table 表名;例：drop table students;

2.3表数据操作的SQL语句

1. 查询数据

-- 1. 查询所有列select * from 表名;例：select * from students;-- 2. 查询指定列select 列1,列2,... from 表名;例：select id,name from students;

2. 添加数据

-- 1. 全列插入：值的顺序与表结构字段的顺序完全一一对应insert into 表名 values (...)例:insert into students values(0, 'xx', default, default, '男');-- 2. 部分列插入：值的顺序与给出的列顺序对应insert into 表名 (列1,...) values(值1,...)例:insert into students(name, age) values('王二小', 15);-- 3. 全列多行插入insert into 表名 values(...),(...)...;例:insert into students values(0, '张飞', 55, 1.75, '男'),(0, '关羽', 58, 1.85, '男');-- 4. 部分列多行插入insert into 表名(列1,...) values(值1,...),(值1,...)...;例：向students表中的的name和height这两列插入数据insert into students(name, height) values('刘备', 1.75),('曹操', 1.6);

注意：主键列是自动增长，但是在全列插入时需要占位，通常使用空值(0或者null或者default)
在全列插入时，如果字段列有默认值可以使用 default 来占位，插入后的数据就是之前设置的默认值

因此：在全列插入数据的时候，id那一列写0就好了，会自动添加主键的。

3. 修改数据

update 表名 set 列1=值1,列2=值2... where 条件例：更新students表中id为6的那一行的age字段和gender字段update students set age = 18, gender = '女' where id = 6;

4. 删除数据

delete from 表名 where 条件例：删除id为5的那一行的数据delete from students where id=5;

有的时候，我们不想要真正地将数据在内存中删除，因此可以加多一列字段，用来表示当前的这一行的数据是否被删除了。类似于一个flag

-- 添加删除表示字段，0表示未删除 1表示删除alter table students add isdelete bit default 0;-- 逻辑删除数据update students set isdelete = 1 where id = 8;

3.as和distinct关键字

3.1as关键字的使用

as关键字就是用来起别名的！

1. 使用 as 给字段起别名

select id as 序号, name as 名字, gender as 性别 from students;得到的数据中的那些字段就会对应的改成as后面的写法

2. 可以通过 as 给表起别名

-- 如果是单表查询 可以省略表名select id, name, gender from students;-- 表名.字段名select students.id,students.name,students.gender from students;-- 可以通过 as 给表起别名 select s.id,s.name,s.gender from students as s;

注意：在这里给表起别名看起来并没有什么意义,然而并不是这样的，我们在后期学习 自连接 的时候，必须要对表起别名。而且用as也可以减少咋们书写代码量

3.2distinct关键字

distinct可以去除重复数据行。这个词本身就有区分的含义。

select distinct 列1,... from 表名;例： 查询班级中学生的性别select name, gender from students;-- 想要对其中重复数据行进行去重操作可以使用 distinct，这样得到的那些数据后，name和gender都相同的行会只保留一行的数据select distinct name, gender from students;

4.where条件查询

使用where条件查询可以对表中的数据进行筛选，条件成立的记录会出现在结果集中。

前面有些例子其实已经展示过了，我们来看看具体的例子

select * from 表名 where 条件;例：筛选得到id为1的那一行的数据select * from students where id = 1;

where语句支持的运算符:

1. 比较运算符
2. 逻辑运算符
3. 模糊查询
4. 范围查询
5. 空判断

4.1比较运算符查询

例1：查询编号大于3的学生:

select * from students where id > 3;

例2：查询编号不大于4的学生:

select * from students where id <= 4;

例3：查询姓名不是“xx”的学生:

select * from students where name != 'xx';

例4：查询没被删除的学生:

select * from students where is_delete=0;

4.2逻辑运算符查询

例1：查询编号大于3的女同学:

这个表中gender为0表示女同学，gender为1表示男同学select * from students where id > 3 and gender=0;

例2：查询编号小于4或没被删除的学生:

select * from students where id < 4 or is_delete=0;

例3：查询年龄不在10岁到15岁之间的学生:

select * from students where not (age >= 10 and age <= 15);

说明:多个条件判断想要作为一个整体，可以结合‘()’。

4.3模糊查询

1. like是模糊查询关键字
2. %表示任意多个任意字符
3. _表示一个任意字符
   例1：查询姓黄的学生:

select * from students where name like '黄%';

例2：查询姓黄并且“名”是一个字的学生:

select * from students where name like '黄_';

例3：查询姓黄或叫靖的学生:

select * from students where name like '黄%' or name like '%靖';

4.4范围查询

1. between … and … 表示在一个连续的范围内查询
2. in 表示在一个非连续的范围内查询
   例1：查询编号为3至8的学生:

select * from students where id between 3 and 8;

例2：查询编号不是3至8的男生:

select * from students where (not id between 3 and 8) and gender='男';

4.5空判断查询

1. 判断为空使用: is null
2. 判断非空使用: is not null
   例1：查询没有填写身高的学生:

select * from students where height is null;

注意:

1. 不能使用 where height = null 判断为空
2. 不能使用 where height != null 判断非空
3. null 不等于 空字符串，表示的是没填这个数据，而不是空字符串

5.排序

5.1排序查询语法

排序查询语法：

select * from 表名 order by 列1 asc|desc [,列2 asc|desc,...]

语法说明:

1. 先按照列1进行排序，如果列1的值相同时，则按照 列2 排序，以此类推
2. asc从小到大排列，即升序
3. desc从大到小排序，即降序
4. 默认按照列值从小到大排列（即asc关键字）

例1：查询未删除男生信息，按学号降序:

select * from students where gender=1 and is_delete=0 order by id desc;

例2：显示所有的学生信息，先按照年龄从大–>小排序，当年龄相同时 按照身高从高–>矮排序:

select * from studentsorder by age desc,height desc;

6.分页查询

使用 limit 关键字实现分页查询

6.1分页查询的语法

select * from 表名 limit start,count表示显示前count个，从start这一行开始

说明:

1. limit是分页查询关键字
2. start表示开始行索引，默认是0
3. count表示查询条数

6.2分页查询案例

已知每页显示m条数据，求第n页显示的数据
提示: 关键是求每页的开始行索引

查询学生表，获取第n页数据的SQL语句:

每一页显示m条数据，因此要看第n页的数据应该从索引(n - 1) * m开始select * from students limit (n-1)*m,m

7.聚合函数

聚合函数又叫组函数，通常是对表中的数据进行统计和计算，一般结合分组(group by)来使用，用于统计和计算分组数据。

常用的聚合函数:

1. count(col): 表示求指定列的总行数
2. max(col): 表示求指定列的最大值
3. min(col): 表示求指定列的最小值
4. sum(col): 表示求指定列的和
5. avg(col): 表示求指定列的平均值

7.1求总行数

-- 返回非NULL数据的总行数.select count(height) from students; -- 返回总行数，包含null值记录;select count(*) from students;

7.2求最大值

-- 查询女生的编号最大值select max(id) from students where gender = 2;

7.3求最小值

-- 查询未删除的学生最小编号select min(id) from students where is_delete = 0;

7.4求和

-- 查询男生的总身高select sum(height) from students where gender = 1;-- 平均身高select sum(height) / count(*) from students where gender = 1;

7.5求平均值

-- 求男生的平均身高, 聚合函数不统计null值，平均身高有误select avg(height) from students where gender = 1;-- 求男生的平均身高, 包含身高是null的select avg(ifnull(height,0)) from students where gender = 1;

注意：ifnull函数: 表示判断指定字段的值是否为null，如果为空使用自己提供的值。

7.6聚合函数的特点

聚合函数默认忽略字段为null的记录 要想列值为null的记录也参与计算，必须使用ifnull函数对null值做替换。

8.分组查询

分组查询就是将查询结果按照指定字段进行分组，字段中数据相等的分为一组。
分组查询基本的语法格式如下：

GROUP BY 列名 [HAVING 条件表达式] [WITH ROLLUP]

• 列名: 是指按照指定字段的值进行分组。
• HAVING 条件表达式: 用来过滤分组后的数据。
• WITH ROLLUP：在所有记录的最后加上一条记录，显示select查询时聚合函数的统计和计算结果

8.1group by 的使用

group by可用于单个字段分组，也可用于多个字段分组
下面的这两个案例，本质上就是进行了去重复的操作

-- 根据gender字段来分组select gender from students group by gender;-- 根据name和gender字段进行分组select name, gender from students group by name, gender;

8.2 group by + group_concat()的使用

group_concat(字段名): 统计每个分组指定字段的信息集合，每个信息之间使用逗号进行分割

-- 根据gender字段进行分组， 查询gender字段和分组的name字段信息select gender,group_concat(name) from students group by gender;得到的就是对性别进行了分组的数据，然后name的字段划分到gender里面

8.3group by + 聚合函数的使用

-- 统计不同性别的人的平均年龄select gender,avg(age) from students group by gender;-- 统计不同性别的人的个数select gender,count(*) from students group by gender;

8.4group by + having的使用

having作用和where类似都是过滤数据的，但having是过滤分组数据的，只能用于group by

-- 根据gender字段进行分组，统计分组条数大于2的select gender,count(*) from students group by gender having count(*)>2;

8.5group by + with rollup的使用

with rollup的作用是：在最后记录后面新增一行，显示select查询时聚合函数的统计和计算结果

-- 根据gender字段进行分组，汇总总人数select gender,count(*) from students group by gender with rollup;-- 根据gender字段进行分组，汇总所有人的年龄select gender,group_concat(age) from students group by gender with rollup;

9.连接查询

连接查询可以实现多个表的查询，当查询的字段数据来自不同的表就可以使用连接查询来完成。

连接查询可以分为:

内连接查询
左连接查询
右连接查询
自连接查询

9.1内连接查询

查询两个表中符合条件的共有记录
内连接查询语法格式:

select 字段 from 表1 inner join 表2 on 表1.字段1 = 表2.字段2

说明:
inner join 就是内连接查询关键字
on 就是连接查询条件
例1：使用内连接查询学生表与班级表:

select * from students as s inner join classes as c on s.cls_id = c.id;

9.2左连接

以左表为主根据条件查询右表数据，如果根据条件查询右表数据不存在使用null值填充

左连接查询语法格式:

select 字段 from 表1 left join 表2 on 表1.字段1 = 表2.字段2

说明:
left join 就是左连接查询关键字
on 就是连接查询条件
表1 是左表
表2 是右表
例1：使用左连接查询学生表与班级表:

select * from students as s left join classes as c on s.cls_id = c.id;

9.3右连接

以右表为主根据条件查询左表数据，如果根据条件查询左表数据不存在使用null值填充

右连接查询语法格式:

select 字段 from 表1 right join 表2 on 表1.字段1 = 表2.字段2

说明:

right join 就是右连接查询关键字on 就是连接查询条件表1 是左表表2 是右表

例1：使用右连接查询学生表与班级表:

select * from students as s right join classes as c on s.cls_id = c.id;

9.4自连接

左表和右表是同一个表，根据连接查询条件查询两个表中的数据。
自连接查询的用法:

select c.id, c.title, c.pid, p.title from areas as c inner join areas as p on c.pid = p.id where p.title = '山西省';

说明:
自连接查询必须对表起别名

自连接查询就是把一张表模拟成左右两张表，然后进行连表查询。
自连接就是一种特殊的连接方式，连接的表还是本身这张表

10.子查询

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

主查询和子查询的关系:

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

10.1子查询的使用

例1. 查询大于平均年龄的学生:

select * from students where age > (select avg(age) from students);

例2. 查询学生在班的所有班级名字:

select name from classes where id in (select cls_id from students where cls_id is not null);

例3. 查找年龄最大,身高最高的学生:

select * from students where (age, height) =(select max(age), max(height) from students);

11.数据库设计三范式

• 范式就是设计数据库的一些通用规范。

1. 1NF强调字段是最小单元，不可再分
2. 2NF强调在1NF基础上必须要有主键和非主键字段必须完全依赖于主键，也就是说 不能部分依赖
3. 3MF强调在2NF基础上 非主键字段必须直接依赖于主键，也就是说不能传递依赖(间接依赖)。

E-R模型由 实体、属性、实体之间的关系构成，主要用来描述数据库中表结构。
开发流程是先画出E-R模型，然后根据三范式设计数据库中的表结构

12.外键SQL语句的编写

外键约束:对外键字段的值进行更新和插入时会和引用表中字段的数据进行验证，数据如果不合法则更新和插入会失败，保证数据的有效性

12.1对于已经存在的字段添加外键约束

-- 为cls_id字段添加外键约束alter table students add foreign key(cls_id) references classes(id);

12.2在创建数据表时设置外键约束

-- 创建学校表create table school(id int not null primary key auto_increment, name varchar(10));-- 创建老师表create table teacher(id int not null primary key auto_increment, name varchar(10), s_id int not null, foreign key(s_id) references school(id));

12.3 删除外键约束

-- 需要先获取外键约束名称,该名称系统会自动生成,可以通过查看表创建语句来获取名称show create table teacher;-- 获取名称之后就可以根据名称来删除外键约束alter table teacher drop foreign key 外键名;

13.将查询结果插入到其它表中

目前只有一个goods表，我们想要增加一个商品分类信息，比如：移动设备这个分类信息，只通过goods表无法完成商品分类的添加，那么如何实现添加商品分类信息的操作?

答案:
创建一个商品分类表，把goods表中的商品分类信息添加到该表中。
将goods表中的分类名称更改成商品分类表中对应的分类id

13.1创建商品分类表

-- 创建商品分类表create table good_cates(id int not null primary key auto_increment, name varchar(50) not null);

13.2把goods表中的商品分类添加到商品分类表

-- 查询goods表中商品的分类信息select cate_name from goods group by cate_name;-- 将查询结果插入到good_cates表中insert into good_cates(name) select cate_name from goods group by cate_name;-- 添加移动设备分类信息insert into good_cates(name) values('移动设备');

14.使用连接更新表中某个字段数据

刚才我们已经创建了一个商品分类表(good_cates)，并完成了商品分类信息的插入，现在需要更新goods表中的商品分类信息，把商品分类名称改成商量分类id。

接下来我们实现第二步操作:
将goods表中的分类名称更改成商品分类表中对应的分类id

-- 查看goods表中的商品分类名称对应的商品分类idselect * from goods inner join good_cates on goods.cate_name = good_cates.name;-- 把该语句中from 后的语句理解为一张虚表update goods g inner join good_cates gc on g.cate_name=gc.name set g.cate_name=gc.id;

14.创建表并给某个字段添加数据

刚才我们完成了商品分类表**(good_cates)**的创建和商品分类信息的添加以及把商品表(goods)中的商品分类名称改成了对应的商品分类id，假如我们想要添加一个品牌，比如：“双飞燕” 这个品牌信息，只通过goods表无法完成品牌信息的添加，那么如何实现添加品牌信息的操作?

答案:

创建一个品牌表，把goods表中的品牌信息添加到该表中。
将goods表中的品牌名称更改成品牌表中对应的品牌id

14.1. 创建品牌表

-- 查询品牌信息 select brand_name from goods group by brand_name;-- 通过create table ...select来创建数据表并且同时插入数据-- 创建商品分类表，注意: 需要对brand_name 用as起别名，否则name字段就没有值create table good_brands ( id int unsigned primary key auto_increment, name varchar(40) not null) select brand_name as name from goods group by brand_name;

14.2更新goods表中的品牌信息

-- 将goods表中的品牌名称更改成品牌表中对应的品牌idupdate goods as g inner join good_brands gb on g.brand_name = gb.name set g.brand_name = gb.id;

15.修改表结构

目前我们已经把good表中的商品分类和品牌信息已经更改成了商品分类id和品牌id，接下来需要把 cate_name 和 brand_name 字段分别改成 cate_id和 brand_id 字段，类型都改成int类型。

-- 查看表结构desc goods;-- 通过alter table语句修改表结构alter table goods change cate_name cate_id int not null, change brand_name brand_id int not null;

16.PyMySQL的使用

16.1Python程序操作MySQL数据库

安装pymysql第三方包:

sudo pip3 install pymysql

1. 导入 pymysql 包

 import pymysql

2. 创建连接对象

调用pymysql模块中的connect()函数来创建连接对象,代码如下:

 conn=connect(参数列表) * 参数host：连接的mysql主机，如果本机是'localhost' * 参数port：连接的mysql主机的端口，默认是3306 * 参数user：连接的用户名 * 参数password：连接的密码 * 参数database：数据库的名称 * 参数charset：通信采用的编码方式，推荐使用utf8

连接对象操作说明:关闭连接 conn.close()提交数据 conn.commit()撤销数据 conn.rollback()

3. 获取游标对象

获取游标对象的目标就是要执行sql语句，完成对数据库的增、删、改、查操作。代码如下:

 # 调用连接对象的cursor()方法获取游标对象  cur =conn.cursor()

游标操作说明:使用游标执行SQL语句: execute(operation [parameters ]) 执行SQL语句，返回受影响的行数，主要用于执行insert、update、delete、select等语句获取查询结果集中的一条数据:cur.fetchone()返回一个元组, 如 (1,'张三')获取查询结果集中的所有数据: cur.fetchall()返回一个元组,如((1,'张三'),(2,'李四'))关闭游标: cur.close(),表示和数据库操作完成

4. pymysql完成数据的查询操作

import pymysql# 创建连接对象conn = pymysql.connect(host='localhost', port=3306, user='root', password='xxx',database='python', charset='utf8')# 获取游标对象cursor = conn.cursor()# 查询 SQL 语句sql = "select * from students;"# 执行 SQL 语句 返回值就是 SQL 语句在执行过程中影响的行数row_count = cursor.execute(sql)print("SQL 语句执行影响的行数%d" % row_count)# 取出结果集中一行数据,　例如:(1, '张三')# print(cursor.fetchone())# 取出结果集中的所有数据, 例如:((1, '张三'), (2, '李四'), (3, '王五'))for line in cursor.fetchall():print(line)# 关闭游标cursor.close()# 关闭连接conn.close()

5. pymysql完成对数据的增删改

import pymysql# 创建连接对象conn = pymysql.connect(host='localhost', port=3306, user='root', password='xxx',database='python', charset='utf8')# 获取游标对象cursor = conn.cursor()try:# 添加 SQL 语句# sql = "insert into students(name) values('刘璐'), ('王美丽');"# 删除 SQ L语句# sql = "delete from students where id = 5;"# 修改 SQL 语句sql = "update students set name = '王铁蛋' where id = 6;"# 执行 SQL 语句row_count = cursor.execute(sql)print("SQL 语句执行影响的行数%d" % row_count)# 提交数据到数据库conn.commit()except Exception as e:# 回滚数据， 即撤销刚刚的SQL语句操作conn.rollback()# 关闭游标cursor.close()# 关闭连接conn.close()

说明:conn.commit() 表示将修改操作提交到数据库conn.rollback() 表示回滚数据

6. 防止SQL注入

   SQL注入：
   用户提交带有恶意的数据与SQL语句进行字符串方式的拼接，从而影响了SQL语句的语义，最终产生数据泄露的现象。

如何防止SQL注入? SQL语句参数化

• SQL语言中的参数使用%s来占位，此处不是python中的字符串格式化操作
• 将SQL语句中%s占位所需要的参数存在一个列表中，把参数列表传递给execute方法中第二个参数

防止SQL注入的示例代码:

from pymysql import connectdef main():find_name = input("请输入物品名称：")# 创建Connection连接conn = connect(host='localhost',port=3306,user='root',password='xxx',database='jing_dong',charset='utf8')# 获得Cursor对象cs1 = conn.cursor()# 非安全的方式# 输入 ' or 1 = 1 or ' (单引号也要输入)# sql = "select * from goods where name='%s'" % find_name# print("""sql===>%s<====""" % sql)# # 执行select语句，并返回受影响的行数：查询所有数据# count = cs1.execute(sql)# 安全的方式# 构造参数列表params = [find_name]# 执行select语句，并返回受影响的行数：查询所有数据count = cs1.execute("select * from goods where name=%s", params)# 注意：# 如果要是有多个参数，需要进行参数化# 那么params = [数值1, 数值2....]，此时sql语句中有多个%s即可# %s 不需要带引号# 打印受影响的行数print(count)# 获取查询的结果# result = cs1.fetchone()result = cs1.fetchall()# 打印查询的结果print(result)# 关闭Cursor对象cs1.close()# 关闭Connection对象conn.close()if __name__ == '__main__':main()

17.事务

事务就是用户定义的一系列执行SQL语句的操作, 这些操作要么完全地执行，要么完全地都不执行， 它是一个不可分割的工作执行单元。

事务的特性:

1. 原子性: 强调事务中的多个操作时一个整体
2. 一致性: 强调数据库中不会保存不一致状态
3. 隔离性: 强调数据库中事务之间相互不可见
4. 持久性: 强调数据库能永久保存数据，一旦提交就不可撤销

• MySQL数据库默认采用自动提交(autocommit)模式, 也就是说修改数据(insert、update、delete)的操作会自动的触发事务,完成事务的提交或者回滚
• 开启事务使用 begin 或者 start transaction;
  回滚事务使用 rollback;
• pymysql 里面的 conn.commit() 操作就是提交事务
• pymysql 里面的 conn.rollback() 操作就是回滚事务

18.索引

索引在MySQL中也叫做“键”，它是一个特殊的文件，它保存着数据表里所有记录的位置信息，更通俗的来说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。

1. 索引是加快数据库的查询速度的一种手段
2. 创建索引使用: alter table 表名 add index 索引名[可选] (字段名, xxx);
3. 删除索引使用: alter table 表名 drop index 索引名;