关系型数据库之Mysql查询及数据库管理(二)

在关系型数据库之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 集合

数据类型列表:

字符型常用的属性修饰符：
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     Course1     Ling Huchong   24      Male       Hamogong2     Huang Rong    19      Female     Chilian Shenzhang3     Lu Wushaung   18      Female     Jiuyang Shenggong4     Zhu Ziliu     52      Male       Pixie Jianfa5     Chen Jialuo   22      Male       Xianglong Shiba Zhang6      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);

1. 查询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查询及数据库管理(二)所有内容