《MySQL数据库应用从入门到精通(第二版)》学习笔记

《MySQL数据库应用从入门到精通(第二版)》学习笔记

颜色解释：绿色标注的字体，即为sql语句的语法格式

• MySQL数据库基本操作

创建数据库：CREATE DATABASE database_name;

查看数据库：SHOW  DATABASES;

选择数据库：USE database_name;

删除数据库：DROP DATABASE database_name;

• MySQL数据库中存储引擎和数据类型

存储引擎指定了表的类型，即如何存储和索引数据、是否支持事务等，同时存储引擎也决定了表在计算机中的存储方式。

数据类型：整数类型、浮点数类型、定点数类型、位类型、日期和时间类型、字符串类型

查看MySQL支持的存储引擎：SHOW  ENGINES;

查看默认存储引擎：SHOW VARIABLES LIKE  ‘storage_engine%’;

查看MySQL帮助文档支持的目录列表：HELP CONTENTS;

• 表的操作

表中的数据库对象包含列、索引和触发器。其中：

• 列（Columns）:也称属性列，在具体创建表时，必须指定列的名字和数据类型；
• 索引（Indexes）：指根据指定的数据库表列建立起来的顺序，提供了快速访问数据的途径且可监督表的数据，使其索引所指向的列中的数据不重复；
• 触发器（Triggers）：指用户定义的事物命令的集合，当对一个表中的数据进行插入、更新或删除时这组命令就会自动执行，可以用来确保数据的完整性和安全性。

创建表语法形式：

CREATE TABLE table_name(

    属性名 数据类型，

    属性名 数据类型，

    ……

    属性名 数据类型

)

查看表定义：DESCRIBE table_name;

查看表详细定义：SHOW CREATE TABLE table_name;

1. 表的增删改查操作

删除表：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|AFTER 属性名2；

1. 操作表的约束

    所谓完整性是指数据的准确性和一致性，而完整性检查指检查数据的准确性和一致性。

完整性约束

完整性约束关键字	含义
NOT NULL	约束字段的值不能为空
DEFAULT	设置字段的默认值
UNIQUE KEY(UK)	约束字段的值是惟一的
PRIMARY KEY(PK)	约束字段为表的主键，可以作为该表记录的唯一标识
AUTO_INCREMENT	约束字段的值为自动增加
FOREIGN KEY(FK)	约束字段为表的外键

设置非空约束（NOT NULL,NK）:

CREATE TABLE table_name(

      属性名 数据类型 NOT NULL,

      …….

);

设置字段的默认值（DEFAULT）:

CREATE TABLE table_name(

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

……

);

设置唯一约束(UNIQUE,UK)：

CREATE TABLE table_name(

属性名 数据类型 UNIQUE,

……

);

设置主键约束（PRIMARY KEY,PK）——单字段主键

CREATE TABLE table_name(

属性名 数据类型 PRIMARY KEY,

……

);

设置主键约束（PRIMARY KEY,PK）——多字段主键

CREATE TABLE table_name(

属性名 数据类型 PRIMARY KEY,

……

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

);

设置字段值自动增加（AUTO_INCREMENT）

CREATE TABLE table_name(

属性名 数据类型 AUTO_INCREMENT,

……

);

设置外键约束（FOREIGN KEY,FK）

CREATE TABLE table_name(

属性名 数据类型 ,

属性名 数据类型 ,

……

CONSTRAINT 外键约束名 FOREIGN  KEY(属性名1)

    REFERENCES 表名(属性名2)

……

);

• 数据的操作

插入完整数据记录：

INSERT INTO table_name(field1,field2,……,fieldn)

      VALUES(value1,value2,……,valuen);

插入多条完整数据记录：

INSERT INTO table_name(field1,field2,……,fieldn)

      VALUES(value11,value12,……,value1n),

(value21,value22,……,value2n),

……

(valuen1,valuen2,……,valuenn);

更新数据记录：

UPDATE  table_name

    SET field1 = value1,

       Field2 = value2

    WHERE  条件;

删除数据记录：

    DELETE FROM table_name WHERE 条件；

• 单表数据记录查询

简单数据记录查询：

SELECT field1,field2,…,fieldn

     FROM table_name;

避免重复数据查询——DISTINCT

SELECT  DISTINCT field1,field2,…,fieldn

     FROM table_name;

修改查询出来的数据字段名——AS：

SELECT field1 AS otherfield1,,field2 AS otherfield2,…,fieldn AS otherfieldn

     FROM table_name;

1．条件数据记录查询

条件数据记录查询：

SELECT field1,field2,…,fieldn

     FROM table_name

WHERE  条件;

补充：关于条件查询语句可以包含如下功能：

• 带关系运算符和逻辑运算符的条件数据查询
• 带BETWEEN  AND 关键字、NOT  BETWEEN  AND的条件数据查询
• 带IS NULL关键字的条件数据查询
• 带IN关键字的条件数据查询
• 带LIKE关键字的条件数据查询

带BETWEEN AND 关键字的范围查询(符合范围)：

SELECT field1,field2,…,fieldn

     FROM table_name

WHERE  field BETWEEN values1 AND value2;

带BETWEEN AND 关键字的范围查询（不符合范围）：

SELECT field1,field2,…,fieldn

     FROM table_name

WHERE  field  NOT BETWEEN values1 AND value2;

带IS NULL关键字的空值查询：

SELECT field1,field2,…,fieldn

     FROM table_name

WHERE  field IS NULL;

带IS NOT NULL关键字的不是空值查询：

SELECT field1,field2,…,fieldn

     FROM table_name

WHERE  field IS NOT NULL;

带IN关键字的集合查询——在集合中：

SELECT field1,field2,…,fieldn

     FROM table_name

WHERE  field IN (value1,value2,…,valuen);

带IN关键字的集合查询——不在集合中：

SELECT field1,field2,…,fieldn

     FROM table_name

WHERE  field NOT  IN (value1,value2,…,valuen);

带LIKE关键字的模糊查询——模糊匹配(通配符：%（多个），_（单个），%%（全部）

SELECT field1,field2,…,fieldn

     FROM table_name

WHERE  field LIKE value;

带LIKE关键字的模糊查询——模糊不匹配

SELECT field1,field2,…,fieldn

     FROM table_name

WHERE  field  NOT LIKE value;

2．排序数据记录查询

    ASC(升序，默认)，DESC(降序)

排序数据记录查询语法：

SELECT field1,field2,…,fieldn

     FROM table_name

WHERE  条件

ORDER BY fileldm1 [ASC|DESC] [,fileldm2 [ASC|DESC];

3．限制数据记录查询数量

限制数据记录查询数量语法形式：

SELECT field1,field2,…,fieldn

     FROM table_name

WHERE  条件

LIMIT  offset_start,row_count;

4．统计函数和分组数据记录查询

统计函数：

• COUNT()函数：实现统计表中记录的条数；
• AVG()函数：实现计算字段值的平均值；
• SUM()函数：实现计算字段值的总和；
• MAX()函数：实现查询字段值的最大值
• MIN()函数：实现查询字段值的最小值。

对于MySQL所支持的统计函数，如果所操作的表中没有任何数据记录，则COUNT()函数返回数据0，而其他函数返回NULL。

分组数据查询：关键字GROUP BY

注意：GROUP BY单独使用时，默认查询出每个分组中随机一条记录，具有很大的不确定性。如果想显示每个分组中的字段，可以通过函数GROUP_CONCAT()函数来实现，该函数可以实现显示每个分组中的指定字段值。

WHERE关键字主要用来实现条件限制数据记录；

HIVING关键字来实现条件限制分组数据记录。

分组数据查询——实现统计功能分组查询：

SELECT GROUP_CONCAT(field)

     FROM table_name

WHERE  条件

GROUP BY field2;

分组数据查询——实现HAVING子句限定分组查询：

SELECT  function (field)

     FROM table_name

WHERE  条件

GROUP BY field1,field2,…,fieldn

HAVING 条件;

• 多表数据记录查询

1. 关系数据操作简介

关系数据操作中传统的运算——并（UNION）、笛卡尔积（CARTESIAN PRODUCT）和专门针对数据库操作的运算——连接（JOIN）。

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

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

连接（JOIN）就是在表关系的笛卡尔积数据记录中，按照相应字段值的比较条件进行选择生成一个新的关系。连接分为内连接（INNER JOIN）、外连接（OUTER JOIN）和交叉连接（CROSS JOIN）。

• 内连接（INNER JOIN）就是在表关系的笛卡尔积数据记录中，保留表关系中所有匹配的数据记录，舍弃不匹配的数据记录。按照匹配的条件可以分为自然连接、等值连接和不等连接。

1. 自然连接（NATURAL JOIN）:就是表关系的笛卡尔积中，首先根据表关系中相同名称的字段自动进行记录匹配，然后去掉重复的字段。
2. 等值连接：就是表关系的笛卡尔积中，选择所匹配字段值相等的数据记录。
3. 不等连接：就是表关系的笛卡尔积中，选择所匹配字段值不相等的数据记录。

• 外连接（OUTER JOIN）就是在表关系的笛卡尔积数据记录中，不仅保留表关系中所有匹配的数据记录，而且还会保留部分不匹配的数据记录。按照保留不匹配条件数据记录来源可以分为左外连接（LEFT OUTER JOIN）、右外连接（RIGHT OUTER JOIN）和全外连接（FULL OUTER JOIN）

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

1. 连接查询SQL语句

MySQL中内连接数据查询通过SQL语句“INNER JOIN … ON ”来实现，语法形式：

SELECT field1,field2,…,fieldn

     FROM join_tablename1 INNER JOIN join_tablename2 【INNER JOIN join_tablenamen】  ON  join_condition;

MySQL中外连接数据查询通过SQL语句“OUTER JOIN … ON ”来实现，语法形式：

SELECT field1,field2,…,fieldn

          FROM join_tablename1  LEFT|RIGHT|FULL [OUTER] JOIN join_tablename2

          ON  join_condition;

1. 合并查询数据记录SQL语句

在MySQL软件中实现查询数据记录合并通过SQL语句UNION来实现，语法如下：

SELECT field1,field2,…,fieldn

    FROM tablename1

UNION | UNION ALL

SELECT field1,field2,…,fieldn

    FROM tablename2

UNION | UNION ALL

SELECT field1,field2,…,fieldn

    FROM tablename3

……;

补充：UNION 和UNION ALL的区别：前者会去掉重复数据记录。

1. 子查询

连接查询实现多表数据查询的性能很差，因此出现了连接查询的替代者子查询。

所谓子查询，就是指在一个查询中嵌套了其他的若干查询，即在一个SELECT语句中的WHERE 或FROM子句中包含另一个SELECT查询语句。在查询语句中，外层SELECT查询语句称为主查询，WHERE子句中的SELECT查询语句被称为子查询，也被称为嵌套查询。子查询语句可以包含IN、ANY、ALL和EXISTS等关键字，除此之外，还可能包含比较运算符。

（1）理论上子查询可以出现在查询语句的任意位置，但实际中，子查询常出现在WHERE和FROM子句中。

• WHERE子句中的子查询：该位置处的子查询一般返回单行单列、多行单列、单行多列数据记录
• FROM子句中的子查询；该位置处的子查询一般返回多行多列数据记录，可以当作一张临时表

（2）关键字ANY用来表示主查询的条件为满足子查询返回查询结果中任意一条数据记录，该关键字有三种匹配方式，分别如下：

• =ANY：其功能与关键字IN一样
• >ANY(>=ANY)：比子查询中返回数据记录中最小的还要大于（大于等于）数据记录；
• <ANY(<=ANY)：比子查询中返回数据记录中最大的还要小于（小于等于）数据记录

（3）关键字ALL用来表示主查询的条件为满足子查询返回查询结果中所有数据记录，该关键字有两种匹配方式，分别如下：

• >ALL(>=ALL)：比子查询中返回数据记录中最大的还要大于（大于等于）数据记录；
• <ALL(<=ALL)：比子查询中返回数据记录中最小的还要小于（小于等于）数据记录

    （4）关键字EXISTS是一个布尔类型，当返回结果集时为TRUE，不能返回结果集时为FALSE。查询时EXISTS对外表采用遍历方式逐条查询，每次查询都会比较EXISTS的条件语句。

• 使用MySQL运算符

MySQL软件提供的运算符包含4种：

• 算术运算符：算术运算符包含：加（+）、减（-）、乘（*）、除（/）、求模（%、mod ）运算5种。
• 比较运算符：大于（>）、小于（<）、等于（=、<=>）、不等于（!=、<>）、大于等于（>=）、小于等于（<=）、存在于指定范围（BETWEEN AND）、为空（IS NULL）、存在于指定集合（IN）、通配符匹配（LIKE）、正则表达式匹配（REGEXP）
• 逻辑运算符：与（AND 、&&）、或（OR、||）、非（NOT、！）、异或（XOR）
• 位运算符：按位与（&）、按位或（|）、按位取反（~）、按位异或（^）、按位左移（<<）、按位右移（>>）

• 使用MySQL常用函数

字符串函数、数值函数、日期函数、系统信息函数。

1. 字符串函数

函数	功能
CANCAT(str1,str2,…strn)	连接字符串str1,str2,…,strn为一个完整字符串
INSERT(str,x,y,instr)	将字符串str从第x位置开始，y个字符长的子串替换为字符串instr
LOWER(str)	将字符串str中所有字符变为小写
UPPER(str)	将字符串str中所有字符变为大写
LEFT(str,x)	返回字符串str中最左边的x个字符
RIGHT(str,x)	返回字符串str中最右边的x个字符
LPAD(str,n,pad)	使用字符串pad对字符串str最左边进行填充，直到长度为n个字符长度
RPAD(str,n,pad)	使用字符串pad对字符串str最右边进行填充，直到长度为n个字符长度
LTRIM(str)	去掉字符串str左边的空格
RTRIM(str)	去掉字符串str右边的空格
REPEAT(str,x)	返回字符串str重复x次的结果
REPLACE(str,a,b)	使用字符串b替换字符串str中所有出现的字符串a
STRCMP(str1,str2)	比较字符串str1和str2
TRIM(str)	去掉字符串str行头和行尾的空格
SUBSTRING(str,x,y)	返回字符串str中从x位置起y个字符长度的字符串

1. 数值函数

函数	功能
ABS(x)	返回x的绝对值
CEIL(x)	返回大于x的最大整数值
FLOOR(x)	返回小于x的最大整数值
MOD(x,y)	返回x模y的值
RAND()	返回0~1内的随机数
ROUND(x,y)	返回数值x的四舍五入后有y位小数的数值
TRUNCATE(x,y)	返回数值x截断为y位的数值

1.  日期和时间函数

 

函数	功能
CURDATE()	获取当前日期
CURTIME()	获取当前时间
NOW()	获取当前的日期和时间
UNIX_TIMESTAMP(date)	获取UNIX时间戳的日期值
FROM_UNIXTIME()	获取UNIX时间戳的日期值
WEEK(date)	返回日期date为一年中的第几周
YEAR(date)	返回日期date的年份
HOUR(time)	返回时间time的小时值
MINUTE(time)	返回时间time的分钟值
MONTHNAME(date)	返回时间time的月份值

1. 系统信息函数

函数	功能
VERSION()	返回数据库的版本号
DATABASE()	返回当前数据库名
USER()	返回当前用户
LAST_INSERT_ID()	返回最近生成的AUTO_INCREMENT值

enjoy it!