MySQL数据库应用从入门到精通

Posted on | In | Visitors:

最近在看《MySQL数据库应用从入门到精通》这本书是基于MySQL5.5版本的，下面是看书过程记录下来的笔记。

数据库概述

SQL主要功能：数据定义语言（DDL）,数据操作语言（DML）,数据控制语言（DCL).

数据可基本操作

create Database database_name;创建数据库
SHOW DATABASES;查看数据库
USE database_name; 选择数据库
DROP DATABASE database_name;删除数据库
在执行SQL语句中，可以用”;”、”\g”、”\G”符号表示语句结束。其中前两个符号的作用一样，而最后一个符号除了表示语句结束外，还可以使得结果显示的更加美观。

mysql中的存储引擎和数据类型

存储引擎

查询存储引擎

存储引擎是MySQL数据库管理系统的一个重要特征，在具体开发时，为了提高MySQL数据库管理系统的使用效率和灵活性，可以根据实际需要来选择存储引擎。因为存储引擎指定了表的类型，即如何存储和索引数据、是否支持事物等，同时存储引擎也决定了表在计算机中的存储方式。
SHOW ENGINES;显示支持的存储引擎
show variables like '%storage_engine%';当前默认的存储引擎:

选择存储引擎

下图是3种常用的存储引擎介绍。
存储引擎特性
上面主要介绍了MyISAM、InnoDB和MEMORY三种存储引擎特性的对比，接下来将详细介绍这3个存储引擎的应用场合并给出相应的建议。

  • MyISAM:由于该存储引擎不支持事务、也不支持外键，所以访问速度比较快。因为此对事务完整性没有要求并以访问为主的应用适用于该存储引擎。
  • InnoDB:由于该存储引擎在事务上具有优势，即支持具有提交、回滚和崩溃恢复能力的事务安装，所以比MyISAM存储引擎占用更多的磁盘空间。因此需要进行频繁的更新、删除操作，同时还对事务的完整性要求比较高，需要实现必发控制，此时适用适用该存储引擎。
  • MEMORY:该存储引擎使用内存来存储数据，因此该存储引擎的数据访问速度比较快，但是安全上没有保障。如果应用中涉及数据比较小，需要进行快速访问，则适用使用该存储引擎。

关于图形化的存储引擎修改这里不做记录，下面讲解通过修改my.ini配置来配置存储引擎。
my.ini配置文件，关于“[mysqld]”组的内容
如果想要修改默认存储引擎，只需修改[mysqld]组中的default-storage-engine参数。如：default-storage-engine=MyISAM
注意：如果修改参数后，需重启MySQL服务才能生效

数据类型

整形

整型具体特性如下图：

浮点数类型、定点数类型和位类型

当需要精确到小数点后10位以上是，需要选择DOUBLE类型。当需要小数数据精确度非常高时，则可以选择DEC和DECIMAL类型，它们的精确度比DOUBLE类型还要高。FLOAT、DOUBLE数据类型存储数据是存储的是近似数，而DECIMAL存储的是字符串，因此提供了更高的精度。
浮点数类型具体特性如下图：

定点数类型具体特性如下图：

FLOAT数据类型和DECIMAL数据类型的区别如下：

结果为：
位类型具体类型如下：

BIT类型的使用

日期和时间类型

日期和时间类型具体特性如下图：

字符串类型

CHAR系列字符串类型具体特性如下：

TEXT系列字符串类型具体特性如下：

BINARY系列字符串类型具体特性如下：

BINARY可以存储二进制数据（例如图片、音乐或者视频文件），而后者只能存储字符数据。如果二进制数据长度经常变化则选择VARBINARY类型，否则选择BINARY。
BLOB系列字符类型具体特性如下：

上面的四种类型与TEXT系列字符串类型非常类似，不同的是，前者可以存储二进制数据（例如图片、音乐或者视频文件），而后者只能存储字符数据。如果需要存储电影等视频文件时则根据实际选择合适长度的BLOB类型

表的操作

表的基本概念

表是包含数据库中所有数据的数据库对象，表中的数据库对象包含列、索引和触发器。

表的基本语法

使用USE database_name;进入要操作的数据库。

创建表的语法形式

可以使用下面几种语法查看表定义

DESCRIBE table_name;
SHOW CREATE TABLE table_name;
DESC table_name;

删除表

DROP TABLE table_name;

修改表

ALTER TABLE old_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;修改字段的顺序

MySQL支持的完整性约束

所谓完整性是指数据的准确性和一致性，而完整性检查就是指检查数据的准确性和一致性。MySQL数据库管理系统提供了一致机制来检查数据库表中的数据是否满足规定的条件，以保证数据库中数据的准确性和一致性，这种机制就是约束。
完整性约束如下图：
CREATE TABLE table_name( 属性名 数据类型 NOT NULL, ...... );非空约束（NOT NULL,NK）
CREATE TABLE table_name( 属性名 数据类型 DEFAULT 默认值, ...... );默认值（DEFAULT）
CREATE TABLE table_name( 属性名 数据类型 UNIQUE, ...... );唯一约束（UNIQUE,UK）
CREATE TABLE table_name( 属性名 数据类型 PRIMARY KEY, ..... );单字段主键（PRIMARY KEY,PK）
CREATE TABLE table_name( 属性名 数据类型,.....[CONSTRAINT 约束名]PRIMARY KEY(属性名,属性名......) );多字段主键（PRIMARY KEY,PK）
CREATE TABLE table_name( 属性名 数据类型 AUTO_INCREMENT, ..... );自动增加（AUTO_INCREMENT）
CREATE TABLE table_name( 属性名 数据类型, 属性名 数据类型, ..... CONSTRAINT 外键约束名 FOREIGN KEY (属性名) REFERENCES 表名 （属性名2） );外键约束（FOREIGN KEY,FK）
注意：如果想给字段上的约束设置一个约束名字，可以执行SQL语句CONSTRAINT.例如：CONSTRAINT 约束名字 约束 (属性名);

索引的操作

数据库对象索引其实与书的目录非常类似，主要是为了提高从表中检索数据的速度。
InnoDB和MyISAM存储引擎支持BTREE类型索引，MEMORY存储引擎支持HASH类型索引，默认为前者索引。
MySQL支持6种索引，分别为普通索引、唯一索引、全文索引、单列索引、多列索引和空间索引。

创建和查看索引

ASC参数用来指定为升序排序，DESC参数用来指定为降序排序

普通索引

创表时创建普通索引如下：

1
2
3
4
5
6
7
CREATE TABLE table_name(
  属性名 数据类型,
  属性名 数据类型,
  ......
  属性名 数据类型,
  INDEX|KEY [索引名] (属性名1 [(长度)] [ASC|DESC])
);

CREATE INDEX 索引名 ON表名(属性名 [(长度)] [ASC|DESC]);在已经存在的表上创建普通索引
ALTER TABLE table_name ADD INDEX|KEY 索引名 （属性名 [(长度)] [ASC|DESC]);通过SQL语句ALTER TABLE 创建普通索引

唯一索引

注意：explain用法EXPLAIN tbl_name或EXPLAIN [EXTENDED] SELECT select_options前者可以得出一个表的字段结构等等，后者主要是给出相关的一些索引信息，而今天要讲述的重点是后者。
创建表时创建唯一索引如下：

1
2
3
4
5
6
7
CREATE TABLE table_name(
  属性名 数据类型,
  属性名 数据类型,
  ......
  属性名 数据类型,
  UNIQUE INDEX|KEY [索引名] （属性名1 [(长度)] [ASC|DESC])
);

CREATE UNIQUE INDEX 索引名 ON表名(属性名 [(长度)] [ASC|DESC]);在已经存在的表上创建唯一索引
ALTER TABLE table_name ADD UNIQUE INDEX|KEY 索引名 （属性名 [(长度)] [ASC|DESC]);通过SQL语句ALTER TABLE 创建唯一索引

全文索引

创建表时创建全文索引如下：

1
2
3
4
5
6
7
CREATE TABLE table_name(
  属性名 数据类型,
  属性名 数据类型,
  ......
  属性名 数据类型,
  FULLTEXT INDEX|KEY [索引名] （属性名1 [(长度)] [ASC|DESC])
  );

CREATE FULLTEXT INDEX 索引名 ON表名(属性名 [(长度)] [ASC|DESC]);在已经存在的表上创建全文索引
ALTER TABLE table_name ADD FULLTEXT INDEX|KEY 索引名 （属性名 [(长度)] [ASC|DESC]);通过SQL语句ALTER TABLE 创建全文索引

多列索引

创建表时创建全文索引如下：

1
2
3
4
5
6
7
8
9
CREATE TABLE table_name(
 属性名 数据类型,
 属性名 数据类型,
 ......
 属性名 数据类型,
 INDEX|KEY [索引名] （属性名1 [(长度)] [ASC|DESC], 
                     ......
                     属性名n [(长度)] [ASC|DESC])
  );

在已经存在的表上创建多列索引如下：

1
2
3
4
5
CREATE INDEX 索引名
   ON 表名（属性名1 [(长度)] [ASC|DESC],
      ......
          属性名n [(长度)] [ASC|DESC]
   ）;

通过SQL语句ALTER TABLE创建多列索引

1
2
3
4
5
ALTER TABLE table_name(
  ADD INDEX|KEY 索引名（属性名1 [(长度)] [ASC|DESC],
                       ......
                      属性名n [(长度)] [ASC|DESC]
   ）;

删除索引

DROP INDEX index_name ON table_name;删除索引

视图的操作

视图的特点

特点如下：

  • 视图的列可以来自不同的表，是表的抽象和在逻辑意义上建立的新关系。
  • 视图是由基本表（实表）产生的表（虚表）。
  • 视图的建立和删除不影响基本表。
  • 对视图内容的更新（添加、删除和修改）直接影响基本表。
  • 当视图来自多个基本表是，不允许添加和删除数据。

创建视图

CREATE VIEW view_name AS 查询语句;创建视图
USE view;使用视图

常量视图

具体语句如下：

1
2
3
CREATE VIEW view_test1
  AS 
    SELECT 3.1415926;

封装使用聚合函数（SUM、MIN、MAX、COUNT等）查询语句的视图

具体语句如下：

1
2
3
4
CREATE VIEW view_test2
   AS 
     SELECT COUNT(name)
     FROM database_name;

封装了实现功能（ORDER BY）查询语句的视图

具体语句如下：

1
2
3
4
5
CREATE VIEW view_test3
  AS
    SELECT name
       FROM table_name
         ORDER BY id DESC;

封装了实现表内连接查询语句的视图

具体语句如下：

1
2
3
4
5
CREATE VIEW view_test4
  AS 
   SELECT s.name
     FROM t_student as s,t_group as g
       WHERE s.group_id=g.id AND g.id=2;

封装了实现表外连接（LEFT JOIN 和RIGHT JOIN）查询语句视图

具体语句如下：

1
2
3
4
5
CREATE VIEW view_test5
  AS 
    SELECT s.name 
       FROM t_student as s LEFT JOIN t_group as g ON s.group_id=g.id
        where g.id=2;

封装了实现子查询相关查询语句的视图

具体语句如下：

1
2
3
4
5
CREATE VIEW view_test6
 AS 
 SELECT s.name
 FROM t_student AS s
     WHERE s.grop_id IN(SELECT id FROM t_group);

封装了实现记录联合（UNION和UNION ALL）查询语句的视图

具体语句如下：

1
2
3
4
5
CREATE VIEW view_test7
  AS
    SELECT id,name FROM t_student
    UNION ALL
    SELECT id,name FROM t_group;

查看视图

USE dababase_name; SHOW TABLES;查看视图名
USE database_name; SHOW CREATE VIEW viewname;查看视图定义信息
USE database_name; DESCRIBE | DESC viewname;查看视图设计信息
SHOW TABLE STATUS [FROM db_name] [LIKE 'pattern'];查看视图（和表）详细信息
SHOW TABLE STATUS返回字段含义如下图：

删除视图

USE database_name; DROP VIEW view_name [,view_name] ...;删除视图

修改视图

CREATE OR REPLACE VIEW语句修改视图
1
2
3
4
5
`USE database_name;
 CREATE OR REPLACE VIEW view_selectproduct
    AS
       SELECT name
            FROM  t_products;
ALTER 语句修改视图
1
2
3
4
5
`USE database_name;
 ALTER VIEW view_selectproduct
    AS
       SELECT name
            FROM  t_products;

利用视图操作基本表

检索（查询）数据

SELECT * FROM view_name;

利用视图操作基本表数据
  • 对视图数据进行添加、删除和更新操作直接影响基本表。
  • 视图来自多个基本表时，不允许添加和删除数据。

添加数据操作

1
2
INSERT INTO view_name(属性1,属性2，属性3...)
 VALUES(value1,value2,value3...);

删除数据操作

1
2
DELETE FROM view_name
   WHERE name=’属性名‘;

更新数据操作

1
2
3
UPDATE view_name
 set 属性1=value
    WHERE 属性2=value；

触发器的操作

在具体的应用中，之所以经常使用触发器数据对象，是由与该对象能够加强数据库表中数据的完整性约束和业务规则等。

创建有一条执行语句的触发器

1
2
3
create trigger trigger_name
   BEFORE|AFTER trigger_EVENT
      ON TABLE_NAME FOR EACH ROW trigger_STMT;

创建包含多条执行语句的触发器

1
2
3
4
5
6
create trigger trigger_name
   BEFORE|AFTER trigger_EVENT
    ON TABLE_NAME FOR EACH ROW
     BEGIN
     trigger_STMT
     END;

查看触发器

SHOW TRIGGERS;语句查看触发器
通过查看系统表triggers实现查看触发器，如下

1
2
USE information_schema;
SELECT * FROM triggers (WHERE TRIGGER_NAME='trigger_name') \G

删除触发器

DROP TRIGGER trigger_name;删除触发器

数据操作

插入数据

插入一部分数据
1
2
INSERT INTO table_name(field1,field2,field3,......fieldn)
  VALUES(value1,value2,value3......valuen);
插入完整数据
1
2
INSERT INTO table_name
  VALUES(value1,value2,value3......valuen);
插入多天数据
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
#插入多条部分数据
INSERT INTO table_name(field1,field2,field3,...fieldn)
  VALUES(value11,value21,value31...valuen1),
        (value11,value21,value31...valuen1),
        (value11,value21,value31...valuen1),
         ......
        (value1m,value2m,value3m...valuenm);
#插入多条完整数据
INSERT INTO table_name
  VALUES(value11,value21,value31...valuen1),
        (value11,value21,value31...valuen1),
        (value11,value21,value31...valuen1),
         ......
        (value1m,value2m,value3m...valuenm);
插入查询结果
1
2
3
4
INSERT INTO table_name1(field11,field12,field13,...field1n)
    SELECT (field21,field22,field23,...field2n)
        FROM table name2
          WHERE ...

更新数据记录

1
2
3
4
5
UPDATE table_name
  SET field1=value1,
      field2=value2,
      field3=value3,
  WHERE CONDITION

删除特定数据记录

1
2
DELETE FROM table_name
WHERE CONDITION

单表数据记录查询

简单数据查询

一般查询

SELECT field1 field2 ...fieldn FROM table_name; 查询指定字段数据
SELECT * FROM table_name; 查询所有字段数据
SELECT DISTINCT field1 field2 ...fieldn FROM table_name;避免数据重复查询–DISTINCT

实现数学四则运算数据查询

mysql支持的关系运算符如下：

1
2
3
4
5
SELECT field1 [AS] otherfield1,field2 [AS] otherfield2,...fieldn [AS] otherfieldn
FROM table_name
#例如：
SELECT ename,sal*12 [AS] yearsalary
FROM t_employee;

设置显示格式数据查询

如下显示的是以固定的格式（ename雇员的年薪为：sal）显示查询到数据

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

结果为：

条件数据记录查询

条件查询的语法格式

1
2
3
SELECT field1、field2 ...field3
 FROM table_name
 WNERE CONDITION;

带关系运算符和逻辑运算符的条件数据查询


带（not）between value1 and value2查询（不）符合范围之内
IS （NOT） NULL查询是（不是）空值
带IN关键字的集合查询
注意：IN查询时，查询集合中包括NULL,则不会影响结果；NOT IN查询时，查询集合中包括NULL,则查询不到结果。

1
2
3
4
SELECT field1 field2 ...fieldn
FROM table_name
WHERE field IN(value1,value2,value3,...,valuen);//在集合中
WHERE field NOT IN(value1,value2,value3,...,valuen);//不在集合中

带LIKE关键字模糊查询

1
2
3
SELECT field1 field2 ...fieldn
FROM table_name
WHERE field [NOT] LIKE value;

LIKE支持的通配符如下：

  • “_”通配符，该通配符值能匹配单个字符
  • “%”通配符，该通配符可以匹配任意长度单位字符串，可以0个字符、1个或者更多

排序数据记录查询

1
2
3
4
SELECT field1 field2 ...fieldn
FROM table_name
WHERE CONDITION
ORDER BY fieldm1 [ASC|DESC] [,fieldm2 [ASC|DESC],];

限制数据记录查询数量

1
2
3
4
5
SELECT field1 field2 ...fieldn
FROM table_name
 where CONDITION
 LIMIT OFFSET_START,ROW_COUNT;
#OFFSET_START为初始位置，可以选择不指定数据。

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

  • COUNT()：记录条数。
  • AVG()：平均值。
  • SUN():总和。
  • MAX():最大值。
  • MIN():最小值。

注意：如果操作表中没有数据，则COUNT()函数返回为0，其它返回为NULL.

分组数据查询

分组查询的简单格式：

1
2
3
4
SELECT function()
  FROM table_name
  WHER CONDITION
   GROUP BY field;

下面为分组查询不同情况：

  • function()为*时是简单分组查询
  • function()为GROUP_CONCAT(field)时实现统计功能分组查询，如下：
1
2
3
SELECT deptno,GROUP_CONCAT(ename),COUNT(ename) number
 FROM t_employee
 GROUP BY deptno;
  • 多字段分组查询
1
2
3
4
SELECT GROUP_COUNT(field),function(field)
FROM table_name
WHERE CONDITION
GROUP BY field1,field2,...fieldn;
  • 实现HAVING字句限定分组查询
1
2
3
4
5
6
7
8
9
10
11
SELECT function(field)
FROM table_name
WHERE CONDITION
GROUP BY field1,field2,...fieldn
HAVING CONDITION;
#例如：
SELECT deptno,AVG(sal) average,GROUP_CONCAT(ename) ename,COUNT(ename) number
FROM t_employee
GROUP BY deptno
HAVING AVG(sal)>2000;

自定义排序

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
原表 user：
id  name   roleId
1    aaa     1
2    bbb     2
3    ccc     3
4    ddd     4
5    eee     5
- MySQL可以通过field()函数自定义排序，格式：field(value,str1,str2,str3,str4)，value与str1、str2、str3、str4比较，返回1、2、3、4，如遇到null或者不在列表中的数据则返回0.
例如：select * from user order by field(roleId,2,3,4,1,5);
结果：
id  name   roleId
2    bbb     2
3    ccc     3
4    ddd     4
1    aaa     1
5    eee     5
- locate（substr,str）函数返回子串substr在字符串str中第一次出现的位置，可以根据该函数进行排序
例如：select * from user order by locate(id,'2,3,1,5,4');
结果：
id  name   roleId
2    bbb     2
3    ccc     3
1    aaa     1
5    eee     5
4    ddd     4

多表数据记录查询

关系数据库操作

并（UNION）

UNION指令的目的是将两个SQL语句的结果合并起来.UNION的一个限制是两个SQL语句所产生的栏位需要是同样的资料种类。另外，当我们用UNION这个指令时，我们只会看到不同的资料值（类似于SELECT DISTINCT），而UNION ALL会将每一笔符合条件的资料都显示出来，无论资料有没有重复。

1
2
3
SELECT DATA FROM database1
UNION ALL
SELECT DATA FROM database2;

笛卡尔积

SELECT *FROM table1 CROSS JOIN table2;交叉连接
笛卡尔积在SQL中的实现方式既是交叉连接(Cross Join)。所有连接方式都会先生成临时笛卡尔积表，笛卡尔积是关系代数里的一个概念，表示两个表中的每一行数据任意组合.

内连接(INNER JOIN)

内连接语法形式：

1
2
3
SELECT field1 field2...fieldn
FROM join_tablename1 INNER JOIN join_tablename2 [INNER JOIN join_tablename]
ON join_condition;

自然连接

在表关系的笛卡尔积中，首先根据表关系中相同名称的字段自动进行记录匹配，然后去掉重复的字段。
是一种特殊的等值连接，它要求两个关系进行比较的分量必须是相同的属性组，并且在结果集中将重复属性列去掉。
一个简单的例子，将下列关系R和S进行自然连接：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT *
from R natural join S;
R：
A    B     C
a    b     c
b    a     d
c    d     e
d    f     g
S：
A    C    D
a    c    d
d    f    g
b    d    g

首先要对两个关系中相同属性组的分量进行比较，即比较R.A，R.C和S.A,S.C。 显然在R中只有第一行和第二行满足条件，因此进行连接得到结果：

1
2
3
A	B	C	D
a	b	c	d
b	a	d	g

等值连接
表关系的笛卡尔积中，选择所匹配字段值相等（=符号）的数据记录。
下面为等值连接与自然连接的区别：

  • 等值连接中不要求相等属性值的属性名相同，而自然连接要求相等属性值的属性名必须相同，即两关系只有在同名属性才能进行自然连接。
  • 等值连接不将重复属性去掉，而自然连接去掉重复属性，也可以说，自然连接是去掉重复列的等值连接。
    一个简单的例子，将下列关系R和S进行等值连接：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
select *
from R inner join S
ON  R.B=S.C;
R：
A    B     C
a    b     c
b    a     d
c    d     e
d    f     g
S：
A    C    D
a    c    d
d    f    g
b    d    g

比较R.B=S.C。 显然在R中只有第一行和第二行满足条件，因此进行连接得到结果：

1
2
3
R.A	R.B	R.C	S.A S.C S.D
c       d       e     b    d   g
d       f       g     d    f   g

不等查询
表关系的笛卡尔积中，选择所匹配字段不相等的数据操作。
内连接查询中的不等连接，就是在关键字ON后的匹配条件中通过除了等于关系运算符来实现不等条件外，可以使用的关系运算符包含”>””>=””<””<=”和”!=”等运算符号。
一个简单的例子，将下列关系R和S进行不等值连接：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
select *
from R inner join S
ON  R.B!=S.C AND R.A!="c";
R：
A    B     C
a    b     c
b    a     d
c    d     e
d    f     g
S：
A    C    D
a    c    d
d    f    g
b    d    g

比较R.B=S.C 和 R.A!=”c”。 显然在R中只有第一行和第二行满足条件，因此进行连接得到结果：

1
2
R.A	R.B	R.C	S.A S.C S.D
c       d      e      b    d   g

外连接

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

一个简单的例子，为外连接的各种情况：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
a表     id   name     
        1   张3            
        2   李四                 
        3   王武           
b表     id jod parent_id
1 23 1
2 34 2
3 34 4
  左连接   
  select   a.*,b.*   from   a   left   join   b     on   a.id=b.parent_id       
  结果是     
  1   张3                   1     23     1   
  2   李四                  2     34     2   
  3   王武                  null   
 
 右连接   
  select   a.*,b.*   from   a   right   join   b     on   a.id=b.parent_id       
  结果是     
  1   张3                    1     23     1   
  2   李四                   2     34     2   
  null                      3     34     4   
    
 完全连接   
  select   a.*,b.*   from   a   full   join   b     on   a.id=b.parent_id   
  结果是     
  1   张3                  1     23     1   
  2   李四                 2     34     2   
  null               　　  3     34     4   
  3   王武                 null
合并查询记录结果

语法如下：

1
2
3
4
5
6
7
8
9
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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
- 单行子查询
        select ename,deptno,sal
        from emp
        where deptno=(select deptno from dept where loc='NEW YORK')；
- 多行子查询
        SELECT ename,job,sal
        FROM EMP
        WHERE deptno in ( SELECT deptno FROM dept WHERE dname LIKE 'A%')；
- 多列子查询
        SELECT deptno,ename,job,sal
        FROM EMP
        WHERE (deptno,sal) IN (SELECT deptno,MAX(sal) FROM EMP GROUP BY deptno)；
- 内联视图子查询
       (1)SELECT ename,job,sal,rownum
          FROM (SELECT ename,job,sal FROM EMP ORDER BY sal)；
       (2)SELECT ename,job,sal,rownum
          FROM ( SELECT ename,job,sal FROM EMP ORDER BY sal)
          WHERE rownum<=5；
  
- 在HAVING子句中使用子查询
        SELECT deptno,job,AVG(sal) FROM EMP GROUP BY deptno,job HAVING AVG(sal)>(SELECT sal FROM EMP WHERE ename='MARTIN')； ·

使用MySQL运算符

算术、比较、逻辑和位运算符

算术运算符

如下图：
注意：”>”、”<”、”>=”和”=<”比较运算符不能操作NULL(空值)。

比较运算符

如下图：
特殊功能运算符（实现正则表达式匹配的需要REGEXP）：
如下图：
如下图：

逻辑运算符

如下图：

位运算符

MySQL常用的函数

使用字符串函数

合并字符串函数

CONCAT(S1,S2...SN)
CONCAT_WS(SEP,S1,S2...SN) SEP为分隔符可以为一个字符串，也可以为其他参数，如果为NULL时返回NULL，其它位置的NULL则可忽略;

比较字符串大小函数STRCMP()

STRCMP(str1,str2);str1>str2为1，str1< str2为-1，str1=str2为0

获取字符串的长度函数LENGTH()和字符数函数CHAT_LENGTH()

LENGTH(str);参数str的长度
CHAR_LENGTH(str);参数str的字符数

大小写转换

UPPER(S)或者UCASE(S)转换为大写
LOWER(S)或者LCASE(S)转换为小写

查找字符串位置

FIND_IN_SET(str1,str2);返回字符串str2中与str1相匹配的字符串位置，str2包含若干个用逗号隔开的字符串。
FIELD(str,str1,str2...);返回一个与字符串str匹配的位置（str1位置为1开始）

返回字符串相匹配的开始位置

LOCATE(str1,str)、POSITION(str1 IN str)和INSTR(str,str1)在str中返回str1的开始位置。

从现有字符串中截取子字符串

LEFT(str ,num)返回字符串中str中包含前num个字母（从左边数）的字符串。
RIGHT(str ,num)返回字符串中str中包含后num个字母（从左边数）的字符串。

去除字符串的首尾空格

LTRIM(str);去除字符串开始处空格
RTRIM(str);去除字符串结束处空格
TRIM(str);去除字符串首尾空格

替代字符串

INSERT(str,pos,len,newstr);字符串中的pos位置开始长度为len的字符串用字符串newstr来替换。如果参数pos的值超过字符串的长度，这返回值为原始字符串str.如果len的长度大于原来字符串（str)中所剩字符串的长度，则从位置pos开始进行全部替换。若任何一个参数为NULL，则返回Null.
REPLACE(str,substr,newstr);字符串substr用字符串newstr来替代。

使用数值函数

常用的数值函数：

0~1随机数

RAND();完全随机
RAND(X);X相同时返回相同的值

整数随机数

CEIl(X)或CEILING(X);大于或者等于数值X的最小整数
FLOOR(X);小于或者等于数值X的最大整数

截取数值函数

TRUNCATE(x,y);返回数值x保留到小数点后y位的值，y可以为负数。

四舍五入

ROUND(x,y); 返回数值x保留到小数点后y位的值，在具体截取数值是需要进行四舍五入的操作。单没有y时默认为四舍五入到整数。

使用日期和时间函数

常用的时间日期函数：

获取当前时间

当前时间
NOW(),CURRENT_TIMESTAMP(),LOCALTIME(),SYSDATE();2017-03-20 17:38:50
CURDATE(),CURRENT_DATE();2017-05-02
CURTIME(),CURRENT_TIME();16:29:59
UNIX_TIMESTAMP(NOW());unix格式，可以不用参数表示当前时间，从1970年1月1号开始计算，以秒为单位。
FROM_UNIXTIME(UNIX_TIMESTAMP(NOW()));把NUIX格式转换为普通格式同NOW()格式。
UTC_DATE();UTC日期 2012-05-22
UTC_TIME();UTC时间 13:00:01

获取时间和日期的各个部分
获取时间和日期的各个部分的功能如下：

关于月份：
MONTH(NOW());月
MONTHNAME(NOW());月
关于星期：

关于天的函数：
DAYOFYEAR(NOW());年中第几天
DAYOFMONTH(NOW());月中第几天
获取指定值得EXTRACT()函数
EXTRACT(tye FROM date);函数会从时间和日期参数中获取指定类型type的值。关于type参数的值可以是YEAR、MONTH、DAY、HOUR、MINUTE和SECOND。

计算日期和时间函数
TO_DAYS(date);从0000年1月1日开始算起多少天。
FROM_DAYS(TO_DAYS(NOW()));一段时间后日期和时间。
DATEDIFF(DATE1,DATE2);日期参数之间的相隔天数。
ADDTIME(DATE,n);date加上n秒后的时间。
SUBTIME(DATE,n);date减上n秒后的时间。
ADDDATE(DATE,n);date加上n天后的日期。
SUBDATE(DATE,n);date减上n天后的日期。
关于ADDDATE()和SUBDATE()函数另外用法：
ADDDATE(d,INTERVAL expr type);日期d加上一段时间后的日期，expr决定时间的长度，type决定了操作对象。
SUBDATE(d,INTERVAL expr type);日期d减去一段时间后的日期，expr决定时间的长度，type决定了操作对象。
参数type类型：

例子如下：

使用系统信息函数

常用系统信息函数

其它函数

流程函数

如下：

特殊函数

如下：

存储过程和函数的操作

存储过程和函数的执行效率要比在程序中拼sql语句的执行效率要高。
存储过程与函数区别：
函数必须有返回值，而存储过程则没有，存储过程的参数类型远远多于函数。

创建存储过程和函数

创建存储过程语法形式

如下图：

创建函数语法形式

创建简单的存储过程和函数

存储过程简单sql语句：

1
2
3
4
5
6
7
8
9
10
USE company;
DELIMITER $$
CREATE PROCEDURE proce_employee_sal()
COMMENT'查询所有雇员的工资'
BEGIN 
    SELECT sal
    FROM t_employee;
END$$
DELIMITER ;

函数简单SQL语句：

1
2
3
4
5
6
7
8
9
10
11
12
USE company;
DELIMITER $$
CREATE FUNCTION func_employee_sal(empno INT(11))
    RETURNS DOUBLE(10,2)
COMMENT'查询所有雇员的工资'
BEGIN 
   RETURN (SELECT sal
    FROM t_employee
    WHERE t_employee.empno=empno);
END$$
DELIMITER ;

MySQL事务

当多个用户访问同一份数据，一个用户在更改数据的过程中可能有其他客户同时发起更改请求，为了保证数据库记录的更新从一个一致性状态更改为另外一个一致性状态，使用事务处理是非常必要的，事务有以下4个特性：

  • 原子性（Atimicity):事务中所有的操作视为一个原子单位，即对事务所进行的数据修改等操作只能完全提交或者完全回滚。
  • 一致性（Consistency):事务在完成时，必须是所有的数据从一种一致性状态变更为另外一种一致性状态，所有的变更都必须应用于事务的修改，以确保数据的完整性。
  • 隔离性（ISOLATION):一个事务中的操作语句所做的修改必须与其他事务所做的修改相隔离。
  • 持久性（Durability):事务完成后，所做的修改对数据的影响是永久的，即使系统重启或者出现系统故障数据仍可以恢复。

MySQL数据库维护和性能提高

MySQL数据库性能优化

建议如下：

  • MySQL软件具有特定的硬件建议，在具体安装和使用数据库软件时，该软件所依托的计算机服务器最好能够遵循这些硬件建议。一般来说MySQL软件应该运行在自己的专用计算机服务器上。
  • MySQL软件安装成功后，会进行一系列的默认配置，这些配置开始通常是比较适合的，但是一段时间后，就需要调整内存分配、缓存区大小。可以通过执行SHOW VARIABLES 和SHOW STATES来实现。
  • MySQL软件是一个多用户多线程的数据库管理系统，对于该类型的服务器，经常会同时执行多个任务。如果这些任务中的某一个执行缓慢，则其他所有任务都会执行缓慢。为了解决这个问题，可以通过执行SQL语句show processlist显示所有的活动进程，或者通过执行kill命令终结消耗太多资源的进程。
  • 通过SELECT语句实现多表查询时，应该多次试验连接和子查询等各种方式，找出最佳的方式。在具体判断select语句执行性能时，可以通过explain查看select语句的执行情况。
  • 使用数据储存过程比一条一条执行语句速度要快许多。
  • 不要查找比需求还要多的数据内容，换言之，不要执行“select *”语句，除非要真正需要查询所有字段。
  • 通过UNION关键字连接的select语句，替代包含一系列复杂OR条件的SELECT语句，可以极大的改进性能。
  • 数据库对象索引可以改善数据检索的性能，但会损失数据CUD操作（数据插入、数据更新和删除）性能。因此不经常查询的表，最好不要创建索引。
  • 关键字LIKE的执行效率很低，一般来说，会通过“full text”来替代关键字LIKE;
  • 数据库中的表时不断变化的实体。一组结构优良的表，使用一段时间后，表的使用和内容就会需要进行更改，因此当初理想的优化和配置就需要改变。

Adhere to the original technology to share, your support will encourage me to continue to create!