数据库操作指南-CSDN博客

本文链接：https://blog.csdn.net/qq_22209929/article/details/84401589

数据的完整性:

数据类型:
★整数：int , bit
★小数：decimal
★字符串:varchar , char
★日期时间:data , time , datetime

★枚举类型（enum）
数据类型特殊说明：
★浮点数，如decimal(5,2)代表共存5位数，小占2位
★varchar(3)填充"ab"时就只会填充2个“ab”
★char(3)填充“ab”时会填充“ab ”3个，包括ab和空格
★字符串text表示储存大文本，当字符大于4000时推荐使用
★对于图片，视频，音频等文件，不存储在数据库中，而是上传到某个服务器中，然后表中存储的时这个文件的保存路径
约束：
★主键primary key ：物理上的存储的顺序
★非空not null：此字段允许填写空值
★唯一unique：此字段的值不允许重复
★默认default：当不填写此值时会使用默认值，如果填写时以填写的为准
★外键foreign key：对关系字段进行约束，当为关系字段填写值时，会到关联表中查询此值是否存在，如果存在则填写成功，如果不存在则填写失败并抛出异常（说明：虽然外键约束可以保证数据的有效性，但是在进行数据的crud（增，删，查，改）时，都会减低数据库的性能，所以不推荐使用，那么数据的有效性如何保证呢？答：可以在逻辑层进行控制)

数据库的操作：

★数据库的链接:

mysql -uroot -p

mysql -uroot -pa1!

★退出数据库：

exit/quit/ctrl+d

★查看所有数据库：

show databases;

★查询当前数据库时间：

select now();

★查询数据库当前版本：

select version();

★创建数据库：

create database python_01; 没有指定数据库类型即默认是latin（查看创建的数据库可以查看创建类型），不能储存中文

create database python_01new charset=utf8; 指定类型后，即是utf8类型

★查看创建的数据库：

show create database python_01;

★删除数据库：

drop database python_01;

★使用数据库：

use python_01;

★查询当前使用的数据库：

select database();

数据表的操作：

★查看当前数据库所有的表：

show tables;

★创建表

create table xxxx(id int primary key not null, name varchar(30) ); create table 数据表名字（字段类型约束，字段类型约束...）

★查看表的结构

desc students;

★修改表-添加字段：

alter table students add birthday datetime;

★修改表-修改字段：

1，alter table students modify birthday date; 不重命名 ---alter table 表名 modify 字段类型及约束

2，alter table students change birthday birth date; 重命名版 ---alter table 表名 change 旧字段新字段类型及约束

★修改表-删除字段：

alter table students drop birth ;

★删除表

drop table students;

数据的增删改查：

★增：

1，全列插入主键字段可以用0 null default 来占位

insert into students values(null,"老一"，21,180.22,1,2,2000-05-21)；对应着表的格式进行插入默认值 123对应着相应的选项

2，部分插入

insert into students(name, gender) values ("大乔" ,2);

3，多行插入

insert into students(name, gender) values ("大乔", 2), ("小乔", 2);

insert into students values(null,"老一"，21,180.22,1,2,2000-05-21), (null,"老二"，21,180.22,1,2,2000-05-21);

★改：

updata students set gender= 1, age= 21 where id= 3; 修改id3的性别为男，年龄更改为21

★删：

1,物理删除

delete from students; 删除整个表（慎重！！！）

delete from students where id = 3; 删除id=3的字段

2，逻辑删除

给表添加一个is_delete字段 bit 类型通过01来判断是否删除

alter table students add is_delete bit default 0; 默认为0即没删除

updata students set is_delete= 1 where id= 6; 修改为1后，则代表删除

★查（重中之重！）：

1，查看所有

select * from students ;

2，指定条件查询

select * from students where id>6; 根据条件查询

3，查询指定的列

select name,gender from students;

select name as 姓名, gender as 性别 from students; 用as来为所查询的列更改别名

4，字段的顺序

select gender as 性别, name as 姓名 from students; 把查询的顺序调换一下就可以了

5，查询字段后去掉重复的

select distinct gender from students;

6，条件查询：

算术运算符 = ，！=，>，<，<=，>=

select * from students where id=5; 查询字符ID为5的记录

逻辑运算符 and，or，not

select * from students where id>2 and id<6; 查询id>2且id<6的记录

select * from students where not id>2; 查询id<=2的记录

select * from students where id<2 or id>5; 查询id<2 或id>5的记录

模糊查询：

like _匹配一个字符，%替换多个字符

select name from students where name like "__"; 查询2个字符的名字

select name from students where name like "%小%"; 查询字符中间包含小字的名字

select name from students where name like "__%" 查询至少二个字符的名字

rlike 正则通过正则表达式来查询

select name from students where name rlike "^周.*"; 查询以周开头的名字

select name from students where name rlike "^周.*伦$"; 查询以周开头，以伦结尾的名字

7，范围查询：

查询非连续 in

select age from students where age in (18,20,22); 查询年龄为18，20，22的信息

连续范围 between.....and.....

select age from students where age between age>18 and age<30; 查询年龄在18-30范围的信息

select age from students where age not between age>18 and age<30; 查询年龄不在18-30范围的信息

非空：is not null 空：is null

select age from students where age is null; 查询年龄记录为null的信息

8，排序：

order by 字段 asc 从小到大（默认） desc从大到小

select * from students where age between age>18 and age<30 and gender=2 order byn age; 从小到大排序查询到的年龄18-30的女性

select * from student where age between age>18 and age>30 and gender=1 order by age desc; 从大到小排序查询到的年龄为18-30的男性

多个条件的排序

select * from student where age between age>18 and age>30 and gender=1 order by age desc,id desc; 从大到小排序查询到的年龄为18-30的男性，如果有二个大小一样的，再按照id从大到小排序

9，聚合和分组：

聚合求搜索出来的总数count，总和sum，最大值max，最小值min，平均值avg，四舍五入round

select count(*) from students where age>18; 查询年龄大于18的个数

select sum(age) from students; 查询学生表的所有人年龄总和

select sum(age)/count(*) from students; 查询学生表的所有人年龄的平均值

select round(avg(age),2) from students; 将查询到学生表的年龄平均值保留二位小数

分组分组需要和聚合一起用才有意义分组group by group_concat(name) 查看分组后有哪几个人 having 聚合结果进行条件判断(对查询出来的表进行再查询)，区分与where是对表进行判断

select gender from students group by gender; 将gender分组显示跟去重复distinct 差不多，必须要和聚合一起用才有意义

select gender group_concat(name) from students group by gender; 分组，然后查看组里面有谁

select gender count(*) from students where gender=1 group by gender; 将性别分组，并且统计有几个男性

select gender group_concat(name,age) from students where gender=1 group by gender 将性别分组，取男性分组，查看分组里面的姓名与年龄可以分开查询结果如group_concat(name,"",age)

select gender group_concat(name) from students group by gender having avg(age)>30; 根据性别分组查询平均年龄大于30的人

select gender group_concat(name) from student group by gender having count(*)>2; 查询各种性别人数大于2的信息

10，分页：

limit start,显示数量必须放于语句最后面！！！

select * from students limit 5; 只显示前面五个

select * from students limit 0,5; 显示第一个到第五个

select * from students order by age limit 5,5; 按照年龄从小到大排序显示第六个到第十个

11，链接查询：

inner join.....on select * from 表1 inner join 表2 on 表1.id = 表2.id; 将表1和表2连接起来，并且根据on的条件进行集合

select * from students as s inner join class as c on s.cls_id=c.id; 将学生表和班级表连接起来，并且让学生的班级ID对应班级表的ID取集合，并且显示出来，不对应的则不显示

select * from students as s left join class as c on s.cls_id=c.id; 左连接，根据表一连接表二，并显示表一的所有内容，如果在表2对应不到，则显示为null 右连接可以right join...on 也可以将左连接的表1表2对调

select * from students left join class on students.cls_id=class.id having class.id is null; 通过having对查询出来的表进行条件判断再查询

12，自关联：

比如说，省，市，区之间相关联，但是由于创建多个表存储不好，则将这些信息全部储存于一个表中，然后通过市的指定字符对应省的id，区的指定字符对应市的id，这样，就可以实现在一个表中关联起来

select province.atitle,city.atitle from areas as province inner join areas as city on province.aid = city.pid having province.atitle="广东省"; 将一个表区分为二个表，然后根据关联的信息来查询需要的信息

13，子查询-简单

在查询语句中嵌套另外一个查询

select * from students where height = (select max(height) from students); 查询最高身高，先执行子查询，再执行主查询

select * from students where pid = (select aid from areas where atitle="广东省")；用子查询，查询自关联的案例

数据库的设计：

需要满足三范式：

一：强调列的原子性，不能再分成其他列

二：一个表必须有一个主键，没有包含在主键中的列必须依赖于主键，而不能只依赖主键的一部分

三：非主键列必须依赖主键，不允许传递依赖，即非主键列A依赖非主键列B，非主键B依赖于主键的情况

E-R模型:

E为主体，R为关系

一对一，创建一个字段来关联

一对多，多对一，需要在多的那个表创建关联字段来关联一的主键值

多对多，需要新建一个聚合表来关联

拆表：

假如需要修改下表中的cate_name字段的笔记本，则需要一个一个的修改，为了方便，可以将此字段拆分为另一个表，关联起来

1，创建一个good_cates表来存

create table goods_cates(id int unsigned primary key auto_increment not null,name varchar(40) not null);

2，将goods表中的cate_name字段分组，然后存于goods_cates表中的name中

insert into goods_cates (name) select cate_name from goods group by cate_name;

3，将goods表中的cate_id字段里面的内容修改为goods_cates表中name对应的id

update goods as g inner join goods_cates as s on g.cate_name = s.name set g.cate_name = s.id

4，将goods中的cate_name字段修改类型跟goods_cates中的id字段一样的类型（必须要一样类型，防止对不上出问题）

alter table goods change cate_name cate_id int unsigned not null;

5，将goods表中的cate_id字段增加外键链接goods_cates表的id字段（做限制，防止在goods表中添加信息时cate_id中的信息不存在与goods_cates表中的id中）

alter table goods add foreign key (cate_id) references goods_cates(id);

6，尽量不要使用外键，很大程度的降低性能

删除外键操作：

show create table goods; 查看CONSTRAINT中的信息，按照这个信息删除外键

alter table goods drop foreign key goods_ibfk_1; 删除goods_cates的外键;

MYSQL的高级应用:

★视图：

对于复杂的查询，往往是要进行多个表关联查询得到的，但是因为数据库的需求等原因可能会发生改变，为了保证查询出来的数据和之前的相同，则需要在多个地方进行修改，维护起来非常麻烦，如果，可以将查询出来的结果，作为一个结果集，之后直接操作这个结果集来查询，就方便很多。

视图只是一张虚表，如果基本表发生了改变，则视图就会改变，不可以直接修改视图，，这样基本表没有变化

视图主要是为了方便操作，特别对于查询操作，减少复杂啊的SQL语句，增加可读性

创建视图：

create view 视图名称 as select语句;

查看视图：

show tables;

使用视图：

select * from 视图名称;

删除视图：

drop view 视图名称;

★事务：

事务就是一个操作序列，要么这些操作全部都执行，要么都不执行，不能分割，比如说，银行转账，A给B转100，则有银行先查询A账户是否有100，如果有，那么扣除100，接着，给B增加100,这样完成了整个操作，其中任何一个步骤出问题了，就会取消操作。

事务有四大特性：ACID

atomicity原子性（不能分割，即需全部执行或者全都不执行）

consistency一致性（数据库保持一致的状态，即需要二边都完成了操作，即才真正完成）

isolation隔离性（事务在执行的时候，对于其他事务是不可见的，如果有个事务在执行，那么另外一个事务会等待正在执行的事务完成，才会执行）

durability持续性（事务一旦完成提交，那么所做的修改就会永久的保存在数据库）

开始一个事务(表的引擎类型必须是innodb才能使用事务，这是mysql表的默认引擎)：

start transaction; 或者 begin;

完成事务：

commit;

回滚事务：

rollback;

★索引：

索引是一种特殊的文件（innoDB数据表的索引是表空间的一部分（即数据小的字段就不适用索引，只有大且需经常查询的字段需要用到索引）），他们包含着对数据表的所有记录指针，通俗一点说，索引即相当于一本书的目录,目的就是为了加快查询速度

创建索引：

create index 索引名称 on 表明(字段(长度)) #其中字段部分，如果是carchar和char类型，则需要写长度

查询：

select * from 索引名称 where 字段=数据;

删除索引：

drop index 索引名称 on 表名;

★主从服务器的设置（配载均衡）：

1，备份主服务器数据库：

mysql -uroot -pa1! jingdong > jd.sql

2,将主服务器的备份文件导入从服务器中(进入mysql后）：

创建新数据库

mysql -uroot -p 新数据库名字 < jd.sql;

3，进入主服务器修改配置：

sudo vim etc/mysql/my.cnf

去掉下图语句前面的#

4，重启主服务器：

sudo service mysql restart

5,修改从服务器配置：

sudo vim etc/mysql/my.cnf

只修改server_id 与主服务器不一样即可

6，重启从服务器：

sudo service mysql restart

7,在主服务器建立一个用户，让从服务器登陆到主服务器

8，在从服务器连接到主服务器：

change master to master_host = "主服务器IP",master_user="刚刚主服务器建立的用户名",master_password="刚刚主服务器建立的用户名对应的密码",master_log_file="对应上表中主服务器查询到的信息",master_log_pos=1121;

9,查看是否同步：

show slave status \G;