1、MySQL概述
MySQL是一种关系型数据库
1.1、存储引擎
- 在一个数据库中有多种引擎，常用的两个引擎是：
InnoDB，MyISAM
| 索引 | 锁 | 全文索引 | 主键 | 速度 | 事务 | 外键 | |
|---|---|---|---|---|---|---|---|
| InnoDB | 数据和主索引文件是一起的，辅助索引记录的是主键值 | 行锁 | 不支持FULLTEXT类型的全文索引，但是InnoDB可以使用sphinx插件支持全文索引，并且效果更好 | InnoDB如果没有设定主键或非空唯一索引，就会自动生成一个6字节的主键，数据是主索引的一部分，附加索引保存的是主索引的值 | 支持 | 支持 | |
| MyISAM | 数据和主索引文件是分开的，辅助索引和主索引类似 | 表锁 | 支持FULLTEXT类型的全文索引 | MyISAM允许没有任何索引和主键的表存在，索引都是保存行的地址 | 较快 | 不支持 | 不支持 |
1.2、列类型
常见类型比较
| JAVA | Java备注 | MYSQL | MYSQL备注 |
|---|---|---|---|
| Int | 短整型 | int | 短整型 |
| long | 长整型 | bigint | 长整型 |
| float | 单精度 | float | 单精度 |
| double | 双精度 | double | 双精度 |
| bigdecimal[类] | 金融数据 | decimal | 金融数据 |
| String | 字符串 | char | 不可变字符串 |
| varchar | 可变字符串 | ||
| Boolean | 布尔值 | bit | 取值0和1 |
- varchar与char的区别
- char的长度是不可变的，而varchar的长度是可变的
- char未达到设置长度的部分用空格填充，而varchar则是有多少字符就存储多少个
- char最多可以存放255个字符，varchar的最大长度为65535个字节，varchar可存放的字符数跟编码有关
日期类型
java->Date
| 类型 | 备注 |
|---|---|
| DATETIME | 年月日，时分秒 |
| DATE | 年月日 |
| TIME | 时分秒 |
| YEAR | 年 |
| TIMESTAMP | 时间缀(时间戳--范围19700101080001 - 203801191111407 基本不会用它,了解) |
其它类型
| 类型 | 备注 |
|---|---|
| BLOB(TINYBLOB,BLOB,LONGBLOB,...) | 这一类都是放二进制文件(音乐,图片,视频等) 不建议使用 |
| TEXT | 大量文本使用 （4G） |
2、DDL-操作数据库和表
2.1、操作数据库
/* 查询全部数据库 */
show databases;
/* 进入数据库 */
use 数据库名;
/* 创建一个数据库 */
create database 数据库名;
/* 删除一个数据库 */
drop database 数据库名;
2.2、操作表
/* 查询全部表 */
show tables;
/* 查询表的结构 */
desc 表名;
2.3、表的约束
- 主键约束
- 每张表必须要有主键
- 主键非空且唯一，代表某一条数据的唯一性
- 主键类型：
- 自然主键：有业务含义的主键(身份证,电话号码,用户名等) --> 不建议使用
- 代理主键：没有业务含义的主键 --> 建议使用
- 非空约束：必须有值，不能为空
- 唯一约束：不可重复
- 外键约束
- 自增：代理主键一般加上自增，自动增加值
- 默认值：不输入修改时默认使用一个值
3、DQL-查询操作
3.1、简单查询
3.1.1、查询全部
SELECT 列名1,列名2,... FROM 表名;
SELECT * FROM 表名; *代表全部文件
3.1.2、消除重复数据
/* 使用DISTINCT关键字 */
SELECT DISTINCT 列名 FROM 表名;
3.1.3、列名重命名
/* 使用AS关键字 */
SELECT 列名1 AS 别名1,列名2 AS 别名2,... FROM 表名;
3.1.4、设置显示格式（字符串拼接）
/* 使用CONCAT关键字 */
SELECT CONCAT(列名1,列名2,...) FROM 表名;
3.2、过滤查询
/* 使用WHERE关键字 */
SELECT * FROM 表名 WHERE 条件;
3.2.1、运算符条件
/* 使用WHERE关键字,不等于条件可使用!=或<> */
SELECT * FROM 表名 WHERE 条件;
3.2.2、逻辑运算符条件
/* 使用WHERE关键字,逻辑运算符AND,OR,NOT */
SELECT * FROM 表名 WHERE 条件;
3.2.3、范围条件
/* 使用BETWEEN关键字,闭区间包括范围1和范围2 */
SELECT * FROM 表名 WHERE 条件 BETWEEN 范围1 AND 范围2;
3.2.4、集合条件
/* 使用IN关键字 */
SELECT * FROM 表名 WHERE 条件 IN(值1,值2,...);
SELECT * FROM 表名 WHERE 条件 NOT IN(值1,值2,...);
3.2.5、空值查询
/* 使用IS关键字 */
SELECT * FROM 表名 WHERE 列名 IS null;
SELECT * FROM 表名 WHERE 列名 IS NOT null;
3.2.6、模糊查询
/* 使用LIKE关键字 */
/* %通配符：用来表示0个或多个任意的字符 */
/* _通配符：用来表示1个任意的字符 */
SELECT * FROM 表名 WHERE 列名 LIKE "关键字";
3.2.6、正则表达式查询
/* 使用REGEXP关键字 */
/* "[A-Za-z]+" : 包含字母 */
SELECT * FROM 表名 WHERE 列名 REGEXP "[A-Za-z]+";
3.3、结果排序
/* 使用ORDER BY关键字,多个排序使用逗号隔开 */
/* ASC：升序，默认不写表示升序 */
/* DESC：降序 */
SELECT * FROM 表名 ORDER BY 列名1 DESC / ASC , 列名2 DESC / ASC;
3.4、分页查询
/* 使用LIMIT关键字 */
SELECT * FROM 表名 LIMIT (当前页数-1)*每页条数,每页条数;
3.5、聚集函数
/* AVG(列名)：求平均数 */
SELECT AVG(列名) FROM 表名;
/* COUNT(列名)：求记录条数,当列的值为null时不会记录,故一般使用主键 */
SELECT COUNT(列名) FROM 表名;
/* MAX(列名)：求最大值 */
SELECT MAX(列名) FROM 表名;
/* MIN(列名)：求最小值 */
SELECT MIN(列名) FROM 表名;
/* SUM(列名)：求和 */
SELECT SUM(列名) FROM 表名;
3.6、分组查询
/* SELECT 列名 FROM 表名 GROUP BY 列名 */
SELECT AVG(salePrice),dir_id FROM product GROUP BY dir_id;
/* SELECT 列名 FROM 表名 GROUP BY 列名 HABING 条件 */
/* HABING关键字对分组之后的结果进行筛选，WHERE关键字对分组之前的结果进行筛选 */
SELECT dir_id,SUM(salePrice) FROM product GROUP BY dir_id HAVING SUM(salePrice) > 1500;
4、DQL-多表查询操作
4.1、笛卡尔积
在多表查询中，没有指定连接条件而产生的结果，使用WHERE关键字加入连接条件可以避免笛卡尔积
4.2、内连接
/* 隐式内连接,不包含JOIN */
SELECT * FROM 列名1,列名2 WHERE 条件;
/* 显式内连接,使用INNER JOIN连接,INNER可省略 */
SELECT * FROM 列名1 JOIN 列名2 ON 条件;
4.3、外连接
/* 左外连接,使用LEFT JOIN连接 */
SELECT * FROM 列名1 LEFT JOIN 列名2 ON 条件;
/* 右外连接,使用RIGHT JOIN连接 */
SELECT * FROM 列名1 RIGHT JOIN 列名2 ON 条件;
4.4、子查询
/* WHERE中的查询条件来自于另一个查询 */
SELECT * FROM product WHERE salePrice > (SELECT salePrice FROM product WHERE productName = "罗技MX1100");
4.5、自连接
/* 本质上仍为内连接和外连接,只是连接的是同一个表,由于连接的是一个表,故必须重命名以作区分 */
SELECT * FROM 表名1 重命名,表名2 重命名 WHERE 条件;
SELECT * FROM 表名1 重命名 JOIN 表名2 重命名 ON 条件;
SELECT * FROM 表名1 重命名 LEFT JOIN 表名2 重命名 ON 条件;
SELECT * FROM 表名1 重命名 RIGHT JOIN 表名2 重命名 ON 条件;
5、DML-数据操作
5.1、插入数据
INSERT INTO 表名 (列名1,列名2,...) VALUES (值1,值2,...);
5.2、修改数据
UPDATE 表名 SET 列名1 = 值1,列名2 = 值2,... WHERE 条件;
5.3、删除数据
DELETE FROM 表名 WHERE 条件;
6、函数
6.1、聚集函数
/* AVG(列名)：求平均数 */
SELECT AVG(列名) FROM 表名;
/* COUNT(列名)：求记录条数,当列的值为null时不会记录,故一般使用主键 */
SELECT COUNT(列名) FROM 表名;
/* MAX(列名)：求最大值 */
SELECT MAX(列名) FROM 表名;
/* MIN(列名)：求最小值 */
SELECT MIN(列名) FROM 表名;
/* SUM(列名)：求和 */
SELECT SUM(列名) FROM 表名;
6.2、类型转换函数
/* 使用CAST(值 AS 类型),CONVERT(值, 类型)进行类型转换 */
SELECT CAST(值 AS 类型) FROM 表名;
SELECT CONVERT(值, 类型) FROM 表名;
/* 常见MySQL类型 */
/* 二进制，同带binary前缀的效果 : BINARY */
/* 字符型，可带参数 : CHAR() */
/* 日期 : DATE */
/* 时间: TIME */
/* 日期时间型 : DATETIME */
/* 浮点数 : DECIMAL */
/* 整数 : SIGNED */
/* 无符号整数 : UNSIGNED */
6.3、字符串函数
/* LENGHT(s): 返回传入字符串长度,编码集为utf8时,中文字符长度为3 */
/* CONCAT(s1,S2,...): 拼接字符串,若其中有任意参数为null,则返回null */
/* TRIM(s): 去掉传入字符串开始和结尾处的空格 */
/* UPPER(s): 将传入字符串的所有字母改为大写字母 */
/* LOWER(s): 将传入字符串的所有字母改为小写字母 */
6.4、日期函数
/* 返回当前日期 */
CURDATE(),CURRENT_DATE(),CURRENT_DATE
/* 返回当前时间 */
CURTIME(),CURRENT_TIME(),CURRENT_TIME
/* 返回当前日期和时间 */
NOW(),CURRENT_TIMESTAMP(),LOCALTIME(),SYSDATE(),LOCALTIMESTAMP()
/* 相关函数 */
YEAR(d)：接受date参数，并返回日期的年份
6.5、控制流程函数
/* SELETE IF(参数1,参数2,参数3),若参数1为真,则返回参数2,若参数1为假,则返回参数3 */
/* SELETE IFNULL(参数1,参数2),若参数1为空,则返回参数2,若参数1不为空,则返回参数1 */
7、备份与恢复
7.1、使用DOS命令
导入：mysqldump -u账户 -p密码 数据库名称 > 脚本文件存储地(地址精确到文件,例如:F:/xx.sql)
导出：mysqldump -u账户 -p密码 数据库名称 < 脚本文件存储地(地址精确到文件,例如:F:/xx.sql)
7.2、使用Navicat工具
导入：右键数据库 --> 运行SQL文件
导出：右键数据库 --> 转储SQL文件
