MySQL

概述

MySQL分为解析指令和文件操作，这里我们主要学习解析指令部分让其对我们想要执行的文件操作

其本质上就是一个软件

开启：net start mysql

连接服务：mysql -u root -p

密码是666666

停止服务：net stop mysql

数据库管理

1.首先是查看数据库

1
show databases #这个会展示date目录下的文件
1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> show databases
    -> ;	#这个是提示命令，就是会告诉你你的指令缺了什么，这里是缺封号
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

mysql>

2.创建数据库文件，通常情况下我们的文件都是utf-8编码，所以我们要告诉数据库这是utf-8

1
create database 数据库名字 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> create database ooaye DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected, 2 warnings (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| ooaye              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql>

3.删除数据库

1
drop database 数据库名字
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> drop database ooaye;
Query OK, 0 rows affected (0.02 sec)

mysql> show databases
    -> ;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql>

4.进入数据库

1
2
use 数据库名字;
show tables;	#展示当前数据库的所有表
1
2
3
4
5
6
7
8
9
10
mysql> create database ooaye DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected, 2 warnings (0.01 sec)

mysql> use ooaye;
Database changed
mysql> show tables
    -> ;
Empty set (0.00 sec)

mysql>

数据库的表管理

1.进入数据库

1
2
use 数据库名称

2.创建表

1
2
3
4
5
6
7
8
9
create table tb(
	列名称 类型 null,	#指可以为空
    列名称 类型 not null,	#不为空
    列名称 类型 dafault 3,	#默认值为3
    列名称 类型 primary key,	#不为空且不重复
    列名称 类型 auto_increment primary key,	#内部自增，比如前面是1，接下来就是2...
    。。。
)default charset=utf8;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> create table tb(id int,name varchar(16),age int)default charset=utf8;
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> show tables
    -> ;
+-----------------+
| Tables_in_ooaye |
+-----------------+
| tb              |
+-----------------+
1 row in set (0.00 sec)

mysql>

3.展示表

1
2
desc 表名称

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> desc tb
    -> ;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(16) | YES  |     | NULL    |       |
| age   | int         | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql>

4.删除表

1
2
drop table 表名称

5.数据类型

通常情况下数据类型都是有符号(signed)，如果想要无符号的，可以写id int unsigned

整形：tinyint,bigint,smallint,int...

浮点型：float,double,decimal(m,n)

这里着重讲下decimal(m,n)：指整数个数为不超过m，小数个数为不超过n(m<=65,n<=30)

1
2
3
4
create table tb(
	salary decimal(8,2)	#保留两位小数
)default charset=utf8;

字符型：char(m)，varchar((m)，text(特点是存储长度非常大，可以用来写文章),mediumtext(更多)，longtext

char(11)指的是输入的字符长度必须为11，char(m)，m<=255，速度快

varchar(11)指的是最多可以容纳11，节省空间

时间型：datetime,date

datetime:(YYYY-MM-DD HH:MM:SS)(2026-12-12 13:25:21)

date:(YYYY-MM-DD)

6.插入数据

1
2
insert into tb(name,age) values('bili',18)('silly',20);	#插入两行数据

7.查看数据

1
2
select * from 表名

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
mysql> create table tb(id int auto_increment primary key,name varchar(16),age int)default charset=utf8;
Query OK, 0 rows affected, 1 warning (0.02 sec)
mysql> show tables
    -> ;
+-----------------+
| Tables_in_ooaye |
+-----------------+
| tb              |
+-----------------+
1 row in set (0.00 sec)

mysql> desc tb;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int         | NO   | PRI | NULL    | auto_increment |
| name  | varchar(16) | YES  |     | NULL    |                |
| age   | int         | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> insert into tb(name,age) values('bili',18),('lily',20),('bob',16);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from tb
    -> ;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | bili |   18 |
|  2 | lily |   20 |
|  3 | bob  |   16 |
+----+------+------+
3 rows in set (0.00 sec)

mysql>

数据管理

1.新增数据

1
insert into 表名(列名称，列名称) values(值,值),(值,值);

2.删除数据

1
2
delete from 表名
delete from 表名 where 条件
1
2
3
delete from tb;
delete from tb where id=3;
delete from tb where id in (3,5);	#指的是把id=3和5的删去，没有删除4

3.修改数据

1
2
update 表名 set 列=值;
update 表名 set 列=值 where 条件;	
1
2
update tb set age=100;
update 表名 set age=age+4 where id>5;

4.查询数据

1
2
select * from 表名;	#查找全部
select 列名称,列名称 from 表名 where 条件;
1
2
select * from tb;	#查找全部
select id,name from tb where id>5;

案例：员工管理

1.创建数据库

mysql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
mysql> show databases
    -> ;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| ooaye              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.01 sec)

mysql> create database unicom DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected, 2 warnings (0.01 sec)

mysql> show databases
    -> ;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| ooaye              |
| performance_schema |
| sys                |
| unicom             |
+--------------------+
6 rows in set (0.00 sec)

mysql> use unicom;
Database changed
mysql> show tables
    -> ;
Empty set (0.01 sec)

mysql> create table admin(
    ->     id int auto_increment primary key,
    ->     username varchar(16) not null,
    ->     passward char(7) not null,
    ->     mobile char(5) not null
    -> ) default charset=utf8;
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> show tables
    -> ;
+------------------+
| Tables_in_unicom |
+------------------+
| admin            |
+------------------+
1 row in set (0.00 sec)

mysql> desc admin;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int         | NO   | PRI | NULL    | auto_increment |
| username | varchar(16) | NO   |     | NULL    |                |
| passward | char(7)     | NO   |     | NULL    |                |
| mobile   | char(5)     | NO   |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

mysql> SHOW VARIABLES LIKE 'port';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 3306  |
+---------------+-------+
1 row in set, 1 warning (0.02 sec)

python操作

用python代码连接上MySQL并发送指令

但要安装依赖pip install pymysql

1
2
3
4
5
6
7
8
9
10
11
import pymysql
#1.连接mysql的unicom库
conn=pymysql.connect(host='127.0.0.1',port=3306,user='root',passwd='666666',charset='utf8',db='unicom')
cursor=conn.cursor(cursor=pymysql.cursors.DictCursor)
#2.发送指令
cursor.execute("insert into admin(username,passward,mobile) values('lion','wq12111','12345')")
#3.确认指令
conn.commit()
#4.关闭
cursor.close()
conn.close()

执行完后查看

1
2
3
4
5
6
7
8
mysql> select * from admin
    -> ;
+----+----------+----------+--------+
| id | username | passward | mobile |
+----+----------+----------+--------+
|  1 | lion     | wq12111  | 12345  |
+----+----------+----------+--------+
1 row in set (0.00 sec)

但是这样子写每次都要打这么长好麻烦，所以修改一下pycharm代码，但是不要直接带变量，可能会发生mysql注入

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
import pymysql
#1.连接mysql的unicom库
conn=pymysql.connect(host='127.0.0.1',port=3306,user='root',passwd='666666',charset='utf8',db='unicom')
cursor=conn.cursor(cursor=pymysql.cursors.DictCursor)
#2.发送指令
sql="insert into admin(username,passward,mobile) values(%s,%s,%s)"
cursor.execute(sql,["李白","2312345","23412"])
#或者是当作字典发送
sql="insert into admin(username,passward,mobile) values(%(n1)s,%(n2)s,%(n3)s)"
cursor.execute(sql,{"n1": "杜甫","n2": "231aacv","n3": "23488"})
#3.确认指令
conn.commit()
#4.关闭
cursor.close()
conn.close()
1
2
3
4
5
6
7
8
9
10
11
12
mysql> select * from admin
    -> ;
+----+----------+----------+--------+
| id | username | passward | mobile |
+----+----------+----------+--------+
|  1 | lion     | wq12111  | 12345  |
|  2 | lion     | wq12111  | 12345  |
|  3 | 李白     | 2312345  | 23412  |
+----+----------+----------+--------+
3 rows in set (0.00 sec)

mysql>

那我们是不是可以直接在python页面做动态输入

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
import pymysql
while True:
    user=input("Enter your username: ")
    pwd=input("Enter your password: ")
    phone=input("Enter your phone number: ")
#1.连接mysql的unicom库
    conn=pymysql.connect(host='127.0.0.1',port=3306,user='root',passwd='666666',charset='utf8',db='unicom')
    cursor=conn.cursor(cursor=pymysql.cursors.DictCursor)
#2.发送指令
    sql="insert into admin(username,passward,mobile) values(%s,%s,%s)"
    cursor.execute(sql,[user,pwd,phone])
#3.确认指令
    conn.commit()
#4.关闭
    cursor.close()
    conn.close()

2.查询数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
import pymysql

#1.连接mysql的unicom库
conn=pymysql.connect(host='127.0.0.1',port=3306,user='root',passwd='666666',charset='utf8',db='unicom')
cursor=conn.cursor(cursor=pymysql.cursors.DictCursor)
#2.发送指令
sql="select * from admin where id>%s"
cursor.execute(sql,[2])
#3.获取所有符合的数据
date=cursor.fetchall()
for i in date:
    print(i)
#3.获取第一个符合条件的数据，得到的是以字典形式
res=cursor.fetchone()
print(res)
#4.关闭
cursor.close()
conn.close()
1
2
3
4
5
6
C:\Users\shizihang\Desktop\学习\PythonProject1\.venv\Scripts\python.exe C:\Users\shizihang\Desktop\学习\PythonProject1\查询数据.py 
{'id': 3, 'username': '李白', 'passward': '2312345', 'mobile': '23412'}
{'id': 4, 'username': '杜甫', 'passward': '231aacv', 'mobile': '23488'}
{'id': 5, 'username': 'lili', 'passward': '1230987', 'mobile': '12567'}

进程已结束，退出代码为 0

3.删除数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
import pymysql

#1.连接mysql的unicom库
conn=pymysql.connect(host='127.0.0.1',port=3306,user='root',passwd='666666',charset='utf8',db='unicom')
cursor=conn.cursor(cursor=pymysql.cursors.DictCursor)
#2.发送指令
sql="delete from admin where id=%s"
cursor.execute(sql,[2])
conn.commit()
#3.获取所有符合的数据

#4.关闭
cursor.close()
conn.close()

4.修改数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
import pymysql

#1.连接mysql的unicom库
conn=pymysql.connect(host='127.0.0.1',port=3306,user='root',passwd='666666',charset='utf8',db='unicom')
cursor=conn.cursor(cursor=pymysql.cursors.DictCursor)
#2.发送指令
sql="update admin set mobile=%s where id=%s"
cursor.execute(sql,['55555',3])
conn.commit()
#3.获取所有符合的数据

#4.关闭
cursor.close()
conn.close()

5.总结

增删改一定要有conn.commit()

查询不强制要求conn.commit(),但是要记得执行fetchall/fetchone

sql语句不要用python的字符串格式拼接，不然有sql注入，一定要用execute+参数

1
cursor.execute(".%s........%s",["XXXX","XXX"])