七、 数据库(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 |
|
(七).外键
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.查询
和一般表查询一样。