在关系型数据库之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查询及数据库管理(二)所有内容
转载于:https://blog.51cto.com/gzsamlee/1772675