mysql数据库python基础知识_python week09 Mysql 数据库基础知识

第一篇：初识数据库

注:

第二篇：库相关操作

一 系统数据库

information_schema： 虚拟库，不占用磁盘空间，存储的是数据库启动后的一些参数，如用户表信息、列信息、权限信息、字符信息等。

performance_schema： MySQL 5.5开始新增一个数据库：主要用于收集数据库服务器性能参数，记录处理查询请求时发生的各种事件、锁等现象 。

mysql：授权库，主要存储系统用户的权限信息。

test：MySQL数据库系统自动创建的测试数据库，值得注意的是Mysql5.7及以后移除了test库以及删除了空密码用户等安全措施。

二 创建数据库

1 语法(help create database)

CREATE DATABASE 数据库名 charset utf8;

2 数据库命名规则：

可以由字母、数字、下划线、＠、＃、＄

区分大小写

唯一性

不能使用关键字如 create select

不能单独使用数字

最长128位

三 数据库相关操作

查看数据库

show databases;

show create database db1;

select database();

选择数据库

USE 数据库名

删除数据库

DROP DATABASE 数据库名;

修改数据库

alter database db1 charset utf8;

第三篇：表相关操作

注:

表相当于文件，表中的一条记录就相当于文件的一行内容，不同的是，表中的一条记录有对应的标题，称为表的字段。

create table tbName (

列名称1　列类型　[列参数]　[not null default ],

....列名称2...

....

列名称N　列类型　[列参数]　[not null default ]

)engine myisam/innodb charset utf8/gbk

例子:

create table user (

id int auto_increment,

name varchar(20) not null default '',

age tinyint unsigned not null default 0,

index id (id)

)engine=innodb charset=utf8;

注:innodb是表引擎,也可以是myisam或其他,但最常用的是myisam和innodb,

charset 常用的有utf8,gbk;

创建表例子1

MariaDB [db1]> insert into t1 values

-> (1,'egon',18,'male'),

-> (2,'alex',81,'female')

-> ;

MariaDB [db1]> select * from t1;

+------+------+------+--------+

| id | name | age | sex |

+------+------+------+--------+

| 1 | egon | 18 | male |

| 2 | alex | 81 | female |

+------+------+------+--------+

MariaDB [db1]> insert into t1(id) values

-> (3),

-> (4);

MariaDB [db1]> select * from t1;

+------+------+------+--------+

| id | name | age | sex |

+------+------+------+--------+

| 1 | egon | 18 | male |

| 2 | alex | 81 | female |

| 3 | NULL | NULL | NULL |

| 4 | NULL | NULL | NULL |

+------+------+------+--------+

插入表例子2

desc 表名  #查看表结构

show create table 表名\G;  #查看创建表的语句

MariaDB [db1]> describe t1; #查看表结构，可简写为desc 表名

+-------+-----------------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+-----------------------+------+-----+---------+-------+

| id | int(11) | YES | | NULL | |

| name | varchar(50) | YES | | NULL | |

| sex | enum('male','female') | YES | | NULL | |

| age | int(3) | YES | | NULL | |

+-------+-----------------------+------+-----+---------+-------+

MariaDB [db1]> show create table t1\G; #查看表详细结构，可加\G

查看表结构例子

注:

注:

语法：

1. 修改表名

ALTER TABLE 表名

RENAME 新表名;

2. 增加字段

ALTER TABLE 表名

ADD 字段名 数据类型 [完整性约束条件…],

ADD 字段名 数据类型 [完整性约束条件…];

ALTER TABLE 表名

ADD 字段名 数据类型 [完整性约束条件…] FIRST;

ALTER TABLE 表名

ADD 字段名 数据类型 [完整性约束条件…] AFTER 字段名;

3. 删除字段

ALTER TABLE 表名

DROP 字段名;

4. 修改字段

ALTER TABLE 表名

MODIFY 字段名 数据类型 [完整性约束条件…];

ALTER TABLE 表名

CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];

ALTER TABLE 表名

CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];

示例：

1. 修改存储引擎

mysql> alter table service

-> engine=innodb;

2. 添加字段

mysql> alter table student10

-> add name varchar(20) not null,

-> add age int(3) not null default 22;

mysql> alter table student10

-> add stu_num varchar(10) not null after name; //添加name字段之后

mysql> alter table student10

-> add sex enum('male','female') default 'male' first; //添加到最前面

3. 删除字段

mysql> alter table student10

-> drop sex;

mysql> alter table service

-> drop mac;

4. 修改字段类型modify

mysql> alter table student10

-> modify age int(3);

mysql> alter table student10

-> modify id int(11) not null primary key auto_increment; //修改为主键

5. 增加约束(针对已有的主键增加auto_increment)

mysql> alter table student10 modify id int(11) not null primary key auto_increment;

ERROR 1068 (42000): Multiple primary key defined

mysql> alter table student10 modify id int(11) not null auto_increment;

Query OK, 0 rows affected (0.01 sec)

Records: 0 Duplicates: 0 Warnings: 0

6. 对已经存在的表增加复合主键

mysql> alter table service2

-> add primary key(host_ip,port);

7. 增加主键

mysql> alter table student1

-> modify name varchar(10) not null primary key;

8. 增加主键和自动增长

mysql> alter table student1

-> modify id int not null primary key auto_increment;

9. 删除主键

a. 删除自增约束

mysql> alter table student10 modify id int(11) not null;

b. 删除主键

mysql> alter table student10

-> drop primary key;

alter table操作表常用示例

复制表结构＋记录 (key不会复制: 主键、外键和索引)

mysql> create table new_service select * from service;

只复制表结构

mysql> select * from service where 1=2; //条件为假，查不到任何记录

Empty set (0.00 sec)

mysql> create table new1_service select * from service where 1=2;

Query OK, 0 rows affected (0.00 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> create table t4 like employees;

删除表有三种方法：drop，delete，和truncate

DROP TABLE 表名;

1. delete是一种DML，它会放到rollback segment，事务提交之后才生效。如果有对应的触发器，那么执行时还会触发trigger。

truncate，drop是DDL，操作立即生效，也就意味着不能回滚。

2. truncate和delete不删除表的结构，drop将删除表的结构被依赖的约束，触发器，索引；依赖于表的存储过程/函数将被保留，但是变为invalid状态。

3. delete语句不影响表所占用的extent，high watermark不动。drop语句将最大限度释放该表所占的空间。truncate语句缺省情况下将空间释放到最小个extent，除非使用reuse storage，那种情况下下truncate将空间恢复到最大个extent。

4. 速度上，drop>truncate>delete

5. 安全性，drop和druncate都不能回滚，因此慎用。

6. 使用上，想删部分数据行，用带where子句的delete命令。想删除表，用drop。想保留表而将所有数据删除，并且和事

第四篇：记录相关操作

注:

第五篇：数据备份、pymysql模块

注:

第六篇：视图、触发器、事务、存储过程、函数

注:

第七篇：ORM框架SQLAlchemy

注:

第八篇：索引原理与慢查询优化

注