python笔记（week7）—数据库

七、    数据库（mysql）

（一）   RDBMS简介

1.基本概念

关系型数据库管理系统（Relational Database Management System，RDBMS）是E.F.Codd博士在其发表的论文《大规模共享数据银行的关系型模型》(Communications of the ACM杂志1970年6月刊)基础上设计出来的。简单来说多张表合起来就是数据库,数据库的作用就是保证数据的一致性，以便数据的增删改查

数据库: 数据库是一些关联表的集合。.

数据表: 表是数据的矩阵。在一个数据库中的表看起来像一个简单的电子表格。

列: 一列(数据元素) 包含了相同的数据, 例如邮政编码的数据。

行：一行（=元组，或记录）是一组相关的数据，例如一条用户订阅的数据。

冗余：存储两倍数据，冗余可以使系统速度更快。(表的规范化程度越高，表与表之间的关系就越多；查询时可能经常需要在多个表之间进行连接查询；而进行连接操作会降低查询速度。例如，学生的信息存储在student表中，院系信息存储在department表中。通过student表中的dept_id字段与department表建立关联关系。如果要查询一个学生所在系的名称，必须从student表中查找学生所在院系的编号（dept_id），然后根据这个编号去department查找系的名称。如果经常需要进行这个操作时，连接查询会浪费很多的时间。因此可以在student表中增加一个冗余字段dept_name，该字段用来存储学生所在院系的名称。这样就不用每次都进行连接操作了。

主键：主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据。

外键：外键用于关联两个表。

复合键：复合键（组合键）将多个列作为一个索引键，一般用于复合索引。

索引：使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。

参照完整性: 参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件，目的是保证数据的一致性。

2.常用关系性数据库

（1）      Oracle

（2）      Mysql:免费、开源

l  MySQL Community Server 社区版本，开源免费，但不提供官方技术支持。正式发布版 GA，为通用版本。开发版 Development Releases ，仅供有兴趣的开发人员使用测评。

l  MySQL Enterprise Edition 企业版本，需付费，可以试用30天。

l  MySQL Cluster 集群版，开源免费。可将几个MySQL Server封装成一个Server。

l  MySQL Cluster CGE 高级集群版，需付费。

l  MySQL Workbench（GUI TOOL）一款专为MySQL设计的ER/数据库建模工具。它是著名的数据库设计工具DBDesigner4的继任者。MySQL Workbench又分为两个版本，分别是社区版（MySQL Workbench OSS）、商用版（MySQL Workbench SE）。

（3）      Sqlserver

（4）      DB2

（5）      Postgresql

（6）      Sqlite

（7）      Access

3.常用的DOS命令

l  目录命令

l  磁盘命令

l  文件命令

l  其他命令

l  Ping命令

l  Shut down

l  基本命令

4.Null

MySQL使用 SQL SELECT 命令及 WHERE 子句来读取数据表中的数据,但是当提供的查询条件字段为 NULL 时，该命令可能就无法正常工作。

（1）              为了处理这种情况，MySQL提供了三大运算符:

l  IS NULL: 当列的值是NULL,此运算符返回true。

l  IS NOT NULL: 当列的值不为NULL, 运算符返回true。

l  <=>: 比较操作符（不同于=运算符），当比较的的两个值为NULL时返回true。 

（2）              关于 NULL 的条件比较运算是比较特殊的。你不能使用 = NULL 或 != NULL 在列中查找 NULL 值 。

（3）              在MySQL中，NULL值与任何其它值的比较（即使是NULL）永远返回false，即 NULL = NULL 返回false 。

（4）              MySQL中处理NULL使用IS NULL和IS NOT NULL运算符。

5.推荐用书

 

 

 

（二）   数据类型

MySQL中定义数据字段的类型对你数据库的优化是非常重要的。MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。

1.Text 类型：

数据类型	描述
CHAR(size)	保存固定长度的字符串（可包含字母、数字以及特殊字符）。在括号中指定字符串的长度。最多 255 个字符。
VARCHAR(size)	保存可变长度的字符串（可包含字母、数字以及特殊字符）。在括号中指定字符串的最大长度。最多 255 个字符。 注释：如果值的长度大于 255，则被转换为 TEXT 类型。
TINYTEXT	存放最大长度为 255 个字符的字符串。
TEXT	存放最大长度为 65,535 个字符的字符串。
BLOB	用于 BLOBs (Binary Large OBjects)。存放最多 65,535 字节的数据。
MEDIUMTEXT	存放最大长度为 16,777,215 个字符的字符串。
MEDIUMBLOB	用于 BLOBs (Binary Large OBjects)。存放最多 16,777,215 字节的数据。
LONGTEXT	存放最大长度为 4,294,967,295 个字符的字符串。
LONGBLOB	用于 BLOBs (Binary Large OBjects)。存放最多 4,294,967,295 字节的数据。
ENUM(x,y,z,etc.)	允许你输入可能值的列表。可以在 ENUM 列表中列出最大 65535 个值。如果列表中不存在插入的值，则插入空值。 注释：这些值是按照你输入的顺序存储的。 可以按照此格式输入可能的值：ENUM('X','Y','Z')
SET	与 ENUM 类似，SET 最多只能包含 64 个列表项，不过 SET 可存储一个以上的值。

2.Number 类型：

数据类型	描述
TINYINT(size)	-128 到 127 常规。0 到 255 无符号*。在括号中规定最大位数。
SMALLINT(size)	-32768 到 32767 常规。0 到 65535 无符号*。在括号中规定最大位数。
MEDIUMINT(size)	-8388608 到 8388607 普通。0 to 16777215 无符号*。在括号中规定最大位数。
INT(size)	-2147483648 到 2147483647 常规。0 到 4294967295 无符号*。在括号中规定最大位数。
BIGINT(size)	-9223372036854775808 到 9223372036854775807 常规。0 到 18446744073709551615 无符号*。在括号中规定最大位数。
FLOAT(size,d)	带有浮动小数点的小数字。在括号中规定最大位数。在 d 参数中规定小数点右侧的最大位数。
DOUBLE(size,d)	带有浮动小数点的大数字。在括号中规定最大位数。在 d 参数中规定小数点右侧的最大位数。
DECIMAL(size,d)	作为字符串存储的 DOUBLE 类型，允许固定的小数点。

* 这些整数类型拥有额外的选项 UNSIGNED。通常，整数可以是负数或正数。如果添加 UNSIGNED 属性，那么范围将从 0 开始，而不是某个负数。

3.Date 类型：

数据类型	描述
DATE()	日期。格式：YYYY-MM-DD 注释：支持的范围是从 '1000-01-01' 到 '9999-12-31'
DATETIME()	*日期和时间的组合。格式：YYYY-MM-DD HH:MM:SS 注释：支持的范围是从 '1000-01-01 00:00:00' 到 '9999-12-31 23:59:59'
TIMESTAMP()	*时间戳。TIMESTAMP 值使用 Unix 纪元('1970-01-01 00:00:00' UTC) 至今的描述来存储。格式：YYYY-MM-DD HH:MM:SS 注释：支持的范围是从 '1970-01-01 00:00:01' UTC 到 '2038-01-09 03:14:07' UTC
TIME()	时间。格式：HH:MM:SS 注释：支持的范围是从 '-838:59:59' 到 '838:59:59'
YEAR()	2 位或 4 位格式的年。 注释：4 位格式所允许的值：1901 到 2155。2 位格式所允许的值：70 到 69，表示从 1970 到 2069。

* 即便 DATETIME 和 TIMESTAMP 返回相同的格式，它们的工作方式很不同。在 INSERT 或 UPDATE 查询中，TIMESTAMP 自动把自身设置为当前的日期和时间。TIMESTAMP 也接受不同的格式，比如 YYYYMMDDHHMMSS、YYMMDDHHMMSS、YYYYMMDD 或 YYMMDD。

（三）增加

1.新增数据库

create database testdb charset utf8；#创建一个叫testdb的数据库，且让其支持中文 

2.新增表

1 create table student(                  #增加一个名为student的表
2 stu_id INT NOT NULL AUTO_INCREMENT,    #自动延展
3 name CHAR(32) NOT NULL,
4 age  INT NOT NULL,
5 register_date DATE,
6 PRIMARY KEY ( stu_id )                    #设置主键
7  );

 

3.插入数据 

1 insert into student (name,age,register_date) values ("张三",22,"2016-03-4");

 

   （四）删除

1.删除数据库。drop database数据库名;

2.删除表。drop table 表名；

3.删除数据。DELETE FROM 表名WHERE  stu_id=5; 

 

（五）修改

1.更新表的数据（update … where…）

update student set age=22 ,name="张三" where stu_id>3;

2.添加、修改或删除列(ALTER TABLE)

(1)添加列:ALTER TABLE table_name ADD column_name datatype

(2)要删除表中的列：ALTER TABLE table_name DROP COLUMN column_name

（六）查询

1.   查看机器上所有的数据库：show databases;

 

 

2.查看数据库创建结构

l  ues 数据库名;进入其中的数据库

l  show create database 数据库名：查看数据库创建结构

 

 

注：SET 后面的就是数据库用的字符编码。

3. show create table 表名：查看表的创建纪录

4.查看数据库所有可用的表。show tables;

5.查看表的结构。desc 表名;

 

 

6.查询数据

1  SELECT column_name,column_name
2  FROM  表名
3 [WHERE Clause]
4 [OFFSET M ][LIMIT N]

 

（1）    查询语句中你可以使用一个或者多个表，表之间使用逗号(,)分割，并使用WHERE语句来设定查询条件。

 

 

以下为操作符列表，可用于 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) 返回 tru

 

（2）    SELECT 命令可以读取一条或者多条记录。查询所有数据:Select * from 表名。模糊查询：Select * from student where data like “2016-06%”。在 SQL 中，可使用以下通配符：

通配符	描述
%	替代一个或多个字符
_	仅替代一个字符
[charlist]	字符列中的任何单一字符
[^charlist]或者[!charlist]	不在字符列中的任何单一字符

 

（3）              通过OFFSET指定SELECT语句开始查询的数据偏移量，默认情况下偏移量为0。使用 LIMIT 属性来设定返回的记录数。

 

7.跨表查询

Mysql 连接（left join, right join, inner join ,full join，有A，B两张表数据如下：

1 2 3 4 5 6

A    B -    - 1    3 2    4 3    5 4    6

 

（1）    INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。

1 2 3 4 5 6 7 8

select * from a LEFT JOIN b on a.a = b.b;   a |  b --+----- 1 | null 2 | null 3 |   3 4 |  4

 

（2）    LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。

1 2 3 4 5 6 7 8

select * from a LEFT JOIN b on a.a = b.b;   a |  b --+----- 1 | null 2 | null 3 |  3 4 |  4

 

（3）       RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

1 2 3 4 5 6 7 8

select * from a RIGHT JOIN b on a.a = b.b;   a    |  b -----+---- 3    |  3 4    |  4 null   |  5 null   |  6

 

（4）       Full join

mysql 并不直接支持full join.

1 2 3 4 5 6 7 8

select * from a left join b on a.a = b.b UNION select * from a right join b on a.a = b.b; +------+------+ | a    | b    | +------+------+ |    3 |    3 | |    4 |    4 | |    1 |   NULL | |    2 |   NULL | | NULL   |    5 | | NULL   |    6 |

 

 

(七).外键

1 CREATE TABLE record (
2  id int NOT NULL AUTO_INCREMENT,
3  record1 int NOT NULL,
4  record2 int NOT NULL,
5 stu_id int not null,
6   PRIMARY KEY (`id`),
7   KEY `fk_student_key` (stu_id),
8   CONSTRAINT `fk_student_key` FOREIGN KEY (stu_id) REFERENCES student (stu_id)
9 );

 

 

 

（1）              Mul就是外键标示。

（2）              在插入数据时，因为两张表的stu_id是关联的。并且Student为主表，表中的stu_id只有1，2，3。故在record中的stu_id不能超过1，2，3.

（3）              删除数据时，删除从表record不影响主表student，但不能删除主表student。

（八）排序

使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。 默认情况下，它是按升序排列。

select *from student where name like binary "%Li" order by stu_id desc;

 

（九）分组

1.count

将数据表按名字进行分组，并统计每个人有多少条记录：

 SELECT name, COUNT(*) FROM   employee_tbl GROUP BY name;

2.sum

SELECT coalesce(name, '总数

'), SUM(singin) as singin_count FROM  employee_tbl GROUP BY name WITH ROLLUP

WITH ROLLUP表示用来求总数。如果不用coalesce(name, '总数')来表达总数名称，那总数的名称为null.

 

（九）事务

MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你即需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务。

（1）事务特点

l  在MySQL中只有使用了Innodb数据库引擎的数据库或表才支持事务

l  事务处理可以用来维护数据库的完整性，保证成批的SQL语句要么全部执行，要么全部不执行

l  事务用来管理insert,update,delete语句

（2）事务条件

一般来说，事务是必须满足如下条件：

n  事务的原子性：一组事务，要么成功；要么撤回。

n  稳定性 ： 有非法数据（外键约束之类），事务撤回。

n  隔离性：事务独立运行。一个事务处理后的结果，影响了其他事务，那么其他事务会撤回。事务的100%隔离，需要牺牲速度。

n  可靠性：软、硬件崩溃后，InnoDB数据表驱动会利用日志文件重构修改。可靠性和高速度不可兼得， innodb_flush_log_at_trx_commit选项 决定什么时候吧事务保存到日志里。

（3）事务操作

l  mysql> begin； ＃开始一个事务 

l  mysql> insert into student (name,age,register_date) values ("张三",22,"2016-03-4");

l  mysql>rollback; #回滚 ， 这样数据是不会写入的

l  mysql>commit; #当数据没问题，输入commit提交命令就行

1 2 3

mysql> begin； ＃开始一个事务  mysql> insert into student (name,age,register_date) values ("张三",22,"2016-03-4");  mysql>rollback; 回滚 ， 这样数据是不会写入的  mysql>commit; 当然如果上面的数据没问题，就输入commit提交命令就行；

 

（十）索引

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。所谓索引就是对要查询的列，将此列的数据转成hash值，然后进行排列，利用二分法之类的进行查询。

l  索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索包含多个列。

l  创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。 

l  实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

l  上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。建立索引会占用磁盘空间的索引文件。

（1）普通索引

1）创建索引

a.普通方式

CREATE INDEX indexName ON mytable(username(length));  

如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。

 b.修改表结构

ALTER mytable ADD INDEX [indexName] ON (username(length))

c.创建表的时候直接指定

1 CREATE TABLE mytable( 
2 ID INT NOT NULL,  
3 username VARCHAR(16) NOT NULL, 
4 INDEX [indexName] (username(length)) 
5 ); 

 

2）删除索引

DROP INDEX [indexName] ON mytable;

（2）唯一索引

它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：

1）创建唯一索引

a.普通方式

CREATE UNIQUE INDEX indexName ON mytable(username(length))

 

b.修改表结构

ALTER mytable ADD UNIQUE [indexName] ON (username(length))

 

c.创建表的时候直接指定

CREATE TABLE mytable(   

ID INT NOT NULL,    

username VARCHAR(16) NOT NULL,   

UNIQUE [indexName] (username(length)) 

);

（十一）视图

视图是一个虚拟表（非真实存在），其本质是【根据SQL语句获取动态的数据集，并为其命名】，用户使用时只需使用【名称】即可获取结果集，并可以将其当作表来使用，但只能查询，不能像一般一样增加和修改。

1.创造视图

1 CREATE VIEW v1 AS
2 SELET stu_id,name FROM student WHERE name=”李四”;

但注意不能通过insert插入数据。

 

 

2.删除视图

Drop view 视图名

3.修改视图

1）不能update

由于视图的数据来于查询的表，不能直接修改视图，实验显示数据都没的了。可通过修改原表来达到改变视图数据。

改原表：

 

 update：

 

 

2）修改查询条件

其实也不相当于重新再创建一遍查询条件

1 SQL CREATE OR REPLACE VIEW Syntax
2 CREATE OR REPLACE VIEW view_name AS
3 SELECT column_name(s)
4 FROM table_name
5 WHERE condition

 

4.查询

和一般表查询一样。 

 

转载于:https://www.cnblogs.com/L736/p/8466233.html