韩顺平Java | C24 MySQL数据库(上)

MySQL官方文档

MySQL安装和配置

MySQL5.7(稳定)、Navicat、SQLyog
MySQL三层结构：数据库管理系统DBMS-数据库DB-表Table（行/记录-用对象表示）

使用命令行窗口连接MySQL数据库

net stop mysql服务名
net start mysql服务名
mysql -h 主机名 -P端口 -u用户名 -p密码 //无-h则默认本机，无-P则默认3306，实际工作中3306一般修改
quit

数据库

DDL：数据定义语句。creat 表, 库…
DML：数据操作语句。增加insert，修改update，删除delete
DQL：数据查询语句。select
DCL：数据控制语句。管理数据库，如grant revoke

创建/查看/删除数据库

# 创建数据库
CREATE DATABASE [IF NOT EXISTS] db_name [create_specification [, create_specification] ...] #[]代表可选。如果不存在则创建，存在则不创建，不写[IF NOT EXISTS]条件存在则报错
create_specification:
[DEFAULT] CHARACTER SET charset_name #指定字符集，默认utf8
[DEFAULT] COLLATE collation_name #指定字符集校对规则（常用的utf8_bin[区分大小写]、默认utf8_general_ci[不区分大小写]）

# 查看数据库
SHOW DATABASES #显示当前数据库服务器中的所有数据库
SHOW CREATE DATABASE db_name #显示数据库创建的定义信息
#CREATE DATABASE `hsp_db01` /*!40100 DEFAULT CHARACTER SET utf8 */ #创建数据可和表的时候，使用``规避关键字， /*代表数据库版本要在4以上，该语句方可执行

# 删除数据库
DROP DATABASE [IF EXISTS] db_name #慎用

备份恢复数据库

# 备份数据库（DOS执行命令行）
mysqldump -u 用户名 -p -B 数据库1 数据库2 数据库n > 文件名.sql #mysqldump.exe在mysql安装目录\bin
# 恢复数据库（进入MySQL命令行再执行 mysql -u root -p）。 备份文件就是对应sql语句
Source 文件名.sql #或将备份文件内容sql语句贴入查询编辑器中执行一遍
# 备份库的表
mqsqldump -u 用户名 -p密码 数据库 表1 表2 表n > d:\\文件名.sql

表

创建/删除/修改表

# 创建表
CREATE TABLE table_name
(
	field1 datatype,
	field2 datatype,
	field3 datatype
)character set 字符集 collate 校对规则 engine 引擎

# 修改表
# 添加列
ALTER TABLE tablename ADD (columnname datatype [DAEFAULT expr][, column datatype]...); #列名/类型/默认值/表达式/列... 无需括号
# ALTER TABLE emp ADD image VARCHAR(32) NOT NULL DEFAULT '' [FIRST | AFTER 'resume'; #注意引号，新添加的列名无，原有的列名有
# 修改列
ALTER TABLE tablename
MODIFY    (column datatype [DEFAULT expr][, column datatype]...);
# ALTER TABLE employee CHANGE `name` user_name VARCHAR(32) NOT NULL DEFAULT ''; #修改列名
# 删除列
ALTER TABLE tablename
DROP    (column);
查看表结构：desc 表名; -- 可以查看表的列
# 修改表名：
Rename table 表名 to 新表名
# 修改表字符集：
alter table 表名 character set 字符集; 

# 删除表
DROP TABLE tablename;

※MySQL数据类型

※CURD

Insert 添加数据

INSERT INTO table_name [(column [, column...])] VALUES (value [, value...]);
# 注意：
-- 字符和日期型数据应包含在单引号中；
-- 列可以插入空值（前提是该字段允许为空）
INSERT INTO tablename value (NULL);

添加多条记录

INSERT INTO tablename (column1, column2, column3...) VALUES (v1, v2, v3), (u1, u2, u3), (w1, w2, w3);
--如果是给表中所有字段添加数据，可以不写前面的字段名称
--当不给某个字段值时，如果允许为空，有默认值就会添加默认值，没有报错；创建表时可以指定希望的默认值

Update 更新数据

UPDATE tbl_name SET col_name=expr1 [, col_name2=expr2 ...] [WHERE where_definition] # 不写where条件代表修改表的所有记录/数据/行，慎用

Delete 删除数据

DELETE FROM tbl_name [WHERE where_defination] #无where条件代表删除所有数据/记录/行

※Select查找

Select 查找单表

SELECT [DISTINCT] *|{column1, column2, column3...} FROM tablename; #distinct去重，*代表查询所有列
SELECT *|{column1 | expression, column2 | expression...} FROM tablename; #使用表达式对查询的列进行运算
SELECT columnname AS 别名 from tablename;
# SELECT `name` (chinese + math + english) AS total_score FROM student;
SELECT column1. column2, column3... FROM tablename [WHERE where_defination] ORDER BY columnx asc|desc; # 默认asc升序 ascending/descending

※函数

合计/统计函数

# count 返回行的总数，不会统计到列值为null的
SELECT COUNT(*)|count(columnname) FROM tablename [WHERE where_definition]
# sum 返回满足条件的行的和，一般用在数值类
SELECT sum(columnname) [, sum(columnname)] from tablename [where_defination]
# avg 返回满足条件的列的平均值
SELECT AVG(columnname) [, AVG(columnname)] from tablename [where where_defination]
# max/min 返回满足条件的列的最大/小值，数值范围内统计
SELECT MAX(columnname)|MIN(columnname) from tablename [where where_defination] 

分组统计

group by 对查询结果分组统计（示意图）
having 子句用于限制分组显示结果

# 使用group by子句对列进行分组，使用having子句对分组后的结果进行过滤
SELECT column1, column2, column3... FROM tablename GROUP BY columnname HAVING ...

-- ？显示每个部门每种岗位的平均工资和最低工资
SELECT AVG(sal), MIN(sal), deptno, job FROM emp GROUP BY deptno, job;
-- ？显示平均工资低于2000的部门号和它的平均工资 //别名
SELECT deptno, AVG(sal) AS avg_sal FROM emp GROUP BY deptno HAVING avg_sal < 2000; # 使用别名进行过滤保留

字符串函数

TRIM(string); 去除字符串两端空格

数学函数

SELECT CONV(16, 16, 10) FROM DAUL; #虚拟表
SELECT RAND([seed]) FROM DAUL; #随机返回0-1随机数，加任意种子返回固定0-1随机数

时间日期

SELECT CURRENT_TIMESTAMP() FROM DAUL; #datetime: YYYY-MM-DD HH:MM:SS
SELECT NOW() FROM DAUL; #datatime: YYYY-MM-DD HH:MM:SS
SELECT * FROM mes WHERE DATE_ADD(send_time, INTERVAL 10 MINUTE) >= NOW()); #查询10min内发布的新闻

SELECT UNIT_TIMESTAMP() FROM DAUL; #返回1970-1-1到现在的秒数，类似于Java中的System.current]
SELECT FROM_UNIXTIME(1618483484, '%Y-%m-&d %H:%i:%s') FROM DAUL; #把unix_timestamp秒数[时间戳]转成指定格式的日期。意义：“再开发中，可以存放一个整数表示日期时间，通过FROM_UNIXTIME转换

加密和系统函数

SELECT USER() FROM DAUL; --用户@IP地址
SELECT MD(str) FROM DAUL; --为字符串计算出一个MD5 32的字符串，常用(用户密码)加密
SELECT *FREOM mysql.user; --查询用户密码PASSWORD算法加密 authentication_string)权限等

流程控制函数

IS NULL; --判断为空
IS NOT NULL; --判断不为空

---

查询加强

在mysql中，日期类型可以直接比较
如何使用like操作符(条件模糊查询)：%：表示0到多个字符； _：表示单个字符

DESC tablename; --查询表结构
SELECT * FROM emp ORDER BY deptno ASC, sal DESC; --按部门升序，薪水降序查询

分页查询

SELECT ... LIMIT START, ROWS; --写在SELECT语句结尾，表示从start+1行开始取，取出rows行，start从0开始计算；按页显示 start = 每页显示记录数*(页数-1)

多子句查询

SELECT column1 FROM tablename
	GROUP BY column1
	HAVING condition
	ORDER BY column2
	LIMIT startnum, rowsnum;