术语
数据库(Database)是按照数据结构来组织、存储和管理数据的仓库。
RDBMS即关系数据库管理系统(Relational Database Management System)
- 数据库: 数据库是一些关联表的集合。
- 数据表: 表是数据的矩阵。在一个数据库中的表看起来像一个简单的电子表格。
- 列: 一列(数据元素) 包含了相同的数据, 例如邮政编码的数据。
- 行:一行(=元组,或记录)是一组相关的数据,例如一条用户订阅的数据。
- 冗余:存储两倍数据,冗余降低了性能,但提高了数据的安全性。
- 主键:主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据。
- 外键:外键用于关联两个表。
- 复合键:复合键(组合键)将多个列作为一个索引键,一般用于复合索引。
- 索引:使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。
- 参照完整性: 参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件,目的是保证数据的一致性。
数据库创建、删除、选择
create test_db
drop test_db
use test_db
MySQL 数据类型
数值类型:
日期类型:
字符类型:
数据表操作
创建数据表
CREATE TABLE tutorials_tbl(
tutorial_id INT NOT NULL AUTO_INCREMENT,
tutorial_title VARCHAR(100) NOT NULL,
tutorial_author VARCHAR(40) NOT NULL,
submission_date DATE,
PRIMARY KEY ( tutorial_id )
);
删除数据表
DROP TABLE table_name;
插入数据:
INSERT INTO tutorials_tbl
(tutorial_title, tutorial_author, submission_date)
VALUES
("Learn MySQL", "Abdul S", NOW()),
("JAVA Tutorial", "Sanjay", '2007-05-06');
查询数据:
SELECT * from tutorials_tbl;
语句
where 子句
SELECT * from tutorials_tbl WHERE tutorial_author='Sanjay';
UPDATE 语句
UPDATE tutorials_tbl SET tutorials_title='Learning JAVA' WHERE tutorials_id=2;
DELETE 语句
DELETE FROM tutorials_tbl WHERE tutorials_id=2;
LIKE 语句
SELECT * from tutorials_tbl WHERE tutorials_author LIKE '%jay';
ORDER BY 子句
SELECT * from tutorials_tbl ORDER BY tutorials_author ASC(或者DESC);
GROUP BY 语句 (根据一个或多个列对结果集进行分组)
SELECT name, COUNT(*) FROM employee_tbl GROUP BY name;
WITH ROLLUP 语句 (实现在分组统计数据基础上再进行相同的统计)
SELECT name, SUM(singin) as singin_count FROM employee_tbl GROUP BY name WITH ROLLUP;
多表查询
JOIN 按照功能大致分为如下三类:
- INNER JOIN(内连接,或等值连接):获取两个表中字段匹配关系的记录。
- LEFT JOIN(左连接):获取左表所有记录,即使右表没有对应匹配的记录。
- RIGHT JOIN(右连接): 与 LEFT JOIN 相反,用于获取右表所有记录,即使左表没有对应匹配的记录。
NULL 值处理
- IS NULL: 当列的值是NULL,此运算符返回true。
- IS NOT NULL: 当列的值不为NULL, 运算符返回true。
- <=>: 比较操作符(不同于=运算符),当比较的的两个值为NULL时返回true。
判断是否为 NULL,必须使用IS NULL和IS NOT NULL
使用= 和 != 运算符是不起作用的
正则表达式
REGEXP 操作符
SELECT name FROM person_tbl WHERE name REGEXP '^st';
MySQL 事务
MySQL 事务主要用于处理操作量大,复杂度高的数据。
- 在MySQL中只有使用了Innodb数据库引擎的数据库或表才支持事务
- 事务处理可以用来维护数据库的完整性,保证成批的SQL语句要么全部执行,要么全部不执行
- 事务用来管理insert,update,delete语句
一般来说,事务是必须满足4个条件(ACID): Atomicity(原子性)、Consistency(稳定性)、Isolation(隔离性)、Durability(可靠性)
- 1、事务的原子性:一组事务,要么成功;要么撤回。
- 2、稳定性 : 有非法数据(外键约束之类),事务撤回。
- 3、隔离性:事务独立运行。一个事务处理后的结果,影响了其他事务,那么其他事务会撤回。事务的100%隔离,需要牺牲速度。
- 4、可靠性:软、硬件崩溃后,InnoDB数据表驱动会利用日志文件重构修改。可靠性和高速度不可兼得, innodb_flush_log_at_trx_commit选项 决定什么时候把事务保存到日志里。
ALTER 命令
当我们需要修改数据表名或者修改数据表字段时,就需要使用到MySQL ALTER命令。
删除,添加或修改表字段
ALTER TABLE testalter_tbl DROP i;
ALTER TABLE testalter_tbl ADD i INT; // 自动添加到数据表字段的末尾。
ALTER TABLE testalter_tbl DROP i;
ALTER TABLE testalter_tbl ADD i INT FIRST; // FIRST (设定位第一列)
ALTER TABLE testalter_tbl DROP i;
ALTER TABLE testalter_tbl ADD i INT AFTER c; // AFTER 字段名(设定位于某个字段之后)
修改字段类型及名称
ALTER TABLE testalter_tbl MODIFY c CHAR(10);
ALTER TABLE testalter_tbl CHANGE i j BIGINT;
ALTER TABLE testalter_tbl CHANGE j j INT;
// 指定字段 j 为 NOT NULL 且默认值为100
ALTER TABLE testalter_tbl MODIFY j BIGINT NOT NULL DEFAULT 100;
ALTER TABLE testalter_tbl ALTER i DROP DEFAULT; // 删除字段的默认值
ALTER TABLE testalter_tbl TYPE = MYISAM; // 修改数据表类型
SHOW TABLE STATUS LIKE 'testalter_tbl'\G // 查看数据表类型
ALTER TABLE testalter_tbl RENAME TO alter_tbl; // 修改表名
MySQL索引
MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度。
索引分单列索引和组合索引。
- 单列索引,即一个索引只包含单个列,一个表可以有多个单列索引,但这不是组合索引。
- 组合索引,即一个索包含多个列。
实际上,索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录。
过多的使用索引将会造成滥用。因此索引也会有它的缺点:虽然索引大大提高了查询速度,同时却会降低更新表的速度。
建立索引会占用磁盘空间的索引文件。
创建索引
CREATE INDEX indexName ON mytable(username(length));
CREATE UNIQUE INDEX indexName ON mytable(username(length))
使用ALTER 命令添加和删除索引
有四种方式来添加数据表的索引:
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 该语句添加一个主键,这意味着索引值必须是唯一的,且不能为NULL。
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 这条语句创建索引的值必须是唯一的(除了NULL外,NULL可能会出现多次)。
ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引,索引值可出现多次。
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):该语句指定了索引为 FULLTEXT ,用于全文索引。
MySQL 临时表
MySQL 临时表在我们需要保存一些临时数据时是非常有用的。临时表只在当前连接可见,当关闭连接时,MySQL会自动删除表并释放所有空间。
CREATE TEMPORARY TABLE SalesSummary(
);
MySQL复制表
完整的复制MySQL数据表,步骤如下:
- 使用 SHOW CREATE TABLE 命令获取创建数据表(CREATE TABLE) 语句,该语句包含了原数据表的结构,索引等。
- 复制以下命令显示的SQL语句,修改数据表名,并执行SQL语句,通过以上命令 将完全的复制数据表结构。
- 如果你想复制表的内容,你就可以使用 INSERT INTO … SELECT 语句来实现。
获取MySQL元数据
获取服务器配置信息
序列
MySQL中最简单使用序列的方法就是使用 AUTO_INCREMENT 来定义列
设置起始值 ALTER TABLE t AUTO_INCREMENT = 100;
数据唯一
MySQL数据表中设置指定的字段为 PRIMARY KEY(主键) 或者 UNIQUE(唯一) 索引来保证数据的唯一性。
SQL 注入
如果您通过网页获取用户输入的数据并将其插入一个MySQL数据库,那么就有可能发生SQL注入安全的问题。
导入导出
导出:
mysqldump是MySQL用于转存储数据库的实用程序。
导入:
1. MySQL 中提供了LOAD DATA INFILE语句来插入数据
2. 使用 mysqlimport 导入数据