1.数据库管理

1.1 SQL语句

1.1.1 查看当前所有的数据库

show databases;

1.1.2 创建数据库

create database 数据库名;

create databse 数据库名 default charset utf8  # 支持中文

8.0版本默认就是utf编码

1.1.3 删除数据库

drop database 数据库;

1.1.4 进入数据库

只有进入某一个数据库，才能对指定数据库里面的数据表或者数据行操作

use 数据库名

1.2 python操作

无论通过何种方式取链接mysql,本质上发送的指令是相同的,只是链接的方式和操作形式不同而已

要想通过python操作mysql，我们需要导入第三方模块

pip install pymysql

# encoding:utf-8

# author:kunmzhao

# email:1102669474@qq.com

import pymysql

# 1.连接数据库

conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='root', charset='utf8')

cursor = conn.cursor()

# 2. 查看数据库

cursor.execute("show databases")

print(cursor.fetchall())

# 3.创建数据库

cursor.execute('create database test2 default charset utf8')

conn.commit()

cursor.execute("show databases")

print(cursor.fetchall())

# 4.删除数据库

cursor.execute('drop database test2')

conn.commit()

cursor.execute("show databases")

print(cursor.fetchall())

# 5.进入数据库

cursor.execute('use mysql')

cursor.execute('show tables;')

print(cursor.fetchall())

cursor.close()

conn.close()

2.数据表管理

2.1 SQL语句

在操作数据表的时候首先需要进入对应的数据库

use 数据库名;

2.1.1 查看当前所有的表

show tables;

2.1.2 删除表

drop table 表名;

2.1.3 清空表

delete from 表名;

2.1.4 创建表

创建表必须要指定表名,列名称，列类型

create table 表名(

    列名字 类型,

    列名字 类型,

    列名字 类型,

);

create table user(

    id int primary key, -- 主键(不允许为空且唯一)

    name varchar(16) not null,  -- 不能为空

    age int default 18  -- 默认值为18

 );

主键：一张表一般都有一个主键，primary key指定

是否为空：代表以后该数据是否可以为空,默认为允许为空,not null指定不可为空

默认值:代表着以后不主动添加该字段数据，设置为默认值, default指定

常见的列类型

int[(m)][unsigned][zerofill]

int             表示有符号，取值范围：-2147483648 ～ 2147483647
int unsigned    表示无符号，取值范围：0 ～ 4294967295
int(5)zerofill  仅用于显示，当不满足5位时，按照左边补0，例如：00002；满足时，正常显示。

tinyint[(m)] [unsigned] [zerofill]

有符号，取值范围：-128 ～ 127.
无符号，取值范围：0 ～ 255

bigint[(m)][unsigned][zerofill]

有符号，取值范围：-9223372036854775808 ～ 9223372036854775807
无符号，取值范围：0  ～  18446744073709551615

decimal[(m[,d])] [unsigned] [zerofill]

准确的小数值，m是数字总个数（负号不算），d是小数点后个数。 m最大值为65，d最大值为30。

char(m)

定长字符串，m代表字符串的长度，最多可容纳255个字符。
定长的体现：即使内容长度小于m，也会占用m长度

varchar(m)

变长字符串，m代表字符串的长度，最多可容纳65535个字节。

text

text数据类型用于保存变长的大字符串，可以组多到65535 (2**16 − 1)个字符。

一般情况下，长文本会用text类型。例如：文章、新闻等。

longtext

A TEXT column with a maximum length of 4,294,967,295 or 4GB (2**32 − 1)

datetime

YYYY-MM-DD HH:MM:SS（1000-01-01 00:00:00/9999-12-31 23:59:59）

date

YYYY-MM-DD（1000-01-01/9999-12-31）

time

HH:MM:SS（'-838:59:59'/'838:59:59'）

timestamp

YYYY-MM-DD HH:MM:SS（1970-01-01 00:00:00/2037年）

MySQL还有很多其他的数据类型，例如：*set、enum、TinyBlob、Blob、MediumBlob、LongBlob 等*，详细见官方文档：https://dev.mysql.com/doc/refman/5.7/en/data-types.html

查看表结构

desc 表名

2.1.5 修改表

添加列

alter table 表名 add 列名 类型;

alter table 表名 add 列名 类型 DEFAULT 默认值;

alter table 表名 add 列名 类型 not null default 默认值;

alter table 表名 add 列名 类型 not null primary key auto_increment;

删除列

alter table 表名 drop column 列名;

修改列类型 + 名称

alter table 表名 change 原列名 新列名 新类型;

修改列默认值

alter table 表名 alter 列名 set default 1000;

删除列默认值

alter table 表名 alter 列名 drop default;

添加主键

alter table 表名 add primary key(列名);

删除主键

alter table 表名 drop primary key;

2.2 python操作

import pymysql

# 连接MySQL

conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', charset="utf8")

cursor = conn.cursor()

# 1. 创建数据库

"""

cursor.execute("create database db4 default charset utf8 collate utf8_general_ci")

conn.commit()

"""

# 2. 进入数据库、查看数据表

"""

cursor.execute("use db4")

cursor.execute("show tables")

result = cursor.fetchall()

print(result)

"""

# 3. 进入数据库创建表

cursor.execute("use db4")

sql = """

create table L4(

    id int not null primary key auto_increment,

    title varchar(128),

    content text,

    ctime datetime

)default charset=utf8;

"""

cursor.execute(sql)

conn.commit()

# 4. 查看数据库中的表

"""

cursor.execute("show tables")

result = cursor.fetchall()

print(result)

"""

# 5. 其他 drop table... 略过

# 关闭连接

cursor.close()

conn.close()

3.数据行管理

数据行操作就是对数据表的内容进行增删改查，是学习mysql的重头戏，先简单学习，后续再深入学习

3.1 SQL语句

3.1.1 新增数据

insert into 表名(列名，列名，列名)    values(对应值，对应值，对应值);

# 插入一条数据

insert into user (id,name,age) values(1,'zhangsan', 18);

# 插入多条数据

insert into user (id,name,age) values(2,'lisi', 18),(3,'wangwu',20);

3.1.2 删除数据

delete from 表名 where 条件;

delete from user where name='zhangsan';

delete from user where id > 9;

delete from user where name='zhangsan' and id < 6;

delete from user where name='zhangsan' or id = 1;

3.1.3 修改数据

update 表名 set 列名=值 where 条件;

update user set name='kunmzhao' where id = 1;

3.1.4查询数据

select * from 表名;

select 列名,列名,列名 from 表名;

select 列名 as 别名,列名,列名 from 表名;

select 列名,列名,列名 from 表名 where 条件;

select * from user;

select id as ID, name as 姓名 from user where id =1;

3.2 python操作

import pymysql

# 连接MySQL，自动执行 use userdb; -- 进入数据库

conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', charset="utf8", db='userdb')

cursor = conn.cursor()

# 1.新增（需commit）

cursor.execute("insert into tb1(name,password) values('武沛齐','123123')")

conn.commit()

# 2.删除（需commit）

cursor.execute("delete from tb1 where id=1")

conn.commit()

# 3.修改（需commit)

cursor.execute("update tb1 set name='xx' where id=1")

conn.commit()

# 4.查询

cursor.execute("select * from tb where id>10")

data = cursor.fetchone() # cursor.fetchall()

print(data)

# 关闭连接

cursor.close()

conn.close()

3.3 查询数据进阶

3.3.1 条件

where

# 比较运算

select * from info where age > 30;

select * from info where aid != 1;

select * from info where between 2 and 4; -- id介于2-4之间

# 逻辑运算

select * from info where name='alex' and age=16;

select * from info where name='alex' or age = 16;

# 成员运算

select * from info where id in (1,4,6);

select * from info where id not in (1, 4, 6);

select * from info where id in (select id from depart);

# 布尔运算

# select * from depart where id=5存在则整体进行查询，否则整体为空

select * from info where exists (select * from depart where id=5);

3.3.2 通配符

一般用于模糊搜素 使用关键字like

%：多个字符

?:单个字符

#：单数字

select * from info where name like "%明%" -- 查询包含明的name

select * from info where name like "明%" -- 查询包含明且以明开头的name

select * from info where name like "%明" -- 查询包含明且以明结尾的name

3.3.3 映射

获取想要的列

select * from info; -- 获取info表的所有字段

select id,name from info; -- 获取info表的id,name字段

select id, name as 姓名 from info; -- 获取info表的id,name字段，并将name字段命名为姓名

3.3.4 排序

select * from info order by age asc; -- 按照年龄顺序排序

select * from info order by age desc; -- 按照年龄倒序排序

select * from info order by age asc,id desc; -- 优先按照age从小到大；如果age相同则按照id从大到小。

3.3.5 取部分

获取部分数据

select * from info limit 5;                         -- 获取前5条数据

select * from info limit 3 offset 2;                -- 从位置2开始，向后获取前3数据

3.3.6 分组

select age,max(id),min(id),count(id),sum(id),avg(id) from info group by age;

select depart_id,count(id) from info group by depart_id having count(id) > 2;  -- 分组之后再进行条件筛选

select count(id) from info;

select max(id) from info;

3.3.7 连表

多个表可以连接起来进行查询

主表 left outer join 从表 on 主表.x = 从表.id

select * from info left outer join depart on info.depart_id = depart.id;

select info.id,info.name,info.email,depart.title from info left outer join depart on info.depart_id = depart.id;

到目前为止SQL执行顺序：

2.mysql-库表行管理