备案控制台
探索云世界
开发者社区 数据库 文章 正文

MySQL数据库总结

2020-02-22 999
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云数据库 RDS MySQL Serverless，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
简介：


一、数据库简介


数据库(Database，DB)是按照数据结构来组织，存储和管理数据的仓库。
典型特征：数据的结构化、数据间的共享、减少数据的冗余度，数据的独立性。
关系型数据库：使用关系模型把数据组织到数据表(table)中。现实世界可以用数据来描述。
主流的关系型数据库产品：Oracle(Oracle)、DB2(IBM)、SQL Server(MS)、MySQL(Oracle)。
数据表：数据表是关系数据库的基本存储结构，二维数据表有行(Row),和列(Column)组成，也叫作记录(行)和字段(列)。
二、MySQL数据类型(5.5版本)


(1)数值类型


MySQL中除了字符串类型需要设置长度,其他类型都有默认长度.


image
image
image
image
三、Sql语句


(1)Sql语句简介


SQL(Structured Query Language):结构化查询语言
SQL是在关系数据库上执行数据操作、检索及维护所使用的标准语言，可以用来查询数据，操纵数据，定义数据，控制数据。
SQL可以分为：


数据定义语言(DDL)：Data Definition Language
数据操纵语言(DML)：Data Manipulation Language
事务控制语言(TCL)：Transaction Control Language
数据查询语言(DQL)：Data Query Language
数据控制语言(DCL)：Data Control Language
(2)数据定义语言DDLcreate,alter,drop


--数据定义语言DDL(create,alter,drop)


-- 一、数据库相关的DDL


-- 1.创建数据库
CREATE DATABASE mybase;
-- 2.创建数据库并指定字符集
CREATE DATABASE mybase1 CHARACTER SET UTF8;
-- 3.查看所有数据库
SHOW DATABASES;
-- 4.查看当前使用的数据库
SELECT DATABASE();
-- 5.修改数据库
ALTER DATABASE mybase CHARACTER SET UTF8;
-- 6.删除数据库
DROP DATABASE mybase1;
-- 切换数据库
USE mybase;


--二、表相关DDL


-- 1.创建表
create table exam(


id INT(11) PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20),
English INT,
Chinese INT,
Math int

);
-- 2.查看数据库中所有表
show TABLES;
-- 3.查看表结构
desc exam;


-- 4.表的删除
drop table exam;


-- 5.表的修改(基于exam表)


-- 5.1添加列
ALTER TABLE exam ADD History INT NOT NULL;
-- 5.2修改列的类型、长度、约束
ALTER TABLE exam MODIFY History DOUBLE(7,2);
-- 5.3修改表的列名
ALTER TABLE exam CHANGE History Physics INT NOT NULL;
-- 5.4修改表名
RENAME TABLE exam TO score;
-- 5.5修改表的字符集
ALTER TABLE score CHARACTER SET GBK;
-- 5.6删除列
scoreALTER TABLE score DROP Physics;
-- 5.7


ALTER TABLE


点我领取阿里云2000元代金券，（阿里云优惠券的作用：购买阿里云产品，最后支付结算的时候，阿里云优惠券可抵扣一部分费用。

-- 三、练习:创建emp表


CREATE TABLE emp(


empno INT PRIMARY KEY AUTO_INCREMENT,
ename VARCHAR(20),
job VARCHAR(20),
mgr int,
hiredate DATE,
sal DOUBLE(7,2),
commit double(5,2),
deptno INT NOT NULL    -- 非空约束

(3)数据操纵语言DMLupdate,insert,delete


-- 数据操纵语言DML(update,insert,delete)


-- 1.插入部分列
INSERT INTO score(id,NAME,English,Chinese,Math) VALUE(1,'Hudie',90,90,90);
INSERT INTO score(id,NAME,English,Chinese) VALUE(NULL,'diedie',91,91);
-- 2.插入所有列
INSERT INTO score VALUES(3,'Shu',80,80,80);
-- 3.修改记录
UPDATE score set Chinese=99; --全表修改
UPDATE score SET Math=100 WHERE id='1';
-- 4.删除记录
DELETE FROM score WHERE id='2';
DELETE FROM score;


-- delete与truncate的区别 √
--TRUNCATE TABLE 删除表的记录:将整个表删除掉,重新创建一个新的表,属于DDL.
--DELETE FROM 删除表的记录:一条一条进行删除,DELETE.
INSERT INTO score VALUES(3,'Shu',80,80,80);
DELETE FROM score;
INSERT INTO score VALUES(NULL,'Libai',10,10,10); --不会清空AUTO_INCREMENT值
TRUNCATE TABLE score;
INSERT INTO score VALUES(NULL,'Libai',10,10,10); --清空AUTO_INCREMENT的值


-- 事务管理:只能作用在DML语句上,如果在一个事务中使用delete删除所有记录,可以找回.
-- 使用delete删除后可以用COMMIT和ROLLBACK找回数据,使用truncate后就找不回来了.


-- delete、truncate、drop的区别 √
delete、truncate、只是删除表的记录,而drop会直接删除表.


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
(4)数据控制语言DCLgrant,revoke


-- 数据控制语言DCL(grant,revoke)
-- 主要为用户授予和撤销权限


-- 1.创建用户:CREATE USER 用户名@ip IDENTIFIED BY 密码;
create user Fox@localhost identified by '123456';


-- 2.给用户授权:grank 权限1,权限2,...,权限n ON 数据库名. TO 用户名@IP;
grant select,drop on mysql. to Fox@localhost;


-- 3.撤销权限:REVOKE 权限1,权限2,...,权限n ON 数据库名. FROM 用户名@IP;
revoke select on mysql. from Fox@localhost;


-- 4.查看用户的权限:SHOW GRANTS FOR 用户名@IPl
show grants for Fox@localhost;
-- 5.删除用户: DROP USER 用户名@IP;
drop user Fox@localhost;
-- 6.登录:mysql -u 用户名-p 密码;
mysql -u root -p
-- 7.退出登录: exit;
exit;


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
(5)数据查询语言DQLselect


-- 数据查询语言DQL(select)
create table exam(


id INT(11) PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20),
English INT,
Chinese INT,
Math int

);
INSERT INTO exam VALUES(NULL,'小花',99,99,99);
INSERT INTO exam VALUES(NULL,'小蓝',89,89,89);


点我领取阿里云2000元代金券，（阿里云优惠券的作用：购买阿里云产品，最后支付结算的时候，阿里云优惠券可抵扣一部分费用。

-- 1.全表查询: SELECT FROM 表;
SELECT FROM exam;
-- 2.查询部分字段: SELECT 字段,字段,字段... FROM 表;
SELECT NAME,English,Math FROM exam;
-- 3.过滤重复字段行: SELECT [DISTINCT] |列名 FROM 表;
SELECT DISTINCT Math FROM exam;
SELECT DISTINCT name,Math FROM exam;
-- 4.查询字段起别名: SELECT 字段 AS 新字段名,字段 新字段名 FROM 表;
SELECT NAME,English AS English_score FROM exam;
-- 5.查询指定字段
SELECT NAME,English,Chinese FROM exam WHERE NAME='李白';
-- 6.使用表达式+、-、、/
SELECT id,NAME,English-20 AS _English FROM exam;
SELECT NAME,English+Math+Chinese FROM exam;
-- 7.模糊查询
SELECT FROM exam WHERE NAME LIKE '小_';
SELECT FROM exam WHERE NAME LIKE '%%';
-- 8.使用and,or
SELECT FROM exam WHERE English > 90 AND Chinese >90;
SELECT FROM exam WHERE English < 90 or Math >99;
-- 9.使用in,not in
SELECT FROM exam WHERE id=2 OR id=3 OR id=4;
SELECT FROM exam where id IN(2,3,4);
SELECT FROM exam where id not IN(2,3,4);
-- 10.使用between...and []
SELECT FROM exam WHERE English BETWEEN 90 AND 100;
-- 11. is null,is not null
INSERT INTO exam(id,NAME) VALUES(NULL,NULL);
SELECT FROM exam WHERE NAME IS NULL;
SELECT FROM exam WHERE NAME IS not NULL;
-- 11.排序查询
SELECT FROM exam ORDER BY Chinese ASC;
SELECT FROM exam ORDER BY Chinese DESC;
SELECT FROM exam ORDER BY English DESC,Chinese DESC;-- 如果英语成绩相同,按照汉语成绩降序排列
SELECT FROM exam WHERE NAME LIKE '小%' ORDER BY English ASC;
-- 12.聚合函数
SELECT SUM(English+Math+Chinese) FROM exam;
SELECT COUNT(id) FROM exam WHERE NAME IS NOT NULL;
SELECT MAX(English) FROM exam;
SELECT MIN(English) FROM exam;
SELECT AVG(English) FROM exam ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
(6)分组查询group by与分页查询limit


-- 一、分组查询


CREATE TABLE emp(


empno INT PRIMARY KEY AUTO_INCREMENT,
ename VARCHAR(20),
job VARCHAR(20),
mgr int,
hiredate DATE,
sal DOUBLE(7,2),
commit double(5,2),
deptno INT NOT NULL

);


INSERT INTO emp VALUES
(1002,'白展堂','clerk',1001,'1983-05-09',7000.00,200.00,10),
(1003,'李大嘴','clerk',1002,'1980-07-08',8000.00,100.00,10),
(1004,'吕秀才','clerk',1002,'1985-11-12',4000.00,null,10),
(1005,'郭芙蓉','clerk',1002,'1985-03-04',4000.00,null,10),
(2001,'胡一菲','leader',null,'1994-03-04',15000.00,NULL,20),
(2002,'陈美嘉','manger',2001,'1993-05-24',10000.00,300.00,20),
(2003,'吕子乔','clerk',2002,'1995-05-19',7300.00,100.00,20),
(2004,'张伟','clerk',2002,'1994-10-12',8000.00,500.00,20),
(2005,'曾小贤','clerk',2002,'1993-05-10',9000.00,700.00,20),
(3001,'刘梅','leader',null,'1968-08-08',13000.00,NULL,30),
(3002,'夏冬梅','manger',3001,'1968-09-21',10000.00,600.00,30),
(3003,'夏雪','clerk',3002,'1989-09-21',8000.00,300.00,30),
(3004,'张一山','clerk',3002,'1991-06-16',88000.00,200.00,30);


-- 1.查询每个部门的平均工资
SELECT deptno,AVG(sal) FROM emp GROUP BY deptno;
-- 2.查询每个职位的最高工资和最低工资
SELECT job,MAX(sal),MIN(sal) FROM emp GROUP BY job;
-- 3.查询每个部门每种职位的最高工资
SELECT deptno,job,MAX(sal) FROM emp GROUP BY deptno,job;
-- 4.查询每个部门的最高薪水,只有最高薪水大于15000的记录才被输出显示
SELECT deptno,MAX(sal)AS max_sal FROM emp GROUP BY deptno HAVING max_sal>=15000;
-- 5.查询每个部门的平均工资
SELECT deptno,AVG(sal) FROM emp GROUP BY deptno HAVING AVG(sal)>9000;
-- 6.Havaing子句与where子句的区别
(1)where是用来过滤记录的,HAVING是用来过滤分组的
(2)过滤的时机不相同,先过滤Where后过滤Having.
(3)WHERE是在查询表时逐行过滤以选取满足条件的记录
(4)having是在数据查询后并且分完组后对分组进行过滤的
(5)HAVING必须跟在group BY
(6)查询语句执行顺序:5select 1from 2where 3group by 4having 6order by


点我领取阿里云2000元代金券，（阿里云优惠券的作用：购买阿里云产品，最后支付结算的时候，阿里云优惠券可抵扣一部分费用。

-- 二、分页查询
-- 1.从第几页开始多少页(下标从0开始)
SELECT FROM emp LIMIT 0,3;
-- 2.每页几条第几页==需要查看第几页-1)乘以第二个参数
SELECT FROM emp LIMIT 10,5;-- 每页五条第三页(3-1)5
SELECT FROM emp LIMIT 2,2; --每页2条第2页(2-1)2
-- 3.查看工资最高的前十个职员信息
SELECT FROM emp ORDER BY sal DESC LIMIT 0,10;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
四、完整性约束


单表约束


主键约束：primary key (默认就是唯一非空的)


外键约束: 用于在两个表之间建立关系，需要指定引用主表的哪一列。


如果表A的主键是表B中的字段,则该字段称为表B的外键,表A(主表),表B(从表).
外键是用来实现参照完整性的，主表更新时从表也更新,主表删除时如果从表有匹配的项,删除失败
唯一约束：unique


非空约束：not null


CREATE TABLE emp(


empno INT PRIMARY KEY AUTO_INCREMENT,--主键约束
ename VARCHAR(20),
job VARCHAR(20),
mgr int,
hiredate DATE,
sal DOUBLE(7,2),
commit double(5,2),
deptno INT NOT NULL    -- 非空约束

);
-- √ 添加 唯一约束和非空约束
ALTER TABLE exam MODIFY NAME VARCHAR(21) UNIQUE NOT NULL;


点我领取阿里云2000元代金券，（阿里云优惠券的作用：购买阿里云产品，最后支付结算的时候，阿里云优惠券可抵扣一部分费用。

-- 创建主表
CREATE TABLE dept(


deptno INT PRIMARY KEY,
dname VARCHAR(20),
loc VARCHAR(20)

);
INSERT INTO dept VALUES
(10,'餐饮部','上海'),
(20,'销售部','浙江'),
(30,'财务部','北京'),
(40,'技术部','深圳');


为从表emp加外键


ALTER TABLE emp ADD FOREIGN KEY (deptno) REFERENCES dept(deptno);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
五、多表查询


多张数据表或视图的查询叫做连接查询


-- 1.笛卡尔积:
SELECT
FROM emp,dept;
-- 2.等值链接(SELECT FROM A,B WHERE A.主键=B.外键;)
SELECT
FROM emp,dept WHERE dept.deptno = emp.deptno;
-- 3.内连接(SELECT FROM A INNER JOIN B ON A.主键=B.外键;)
SELECT
FROM emp INNER JOIN dept ON dept.deptno = emp.deptno;
-- 4.外连接:
-- 4.1左外连接:(SELECT FROM A LEFT OUTER JOIN B ON 条件;)
SELECT
FROM emp LEFT OUTER JOIN dept ON dept.deptno=emp.deptno;
-- 4.2右外连接:(SELECT FROM A right OUTER JOIN B ON 条件;)
SELECT
FROM emp RIGHT OUTER JOIN dept ON dept.deptno=emp.deptno;
-- 5.子查询:
-- 5.1单行单列,工作地点在上海的员工
SELECT
FROM emp WHERE deptno=(SELECT deptno FROM dept WHERE loc ='上海');
-- 5.2多行单列,工作地点不在上海的员工
SELECT *
FROM emp WHERE deptno in(SELECT deptno FROM dept WHERE loc <>'上海');
-- 6.自连接
SELECT e1.,e2. FROM emp e1 inner join emp e2 ON e1.mgr =e2.empno
WHERE e1.ename ='吕子乔'


点我领取阿里云2000元代金券，（阿里云优惠券的作用：购买阿里云产品，最后支付结算的时候，阿里云优惠券可抵扣一部分费用。

多表查询练习↓
-- 1.查看每个员工的名字以及其所在部门的名字
SELECT emp.ename,dept.dname,dept.loc
FROM emp,dept
WHERE emp.deptno = dept.deptno;
-- 2.查看工作地点在北京的员工有哪些
SELECT
FROM emp INNER JOIN dept ON emp.deptno = dept.deptno
WHERE dept.loc ='北京';
-- 3.查看每个城市员工的平均工资
SELECT dept.loc,ifnull(avg(sal),0)
FROM emp right join dept
on emp.deptno =dept.deptno
GROUP BY dept.loc;
-- 4.查看工作地点在上海的员工有哪些
SELECT emp.,dept.dname,dept.loc
FROM emp LEFT OUTER JOIN dept ON emp.deptno=dept.deptno
WHERE dept.loc='上海';
-- 5.查找和曾小贤同职位的员工
SELECT
FROM emp
WHERE job=(SELECT job FROM emp WHERE ename='曾小贤');
-- 6.查找薪水比整个机构平均水平高的员工
SELECT
FROM emp
WHERE sal>(SELECT AVG(sal) FROM emp);
-- 7.查询出部门中有clerk但职位不是clerk的员工的信息
SELECT *
FROM emp
WHERE deptno IN(SELECT DISTINCT deptno FROM emp WHERE job='clerk') AND job!='clerk';
-- 8.查看每个城市员工的平均工资'
SELECT dept.loc,AVG(sal)
FROM emp INNER JOIN dept ON emp.deptno=dept.deptno GROUP BY dept.loc;
-- 9.查询出最低薪水高于部门20的最低薪水的部门信息
SELECT deptno,MIN(sal) AS MIN_sal
FROM emp
GROUP BY deptno
HAVING min_sal>(SELECT MIN(sal) FROM emp WHERE deptno=20);
-- 10.列出所有员工的姓名及其直接上级的姓名
SELECT e1.ename,e2.ename
FROM emp e1
LEFT JOIN emp e2 ON e1.mgr = e2.empno;
更多信息参阅云数据库MySQL文档


点我领取阿里云2000元代金券，（阿里云优惠券的作用：购买阿里云产品，最后支付结算的时候，阿里云优惠券可抵扣一部分费用。
文章标签：
云数据库 RDS MySQL 版
SQL
关系型数据库
数据安全/隐私保护
MySQL
Oracle
数据库
存储
相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境，其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
开源数据EMR
目录
相关文章
瑶大头*^_^*
|
6月前
|
关系型数据库 MySQL 数据库
MySQL数据库（三）
MySQL数据库
瑶大头*^_^*
22 0
日星月云
|
8月前
|
SQL 关系型数据库 MySQL
MYSQL数据库7
MYSQL数据库7
日星月云
33 0
Y君的进化史
|
8月前
|
关系型数据库 MySQL 数据库
MySQL数据库（二）下
MySQL数据库（二）下
Y君的进化史
33 0
瑶大头*^_^*
|
6月前
|
存储 关系型数据库 MySQL
MySQL数据库（二）
MySQL数据库
瑶大头*^_^*
25 0
瑶大头*^_^*
|
6月前
|
存储 关系型数据库 MySQL
MySQL数据库（一）
MySQL数据库
瑶大头*^_^*
24 0
爱吃电脑的跳跳
|
9月前
|
SQL 存储 缓存
MySQL数据库使用的10个小技巧
当涉及到使用MySQL数据库，掌握一些小技巧能够提高工作效率，优化查询性能，以及避免一些常见的错误。以下是10个关于MySQL使用的小技巧
爱吃电脑的跳跳
60 2
老师我作业忘带了
|
存储 SQL 关系型数据库
MySQL数据库（2）
MySQL数据库（2）
老师我作业忘带了
52 0
MySQL数据库（2）
老师我作业忘带了
|
SQL 关系型数据库 MySQL
MySQL数据库（3）
MySQL数据库（3）
老师我作业忘带了
69 0
老师我作业忘带了
|
SQL 存储 Oracle
MySQL数据库（1）
MySQL数据库（1）
老师我作业忘带了
68 0
MySQL数据库（1）
金士曼
|
SQL 关系型数据库 MySQL
MySQL数据库（初步了解）
MySQL数据库（初步了解）
金士曼
94 0

热门文章

最新文章

  • 1
    疑犯追踪第一季/全集Person Of Interest迅雷下载
  • 2
    【直播系列之一】1篇文章看懂峰值带宽、流量、转码、连麦、截图五大直播计费方式
  • 3
    理解事务的4种隔离级别
  • 4
    阿里云网站域名备案流程全过程讲解(图文)
  • 5
    在服务器的raid1中安装windows server系统（踩坑记录）
  • 6
    如何用Vue实现简易的富文本编辑器，并支持Markdown语法
  • 7
    怎么才能快速提高小程序留存率！
  • 8
    程序员请放下浮躁的心
  • 9
    如何解决域中普通用户只限于10台客户机加入域的数量问题
  • 10
    博客园模板 样式优化
  • 1
    未来技术纵横谈：区块链、物联网与虚拟现实的融合与创新
    39
  • 2
    软件体系结构 - 缓存技术（9）缓存穿透
    38
  • 3
    提升Android应用性能的实用技巧
    35
  • 4
    工具变量法(两阶段最小二乘法2SLS)线性模型分析人均食品消费时间序列数据和回归诊断（下）
    41
  • 5
    软件体系结构 - 缓存技术（8）缓存雪崩
    41
  • 6
    软件体系结构 - 缓存技术（7）Redis持久化方法
    44
  • 7
    Python用KShape对时间序列进行聚类和肘方法确定最优聚类数k可视化
    34
  • 8
    软件体系结构 - 缓存技术（6）淘汰策略
    37
  • 9
    基于R语言股票市场收益的统计可视化分析
    42
  • 10
    软件体系结构 - 数据分片（2）一致性哈希分片
    38

    • 相关课程

    更多
  • MySQL数据库入门学习
  • 快速掌握 MongoDB 数据库
  • 数据库的前世今生
  • Redis数据库入门
  • 大咖说 - 开源数据库
  • 数据库及SQL/MySQL基础

    • 相关电子书

    更多
  • 冬季实战营第三期：MySQL数据库进阶实战
  • 数据库2025 V3
  • 为什么PostgreSQL是最适合去O的数据库

    • 相关实验场景

    更多
  • WorkBench操作云上数据库
  • 云端MySQL的安装及应用
  • 手动部署MySQL数据库（Alibaba Cloud Linux 2）
  • 手动部署MySQL数据库（CentOS 7）
  • MySQL数据库DTS迁移上云
  • 如何通过RDS MySQL数据库内网访问
    • 下一篇
    部署LAMP环境（Alibaba Cloud Linux 3）