数据库操作

# 创建数据库
create database 库名 charset=utf8;
# 使用数据库
use 库名;
# 退出数据库
quit
# 查看所有数据库
show databases;
# 查看当前使用的数据库
select database();
# 删除数据库
drop database 库名;

表操作

#查看当前数据库中所有表
show tables;#创建表
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('男','女')
);#修改表-add添加字段
alter table 表名 add 列名 类型 约束;
alter table students add birthday datetime;
#修改表-modify修改字段类型(modify: 改 字段类型、约束)
alter table 表名 modify 列名 类型 约束;
alter table students modify birthday date not null;
#修改表-change修改字段名和字段类型(change: 改 字段名、字段类型、约束)
alter table 表名 change 原名 新名 类型 约束;
alter table students change birthday birth datetime not null;#查看创表SQL语句
show create table 表名;
#查看创库SQL语句
show create database 数据库名;#删除表
drop table 表名;#查询数据-查询所有列
select * from 表名;
#查询数据-查询指定列
select 列1,列2,... from 表名;#插入数据-全列多行插入
insert into 表名 values(...),(...)...;
insert into students values(0, '张飞', 55, 1.75, '男'),(0, '关羽', 58, 1.85, '男');
#插入数据-部分列多行插入
insert into 表名(列1,...) values(值1,...),(值1,...)...;
insert into students(name, height) values('刘备', 1.75),('曹操', 1.6);#修改数据
update 表名 set 列1=值1,列2=值2... where 条件
update students set age = 18, gender = '女' where id = 6;#删除数据-物理删除
delete from 表名 where 条件
#删除数据-逻辑删除(添加删除表示字段，0表示未删除 1表示删除)
alter table students add isdelete bit default 0;
update students set isdelete = 1 where id = 8;#去除重复数据行(distinct)
select distinct 列1,... from 表名;#排序查询(order by关键字默认asc从小到大排列，即升序,desc从大到小排序，即降序)
select * from students  order by age desc,height desc;#分页查询
select * from 表名 limit start,count
select * from students where gender=1 limit 0,3;#模糊查询
like % _
like是模糊查询关键字
%表示任意多个任意字符
_表示一个任意字符
# 查询姓黄的学生:
select * from students where name like '黄%';
# 查询姓黄并且“名”是一个字的学生:
select * from students where name like '黄_';
# 查询姓黄或叫靖的学生:
select * from students where name like '黄%' or name like '%靖';#范围查询
# between .. and .. 表示在一个连续的范围内查询
select * from students where id between 3 and 8;
# in 表示在一个非连续的范围内查询
select * from students where id in (1,3,8);#空判断查询
# 判断为空使用: is null
select * from students where height is null;# 判断非空使用: is not null
select * from students where height is not null;#分组查询
基本的语法格式如下：
GROUP BY 列名 [HAVING 条件表达式] [WITH ROLLUP]
列名: 是指按照指定字段的值进行分组。
HAVING 条件表达式: 用来过滤分组后的数据。
WITH ROLLUP：在所有记录的最后加上一条记录，显示select查询时聚合函数的统计结果
# group by + having使用
select gender,count(*) from students group by gender having count(*)>2;
# group by + with rollup使用
select gender,group_concat(age) from students group by gender with rollup;

数据类型

#常用数据类型如下:
整数：int，bit
小数：decimal
字符串：varchar,char
日期时间: date, time, datetime
枚举类型(enum)#数据类型说明:
decimal表示浮点数，如 decimal(5, 2) 表示共存5位数，小数占 2 位.
char表示固定长度的字符串，如char(3)，如果填充'ab'时会补一个空格为'ab '，3表示字符数
varchar表示可变长度的字符串，如varchar(3)，填充'ab'时就会存储'ab'，3表示字符数
对于图片、音频、视频等文件，不存储在数据库中，而是上传到某个服务器上，然后在表中存储这个文件的保存路径.
字符串 text 表示存储大文本，当字符大于4000时推荐使用, 比如技术博客.
常见的约束:
主键约束 primary key
非空约束 not null
惟一约束 unique
默认约束 default
外键约束 foreign key

运算符

#比较运算符
# 等于: =
select * from students where id = 1;
# 大于: >
select * from students where id > 2;
# 大于等于: >=
select * from students where id >= 3;
# 小于: <
select * from students where id < 4;
# 小于等于: <=
select * from students where id <= 5;
# 不等于: != 或 <>
select * from students where id != 6;
select * from students where id <> 6;#逻辑运算符
# and
select * from students where id > 3 and gender=0;
# or
select * from students where id < 4 or is_delete=0;
# not
select * from students where not (age >= 10 and age <= 15);

常用集合函数 

count(col): 表示求指定列的总行数 max(col): 表示求指定列的最大值 min(col): 表示求指定列的最小值 sum(col): 表示求指定列的和 avg(col): 表示求指定列的平均值# 总行数
select count(*) from students;
# 最大值
select max(id) from students where gender = 2;
# 平均值(ifnull函数:判断指定字段值是否为null，如果为空使用自己提供的值)
select avg(ifnull(height,0)) from students where gender = 1;

外键sql语句

#外键约束不一定要链接到另一个表中的主键约束。 外键还可以定义为引用另一个表中 UNIQUE 约束的列。# 对于已经存在的字段添加外键约束,为cls_id字段添加外键约束
alter table students add foreign key(cls_id) references classes(id);
# 在创建数据表时设置外键约束
-- 创建学校表
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)
);# 删除外键约束
-- 需要先获取外键约束名称,该名称系统会自动生成,可以通过查看表创建语句来获取名称
show create table teacher;
-- 获取名称之后就可以根据名称来删除外键约束
alter table teacher drop foreign key 外键名;

pysql>mysql使用

#pysql>mysql使用
安装:打开终端,复制下文并回车： pip install -i https://pypi.tuna.tsinghua.edu.cn/simple pysql>mysql
事务：就是用户定义的一系列执行SQL语句的操作, 这些操作要么完全地执行，要么完全地都不执行， 它是一个不可分割的工作执行单元。pysql>mysql就是使用事务。
sql>mysql命令行终端默认支持事务，但是每次语句执行都会自动提交事务。
set autocommit = 0 表示取消自动提交事务模式，需要手动执行commit完成事务的提交
开启事务:
sql>mysql begin;
提交事务:
将本地缓存文件中的数据提交到物理表中，完成数据的更新。
sql>mysql commit;
回滚事务:
放弃本地缓存文件中的缓存数据, 表示回到开始事务前的状态
sql>mysql rollback;
查询数据库数据模板
# 目的：获得变量result中的数据
import pysql>mysql
conn = pysql>mysql.connect(host='localhost', port=3306, user='root', password='sql>mysql',database='python', charset='utf8')# 获取游标对象
cursor = conn.cursor()
sql = "select * from students;"
row_count = cursor.execute(sql)
print("SQL 语句执行影响的行数", row_count)
# 获取查询一条结果
# result = cursor.fetchone()
# 获取查询结果集
result = cursor.fetchall()
# 关闭游标
cursor.close()
# 关闭连接
conn.close()
print(result)
修改数据
import pysql>mysql#创建连接对象(连接的是一个数据库，若要操作其他数据库，需要重新连接其他数据库)
参数host：连接的sql>mysql主机，如果本机是'localhost'
参数port：连接的sql>mysql主机的端口,默认是3306
参数user：连接的用户名
参数password：连接的密码
参数database：数据库的名称
参数charset：编码,使用utf8conn = pysql>mysql.connect(host='localhost', port=3306, user='root', password='sql>mysql',database='python', charset='utf8')#获取游标对象
cursor = conn.cursor()try:# 添加 SQL 语句# sql = "insert into students(name) values('刘璐'), ('王美丽');"# 删除 SQL语句# sql = "delete from students where id = 5;"# 修改 SQL 语句sql = "update students set name = '王铁蛋' where id = 6;"# 查询表中全部数据# sql = "select * from students"#执行 SQL 语句cursor.execute(sql)cursor.execute(sql)#提交数据到数据库conn.commit()
except Exception as e:print(e)#回滚数据， 即撤销刚刚的SQL语句操作conn.rollback()
finally：#关闭游标cursor.close()#关闭连接conn.close()
大量上传数据
用pysql>mysql占位符%s,不能用Python中%s占位符传参
import pysql>mysql
conn = pysql>mysql.connect(host='localhost', port=3306, user='root', password='sql>mysql',database='python', charset='utf8')
# 获取游标对象
cursor = conn.cursor()
sql = "insert into students(name, age, sex) values(%s, %s, %s);"
try:for line in data:# cursor.execute(sql, ['张三', 25, '男'])cursor.execute(sql, line)   # 元组或列表或字典传参conn.commit()
except Exception as e:# 回滚数据， 即撤销刚刚的SQL语句操作conn.rollback()
finally:# 关闭游标cursor.close()# 关闭连接conn.close()

防止sql注入

#sql注入
#添加 SQL 语句(不安全的sql注入)
sql = "select * from students name = '%s';" % "杨过' or 1 = 1 or '"
不安全的sql注入
cursor.execute(sql)
#防止sql注入
#添加 SQL 语句(防止sql注入，即用sql>mysql占位符%s,不能用Python中%s占位符传参)
sql = "select * from students name = %s;"
cursor.execute(sql, ("杨过",))