MySQL数据库基础知识点复习总结

一、数据库介绍

1、什么是数据库

数据库就是一个存放计算机数据的仓库，这个仓库是按照一定的数据结构（数据结构是指数据的组 织形式或数据之间的联系）来对数据进行组织和存储的，可以通过数据库提供的多种方法来管理其 中的数据。

2、数据库的种类

最常用的数据库模式主要有两种，即关系型数据库和非关系型数据库。

3、生产环境常用数据库

生产环境主流的关系型数据库有 Oracle、Microsoft SQL Server、MySQL/MariaDB等。 生产环境主流的关系型数据库有 MongoDB Memcached Redis

4、关系型数据库

1、关系型数据库介绍 关系型数据库模型是把复杂的数据结构归结为简单的二元关系（即二维表格形式）。在关系型数据 库中，对数据的操作几乎全部建立在一个或多个关系表格上，通过这些关联的表格分类、合并、连 接或选取等运算来实现数据的管理。 关系型数据库诞生距今已有 40 多年了，从理论产生到发展到实现产品，例如：常见的 MySQL 和 Oracle 数据库，Oracle 在数据库领域里上升到了霸主地位，形成每年高达数百亿美元的庞大产业 市场，而 MySQL 也是不容忽视的数据库，以至于被 Oracle 重金收购了。 2、关系型数据库小结 关系型数据库在存储数据时实际就是采用的一张二维表（和 Word 和 Excell 里表格几乎一样）。 市场占有量较大的是 MySQL 和 Oracle 数据库，而互联网场景最常用的是 MySQL 数据库。 通过 SQL 结构化查询语言来存取、管理关系型数据库的数据。 关系型数据库在保持数据安全和数据一致性方面很强，遵循 ACID 理论

5、非关系型数据库

1、非关系数据库诞生的背景

非关系型数据库也被称为 NoSQL 数据库，NoSQL 的本意是 “Not Only SQL”，指的是非关系型数 据库，而不是“NO SQL”的意思，因此，NoSQL 的产生并不是要彻底否定关系型数据库，而是作为 传统数据库的一个有效补充。NoSQL 数据库在特定的场景下可以发挥难以想象的高效率和高性 能。 随着 Web2.0 网站的兴起，传统的关系型数据库在应付 Web2.0 网站，特别是对于规模日益扩大 的海量数据，超大规模和高并发的微博、微信、SNS 类型的 Web2.0 纯动态网站已经显得力不从 心，暴露了很多难以克服的问题，例如：传统的关系型数据库IO瓶颈、性能瓶颈都难以有效突 千锋教育云计算学院 破，于是开始出现了大批针对特定场景，以高性能和使用便利为目的功能特异化的数据库产品。 NoSQL（非关系型）类的数据库就是这样的情景中诞生并得到了非常迅速的发展。 NoSQL 是非关系型数据库的广义定义。它打破了长久以来关系型数据库与ACID理论大一统的局 面。NoSQL数据存储不需要固定的表结构，通常也不存在连续操作。在大数据存取上具备关系型 数据库无法比拟的性能优势。该术语（NoSQL）在2009年初得到了广泛认同。 当今的应用体系结构需要数据存储在横向伸缩性上能够满足需求。而NoSQL存储就是为了实现这 个需求而诞生的。Google的 BigTable 与Amazon的Dynamo是非常成功的商业 NoSQL 实现。一 些开源的 NoSQL 体系，如 Facebook 的 Cassandra，Apache 的 HBase，也得到了广泛认同， Redis，MongoDB 也逐渐的越来越受到各类大中小型公司的欢迎和追捧。

2、非关系型数据库小结

NoSQL 数据库不是否定关系型数据库，而是作为关系数据库的一个重要补充。 NoSQL 数据库为了灵活及高性能、高并发而生，忽略影响高性能、高并发的功能。 在NoSQL 数据库领域，当今的最典型产品为 Redis（持久化缓存）、MongoDB、 Memcached（纯内存）等。 NoSQL 数据库没有标准的查询语言（SQL），通常使用REST式的数据接口或者查询API。

3、非关系型数据库种类

1、键值（Key-Value）存储数据库 键值数据库就类似传统语言中使用的哈希表。可以通过key来添加、查询或者删除数据，因为使用 key主键访问，所以会获得很高的性能及扩展性。 键值（Key-Value）数据库主要是使用一个哈希表，这个表中有一个特定的键和一个指针指向特定 的数据。Key-Value模型对于IT系统来说的优势在于简单、易部署、高并发。 典型产品：Memcached、Redis、MemcachedB、Berke ley DB

2、列存储（Column-Oriented）数据库 列存储数据库将数据存储存在列族（Column Family）中，一个列族存储经常被一起查询的相关数据。典型产品：Cassandra，HBase

3、面向文档（Document-Oriented）的数据库 文档型数据库的灵感是来自于Lotus Notes办公软件的，而且它同第一种键值存储相类似。该类型 的数据模型是版本化的文档，半结构化的文档以特定的格式存储，比如JSON。文档型数据库可以 看作是键值数据库的升级版，允许之间嵌套键值。而且文档型数据库比键值数据库的查询效率更 高。 面向文档数据库会将数据以文档的形式存储。每个文档都是自包含的数据单元，是一系列数据项的 集合。每个数据项都有一个名称与对应的值，值既可以是简单的数据类型，如字符串、数字和日期 等；也可以是复杂的类型，如有序列表和关联对象。数据存储的最小单位是文档，同一个表中存储 的文档属性可以是不同的，数据可以使用XML、JSON 或者 JsonB 等多种形式存储。 典型产品：MongDB、CouchDB

1、结构化查询语言 SQL

• 结构化查询语言(Structured Query Language)，简称SQL，是数据库的标准查询语言。可以通过 DBMS对数据库进行定义数据，操纵数据，查询数据，数据控制等 
• 1、数据定义语言 (DDL)
• Data Dafinitaon Language
• 如创建表 create
• 删除表 drop
• 修改表 alter
• 清空表 truncate，彻底清空，无法找回

  show databases;	# 查看所有数据库：
  show tables;	# 查看所有表：
  drop database db1;	# 删除数据库
  create database db1 default character set utf8;	# 创建数据库
  use database;	# 选择数据库
  create table t1(id int(3),name varchar(20));	# 创建表
  desc t1	# 查看表
  insert into t1 values(1,'user1');	# 插入数据
  alter table t1 add(age int(3));	# 添加表字段语句
  alter table t1 drop id;	# 删除表字段语句
  alter table t1 modify age varchar(2);	# 修改表字段类型格式
  alter table t1 change age p1age int(3);	# 修改表字段名称
  alter table t1 rname per;	# 修改表字段名称
  truncate table t1;	# 清空表结构
  drop table t1;	# 删除表结构

  1、数据操纵语言(DML)

• insert：向表中插入数据

• delete：删除表中的数据，格式：delete from tablname [where 条件]

• update：修改表中的数据 格式：

• update tablname set colName1=value1[,colName2=value2] [where 条件] where : 对表中的数据增加条件进行限制，起到过滤的作用。

• 1. 格式: where colName 关系运算符 value [or|and 条件2]

• 2. 关系运算符: >，>=，等于：=，不等于：!= 或 <>

• null值操作：比较null时，不能使用=或者!= 或者<>，而是使用 is或者is not，在select子句中，使用关系运算符

实验实例

1、创建一个表,表名为ikun

create table ikun(

id int,

name varchar(20),

birth date,

habby varchar(50)

);

2、插入数据

插入数据  100，'蔡徐坤' ,'唱跳rap' ，下面是两种插入方式，第二种可以批量插入。只需添加多个用 逗号隔开即可

insert into ikun values (101,'蔡徐坤',null,'唱跳rap');

insert into ikun(id,name,address) values(102,'鸽鸽','篮球');

3、删除数据

# 删除表ikun中的所有数据。 delete from ikun;

# 删除表中ikun中的id为101的记录。 delete from ikun where id=101;

# 删除表ikun中 id为102和地址为篮球的的数据 select * from ikun;

delete from ikun where id=102 and habby='篮球';

4、修改数据 

 # 修改表 ikun中的字段habby为背带裤。 update ikun set habby='背带裤';

 # 修改表 ikun中的 id 为 102 的 “habby” 为“music”，人名为“偶像练习生”。 update ikun set habby='music',name='偶像练习生' where id=102;

# 将生日为 null 的记录的 name 改为 'general' update ikun set name='general' where tbirth is null;

# 将 id 为 101 的 birth 改为'1998-8-2'; update ikun set birth = '1998-8-2' where id = 101;

# 将 id 为 102 的 address 改为 null. update ikun set address=null where id=102;

 2、数据查询语言(DQL)

数据查询语言（Data Query Language），一般在工作中用到DQL的情况比较多，可以说是大多数使用数据库的时候都是在查询数据，所以这一段就写的比较多一些。

1、Select/for 基本查询语句

格式： select子句  from子句     select colName[,colName.......]  from tablname;

2、给列起别名

格式： select 列名1 as "要起的名" [, 列名2 as "要起的名" ,... ]  from tablname;

3、Where子句

• 作用：在增删改查时，起到条件限制的作用
• 多条件写法：in | not in (集合元素，使用逗号分开); 注意：同一个字段有多个值的情况下使用。in 相当于 or， not in 相当于 and
• all | any 与集合连用，此时集合中的元素不能是固定的必须是从表中查询到的数据。
• 范围查询：colName between val1 and val2; 查询指定列名下的val1到val2范围中的数据
• 模糊查询：like

1. 通配符：% 表示0或0个以上字符。_ 表示匹配一个字符
2. 格式: colName like value;

4、 实验实例

1、创建表

这个表弄出来是要需要用大量数据的，就暂时不用鸽鸽的数据了，哈哈哈，不用怀疑本ikun的真实性。

create table abc (
abc int(5),
name VARCHAR(10),
job VARCHAR(9),
mgr int(4),
hiredate DATE,
sal int(7),
comm int(7),
deptid int(2)
);

2、添加 数据

insert into abc (abcid,name,job,mgr,hiredate,sal,comm,deptid) values

(7369 ,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20),

(7499 ,'abcd','SALESMAN' , 7698 , '1981-2-10' ,  1600 , 300 , 30),

(7521 ,'WARD', 'SALESMAN' , 7698,  '1981-2-22' ,  1250 , 500 , 30),

(7566 ,'JONES','MANAGER' ,  7839,  '1981-4-2' ,  2975 , NULL , 20),

(7654 ,'MARTIN','SALESMAN' , 7698,  '1981-9-28',   1250 , 1400 , 30),

(7698 ,'BLAKE','MANAGER' ,  7839 , '1981-5-1' ,   2850 , NULL , 30),

(7782 ,'CLARK','MANAGER' ,  7839 , '1981-6-9' ,   2450 , NULL , 10),

(7788 ,'SCOTT','ANALYST' ,  7566,  '1987-4-19',   3000 , NULL , 20),

(7839 ,'KING','PRESIDENT' ,NULL,  '1981-11-17',  5000 , NULL , 10),

(7844 ,'TURNER','SALESMAN' , 7698,  '1981-9-8',    1500 , 0   ,  30),

(7876 ,'ADAMS','CLERK' ,   7788 , '1987-5-23',   1100,  NULL , 20),

(7900 ,'JAMES','CLERK' ,    7698 , '1981-12-3',   950 ,  NULL , 30),

(7902 ,'FORD','ANALYST' ,  7566 , '1981-12-3' ,  3000 , NULL , 20),

(7934 ,'MILLER','CLERK',     7782 , '1982-1-23',   1300 , NULL , 10),

(8002 ,'IRONMAN','MANAGER',   7839 , '1981-6-9',    1600, NULL , 10)，

(8003 ,'SUPERMAN','MANAGER',   7839 , '1981-6-9',    1600 , NULL , NULL);

3、查询数据

# 查询员工表 abc 中的 员工姓名，员工职位，员工入职日期和员工所在部门。

select name,job,hiredate,deptid from abc;

4、给列起别名

#查询员工姓名和员工职位。分别起别名，姓名和职位。

select name as "姓名" ,job as "职位" from abc;

5、Where 子句

# 查询员工表中部门号为 10 和 20 的员工的编号，姓名，职位，工资

select abcid,name,job,sal from abc where deptid=10 or deptid =20;

# 查询员工表中部门号不是 10 和 20 的员工的所有信息。

select * from abcdu where deptid<>10 and deptid<>20;

# 使用in ，not in修改上面的练习

select abcid,name,job,sal from abc where deptid in(10,20);

select * from abc where deptid not in (10,20);

6、All|Any与集合连用

# 查询员工abc,blake,clark三个人的工资

select * from abc where sal>all(select sal from abc where name in ('abc','blake','clark'));

7、范围查询

#查询工资大于等于1500并且小于等于2500的员工的所有信息。

select * from abc where sal between 1500 and 2500;

# 查询工资小于2000和工资大于2500的所有员工信息。

select * from abc where sal not between 2000 and 2500; 

8、模糊查询：Like 

# 查询员工姓名中有a和s的员工信息。

select name,job,sal,comm,deptid from abc where name like '%a%' and name like '%s%'; select name,job,sal,comm,deptid from abc where name like '%a%s%' or name like '%s%a%';

5、排序查询 Order By子句

• 当在查询表中数据时，记录比较多，有可能需要进行排序，此时可以使用 order by子句。
• 语法：select...from tablname [where 子句][order by 子句]
• 注意: 可以通过一个或多个字段排序。
• 格式:order by colName [ ASC | DESC ][ , colName1....[ ASC | DESC ] ];
• 排序规则: ASC：升序 ，DESC: 降序，默认是升序排序。
• select * from abc order by sal DESC; 

6、Distinct 去重

• 有的时候我们需要对重复的记录进行去重操作，比如，查询表中有哪些职位，此时，一种职位只需 要显示一条记录就够。
• 位置：必须写在select关键字后。

       select distinct name from abc;

7、分组查询 Group By 子句

• 分组查询与分组函数(聚合函数)：有的时候，我们可能需要查询表中的记录总数，或者查询表中每 个部门的总工资,平均工资，总人数。这种情况需要对表中的数据进行分组统计。需要group by子 句。
• 位置： select...from name [where 条件] [group by子句] [order by子句]
• 用法:group by Field1[,Field2]
• 注意：在分组查询时，select子句中的字段，除了聚合函数外，只能写分组字段。

       # 查询以部门id为分组员工的平均工资

        select deptid,avg(sal) as av from abc group by deptid;

• 聚合函数:

        1. count(Filed) 统计指定字段的记录数。

        2. sum(Filed) 统计指定字段的和。        

        3. avg(Filed) 统计指定字段的平均值

        4. max(Filed) 返回指定字段中的最大值。

        5. min(Filed) 返回指定字段中的最小值。        

        聚合函数会忽略null值。因此有时候需要使用函数：ifnull(field,value) （如果field字段对应的值不 是null,就使用field的值，如果是null,就使用value.）

        注意：多字段分组时（多表联合查询时不加任何条件），最多分组的数目为 Filed1Field2[Filed3....] 

 

7、分组查询添加条件 Having 子句

• 在分组查询时，有的时候可能需要再次使用条件进行过滤，这个时候不能 where子句，应该使用 having子句。having子句后可以使用聚合函数。
• 位置：位于group by子句后

        # 查询以部门id为分组员工的平均工资并且大于2000的部门 select deptid,avg(sal) as av from abc group by deptid having avg(sal)>2000;

8、基本查询总结

• 基本的查询语句包含的子句有：select子句，from子句，where子句，group by子句，having子 句，order by子句
• 完整的查询语句：select..from..[where..][group by..][having..][order by..]
• 执行顺序：先执行from子句，再执行where子句，然后group by子句，再次having子句，之后 select子句，最后order by子句

9、高级关联查询

• 有的时候，查询的数据一个简单的查询语句满足不了，并且使用的数据在表中不能直观体现出来。 而是需要预先经过一次查询才会有所体现。先执行的子查询。子查询嵌入到的查询语句称之为父查 询。
• 子查询返回的数据特点：

        1. 可能是单行单列的数据。

        2. 可能是多行单列的数据

        3. 可能是单行多列的数据

        4. 可能是多行多列的数据 

• 子查询可以在where、from、having、select字句中，在select子句中时相当于外连接的另外一种 写法。

        # 查询表中各部门人员中大于部门平均工资的人

        select name,sal,a.deptid ,b.av from abc a, (select deptid,avg(ifnull(sal,0)) as av from abc group by deptid) b where a.deptid=b.deptid and a.sal>b.av order by deptid ASC; 

3、数据控制语言(DCL)

数据控制语言 Data Control Language,作用是用来创建用户，给用户授权，撤销权限，删除用户。

格式: 

1、创建用户

create user username@ip identified by newPwd;

create user 'ikun'@'183.2.172.42' identified by 'www.baidu.com'；

2、显示用户的权限

show grants for username@ip;

3、授权

grant 权限1，权限2... on 数据库名.* to username@ip; grant select,drop,insert on qfedu.* to 'ikun'@'183.2.172.42';

• DML 权限：insert,delete,update
• DQL 权限：select
• DDL 权限：create,alter,drop...

4、撤销权限

revoke 权限1，权限2..on 数据库名.* from username@ip;

revoke drop on qfedu.* to 'ikun'@'183.2.172.42';

5、删除用户

drop user username;

drop user ikun; 

6、使权限立即生效 

flush privileges;