数据库基本操作

对数据库操作

show databases;//展示MySQL中所有数据库
create database [if not exists] db_name;//创建数据库
use db_name; //使用数据库
drop database db_name;//删除
show database create db_name;//查看数据库创建信息
select database();//查看当前在那个数据库中
alter database db_name character set编码名称;
select now();//返回当前系统时间

对表的操作

show tables; //展示数据库中所有表
select @@ datadir;查看表存储的具体路径
desc table_name;  //查表
create table tb_name{  //创建表
名称 1 类型1 comment ‘添加注释’ ；
名称2 类型2
...
};
drop table [if exists] tb_name;//删除表
show create table tb_name;//查看注释信息
alter  table tb_name add  新属性名称 类型;//新增一个属性
alter table db_name drop 列名称;  //删除某一列
alter table tb_name change 原字段名  新字段名 类型[约束]；//修改某一列
alter table 旧表名 rename 新表名；//修改表名
alter table tb_name convert to character 新字符集；//修改表字符集

常用数据类型

int --4字节
bigint–8字节
decimal(m,d)–双精度，m代表长度 ，d表示小数点位数
varchar–可变长度字符串–0到65535（即64K大小）
char(定长)
blob–65535–按照二进制存储字符串的–长文本字符串
datetime–1000到99999年–日期类型–格式：‘yyyy-MM-dd hh:mm:ss’
【时间戳：当前 时间距离 1970年1月1日0分0秒的秒数差，返回 一个长整型整数】

表数据的CURD（Create Update Retrieve Delete）

1、新增数据：

insert into tb_name(属性名称...)values(属性值...)

1.1单行插入

insert  into tb_name()values() --未赋值的属性名称则自动赋值NULL 

1.2单行全列插入

insert into tb_name values(属性值)；--属性值的个数要完全匹配

1.3多行插入

insert into tb_name(属性名)
values(第一行),(第二行);

1.4 多行全列

insert into tb_name values
(第一行 ),
(第二行),
(第三哼);

2、查询（Retrieve）
select[distinct][列名称 ]
from tb_name;
[where…]
[order by 某列 [asc|desc]]
[limit]
2.1查找指定的列

select 列名称,列名称  from tb_name;
select * from tb_name;
select 属性名 from tb_name;

2.2 查询字段为表达式

select 列名称，列名称 + 10 from tb_name;生成一个临时表

null加任何都为null
2.3给查询结果起别名

select 列名称 as 新名称 from tb_name;

2.4去重查询,得到去重之后的结果

> select distinct 列名称 from tn_name; select distinct 列名称，列名称 from
> tn_name;//组合去重

2.5按照结果集排序

select 列名称 from tb_name order by 列名称 【asc | desc】
查找按照asc升序，desc降序 ，默认asc，可以使用别名 ，存在null默认最小（比零小，比负数小，两个都是null看行号）

2.6分页查询

select 属性名称 from tb_name limit n;//查询结果按照前n行输出

select 属性名称 from tb_name limit n offset s;//查询结果按照前n行输出，从第s行开始

2.7 where查询
不支持别名，null比较is null，，，is not null

2.8 区间查询
eg.查询语文成绩在 【85-90】之间的同学姓名和他们的语文成绩

select name，Chinese from tb_name where chinese between 85 and 90;
select name，Chinese from tb_name where >= 85 and  <= 90; 

2.9包含查询

eg.查询数学成绩是10或60或98的同学姓名和他们的数学成绩
select name，math from tb_name where math in (60,10,98);

2.10模糊查询

eg.所有名字中带鹏字的学生姓名
select name from tb_name where name like  '%鹏%'；
eg.查询所有名字中鹏字出现在第二个位置且名字由三个字符组成
select name from tb_name where name like '_鹏_'；

下划线"_“代表占位,”%"表示模糊

3、修改表数据 update
语法 update 表名 set 列名 = （表达式 - 根据这个表达式确定修改的行）where ；
3.1、修改一行一列

update tb_name set 列名  = math +10 where name = '孙悟空';

3.2、修改一行多列

update tb_name set 列明1 = （），列名2=（） where name = '孙悟空';

3.3、修改多行多列，多行由where确定。
3.4、若update不带where条件则该属性的所有行都受影响

---

4、删除delete
语法 delete from 表名 where 过滤操作
4.1 删除名字叫孙悟空的学生记录
delete from tb_name where name = ‘孙悟空’;
4.2 不加where 全表删除
【一行行删】
【可恢复，可以加条件，删除慢】
4.3全表删除truncuate
语法： truncate table tb_name;
【直接将数据文件的大小变为0】
【删除快，数据不可恢复，不能加条件】

数据库约束

【对于某一列的值能添加哪些内容做了一定的限制–称为约束】

1、非空约束not null

alter table tb_name change 属性名 属性名 类型 not null;//将某个属性改为非空约束

2、unique 唯一约束–索引
【查看唯一约束：show keys from tb_name】
【null不受唯一键的约束 】

3、默认值约束default
【规定了默认值的表，再插入数据时若没有指定该列，则使用默认值插入】
【若对默认值显示插入NULL，不会触发默认值】
4、主键约束 primary-索引
【一张表只能有一个主键，primary = unique + not null】
【主键约束的列值不能为空，不能为null】
【主键约束可以由多个列共同组成】

5、自增主键auto_increment
【因为主键不重复且不为空，一般作为主键的列都是int或者定长的char型】
①自增主键可以显示的插入null或者不写都会触发自增操作
②关于自增主键删除后的自增情况，自增主键以当前最大值为基准+1


③显示给主键插入一个值，下一次触发自增操作时，仍然还是以当前出现过的最大值为基准自增
④插入失败也会使用自增主键
6、外键约束
【涉及多个表之间的关联约束】
语法 ：foreign key(表中属性) references tb_name不同表(属性);
【增加看主表，删除看从表】
7、check约束

表的设计（数据库三范式）

第一范式：确保每列的原子性（设计表时，每一列都不能再次分解）
第二范式：确保当前表中所有属性都和主键相关
第三范式：表中所有属性都和主键直接相关而不是间接相关

binlog机制

每当数据库进行修改（增删改）操作时 ，都在binlog文件中记录此动作。

CURD进阶

1、根据查询结果一次性插入多条记录
insert into tb_name (属性) select where 过滤条件
根据select结果集插入表中数据，select处的属性要和插入的属性一一对应。
2、聚合函数
【把行之间的数据，和列无关】

① count（ * ） 相当于select（ * ）不针对某个属性，全表扫描。效率低
count(属性) 会去除所有值为null，只统计不为null个数
count（任意数值） 效率等同于 count（ * ），在临时表中创建了一列属性，值都是count（数值），统计当前表中有多少行，但速度快于count（ * ），因为它使用索引。
② sum 可以起别名
【聚合函数搭配group by 分组查询使用】
select 使用 group by 可以对指定查询的列进行分组。

步骤：先按照role分组，分组之后使用avg进行求平均值
【分组之后进行条件查询必须使用having】
【不能再聚合之后使用where，聚合之前可以】

多表查询

联合查询：两张表的笛卡尔积，本质上两个表的排列组合—>多表查询
【做题步骤：①先求笛卡尔积②根据题目条件筛选】
select tb_1.*, tb_2.* from tb_1, tb_2;

内连接

【笛卡尔积根据关联关系严格筛选】
【关键字 inner join ，，， on】
语法： tb_name inner join tb_name on 内连接条件 where 具体的筛选条件

【内连接中一定都是多个表同时都存在数据关联的情况】

外连接

【两张表查询时某张表存在空数据，仍然显示出来】
①左外连接、左表数据完全显示
语法：from tb_name left join tb_name on 条件
②右外连接、由表数据完全显示
语法：from tb_name right join tb_name on 条件

自连接

【涉及同一张表中行数据的筛选，用自连接】


【查询到Java成绩对应的课程id为1】

子查询

单行子查询：内部查询sql返回单条记录的查询


多行子查询：内部查询sql返回多条记录的查询


使用exists多行子查询
【从外到里】