操作表
列约束
auto_increment : 自增 1
primary key : 主键索引，加快查询速度, 列的值不能重复
NOT NULL 标识该字段不能为空
DEFAULT 为该字段设置默认
例子: (推荐)
create table t3(
id int unsigned auto_increment primary key,
name char(10) not null default 'xxx',
age int not null default 0
)charset=utf8;
mysql> insert into t3 (age) values (10);
Query OK, 1 row affected (0.05 sec)
mysql> select * from t3;
+----+------+-----+
| id | name | age |
+----+------+-----+
| 1 | xxx | 10 |
+----+------+-----+
列类型
create table 表名(
字段名 列类型 unsigned [可选的参数], ### 记住加逗号
字段名 列类型 [可选的参数], ### 记住加逗号
字段名 列类型 [可选的参数] ### 最后一行不加逗号
.....
)charset=utf8; #### 后面加分号
- 数字
- 整型
tinyint
smallint
int （************************） 推荐使用
mediumint
bigint
a.整数类型
b.取值范围
c.unsigned 加上代表不能取负数 只适用于整型
应用场景：
根据公司业务的场景，来选取合适的类型
- 浮点型 （***********）
create table t5(
id int auto_increment primary key,
salary decimal(16,10),
num float
)charset=utf8;
float: 不一定精确
decimal: 非常的精确的数字 (5000.23) decimal(6, 2) m是数字总个数（负号 不算），d是小数点后个数。
正好 10 位：
mysql> insert into t5 (salary, num) values (500023.2312345678, 5000.2374837284783274832);
Query OK, 1 row affected (0.04 sec)
mysql> select * from t5;
+----+-------------------+---------+
| id | salary | num |
+----+-------------------+---------+
| 1 | 500023.2312345678 | 5000.24 |
+----+-------------------+---------+
1 row in set (0.00 sec)
少于10位：(会自动使用0补齐)
mysql> insert into t5 (salary, num) values (500023.231234567, 5000.2374837284783274832);
Query OK, 1 row affected (0.04 sec)
mysql> select * from t5;
+----+-------------------+---------+
| id | salary | num |
+----+-------------------+---------+
| 1 | 500023.2312345678 | 5000.24 |
| 2 | 500023.2312345670 | 5000.24 |
+----+-------------------+---------+
多于10位：（在第十位进行四舍五入）
mysql> insert into t5 (salary, num) values (500023.23123456789, 5000.2374837284783274832);
Query OK, 1 row affected, 1 warning (0.03 sec)
mysql> select * from t5;
+----+-------------------+---------+
| id | salary | num |
+----+-------------------+---------+
| 1 | 500023.2312345678 | 5000.24 |
| 2 | 500023.2312345670 | 5000.24 |
| 3 | 500023.2312345679 | 5000.24 |
+----+-------------------+---------+
- 字符串
- char(长度) ： 定长
create table t6(
id unsigned int auto_increment primary key,
name char(10) not null default 'xxx',
)charset=utf8;
- varchar(长度)：变长
create table t6(
id int auto_increment primary key,
name varchar(10) not null default 'xxx'
)charset=utf8;
mysql> insert into t6 (name) values ('hello');
Query OK, 1 row affected (0.03 sec)
mysql> select * from t6;
+----+-------+
| id | name |
+----+-------+
| 1 | hello |
+----+-------+
1 row in set (0.00 sec)
mysql> insert into t6 (name) values ('hellodbsabdsjabjdsa');
ERROR 1406 (22001): Data too long for column 'name' at row 1
区别：
char: 定长， 无论插入的字符是多少个，永远固定占规定的长度
场景：
1. 身份证
2. 手机号 char(11)
3. md5加密之后的值，比如密码 等 char(32)
varchar: 变长， 根据插入的字符串的长度来计算所占的字节数，但是有一个字节是用 来保存字符串的大小的
注意：如果， 不能确定插入的数据的大小， 一般建议使用 varchar(255)
- 时间日期类型
YEAR
YYYY（1901/2155）
DATE
YYYY-MM-DD（1000-01-01/9999-12-31）
TIME
HH:MM:SS（'-838:59:59'/'838:59:59'）
DATETIME (***************************)
YYYY-MM-DD HH:MM:SS（1000-01-01 00:00:00/9999-12-31 23:59:59 Y）
TIMESTAMP
YYYYMMDD HHMMSS（1970-01-01 00:00:00/2037 年某时）
例子：
create table t8(
d date,
t time,
dt datetime
);
mysql> insert into t8 values(now(),now(),now());
Query OK, 1 row affected, 1 warning (0.08 sec)
mysql> select * from t8;
+------------+----------+---------------------+
| d | t | dt |
+------------+----------+---------------------+
| 2019-10-29 | 10:49:51 | 2019-10-29 10:49:51 |
+------------+----------+---------------------+
1 row in set (0.00 sec)
insert into t8 values(now(),now(),now());
- 枚举
列出所有的选项
create table t9 (
id int auto_increment primary key,
gender enum('male','female')
)charset utf8;
mysql> insert into t9 (gender) values ('male');
Query OK, 1 row affected (0.04 sec)
mysql> insert into t9 (gender) values ('female');
Query OK, 1 row affected (0.03 sec)
mysql> insert into t9 (gender) values ('dshajjdsja');
增(create)
create table 表名(
字段名 列类型 [可选的参数], ### 记住加逗号
字段名 列类型 [可选的参数], ### 记住加逗号
字段名 列类型 [可选的参数] ### 最后一行不加逗号
.....
)charset=utf8; #### 后面加分号
改(alter)
1. 修改表名
ALTER TABLE 旧表名 RENAME 新表名;
mysql> alter table t8 rename t88;
Query OK, 0 rows affected (0.19 sec)
2. 增加字段
ALTER TABLE 表名
ADD 字段名 列类型 [可选的参数],
ADD 字段名 列类型 [可选的参数];
mysql> alter table t88 add name varchar(32) not null default '';
Query OK, 0 rows affected (0.82 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 注：添加的列永远是添加在最后一列之后
# 在第一行进行添加
ALTER TABLE 表名
ADD 字段名 列类型 [可选的参数] FIRST;
mysql> alter table t88 add name3 varchar(32) not null default '' first;
Query OK, 0 rows affected (0.83 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 在...之后进行添加
ALTER TABLE 表名
ADD 字段名 列类型 [可选的参数] AFTER 字段名;
mysql> alter table t88 add name4 varchar(32) not null default '' after d;
Query OK, 0 rows affected (0.68 sec)
Records: 0 Duplicates: 0 Warnings: 0
3. 删除字段
ALTER TABLE 表名 DROP 字段名;
mysql> alter table t88 drop name4;
Query OK, 0 rows affected (0.66 sec)
Records: 0 Duplicates: 0 Warnings: 0
4. 修改字段
ALTER TABLE 表名 MODIFY 字段名 数据类型 [完整性约束条件…];
mysql> alter table t88 modify name2 char(20);
Query OK, 1 row affected (0.88 sec)
Records: 1 Duplicates: 0 Warnings: 0
# 注：只修改数据类型，不修改字段名
# 对字段名，数据类型[约束条件]进行修改
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];
mysql> alter table t88 change name2 name22 varchar(32) not null default '';
Query OK, 1 row affected (0.82 sec)
Records: 1 Duplicates: 0 Warnings: 0
# 只写修旧表名会直接报错
mysql> alter table t88 change name22 name23;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
删(drop)
drop table 表名; #### 线上禁用
mysql> drop table t9;
Query OK, 0 rows affected (0.18 sec)
查(show)
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1 |
+----------------+
1 row in set (0.00 sec)
复制表结构(like)
like
mysql> create table t89 like t88;
Query OK, 0 rows affected (0.33 sec)
# 注：复制后序号会相差几个
操作表数据行
增
语法：
insert into 表名 (列1, 列2) values (值1,'值2');
例子：
insert into t1 (id, name) values (1, 'zekai');
insert into t1 (id, name) values (1, 'zekai2');
insert into t1 (id, name) values (1, 'zekai2'),(2, 'zekai3'),(3,'zekai4');
mysql> insert into t66 (name) select name from t6;
Query OK, 4 rows affected (0.09 sec)
Records: 4 Duplicates: 0 Warnings: 0
删
delete from 表名 where 条件;
mysql> delete from t5 where id=1;
mysql> delete from t5 where id>1;
mysql> delete from t5 where id>=1;
mysql> delete from t5 where id<1;
mysql> delete from t5 where id<=1;
mysql> delete from t5 where id>=1 and id<10;
Query OK, 1 row affected (0.06 sec)
delete from 表名; 删除表中所有的数据
mysql> insert into t5 (salary, num) values (500023.2312345679,5000.24);
Query OK, 1 row affected (0.08 sec)
mysql> select * from t5;
+----+-------------------+---------+
| id | salary | num |
+----+-------------------+---------+
| 4 | 500023.2312345679 | 5000.24 |
+----+-------------------+---------+
1 row in set (0.00 sec)
truncate 表名; #### 没有where条件的
mysql> truncate t5;
Query OK, 0 rows affected (0.25 sec)
mysql> select * from t5;
Empty set (0.00 sec)
mysql> insert into t5 (salary, num) values (500023.2312345679,5000.24);
Query OK, 1 row affected (0.06 sec)
mysql> select * from t5;
+----+-------------------+---------+
| id | salary | num |
+----+-------------------+---------+
| 1 | 500023.2312345679 | 5000.24 |
+----+-------------------+---------+
1 row in set (0.00 sec)
区别：
1. delete之后，插入数据从上一次主键自增加1开始， truncate则是从1开始
2. delete删除， 是一行一行的删除， truncate：全选删除 truncate删除的速度是高于delete的
改
update 表名 set 列名1=新值1,列名2=新值2 where 条件;
mysql> update t66 set name='xxxx' where id=30;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update t66 set name='xxxx' where id<30;
mysql> update t66 set name='xxxx' where id<=30;
mysql> update t66 set name='xxxx' where id>=30;
mysql> update t66 set name='xxxx' where id>30;
mysql> update t66 set name='xxxx' where id>20 and id<32;
mysql> update t66 set name='xxxx' where id>20 or name='zekai';
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0
查
# 语法：
select 列1, 列2 from 表名; （*代表查询所有的列）
select * from 表名; （*代表查询所有的列）
select * from t66 where id>30 and id<40;
select * from t66 where id>30;
select * from t66 where id<30;
select * from t66 where id<=30;
select * from t66 where id>=30;
select * from t66 where id!=30;
select * from t66 where id<>30;
mysql> select * from t1;
+------+-------+
| id | name |
+------+-------+
| 1 | zekai |
+------+-------+
1 row in set (0.00 sec)
between..and...: 取值范围是闭区间
select * from t66 where id between 30 and 40;
mysql> select * from t66 where id between 31 and 33;
+----+--------+
| id | name |
+----+--------+
| 31 | dsadsa |
| 32 | dsadsa |
| 33 | dsadsa |
+----+--------+
避免重复DISTINCT
mysql> select distinct name from t66;
+--------+
| name |
+--------+
| xxxx |
| hds |
| dsadsa |
+--------+
3 rows in set (0.00 sec)
通过四则运算查询 （不要用）
mysql> select name, age*10 from t3;
+------+--------+
| name | age*10 |
+------+--------+
| xxx | 100 |
+------+--------+
1 row in set (0.01 sec)
mysql> select name, age*10 as age from t3;
+------+-----+
| name | age |
+------+-----+
| xxx | 100 |
+------+-----+
1 row in set (0.02 sec)
in(80,90,100):
mysql> select * from t66 where id in (23,34,11);
+----+------+
| id | name |
+----+------+
| 11 | xxxx |
| 23 | hds |
+----+------+
2 rows in set (0.04 sec)
like : 模糊查询
以x开头：
mysql> select * from t66 where name like 'x%';
+----+------+
| id | name |
+----+------+
| 1 | xxxx |
| 2 | xxxx |
| 3 | xxxx |
| 4 | xxxx |
| 8 | xxxx |
| 9 | xxxx |
| 10 | xxxx |
| 11 | xxxx |
| 15 | xxxx |
| 16 | xxxx |
| 17 | xxxx |
| 18 | xxxx |
| 30 | xxxx |
+----+------+
13 rows in set (0.05 sec)
以x结尾：
mysql> select * from t66 where name like '%x';
+----+------+
| id | name |
+----+------+
| 1 | xxxx |
| 2 | xxxx |
| 3 | xxxx |
| 4 | xxxx |
| 8 | xxxx |
| 9 | xxxx |
| 10 | xxxx |
| 11 | xxxx |
| 15 | xxxx |
| 16 | xxxx |
| 17 | xxxx |
| 18 | xxxx |
| 30 | xxxx |
+----+------+
13 rows in set (0.00 sec)
包含x的：
mysql> select * from t66 where name like '%x%';
pymysql 操作数据库
from pymysql import *
def main():
# 创建connection连接
conn = connect(host='', port=3306, database='', user='',
password='', charset='utf8')
# 获取cursor对象
cs1 = conn.cursor()
# 执行sql语句
query = "update 表名 set 字段1 = 值1 where 字段2 = '{}'".format(值2)
cs1.execute(query)
# 提交之前的操作，如果之前已经执行多次的execute，那么就都进行提交
conn.commit()
# 关闭cursor对象
cs1.close()
# 关闭connection对象
conn.close()
if __name__ == '__main__':
main()