数据库基础+增删查改初阶

数据库基础+增删查改初阶

一。数据库操作

1.概念：

一个mysql服务器上有很多的表，把有关系的表放在一起就构成了一个数据集合，此时称为“数据库”，一个mysql1服务器上可以有多个这样的数据库

2.创建数据库：

create database 数据名；

注：sql关键字大小写都可以

数据库在创建时不能重复，因此要在创建时加上一个修饰

create database if not exists 数据名

咱们一般在写数据库的时候需要中文，但是mysql默认是拉丁文，不支持中文，所以在创建数据库的时候，要手动指定编码为中文编码（GBK或UTF8）因此可以写成：

create database 数据库名 charset utf8;

此处的utf8是不完全的，有写表情是无法识别的，因此如果没有特殊要求，建议写成utf8mb4

3.查看数据库：

列出mysql服务器中都有哪些数据库

show databases；

注：以sql开头的语句，都要在写完一句话的时候加上分号，当然mysql客户端允许输入sql的时候换行

4.选中数据库

use 数据库名

5.删除数据库：

drop database 数据名

删除不仅仅是删除了数据库，而且删除了数据库中所有的表和表中的数据

6.补充：

数据库可分为线上数据库和线下数据库，线上数据库存储的是用户真实的信息，线下数据库是开发测试的一些假的数据

mysql服务器必须保证24小时不间断运行，如果挂了，就会造成极大的影响，因此有了报警系统，会自动的监视着mysql服务器的运行状态，一旦mysql服务器停止工作，报警系统会第一时间把消息以电话，短信，邮箱等形式传递给相关负责人

二。数据表操作

注：针对数据表中的操作，前提是选中数据库

1.创建表：

create table 表名（列名 类型，列名 类型...）；

注意：一定是类型在列名的后面

例子：

create table student(id int, name archer(10));

2.查看所有表：

show tables;

3.查看指定表的结构：

desc 表名

4.删除表：

drop table 表名

补充：有时候删表比删库更严重，因为删库程序会在第一时间报错，而删表，程序不会在第一时间报错，可能会以这个错误的数据运行很长时间

三。增删改差（CRUD）

1.新增：

（1）法一：直接插入

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

此处的值必须与创建表时的数据类型一一对应

（2）法二：指定列插入

insert into 表名（列名，列名...）values （值,值....);

此处放入的值就要与列名所对应

（3）法三：一次插入多行：

Insert into 表名 values(值，值...) , (值，值...) ....

注意：一次插入多行的速度是要大于一行一行插入的

2.查询：

（1）全列查询：

select*from 表名

注意：select*是一个危险的操作，mysql是一个“客户端—服务器”结构程序，如果当前这个表的数据非常多，就会产生几个问题：1.在查询的时候会读取整个硬盘，会把整个硬盘的IO都给跑满了，此时在想访问硬盘就会非常的慢。2.操作网络，可能把网络的带宽跑满，此时其他网络访问服务器就会变得很慢

但是目前初学阶段，是可以使用这样的操作的，当以后进入公司企业工作的生产环境中，这种操作一定要慎重使用

补充：

1.办公环境：入职公司，公司会分配一个电脑给员工

2.开发环境：有的开发环境是办公环境，也有的是开发环境，是需要专门的服务器的

3.测试环境：测试工程师针对程序测试的时候，搭配的环境

4.生产环境：一组服务器，这个服务器外面的用户可以直接访问的服务器，如果生产环境的服务器出现问题，用户会得到反馈

第1，2，3都是线下环境，第4个叫做线上环境

（2） 指定列查询：

select 列名,列名... from 表名

（3）查询字段为表达式：

例子：

select name，point+10 from 列名;

这种操作不会修改数据库当中的原始数据，只会暂时的进行更改

（4）别名：

格式：

select 表达式 as 别名 from 表名；

例子：

select name，english+chinese+math as sum from student;

这里的sum其实就是english+chinese+math的别名。

（5）去重：

distinct去修饰某个列，或多个列

值相同的行只会保留一个

select distinct 列名 from 表名;

这里的列名可以是多个，但是最好根据需求来确定写什么列，因为如果写了多个列，那么必须保证这些列的元素都相同才可以去重

（6）查询进行排序：

规则：1.要针对哪个列进行比较 2. 排序时是升序还是降序

select 列名 from 表名 order by 列名 asc/desc

asc表示升序，desc表示降序

如果不写desc或者asc则自动默认为升序排序

这里的排序是临时数据，他不会影响原有数据在数据库的存储顺序

注意：如果排序的时候遇到了null，则视为null比任何数据都要小，升序时排在最上面，降序时排在最下面

同样，可以对多个字段进行排序，排序优先级随书写顺序

例子1：

查询同学的各门成绩，一次按数学降序，英语升序，语文升序的方式显示

select name, math,english,chinese from exam order by math desc, english,chinese;

例子2:

select*from exam order by math desc, chinese desc;

先以数学成绩排序，当遇到数学成绩相同的，再按照语文成绩进行排序

注意：null参与各种运算的结果还是null

（7）条件查询：

select 列名 from 表名 where 条件;

在执行条件查询的时候会遍历这个表的每一行记录，把每一行的数据分别代入这个条件中，如果成立就会把结果放入结果集合中，如果不成立就pass

注意：

(1).where 条件可以用于表达式，但是不能使用别名

(2).and的优先级大于or，在同时使用时，要用小括号（）包裹优先执行的部分

无论有几个列，都可以使用上述方法进查询

基本查询：

(1).查询英语成绩小于60分的同学

select name,english from exam where english<60;

(2).查询英语成绩高于语文成绩的同学：

select name,english,chinese from exam where english>chinese;

(3).查询语数外总分低于200的同学：

select name,english+math+chinese from exam where english+math+chinese<200;

and与or：

查询语文大于80分，英语大于100分的同学

select name chinese,english from exam where chinese>80 and english>100;

范围查询：

(1)between…and…

查询语文成绩在80到90分的同学的语文成绩：

select name ,chinese from exam where chinese between 80 and 90;

(2)in

查询数据时58或59或60或61的同学的数学成绩

select name, math from exam where math in(58,59,60,61);

模糊查询：LIKE

select name from exam where name LIKE '孙%'；

注：%表示任意多个字符

select name from exam where name LIKE'孙_‘；

注：_表示任意一个字符

（8）分页查询：LIMIT

limit可以限制这次查询最多查询出几个结果，适用条件就是有的时候数据非常的多，一次展示出来会影响效率，不方便用户观看

select *from 表名 limit 数字;

这个数字就是在限制打印的个数，这段代码的意思就是只打印这个表的数字之前的数据

select *from 表名 limit 数字1 offset 数字2;

这串代码表示，只会打印数字1到数字2之间到数据

例子：

select *from exam limit 3 offset 3；

这里只会打印表中的4，5，6行数据

注意：limit一般放在一句话的最后

3.修改：

update 表名 set 列名=值 where 条件;

修改是可以直接修改多个值的

update 表名 set 列名=值，列名=值，......where 条件;

例子：将总成绩倒数前三的3位同学的数学成绩减30分

update exam set math=math-30 order by chinese+math+English limit 3;

4.删除：

delete from 表名 where 条件 /order by / limit;

例子：

delete from student where name=’xiao ming‘；

这样就删除了student这张表中小明这一行的所有数据

删表：

delete from 表名;

注意：此处的delete from 和drop table不同。

delete from 是删除表中的所有数据，但是不删表

drop table是即删除表中数据，又把表删除

四。数据库约束

1.NULL约束

指某个列不能存放null值

create table student(id int not null, name varchar(10),email int);

2.UNIQUE约束

被修饰的列是不能重复的

create table student(id int unique, name varchar(10),email int);

3.default约束：

规定没有给列赋值时的默认值

create table student(id int, name varchar(10) default ‘unknown’,email int);

这里就是对name设置了默认值，如果当插入数据的时候，没有插入这一列，则默认设为unknown

4.primary key主键：

确保某列有唯一的标识，简单点来说就是一行记录的身份标识

用法其实就是not null和unique的结合

一个表中只能有一个主键

create table student(id int primary key, name varchar(10) ,email int);

对应整数主键，通常搭配auto_increment来使用，插入数据对应字段不给值时，使用最大值+1

create table student(id int primary key auto_increment, name varchar(10) );

例子：

insert into student values(null,'zhangsan');

假设这张表中没有插入过数据，则默认插入这条数据中的null为1；

如果再次插入上面一串代码，则id 就变为了1+1=2，第一个1是目前的最大值，第二个1是指auto_increment的特性（最大值+1）

写做空，其实是交给数据库服务器自动分配

自动分配也存在局限性，如果当mysql服务器是没有问题的，如果是有多个服务器，这是自动分配就不行了

5.foreign key 外键:

作用：两个表之间的关联关系

例子：

class（classid，name ）100。    TT1114101      TT1964102      TT2103
student（id ，  name，  classid）1    zhangsan   1002    lisi       1013    wangwu。   1024    zhaoliu    200

这里的student表中的zhaoliu的classid是不合法的，因为classid是根据class表中的classid来定义的，如果class表中没有，则不合法，所以叫这里的class表为父表，student为子表

写法：

create table student(id int primary key,name archer(10),classId int, foreignn key(classId) references class(classId));           

foreign key要加在最后的部分，foreign key 后的第一个括号要写被约束的列，后面references 再加上约束别人的表（约束列）

注意：如果被外键修饰，并且已经插入数据，则不能直接删除父表，应先删除子表再删除父表

6.扩展：

有一个生活中常见的场景，就是在逛某宝或某多多的时候经常会发现一个商品卖完了，但是在上面外键的部分讲到了，如果要删除被外键修饰的列的数据时，需要先把所有已经插入的数据全部删除，才可以删除被外键修饰的列，但是为什么这些购物网站可以成功的把商品下架，并且对已购买的买家依然发货？

其实这其中就使用了一个很巧妙的方法：逻辑删除

先给商品表加上一个单独的列，表示是否在线（不在线，就相当于下架了）

商品表（goodsid，name，price…，isOK）

这里的isOK就是我们新建的单独的列，如果值为1，表示商品在线，如果值为0，则表示下架，如果商品需要下架只需要把isOK从1改为0

其实电脑上删除文件也是使用了逻辑删除的方法，如果只是将文件放入垃圾箱，并且清理回收站是无法做到完全删除的，硬盘中的数据完全删除是需要时间的，需要后续有文件把这块标价无效的空间重复利用，才会真正的消失，当然有一种非常快的方法可以把硬盘上的数据完全删除，那就是使用物理删除，就是通过把硬盘砸了等物理方法销毁数据