MySQL数据库特点的总结

什么是数据库

• 数据库（Database）是按照数据结构来组织、储存和管理数据的仓库。

• 常见数据库

  • Oracle
  • DB2
  • SQL Server
  • Postgre SQL
  • MySQL

数据库相关术语

• 数据库系统（Database System）DBS

  • 数据库(Database)
  • 数据库管理系统(Database Management System)DBMS
  • 应用开发工具
  • 管理员及工具

• SQL语言：结构化查询语言

  • DDL：数据定义语言（创建数据结构）
  • DML：数据操作语言（增删改数据）
  • DQL：数据查询语言（查数据）
  • DCL：数据控制语言（控制用户访问权限）

MySQL相关操作

配置文件

• my.cnf是配置文件，在Windows上时my.ini

登录/退出MySQL

• 登录方法

mysql -uroot -p
mysql -uroot -p[密码]

• 退出

exit
quit

• 查看版本

mysql -uroot -p --version

• 登录的同时打开指定数据库

mysql -uroot -p -D [数据库名称]

• 登录信息中需要掌握的内容

  • 命令行结束符默认使用;或\g来结束；
  • help [关键字]来查询帮助；
  • \c可以取消当前命令的执行。

常用SQL语句

SELECT USER()       #得到登陆的用户
SELECT VERSION()    #得到MySQL的版本信息
SELECT NOW()        #得到当前的日期时间
SELECT DATABASE()   #得到当前打开的数据库

SQL语句语法规范

• 常用MySQL的关键字需要大写，库名、表名、字段名称等使用小写
• SQL支持折行操作，拆分的时候不能把完整的单词拆开
• 数据库名称、表名称、字段名称不要使用MySQL的保留字，如果必须使用，需要使用反引号将其括起来

数据库相关操作

CREATE (DATABASE|SCHEMA) db_name; #注意，数据库名称最好有意义，名称不要包含特殊字符或者MySQL关键字。
SHOW (DATABASES|SCHEMAS);
CREATE DATABASE [IF NOT EXISTS] db_name;
SHOW WARNINGS; #查看警告
CREATE DATABASE [IF NOT EXISTS] db_name DEFAULT CHARACTER SET [=] charset; #创建数据库的时候指定编码方式
ALTER DATABASE db_name DEFAULT CHARACTER SET [=] charset; #修改指定数据库的编码方式。
USE db_name; #打开指定数据库
SELECT (DATABASE|SCHEMA); #得到当前打开的数据库
DROP DATABASE db_name; #删除数据库
DROP DATABASE [IF EXISTS] db_name; #如果数据库存在则删除

数据表相关操作

数据表

• 数据表是数据库最重要的组成部分之一，数据保存在数据表中;
• 数据表由行(row)和列(column)来组成;
• 每个数据表中至少要有一列，行可以有零行或若干行;
• 表名要求唯一，不要包含特殊字符，最好含义明确。

创建数据表

CREATE TABLE [IF NOT EXISTS] tbl_name(
  字段名称 字段类型 [完整性约束条件],
  字段名称 字段类型 [完整性约束条件],
  ...
)ENGINE=存储引擎 CHARSET=编码方式;
unsigned  #无符号，没有负数，从零开始
zerofill #零填充，当显示长度不够时可以使用前补0的效果填充至指定长度
NOT NULL #非空约束，也就是插入值的时候这个字段必须要给值
DEFAULT #默认值，如果插入记录的时候没有给字段赋值，则使用默认值
PRIMARY KEY #主键，标志记录的唯一性，即值不能重复，而且一个表只能有一个主键，如果一个字段是主键，则自动禁止为空
UNIQUE KEY #唯一性，表示不能重复，一个表中可以有多个字段是唯一索引，但是NULL值除外
AUTO_INCREMENT #自动增长，只能用于数值列，而且配合索引
FOREIGN KEY # 外键约束

查看当前数据库下已有的数据表

SHOW TABLES;
SHOW [FULL] TABLES [{FROM|IN} db_name] [LIKE 'pattern'|WHERE expr];

查看指定数据表的详细信息

SHOW CREATE TABLE tbl_name;

查看表结构

DESC tbl_name;
DESCRIBE tbl_name;
SHOW COLUMNS FROM tbl_name;

删除指定的数据表

DROP TABLE [IF EXISTS] tbl_name;

表结构相关操作

ALTER TABLE tbl_name ADD 字段名称 字段属性 [完整性约束条件] [FIRST|AFTER 字段名称] --添加字段
ALTER TABLE tbl_name DROP 字段名称 --删除字段

-- 一条语句完成多项操作
ALTER TABLE user1
ADD age TINYINT UNSIGNED NOT NULL DEFAULT 18,
ADD addr VARCHAR(100) NOT NULL DEFAULT 'Beijing'

-- 添加默认值
ALTER TABLE tble_name
ALTER 字段名称 SET DEFAULT 默认值

-- 删除默认值
ALTER TABLE tble_name
ALTER 字段名称 DROP DEFAULT

-- 添加主键
ALTER TABLE tble_name
ADD PRIMARY KEY(字段名称)

-- 删除主键
ALTER TABLE tbl_name
DROP PRIMARY KEY;

-- 添加唯一
ALTER TABLE tbl_name
ADD UNIQUE KEY|INDEX index_name (字段名称)

-- 删除唯一
ALTER TABLE tble_name
DROP INDEX index_name;

-- 修改字段类型、字段属性
ALTER TABLE tbl_name
MODIFY 字段名称 字段类型 [字段属性] [FIRST|AFTER 字段名称]

-- 修改字段名称、字段类型、字段属性
ALTER TABLE tbl_name
CHANGE 原字段名称 新字段名称 字段类型 字段属性[FIRST|AFTER 字段名称]

-- 修改数据表名称(两种方法)
ALTER TABLE tbl_name RENAME [TO|AS] new tbl_name;
RENAME TABLE tbl_name TO new_tbl_name;

MySQL数据类型

数值型

• 整数型
• 浮点数
• 定点数

字符串类型

• CHAR效率高于VARCHAR, CHAR相当于拿空间换时间, VARCHAR拿时间换空间；
• CHAR默认存储数据的时候，后面会用空格填充到指定长度；而在检索的时候会去掉后面的空格；VARCHAR在保存的时候不进行填充，尾部的空格会留下；
• TEXT列不能有默认值，检索的时候不存在大小写转换。

日期时间类型

MySQL存储引擎

MyISAM存储引擎

• 在MySQL5.5之前一直使用的默认存储引擎是MyISAM存储引擎；

• 会在磁盘中产生三个文件：

  • .frm，表结构文件
  • .MYD，数据文件
  • .MYI，索引文件

• 可以在建表的时候指定数据文件和索引文件的存储位置，只有MyISAM表支持：

  • DATA DIRECTORY [=] 数据保存的绝对路径
  • INDEX DIRECTORY [=] 索引文件保存的绝对路径

• MyISAM最大支持的数据量：2的64次方条记录；

• 每个表最多可以建立64个索引

• 如果是复合索引，每个复合索引最多包含16个列，索引值最大是1000B

• MyISAM引擎的存储格式

  • 定长(FIXED 静态)

    • 是指字段中不包含像VARCHAR/TEXT/BLOB
    • 静态表的查询速度很快

  • 动态(DYNAMIC)

    • 字段中包含了VARCHAR/TEXT/BLOB

  • 压缩(COMPRESSED)

    • 通过myisampack创建

InnoDB存储引擎

• 设计遵循ACID模型，支持事务，具有从服务崩溃中恢复的能力，能够最大限度地保护用户的数据；

  • 原子性(Atomicity)
  • 一致性(Consistency)
  • 隔离性(Isolation)
  • 持久性(Durability)

• 支持行级锁，好处是可以提升用户高并发时的读写性能；

• 支持外键，保证数据的一致性和完整性；

• InnoDB有自己的独立缓冲池，常用的数据和索引都在缓存中；

• 对于INSERT、UPDATE、DELETE操作，InnoDB会使用一种change buffering的机制来自动优化，还可以提供一致性的读，并且还能够缓存变更的数据，减少磁盘I/O，提高性能；

• 创建InnoDB表之后产生两个文件：

  • .frm，表结构文件
  • .ibd，数据和索引存储在表空间中

• 使用InnoDB的表时，都需要创建主键，最好是配合上AUTO_INCREMENT，也可以把经常查询的列作为主键。

MySQL数据操作

添加记录

INSERT [INTO] tbl_name[(col_name,...)] {VALUE|VALUES}
-- [(col_name,...)] 表示字段
-- 不指定字段名称
INSERT tbl_name VALUE(value...)
-- 列出指定字段
INSERT tbl_name(字段名称,...) VALUES(值,...)
-- 一次添加多条记录
INSERT  tbl_name[(字段名称,...)] VALUES(值,...),(值,...);

INSERT tbl_name SET 字段名称=值,...;
INSERT tbl_name[(字段名称...)] SELECT 字段名称,... FROM tbl_name [WHERE];

修改记录

UPDATE tbl_name SET 字段名称=值，字段名称=值 [WHERE 条件]
--如果不加条件，整个表中的记录都会被更新

删除记录

DELETE FROM tbl_name [WHERE 条件]
-- 如果不添加条件，表中所有记录都会被删除
-- DELETE清空数据表的时候不会重置AUTO_INCREMENT的值，可以通过ALTER语句将其重置
-- 重置AUTO_INCREMENT值的方法
ALTER TABLE tbl_name AUTO_INCREMENT=1;
-- 一步解决上述两个问题
TRUNCATE [TABLE] tbl_name;
-- 清空表中所有记录
-- 会重置AUTO_INCREMENT的值

查询记录

SELECT select_expr,... FROM tbl_name
[WHERE 条件]
[GROUP BY {col_name|position} HAVING 二次筛选]
[ORDER BY {col_name|position|expr}[ASC|DESC]]
[LIMIT 限制结果集的显示条数]
-- 查询表中的所有记录，*代表所有字段
SELECT * FROM tbl_name;
-- 库名.表名的形式来选择字段
SELECT 字段名称,... FROM db_name.tbl_name;
-- 给字段起别名
SELECT 字段名称 [AS] 别名名称,... FROM db_name.tbl_name;
-- 给数据表起别名
SELECT 字段名称,... FROM tbl_name [AS] 别名;
-- 表名.字段名称
SELECT tbl_name.col_name,... FROM tbl_name;
-- WHERE条件，会筛选出符合条件的记录
-- 比较运算符
> >= = <= != <=>
-- 利用<=>来检测NULL值
SELECT id,username,age,userDesc FROM user1
WHERE userDesc<=>NULL;
-- 检测NULL值
SELECT id,username,age,userDesc FROM user1
where userDesc IS [NOT] NULL;
-- <=>与=的区别 <=>可以检测NULL值
-- 指定范围 [NOT]BETWEEN...AND...
SELECT id,username,age,sex FROM user1
where age BETWEEN 18 AND 30;
-- 指定集合 [NOT]IN(...)
SELECT id,username,age FROM user1
WHERE id IN(1,3,5,7,9);
-- 逻辑运算符 AND OR
SELECT id,username,age,sex FROM user1
WHERE sex='男' AND age>=20;
-- 模糊查询 [NOT] LIKE
SELECT id,username,age,sex FROM user1
WHERE username LIKE 'king'
-- 通配符 %任意长度的字符串 _任意一个字符
SELECT id,username,age,sex FROM user1
WHERE username LIKE '%三%'
-- 分组操作 GROUP BY 把值相同的放到一个组中，最终查询出的结果只会显示组中一条数据
SELECT id,username,age,sex FROM user1
GROUP BY sex;
-- 分组操作配合GROUP_CONCAT()字段查看组中成员的信息
SELECT GROUP-CONCAT(username),age,sex,addr FROM user1
GROUP BY sex;
-- 分组操作配合聚合函数使用
COUNT() #统计记录总数，如果写的是COUNT(字段名称)，字段中的值为NULL，不统计进来，写COUNT(*)全部统计
SUM()   #求和
MAX()   #求最大值
MIN()   #求最小值
AVG()   #求平均值
-- 配合WITH ROLLUP关键字使用，会在记录末尾添加一条记录，是上面所有记录的综合
SELECT GROUP_CONCAT(usrname) AS usersDetail,
COUNT(*) AS totalUsers
FROM user1
GROUP BY sex
WITH ROLLUP;
-- HAVING子句对分组结果进行二次筛选
SELECT GROUP_CONCAT(usrname) AS usersDetail,
COUNT(*) AS totalUsers
FROM user1
GROUP BY sex
HAVING COUNT(*)>=3
-- ORDER BY 排序
ORDER BY 字段名称 ASC|DESC
SELECT id,username,age
FROM user1
ORDER BY age ASC;
ORDER BY RAND();    #随机显示记录
-- LIMIT 限制结果集显示条数
LIMIT 值  #显示结果集的前几条记录
LIMIT offset, row_count  #从offset开始显示几条记录

多表查询

– 多表联查的上限是3个表，超过三个表的时候，查询的效率是很低的。

笛卡尔积形式

• 全部循环连接一遍

内连接形式

• 查询两个表中符合连接条件的记录

SELECT 字段名称,... FROM tbl_name1
INNER JOIN tbl_name2
ON 连接条件
------------
SELECT emp.id,emp.username,emp.age,dep.depName
FROM emp
INNER JOIN dep
ON emp.depId=dep.id

外连接形式

• 左外连接

  • 先显示左表中的全部记录，再去右表中查询符合条件的记录，不符合的以NULL值代替

SELECT 字段名称,... FROM tbl_name1
OUTER LEFT JOIN tbl_name2
ON 条件

• 右外连接

  • 先显示右表中的全部记录，再去左表中查询符合条件的记录，不符合的以NULL值代替

SELECT 字段名称,... FROM tbl_name1
OUTER RIGHT JOIN tbl_name2
ON 条件

外键约束

• 注意：只有InnoDB的存储引擎支持外键

创建外键

• 建表时指定外键

  • 子表的外键字段和主表的主键字段类型要相似；如果是数值型要求一致，并且无符号也要一致；如果是字符型，要求类型一致，长度也要相同；

  • 如果外键字段没有创建索引，MySQL会自动帮我们添加索引；

  • 子表的外键关联的必须是父表的主键。

  • 外键约束的参照操作

    1. CASCADE 从父表删除或更新，子表也跟着删除或者更新，级联对的操作
    2. SET NULL 从父表删除或者更新记录，并且设置子表的外键列为null才可以。
    3. NO ACTION|RESTRICT 默认操作

[CONSTRAINT 外键名称] FOREIGN KEY(字段名称) REFERENCES 主表(字段名称)

• 动态添加删除外键

  • 动态添加外键时表中的数据一定要是合法的，没有脏值，否则外键没法创建成功。

--添加外键
ALTER TABLE tbl_name
[CONSTRAINT 外键名称] ADD FOREIGN KEY(外键字段) REFERENCES 主表(主键字段)
--删除外键
ALTER TABLE news
DROP FOREIGN KEY cateId_fk_newsCate;

特殊形式的查询

子查询

内层语句查询的结果可以作为外层语句查询的条件

SELECT 字段名称 FROM tbl_name WHERE col_name=(SELECT col_name FROM tbl_name)

由in引发的子查询

SELECT * FROM emp
WHERE depId IN (SELECT id FROM dep);

由比较运算符引出的子查询

SELECT id,username,score FROM stud
WHERE score>=(SELECT score FROM level WHERE id=1);

SELECT id,username,score FROM stud
WHERE score<=(SELECT score FROM level WHERE id=3);

由EXISTS引发的子查询

--只有exists后面的语句为真才会执行前面的语句
SELECT * FROM emp
WHERE EXISTS(SELECT depName FROM dep WHERE id=10)

ANY SOME ALL子查询

SELECT * FROM stu
WHERE score>=ANY(SELECT score FROM level);

SELECT * FROM stu
WHERE score>=SOME(SELECT score FROM level);

SELECT * FROM stu
WHERE score>=ALL(SELECT score FROM level);

INSERT…SELECT子查询

INSERT user1(username)
SELECT username FROM user;

INSERT user2 SET username=(SELECT username FROM stu WHERE id=9)

-- 去掉字段的重复值
SELECT DISTINCT(username) FROM user2;

CREATE…SELECT子查询

CREATE TABLE user1(
  id INT UNSIGNED AUTO_INCREMENT KEY,
  username VARCHAR(20)
)SELECT id,username FROM emp;

联合查询

UNION

• 会去掉两个表中重复的值

SELECT 字段名称,... FROM tbl_name1
UNION
SELECT 字段名称,... FROM tbl_name2
UNION
...

UNION ALL

• 进行简单的合并，并不会去掉重复值

SELECT 字段名称,... FROM tbl_name1
UNION ALL
SELECT 字段名称,... FROM tbl_name2
UNION ALL
...

自身连接查询

• 无限级分类的实现形式，有点类似于链表，可以以链的形式寻找父分类。

SELECT s.id,s.cateName AS sCateName,p.cateName AS pCateName
FROM cate AS s
LEFT JOIN cate AS p
ON s.pId=p.id;

MySQL常用函数

数学函数

字符串函数

-- CHAR_LENGTH():得到字符串的字符数
SELECT CHAR_LENGTH('ABC');

-- LENGTH():返回字符串的长度，与上面的函数的区别在于对中文的识别
SELECT LENGTH('ABC')

-- CONCAT(s1,s2,...):将字符串合并为一个字符串
SELECT CONCAT('A','B','C');

-- CONCAT_WS(x,s1,s2,s3,...):以指定分隔符拼接字符串
SELECT CONCAT_WS('-','A','B','C');

-- 将字符串转换为大写或者小写 UPPER()|UCASE() LOWER()|LCASE()
select upper('hello king'),ucase('hello imooc'),lower('HELLO ADMIN'),LCASE('HELLO EVERYBODY');

-- 字符串的翻转REVERSE()
SELECT REVERSE('ABC');

-- LEFT()|RIGHT():返回字符串的前几个字符或者后几个字符
SELECT LEFT('HELLO',2),RIGHT('HELLO',2);

--LPAD()|RPAD():用字符串填充到指定长度
SELECT LPAD('abc',10,'?');

-- 去掉字符串两端的空格TRIM()|LTRIM()|RTRIM()
SELECT TRIM(' ABC '),LTRIM(' ABC '),RTRIM(' ABC ');

-- REPEAT(S,n):重复指定的字符串
SELECT REPEAT('HELLO',3);

-- REPLACE(s1,s2,s3):字符串的替换，在s1中找到s2用s3来替换
SELECT REPLACE('HELLO KING','KING','QUEEN');

-- SUBSTRING(s1,n1,n2):字符串的截取，把s1字符串从n1处开始截取n2位
SELECT SUBSTRING('abcdef',1,3);

-- STRCMP(s1,s2):比较字符串的ASCII码值
SELECT STRCMP('A','C');

日期时间函数

-- 返回当前时间
SELECT CURTIME(),CURRENT_TIME();

-- 返回当前的日期时间
SELECT NOW(),CURRENT_TIMESTAMP(),SYSDATE();

-- 返回日期中的月份和月份的名称
SELECT MONTH('2017-02-19');
SELECT MONTH(CURDATE()),MONTHNAME(CURDATE());

-- 返回星期
SELECT DAYNAME(NOW());

-- 返回一周内的第几天
SELECT DAYOFWEEK(NOW());

-- 返回一年中的信息
SELECT WEEK(NOW());
SELECT YEAR(NOW());
SELECT DAY(NOW());
SELECT HOUR(NOW());
SELECT MINUTE(NOW());
SELECT SECOND(NOW());

-- 计算两个日期之间相差的天数
SELECT DATEDIFF('2017-03-01','2017-09-30');

其他常用函数

-- 得到版本信息、连接数以及数据库名
SELECT VERSION(),CONNECTION_ID(),DATABASE();

-- 得到当前登录的用户
SELECT USER(),CURRENT_USER(),SYSTEM_USER(),SESSION_USER();

-- 返回上一步产生的自增长ID
SELECT LAST_INSERT_ID();

-- 使用加密算法，返回加密字符串
SELECT MD5('KING');
SELECT PASSWORD('king'); #默认加密算法