MySQL学习

MySQL学习

— Java攻城狮学习路线 —

入门：

进阶：

1. 简介

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，目前属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件。

1.1 什么是据库？

数据库（Database）是按照数据结构来组织、存储和管理数据的仓库。每个数据库都有一个或多个不同的API用于创建，访问，管理，搜索和复制所保存的数据。基于读写速度考虑，现在使用关系型数据库管理系统（RDBMS）来存储和管理大数据量。所谓的关系型数据库，是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。其特点为：

  • 数据以表格的形式出现
  • 每行为各种记录
  • 每列为记录所对应的数据项
  • 许多的行和列组成一张表单
  • 若干的表单组成数据库

1.2 RDBMS 术语

  • 数据库: 数据库是一些关联表的集合。
  • 数据表: 表是数据的矩阵。在一个数据库中的表看起来像一个简单的电子表格。
  • 列: 一列(数据元素) 包含了相同的数据, 例如邮政编码的数据。
  • 行：一行（=元组，或记录）是一组相关的数据，例如一条用户订阅的数据。
  • 冗余：存储两倍数据，冗余降低了性能，但提高了数据的安全性。
  • 主键：主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据。
  • 外键：外键用于关联两个表。
  • 复合键：复合键（组合键）将多个列作为一个索引键，一般用于复合索引。
  • 索引：使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。
  • 参照完整性: 参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件，目的是保证数据的一致性。

2. 安装

MySQL下载地址：https://www.mysql.com/downloads/

相关安装教程较为简单，可自行查询。

3. 数据库连接

3.1 使用命令连接

Linux和Windows连接方式一样，在命令行输入：

[root@host]# mysql -uroot -p
Enter password:******

登录后成功后输出：

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is XXXXXX to server version: 5.X.X

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

基本操作：

  • 查看帮助：
    • hlep;
    • \h
    • ?加上相关关键字
  • 取消当前命令执行：\c
  • 命令行结束符：
    • ;
    • \g
  • 退出系统：
    • exit
    • quit
    • \q

3.2 JDBC连接

利用Java API实现数据库连接，详情见JDBC相关资料。

4. 数据库操作

4.1 创建数据库

使用create关键词创建数据库，database和schema等效。

CREATE DATABASE dbname;
CREATE SCHEMA dbname;

指定编码方式创建数据库。

CREATE DATABASE [IF NOT EXISTS] dbname [DEFAULT] CHARACTER SET [=] utf-8;

4.2 查看数据库

使用show关键字查看当前服务器下全部数据库。

SHOW DATABASE;
SHOW SCHEMA;

4.3 查看数据库详细信息

SHOW CREATE DATABASE dbname;

4.4 修改数据库编码方式

ALTER DATABASE dbname [DEFAULT] CHARACTER SET [=] utf-8;

注：[]表示可以省略

4.5 打开数据库

USE dbname;

4.6 得到当前打开数据库

SELECT DATABASE();
SELECT SCHEMA();

4.7 删除数据库

DROP DATABASE [IF EXISTS] dbname;

5. 注释

两种注释方式。

# 这是一段注释1
-- 这是一段注释2

6. 数据类型

6.1 数值类型

类型 大小 范围 用途
TINYINT 1 字节 (-128，127) 小整数值
SMALLINT 2 字节 (-32 768，32 767) 大整数值
MEDIUMINT 3 字节 (-8 388 608，8 388 607) 大整数值
INT 4 字节 (-2 147 483 648，2 147 483 647) 大整数值
BIGINT 8 字节 (-9 233 372 036 854 775 808，9 223 372 036 854 775 807) 极大整数值
FLOAT 4 字节 (-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) 单精度浮点数值
DOUBLE 8 字节 (-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 双精度浮点数值
DECIMAL M,D决定 依赖于M和D的值 小数值

6.2 字符串类型

类型 大小 用途
CHAR 0-255字节 定长字符串
VARCHAR 0-65535 字节 变长字符串
TINYBLOB 0-255字节 不超过 255 个字符的二进制字符串
TINYTEXT 0-255字节 短文本字符串
BLOB 0-65 535字节 二进制形式的长文本数据
TEXT 0-65 535字节 长文本数据
MEDIUMBLOB 0-16 777 215字节 二进制形式的中等长度文本数据
MEDIUMTEXT 0-16 777 215字节 中等长度文本数据
LONGBLOB 0-4 294 967 295字节 二进制形式的极大文本数据
LONGTEXT 0-4 294 967 295字节 极大文本数据

6.3 日期和时间类型

类型 大小 格式 用途
DATE 3 字节 YYYY-MM-DD 日期值
TIME 3 字节 HH:MM:SS 时间值或持续时间
YEAR 1 字节 YYYY 年份值
DATETIME 8 字节 YYYY-MM-DD HH:MM:SS 混合日期和时间值
TIMESTAMP 4 字节 YYYYMMDD HHMMSS 混合日期和时间值，时间戳

7. 数据表操作

数据表由行和列组成，每个数据表中至少有1列，行可以0,1或多行，表名必须唯一，且含义明确。

7.1 创建表

CREATE TABLE [IF NOT EXISTS] tblname(
    字段名称 字段类型 [完整性约束条件]，
    字段名称 字段类型 [完整性约束条件]，
    字段名称 字段类型 [完整性约束条件]，
    ...
)ENGINE=存储引擎 CHARSET=编码方式;

MySQL中在表创建时指定数据项的约束条件：

约束条件 作用
UNSIGNED 无符号
ZEROFILL 零填充
NOT NULL 非空
DEFAULT 默认值
PRIMARY KEY 主键，标识记录唯一性，值不重复且不为空
UNIQUE KEY 唯一性
AUTO_INCREMENT 自动增长，数值列，配合索引
FOREIGN KEY 外键约束 
# 实例
CREATE TABLE IF NOT EXISTS `tbl`(
   `id` INT UNSIGNED AUTO_INCREMENT,
   `title` VARCHAR(100) NOT NULL,
   `author` VARCHAR(40) NOT NULL,
   `date` DATE,
   PRIMARY KEY ( `id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

7.2 查看表

SHOW [FULL] TABLES [[FROM/IN] dbname] [LIKE 'pattern' / WHERE expr]
#实例
SHOW TABLES;

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

SHOW CREATE TABLE tlbname;

7.2.2 查看表结构：

DESC tblname;
DESCRIBE tblname;
SHOW COLUMNS FROM tblname；

7.4 删除表

DROP TABLE [IF EXISTS] tblname;

7.5 表结构操作

7.5.1 添加字段

ALTER TABLE tblname
ADD 字段名称 字段属性 [完整性约束条件]
[FIRST|AFTER 字段名称];

7.5.2 删除字段

ALTER TABLE tblname
DROP 字段名称;

7.5.3 添加默认值

ALTER TABLE tblname
ALTER 字段名称 SET DEFAULT 默认值;

7.5.4 删除默认值

ALTER TABLE tblname
ALTER 字段名称 DROP DEFAULT;

7.5.5 修改字段类型，属性

ALTER TABLE tblname
MODIFY 字段名称 字段类型 [字段属性] [FIRST|AFTER 字段名称];

7.5.6 修改字段名称、类型、属性

ALTER TABLE tblname
CHANGE 原字段名称 新字段名称 字段类型 字段属性
[FIRST|AFTER 字段名称];

7.5.7 添加主键

ALTER TABLE tblname
ADD PRIMARY KEY(字段名称);

7.5.8 删除主键

ALTER TABLE tblname
DROP PRIMARY KEY;

7.5.9 添加唯一索引

# 1
ALTER TABLE tblname
ADD UNIQUE KEY indexname(字段名称);
# 2
ALTER TABLE tblname
ADD UNIQUE INDEX indexname(字段名称);

7.5.10 删除唯一索引

ALTER TABLE tblname
DROP indexname;

7.5.11 修改数据表名称

# 1
ALTER TABLE tblname
RENAME [TO|AS] newtblname;
# 2
RENAME TABLE tblname TO newtblname;

8. 数据操作

8.1 添加记录

INSERT [INTO] tblname ( field1, field2,...fieldN )
VALUES ( value1, value2,...valueN );

8.1.1 不指定字段名称

INSERT tblname VALUES ( value1, value2,...valueN );

8.1.2 列出指定字段

INSERT tblname ( field1, field2,...fieldN ) 
VALUES ( value1, value2,...valueN );

8.1.3 一次添加多条记录

# 1
INSERT tblname ( field1, field2,...fieldN ) VALUES 
( value1, value2,...valueN ),
( value1, value2,...valueN ),
...
( value1, value2,...valueN );
# 2 
INSERT tblname SET field1 = value1, field2 = value2,...;
# 3
INSERT tblname [(field1, field2,...fieldN)] 
SELECT field1, field2,...fieldN
FROM tblname [WHERE 条件];

8.2 修改记录

# 如果不添加条件，整个表中的记录都会更新
UPDATE tblname SET field1 = value1, field2 = value2,... 
[WHERE 条件];

8.3 删除记录

# 如果不加入条件，所有记录都会删除，增长值不会改变
DELETE FROM tblname [WHERE 条件];
# 设置增长值为1
ALTER TABLE tblname AUTO_INCREMENT = 1;
# 彻底清空数据表
TRUNCATE [TABLE] tblname;

8.4 查询记录

SELECT 条件1，条件2... FROM tblname
[WHERE 条件]
[GROUP BY colname或position HAVING 二次筛选]
[ORDER BY colname,postion或expr [ASC|DESC]]
[LIMIT 限制结果集显示条数];

8.4.1 查询所有记录

SELECT * FROM tblname;
SELECT * FROM dbname.tblname;

8.4.2 指定字段查询

SELECT 字段名称,... FROM tblname;

8.4.3 给字段起别名

SELECT 字段名称,... [AS] 别名名称,... FROM dbname.tblname;

8.4.4 给表起别名

SELECT 字段名称,... FROM tblname [AS] 别名;

8.4.5 WHERE条件筛选记录

SELECT field1, field2,...fieldN FROM table_name1, table_name2...
[WHERE condition1 [AND|OR] condition2.....
操作 符号
比较运算符 >, >=, <, <=, !=, <> ,<=>
指定范围 [NOT] BETWEEN...AND...
指定集合 [NOT] IN (value,...)
逻辑运算符 AND, OR
匹配字符 [NOT] LIKE; % （任意长度字符）; _ （任意一个字符）

8.4.6 GROUP BY分组

把值放到一个组中，结果只会显示组中一条记录

  • 配合GROUP_CONCAT()查看组中某个字段的详细信息
SELECT GROUP_CONCAT(username),age,sex,addr
FROM tblname
GROUP BY sex;
  • 配合聚合函数使用
    COUNT()统计总数; SUM()求和; MAX()最大值; MIN()最小值; AVG()平均值
    注：COUNT(字段名称),不统计值为NULL记录; COUNT(*)统计所有
SELECT sex, username, COUNT(*) FROM tblname
GROUP BY sex;
  • 配合WITH ROLLUP使用，在记录末尾添加一条，为所有记录总和
SELECT sex, username FROM tblname
GROUP BY sex
WITH ROLLUP;
  • HAVING字句对分组结果进行二次筛选
SELECT sex, username FROM tblname
GROUP BY sex
HAVING sex = '男';

8.4.7 ORDER BY排序

# 默认排序为升序 ASC，可指定降序 DESC
# 1.按照多个字段排序
SELECT id, username, age
FROM tblname
ORDER BY age ASC, id ASC;
# 2.随机排序
SELECT id, username, age
FROM tblname
ORDER BY RAND();

8.4.8 LIMIT 限制结果集展示条数

# 1.显示前count条记录
SELECT * FROM tblname
LIMIT count;
# 2. 从offset页开始，显示count条记录
# offset从0开始编号
SELECT * FROM tblname
LIMIT offset, count;

8.5 多表查询

8.5.1 笛卡尔积形式

# 列出所有可能连接
SELECT * FROM tblname1 CROSS JOIN tblname2;

8.5.2 内连接形式

# 查询两个表中符合连接条件的记录
SELECT 字段名称,... FROM tblname1 
INNER JOIN tblname2
ON 连接条件;
# 例
SELECT e.id, e.username, e.age, d.depName 
FROM emp AS e 
INNER JOIN dep AS d
ON d.depID = d.id;

8.5.3 外连接形式

  • 左外连接
# 先显示左表中的全部记录，再去右表中查询复合条件记录，不符合以NULL代替
SELECT 字段名称,... FROM tblname1 
LEFT OUTER JOIN tblname2
ON 连接条件;
  • 右外连接
# 先显示右表中的全部记录，再去左表中查询复合条件记录，不符合以NULL代替
SELECT 字段名称,... FROM tblname1 
RIGHT OUTER JOIN tblname2
ON 连接条件;

8.6 外键约束

只有InnoDB支持外键，详情查看存储引擎相关资料。

8.6.1 创建外键

  • 建表时指定
# 先创建主表，子表的外键字段类型和主表要相似
# 如果外键字段没有创建索引，会自动创建
# 子表外键关联必须是父表的主键
[CONSTRAINT 外键名称] FOREIGN KEY (字段名称) 
REFERENCES 主表 (字段名称);
  • 动态添加删除外键
# 添加：要保证记录合法
ALTER TABLE tblname 
ADD FOREIGN KEY (外键字段)
REFERENCES 主表 (字段名称);
# 删除
ALTER TABLE tblname 
DROP FOREIGN KEY (外键字段);

8.6.2 外键约束

约束条件 作用
CASCADE 从父表删除或更新，子表相同操作
SET NULL 从父表删除或更新,设置子表外键列为NULL
NO ACTION/RESTRICT 拒绝对父表做更新或删除 
# 使用方法，设置时指定
# 删除约束
ALTER TABLE tblname 
ADD FOREIGN KEY (外键字段)
ON DELETE CASCADE;
# 更新约束
ALTER TABLE tblname 
ADD FOREIGN KEY (外键字段)
ON UPDATE CASCADE;

8.7 特殊形式查询

8.7.1 子查询

SELECT 字段名称 FROM tblname
WHERE colname = (SELECT colname FROM tblname);
  • 由IN引发的子查询
# 也可以使用NOT 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);
  • 由EXISTS引发的子查询
SELECT * FROM emp WHERE EXISTS (
SELECT depName FROM dep WHERE id = 10);
  • 带有ANY,SOME,ALL关键字的子查询
运算符 ANY SOME ALL
">",">=" 最小值 最小值 最大值
"<", "<=" 最大值 最大值 最小值
"=" 任意值 任意值
"<>", "!=" 任意值 
SELECT * FROM stu
WHERE score >= ANY(
SELECT score FROM level);
  • 其他方式
# INSERT ... SELECT
# CREATE ... SELECT
CREATE TABLE user(
    id INT UNSIGNED AUTO_INCREMENT KEY,
    username VARCHAR(20)
) SELECT id ,username FROM emp;
# 去掉字段重复值 DISTINCT
SELECT DISTINCT (username) FROM emp;

8.7.2 联合查询

# UNION 去掉重复值
SELECT 字段名称,... FROM tblname1
UNION
SELECT 字段名称,... FROM tblname2
# UNION ALL 简单合并
SELECT 字段名称,... FROM tblname1
UNION ALL
SELECT 字段名称,... FROM tblname2

8.7.3 自身连接查询

可实现无极限分类。

9. 常用函数

9.1 数学函数

函数 作用 函数 作用
CEIL() 进一取整 ABS() 绝对值
FLOOR() 舍一取整 POWER() a^b
ROUND() 四舍五入 PI() 圆周率
TRUNCATE() 截取 RAND() 随机数
MOD() 取余 SIGN() 符号函数
EXP(a) e^a

9.2 字符串函数

函数 作用 函数 作用
CHAR_LENGTH() 得到字符串字符数 LENGTH() 得到字符串长度
CONCAT(str1,str2) 合并字符串 CONCAT_WS(separator, str1, str2) 已注定分隔符拼接字符串
UPPER()/UCASE() 转大写 LOWER()/LCASE() 转小写
REVERSE() 字符串反转 LEFT(str,n) 返回前几个字符
RIGHT() 返回后几个字符 LPAD()/RPAD(str1,n,str2) 用字符串左/右填充到指定长度
TRIM()/LTRIM()/RTRIM() 去掉全部/左/右空格 REPEAT(str,n) 重复指定次数
REPLACE(str,str1,str2) 替换字符串 SUBSTRING(str,n1,n2) 截取子串
STRCMP(str1,str2) 比较字符串

9.3 日期时间函数

函数 作用 函数 作用
CURDATE()/CURRENT_DATE() 返回当前日期 CURTIME()/CURRENT_TIME() 当前时间
NOW()/CURRENT_TIMESTAMP()/SYSDATE() 返回日期和时间 MONTH() 返回日期中月份
DAYNAME() 返回星期几 DAYOFWEEK() 一周内第几天
WEEK() 一年第几个星期 YEAR() 返回年份
HOUR() 小时 MINUTE() 分钟
SECOND() 秒数 DATEDIFF() 计算日期相差天数

9.4 其他函数

函数 作用 函数 作用
VERSION() 系统版本 CONNECTION_ID() 连接数
DATABASE()/SCHEMA() 打开数据库 USER()/CURRENT_USER() 当前用户
SYSTEM_USER()/SESSION_USER() 当前用户 LAST_INSERT_ID() 上一个自增长id
MD5() 加密算法 PASSWORD() 默认用户加密算法

10. 导入导出数据

10.1 导出数据

10.1.1 SELECT ... INTO OUTFILE

SELECT * FROM runoob_tbl 
INTO OUTFILE '/tmp/tables.txt';
# 生成一个文件，各值用逗号隔开
SELECT a,b,a+b INTO OUTFILE '/tmp/result.text'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM test_table;

10.1.2 mysqldump导出

mysqldump是mysql用于转存储数据库的实用程序。它主要产生一个SQL脚本，其中包含从头重新创建数据库所必需的命令CREATE TABLE INSERT等。

# 1.命令栏导出
$ mysqldump -u root -p --no-create-info \
            --tab=/tmp DB tbl
password ******
# 2.导出表到指定文件
$ mysqldump -u root -p DB tbl > dump.txt
password ******
# 3.导出整个数据库数据
$ mysqldump -u root -p DB > database_dump.txt
password ******
# 4.备份所有数据库
$ mysqldump -u root -p --all-databases > database_dump.txt
password ******

10.2 导入数据

10.2.1 LOAD DATA

# 1.导入dump.txt 
LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl;
# 2.明确指出列的分隔符和尾行标记
LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl
FIELDS TERMINATED BY ':'
LINES TERMINATED BY '\r\n';
# 3.指定列的顺序
LOAD DATA LOCAL INFILE 'dump.txt' 
INTO TABLE mytbl (b, c, a);

10.2.2 mysqldump导入

# 1.导入dump.txt 
$ mysqlimport -u root -p --local database_name dump.txt
password *****
# 2.指定选项来设置指定格式
$ mysqlimport -u root -p --local --fields-terminated-by=":" \
   --lines-terminated-by="\r\n"  database_name dump.txt
password *****
# 3.使用 --columns 选项来设置列的顺序
$ mysqlimport -u root -p --local --columns=b,c,a \
    database_name dump.txt
password *****

11. 图形化工具

MySQL Workbench 下载: https://dev.mysql.com/downloads/workbench/

12. 快速参考

SQL 语句 语法
AND / OR SELECT column_name(s) FROM table_name WHERE condition AND/OR condition
ALTER TABLE ALTER TABLE table_name ADD column_name datatype
ALTER TABLE table_name DROP COLUMN column_name
AS (alias) SELECT column_name AS column_alias FROM table_name
SELECT column_name FROM table_name AS table_alias
BETWEEN SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2
CREATE DATABASE CREATE DATABASE database_name
CREATE TABLE CREATE TABLE table_name(
column_name1 data_type,
column_name2 data_type,
column_name2 data_type,
...)
CREATE INDEX CREATE INDEX index_name ON table_name (column_name)
CREATE UNIQUE INDEX index_name ON table_name (column_name)
CREATE VIEW CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition
DELETE DELETE FROM table_name WHERE some_column=some_value
DELETE FROM table_name
DELETE * FROM table_name
DROP DATABASE DROP DATABASE database_name
DROP INDEX DROP INDEX table_name.index_name (SQL Server)
DROP INDEX index_name ON table_name (MS Access)
DROP INDEX index_name (DB2/Oracle)
ALTER TABLE table_name
DROP INDEX index_name (MySQL)
DROP TABLE DROP TABLE table_name
GROUP BY SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value
IN SELECT column_name(s)
FROM table_name
WHERE column_name
IN (value1,value2,..)
INSERT INTO INSERT INTO table_nameVALUES (value1, value2, value3,....)
INSERT INTO table_name(column1, column2, column3,...)VALUES (value1, value2, value3,....)
INNER JOIN SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name
LEFT JOIN SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name
RIGHT JOIN SELECT column_name(s)
FROM table_name1
RIGHT JOIN table_name2
ON table_name1.column_name=table_name2.column_name
FULL JOIN SELECT column_name(s)
FROM table_name1
FULL JOIN table_name2
ON table_name1.column_name=table_name2.column_name
LIKE SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern
ORDER BY SELECT column_name(s)
FROM table_name
ORDER BY column_name [ASC/DESC]
SELECT SELECT column_name(s) FROM table_name
SELECT * SELECT * FROM table_name
SELECT DISTINCT SELECT DISTINCT column_name(s) FROM table_name
SELECT INTO SELECT * INTO new_table_name [IN externaldatabase] FROM old_table_name
SELECT column_name(s) INTO new_table_name [IN externaldatabase] FROM old_table_name
SELECT TOP SELECT TOP number/percent column_name(s) FROM table_name
TRUNCATE TABLE TRUNCATE TABLE table_name
UNION SELECT column_name(s) FROM table_name1 UNION SELECT column_name(s) FROM table_name2
UNION ALL SELECT column_name(s) FROM table_name1 UNION ALL SELECT column_name(s) FROM table_name2
UPDATE UPDATE table_name
SET column1=value, column2=value,...
WHERE some_column=some_value
WHERE SELECT column_name(s) FROM table_name WHERE column_name operator value

更新中...

posted @ 2018-04-26 22:45  阿婆土豆  阅读(503)  评论(0)  编辑  收藏  举报