1.SQL语句:
1.1创建表格:
-- 注释
-- not null 非空约束,即改列数据不能为空
-- primery key 设置改字段为主键
-- auto_increment 主键自增(主键不会减)
create table stu(
id int(10) not null primary key auto_increment,
name varchar(10) not null,
age int(5) not null,
class varchar(20) not null
)
1.2删除表格:
drop table stu
1.3添加字段:
设置id为主键后,可无需添加id
insert into stu(name,age,class) values
("张三",21,"三班"),
("李四",22,"二班");
1.4删除字段:
-- 删除表内全部数据
delete from stu
-- 条件筛选删除数据
delete from stu where id = 2
1.5修改数据:
update stu set age = 24 where age = 21
-- 同时修改两条数据
update stu set age = 23,class = "五班" where age = 22
1.6查询数据:
取别名时as后面的别名最好用反引号,用双引号有时可能会报错
select * from stu
select name,age from stu
select name as "姓名",age as "年龄" from stu
-- 带条件查询
select * from stu where age > 20
select * from stu where age <> 20
select * from stu where age != 20
select * from stu where age between 20 and 25
select * from stu where age in (21,22)
select * from stu where age = 21 or age = 22
select * from stu where age = 21 and class = "三班"
-- 模糊查询
select * from stu where name like "李%"
select * from stu where name like "%李%"
1.7聚合函数:
- sum()
- avg()
- count()
- max()
- min()
聚合函数一般和group by字句搭配使用
规定,select后方所查询的凡是没有放在聚合函数内的字段都要同时放在group by后方
eg:
select class,gender,count(*) from stu group by class,gender
1.8having字句:
having字句一般用于对查询到的结果再进行筛选
-- 输出每个班级平均分大于85分的信息 select class,avg(score) from stu group by class having avg(score) > 85
1.9排序:
order by
-- 默认为升序
select * from stu order by score
-- 降序排序
select * from stu order by score desc
1.10limit:
-- 取前3条数据
select * from stu limit 3
-- 从第四条数据开始,取六条数据,此时取出的数据不包含第四条
select * from stu limit(4,6)
2.python连接数据库:
2.1安装pymysql
2.2创建连接
import pymysql
conn = pymysql.connect(
user="root",
password="123456",
host="localhost",
database="spider",
port=3306,
)
2.3创建游标
cursor = conn.cursor
2.4准备sql
sql = "insert into stu(name,age,class) values ('王华',21,'二班')"
2.5执行语句
cursor.execute(sql)
2.6提交事务
conn.commit()
什么是事务:事务是应用程序中一系列的严密操作,所有操作必须成功完成,否则在每个操作中所做的所有更改都会被撤销,也就是是事务具有原子性,一个事务中的一系列列操作要么全部成功,要么一个不做。事务的结束有两种,当事务中的所有步骤全部成功执行时,事务提交,如果其中一个步骤失败,将发生回滚操作
事务的四个特征(ACID):
🍎原子性
🍏一致性
🍊隔离性
🏠持续性
pycharm中python连接数据库后,默认将事务打开了,所以需提交事务
try:
conn = pymysql.connect(
user = "root",
password = "123456",
host = "localhost",
database = "spider"
port=3306,
)
cursor=pymysql.cursor()
cursor.execute(sql)
except Exception as e:
print (e)
conn.rollback()
finally:
if cursor:
cursor.close()
if conn:
conn.close()
2.7DictCursor
查询数据库信息时,可通过DictCursor返回字典数据类型
from pymysql.cursor import DictCursor def get_conn(): conn = pymysql.connect( user="root", password="123456", host="localhost", database="spider", unix_socket=None, port=3306, ) return conn def get_all(sql): try: conn=get_conn() cursor=conn.cursor(cursor=DictCursor) cursor.execute(sql) return cursor.fetchall() except Exception as e: print(e) finally: if cursor: cursor.close() if conn: conn.close()