MySQL数据库基础知识点总结

MySQL数据库基础知识

什么是数据库？ 什么是数据库管理系统？什么是SQL？ 他们之间的关系是什么？

数据库：

英文单词DataBase,简称DB。按照一定格式存储数据的一些文件的组合。顾名思义：存储数据的仓库，实际上就是一堆文件，这些文件中存储了具有特定格式的数据。

数据库管理系统：

DataBaseManagement,简称DBMS。

数据库管理系统是专门用来管理数据库中数据的，数据库管理系统可以对数据库当中的数据进行增删改查。

常见的数据库管理系统：

MySQL，Oracle，MS SqlServer，DB2，Sybase等。

SQL：结构化查询语言

（Structured Query Language）

程序员需要学习SQL语句，程序员通过编写SQL语句，然后DBMS负责执行SQL语句，最终来完成数据库中数据的增删改查操作。

SQL是一套标准，程序员主要学习的就是SQL语句，SQL可以在MySQL中使用，也可以在Oracle使用，也可以在DB2使用。

三者之间的关系?

DBMS–执行–>SQL–>操作–> DB

找一找MySQL的服务在哪里？

计算机–>右键–>管理–>服务和应用程序–>服务–>找到MySQL服务，在服务上点击右键：

启动

重启服务

停止服务

管理员命令行启动和停止服务

语法：net start 服务名称；

​ net stop 服务名称；

其他服务的启停都可采用以上命令；

MySQL登录

方法一命令行键入:mysql -uroot -p123456

方法二命令行键入:>mysql -uroot -p
Enter password: ***

MySQL常用命令：

退出MySQL：exit

查看数据库：show databases;

（注意：以分号结尾，分号是英文的分号，且不区分大小写。）

（注意：MySQL不见分号不执行，分号表示结束。

​ \c也可以用来终止一条命令的输入）

创建数据库：create database 数据库名;

使用数据库：use 数据库名;

查看数据库中的表：show tables;（需要先使用数据库）

不看表中的数据，只看表的结构，有一个命令：desc 表名;

查看当前MySQL数据库的版本号：select version();

查看当前使用的是哪个数据库：select database();

数据库中最基本的单元是表：table

数据库中是以表格的形式表示数据的；

因为表比较直观

任何一张表都有行和列：

行（row）:被称为数据/记录。

列（column）:被称为字段。

如姓名字段，性别字段，年龄字段。

关于SQL语句的分类

DQL:数据查询语言（凡是带有select关键字的都是查询语句）

select…

DML:数据操作语言（凡是对表中的数据进行增删改操作的都是DML）

insert delete update

insert:增 eg:insert into tb_user(userName,userPwd,userAge)value('小明','123',25)

delete:删 eg: delete from tb_user where userAge=25

update:改 eg:update tb_user set userAge=20 where userName='zhangsan'

DDL:数据定义语言（凡是带有create、drop、alter的都是DDL）

DDL主要操作的是表的结构，不是表中的数据。

create:新建，等同于增

新建数据库：

• create database 库名

• create database if not exists db001 charcter set UTF-8;

drop:删除

• 删除数据库 drop database db001

• 判断数据库存在，存在再删除drop database if exists db001

  • 删除表

    drop table 表名

    drop table if exists 表名

alter:修改

• 修改表名：alter table 表名 rename to 新的表名;

• 修改列名称，类型：alter table 表名 modify 列名 数据类型

这个增删改和DML不同，这个主要是对表的结构进行操作。

TCL:事务控制语言

包括：

​ 事务提交：commit

​ 事务回滚：rollback

DCL:数据控制语言。

例如：授权grant，撤销权限revoke。

简单查询

查询一个字段:

select 字段名 from 表名；

其中要注意：

​ select和from都是关键字

​ 字段名和表名都是标识符

eg: select dname from dept;

查询多个字段：

用","隔开

eg: select deptno,dname from dept;

查询所有字段：

第一种方式：把每个字段名都写上

eg: select a,b,c,d,e from tablename;

第二种方式：使用*

eg: select * from emp;

(这种方式的缺点：

​ 1.效率低：需要先将*变为字段

​ 2.可读性差。

​ 在实际开发中不建议。)

如何给查询的列起别名？

使用as关键字起别名。

eg: select deptno,dname as deptname from dept;

注意:只是将显示的查询结果列名显示为deptname，原列名还是叫：dname

select 语句是永远都不会进行修改操作的（因为他只负责查询）。

as关键字可以省略吗？可以的

eg: select deptno,dname deptname from dept;

起别名时，别名里有空格怎么办？

select deptno,dname ‘dept name from’ dept;//加单引号

select deptno,dname “dept name from” dept;//加双引号

注意:在所有的数据库当中，字符串统一使用单引号括起来。

单引号是标准，双引号在Oracle数据库中用不了，但是在MySQL中可以使用

单引号比较标准，双引号不标准。

字段可以使用数学表达式 eg: sal*12

别名是中文需要用单引号括起来

条件查询

单条件查询

SELECT [ DISTINCT ] * | 列名 | 表达式 [别名] [,…]

FROM 表名

[ WHERE 条件表达式 ] ;

特殊比较运算符

​ BETWEEN…AND…运算符

语法：WHERE 列名 BETWEEN 值1 AND 值2;

​ 注意:

使用between and时，必须遵循左小右大；

between and是闭区间，包括两端的值。

​ IN运算符

语法：WHERE 列名 IN （值1, 值2, …值n)

​ and 并且

​ or 或者

​ LIKE运算符

​ 语法：WHERE 列名 LIKE（值1, 值2, …值n)

• % : 百分号，代表匹配零个或任意个字符。

• _ : 下划线，代表匹配1个任意字符。

• 找出名字中有"_"下划线的人？

  select name from t_student where name like ‘%_%’;//这样写是不行滴

  select name from t_student where name like ‘%\_%’;//需要加上 \ 转义字符，把下划线变成普通的字符。

​ IS NULL 运算符

• 使用 “列名 = NULL”是查询不出来的。只能使用 “列名 IS NULL”

• （因为null不能用等号进行衡量）

• 注意：查询某列不为空，使用 “列名 IS NOT NULL”

  各种运算符的优先级如下表。

  优先级	运算分类	运算符举例
  1	算术运算符	*, , +, -
  2	比较运算符	=, <>, <, >, <=, >=
  3	特殊比较运算符	BETWEEN…AND… ,IN,LIKE,IS NULL
  4	逻辑非	NOT
  5	逻辑与	AND
  6	逻辑或	OR

对查询结果进行排序

需要使用ORDER BY子句

1.可以按照3种方式进行排序：分别是按列名排序、按列别名排序、按列序号排序。

2.ASC表示按升序排序(默认值), DESC表示按降序排序。

3.可以同时按照多个列名进行排序

4.ORDER BY 子句必须写在SELECT语句的最后

按多列排序：

SELECT    ename, deptno, sal 
FROM      emp 
ORDER BY  deptno ASC, sal DESC;

聚合函数

概念：将一列数据作为一个整体，进行纵向计算

分类：count(列名)：统计数量(一般选用不为null的列)

max(列名)：最大值

min(列名)：最小值

sum(列名)：求和

avg(列名)：平均值

语法：SELECT 聚合函数名(列名) FROM 表;

分组查询

语法：SELECT 字段列表 FROM 表名 [WHERE 分组前条件限定] GROUP BY 分组字段名 [HAVING 分组后条件限过滤];

注意：分组之后，查询的字段为聚合函数和分组字段，查询其他字段无任何意义

where和having的区别:

1.执行时机不一样，where是分组之前进行限定，不满足where条件，则不参与分组，而having是分组之后对结果进行过滤。

2.可判断的条件不一样，where不能对聚合函数进行判断，having可以。

**执行顺序：**where>聚合函数>having

分页查询

语法：SELECT 字段列表 FROM 表名 LIMIT 起始索引，查询条目数;

eg:

select * from stu limit 0,3;

//从0开始查询，查询3条数据

注意：起始索引从0开始

tips：分页查询limit是MySQL的方言

Oracle分页查询使用rownumber

SQL Server分页查询使用top

约束

1.约束的概念：

• 约束是作用于表中列上的规则，用于限制加入表中的数据
• 约束的存在保证了数据库中数据的正确性、有效性和完整性

2.约束的分类

• 非空约束：保证列中所有数据不能有null值；关键字：NOT NULL
• 唯一约束：保证列中所有数据各不相同；关键字：UNIQUE
• 主键约束：主键是一行数据的唯一标识，要求非空且唯一；关键字：PRIMARY KEY
• 默认约束：保存数据时，未指定值则采用默认值；关键字：DEFAULT
• 外键约束：外键用来让两个表的数据之间建立连接，保证数据的一致性和完整性；关键字：FOREIGN KEY

索引

索引是帮助MySQL高效获取数据的数据结构，通俗点的说，数据库索引好比是一本书的目录，可以直接根据页码找到对应的内容，目的就是为了加快数据库的查询速度。

索引的分类

主键索引：primary key

• 设定为主键后，数据库自动建立索引，InnoDB为聚簇索引，主键索引列值不能为空（Null）。

唯一索引：

• 索引列的值必须唯一，但允许有空值（Null），但只允许有一个空值（Null）。

复合索引：

• 一个索引可以包含多个列，多个列共同构成一个复合索引。

全文索引：

• Full Text（MySQL5.7之前，只有MYISAM存储引擎引擎支持全文索引）。
• 全文索引类型为FULLTEXT，在定义索引的列上支持值的全文查找允许在这些索引列中插入重复值和空值。全文索引可以在Char、VarChar 上创建。

空间索引：

• MySQL在5.7之后的版本支持了空间索引，而且支持OpenGIS几何数据模型，MySQL空间索引遵循OpenGIS几何数据模型规则。

前缀索引：

• 在文本类型为char、varchar、text类列上创建索引时，可以指定索引列的长度，但是数值类型不能指定。

创建单列索引：

#创建单列索引，只能包含一个字段
create index name_index on tb_user(userName);
#name_index是索引名
#tb_user是表名
#userName是列名

创建唯一索引：

#创建唯一索引，只能有一个列
create unique index age_index on tb_user(userAge);

创建复合索引：

#复合索引
create index name_age_index on tb_user(userName,userAge);

查询索引：

#查询tb_user表中的索引
show index from tb_user;

• MySQL索引使用的数据结构主要有BTree索引和hash索引。

对于hash索引来说，底层的数据结构就是哈希表，因此在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快；其余大部分场景建议选择BTree索引。

索引的优缺点

优点：

• 大大提高数据查询速度。
• 可以提高数据检索的效率，降低数据库的IO成本，类似于书的目录。
• 通过索引列对数据进行排序，降低数据的排序成本降低了CPU的消耗。
• 被索引的列会自动进行排序，包括【单例索引】和【组合索引】，只是组合索引的排序需要复杂一些。
• 如果按照索引列的顺序进行排序，对order 不用语句来说，效率就会提高很多。

缺点：

• 索引会占据磁盘空间。
• 索引虽然会提高查询效率，但是会降低更新表的效率。比如每次对表进行增删改查操作，MySQL不仅要保存数据，还有保存或者更新对应的索引文件。
• 维护索引需要消耗数据库资源。

综合索引的优缺点：

• 数据库表中不是索引越多越好，而是仅为那些常用的搜索字段建立索引效果最佳!

多表查询

内连接

内连接查询语法：
1.隐式内连接
select 字段列表 from 表1，表2... where 条件;
2.显示内连接
select 字段列表 from 表1[inner] join 表2 on 条件;

外连接

外连接查询语法
1.左外连接
select 字段列表 from 表1 left[outer] join 表2 on 条件;
2.右外连接
select 字段列表 from 表1 right[outer] join 表2 on 条件;

子查询

1.子查询根据查询结果不同，作用不同：

• 单行单列：作为条件值，使用= != > <等进行条件判断
  select 字段列表 from 表 where 字段名 = (子查询);
• 多行单列：作为条件值，使用in等关键字进行条件判断
  select 字段列表 from 表 where 字段名 in (子查询);
• 多行多列：作为虚拟表
  select 字段列表 from (子查询) where 条件;

事务

事务简介：

• 数据库的事务（Transaction）是一种机制、一个操作序列，包含了一组数据库操作命令
• 事务把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么同时成功，要么同时失败
• 事务是一个不可分割的工作逻辑单元
• • 开启事务
• start transaction;
• 或者begin;
• • 提交事务
• commit;
• • 回滚事务
• rollback;

事务的的四大特征：

• 原子性（Atomicity）:事务是不可分割的最小操作单位，要么同时成功，要么同时失败
• 一致性（Consistency）:事务完成时，必须使所以的数据都保持一致状态
• 原子性（Isolation）:多个事务之间，操作的可见性
• 原子性（Durability）:事务一旦提交或回滚，它对数据库中的数据的改变就是永久的