mysql基本操作

1. 表管理

创建表结构

create table tb2(
	id int not null auto_increment primary key, --不允许为空，自增列，主键（注意：一个表只能有一个自增列，一般为主键）
	name varchar(16) not null,
	email varchar(32) null,
	age int default 20  --默认值20
)default charset=utf8; 

观察表结构

desc tb1;

修改表

删除表或者清空表中的数据

---删除表
drop table tb1;
--清空表
delete from tb1;
--或者
truncate tb1;
--truncate 无法恢复，但速度快

修改列

--添加列
alter table tb2 add pwd varchar(32) not null;
--修改列，整体修改
alter table tb2 change pwd pwd varchar(32) default 'abc123';
--注意，这样会改为可以为空，所以使用change应当写全类型

2.常见类型

整型

注意取值范围，严格模式会导致报错

int
int unsigned
int(5)zerofill
tinyint
bigint

小数

decimal(m,d)，m代表有效位数，d代表小数位数
也有float和double，和python一样，但很少使用

字符串

char(长度)，定长字符串，最大255个字符，超出根据设置配置文件可以自动截断
varchar(长度)，变长字符串，最多容纳65535个字节（如果用utf8，则可以容纳65535/3）；按照实际数据长度存储，超出报错
text 65535个字符
mediumtext
longtext

时间

datetime  原样记录日期和时间
timestamp 时间戳，存储本条记录修改的时间

3.表数据的操作

插入新数据

insert into b1(name, bhd) values('dongdong','2021-11-12');
-- 对制定列插入一条数据
insert into b1(name, bhd) values('phebe','2011-1-12'),('mike','2010-12-08');
-- 插入多条

删除数据

delete from b1;
--删除所有记录
delete from b1 where name='phebe';
--加入限定条件，并可以使用逻辑运算 and、or 和 比较运算 >

修改数据

update b1 set name='liudong' where name='dongdong';
update b1 set name='Mike' where id=3;
insert into b1(name,sex) values('phebe','F');

alter table b1 add salary decimal(8,2);
update b1 set salary=3500.0;.
--这样会修改sex字段的所有数据
update b1 set salary=salary+500 where name="liudong";
update b1 set name=concat("Lian",name) where name='phebe';

查询数据

select * from b1;
select name,salary from b1;
select name,bhd as change_time from b1;

sql注入问题

不要使用python的字符串格式化，使用pymysql的execute方法

import pymysql

conn = pymysql.connect(host='192.168.31.90', port=3306, user='root', password='abc', charset='utf8', db='test02')
cursor = conn.cursor()

user = input("用户名：")
pwd = input("密码：")

# cursor.execute('select * from b1 where name="{}" and pwd="{}"'.format(user, pwd))
# 不要使用上面这种，会有sql注入风险
# cursor.execute('select * from b1 where name=%s and pwd=%s', [user, pwd])
# 使用这两种方法都可以
cursor.execute('select * from b1 where name=%(name)s and pwd=%(pwd)s', {'name': user, 'pwd': pwd})

rst = cursor.fetchone()
print(rst)
conn.commit()
cursor.close()
conn.close()

4. 练习

第一题

根据下面的要求创建表

列名	类型	备注
id	int	不为空 & 自增 & 主键
name	varchar(32)	不为空
password	varchar(64)	不为空
gender	char(1)	不为空，支持：男、女
email	varchar(64)	可以为空
amount	decimal(10,2)	不为空 & 默认值为 0
ctime	datetime	新增时的时间 提示：可基于datetime模块实现

create table admin(
	id int not null auto_increment primary key,
	name varchar(32) not null,
	password varchar(64) not null,
	gender char(1) not null,
	email varchar(64) null,
	amount decimal(10,2) not null default 0,
	ctime datetime
)default charset=utf8;

第二题

编写代码实现将 excel文件的内容录入到 MySQL 数据库中。
要求：自己创建一个自增列作为主键

import pymysql

conn = pymysql.connect(host='192.168.31.90', port=3306, user='root', password='abc', charset='utf8', db='test02')
cursor = conn.cursor()

# 创建表代码
# sql = '''
#     create table videos(
#         id int auto_increment primary key not null,
#         num int,
#         title varchar(100),
#         url varchar(200)
#         )default charset="utf8";
# '''
# cursor.execute(sql)
# conn.commit()





# 读取数据
# sql ='select * from videos'
# cursor.execute(sql)
# rst = cursor.fetchall()
# for data in rst:
#     print(data)

# 问题，出现在csv文件中的文本内容中的逗号导致截断错误
cursor.close()
conn.close()