python基础(十二)
一、 Mysql的介绍
1.1、 数据库介绍
数据库(Database)是按照数据结构来组织、存储和管理数据的仓库,每个数据库都有一个或多个不同的API用于创建,访问,管理,搜索和复制所保存的数据。我们也可以将数据存储在文件中,但是在文件中读写数据速度相对较慢。所以,现在我们使用关系型数据库管理系统(RDBMS)来存储和管理的大量数据量。所谓的关系型数据库,是建立的关系模型基础上的数据库,借助于集合代数等数学概念和方法来处理数据库中的数据。
RDBMS即关系数据库管理系统的特点:
1.数据以表格的形式出现
2.每行为各种记录名称
3.每列为记录名称所对应的数据域
4.许多的行和列组成一张表单
5.若干的表单组成database
6.下面是若干术语:
主键:主键是唯一的。一个数据表只能包含一个主键。你可以使用主键来查询数据。
外键:外键用于关联两个表。
复合键:复合键(组合键)将多个列作为一个索引键,一般用于复合索引。
索引:使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。
参照完整性:参照的完整性要求关系中的不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件,目的是保证数据的一致性。
MySQL支持大型数据库,支持5000万条记录的数据仓库,32位系统表文件可最大支持4GB,64位系统支持最大的表文件为8TB.
1.2、MySQL的常用管理命令
USE 数据库名:选择要操作的Mysql数据库,使用该命令后所有Mysql命令都只针对该数据库。
SHOW DATABASES:列出Mysql数据库管理系统的数据库列表。
SHOW TABLES: 显示制定数据库的所有表,使用该命令前需要使用use命令来选择要操作的数据库。
SHOW COLUMNS FROM 数据表:显示数据表的属性,属性类型,主键信息,是否为NULL,默认值等其他信息。
DESC 表名称:显示表结构
create database 数据库名称 charset 'utf-8':创建一个数据库使其支持中文
DROP DATABASE 数据库名称:删除数据库
SHOW INDEX FROM 数据表:显示数据表的详细索引信息,包括PRIMARY KEY (主键)
1.3、Mysql数据类型
1.数值类型
类型 | 大小 | 范围(有符号) | 范围(无符号) | 用途 |
---|---|---|---|---|
TINYINT | 1 字节 | (-128,127) | (0,255) | 小整数值 |
SMALLINT | 2 字节 | (-32 768,32 767) | (0,65 535) | 大整数值 |
MEDIUMINT | 3 字节 | (-8 388 608,8 388 607) | (0,16 777 215) | 大整数值 |
INT或INTEGER | 4 字节 | (-2 147 483 648,2 147 483 647) | (0,4 294 967 295) | 大整数值 |
BIGINT | 8 字节 | (-9 233 372 036 854 775 808,9 223 372 036 854 775 807) | (0,18 446 744 073 709 551 615) | 极大整数值 |
FLOAT | 4 字节 | (-3.402 823 466 E+38,1.175 494 351 E-38),0,(1.175 494 351 E-38,3.402 823 466 351 E+38) | 0,(1.175 494 351 E-38,3.402 823 466 E+38) | 单精度 浮点数值 |
DOUBLE | 8 字节 | (1.797 693 134 862 315 7 E+308,2.225 073 858 507 201 4 E-308),0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308) | 0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308) | 双精度 浮点数值 |
DECIMAL | 对DECIMAL(M,D) ,如果M>D,为M+2否则为D+2 | 依赖于M和D的值 | 依赖于M和D的值 | 小数值 |
2. 日期和时间类型
类型 | 大小 (字节) | 范围 | 格式 | 用途 |
---|---|---|---|---|
DATE | 3 | 1000-01-01/9999-12-31 | YYYY-MM-DD | 日期值 |
TIME | 3 | '-838:59:59'/'838:59:59' | HH:MM:SS | 时间值或持续时间 |
YEAR | 1 | 1901/2155 | YYYY | 年份值 |
DATETIME | 8 | 1000-01-01 00:00:00/9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值 |
TIMESTAMP | 4 | 1970-01-01 00:00:00/2037 年某时 | YYYYMMDD HHMMSS | 混合日期和时间值,时间戳 |
3.字符串
类型 | 大小 | 用途 |
---|---|---|
CHAR | 0-255字节 | 定长字符串 |
VARCHAR | 0-65535 字节 | 变长字符串 |
TINYBLOB | 0-255字节 | 不超过 255 个字符的二进制字符串 |
TINYTEXT | 0-255字节 | 短文本字符串 |
BLOB | 0-65 535字节 | 二进制形式的长文本数据 |
TEXT | 0-65 535字节 | 长文本数据 |
MEDIUMBLOB | 0-16 777 215字节 | 二进制形式的中等长度文本数据 |
MEDIUMTEXT | 0-16 777 215字节 | 中等长度文本数据 |
LONGBLOB | 0-4 294 967 295字节 | 二进制形式的极大文本数据 |
LONGTEXT | 0-4 294 967 295字节 | 极大文本数据 |
1.4、 事务
1. 定义
事务由单独单元的一个或多个SQL语句组成,在这个单元中,每个Mysql语句是相互依赖的。而整个单独单元作为一个不可分割的整体,如果单元中某条SQL语句一旦执行失败或产生错误,整个单元将会回滚。所有收到影响的数据将返回到数据开始以前的状态;如果单元中的所有SQL语句均执行成功,则事物被顺利执行。
2. 事务特点
在mysql中只有使用InnoDB数据库引擎的数据库或表才支持事务
事务处理可以用来维护数据库的完整性,保证成批的SQL语句要么全部执行,要么全部不执行
事务用来管理insert,update,delete语句
3. 事务四大特征
a. 事务的原子性:一组事务,要么成功,要么撤回。
b. 稳定性: 有非法数据(外键约束之类)事务撤回。
c. 隔离性: 事务独立运行。一个事务处理后的结果,影响了其他事务,那么其他事务会撤回。事务100%隔离,需要牺牲速度。
d. 可靠性:软、硬件崩溃后,InnoDB数据表驱动会利用日志文件重构修改。可靠性和高速度不可兼得,Innodb_flush_log_at_trx_commit选项,决定什么时候把事务保存在日志里。
1.5 索引
1. 定义
索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分),他么包含着数据表里所有记录的引用指针。更通俗的说,数据库索引好比是一本书前面的目录,能加快数据库的查询速度。上述sql语句,在没有是意淫的情况下,数据库会遍历全部200条数据后选择符合条件的;而有了相应的索引之后,数据库会直接在索引中查找符合条件的选项。如果我们把SQL语句换成"SELECT * FROM article WHERE id=2000000",那么是希望数据库按照顺序读取完200万行数据以后给你结果还是直接在索引中定位呢?上面的两个图片鲜明的用时对比已经给出答案(注:一般数据库默认都会为主键生成索引)。
2. 索引的分类
普通索引、唯一索引、主键索引、全文索引、单列索引、多列索引、组合索引
3. 常用索引命令
alter table 表名称 add index 索引名称 (column_list) ;#添加普通索引
alter table 表名称 add unique (column_list) ;#添加唯一索引
alter table 表名称 add primary key (column_list); #添加主键索引,要保证主键不为空
create index 索引名称 on 表名称 (column_list) ; #创建普通索引
create unique index 索引名称 on 表名称 (column_list); #创建唯一索引
drop index 索引名称 on 表名称 ; #删除索引
alter table 表名称 drop index 索引名称; #删除索引
alter table 表名称 drop primary key; #删除主键索引
SHOW INDEX FROM 表名称\G #显示索引信息
4.索引的弊端
首先,索引要占用磁盘空间。通常情况下,这个问题不是很突出。但是,如果你创建每一种可能组合的索引,索引文件体积的增长速度将远远超过数据文件。如果你有一个很大的的表,索引文件的大小可能达到操作系统允许的最大文件限制。第二,对于需要写入数据的操作,比如DELETE、UPDATE以及INSERT操作,索引会降低他们的速度。这是因为MYSQL不仅要把改动数据写入数据文件,而且它还要把这些改动写入索引文件。
二、 Mysql常用命令实操
MYSQL创建数据库:
创建一个student表实例:
mysql> create table student( -> stu_id INT NOT NULL AUTO_INCREMENT, -> name CHAR(32) NOT NULL, -> age INT NOT NULL, -> register_date DATE, -> PRIMARY KEY (stu_id) -> );
解析:
如果不想字段为NULL可以在创建表的时候设置字段的属性为NOT NULL,在操作数据库时如果输入该字段的数据为NULL,就会报错。
AUTO_INCREMENT定义列为自增的属性,一般用于主键,数值会自动加1.
PRIMARY KEY关键字用于定义列为主键。您可以使用多列来定义主键,列间以逗号分隔。
MYSQL插入数据:
插入数据实例:
mysql> insert into student (name,age,register_date) values ('chai shao',23,'2016-10-27'); Query OK, 1 row affected (0.00 sec) mysql> select * from student; +--------+-----------+-----+---------------+ | stu_id | name | age | register_date | +--------+-----------+-----+---------------+ | 1 | chai shao | 23 | 2016-10-27 | +--------+-----------+-----+---------------+ 1 row in set (0.00 sec)
MySQL查询数据
查询实例:
mysql> insert into student (name,age,register_date) values ('a',22,'2016-10-27'); Query OK, 1 row affected (0.00 sec) mysql> insert into student (name,age,register_date) values ('b',22,'2016-10-27'); Query OK, 1 row affected (0.00 sec) mysql> insert into student (name,age,register_date) values ('c',22,'2016-10-27'); Query OK, 1 row affected (0.01 sec) mysql> insert into student (name,age,register_date) values ('d',22,'2016-10-27'); Query OK, 1 row affected (0.00 sec) mysql> select * from student; #现在有五条数据 +--------+-----------+-----+---------------+ | stu_id | name | age | register_date | +--------+-----------+-----+---------------+ | 1 | chai shao | 23 | 2016-10-27 | | 2 | a | 22 | 2016-10-27 | | 3 | b | 22 | 2016-10-27 | | 4 | c | 22 | 2016-10-27 | | 5 | d | 22 | 2016-10-27 | +--------+-----------+-----+---------------+ 5 rows in set (0.00 sec) mysql> select * from student limit 3 offset 1; #从第二条数据开始显示3条数据 +--------+------+-----+---------------+ | stu_id | name | age | register_date | +--------+------+-----+---------------+ | 2 | a | 22 | 2016-10-27 | | 3 | b | 22 | 2016-10-27 | | 4 | c | 22 | 2016-10-27 | +--------+------+-----+---------------+ 3 rows in set (0.00 sec) mysql> select * from student limit 3,1; #从第三条数据开始查询,只显示一条数据 +--------+------+-----+---------------+ | stu_id | name | age | register_date | +--------+------+-----+---------------+ | 4 | c | 22 | 2016-10-27 | +--------+------+-----+---------------+ 1 row in set (0.00 sec)
解析:
查询语句中你可以使用一个或者多个表,表之间使用逗号(,)分割,并使用WHERE语句来设定查询条件。
SELECT命令可以读取一条或者多条记录。
可以使用星号(*)来代替其他字段,SELECT语句会返回表的所有字段数据
使用WHERE语句来包含任何条件
可以通过OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0.
可以使用LIMIT属性来设定返回的记录数。
MYSQL where 子句
语法:select n1,n2,....nn from table_name1,table_name2... [where condition1 [AND [OR]] condition2]
以下为操作符列表,可用于WHERE子句中
下表中实例假定A为10,B为20
操作符 | 描述 | 实例 |
---|---|---|
= | 等号,检测两个值是否相等,如果相等返回true | (A = B) 返回false。 |
<>, != | 不等于,检测两个值是否相等,如果不相等返回true | (A != B) 返回 true。 |
> | 大于号,检测左边的值是否大于右边的值, 如果左边的值大于右边的值返回true | (A > B) 返回false。 |
< | 小于号,检测左边的值是否小于右边的值, 如果左边的值小于右边的值返回true | (A < B) 返回 true。 |
>= | 大于等于号,检测左边的值是否大于或等于右边的值, 如果左边的值大于或等于右边的值返回true | (A >= B) 返回false。 |
<= | 小于等于号,检测左边的值是否小于于或等于右边的值, 如果左边的值小于或等于右边的值返回true | (A <= B) 返回 true。 |
使用主键来作为WHERE子句的条件查询是非常快速的。
mysql> select * from student where stu_id > '3'; +--------+------+-----+---------------+ | stu_id | name | age | register_date | +--------+------+-----+---------------+ | 4 | c | 22 | 2016-10-27 | | 5 | d | 22 | 2016-10-27 | +--------+------+-----+---------------+ 2 rows in set (0.00 sec)
MYSQL UPDATE查询
语法:
UPDATE table_name SET n1=new-value1,n2=new-value2 [WHERE Clause]
mysql> update student set age=22,name='haha' where stu_id>3; Query OK, 2 rows affected (0.00 sec) Rows matched: 2 Changed: 2 Warnings: 0 mysql> select * from student where stu_id > '3'; +--------+------+-----+---------------+ | stu_id | name | age | register_date | +--------+------+-----+---------------+ | 4 | haha | 22 | 2016-10-27 | | 5 | haha | 22 | 2016-10-27 | +--------+------+-----+---
MYSQL DELETE语句
语法:
DELETE FROM table_name [WHERE CLause]
mysql> delete from student where stu_id=5; Query OK, 1 row affected (0.00 sec) mysql> select * from student where stu_id ='5'; Empty set (0.00 sec)
MYSQL LIKE 语句
语法:
SELECT n1,n2,...nn table_ame1,table_name2... WHERE n1 LIKE condition1 [AND [OR]] n2 = 'value'
mysql> select * from student where name like "%a"; #查询name表中带a的字段 +--------+------+-----+---------------+ | stu_id | name | age | register_date | +--------+------+-----+---------------+ | 2 | a | 22 | 2016-10-27 | | 4 | haha | 22 | 2016-10-27 | +--------+------+-----+---------------+ 2 rows in set (0.00 sec)
MYSQL排序
语法:
SELECT n1,n2,...nn table_name1,table_name2... ORDER BY n1,[n2...] [ASC [DESC]] #使用ASC或DESC关键字来设置查询结果是按升序或降序排序。默认情况下,它是按升序排列。
mysql> select * from student where name like "%a" order by stu_id desc; #让其按降序排列 +--------+------+-----+---------------+ | stu_id | name | age | register_date | +--------+------+-----+---------------+ | 4 | haha | 22 | 2016-10-27 | | 2 | a | 22 | 2016-10-27 | +--------+------+-----+---------------+ 2 rows in set (0.00 sec) mysql> select * from student where name like "%a" order by stu_id; #默认为按升序排列 +--------+------+-----+---------------+ | stu_id | name | age | register_date | +--------+------+-----+---------------+ | 2 | a | 22 | 2016-10-27 | | 4 | haha | 22 | 2016-10-27 | +--------+------+-----+---------------+ 2 rows in set (0.00 sec)
MYSQL GROUP BY 语句
SELECT column_name,function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name;
例子:
mysql> select name,COUNT(*) FROM student GROUP BY name; #使用GROUP BY语句将数据表按名字进行分组,并统计每个名字有多少条记录。 +-----------+---------- | name | COUNT(*) | +-----------+----------+ | a | 1 | | b | 1 | | chai shao | 1 | | haha | 1 | +-----------+----------+ 4 rows in set (0.01 sec)
mysql> select name, SUM(age) as singin_count FROM student GROUP BY name WITH ROLLUP; +-----------+--------------+ | name | singin_count | +-----------+--------------+ | a | 22 | | b | 22 | | chai shao | 23 | | haha | 22 | | NULL | 89 | +-----------+--------------+ 5 rows in set (0.00 sec)
MYSQL ALTER命令
我们修改数据库表名或者修改数据表字段时,就需要使用到MYSQL ALTER命令。
alter
table
student
drop
register_date; #从student表删除register_date字段
alter
table
student
add
phone
int
(11)
not
null
; #添加phone字段
修改字段类型及名称
如果需要修改字段类型及名称,你可以在ALTER命令中使用MODIFY或CHANGE子句。
例如,把字段c的类型从CHAR(1)改为CHAR(10),可以执行以下命令:
ALTER TABLE test_table1 MODIFY c CHAR(10);
使用CHANGE子句,语法有很大的不同。在CHANGE关键字之后,紧跟着的是你要修改的字段名,然后指定新字段名及类型。尝试如下实例:
ALTER TABLE test_table1 CHANGE i j BUGUBT;
ALTER TABLE test_table1 CHANGE i j INT;
当你修改字段时,你可以指定是否包含只或者是否设置默认值。
以下实例,制定字段j为NOT NULL 且默认值为100.
mysql> ALTER TABLE yii_page MODIFY body BIGINT NOT NULL DEFAULT 100; #更改yii_page表 从NULL改为NOT NULL并且值默认100 Query OK, 2 rows affected, 2 warnings (0.07 sec)
mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | student | +----------------+ 1 row in set (0.00 sec) mysql> ALTER TABLE student RENAME TO student1; #修改表名 Query OK, 0 rows affected (0.01 sec) mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | student1 | +----------------+ 1 row in set (0.00 sec)
MYSQL外键
外键,一个特殊的索引,用于关联两个表,只能是制定内容
MYSQL NULL 值处理
我们已经知道MYSQL使用SQL SELECT 命令及WHERE子句来读取数据表中的数据,但是当提供的查询条件字段为NULL时,该命令可能就无法正常工作。
为了处理这种情况,MySQL提供了三大运算符:
IS NULL:当列的值不为NULL,运算符返回ture。
<=>:比较操作符(不同于=运算符),当比较的两个值为NULL时返回true
关于NULL的条件比较运算时比较特殊的。你不能使用=NULL或!=NULL在列中查找NULL值。
在MYSQL中,NULL值与任何其他值得比较(即使是NULL)永远返回false,即NULL=NULL返回false.
MYSQL中处理NULL使用IS NULL和IS NOT NULL运算符。
MYSQL连接(left join,right join,inner join,full join)
可以在select,update和delete语句中使用mysql的join来联合多表查询。
join按照功能大致分为如下三类:
INNER JOIN(内连接或等值连接):获取两个表中字段匹配关系的记录。
LEFT JOIN(左连接):获取左表所有记录,即使右表没有对应匹配的记录。
RIGHT JOIN(右连接):与LEFT JOIN相反,用于获取右表所有记录,即使左边没有对应匹配的记录。