登录数据库

1.在MySQL安装目录下的的bin目录打开命令行界面(可按住shift再鼠标左击)。
2.输入mysql -u数据库用户名 -p命令。
3.输入密码登录数据库。

---

常用命令

SHOW DATABASES //显示所有已创建的数据库
USE db_name //进入某个数据库
CREATE DATABASE db_name //创建一个数据库
DROP DATABASE db_name //删除某个数据库
SHOW TABLES //显示当前数据库下所有的表
DESC tb_name //查看某个表各个字段的描述信息
SHOW CREATE TABLE tb_name //查看创建某个表时的信息
\T 某个文件  //将命令行下所有的信息输出到指定文件中
\t  //关闭输出日志功能

---

建表语句

CREATE TABLE [IF NOT EXISTS] tb_name(字段 描述)[ENGINE=INNODB(默认)|MyISAM] [CHARSET=utf-8(默认)];

CREATE TABLE IF NOT EXISTS user(
id int primary key auto_increment,
username varchar(20) not null unique,
passward varchar(20) not null,
addr varchar(10),
age tinyint,
sex enum('男','女','保密')
);

图片.png

插值操作

INSERT INTO user VALUES(1,'张三','1000','北京',16,'男');
INSERT INTO user(username,passward,age,sex) VALUES('张三丰','12345',22,'男');

图片.png

---

修改操作

对某个字段的属性进行操作
语句格式
ALTER TABLE tb_name [MODIFY] [ADD] [DROP] [ALTER] [CHANGE]

DROP：删除某个字段、索引等
ADD：添加某个字段、索引等
MODIFY：修改某个字段的数据类型
CHANGE：重命名列(要求数据类型一致)或修改某个字段的类型
ALTER：用于设置或删除默认值(DROP DEFAULT|SET DEFAULT '')

MODIFY与CHANGE的区别
CHANGE修改列名：

图片.png

CHANGE修改数据类型：

图片.png
MODIFY修改数据类型
图片.png

---

更新操作

对表中数据进行更新操作
语句格式
UPDATE tb_name SET 字段名=值 [WHERE] [ORDER BY] [LIMIT]
LIMIT后只能有一个参数，表示更新前几条记录。

UPDATE user SET age=13;

图片.png
注意不能添加TABLE字段，否则报错

---

删除操作

语句格式：DELETE FROM tb_name [WHERE 条件] [ORDER BY 字段] [LIMIT 限制条数]
LIMIT后只能有一个参数，表示删除前几条记录

DELETE FROM user WHERE id=11;

图片.png
彻底清空数据表(自增长值也会恢复初始值)：TRUNCATE [TABLE] tb_name

---

查询操作

单表查询

语句格式：
SELECT 字段名称 FROM tb_name [WHERE] [GROUP BY [WITH ROLLUP]] [HAVING] [ORDER BY] [LIMIT]

WHERE:条件查询
IN(10,20),BETWEEN .. AND ..：区间筛选
LIKE,NOT LIKE ：模糊查询，%表示有一个或多个字符，_表示有一个字符
AND OR > < = != <=>(可以筛选出NULL值)：逻辑运算

原始数据

图片.png

区间筛选：

图片.png
模糊查询：
图片.png

GROUP BY:对查询结果进行分组，但只会显示每个分组中的第一条数据内容；
通过GROUP_CONCAT(字段名称) 可以得到每个分组中具体的数据信息
COUNT(字段名)可以统计总数；
MAX(字段名)可以筛选出最大值；
MIN(字段名)可以筛选出最小值；
AVG(字段名)可以计算出平均值；
SUM(字段名)可以计算出总和；
配合AS 别名 使结果更加直观；
WITH ROLLUP 语句可以在结果末尾增加一行对所有数据进行统计。

分组查询：
SELECT sex,GROUP_CONCAT(username) AS username,COUNT(*) AS user,
MAX(age) AS max_age,MIN(age) AS min_age,AVG(age) AS avg_age,SUM(age) AS sum_age FROM user WHERE id<5 GROUP BY sex WITH ROLLUP;

图片.png

HAVING:对分组后的结果进行二次筛选，用法与WHERE用法基本相同。

SELECT id,GROUP_CONCAT(username) AS username,addr FROM user WHERE id<5 GROUP BY sex HAVING addr='上海' OR addr='北京';

图片.png

ORDER BY：对查询结果结果进行排序
DESC：降序     ASC：升序(默认)  NULL值最小  RAND()可以提取随机记录
可以使用多个字段的升降序组合在一起，当第一个字段值排序后出现相同值时按第二个字段的排序方法排序。

图片.png

LIMIT：限制显示条数
LIMIT 显示条数
LIMIT 偏移量,显示条数(可实现分页功能，第一条记录的偏移量为0)

图片.png

多表查询

原始数据

图片.png
图片.png

通过不同表中具有相同意义的字段将不同表连接起来

内连接查询

(显示两个表中符合连接条件的记录)：INNER JOIN
通过ON连接条件

外连接查询

左外连接：LEFT [OUTER] JOIN 显示左表的全部记录及右表中符合连接条件的记录。
右外连接：RIGHT [OUTER] JOIN 显示右表的全部记录及左表中符合连接条件的记录。

内连接查询

SELECT user.id,username,proname FROM user [INNER|CROSS] JOIN province ON user.proID=province.id;

图片.png

外连接查询

SELECT user.id,username,proname FROM user LEFT JOIN province ON user.proID=province.id;

图片.png

联合查询

把多个表中的记录统计输出
用法：SELECT 字段名 FROM 表名 UNION [ALL] SELECT 字段名 FROM 表名；
UNION ALL:输出所有记录
UNION:去除重复的记录

子查询

使用[NOT] IN()实现子查询
使用各种比较运算符实现子查询>、<、>=、<=、<>、!=、<=>
使用[NOT] EXISTS()实现子查询
例：SELECT id,username FROM employee WHERE EXISTS (SELECT id FROM department);
使用ANY|SOME或者ALL实现子查询
与比较运算符的结合使用

图片.png

可以使用INSERT INTO tb_name(字段名称) SELECT 字段 FROM tb_name将查询出来的数据直接插入表中，也可以在建表语句后加上SELECT 语句将查询结果写入表中。

正则表达式查询

通过REGEXP '匹配规则' 来使用

常用规则
^：匹配字符串开始的部分(^a)
$：匹配字符串结束的部分(a$)
.：代表字符串中的任意一个字符，包括回车和换行
[字符集合]：匹配字符集合中的任意一个字符([abc]、[a-z])
[^字符集合]：匹配除字符集合外中的任意一个字符
a|b|c：匹配a、b、c中的任意一个字符串
*：匹配0个1个或多个其前面的字符(a*)
+：匹配1个或多个其前面的字符(a+)
String{N}：字符串出现N次
字符串{M,N}：字符串最少出现M次，最多N次

图片.png

---

外键(FOREIGN KEY)

主表与子表的存储引擎必须为INNODB，外键列和参照列必须具有相同的数据类型，并且必须创建索引
外键创建
ALTER TABLE tb_name ADD [CONSTRAINT 外键名] FOREIGN KEY(外键列) REFERENCES department(参照列)
外键删除
ALTER TABLE tb_name DROP FOREIGN KEY 外键名

CREATE TABLE IF NOT EXISTS department(
id smallint unsigned primary key auto_increment,
depName varchar(30) unique
);

INSERT INTO department(depName) VALUES('学风部'),
('社践部'),
('外联部'),
('体育部'),
('网宣部');

CREATE TABLE IF NOT EXISTS employee(
id smallint unsigned auto_increment primary key,
username varchar(30) not null unique,
depId smallint unsigned,
[CONSTRAINT 外键名]FOREIGN KEY(depId) REFERENCES department(id)
);

INSERT INTO employee(username,depId) VALUES('小明',1),
('小红',2),
('小王',3),
('张三',4),
('李四',5),
('王五',1);

图片.png
图片.png
图片.png

外键约束的参照操作

NOT ACTION、RESTRICT：默认形式，拒绝对父表的删除和更新操作；
CASCADE：从父表更新或删除时自动删除或更新子表中匹配的行；
FOREIGN KEY(depId) REFERENCES department(id) ON DELETE CASCADE ON UPDATE CASCADE

图片.png
图片.png

SET NULL：从父表更新或删除时将子表的外键列设为NULL(外键列没有指定为NOT NULL)；
FOREIGN KEY(depId) REFERENCES department(id) ON DELETE SET NULL ON UPDATE SET NULL

图片.png

---

MySQL函数库

数学函数库

图片.png

字符串函数库

图片.png
图片.png

日期时间函数

图片.png
图片.png
图片.png

条件判断函数和系统函数

图片.png
图片.png

其他常用函数

图片.png
图片.png

---

索引

建表时创建索引：
[UNIQUE|FULLTEXT|SPATIAL] KEY|INDEX 索引名(字段名)
在已存在的表上创建索引
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名称 ON tb_name {字段名称[(长度)] }
ALTER TABLE tb_name ADD [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名称(字段名称[(长度)]);
删除索引
DROP INDEX 索引名称 ON tb_name