【MySQL】数据库基础知识汇总和增删查改操作

1.数据库基础知识:

1. 什么是数据库：这是一类具体的软件，把很多的数据给组织起来，在数据库这个软件内部会广泛的使用到数据结构，将数据组织起来的目的就是为了增删查改

2. 关系型数据库：MySQL、SQL Server、Oracle、SQLite（超轻量级数据库）

3. 非关系型数据库：Redis、HBase、MongoDB

4. MySQL也是一个客户端-服务器的结构，客户端和服务器之间也是通过网络实现交互的很多时候说到MySQL也是指的是MySQL服务器

5. 数据库也是要把数据存储在外存（硬盘）上的，因为外存的数据能够持久化存储。但是也有特例，redis就是属于少数的内存数据库，换来的好处就是访问速度快!

6. 每一个表分成了很多行，每一行称为是一条记录；每一行又有很多列，每一列称为是一个字段.

7. SQL是个编程语言，MySQL是个软件可以解析执行SQL语言。数据库操作是相对低效的(实际开发中,数据库经常容易成为一个系统的性能瓶颈)

内存 和 外存（硬盘。U盘、光盘、软盘）：

内存和外存（硬盘）的区别：

1. 内存存储的空间小，外存的存储空间大
2. 内存访问速度快，外存访问速度慢（3-4个数量级）
3. 内存成本高，外存成本低
4. 内存的数据容易丢失（断电就没了），外存的数据持久化存在（断电也不会消失），但是一般的硬盘存储也是有一定的时间限制的。

MySQL组织数据的基本格式：

关系型数据库：

数据库服务器=>包含了很多数据库=>包含了很多数据表=>包含了很多行（记录）=>包含了很多列（字段）

非关系型数据库：

组织形式更加的灵活，通常是按照文档或者键值对的方式来存储的。

在命令行输入sql的小技巧:

1. 使用上下方向键,可以找到上一条/下一条sql语句.
2. 如果有个sql输入了一半,不想继续执行了可以使用ctrl+c来终止sql.

---

---

2.对数据库的相关操作:

(这里指的是数据库服务器上的某个数据集合)

2.1.显示当前所有的数据库, show databases;

2.2.创建数据库, create database 数据库名;

1. 如果数据库名已经存在,则创建的时候会报错!
2. 不推荐使用关键字来作为数据库名/表名/列名,如果要用则需要使用反引号括起来 (tab上面的那个键).
3. create database if not exists 数据库名;这种写法表示如果数据库已经存在则不会报错,不存在就创建.

2.3.选中数据库, use 数据库名;

1. 后续针对数据库的操作,需要先确定你想操作那个库.

2.4.删除数据库, drop database 数据库名;

1. 数据库的删除操作是非常危险的操作!
2. 一旦删除了,数据可能就找不回来了,还是有可能找回来(找回来一部分).原因如下:
3. 数据是存储在硬盘这样的外存上的,删除数据库其实就是删除硬盘上的数据.操作系统把硬盘分成了很多小的盘块,每个盘块都会保存一部分的数据.要删除数据的时候,并不是将这些数据给涂抹掉而是标记成"无效",标记成无效之后,这个盘块就会被系统分配给其他程序用来保存数据,但是在这个盘块被分出去之前,上面的数据还是存在的.

---

---

3.对表的相关操作:

(操作表的时候离不开数据类型)

3.1.基础类型:

常用类型:

int / bigint / double / decimal / varchar / datetime

3.1.1.数值类型(分为整数和浮点数):

数据类型	大小	对应java类型	说明
bit[(M)]	M指定位数,默认为1
tinyint	1字节	Byte
smallint	2字节	Short
int	4字节	Integer
bigint	8字节	Long
float(M,D)	4字节	Float	单精度,M有效数字长度,D小数点后的位数,会发生精度丢失
double(M,D)	8字节	Double
decimal(M,D)	M/D最大值+2	BigDecimal	双精度,M有效数字长度,D小数点后的位数,精确数值
numeric(M,D)	M/D最大值+2	BigDecimal	双精度,M有效数字长度,D小数点后的位数,精确数值

1. c/c++/java这些编程语言表示浮点数时都是遵守IEEE754标准的,在这个标准中有些浮点数精度不准有误差,这个误差会随着算术运算而被放大.
2. 在SQL中如果使用的是decimal(M,D)或者numeric(M,D);来表示小数,优点就是误差变小了,缺点是计算效率降低,存储空间变高了.
3. 在SQL中不推荐使用unsigned,尽量不要使用!

3.1.2.字符串类型:

数据类型	大小	说明	对应java类型
varchar(size)	0-65535个字节	可变长度字符串	String
text	0-65535个字节	长文本数据	String
mediumtext	0-16777215个字节	中等长度文本数据	String
blob	0-65535个字节	二进制形式的长文本数据	byte[ ]

1. varchar(size);是最常用的字符串类型,可变长字符串.size表示了最大的长度,意思是字符串中最多能包含几个字符(不是字节).
2. 文本数据(txt/.java/.c/.html);二进制数据(docx/ppt/exe/dll/png/mp4).用记事本打开后能看懂的就是文本文件,看不懂的就是二进制文件.

3.1.3.日期类型:

数据类型	大小	说明	对应的java类型
datetime	8个字节	不会进行时区的检索和转换	java.util.Data/java.sql.Timestamp
timestamp	4个字节	自动检索当前时区并转换	java.util.Data/java.sql.Timestamp

1. now();是SQL内置的函数,获取到当前时间.
2. 只是按照字符串的格式插入,时间在内部就是一个datetime类型

3.2.创建表, create table 表名(列名 类型,列名 表名…);

1. 表操作之前要先 use 数据库
2. 创建表需要保证同一个数据库里面不能又同名的表
3. 保证表名和关键字不能重复,实在想用使用``引起来
4. 建表的时候建表语句可能比较长,可以把sql分成多行来写
5. comment ‘xx’,这是个注释,也可以使用-- xx的形式(-- 后面紧跟着空格)

3.3.查看当前数据库中所有表, show tables;

3.4.查看指定的表结构, desc 表名;

1. Field是字段
2. int(11);这里的11和数据存储无关,这里描述的是打印的格式;vachar(20)最多能存20个字符.
3. 一个学生表中的id和name都是字段

3.5.删除表, drop table 表名;

---

---

4.对数据的相关操作:

数据库存在的意义就是用来组织数据,组织数据的意义就是为了增删查改CRUD

4.1.新增:

4.1.1.新增数据, insert into 表名 values (值,值…);

1. 这里的 into 可以省略
2. 这里的值的类型和个数需要和表的类型/个数相匹配
3. 在SQL中,没有字符类型,使用单引号或者双引号都可以表示字符串

4.1.2.指定列插入, insert into 表名 (列名,列名,…) values (值,值,…);

1. 指定针对哪几列进行插入,多个列之间使用 , 分割.
2. 如果写的列名不存在,就会报错!

4.1.3.多行插入, insert into 表名 values (值,值,…),(值,值,…),(值,值,…),(值,值,…);

修改数据库字符编码:

create database xxx character set ut8;

缺点就是每次创建数据库都需要设置字符集,有点麻烦

还有个一劳永逸的方法:,那就是修改MySQL的配置文件

4.2.查询:

查询操作,查找语句是SQL最复杂的操作,SQL中是使用 select 关键字,表示查询.

4.2.1.全列查询, select * from 表名;

1. 查询出当前你表的所有行和所有列
2. 这里的 * 称为通配符,表示所有的列.
3. 这是一个危险操作,会让服务器大规模的读取磁盘上的数据,在把数据通过网卡写回到客户端,如果当前存储的数据量级非常大就会导致读磁盘和写网卡的时间非常长,甚至于把磁盘带宽和网卡带宽都吃满.

4.2.2.指定列查询, select 列名,列名,… from 表名;

1. 可以写多个列名,中间使用 , 分割
2. 查询过程中客户端显示的内容只是一个临时表,临时表是根据服务器的数据生成的.
3. 所有的查询操作,都不会影响服务器磁盘上的数据

4.2.3.查询带表达式, select 列名+10,表达式,… from 表名;

1. 表达式查询是行和行之间的查询
2. 一边查,一边计算
3. 这里只是影响到临时表的数据展示,对于数据库服务器的原有数据没有任何影响.

4.2.4.查询带别名, select 表达式 as 别名 from 表名;

1. 在SQL中允许在进行查询的时候,给临时表的列指定别名
2. 此处的 as 可以省略,但是不建议.

4.2.5.查询去重, select distinct 列名 from 表名;

1. distinct 关键字 修饰某个列,针对这列的数据进行去重.
2. 去重操作,可以针对多个列来进行,如果是多个列去重,则只有说多个列都是相同的时候才视为重复才进行去重

4.2.6.查询结果排序, select 列名 from 表名 order by 列名 asc/desc;

1. 按照order by 后面的这个列进行排序,默认是升序,加上desc就是降序了
2. asc是个升序,但是没必要写
3. select * from xxx order by math desc ,chinese desc;这样math和Chinese才能同时排降序.
4. 排序还能够指定多个列排序,指定多个列的时候,哪个列靠前哪个列的优先级就高,如下:
5. select * from xxx order by math,chinese;这个操作的意思是,先看math的大小,如果math相同再比较chinese.
6. 给上述代码举个例子:找对象的要求(有钱,帅,人品好),先把所有的追求者的资产降序排序,薪资一样的再比较颜值,颜值都不错的在比较人品.

4.2.7.条件查询, select 列名 from 表名 where 条件;

1. 查询的时候指定筛选条件,使用where字句来表示条件

2. between a and b;表示判定当前的值是否在[a,b]区间中,既包含a又包含b

3. in(a,b,c,…)判定当前的值是否出现在( ) 中,出现则认为是真

4. 这里的条件相当于是针对表进行遍历,依次取出每一条记录带入条件,看条件是true还是false.条件成立就把记录留下来放到结果中,不成立就会跳过.

5. select * from xxx where chinese > english 针对同一行的列和列比较,不能跨行比较

6. where 条件的时候也可以使用表达式.

7. 在条件查询的SQL中先执行where符合条件的记录,再执行select 前半部分决定显示哪些列,以及进行啥样的计算.

8. 前面说的order by 则可以使用别名,这个操作是在查询结果都知道了之再排序的

9. 在 SQL语句中, and 的优先级比 or 高.当一个条件同时有and和or的时候先算and.

10. 条件查询是需要遍历表的,在表很大的时候,遍历操作非常的低效,所以数据库就想出了一些办法来优化查询其中最有效的方法就是索引,更加便于SQL引擎来优化代码优化查询

11. SQL引擎就是在服务器中,针对SQL语句解析的模块,下面的操作比上面的更好!

12. 模糊查询,这是一个比较低效的操作.依赖一些通配符,来表示要匹配的值是什么样的.(%代替任意个任意字符;_代替一个任意字符)

13. NULL = NULL 结果还是 NULL= > false; where math = null -> 为false,直接跳过.where math <=> null 和where math is null的作用是一样的.

4.2.8.分页查询, select 列名 from 表名 limit N offset M;

1. select * from xxx limit N;查询前N条记录
2. select * from xxx limit N offset M; 从第M条下一条开始,查询N条记录 (M从0开始算)

(结合使用关键字时的顺序: from + where + order by + limit)

4.3.修改:

4.3.1.修改操作, update 表名 set 列名 = 值,列名 = 值,… where 条件;

1. 需要明确针对 哪张表 的 哪些行 的 哪些列 改成 哪些值
2. 一条语句也可以修改多个列,针对记录进行筛选符合条件的记录
3. 如果在update的时候没写条件,整个表的所有记录都会发生改变
4. update也是一个危险操作 !

4.4.删除:

4.4.1.删除操作, delete from 表名 where 条件;

1. 通过这个条件来控制删除哪条/哪些记录
2. 如果没写条件相当于全部删除了
3. delete只是吧表里的记录都给删了,表还存在(剩下一个空表)
4. drop是连表都给删了,空表都没了