MySQL基础知识大总结

一，介绍

数据库是什么，我们在学习其他编程语言的时候会使用数组呀，链表，二叉树等等一些数据结构来存储我们的数据，但是大家有没有发现我们一旦关闭程序，所有的数据都没有了，这在发行的软件来看是很不合理的吧，比如你的游戏账号在整个应用程序更新的时候就会全部丢失，所以我们会去使用数据库，来在硬盘上长久的存储我们的数据。 

 SQL的分类

DDL数据定义语言，用来维护存储数据的结构

DML数据操纵语言，用来对数据进行操作

DCL数据控制语言，主要负责权限管理和事务

不多抄了，我们直接开始实践。 

二，数据库操作

1，展示数据库

语法：

show databases;

可以查询所有数据库，

这里就是看到我们创建的所有数据库了，这4个是系统自带的，不要删除，删除了我们就要重新下了。 

2，创建数据库

语法：

create  database if not exists [数据库名]  [character set [字符集]]  collate [排序规则]；

character set 字符集的目的是让他能读取汉字，在5.7版本的字符集默认是无法读取汉字的，collate 是我们的排序规则，形成习惯，每次建库都这样写就行，if  not exists 是这个数据库如果不存在的意思。

我们这样就看到java113了。

我们创建数据库的时候是不可以使用关键字的，但是我们可以通过``(esc 下面的符号)来用关键字来创建数据库。 

我们来查询下数据库，

我们成功创建了数据库，但是我们要是使用``是会报错的。

 3，删除数据库

语法：

drop database if exists [表名]; 

我们来把刚刚创建的database数据库删掉。 

查询数据库

4，使用数据库

语法：

use [库名]；

 

三，数据类型

1，数值类型

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

2，字符串类型

数据类型	大小	说明	对应java类型
varchar(size)	0-65,535字节	可变长度字符串	String
text	0-65,535字节	长文本数据	String
mediumtext	0-16 777 215字节	中等长度文本数据	String
blob	0-65,535字节	二进制形式的文本数据	Byte[]

3，日期类型

数据类型	大小	说明	对应java类型
datetime	8字节	范围从1000到9999年，不会进行时区的 检索及转换。	java.util.date; java.sql.Timestamp

四，表操作

1，显示数据库中的所有表

语法：

show tables；

我们可以看到现在的表是没有的。

2，创建数据表

 语法：

create table if not exists [表名](

字段1 数据类型  [ [comment] '说明'],

字段2 数据类型 .......,

字段3 数据类型

);

我们这时候在展示所有数据表

在java113的数据库中已经出现了我们刚才创建的表；

3，查询表结构

语法：

desc 表名；

4，删除表

语法：

drop table if exists 表名；

我们数据库中的表又为空了。

 五，CRUD

语法：CRUD是啥玩应，其实就是增删查改英文的缩写。

 1，新增

语法：

insert into 表名 (字段)，(字段)  value (值，值)，[(值，值)];

 desc student;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| student_id | bigint       | YES  |     | NULL    |       |
| name       | varchar(50)  | YES  |     | NULL    |       |
| score      | decimal(3,1) | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

 我们还是看student表，

 select * from student;
Empty set (0.00 sec)

 这里完全是空的。

1，单行数据，全列插入

 insert into student values (1,'张三',98.5);

 select * from student;
+------------+--------+-------+
| student_id | name   | score |
+------------+--------+-------+
|          1 | 张三   |  98.5 |
+------------+--------+-------+

 我们成功插入了一行数据。

2，多行数据，指定列插入

insert into student (student_id) values (1),(2),(3);

select * from student;
+------------+--------+-------+
| student_id | name   | score |
+------------+--------+-------+
|          1 | 张三   |  98.5 |
|          1 | NULL   |  NULL |
|          2 | NULL   |  NULL |
|          3 | NULL   |  NULL |
+------------+--------+-------+

 我们只指定了student_id的数据并没有指定其他字段的数据，

这里跟大家说一下，这个小黑框是可以直接导入本地文件保存的数据库的，我们首先使用编辑器来保存我们的sql代码，我用的Navicat，

保存，在小黑框输入

 source C:/MySQL/test.sql;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| java113            |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

 我们就能直接执行所有的sql代码。

2，查询

语法：

select (列名) from 表名； 

select * from student;
+------------+--------+-------+
| student_id | name   | score |
+------------+--------+-------+
|          1 | 张三   |  98.5 |
|          1 | NULL   |  NULL |
|          2 | NULL   |  NULL |
|          3 | NULL   |  NULL |
+------------+--------+-------+

查询所有列的结果。

还可以查询单个的列；

select student_id from student;
+------------+
| student_id |
+------------+
|          1 |
|          1 |
|          2 |
|          3 |
+------------+

 重新弄一个数据；

select * from student;
+------------+--------+------+---------+---------+
| student_id | name   | math | chinese | english |
+------------+--------+------+---------+---------+
|          1 | 张三   | 89.0 |    78.0 |    45.0 |
|          2 | 李四   | 99.0 |    56.0 |    74.0 |
|          3 | 王五   | 89.0 |    88.0 |    93.0 |
|          4 | 赵六   | 97.0 |    67.0 |    85.0 |
+------------+--------+------+---------+---------+

 我们还可以让查询结果为表达式；

 select student_id,name,math+chinese+english from student;
+------------+--------+----------------------+
| student_id | name   | math+chinese+english |
+------------+--------+----------------------+
|          1 | 张三   |                212.0 |
|          2 | 李四   |                229.0 |
|          3 | 王五   |                270.0 |
|          4 | 赵六   |                249.0 |
+------------+--------+----------------------+

 我们查询math,chinese,english三个字段相加的表达式，这个字段的数据是放在一个临时表中的，要不212.0超出了我们定义的deccimal(3,1)的范围的，因为我们只定义的有序长度为3，

我们还可以给这个表达式起一个别名。

表达式 [as]  ' 别名' 

这个as，和' ' 是可以省略的，但是当别名中有空格的时候，''是不可以省略的；

select student_id,name,math+chinese+english as 总分 from student;
+------------+--------+--------+
| student_id | name   | 总分   |
+------------+--------+--------+
|          1 | 张三   |  212.0 |
|          2 | 李四   |  229.0 |
|          3 | 王五   |  270.0 |
|          4 | 赵六   |  249.0 |
+------------+--------+--------+

那么如果字段中出现了null还能进行表达式的运算吗

 select * from student;
+------------+--------+------+---------+---------+
| student_id | name   | math | chinese | english |
+------------+--------+------+---------+---------+
|          1 | 张三   | 89.0 |    78.0 |    45.0 |
|          2 | 李四   | 99.0 |    56.0 |    74.0 |
|          3 | 王五   | 89.0 |    88.0 |    93.0 |
|          4 | 赵六   | 97.0 |    67.0 |    85.0 |
|          5 | 钱七   | 78.0 |    NULL |    97.0 |
+------------+--------+------+---------+---------+

+------------+--------+--------+
| student_id | name   | 总分   |
+------------+--------+--------+
|          1 | 张三   |  212.0 |
|          2 | 李四   |  229.0 |
|          3 | 王五   |  270.0 |
|          4 | 赵六   |  249.0 |
|          5 | 钱七   |   NULL |
+------------+--------+--------+

 我们可以看到结果是null，所以在我们列与列之间的运算，出现了null，是无法计算的，但是之后行与行直接的聚合函数是不受null的影响的。

我们还可以对数据进行去重

select distinct (列名) from 表名；

我们在增加些数据；

我们可以看到出现了三个张三，第二个张三与第一个张三完全一样，但是最后一个张三的English与第一个不一样，我们来看看去重操作的现象；

我们看到第三个张三留下了，第二个不见了，所以我们去重操作是我们选择的字段的每一列的所有字段都重复才会被去重。

1，排序

语法：

order by (列名) asc|desc;

asc是升序，desc是降序，null被视为最小的数；

我们来从高到低查询下学生成绩的总分。 

select student_id,name,math+chinese+english as 总分 from student order by 总分 desc;
+------------+--------+--------+
| student_id | name   | 总分   |
+------------+--------+--------+
|          3 | 王五   |  270.0 |
|          1 | 张三   |  266.0 |
|          4 | 赵六   |  249.0 |
|          2 | 李四   |  229.0 |
|          1 | 张三   |  212.0 |
|          1 | 张三   |  212.0 |
|          5 | 钱七   |   NULL |
+------------+--------+--------+

这里我们在使用order by的时候是可以使用别名（总分）的，但是我们之后使用的where条件查询语句是不行的。

我们也可以指定多种排序方式，按优先级进行排序

select * from student order by math desc,chinese asc,english desc;
+------------+--------+------+---------+---------+
| student_id | name   | math | chinese | english |
+------------+--------+------+---------+---------+
|          2 | 李四   | 99.0 |    56.0 |    74.0 |
|          4 | 赵六   | 97.0 |    67.0 |    85.0 |
|          1 | 张三   | 89.0 |    78.0 |    99.0 |
|          1 | 张三   | 89.0 |    78.0 |    45.0 |
|          1 | 张三   | 89.0 |    78.0 |    45.0 |
|          3 | 王五   | 89.0 |    88.0 |    93.0 |
|          5 | 钱七   | 78.0 |    NULL |    97.0 |
+------------+--------+------+---------+---------+

2，条件查询

语法：

select (列名) from 表名 where 条件；

运算符	说明
>,>=,<,<=
=	等于，但是null=null的结果为null
<=>	等于，null=null为1，true
!=,<>	一个是正常的，第二个为null设计的不等于
between a and b	在[a,b]之间的数
in(a)	只要数包含在a即可
is null
is not null
like	模糊匹配  %代表有多个或者0个字符，_表示必须有一个字符。
and	并且
or	或者
not	非

我们来几个小练习。 

1，查询语文成绩小于80的学生

 select * from student where chinese<=80;
+------------+--------+------+---------+---------+
| student_id | name   | math | chinese | english |
+------------+--------+------+---------+---------+
|          1 | 张三   | 89.0 |    78.0 |    45.0 |
|          2 | 李四   | 99.0 |    56.0 |    74.0 |
|          4 | 赵六   | 97.0 |    67.0 |    85.0 |
|          1 | 张三   | 89.0 |    78.0 |    45.0 |
|          1 | 张三   | 89.0 |    78.0 |    99.0 |
+------------+--------+------+---------+---------+

2，查询名字为赵六的数学成绩

mysql> select math from student where name = '赵六';
+------+
| math |
+------+
| 97.0 |
+------+

3，查询语文成绩为空的学生编号

select student_id from student where chinese is null;
+------------+
| student_id |
+------------+
|          5 |
+------------+

4，查询数学成绩不等于语文成绩的学生姓名

select name from student where math != chinese;
+--------+
| name   |
+--------+
| 张三   |
| 李四   |
| 王五   |
| 赵六   |
| 张三   |
| 张三   |
+--------+

5，查询数学成绩在60到80之间的学生姓名

select name from student where math between 60 and 80;
+--------+
| name   |
+--------+
| 钱七   |
+--------+

6，查询语文成绩包含（98,78,45）的学生姓名

select name from student where chinese in (98,78,45);
+--------+
| name   |
+--------+
| 张三   |
| 张三   |
| 张三   |
+--------+

7，查询姓王的学生

select * from student where name like '王%';
+------------+--------+------+---------+---------+
| student_id | name   | math | chinese | english |
+------------+--------+------+---------+---------+
|          3 | 王五   | 89.0 |    88.0 |    93.0 |
+------------+--------+------+---------+---------+

这里用%的时候他可以是三个名字的也可以是两个名字的但是用’_‘的话就只能是两个名字的。

8，查询王某某必须是三个名字

select name from student where name like '王__';
Empty set (0.00 sec)

9，查询最后名字为七的学生

select * from student where name like '%七';
+------------+--------+------+---------+---------+
| student_id | name   | math | chinese | english |
+------------+--------+------+---------+---------+
|          5 | 钱七   | 78.0 |    NULL |    97.0 |
+------------+--------+------+---------+---------+
1 row in set (0.00 sec)

3，分页查询

真实的数据库是很大的，我们在使用查询语句是非常的危险的，我们查询一条语句可能会产生巨大的磁盘开销，我们要加以限制。

语法：

select （列名）from 表名 [条件] limit  n；

select （列名）from 表名 [条件] limit  a，b；

select （列名）from 表名 [条件] limit  w offset t；

我用不同的字母写了，但是他的字母其实是一样的，但是我感觉不好区分。 

select （列名）from 表名 [条件] limit  n； 

用法是查询n条数据，我们来查2个学生的成绩；

select * from student limit 2;
+------------+--------+------+---------+---------+
| student_id | name   | math | chinese | english |
+------------+--------+------+---------+---------+
|          1 | 张三   | 89.0 |    78.0 |    45.0 |
|          2 | 李四   | 99.0 |    56.0 |    74.0 |
+------------+--------+------+---------+---------+

 select （列名）from 表名 [条件] limit  a，b；

用法是跳过a条数据，查询b条数据

我们跳过张三和李四查询2条记录

select * from student limit 2,2;
+------------+--------+------+---------+---------+
| student_id | name   | math | chinese | english |
+------------+--------+------+---------+---------+
|          3 | 王五   | 89.0 |    88.0 |    93.0 |
|          4 | 赵六   | 97.0 |    67.0 |    85.0 |
+------------+--------+------+---------+---------+

 select （列名）from 表名 [条件] limit  w offset t；

这个就是到过来，跳过t条数据，查询w条数据。

select * from student limit 2 offset 2;
+------------+--------+------+---------+---------+
| student_id | name   | math | chinese | english |
+------------+--------+------+---------+---------+
|          3 | 王五   | 89.0 |    88.0 |    93.0 |
|          4 | 赵六   | 97.0 |    67.0 |    85.0 |
+------------+--------+------+---------+---------+

3，修改

语法：

update 表名 set 列名 = 新 +[条件]

同样，修改也是挺危险的操作，如果不加限制条件，我们可能一下就将所有用户的数据就修改成一个，所以在我们工作中，我们一般是又加了一个字段来标记这个字段是否被删除了。

这个不难，我们来几个例子就行

-- 将张三同学的数学成绩变更为 80 分

 update student set math = 80 where name = '张三';
Query OK, 3 rows affected (0.05 sec)
Rows matched: 3  Changed: 3  Warnings: 0

select * from student;
+------------+--------+------+---------+---------+
| student_id | name   | math | chinese | english |
+------------+--------+------+---------+---------+
|          1 | 张三   | 80.0 |    78.0 |    45.0 |
|          2 | 李四   | 99.0 |    56.0 |    74.0 |
|          3 | 王五   | 89.0 |    88.0 |    93.0 |
|          4 | 赵六   | 97.0 |    67.0 |    85.0 |
|          5 | 钱七   | 78.0 |    NULL |    97.0 |
|          1 | 张三   | 80.0 |    78.0 |    45.0 |
|          1 | 张三   | 80.0 |    78.0 |    99.0 |
+------------+--------+------+---------+---------+

 

-- 将李四同学的数学成绩变更为 60 分，语文成绩变更为 70 分  

update student set math = 60,chinese = 70 where name = '李四';
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

select * from student;
+------------+--------+------+---------+---------+
| student_id | name   | math | chinese | english |
+------------+--------+------+---------+---------+
|          1 | 张三   | 80.0 |    78.0 |    45.0 |
|          2 | 李四   | 60.0 |    70.0 |    74.0 |
|          3 | 王五   | 89.0 |    88.0 |    93.0 |
|          4 | 赵六   | 97.0 |    67.0 |    85.0 |
|          5 | 钱七   | 78.0 |    NULL |    97.0 |
|          1 | 张三   | 80.0 |    78.0 |    45.0 |
|          1 | 张三   | 80.0 |    78.0 |    99.0 |
+------------+--------+------+---------+---------+

-- 将总成绩倒数前三的 3 位同学的数学成绩减去 30 分

 update student set math = math - 30 order by math + chinese + english asc limit 3;
Query OK, 3 rows affected (0.03 sec)
Rows matched: 3  Changed: 3  Warnings: 0

select * from student;
+------------+--------+------+---------+---------+
| student_id | name   | math | chinese | english |
+------------+--------+------+---------+---------+
|          1 | 张三   | 50.0 |    78.0 |    45.0 |
|          2 | 李四   | 60.0 |    70.0 |    74.0 |
|          3 | 王五   | 89.0 |    88.0 |    93.0 |
|          4 | 赵六   | 97.0 |    67.0 |    85.0 |
|          5 | 钱七   | 48.0 |    NULL |    97.0 |
|          1 | 张三   | 50.0 |    78.0 |    45.0 |
|          1 | 张三   | 80.0 |    78.0 |    99.0 |
+------------+--------+------+---------+---------+

 

-- 将所有同学的语文成绩更新为原来的 2分之一 倍

mysql> update student set chinese = chinese / 2;
Query OK, 6 rows affected (0.02 sec)
Rows matched: 7  Changed: 6  Warnings: 0mysql> select * from student;
+------------+--------+------+---------+---------+
| student_id | name   | math | chinese | english |
+------------+--------+------+---------+---------+
|          1 | 张三   | 50.0 |    39.0 |    45.0 |
|          2 | 李四   | 60.0 |    35.0 |    74.0 |
|          3 | 王五   | 89.0 |    44.0 |    93.0 |
|          4 | 赵六   | 97.0 |    33.5 |    85.0 |
|          5 | 钱七   | 48.0 |    NULL |    97.0 |
|          1 | 张三   | 50.0 |    39.0 |    45.0 |
|          1 | 张三   | 80.0 |    39.0 |    99.0 |
+------------+--------+------+---------+---------+

4，删除

语法：
delete from 表名  + 限制条件

删除也是挺危险的，大家一会儿进行完练习就可以把它忘了。

-- 删除两个数学成绩为50张三同学的数学成绩

 delete from student where name = '张三' and math = 50;
Query OK, 2 rows affected (0.03 sec)mysql> select * from student;
+------------+--------+------+---------+---------+
| student_id | name   | math | chinese | english |
+------------+--------+------+---------+---------+
|          2 | 李四   | 60.0 |    35.0 |    74.0 |
|          3 | 王五   | 89.0 |    44.0 |    93.0 |
|          4 | 赵六   | 97.0 |    33.5 |    85.0 |
|          5 | 钱七   | 48.0 |    NULL |    97.0 |
|          1 | 张三   | 80.0 |    39.0 |    99.0 |
+------------+--------+------+---------+---------+

-- 删除整张表数据-- 准备测试表

mysql> delete from student;
Query OK, 5 rows affected (0.03 sec)

 

select * from student;
Empty set (0.00 sec)

今天我们先到这里，火速更新下期。