MySQL数据库操作
目录
一. MySQL 数据库介绍
二. MySQL库操作
1. 系统数据库
2. 数据库命名规则
3. 选择数据库
4. 查看数据库
5. 删除数据库
三. MySQL表操作
1. 表介绍
2. 查看表
3. 创建表
四. MySQL数据操作
1. 插入数据 INSERT
2. 删除数据 INSERT
3. 更新数据 INSERT
4. 单表查询
4.1 准备数据
4.2 where 条件
4.3 group by 分组
4.4 聚合函数:count() avg() max() min() sum()
4.5 having 过滤
4.6 order by排序
4.7 limit 限制结果条目
五. 正则匹配
1. 子查询
2. 多表连接查询
六. MySQL数据库用户授权
1. 创建用户
2. 授权操作
3. 查看权限
4. 撤销权限
一. MySQL 数据库介绍
数据库目前标准的指令集是 SQL。SQL是 Structured Query Language 的缩写，即结构化查询语言。它是1974年由 Boyce 和 Chamberlin 提出来的，1975~1979 年 IBM 公司研制的关系数据库管理系统原型 System R 实现了这种语言。经过多年的发展，SQL语言得到了广泛的应用。
SQL 语言主要由以下几部分组成:
- DDL(Data Definition Language，数据定义语言):用来建立数据库、数据库对象和定义字段，如CREATE、ALTER、DROP。
- DML(Data Manipulation Language，数据操纵语言):用来插入、删除和修改数据库中的数据，如 INSERT、UPDATE、DELETE。
- DQL(Data Query Language，数据查询语言):用来查询数据库中的数据，如 SELECT。
- 数据控制语言):用来控制数据库组件的存DCL(Data Control Language,取许可、存取权限等，如 COMMIT、ROLLBACK、GRANT、REVOKE。
二. MySQL库操作
1. 系统数据库
经初始化后的 MySQL 服务器 ，默认建立了四个数据库:sys、mysql、information_schema performance_schema
例：创建一个名为db1的库
2. 数据库命名规则
- 可以由字母、数字、下划线、@、#、$
- 区分大小写
- 唯一性
- 不能使用关键字 如create、select
- 不能单独使用数字
- 最长128位
3. 选择数据库
语法
use 数据库名；例：切换到db1
4. 查看数据库
mysql> show databases; 查看当前数据库中有哪些表
mysql> show create database db1; 显示创建名为db1的数据库时所使用的sql语句
mysql> show select database (); 返回当前选中的数据库的名称5. 删除数据库
语法：
drop database 数据库名；例
三. MySQL表操作
1. 表介绍
库相当于文件夹，而表相当于文件，表中的一条记录就相当于文件的一行内容，不同的是，表中的一条记录有对应的标题，称为表的字段
| ID | name | age | sex |
| 1 | 张三 | 18 | male |
| 2 | 李四 | 76 | female |
| 3 | 王五 | 23 | male |
id，name，age，sex称为字段，其余的一行内容称为一条数据记录
2. 查看表
使用show table查看当前所在的数据库中包含的表，在操作之前，需要先使用use语句切换到所使用的数据库，就像要查看一个文件夹里面有多少文件
先进入要查看的表所在的库里
mysql> use mysql;
mysql> show tables;3. 创建表
语法
mysql> create table 表名(
字段名1 类型[(宽度) 约束条件]，
字段名2 类型[(宽度) 约束条件]；
字段名3 类型[(宽度) 约束条件]);注意：
在同一个表中，字段名是不能相同的
宽度和约束条件可选
字段名和类型是必须的，字段的类型宽度和约束条件是可选项
表中最后一个字段不要加逗号类型介绍
表内存放的数据有不同的类型，类似于使用Excel存储数据时也需要设置的数据类型，每种数据类型都有自己的宽度，但宽度时可选的，不设置时，会使用字段的默认宽度
MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符) 类型
数值类型
| 类型 | 大小 | 范围（有符号） | 范围（无符号） | 用途 |
| INT | 4字节 | (-2 147 483 648,2 147 483 647) | (0,4 294 967 295) | 大整数值 |
| DOUBLE | 8字节 | (-1.797E+308,-2.22E-308) | (0,2.22E-308,1.797E+308) | 双精度浮点数值 |
| DOUBLE(M,D) | 8个字节，M表示长度，D表示小数 | 同上，受M和D的约束DOUBLE(5,2) -999.99-999.99 | 同上，受M和D的约束 | 双精度浮点数值 |
| DECIMAL(M,D) | DECIMAL(M,D) | 依赖与M的D的值，M最大值为65 | 依赖与M的D的值，M最大值为65 | 小数 |
日期类型
| 类型 | 大小 | 范围 | 格式 | 用途 |
| DATE | 3 | 1000-01-01/99999-12-31 | YYYY-MM-DD | 日期值 |
| TIME | 3 | '-838:59:59'/'838:59:59' | HH:MM:SS | 时间值或持续时间 |
| YEAR | 1 | 1901/2155 | YYYY | 年份值 |
| DATETIME | 8 | 1000-01-01 00:00:00/9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值 |
| TIMESTAMP | 4 | 1970-01-01 00:00:00/2038结束时间是第2147483647秒北京时间2038-1-19 11:14:07,格林尼治时间2038年1月19日凌晨03:14:07 | YYYYMMDD HHMMSS | 混合日期和时间值，时间戳 |
字符串类型
| 类型 | 大小 | 用途 |
| CHAR | 0-255字符 | 定长字符串char(10) 10 个字符 |
| VARCHAR | 0-65535字节 | 变长字符串varche(10) 10个字符 |
| BLOB | 0-65535字节 | 二进制形式的长文本数据 |
| TEXT | 0-65535字节 | 长文本数据 |
约束条件
约束条件与数据类型的宽度一样，都是可选参数，类似于使用excel存储数据时，可以利用excel的公式限制员工ID列：禁止重复值，且不能为空
约束条件作用：用于保证数据的完整性和一致性，主要分为：
| 约束条件 | 说明 |
| PRIMARY KEY(PK) | 标识该字段为该表的主键，可以唯一的标识记录 |
| FOREIGN KEY(FK) | 标识该字段为该表的外键 |
| NOT NULL | 标识该字段不能为空 |
| UNIQUE KEY(UK) | 标识该字段的值是唯一的 |
| AUTO INCREMENT | 标识该字段的值自动增长(整数类型而且为主键) |
| DEFAULT | 为该字段设置默认值 |
| UNSIGNED | 无符号 |
| ZEROFILL | 使用 0填充 |
| ENMU() | 限制字段可以存储的值集合 |
创建表示例
mysql> create database db1; #创建 db1库mysql>use db1; #进入db1库mysql> create table tl(
-> id int,
-> name varchar(50)，
-> age int(3),
-> sex enum('male',’female')
-> );mysql> show tables;查看表结构
mysql> describe tl; #describe 可简写成 desc查看详细表结构或者创建表所使用的语句可以执行show create table t1\G;#查看表详细结构，可加\G；\G表示以长格式展示结果 
修改表
修改表名
alter table 表名 rename 新表名；例子：
alter table t1 rename t2;增加字段
ALTER TABLE 表名 ADD 字段名 数据类型[完整性约束条件…],ADD 字段名数据类型[完整性约束条件…];ALTER TABLE 表名 ADD 字段名，数据类型[完整性约束条件…] FIRST;ALTER TABLE 表名 ADD 字段名，数据类型[完整性约束条件…] AFTER 字段名;例子:
mysql> ALTER TABLE t1 ADD class int(3)not null;
Query OK, 0 rows affected, l warning(0.04 sec)
Records:0 Duplicates:0Warnings:1删除字段
ALTER TABLE 表名 DROP 字段名;例子:
mysql> alter table t2 drop class;
Query OK,0 rows affected(0.02 sec)
Records:0 Duplicates:0Warnings:0修改字段
ALTER TABLE 表名 MODIFY 字段名 数据类型[完整性约束条件…];ALTER TABLE 表名 CHANGE 旧字段名 新字段名 旧数据类型[完整性约束条件…];ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新数据类型[完整性约束条件…];例子:
mysql> alter table t2 modify name char(50):
Query Ok, 0 rows affected(0.02 sec)
Records:0 Duplicates:0Warnings:0mysql> alter table t2 change name user name varchar(50);
Query Ok, 0 rows affected(0.02 sec)
Records:0 Duplicates:0Warnings:0复制表
只复制表结构，不复制表中数据
mysql> create table t3 like t2;
Query OK, 0 rows affected(0.0l sec)复制表结构+记录(key不会复制:主键、外键和索引)
mysql> create table t4 select * from t2;
Query OK, 0 rows affected(0.03 sec)
Records:0 Duplicates:0Warnings:0删除表
删除数据库中的表，需要指定“数据库名.表名”作为参数;若只指定表名参数，则需先通过执行“USE”语句切换到目标数据库mysql> drop table t2;
Query OK, 0 rows affected(0.0l sec)mysql> drop table dbl.t3;
Query OK, 0 rows affected (0.0l sec)四. MySQL数据操作
在MySQL管理软件中，可以通过SQL语句种的DML语言来实现数据的操作，包括使用INSERT实现数据的插入、使用UPDATE实现数据的更新、使用DELETE实现数据的删除、使用SELECT查询数据。
mysql> use db1; #进入db1库mysql> create table t1(-> id int,-> name varchar(50),-> age int(3),-> sex enum('male','female')-> );mysql> show tables;1. 插入数据 INSERT
INSERT INTO 语句：用于向表中插入新的数据记录，语句格式如下：
插入数据后可使用
mysql>select * from t1; #验证顺序插入：
INSERT INTO 表名 VALUES(值 1,值 2,值 3…值 n);
mySql> INSERT INTO tI VALUES (1,"zhangsan",18, "male");
Query OK,l row affected(0.00 sec)指定字段插入数据：
INSERT INTO 表名(字段 1,字段 2,字段 3…字段 n)VALUES (值 1,值 2,值 3…值n);
mysql> INSERT INTO tl (id, name, age, sex) VALUES(2,"lisi", 18,"female");
Query OK, l row affected(0.00 sec)插入多条记录：
INSERT INTO 表名 VALUES (值 1,值 2,值 3…值 n),(値 1,值 2,值 3…值 n)，(值 1值 2,值 3…值 n);
mysqI>INSERT INTO t1 VALUES (3, "wangwu", 28, "male"), (4,"zhaoliu",30, "female”);
Query OK, 2 rows affected(0.0l sec)
Records:2 Duplicates:0Warnings:02. 删除数据 INSERT
DELETE 语句:用于删除表中指定的数据记录，语句格式如下：
删除数据后可使用
mysql>select * from tl; 验证DELETE FROM 表名WHERE 条件表达式
mysql> delete from t1 where id=1;
Query OK, l row affected(0.0l sec)3. 更新数据 INSERT
UPDATE 语句:用于修改、更新表中的数据记录。语句格式如下：
更新数据后可使用
mysql>select * from t1; 验证UPDATE表名 SET 字段名 1=字段值 1[,字段名 2=字段值 2]WHERE 条件表达式
mysql> update tl set age=100 where name="wangwu";
Query OK, l row affected(0.00 sec)
Rows matched:1Changed:1Warnings:0查询数据INSERT
4. 单表查询
SELECT 字段 1,字段 2...FROM 表名WHERE 条件GROUP BY 字段HAVING 筛选ORDER BY 字段LIMIT 限制条数关键字执行的优先级
from
where
group by
having
select
order by
limit说明：
| 1.找到表：from 2.拿着 where 指定的约束条件，去文件/表中取出一条条记录 4.将分组的结果进行 having 过滤 5.执行select 6.将结果按条件排序：order by 7.限制结果的显示条数：limit |
4.1 准备数据
create database test;
use test;
create table renyuan(
id int not null unique auto_increment, #员工id
name varchar(20) not null, #姓名
sex enum('male','female') not null default 'male', #性别，大部分是男的
age int(3) unsigned not null default 28, #年龄
hire_date date not null, #入职时间
post varchar(50), #岗位
post_comment varchar(100), #职位描述
salary double(15,2), #薪资
office int, #办公室，一个部门一个屋子
depart_id int #部门编号
);
insert into renyuan(name,sex,age,hire_date,post,salary,office,depart_id) values
('zhangsan','male',18,'20170301','teacher',7300.33,401,1), #教学部
('lisi','male',78,'20150302','teacher',1000000.31,401,1),
('wangwu','male',81,'20130305','teacher',8300,401,1),
('zhaoliu','male',73,'20140701','teacher',3500,401,1),
('suqi','male',28,'20121101','teacher',2100,401,1),
('zhuba','female',18,'20110211','teacher',9000,401,1),
('洪金宝','male',18,'19000301','teacher',30000,401,1),
('成龙','male',48,'20101111','teacher',10000,401,1),('歪歪','female',48,'20150311','sale',3000.13,402,2),#销售部门
('丫丫','female',38,'20101101','sale',2000.35,402,2),
('丁丁','female',18,'20110312','sale',1000.37,402,2),
('星星','female',18,'20160513','sale',3000.29,402,2),
('格格','female',28,'20170127','sale',4000.33,402,2),('张野','male',28,'20160311','operation',10000.13,403,3), #运营部门
('程咬金','male',18,'19970312','operation',20000,403,3),
('程咬银','female',18,'20130311','operation',19000,403,3),
('程咬铜','male',18,'20150411','operation',18000,403,3),
('程咬铁','female',18,'20140512','operation',17000,403,3)
;
简单查询
语法：把表种需要的列的数据查询出来
SELECT 字段 1,字段 2...FROM 表名例子:
SELECT id, name, office FROM renyuan;表示所有字段时，可以使用通配符“*”，显示所有的数据记录
SELECT * FROM renyuan;4.2 where 条件
WHERE 是 SQL, 中用于筛选数据的核心子句，其作用是通过指定条件从表中过滤出符合条件的记录。它可应用于 SELECT、UPDATE、DELETE 等语句中，控制操作的范围
where 字句中可以使用：
- 比较运算符：> < >= <= <> !=
- between 80 and 100 值在10到20 之间
- in(80,90,100)值是 10 或 20 或 30
- like 'egon%'
- pattern 可以是%或_
- %表示任意多字符
- _表示一个字符
- 逻辑运算符:在多个条件直接可以使用逻辑运算符 and or not
4.3 group by 分组
分组指的是：将所有记录按照某个相同字段进行归类，比如针对员工信息表的职位分组，或者按照性别进行分组等
select sex,count(*) from renyuan group by sex; #按性别进行分组但是只查看sex字段又没有任何意义，因为需要的是组内的信息，所以需要结合聚合函数查看
4.4 聚合函数:count() avg() max() min() sum()
count()：计数
计算男女生的人数
select sex,count(*) from renyuan group by sex;每个岗位的平均薪资
select post,avg(salary) from renyuan group by post; 每个岗位的最高薪资
select post,max(salary) from renyuan group by post; 每个岗位的最低薪资
select post,min(salary) from renyuan group by post;每个岗位的薪资总和
select post,sum(salary) from renyuan group by post; 4.5 having 过滤
HAVING与WHERE不一样的地方
执行优先级从高到低:where>group by>having
Where 发生在分组 group by 之前，因而 where 中可以有任意字段，但是绝对不能使用聚合函数。
Having 发生在分组 group by 之后，因而 Having 中可以使用分组的字段，无法直接取到其他字段,但可以使用聚合函数
有哪些岗位是平均工资大于 10000 的
select post,avg(salary) from renyuan group by post having avg(salary)>10000;4.6 order by排序
排序的关键字可以使用 ASC或者 DESC。ASC 是按照升序进行排序的，是默认的排序方式，即 ASC 可以省略。SELECT 语句中如果没有指定具体的排序方式则默认按 ASC 方式进行排序。DESC 是按降序方式进行排列
mysql>select * from renyuan order by id asc; #默认是正序 asc 可以省略
mysql>select * from renyuan order by id desc; #倒叙4.7 limit 限制结果条目
在使用 MySQL SELECT 语句进行查询时，结果集返回的是所有匹配的记录。有时候仅需要返回第一行或者前几行，这时候就需要用到LIMIT 子句
倒叙排列后仅显示第一行
mysql>select * from renyuan order by id desc limit 1:五. 正则匹配
MySQL 正则表达式通常是在检索数据库记录的时候，根据指定的匹配模式匹配记录中符合要求的特殊字符串。MySQL的正则表达式使用 REGEXP 这个关键字来指定正则表达式的匹配模式
Name 是1开头的姓名
mysql>select * from renyuan where name regexp '^1';Name 是u结尾的姓名
select * from renyuan where name regexp "u$";Name 是 wan 和 wu之间至少1个g的姓名
select * from renyuan where name regexp "wang+wu";1. 子查询
子查询也被称作内查询或者嵌套查询，是指在一个查询语句里面还嵌套着另一个查询语句。子查询语句是先于主查询语句被执行的，其结果作为外层的条件返回给主查询进行下一步的查询过滤。子查询不仅可以在 SELECT 语句中使用，在 INERT、UPDATE、DELETE 中也同样适用。在嵌套的时候，子查询内部还可以再次嵌套新的子查询，也就是说可以多层嵌套。
语法结构：
<表达式 >[NOT] IN〈子查询>
查询平均年龄在25岁以上的部门名
mysql> select id ,name from bumeb where id in (select dep_id from renyuan group by dep_id having avg(avg) > 25);2. 多表连接查询
MySQL 的连接查询，通常都是将来自两个或多个表的行结合起来，基于这些表之间的共同字段，进行数据的拼接。首先，要确定一个主表作为结果集，然后将其他表的行有选择性的连接到选定的主表结果集上。使用较多的连接查询包括:内连接、左连接和右连接
2.1 内连接(只连接匹配的行)
MySQL中的内连接就是两张或多张表中同时符合某种条件的数据记录的组合。通常在 FROM 子句中使用关键字 INNERJOIN 来连接多张表，并使用 ON 子句设置连接条件。
语法：
SELECT column name(s)FROM tablel
INNER JOIN table2 ON tablel.column name = table2. column name;
2.2 左连接(优先显示左表全部记录)
MySQL 除了内连接，还可以使用外连接。区别于MySQL 外连接是将表分为基础表和参考表，再依据基础表返回满足条件或不满足条件的记录。外连接按照连接时表的顺序来分，有左连接和右连接之分。
左连接也可以被称为左外连接，在FROM子句中使用LEFTJOIN 或者 LEFIOUTER JOIN 关键字来表示。左连接以左侧表为基础表，接收左表的所有行，并用这些行与右侧参考表中的记录进行匹配,也就是说匹配左表中的所有行以及右表中符合条件的行
2.3 右连接(优先显示右表全部记录)
右连接也被称为右外连接，在FROM子句中使用RIGHTJOIN 或者RIGHTOUTER JOIN 关键字来表示。右连接跟左连接正好相反，它是以右表为基础表，用于接收右表中的所有行，并用这些记录与左表中的行进行匹配。也就是说匹配右表中的每一行及左表中符合条件的记录 
六. MySQL数据库用户授权
MySQL 数据库的 root 用户账号拥有对所有数据库、表的全部权限，频繁使用 root 账号会给数据库服务器带来一定的安全风险。实际工作中，通常会建立一些低权限的用户，只负责一部分数据库、表的管理和维护操作，甚至可以对查询、修改、删除记录等各种操作做进一步的细化限制，从而将数据库的风险降至最低。
1. 创建用户
语法：
CREATE USER '用户名'@'来源地址' IDENTIFIED BY '密码'- 用户名@来源地址：用于指定用户名称和允许访问的客户机地址,即谁能连接能从哪里连接。来源地址可以是域名、IP 地址，还可以使用“%”通配符，表示某个区域或网段内的所有地址，如“%.bdqn.com”“192.168.1.%”等。
- IDENTIFIED BY：用于设置用户连接数据库时所使用的密码字符串。在新建用户时，若省略“IDENTIFIED BY”部分，则用户的密码将为空
例：
2. 授权操作
语法：
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'来源地址'例：
3. 查看权限
SHOW GRANTS 语句:专门用来查看数据库用户的授权信息，通过FOR子句可指定查看的用户对象(必须与授权时使用的对象名称一致)
语法：
SHOW GRANTS FOR '用户名'@'来源地址'
4. 撤销权限
REVOKE 语句:用于撤销指定用户的数据库权限，撤销权限后的用户仍然可以连接到 MySQL 服务器，但将被禁止执行对应的数据库操作
语法：
REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'来源地址'