Mysql 基础脑图笔记

MySQL 数据库简介

Windows下安装MySQL

配置初始化文件（win）

[mysqld]
# 设置3306端口
port=3306
# 设置mysql的安装目录
basedir=G:\WWW\mysql
# 设置mysql数据库的数据的存放目录
datadir=G:\WWW\mysql\data  
# 允许最大连接数
max_connections=200
#允许连接失败的次数。防止有人从该主机试图攻击数据库系统
max_connect_errors=10
# 服务端使用的字符集默认为UTF8
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 默认使用“mysql_native_password”插件认证
default_authentication_plugin=mysql_native_password
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
[client]
# 设置mysql客户端连接服务端时默认使用的端口
port=3306
default-character-set=utf8

初始化 MySQL（win）

cd /d G:\WWW\mysql\bin #管理员身份运行 cmd ,进入 bin 目录
mysqld --initialize --console #初始化MySQL环境,需耐心等待几分钟
mysqld install #安装 MySQL 服务
net start mysql # 启动命令
net stop mysql # 关闭命令
mysqld remove #卸载 MySQL

Linux 下安装 MySQL

步骤

MySQL 配置文件（linux）

[mysqld]
# 设置3306端口
port=3306
# 设置mysql的安装目录
basedir=/usr/local/mysql
# 设置mysql数据库的数据的存放目录
datadir=/usr/local/mysql/data
# 允许最大连接数
max_connections=200
#允许连接失败的次数。防止有人从该主机试图攻击数据库系统
max_connect_errors=10
# 服务端使用的字符集默认为UTF8
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 默认使用“mysql_native_password”插件认证
default_authentication_plugin=mysql_native_password
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
[client]
# 设置mysql客户端连接服务端时默认使用的端口
port=3306
default-character-set=utf8

与 MySQL 建立连接

MySQL 创建数据库

MySQL 操作数据库

MySQL 的数据类型

MySQL 设计数据表

ALTER 命令修改表

修改表字段数据类型

ALTER TABLE `new_student`
	MODIFY COLUMN `name` 
	char(50)
	CHARACTER SET utf8 COLLATE utf8_general_ci 
	NOT NULL 
	DEFAULT '无名' 
	AFTER `id`;

新增表字段

ALTER TABLE `new_student`
	ADD COLUMN `sex` 
	tinyint(2) UNSIGNED 
	NOT NULL 
	DEFAULT 1 
	COMMENT '性别 : 1:男 2:女' 
	AFTER `id_number`;

删除表字段

ALTER TABLE `new_student`
	DROP COLUMN `sex`;

修改表字段名称

ALTER TABLE `new_student`
	CHANGE COLUMN `name` `new_name` 
	char(50) 
	CHARACTER SET utf8 COLLATE utf8_general_ci 
	NOT NULL 
	DEFAULT '无名' 
	AFTER `id`;

修改表字段默认值

ALTER TABLE `new_student`
	MODIFY COLUMN `new_name` 
	char(50) CHARACTER SET utf8 COLLATE utf8_general_ci 
	NOT NULL 
	DEFAULT '小花' 
	AFTER `id`;

删除数据表

DROP TABLE new_student;

需要删除数据表一定要谨慎，删除数据表之前最好做备份

INSERT 插入数据

向数据表插入一条数据

INSERT INTO teacher (name, age, id_number)
VALUES ('秦小贤', 18, '42011720200604088X');

向数据表插入多条数据

INSERT INTO teacher (name, age, id_number)
VALUES 
	('王小花', 19, '42011720200604077X'),
	('张晓丽', 18, '42011720200604099X'),
	('刘美丽', 20, '42011720200604020X'),
	('吴帅', 21, '42011720200604022X'),
	('张平', 22, '42011720200604033X')

SQL 简介

聚合函数

GROUP BY HAVING 举例

SELECT a.course_id, c.course_name, AVG(age)
FROM student_course a
	INNER JOIN student b ON a.student_id = b.id
	INNER JOIN course c ON a.course_id = c.id
GROUP BY a.course_id, c.course_name
HAVING AVG(age) >= 20;

条件判断函数

CASE条件判断举例

SELECT *
	, CASE name
		WHEN 'Tom' THEN '汤姆'
		WHEN 'Jack' THEN '杰克'
		WHEN 'Mary' THEN '玛丽'
		WHEN 'Timo' THEN '提莫'
		WHEN 'Bob' THEN '鲍勃'
		WHEN 'Judy' THEN '朱蒂'
		ELSE '未定义'
	END AS "chinese_name"
FROM teacher;

系统函数

函数名	说明
CONCAT(str0,str1)	将两个字段的值拼接在一起
CONCAT_WS(“拼接符”,str0,str1)	将两个字段的值使用指定拼接符拼接在一起
GROUP_CONCAT(expr)	将指定分组的字段内容拼接在一起
UPPER(str)	将字符串包含的英文字符小写转化成大写
lOWER(str)	将字符串包含的英文字符大写转化成小写
INITCAP(str)	将字符串的首字母变成大写
LENGTH(str)	获取字符串的长度
SUBSTR(str FROM pos FOR len)	截取字符串，其中 pos 表示其实位置，len 表示结束位置
TRIM(str)	去除字符串两边空格
INSTR(str,substr)	查找指定字符在字符串的中的位置，其中 substr 表示需要查找的字符，例如 ‘Tom’ 中 ‘T’ 的位置为 1，‘m’ 的位置为 3
LPAD(str,len,padstr)	左填充，其中 len，表示字符串总长度，padstr 表示填充的字符
RPAD(str,len,padstr)	右填充，其中 len，表示字符串总长度，padstr 表示填充的字符
LEFT(str,len)	取一个字符串的前多少位
RIGHT(str,len)	取一个字串的后多少位
CEIL(X)	向上取整
FLOOR(X)	向下取整
MOD(N,M)	取余，例如 MOD(age,5)，将字段 age 除以 5,除不尽的取余数
POWER(X,Y)	幂运算，例如 MOD(age,2)，获取字段 age 值的 2 次方的值
NOW()	获取当前日期和时间
CURDATE()	获取当前日期，不包含时间
CURTIME()	获取当前时间，不包含日期
YEAR(now())	获取当前的年份
HOUR(NOW())	获取当前时间的小时数
MINUTE(now())	获取当前时间的分钟数
SECOND(NOW())	获取当前时间的秒数
MONTHNAME(now())	获取当前日期的英文月份
MONTH(NOW())	获取当前日期的数字月份
DATE_ADD(date,INTERVAL expr unit)	查询日期的变化，例如 DATE_ADD(‘2020-03-03’,INTERVAL 10 day) 表示 2020-03-03 十天之后的日期
DATEDIFF(expr1,expr2)	日期差，例如 DATEDIFF(‘2019-12-29’，‘2019-12-01’) 表示 2019-12-29 距离 2019-12-01 的天数
DATE_FORMAT(date,format)	将指定日期转化为自定义格式，例如 DATE_FORMAT(‘2019-12-29’，’%m/%d/%y’) ，其中 %m 表示前面对应的月份，%d 表示前面对应的日期的天数，%y 表示前面对应的年份
STR_TO_DATE(str,format)	将指定日期转化为自定义格式，例如 DATE_FORMAT(‘12-29-2019’，’%m/%d/%y’)
DATE_FORMAT(date,format)	将指定日期转化为自定义格式，例如 DATE_FORMAT(‘2019/12/29’，’%m/%d/%y’)
MD5(str)	对字符串进行 MD5 转换
UNIX_TIMESTAMP()	将指定日期转化为时间戳
COUNT()	获取查询结果集条数
AVG(expr)	获取指定列平均值
SUM(expr)	获取指定字段值的总和
MIN(expr)	获取指定字段值的最小值
MAX(expr)	获取指定字段值的最大值

MySQL 中使用正则表达式

示例

SELECT * FROM student WHERE name REGEXP '^王';

手机号正则

^1([38][0-9]|4[579]|5[0-3,5-9]|6[6]|7[0135678]|9[89])\d{8}$

域名网址正则表达式

^(?=^.{3,255}$)(http(s)?:\/\/)?(www\.)?[a-zA-Z0-9][-a-zA-Z0-9]{0,62}(\.[a-zA-Z0-9][-a-zA-Z0-9]{0,62})+(：\d+)*(\/\w+\.\w+)*$

日期+时间正则表达式

^[1-9]\d{3}-(0[1-9]|1[0-2])-(0[1-9]|[1-2][0-9]|3[0-1])\s+(20|21|22|23|[0-1]\d)：[0-5]\d:[0-5]\d$

MySQL 存储引擎

查看所有存储引擎开启状态

SHOW ENGINES;