189 8069 5689
在关系型数据库之MySQL编译安装及数据库基础(一)我们大致了解的数据库的基本应用了，下面我们来聊聊MySQL的家常吧，在实际生产工作中我需要了解自己再数据库领域应该选择哪条道？这是们走向数据库光明之路的前提，关于数据库发展方向有开发DBA和管理DBA，它们分别需要哪些技能呢:
创新互联是一家专注网站建设、网络营销策划、微信小程序、电子商务建设、网络推广、移动互联开发、研究、服务为一体的技术型公司。公司成立十年以来，已经为上千家成都办公窗帘各业的企业公司提供互联网服务。现在，服务的上千家客户与我们一路同行，见证我们的成长；未来，我们一起分享成功的喜悦。
开发DBA:数据库设计(E-R关系图)、SQL开发、内置函数、存储过程(存储过程和存储函数)、触发器、事件调查器(even scheduler)
管理DBA:安装、升级、备份、恢复、用户管理、权限管理、监控、分析、基准测试、语句优化(SQL语句编写必备)、数据字典、按需要配置服务器(服务器变量（必须掌握）:MyISAM,InnoDB,缓存，日志)
下面我们一步一步深入走向MySQL的世界:
SQL语言组成部分:
(1)DDL：数据定义语言
(2)DML：数据操作语言
(3)完整性定义语言:DDL的部分功能
如:(约束)主键、外键、唯一键、条件、非空、事务
(4)视图定义:虚表或临时表，存储下来是SELECT语句
(5)事务控制
(6)嵌入式SQL和动态SQL
(DCL)控制语言
数据类型的作用:
(1)存储的值类型(比如:类型为字段 则无法存储数据)
(2)占据的存储空间(char(40))
(3)定长、变长(char,varchar)
(4)如何被索引及排序
(5)是否能够被索引；(比如text,只能定位左边一部分)
数据字典：系统编目 (system catalog) ---如花名册一样
保存数据库服务器上的元数据
元数据：
关系的名字
每个关系的各字段的名字
各字段的数据类型和长度（）
约束
每个关系上的视图的名字及视图的定义
授权用户的名字
用户的授权和账户信息
统计类的数据：
每个关系字段的个数；
每个关系中的行数；
每个关系的存储方法；
保存元数据的数据库：
information_schema
mysql
performance_shcema
数据类型：
字符型
char ------固定的空间
varchar -------变化的空间，不区分大小写
tinytext
text （作为对象存储，不会直接存储在表中，而是存放了指向其他表的指针）
mediumtext
longtext
binary ------------固定的空间
varbinary -------------变化的空间
tinyblob
blob （二进制大对象）
mediumblob
longblob
数值型
精确数值型
tinyint
smallint
mediumint
int
bigint
decimal
1 整型
2 十进制数据：decimal（定点数，精确表示）
近似数值型 (float,double)
单精度浮点型
双精度浮点型
日期时间型
date
time
datetime
timestamp
year
布尔型（mysql 实际上没有） 0和1
内建类型
enum 枚举
set 集合
数据类型列表:
| Type | Storage Required | Maximum Length |
| CHAR(M) | M characters | 255 characters |
| VARCHAR(M) | L characters plus 1 or 2 bytes | 65535 characters(subject to limittations) |
| TINYTEXT | L characters +1 byte | 255 characters |
TEXT | L characters +2 bytes | 65,535 characters |
| MED IUMTEXT | L characters +3 bytes | 16,777,215 characters |
| LONGTEXT | L characters +4 bytes | 4,294,967,295 characters |
| Data Type Nmae | SQL Standard | Fixed/Variable Length | Range | Size | Attributes |
| BINARY | No | Fixed | length of 0-255 bytes | M bytes | DEFAULT NOT NULL NULL |
| VARBINARY | No | Variable | Length of 0-65532bytes | L*x+1 if L < 255 L*x+2 if L > 255 | DEFAULT NOT NULL NULL |
| TINYBLOB | No | Variable | Max length of 255 bytes | L+1 bytes 1 byte stroes length | NOT NULL NULL |
| BLOB | No | Variable | Max length of 65,535 bytes(64 Kb) | L+2 bytes 2 byte stroes length | NOT NULL NULL |
| MEDIUMBLOB | No | Variable | Max length of 16,777,215 bytes(64 Kb) | L+3 bytes 3 byte stroes length | NOT NULL NULL |
| LONGBLOB | No | Variable | Max length of 4,294,967,295 bytes(64 Kb) | L+4 bytes 4 byte stroes length | NOT NULL NULL |
| Data Type | SIGNED Range | UNSIGNED Range | Size |
| TINY INT | -128 to 127 | 0 to 255 | 1 byte |
| SMALLINT | -32,768 to 32,767 | 0 to 65,535 | 2 bytes |
| MEDIUMINT | -8,388,608 to 8,388,607 | 0 to 16,777,215 | 3 bytes |
| INT | -2,147,483,648 to 2,147,483,647 | 0 to 4,294,967,295 | 4 bytes |
| BIGINT | -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 | 0 to 18,446,744,073,709,551,615 | 8 bytes |
字符型常用的属性修饰符：
not_null :非空约束
null ：允许为空
default 'string' : 默认值，不使用于text类型
character set ‘字符集’
示例:查询字符集设置及服务器字符变量
mysql> show character set ; mysql> show variables like '%char%';
collation '规则' ：排序规则
示例:查询排序规则
mysql> show collation;
auto_increment : 自动增长
前提：非空，且唯一:支持索引，非负值
UNSIGNED：无符号
null
not null
default
浮点型常用修饰符
notnull
null
default
unsigned
日期时间型的修饰符
notnuall
null
default
ENUM和SET 的修饰符 (enum枚举，set 集合 --不适合排序)
not null
null
default ''
MySQL SQL_MODE: SQL模式
TRADITIONAL, STRICT_TRANS_TABLES, or STRICT_ALL_TABLES
设定服务器变量的值：（仅用于支持动态的变量）
支持修改的服务器变量：
动态变量：可以Mysql运行时修改
静态变量： 与配置文件中修改其值，并重启后方能生效；
服务器变量从其生效范围来讲，有两类：
全局变量：服务器级别，修改之后仅对新建立的会话有效； global
回话变量：会话级别，仅对当前回话有效； session
会话建立时，从全局继承各变量；
查看服务器变量 ：
mysql> show {global|session} variables like/where子句;示例如下:
mysql> select @@{global|session}.variables_name;示例如下:
mysql> select * from information_schema.global_variables WHERE VARIABLE_NAME='SOME_VARIABLE_NAME'; mysql> select * from information_schema.session_variables WHERE VARIABLE_NAME='SOME_VARIABLE_NAME';
示例如下:
修改变量
前提：默认仅管理员有权限修改全局变量
mysql> SET {GLOBAL|SESSION} VARIABLE_NAME='VALUE';
注意：无论是全局还是会话级别的动态变量修改，在重启mysqld后都会失效；想永久有效，可定义在配置文件中的相应段中[mysqld]；
MySQL中字符大小写：
1、SQL关键字及函数名不区分字符大小写；
2、数据库、表及视图名称的大小区分与否取决于低层OS、FS
3、存储过程、存储函数及时间调度器的名字不区分大小写，但触发器区分大小写
4、表别名区分大小写；
5、对字段中的数据，如果字段类型为Binary类型，则区分大小写，非binary不区分大小写；
更改数据库名没有更好的办法,只能备份好，然后新建；
数据库操作：
创建数据库:
mysql> CREATE {DATABASE|SCHEMA} [IF NOT EXISTS] db_name [DEFAULT] [CHARACTER SET=''] [DEFAULT] [COLLATE='']示例如下:
删除数据库
DROP {DATABASE | SCHEMA} [IF EXISTS] db_name示例如下:
修改数据库
ALTER {DATABASE|SCHEMA} db_name [DEFAULT] [CHARACTER SET=''] [DEFAULT] [COLLATE='']示例如下:
数据库表管理：
表创建：第一种方式
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
(create_definition,...)
[table_options]
[partition_options]
(create_definition,...)：
字段的定义：字段名、类型和类型修饰符
键、约束或索引：
PRIMARY KEY, UNIQUE KEY, FOREIGN KEY, CHECK
{INDEX|KEY}
[table_options]
ENGINE [=] engine_name
查看mysql支持的引擎:
mysql> SHOW ENGINES;
AUTO_INCREMENT [=] value
[DEFAULT] CHARACTER SET [=] charset_name
[DEFAULT] COLLATE [=] collation_name
COMMENT [=] 'string'
DELAY_KEY_WRITE [=] {0 | 1}
ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}
TABLESPACE tablespace_name [STORAGE {DISK|MEMORY|DEFAULT}]
表分为两种：
MyISAM表，每表有三个文件，都位于数据库目录中；
tb_name.frm 表结构定义
tb_name.MYD 数据文件
tb_name.MYI 索引文件
InnoDB表 ，有两种存储方式
1、默认：每表有一个独立文件和一个共享的文件
tb_name.frm :表结构的定义，位于数据库目录中；
ibdata#:共享的表空间文件，默认位于数据目录（datadir指向的目录）
2、独立的表空间文件
tb_name.frm 每表有一个表结构文件
tb_name.ibd 一个独有的表空间文件
配置启用InnoDB引擎独立表空间:
#vim /etc/my.cnf
innodb_file_per_table = on
示例如下:查看特定表的信息--如查看使用的存储引擎
表创建：第二种方式（复制表数据不复制表结构）
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
[(create_definition,...)]
[table_options]
select_statement
示例如下:
表创建：第三种方式（复制表结构）
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
{ LIKE old_tbl_name | (LIKE old_tbl_name) }
示例如下:
表删除：
mysql> drop table table_name; 删除表名为xxx的表
表中的数据删除:
mysql> delete from table_name where 字段名='values'
如 DELETE FROM Test1 WHERE Name='samlee'
清空表数据:
mysql> truncate table_name
truncate是一个能够快速清空资料表内所有资料的SQL语法。并且能针对具有自动递增值的字段。
表修改：
语法格式:alter table tab1_name
修改字段定义：alter
添加新字段（add）
(after)可定义字段排序规则
示例如下:
mysql> use mydb;
mysql> CREATE TABLE Test5(ID int(10) UNSIGNED auto_increment NOT NULL,PRIMARY KEY(ID));
mysql> ALTER TABLE Test5 ADD Age TINYINT UNSIGNED NOT NULL;
mysql> ALTER TABLE Test5 ADD Gender ENUM('M','F') NOT NULL DEFAULT 'M' AFTER ID;
mysql> ALTER TABLE Test5 ADD Name char(10) NOT NULL ;
删除字段(DROP)
mysql> ALTER TABLE Test5 DROP Age;
示例如下:
修改字段-->
修改字段名称(change)
mysql> ALTER TABLE Test5 CHANGE Name StuName CHAR(30) NOT NULL;
示例如下:
修改字段类型及属性(modify)
mysql> ALTER TABLE Test5 MODIFY Gender ENUM('M','F') NOT NULL AFTER ID;
修改约束、键或索引
alter下在表中特定的字段上创建索引index(add)
mysql> ALTER TABLE Test5 ADD INDEX (StuName);
查看某表中的索引index
mysql> SHOW INDEXES FROM Test5\G;
删除表中特定字段index (drop)
mysql> ALTER TABLE Test5 DROP INDEX StuName;
修改表名(rename)
格式如下:
>RENAME TABLE old_tablename TO new_tablename
修改表的存储引擎
格式如下:
>alter table table_name engine=MyISAM
示例如下:
mysql> ALTER TABLE Test5 engine=MyISAM; mysql> SHOW TABLE STATUS LIKE 'Test5'\G;
扩展Tips:
1.指定排序标准的字段：
ORDER BY col_name [, col_name] ...
2.转换字符集及排序规则：
CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
3.表选项修改：
[table_options]
ENGINE [=] engine_name
mysql> SHOW ENGINES;
AUTO_INCREMENT [=] value
[DEFAULT] CHARACTER SET [=] charset_name
[DEFAULT] COLLATE [=] collation_name
COMMENT [=] 'string'
DELAY_KEY_WRITE [=] {0 | 1}
ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}
TABLESPACE tablespace_name [STORAGE {DISK|MEMORY|DEFAULT}]阶段测试:
新建如下表（包括结构和内容）：
ID Name Age Gender Course
1 Ling Huchong 24 Male Hamogong
2 Huang Rong 19 Female Chilian Shenzhang
3 Lu Wushaung 18 Female Jiuyang Shenggong
4 Zhu Ziliu 52 Male Pixie Jianfa
5 Chen Jialuo 22 Male Xianglong Shiba Zhang
6 Ou Yangfeng 70 Male Shenxiang Bannuo Gong
---------------------------------------------------------------------
##创建上表并插入数据
mysql> CREATE TABLE student(ID smallint not null primary key,Name varchar(40) not null,Age tinyint unsigned not null,Gender ENUM('F','M') not null default 'M',Course varchar(40) not null);
mysql> INSERT INTO student(ID,Name,Age,Gender,Course) value (1,'Ling Huchong',24,'M','Hamogong');
mysql> INSERT INTO student(ID,Name,Age,Gender,Course) value (2,'Huang Rong',19,'F','Chilian Shenzhang');
mysql> INSERT INTO student(ID,Name,Age,Gender,Course) value (3,'Lu Wushuang',18,'F','Jiuyang Shenggong');
mysql> INSERT INTO student(ID,Name,Age,Gender,Course) value (4,'Zhu Ziliu',52,'M','Pixie Jianfa');
mysql> INSERT INTO student(ID,Name,Age,Gender,Course) value (5,'Chen Jialuo',22,'M','Xianglong Shiba Zhang');
mysql> INSERT INTO student(ID,Name,Age,Gender,Course) value (6,'Ou Yangfeng',52,'M','Shenxiang Bannuo Gong');(1)新增字段：Class 字段定义自行选择；放置于Name字段后;
mysql> alter table student add Class tinyint not null after Name;
(2)将ID字段名称修改为SID;
mysql> ALTER TABLE student change ID SID smallint not null;
(3)将SID字段放置最后;
mysql> ALTER TABLE student MODIFY SID smallint not null AFTER Class;
MySQL的查询操作
select 查询选择和投影操作查询:
投影：挑选要显示的字段
投影：SELECT 字段1, 字段2, ... FROM tb_name; #选定显示指定字段 SELECT * FROM tb_name; #显示所有字段
选择：挑选符合条件的行
选择：SELECT 字段1, ... FROM tb_name WHERE 子句[布尔条件表达式];
布尔条件表达式操作符：
*#: = 等于 *#: <=> abc = bde ,abc =NULL *#: <> 不等于 *#: < 小于 *#: <= 小于等于 *#: > 大于 *#: >= 大于等于
通过案例理解MySQL查询:
创建案例环境:
mysql> CREATE TABLE students (SID INT UNSIGNED AUTO_INCREMENT NOT NULL UNIQUE KEY,Name CHAR(30) NOT NULL, Age TINYINT UNSIGNED NOT NULL,Gender ENUM('F','M') NOT NULL,Tutor CHAR(30),ClassID TINYINT UNSIGNED);
mysql> INSERT INTO students VALUES (1,'Guo Jing',27,'M','Hong qigong',2),(2,'Yang Guo',28,'M','Ou Yangfeng',3),(3,'Qiao feng',21,'M','Ling Huchong',3);
mysql> INSERT INTO students VALUES (4,'Xue Baochai',19,'F','Rong Sir',1),(5,'Xia Yuhe',37,'F','Shi Qian',2),(6,'Wu Yong',51,'M','Lin Daiyu',1);
mysql> INSERT INTO students VALUES (7,'Tom',11,'M','Jerry',1),(8,'Tomy','13','M',NULL,4);查询students表Tutor为空的记录
mysql> SELECT * FROM students WHERE Tutor IS NULL;
2.查询students表Tutor不为空的记录
mysql> SELECT * FROM students WHERE Tutor IS NOT NULL;
模糊查询:
LIKE: 支持的通配符
%: 任意长度的任意字符 （模糊查找，性能比较低）
_ : 任意单个字符
RLIKE，REGEXP :支持使用正则表达式 (模糊查找，性能比较低)
3.查询姓名以X开头的记录
mysql> SELECT * FROM students WHERE Name LIKE 'X%';
或使用RLIKE
mysql> SELECT * FROM students WHERE Name RLIKE '^X.*';
4.查询students表年龄为25、26、27、28的记录(使用IN: 判断指定字段是否在给定的列表中):
mysql> SELECT * FROM students WHERE Age IN (25,26,27,28);
5.查询年龄为25至40的记录
mysql> SELECT * FROM students WHERE Age BETWEEN 25 AND 40;
组合条件测试：
NOT, ! :否定 AND, && ：并且 OR, || ：或者
6.查询SID为"5" 性别为"F"的记录
mysql> SELECT * FROM students WHERE SID = '5' AND Gender = 'F';
7.查询Age为'28' 或 Gender为'F'的记录
mysql> SELECT * FROM students WHERE Age = '28' OR Gender = 'F';
查询排序:
ORDER BY
(ASC 升序显示)
8.查询Age为'28' 或 Gender为'F'的记录,按Age数值从小到大排序
mysql> SELECT * FROM students WHERE Age = '28' OR Gender = 'F' ORDER BY Age;
(DESC 降序显示)
9.查询Age为'28' 或 Gender为'F'的记录,按Age数值从大到小排序
mysql> SELECT * FROM students WHERE Age = '28' OR Gender = 'F' ORDER BY Age DESC;
聚合函数 sum（），avg（），max（），min( ) ...
总和 平均值 最大值 最小值
10.计算学生所有学生的平均年龄
mysql> SELECT SUM(Age) FROM students;
11.计算学生年龄的最大数值
mysql> SELECT MAX(Age) FROM students;
12.统计表的记录数
mysql> SELECT COUNT(Age) FROM students;
13.计算学生年龄的最小数值
mysql> SELECT MIN(Age) FROM students;
14.查询年龄大于30的，所有人的平均年龄之和
mysql> SELECT SUM(Age) FROM students WHERE Age > 30;
group by -->分组
having --> 聚合
15.以下操作在students表上执行
以ClassID分组，显示每班的同学的人数；
mysql> SELECT ClassID,COUNT(Age) FROM students GROUP BY ClassID;
16.以Gender分组，显示其年龄之和；
mysql> SELECT Gender,SUM(Age) FROM students GROUP BY Gender;
17.以ClassID分组，显示其平均年龄大于25的班级；
mysql> SELECT ClassID,AVG(Age) FROM students GROUP BY ClassID HAVING AVG(age) > 25;
18.以Gender分组，显示各组中年龄大于25的学员的年龄之和；
mysql> SELECT Gender,SUM(Age) FROM students WHERE Age > 25 GROUP BY Gender;
19.使用LIMIT显示指定行(有效处理大数据过滤查询操作，减少系统性能消耗)
mysql> SELECT * FROM students LIMIT 2,3;
mysql> SELECT * FROM students LIMIT 4;
扩展Tips:
(1)导出数据库文件:
# mysqldump -uroot -hlocalhost -predhat mydb > mydb.sql
(2)导入数据库文件
# mysql -uroot -hlocalhost -prehdat < mydb.sql
SELECT语句的执行流程：
FROM clause --> WHERE clause --> GROUP BY --> HAVING clause --> ORDER BY ... --> SELECT --> LIMIT
SELECT语句:
DISTINCT：指定的结果相同的只显示一次； SQL_CACHE：缓存于查询缓存中； SQL_NO_CACHE：不缓存查询结果；
MySQL多表查询和子查询
导入hellodb.sql 以下操作在students表上执行；
#mysql -uroot -hlocalhost -predhat < hellodb.sql
联结查询：事先将两张或多张表join，根据join的结果进行查询；
(1)cross join:交叉联结
#第一张表行数*第二张表行数=总行数(很少用)
mysql> SELECT students.Name,classes.Class FROM students,classes WHERE students.ClassID = classes.ClassID;
(2)自然连接(仅能够在两者相等的情况下才能建立联结)--内连接
等值连接
条件比较
(3)外连接
左外连接:只保留出现在左外连接运算之前(左边)的关系中的元组--以左边的元素为准，右边没有则null
#left_tb LEFT JOIN right_tb ON 连接条件
mysql> SELECT s.Name,c.Class FROM students AS s LEFT JOIN classes AS c ON s.ClassID = c.ClassID;
右外连接：只保留出现在右外连接运算之后（右边）的关系中的元组；
#left_tb RIGHT JOIN right_tb ON 连接条件
mysql> SELECT s.Name,c.Class FROM students AS s RIGHT JOIN classes AS c ON s.ClassID = c.ClassID;
(4)自连接
mysql> SELECT t.Name,s.Name FROM students AS s,students AS t WHERE s.StuID = t.TeacherID;
Tips：
AS字段别名:
mysql> SELECT Name AS SamleeName FROM students;
子查询：在查询中嵌套的查询
用于where中的子查询
1、用于比较表达式中的子查询
子查询的返回值只能有一个
2、用于exists中的子查询
判断是否存在；
3、用于IN中的子查询；
判断存在于指定列表中
用于from子句的子查询
(1)子查询（1）用在where中
mysql> SELECT s.Name,s.Age,s.Gender FROM (SELECT * FROM students WHERE Gender='M') AS s WHERE s.Age > 25;
(2)子查询（2） 用在from中
mysql> SELECT Name,Age FROM students WHERE Age > (SELECT AVG(Age) FROM students);
以上为关系型数据库之Mysql查询及数据库管理(二)所有内容