MySQL数据库应用从入门到精通学习笔记-白红宇

MySQL数据库应用从入门到精通学习笔记

阅读量：5137 次

发布时间：2019-06-13

本文共 23350 字，大约阅读时间需要 77 分钟。

以下内容是学习《MySQL数据库应用从入门到精通》过程中总结的一些内容提要，供以后自己复现使用。

一：数据库

查看所有数据库: SHOW DATABASES

创建数据库: CREATE DATABSE database_name

切换数据库: USE database_name

删除数据库: DROP DATABASE database_name

二：存储引擎

查看MYSQL支持的引擎: SHOW ENGINES \G (\G 使显示格式更好看)

查看所支持的存储引擎: SHOW VARIABLES LIKE 'have%';

查看默认的存储引擎： SHOW VARIABLES LIKE 'storage_engine%';

经试验，自己mysql上运行应该是: SHOW VARIABLES LIKE 'default_storage_engine%'

修改默认存储引擎:方法1：向导模式修改方法2：修改配置文件my.ini中default-storage-engine

三：数据类型

数据类型字节

TINYINT 1

SMALLINT 2

MEDIUMINT 3

INT 和 INTEGER 4

BIGINT 8

FLOAT 4

DOUBLE 8

DECI(M,D) M+2

DECIMAL(M,D) M+2

存储小数用float，需要精确到小数点后10位，用double；需要更精确的，用decimal

BIT(M) 1~8

DATE 4

DATETIME 8

TIMESTAMP 4

TIME 3

YEAR 1

年月日用DATE;年月日时分秒用DATETIME;经常插入或更新日期为当前系统时间，用TIMESTAMP;时分秒用TIME;年份用YEAR。

存储的日期需要让不同地区矠用户使用，则可以使用TIMESTAMP，因为只有该类型日期能够与实际时区相对应。

eg:

create table t_data_test(

f_date DATE,

f_datetime DATETIME,

f_timestamp TIMESTAMP,

f_time TIME,

f_year YEAR);

SELECT CURDATE(),NOW(),NOW(),TIME(NOW()),YEAR(NOW());

结果:

CURDATE() NOW() NOW() TIME(NOW()) YEAR(NOW())

12/1/2017 12/1/2017 20:16:56 12/1/2017 20:16:56 20:16:56 2017

INSERT INTO t_data_test VALUES(CURDATE(),NOW(), NOW(), time(NOW()), YEAR(NOW()));

SELECT * FROM t_data_test;

f_date f_datetime f_timestamp f_time f_year

12/1/2017 12/1/2017 20:13:37 12/1/2017 20:13:37 20:13:37 2017

CHAR(M) M

VARCHAR(M) M

TINYTEXT 0~255

TEXT 0~65535

MEDIUMTEXT 0~167772150

LONGTEXT 0~4294967295

存文本文件

BINARY(M) M

VARBINARY(M) M

存二进制数据（如图片、音乐、视屏文件）

TINYBLOB 0~255

BLOB 0~2^16

MEDIUMBLOB 0~2^24

LONGBLOB 0~2^32

存大量二进制数据（如电影等视屏文件）

四：表

创建表：

CREATE TABLE table_name(

属性名数据类型,

属性名数据类型

)

查看表:

DESCRIBE table_name;

查看表详细定义（创建语句）:

SHOW CREATE TABLE table_name (\G);

删除表:

DROP TABLE table_name;

修改表：

改表名: ALTER TABLE old_table_name RENAME [TO] new_table_name;

在表的最后一个位置增加字段: ALTER TABLE table_name ADD 属性名数据类型;

在表的最后一个位置增加字段: ALTER TABLE table_name ADD 属性名数据类型 FIRST;

在表的指定字段之后增加字段: ALTER TABLE table_name ADD 属性名数据类型 AFTER 属性名;

删除字段：ALTER TABLE table_name DROP 属性名;

修改字段：ALTER TABLE table_name MODIFY 属性名数据类型;

修改字段名字：ALTER TABLE table_name CHANGE 旧属性名新属性名数据类型;

同时修改字段名字和属性：ALTER TABLE table_name CHANGE 旧属性名新属性名新数据类型;

修改字段的顺序：ALTER TABLE table_name MODIFY 属性名1 数据类型 FIRST

或: ALTER TABLE table_name MODIFY 属性名1 数据类型 AFTER 属性名2

五：表约束

NOT NULL

DEFAULT

UNIQUE KEY(UK)

PRIMARY KEY(PK)

FOREIGH KEY(FK)

AUTO_INCREMENT

eg:

CREATE TABLE table_name(

属性名数据类型 NOT NULL,

属性名数据类型 DEFAULT 默认值,

属性名数据类型 UNIQUE,

属性名数据类型 PRIMARY KEY,

属性名数据类型 AUTO_INCREMENT

)

或将约束条件写在后面：

CREATE TABLE table_name(

属性名数据类型 NOT NULL,

属性名数据类型 DEFAULT 默认值,

属性名数据类型,

CONSTRAINT UK约束名 UNIQUE(dname)，

CONSTRAINT PK约束名 PRIMARY KEY(属性名，属性名……),

CONSTRAINT FK约束名 FOREIGH KEY(属性名1) REFERENCES 表名(属性名2)

)

六：索引

表是存储和操作数据的逻辑结构，而索引则是一种有效组合数据的方式。通过索引对象，可以快速查询到表中矠特定记录，是一种提高性能的常用方式。一个索引会包含表中按照一定顺序排序的一列或多列字段。

索引按照存储类型，可以分为B型树索引(BTREE)和哈希索引(HASH). InnoDB和MyISAM存储引擎支持BTREE类型索引，MEMORY存储引擎支持HASH类型索引。默认为前者索引。

索引除了可以提高数据库管理系统的查找速度，还可以保证字段的唯一性，从而实现数据库表的完整性。创建索引可以提高查询速度，但过多的创建索引则会占据许多磁盘空间。

以下情况适合创建索引：

1）经常被查询的字段，即在where子句中出现的字段；

2）分组的字段，即在group by子句中出现的字段；

3）存在依赖关系的子表和父表之间的联合查询，即主键或外键字段。

4）设置唯一完整性约束的字段。

以下情况不适合创建索引：

1）在查询中很少被使用的字段；

2）拥有许多重复值的字段。

索引可以分为普通索引、全文索引、单列索引、多列索引和空间索引。

创建(3种方式)：

CREATE TABLE table_name(

属性名数据类型,

……

属性名数据类型,

【UNIQUE|FULLTEXT】INDEX | KEY [索引名] (属性名【(长度)】【ASC|DESC】, ...)

);

CREATE 【UNIQUE|FULLTEXT】INDEX 索引名 ON 表明 (属性名【(长度)】【ASC|DESC】, ...)

ALTER TABLE table_name ADD 【UNIQUE|FULLTEXT】INDEX|KEY 索引名(属性名【(长度)】【ASC|DESC】, ...)

eg: CREATE TABLE t_dept(

deptno INT,

dname VARCHAR(20),

loc VARCHAR(40),

INDEX index_deptno(deptno)

);

唯一索引要加UNIQUE修饰符，全文索引加FULLTEXT修饰符。普通索引和多列索引不需要加。多列索引后面是多个列名，中间用,分隔。

查看索引:

SHOW CREATE TABLE table_name \G;

校验索引是否被启用:

EXPLAIN SELECT * FROM t_dept WHERE deptno = 1\G;

删除索引:

DROP INDEX index_name ON table_name；

七、视图

视图可以提高复杂SQL语句矠复用性和表操作的安全性。视图本质上是一种虚拟表，其内容与真实的表相似，但视图并不在数据库中以存储的数据值形式存在。行和列数据来自定义视图的查询所引用基本表，并且在具体引用视图时动态生成。

视图特点如下：

1）视图的列可以来自不同的表，是表的抽象和在逻辑意义上建立的新关系；

2）视图是由基本表（实表）产生的表（虚表）；

3）视图的建立和删除不影响基本表；

4）对视图内容的更新（添加、删除和修改）直接影响基本表；

5）当视图来自多个基本表时，不允许添加和删除数据。

创建视图：

CREATE VIEW view_name AS 查询语句;

查看视图:

SHOW TABLES;

DESCRIBE view_name;

SHOW CREATE VIEW view_name;

select * from information_schema.views where table_name like 'v%'

查看视图详细信息:

SHOW TABLE STATUS 【FROM db_name】【LIKE 'pattern'】

删除视图:

DROP VIEW view_name【,view_name2, ...】

修改视图:

CREATE OR REPLACE VIEW view_name AS 查询语句

ALTER VIEW view_name as 查询语句

或：先删除后创建新的

利用视图操作基本表：

和操作实表一样，只不过将表名换成了视图名。

八、触发器

经常使用触发器是因为触发器可以加强数据库表中数据的完整性约束和业务规则等。

触发器使用地方：DELETE、INSERT、UPDATE。

触发器中可以写一行语句，也可以写多行语句。

每张表只能针对DELETE、INSERT、UPDATE三个事件的BEFORE和AFTER两个时间点添加最多六个（3event*2时间点）触发器。

创建触发器:

CREATE TRIGGER trigger_name

BEFORE|AFTER trigger_event

ON table_name FOR EACH ROW

【BEGIN】

trigger_stmt;

【END】

其中trigger_event就是DELETE、INSERT、UPDATE；trigger_stmt就是要执行的语句,单行语句不需要加BEGIN END，多行则需要。

eg:

每次t_dept表插入数据前都在t_diary日志表中增加一条日志记录。

CREATE TRIGGER tri_diarytime

BEFORE INSERT

ON t_dept FOR EACH ROW

INSERT INTO t_diary VALUES(NULL,'t_dept',now());

DELIMITER $$

CREATE TRIGGER tri_diarytime

BEFORE INSERT

ON t_dept FOR EACH ROW

BEGIN

INSERT INTO t_diary VALUES(NULL,'t_dept',now());

END

DELIMITER ;

写多行语句前要通过DELIMITER更换分隔符或还原分隔符。

查看触发器：

SHOW TRIGGERS;

SELECT * FROM information_schema.triggers where trigger_name like 'pattern';

删除触发器:

DROP TRIGGER trigger_name;

九、插入、更新、删除数据

插入完整数据记录:

INSERT INTO table_name VALUES(value1,value2...);

插入数据记录一部分:

INSERT INTO table_name(field1,field2,...) VALUES(value1,value2,...);

插入多条数据:

INSERT INTO table_name VALUES(value11,value12...),(value21,value22...),...;

INSERT INTO table_name(field1,field2,...) VALUES(value11,value12...),(value21,value22...),...;

插入查询结果:

INSERT INTO table_name(field1,field2,...)

SELECT (field1,field2,...)

FROM table_name2 WHERE ...

注意:自增的字段或有默认值的字段，可以采用插入数据记录的一部分的方法插入，这种方法还不依赖表字段的顺序，可扩展性好，建议使用。

更新数据:

UPDATE table_name

SET field1 = value1,

SET field2 = value2,

...

WHERE CONDITION;

删除数据:

DELETE FROM table_name WHERE CONDITION;

删除整张表里的数据：

DELETE FROM table_name;

TRUNCATE table_name；(速度更快，建议使用，原理：删除整张表，然后再根据原表DLL语句创建一个一模一样的表)

十、单笔数据记录简单查询

简单查询使用方式包括:

1)简单数据查询；

2）避免重复数据查询；

3）实现数学四则运算数据查询； +-*/%

4）设置显示格式数据查询。

eg:

SELECT * FROM t_dept;

SELECT DISTINCT filed1,field2 ... fieldn FROM table_name; (多个字段组合起来唯一)

SELECT ename, sal*12 AS yearsalary FROM t_employee;

SELECT CONCAT(ename, '雇员的年薪为: '， sal*12) yearsalary FROM t_employee;

条件数据查询:

单条件

多条件

between and

NOT

IS NULL / IS NOT NULL

IN / NOT IN (使用IN时，查询的集合（IN后面的枚举值）中如果存在NULL，则不会影响查询；如果使用关键字NOT IN，查询的集合中如果存在NULL，则不会查询到任何数据)

LIKE / NOT LIKE _ %

排序:

SELECT field1,field2...fieldn

FROM table_name

WHERE CONDITION

ORDER BY fieldm1 [ASC|DESC], fieldm2 [ASC|DESC] ...

如果字段的值为空值(NULL)，则该值为最小值，因此在降序排序中将最后显示，在升序排序中将最先显示。

限制数据记录查询数量

SELECT field1,field2...fieldn

FROM table_name

WHERE CONDITION

LIMIT offset_start,row_count;

使用方式:

1)不指定初始位置，只指定行数。 LIMIT row_count;

2)指定初始位置，指定行数。 LIMIT offset_start,row_count; 不包括offset那一行

统计函数：

count() avg() sum() max() min()

count(*):对表中数据进行统计，不管表字段中包含的是NULL值还是非NULL值；

count(field):对指定字段的记录进行统计，忽略NULL值。

其他几个统计函数，必须指定字段，所以忽略NULL值。

如果所操作的表中没有任何数据记录，则count()函数返回0，其他统计函数返回NULL。

分组数据记录查询：

SELECT function()

FROM table_name

WHERE CONDITION

GROUP BY field1,field2...

HAVING CONDITION

在具体进行分组查询时，分组所依赖的字段上的值一定要具有重复值，否则将没有任何意义。

首先针对field1进行分组，然后针对每组按照字段field2进行分组，以此类推。

SELECT * FROM table_name GROUP BY CONDITION; 是从分组中随机选择一条记录显示。所以group by 要和统计函数一起使用才有意义。

SELET deptno,GROUP_CONCAT(ename),COUNT(ename) number enames FROM t_employee GROUP BY deptno;

会将每组中的ename组合在一起显示.

结果:

deptno enames number

10 MILLER,KING,CLARK 3

20 FORD,ADAMS,SCOTT,JONES,SMITH 4

30 BLAKE,MARTIN 2

十一、多表数据记录查询：

在具体应用中如果需要实现多表数据记录查询，一般不使用连接查询，因为该操作效率比较低，而是使用子查询操作。进行连接操作时，会求笛卡尔积，如果表数据记录多，则可能会造成死机。

MYSQL在具体实现连接查询操作时，首先将两个或两个以上的表按照某个条件连接起来，然后再查询到所要求的数据记录。

连接操作是关系数据库操作中专门用于数据库操作的关系运算，包括并（UNION）、笛卡尔积（CARTESIAN PRODUCT）和专门针对数据库操作的运算-连接（JOIN）。

并（UNION）：把具有相同字段数目和字段类型的表合并到一起。

笛卡尔积（CARTESIAN PRODUCT):是没有连接条件表关系返回的结果。

eg：表A：字段数目为m，行数为k。表B字段数目为n，行数为l。则结果表字段数目为m+n,行数为k*l.

连接（JOIN）：在表关系的笛卡尔积数据记录中，按照相应字段值的比较条件进行选择生成一个新的关系。

连接分为内连接(inner join) 、外连接(outer join) 和交叉连接(cross join)。

1.内连接：在表关系的笛卡尔积数据记录中，保留表关系中所有匹配的数据记录，舍弃不匹配的数据记录。

按照匹配的条件可以分成自然连接、等值连接和不等连接。

自然连接：在表关系的笛卡尔积中，首先根据表关系中相同名称的字段自动进行记录匹配，然后去掉重复的字段。

eg：表A 有字段名a,b;表B也有字段名a,b。则自然连接时，先求A和B的笛卡尔积，然后匹配A.a=B.a and A.b = B.b的记录，不符合该条件的全部舍弃。笛卡尔积中，A.a A.b B.a B.b都会显示，共4个字段，但自然连接中，只显示a,b2个字段。

特点:1)在具体执行自然连接时，会自动判断相同名称的字段，然后进行数据值的匹配。

2)在执行完自然连接的新关系中，虽然可以指定包含哪些字段，但是不能指定执行过程中的匹配条件，即哪些字段的值进行匹配。

3)在执行完自然连接的关系中，执行过程中所有匹配的字段名只有一个，即会去掉重复字段。

等值连接：在表关系的笛卡尔积中，选择所匹配字段值相等的数据记录。

特点：1)使用=指定匹配条件；

2)在新关系中，不会去掉重复字段。

不等连接：在表关系的笛卡尔积中，选择所匹配字段值不相等的数据记录。

特点：1)使用!=指定匹配条件；

2)在新关系中，不会去掉重复字段。

2.外连接:在表关系的笛卡尔积数据记录中，不仅保留表关系中所有匹配的数据记录，而且还会保留部分不匹配的数据记录。

按照保留不匹配条件数据记录来源可以分为左外连接(left outer join)、右外连接(right outer join)和全外连接(full outer join)。

左外连接：在表关系的笛卡尔积中，除了选择相匹配的数据记录，还包含关联左边表中不相匹配的数据记录。

右外连接：在表关系的笛卡尔积中，除了选择相匹配的数据记录，还包含关联右边表中不相匹配的数据记录。

全外连接：在表关系的笛卡尔积中，除了选择相匹配的数据记录，还包含关联左右两边表中不相匹配的数据记录。

3.交叉连接：不带where子句的连接，返回的就是笛卡尔积数据记录。

总结：

表A和B

UNION： A并B

inner join: A交B 根据交的条件分为：自然连接、等值连接、不等值连接。使用的是比较运算符进行匹配条件的判断。

outer join:

左外连接：A并(A交B)

右外连接：(A交B)并B

全外连接:A并(A交B)并B

cross join: A乘B 十字交叉，笛卡尔积

语法:

内连接：