MySQL2——基本操作

主要介绍MySQL的实际操作。

一：操作数据库的SQL

1.从cmd进入mysql

mysql -uroot -p密码 

2.创建数据库

create database 数据库名;

 注意：一定要以分号结尾！这里的create，database都是数据库中的关键字。

3.查看数据库

show databases;

 注意：databases一定要使用复数形式；

 4.选中数据库

use 数据库名;

 5.删除数据库

drop database 数据库名;

         注意：删除数据库非常危险！！！！！！！！程序猿和代码，有一个能跑就行。尤其是在公司的生产环境下，千万不要轻易尝试！风险极高！

         如果不小心把开发环境/测试环境中的数据库删除，影响有限；但如果把生产环境中的数据库删掉，那就废了，很可能导致用户投诉，用户流失，甚至造成更加直接的经济损失，严重的还会吃到官司，很“刑”，日子越来越有“判头”了。

        既然删除数据库危害如此之大，有什么方法可以降低这种风险呢？

1. 权限：只授予少数人修改数据库的权限；由此衍生出一个岗位，叫数据库管理员（DBA）；
2. 备份：把数据拷贝一份，存到别的地方去；
3. 硬盘数据恢复：如果是极端情况，删了库，也没有备份，还有一招，就是硬盘数据恢复。

        关于硬盘数据恢复：

操作系统为了方便进行管理,把整个硬盘分成了若干个"盘块" . 每个盘块都可以保存一定的数据 . 每个文件,实际上可能是由一个或者多个盘块上面的数据来构成的 .

当操作系统删除文件的时候,为了提高删除动作的效率,在删除的时候并不是真的把硬盘上之前保存的数据擦除掉,而只是把该文件对应的盘块,标记成"无效状态" ---- > "逻辑删除" . 因此一旦出现误删库的情况,就要尽快让主机断电 !! 就避免操作系统把这些被标记成无效的盘块给分配出去!!!

这就好像我收拾房间,收拾了一些垃圾.把这些垃圾直接一股脑丢到楼下垃圾桶里. 万一过了一会，啊，,我的身份证好像在里面呢!!!赶紧去楼下，翻垃圾桶,大概率还是能翻回来的!!

注意!!!上述的操作,不一定能100%都恢复回来,大概率是只能恢复一部分!!

二：SQL数据类型

2.1数值类型

需要记忆的数据：整数类型的范围 :

2.2字符串类型

示例：
VARCHAR(50)，表示这个字段最多存50个字符，一个字节固定8bit，而一个字符长度则取决于具体的字符编码~~

java中默认使用的是unicode编码。 

2.3日期类型

 三：操作表的SQL

1.创建表

create table 表名(列名 类型,列名 类型......);

注意：

• 同一个数据库中，不能有两个表名字相同；
• 创建表时，表名和列名，不能和数据库的关键字冲突；如果你就是要用，那必须用反引号把表名引起来，反引号（`）就是键盘左上角的那个键；
• 有时候，建表语句比较复杂，可以分成多行来写；
• 由于在mysql命令行直接编写，无法退回到上一步，所以很不方便，可以使用其他编辑器进行书写，并粘贴到命令行的黑框框里面。

2.显示有哪些表

show tables;

3.查看表结构

desc 表名;

 desc—>describe,查看表结构，就是描述一下这个表。

 注意：

Field就是字段，每一列都是一个字段，表示这个表有几列；

int(11)是指打印数字时显示数字的最大宽度是11位，只影响在客户端中的显示，不影响存储和计算。

4.删除表

drop 表名;

注意：删除表的危害，比删库有过之而无不及！

5.插入信息

insert into 表名 values(值,值...);

insert into 表名 values(值,值),(值,值)(值,值),(值,值).........;

insert into 表名 (列名) values(值);

insert into 表名 (列名,列名) values(值,值);

        可以插入单个数据，也可以同时插入多个数据；可以指定某一列进行插入，此时其余列为默认值；也可以指定某几列插入，其余列为默认值。

        如何才能插入中文数据呢？ 

         什么是配置文件？比如我安装了一个游戏，叫菜鸡荣耀，发现玩的时候有点卡！这不废了吗，我就会在设置里面进行一系列调整，例如画质设置为低，把分辨率从2K调到1080P，关闭一些特效等等，这些能够进行调整的设置项，被称为“配置”。像咱们玩的一些游戏，在修改配置项的时候，都可以直接在游戏界面进行修改，这样做主要是让普通玩家用起来很方便。

        在编程圈子里，用到的很多软件程序，也需提供很多的配置项，由于这些软件是程序员在使用，所以开发这些软件的大佬就没有做方便修改的界面，而是直接把配置项放到了一个单独的文件里，按照特定格式来组织。

        程序猿想要修改配置，就要修改这个配置文件。

关于配置文件：

• 配置文件在哪？不好找，得是程序猿才能方便找到；
• 配置文件的格式有规律，要是不懂格式，容易出错！
• 掌握原生的修改配置的方式是必要的。

如何修改MySQL配置文件？

1.先确认当前数据库的字符集，使用命令：

show variables like 'character%'; 

         现在我这里相关字符集已经都是utf-8了，所以可以插入中文；如果不是，继续进行下一步，修改配置文件；

2.找到配置文件，my.ini；

 

   3.修改配置文件；

 4.额外工作；（容易被忽视！！！）

          以上是修改配置文件的全部内容！

---

6.查询操作

查询全部列：select * from 表名;

查询某一列：select 列名 from 表名;

查询某几列：select 列名，列名 from 表名;

带表达式的查询：select 表达式 from 表名;

带表达式+别名的查询：select 表达式 as 别名 from 表名;

去除重复项的查询1：select distinct 列名 from 表名;

去除重复项的查询2：select distinct (列名,列名.....) from 表名;

简单示例： 

7.排序查询

select 列名 from 表名 order by 列名;

select 列名 from 表名 order by 列名 desc;

这里的desc是descending，是下降的意思；

 注意：

1.order by可以针对带有别名的表达式进行排序；

2.order by进行排序时，还可以指定多个列进行排序，如果第一列不分胜负，再按照第二列进行排序。

8.条件查询

select * from 表名 where 条件; 

 注意：

1.关于NULL及模糊匹配 

2.AND优先级高于OR；

3.模糊匹配说明

• %x 匹配以x结尾的数据
• x% 匹配以x开头的数据
• %x% 匹配含有x的数据
• _ 匹配一个任意字符
• __ 匹配两个任意字符

 9.分页查询（只取出查询结果中的一部分）

select 列名 from 表名 limit N offset M;

select 列名 from 表名 limit M,N;

从第M条开始查询，最多返回N条数据。

注意：后序如果要针对生产环境的数据库进行查询，最好都加上limit，防止出现意外导致服务器挂了。

10.修改UPDATE

update 表名 set 列名 = 值，列名 = 值........where 字句;

注意：如果不加筛选条件，默认对所有行进行修改。

 11.删除DELECT

delete from 表名 where 条件;

12.添加新列

ALTER TABLE 表名 ADD 新字段名 数据类型[约束条件]; 

---

插入：create

查询：retrieve

修改：update

删除：delete

常见叫法：CRUD，即增删改查！在公司刚进入实习期，一般就干这个。

四：查找进阶

4.1约束类型

        约束就是，数据库可以让程序员定义一些对数据的限制规则，数据库会在插入/修改数据的时候按照这些规则对数据进行校验。如果校验不通过，就直接报错！

1.NOT NULL

初始情况下，一个表允许为NULL；

加上not null约束后，表明该值不能为空！ 

2.DEFAULT

初始的默认值是NULL；

 使用default设置默认值后：

 3.PRIMARY KEY

        主要介绍自增主键（auto_increment）。因为主键的值不能为空且具有唯一性，所以MySQL为了方便大家书写主键，内置了一个功能，即“自增主键”，帮助我们自动生成主键的值。

         注意：如果数据库是分布式部署的，这时自增主键就会带来问题。

        北冥有鱼，其名为鲲，鲲之大，一锅炖不下；化而为鸟，其名为鹏，鹏之大，需要两个烧烤架。分布式，就是数据量太大，一台机器存不下，就要使用多台机器来存。

 MySQL生成自增主键的时候可以保证在自己这个节点上 , 生成的id是唯一的 , 但是无法保证这个id在其他的节点上也是唯一的!!

一个典型的解决方案是 , 在生成id的时候 , 让这些节点相互协商一下 , 彼此了解对方的情况之后 , 就能生成唯一的id了 . 代价有点大 ~

还有一个办法 , 就是使用"随机生成"的思路 , 唯一id = 时间戳(ms) + 机房编号/主机编号 + 随机因子 , 此处的"+"是字符串拼接 .

4.FOREIGN KEY

        外键，用于关联其他表的属性或唯一值。先把所有键都定义完，然后逗号，再写外键约束。

此时， class为父表，student为子表，在父表为空的情况下，不能对子表进行插入！因为出现了一种值的依赖关系 .

正确情况为：

 关于查询效率：在外键约束下,每次你插入/修改操作,都会先触发在父表中的查询.父表中存在才能插入/修改成功 . 否则就会失败!!每次插入都要先查询,是否就会非常拖慢执行效率?确实如此!!!但是也没有特别拖慢~~

如果查询操作触发了遍历表,低效.
如果查询操作能够触发索引,相对快不少.

建立外键约束的时候,MySQL就要求,引用的父表的列,必须是主键或者是UNIQUE自带索引 , 查询速度就会快一些 !

 同时，因为外键的约束，在父表中夜不能随意删除元素，如下所示：

总结：
父表和子表互相限制！

父表对于子表的限制是不能随意插入/修改；

子表对于父表的限制是不能随意删除/修改。 

外键约束思考题：

Q : 假设某商品表和订单表建立"外键"关系 , 该商品下架 , 在外键约束下 , 如何把商品表中的记录进行删除 , 同时又不影响订单表中的记录呢 ?

A : 我们可以进行逻辑上的删除 , 操作系统删除磁盘上的数据 , 也是逻辑上的删除 , 本质上就是把对应的盘块给标记成无效 , 并不是真的把盘块上的数据给清空了 . 例如 :

商品表(goodId, name, price, ok)

约定 : 当ok=1时表示商品在线 , 当ok=0时表示商品下线 . 需要下线商品的时候 , 只需要把ok改为0 , 并不影响外键约束 , 需要展示商品时 , 只需基于字段ok进行筛选 .

4.2表的设计

        Q：面试官：你的项目里的数据库是如何设计的？

        A：我的数据库里有几张表，每张表分别是干啥的，以及表里有哪些字段？我们要明确需求中的“实体”，一般来说，每个实体都会分配一个表来表示。

        实体之间的关系：一对一 , 一对多 , 多对多 , 没关系 .

 

4.3 SQL当中的复杂查询

1.把查询结果作为新增的数据

insert into 表名1 select 列名 from 表名2;

要求从表2查询出来的结果的列数和类型和表1匹配。

 2.聚合查询

        聚合函数（行和行之间进行运算）

举例：

1.用count统计收集的分数有多少个？ 

 2.用sum统计所有人的数学分数之和。

GROUP BY子句

在进行聚合查询的时候 , 也能指定条件筛选 :

1.在聚合之前进行筛选 , 针对筛选后的结果 , 再聚合 , where子句 ;

2.在聚合之后进行筛选 , having子句 .

 

3.联合查询  

分为内连接和外连接。

1.内连接：

        什么是内连接？只有两张表中一一对应的数据才能被查询出来。例如 :

多表查询的一般步骤:

1. 先根据需求理清楚说想要的数据都在哪些表中.
2. [核心操作]先针对多个表进行笛卡尔积.
3. 根据连接条件,筛选出合法数据,过滤掉非法数据.
4. 进一步增加条件,根据需求做更精细的筛选.
5. 去掉不必要的列,保留最关注的信息.

 2.外连接：

        外连接分为左外连接和右外连接。如果联合查询，左侧的表完全显示我们就说是左外连接；右侧的表完全显示我们就说是右外连接。其SQL语句如下所示：

-- 左外连接，表1完全显示
select 列名 from 表1 left join 表2 on 连接条件;
-- 右外连接，表2完全显示
select 列名 from 表1 right join 表2 on 连接条件;

 

 3.自连接

        还有一种比较特殊的多表查询，叫自连接。MySQL只支持列与列之间的查询，不支持行与行之间的查询的。通过自连接，可以把行与行之间的查询转化为列与列之间的查询。

        注意：进行自连接时，必须给表起别名。

栗子: 哪位同学的课程3比课程1成绩高?

 Step1：进行自连接

         此时发现course_id有两列了，但还是不方便比较。所以需要使用course_id进一步进行划分。

 Step2：加入筛选条件ocurse_id

Step3：筛选需要的列；

得到最终结论，学号为1和3的同学，课程3比课程1成绩高。

4.子查询

        顾名思义，就是把一个查询嵌套另外一个查询。可以使用in或者‘=’。举个栗子：查询‘不想毕业’同学的同班同学：

5.合并查询

select 子句 union select 子句;

---

本课内容结束！