Mysql笔记

常用sql操作:

1.创建数据库 create database db1;
2.使用数据库use db1;
3.创建表结构:
create table student (id int auto_increment primary key,name varchar(20),age int,sex char(1));
4.查看数据库中数据表: show tables;
5.删除数据库 drop database db1;
用户信息sql:
6.退出数据库交互环境
exit;
7.增加用户:
grant select,insert,update,delete on db1.* to hdx@”%” identified by “lhn115023909”;
8.删除用户: drop user hdx@”%”;
9.显示用户: select user from mysql.user;
10.修改密码:
grant select,insert,update,delete on db1.* to hdx@"%" identified by "lhn115023808”;
表信息sql:
1.显示表的定义:desc student;
2.清空表中所有数据: delete from student;
3.删除表结构:drop table student;
4.查看数据库中数据表:show tables;
5.修改表名:alter table student rename to person;
6.添加列:alter table student add addr varchar(30);
7.删除列:alter table student drop addr;
insert语句 # 关键字不区分大小写 添加数据到数据表里
insert into table_name(列名1,列名2,…) values (值1,值2,…)
如果insert语句中的列名序列与表定义中的位置相同,可以省略不写
比如student列名为:id,name,age,sex
insert into student (id,name,age,sex) values (6,‘nannan’,23,‘女’);
insert into student (name,age,sex) values (’tom’,22,’男’);
insert into student values (4,’tom’,22,’男’); # 简写
插入数据的时候列名要和值的顺序对应
如果不指明某列的值且在表定义的时候没有指定默认值,则数据库将其设置为默认值Null
insert into student (2,‘ben’,28,); # 性别为空,默认新插入的记录中该列被置为Null
delete语句: # 删除数据表中已有的行
delete from table_name where 条件表达式
比如:delete from student where id = 2
delete from student where name like ‘张%’ and (id<30 or id >20)
delete from student where name like ‘张%’ and id<30 or id >20
delete from student where id between 20 and 30
delete from student where name in (‘ben’,’tom’,’haha’)
delete from student where age >=30
update语句 更新修改表中已有的数据
update table_name set 列名1 = 新值,列名2 = 新值 where 条件表达式
修改表中已有的数据:
update student set name = ‘ben’,age = 22 where id = 32
#把所有学生的年龄改为18
update student set age = 18
update student set age = age + 1 , name = ‘tom’ where name is null
update student set age = 22 where name is like ‘何%’ 将以何 名字开头的年龄设置成22
update student set name = ‘ben’ where age between 18 and 28 18-28之间
select语句 查询数据
1.查询表student中所有记录的所有列
select * from student 查询表里所有数据
2.查询所有学生名字,并且去掉重复的名字
select distinct name from student
select id,name,age,sex from student where name like ‘ben%’;
select id,name,age from student where age between 18 and 26;
聚合函数:
count:统计记录个数 select count(age) from student;
avg:计算某列的平均值 select avg(age) from student;
max:求某列的最大值 select max(age) from student;
min:求某列的最小值 select min(age) from student;
sum:求和 select sum(age) from student;
group_concat:连接某列所有值 select group_concat(age) from student;
first:返回某列的第一个值
last:返回某列的最后一个值
var:计算某列方差
分组
group by 用于对数据进行分组以便于汇总计算,having是group by的可选项,用于对汇总结果
进行筛选.汇总计算是指统计记录的个数,计算某列的平均值等
select age,count() from student group by age having count() > 0; group by后面为分组的条件
select age,count(*)from student group by age having age>=20;
order by 用于指定返回结果的记录按某个或某几列的大小的排序
select * from student order by age desc; 从大到小
select * from student order by age asc; 从小到大
select * from student order by id desc;
pymysql python的第三方模块
#pymysql定义
import pymysql
try:
# 连接数据库
conn = pymysql.connect(host =‘127.0.0.1’,port=3306,user=‘root’,password=‘123456’,db=‘db1’,charset=‘utf8’)
cursor=conn.cursor() #获取结果集
row = cursor.execute(‘select * from student’) #执行sql展示了表里的行数据
print(row)
#cursor.scroll(1,‘absolute’) #移动结果集的游标 absolute为绝对位置
cursor.scroll(1,‘relative’) #相对移动
row_1 = cursor.fetchone() #取一行数据
print(row_1)
row_2 = cursor.fetchone() # 取一行数据
print(row_2)
conn.commit() #连接
cursor.close() #关闭结果集 要关闭资源
conn.close() #关闭数据库连接
except Exception as e:
print(e)

#事务管理:提交操作,回滚操作: rollback()
import pymysql
try:
conn=pymysql.connect(host =‘127.0.0.1’,port=3306,user=‘root’,password=‘123456’,db=‘db1’,charset=‘utf8’)
cursor = conn.cursor()
sql = ‘insert into student(id,name,age,sex,addr) values (5,“jim”,28,“男”,“南京”)’ #对数据库进行添加用户操作
cursor.execute(sql) 执行sql展示了表里的行数据
conn.commit() #数据库连接
except Exception as e:
conn.rollback() #回滚操作,如果出现错误回到之前状态
print(‘执行回滚操作’)
print(e)
cursor.close()
conn.close()

如何批量获取数据
fetchone 取一位
fetchall 所有
fetchmany 设定前几位
import pymysql
try:
conn=pymysql.connect(host =‘127.0.0.1’,port=3306,user=‘root’,password=‘123456’,db=‘db1’,charset=‘utf8’)
cursor = conn.cursor()
sql = ‘select * from student’
cursor.execute(sql)
#rows = cursor.fetchall() #获取结果集里所有的数据
rows = cursor.fetchmany(3) #获取结果集里前3项
for row in rows:
print(row)
conn.commit()
except Exception as e:
conn.rollback() #回滚操作,如果出现错误回到之前状态
print(‘执行回滚操作’)
print(e)
cursor.close()
conn.close()

更新数据 update
import pymysql
try:
conn=pymysql.connect(host =‘127.0.0.1’,port=3306,user=‘root’,password=‘123456’,db=‘db1’,charset=‘utf8’)
cursor = conn.cursor()
sql = ‘update student set age = age+%s where age>%s’
sql_1 =‘select * from student’
cursor.execute(sql,(1,28)) #两个参数,第一个sql语句,第二个是传给sql语句两个占位符的值
rows = cursor.fetchall() #获取结果集里所有的数据
for i in rows:
print(i)
conn.commit()
except Exception as e:
conn.rollback() #回滚操作,如果出现错误回到之前状态
print(‘执行回滚操作’)
print(e)
cursor.close()
conn.close()

删除数据 delete
import pymysql
try:
conn=pymysql.connect(host =‘127.0.0.1’,port=3306,user=‘root’,password=‘123456’,db=‘db1’,charset=‘utf8’)
cursor = conn.cursor()
sql = ‘select * from student’
sql_1 = ‘delete from student where id=%s’
cursor.execute(sql_1,(5))
cursor.execute(sql)#两个参数,第一个sql语句,第二个是传给sql语句两个占位符的值
rows = cursor.fetchall()
for i in rows:
print(i)
conn.commit()
except Exception as e:
conn.rollback() #回滚操作,如果出现错误回到之前状态
print(‘执行回滚操作’)
print(e)
cursor.close()
conn.close()