Mysql 入门-蒲公英云

数据库就是一种特殊的文件，其中存储着需要的数据

一：操作数据库

1.1 命令行连接

mysql -uroot -p
回车后输入密码，当前设置的密码为mysql

1.2 查看所有数据库

show databases;

1.3 使用数据库

use 数据库名;

1.4 查看当前使用的数据库

select database();

1.5 创建数据库

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

1.6 删除数据库

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

二：数据表操作

2.1 查看当前数据库中所有表

show tables;

2.2 查看表结构

desc 表名;

2.3 创建表

例：创建班级表
create table classes(
 id int unsigned auto_increment primary key not null,
 name varchar(10)
);

2.4 修改表-添加字段

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

2.5 修改表-修改字段：重命名版

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

2.6 修改表-修改字段：不重命名版

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

2.7 修改表-删除字段

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

2.8 删除表

drop table 表名;
例：
drop table students;

2.9 查看表的创建语句

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

三 . MySQL 查询

3.1 普通查询

查询所有字段

select from 表名;
例：
select from students;
查询指定字段

select 列1,列2,… from 表名;
例:
select name from students;
使用 as 给字段起别名

select id as 序号, name as 名字, gender as 性别 from students;
可以通过 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;
在select后面列前使用distinct可以消除重复的行

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

3.2 条件查询

使用where子句对表中的数据筛选，结果为true的行会出现在结果集中

语法如下：

select from 表名 where 条件;
例：
select from students where id=1;
where后面支持多种运算符，进行条件的处理
- 比较运算符 = > >= < <= != <>
- 逻辑运算符 and or not
- 模糊查询 like
  - %表示任意多个任意字符
  - _表示一个任意字符
```
select * from students where name like '黄%' or name like '%靖';
```
- 范围查询
  - in表示在一个非连续的范围内
```
select * from students where id in(1,3,8);
```
  - between … and …表示在一个连续的范围内
```
select * from students where id between 3 and 8;
```

空判断 判空is null

select * from students where height is null;

优先级

优先级由高到低的顺序为：小括号，not，比较运算符，逻辑运算符

3.3 排序查询

为了方便查看数据，可以对数据进行排序

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

说明

将行数据按照列1进行排序，如果某些行列1的值相同时，则按照列2排序，以此类推
默认按照列值从小到大排列(asc)
asc从小到大排列，即升序
desc从大到小排序，即降序

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

select * from students order by age desc,height desc;

3.4 聚合函数

count(*)表示计算总行数，括号中写星与列名，结果是相同的
max(列)表示求此列的最大值
min(列)表示求此列的最小值
sum(列)表示求此列的和
avg(列)表示求此列的平均值

select count(*) from students;

3.5 分组

group by的含义:将查询结果按照1个或多个字段进行分组，字段值相同的为一组
group by + group_concat()
- group_concat(字段名)可以作为一个输出字段来使用，
- 表示分组之后，根据分组结果，使用group_concat()来放置每一组的某字段的值的集合
group by + 集合函数
- 通过group_concat()的启发，我们既然可以统计出每个分组的某字段的值的集合，那么我们也可以通过集合函数来对这个值的集合做一些操作
group by + having
- having 条件表达式：用来分组查询后指定一些条件来输出查询结果
- having作用和where一样，但having只能用于group by
group by + with rollup
- with rollup的作用是：在最后新增一行，来记录当前列里所有记录的总和
select gender,group_concat(id) from students group by gender;
+————+—————————+
| gender | group_concat(id) |
+————+—————————+
| 男 | 3,4,8,9,14 |
| 女 | 1,2,5,7,10,12,13 |
| 中性 | 11 |
| 保密 | 6 |
+————+—————————+

分别统计性别为男/女的人年龄平均值
select gender,avg(age) from students group by gender;
+————+—————+
| gender | avg(age) |
+————+—————+
| 男 | 32.6000 |
| 女 | 23.2857 |
| 中性 | 33.0000 |
| 保密 | 28.0000 |
+————+—————+

select gender,count() from students group by gender having count()>2;
+————+—————+
| gender | count(*) |
+————+—————+
| 男 | 5 |
| 女 | 7 |
+————+—————+

select gender,count() from students group by gender with rollup;
+————+—————+
| gender | count() |
+————+—————+
| 男 | 5 |
| 女 | 7 |
| 中性 | 1 |
| 保密 | 1 |
| NULL | 14 |
+————+—————+

3.6 分页

当数据量过大时，在一页中查看数据是一件非常麻烦的事情

语法

select * from 表名 limit start,count

说明

从start开始，获取count条数据

例1：查询前3行男生信息

select * from students where gender=1 limit 0,3;

3.7 总结

查询的完整格式 ^_^ 不要被吓到其实很简单 ! _ !

SELECT select_expr [,select_expr,...] [ 
 FROM tb_name
 [WHERE 条件判断]
 [GROUP BY {col_name | postion} [ASC | DESC], ...] 
 [HAVING WHERE 条件判断]
 [ORDER BY {col_name|expr|postion} [ASC | DESC], ...]
 [ LIMIT {[offset,]rowcount | row_count OFFSET offset}]
]

完整的select语句

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

四：python 操作 MYSQL

4.1 引入模块

在py文件中引入pymysql模块

from pymysql import *

4.2 Connection 对象

用于建立与数据库的连接
创建对象：调用connect()方法

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

4.3 对象的方法

close()关闭连接
commit()提交
cursor()返回Cursor对象，用于执行sql语句并获得结果

4.4 Cursor对象

用于执行sql语句，使用频度最高的语句为select、insert、update、delete
获取Cursor对象：调用Connection对象的cursor()方法

cs1=conn.cursor()

4.5 对象的方法

close()关闭
execute(operation [, parameters ])执行语句，返回受影响的行数，主要用于执行insert、update、delete语句，也可以执行create、alter、drop等语句
fetchone()执行查询语句时，获取查询结果集的第一个行数据，返回一个元组
fetchall()执行查询时，获取结果集的所有行，一行构成一个元组，再将这些元组装入一个元组返回

4.6 对象的属性

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

五：python mysql 增删改查

5.1 基础

from pymysql import *
def main():
 # 创建Connection连接
 conn = connect(host='localhost',port=3306,database='jing_dong',user='root',password='mysql',charset='utf8')
 # 获得Cursor对象
 cs1 = conn.cursor()
 # 执行insert语句，并返回受影响的行数：添加一条数据
 # 增加
 count = cs1.execute('insert into goods_cates(name) values("硬盘")')
 #打印受影响的行数
 print(count)
 count = cs1.execute('insert into goods_cates(name) values("光盘")')
 print(count)
 # # 更新
 # count = cs1.execute('update goods_cates set name="机械硬盘" where name="硬盘"')
 # # 删除
 # count = cs1.execute('delete from goods_cates where id=6')
 # 提交之前的操作，如果之前已经之执行过多次的execute，那么就都进行提交
 conn.commit()
 # 关闭Cursor对象
 cs1.close()
 # 关闭Connection对象
 conn.close()
if __name__ == '__main__':
 main()

5.2 查询一行数据

from pymysql import *
def main():
 # 创建Connection连接
 conn = connect(host='localhost',port=3306,user='root',password='mysql',database='jing_dong',charset='utf8')
 # 获得Cursor对象
 cs1 = conn.cursor()
 # 执行select语句，并返回受影响的行数：查询一条数据
 count = cs1.execute('select id,name from goods where id>=4')
 # 打印受影响的行数
 print("查询到%d条数据:" % count)
 for i in range(count):
 # 获取查询的结果
 result = cs1.fetchone()
 # 打印查询的结果
 print(result)
 # 获取查询的结果
 # 关闭Cursor对象
 cs1.close()
 conn.close()
if __name__ == '__main__':
 main()

5.3 查询多行数据

from pymysql import *
def main():
 # 创建Connection连接
 conn = connect(host='localhost',port=3306,user='root',password='mysql',database='jing_dong',charset='utf8')
 # 获得Cursor对象
 cs1 = conn.cursor()
 # 执行select语句，并返回受影响的行数：查询一条数据
 count = cs1.execute('select id,name from goods where id>=4')
 # 打印受影响的行数
 print("查询到%d条数据:" % count)
 # for i in range(count):
 # # 获取查询的结果
 # result = cs1.fetchone()
 # # 打印查询的结果
 # print(result)
 # # 获取查询的结果
 result = cs1.fetchall()
 print(result)
 # 关闭Cursor对象
 cs1.close()
 conn.close()
if __name__ == '__main__':
 main()