数据库基础(一)-CSDN博客

本文链接：https://blog.csdn.net/qq_18831475/article/details/104768106

title: 数据库基础（一）
author: 软帝学院
summary: DDL、DML、DQL，CRUD，约束条件
categories:

数据库
tags:
MySQL

常见数据库：

关系型数据库
- Oracle
- MySQL
- SQLServer
非关系型数据库
- MogoDB
- Redis

Mysql常识

查看所有的数据库：

show databases；

MySQL数据库中大小写不敏感，基本不区分大小写

图形可视化界面：取代dos命令行，在可视化界面中操作数据库

数据库常用操作

-- MySQL的注释 --
-- 使用MySQL，需要先选择使用哪个数据库
use 数据库名;-- 指定使用哪个数据库
show tables;-- 查看数据库中的表
creat database emp; -- 创建名字为emp的数据库

数据库中的数据类型

**整型：**int/bigint,不设置长度的话默认11位
**字符串：**char/varchar
char：定长字符串，char（10），指定长度为10的一个字符串，如果存入的字符串长度不足10，会使空字符串填充，一般用于定长的数据列（手机号，身份证号）
- varchar：变长字符串，varchar（10）表示该字段的最大长度为10，存入多少个字符长度就为几，一般用于长度不确定的数据列
**小数型：**double/decimal
double浮点型 double（5,2）最多5位，其中必须有2位是小数，即最大值是999.99
decimal,用法和浮点一样，但是不会有精度问题
日期型：
date日期，格式 yyyy-MM-dd,占用3个字节
- timestamp时间戳，格式 yyyy-MM-dd hh:mm:ss，占用4个字节
- datetime，格式和时间戳一样，但是占用8个字节

SQL语言

**DDL：**数据库定义语言，用于表的创建，修改，删除
**DML：**数据操作语言，对表内数据的增加，删除，修改
**DQL：**数据查询语言，对表内数据查询
**DCL：**数据控制语言，创建数据库用户，分配账户权限，密码等
**TCL：**事务控制语言，处理数据库中的事务

DDL：表的创建、修改、删除

创建数据库

careat database 数据库名;

删除数据库

drop database 数据库名;

使用指定的数据库

use 数据库;

查询数据库中所有的表

show tables;

查看表的结构

desc 表名;

表的创建

creat table 表名(
	字段名1 数据类型1,
	字段名2 数据类型2,
	....
    字段名n 数据类型
);  
-- 创建表的格式，最后一个字符后不用加逗号

设置主键

primary key
设置自增

auto_increment
设置默认值

default 值
设置不能为空

not null

-- 建表的时候就可以设置一个自增的主键
creat table 表名(
	字段名1 数据类型1 primary key auto_increment,
	字段名2 数据类型2 default 60,-- 默认值为60
	....
    字段名n 数据类型
);

对表结构进行修改

-- 添加:在表中添加一列数据，可以添加多行，括号内字段名数据类型用逗号隔开
alter table 表名 add(字段名 数据类型 , 字段名 数据类型);
-- 删除:删除表中的一列字段数据
alter table 表名 drop 字段;
-- 修改表名：方法一
rename table 原表名 to 新表名;
-- 方法二
alter table 原表名 to 新表名;
-- 修改列名
alter table 表名 change 原列名 新列名 数据类型;
-- 修改字段列的其他属性
alter table 表名 modify 列名字段 数据类型 属性如（primary key，default等）;

删除表

drop table 表名;

DML：数据操作语言，对表内的数据的增加、删除、修改

CRUD操作

C （Create 新增）
R （Retrieve 查询）
U （Update 更改）
D（Delecte 删除）

添加数据

向表中插入数据，前面括号中的字段名顺序可以调换，但是后面的字段值跟字段必须一一对应

insert into 表名 (字段名1，字段名2...) values (字段值1，字段值2...);
-- 例
insert into student (id,name) values(1001,'小花');
-- 在student表中，添加数据

对表中所有的字段添加数据，可以省略字段名，但此时values后的字段值顺序要严格按照数据库表的顺序

insert into 表名 values(字段值1,字段值2...);

一次向表中添加多行数据

insert into 表名 values (字段值1，值2..),(字段值2-1，值2-2..)，(字段值3-1，值3-2..);
-- 例
insert into student values(1002,'小明',100),(1003,'小李',99);

注意：加入设置了自增长的主键，使用方式1或者方式3**插入数据时，即使没有加主键的字段，系统约会自动给主键列设值，方式2***添加所有的数据时，即使***主键有增长，也必须在括号内将主键的字段写出来

删除数据

删除表中所有的数据

delete from 表名;

删除表中特定的数据

delete from 表名 where 字段名=字段值;
-- 例
delete from student where id = 10001;
-- student表中，删除id=10001的数据

修改数据

修改表中字段的数据，如果不带调教的话就是修改了表中该字段的所有值

update 表名 set 字段名=字段值，字段名2=值2...where 条件
-- 例
update student set score=100 where name='小明'
-- student表中，name为小明的score设置为100

DQL：数据库查询语句

查询所有的字段

select * from 表;

**别名 as：**用于查询时给字段或者表名起别名，别名只在当前这次查询有效（as可以省略不写）

select id as 学号，name as 姓名 from stu;
select id 学号 ，name 姓名 from stu;

去重 distinct：写在select后面

select distinct * from stu;
select distinct id from stu;

**排序 order by：**将查询的结果用于根据某个字段排序，一般用于查询语句
- 升序 asc（可以省略）
- 降序 desc

-- 查询分数，根据id排序，升序（asc省略）
select score 分数 from stu order by id;
-- 查询分数，先按分数升序，相同再按id降序
select score 分数 from stu order by score asc,id desc;

**分组 group by：**每一组只会显示其中的一条记录
配合分组使用的聚合函数：
- 求个数 count(*)
- 求最大值max（字段）
- 求最小值min（字段）
- 求和sum（字段）
- 求平均值 avg（字段）

-- 根据性别分组
select gender from stu group by dender;
-- 根据分数分组，查出每个分数有多少人
select score,count(*)from stu group by score;

**数据过滤 having：**与group by结合使用，用于对分组后的数据进行过滤

-- 查询stu表的班级，按照班级分组，分组后的分数和要大于等于200
select classid from stu group by classid having sum(score)>=200;

**条件查询 where ：**对表中的数据进行过滤

select * from stu where score > 80;

模糊查询 like
- **% ：**匹配0个或者多个占位符
- **_ ：**匹配一个占位符（占一个位置）

-- 查询名字，第二个字是张的所有名字
select name from stu where name like '_张%';

**between…and…**判断是否在范围之间

-- 查询分数在70-80之间的学生
select * from stu where score between 70 and 80;
select * from stu where score > 70 and score < 80;
-- 查询分数不在70-80之间的学生
select * from stu where score not between 70 and 80;

in() 判断是否在一个列表

-- 查询分数为90,91,92的学生
select * from stu where score in(90,91,92);
-- 查询分数不为90,91,92的学生
select * from stu where score not in(90,91,92);

**is null / is not null 😗*为空/不为空

-- 查询出姓名为空的学生
select * from stu where name is null;
-- 查询出姓名不为空的学生
select * from stu where name is not null;

不等于 <>/!=

-- 查询分数不为99的学生
-- <>
select * from stu where score <> 99;
-- ！=
select * from stu where score ！=99;

分页 limit（开发常用）：
- limit 显示数据个数如 limit 5;
- limit 起始位置，显示数据个数如 limit 6,3（从第7条数据开始，数3条）

select * from stu limit 5;
select * from stu limit 6,3;

约束分类

约束条件 constraint：约束实际上就是表中数据的限制条件

作用：表在设计的时候加入约束的目的就是为了保证表中记录完整和有效

非空约束 not null

只有列级约束

create table stus(
	id int(2),
    name varchar(10) not null
)

主键约束（primary key，简称pk）
1. 主键约束 = 不能重复 + 不能为空（null）
2. 主键约束可以有两种实现方式：列级约束和表级约束

create table stus(
	-- 列级约束，将id设为主键
    id int(2) primary key,
	name varchar(10)
);

设置主键自增

alter table stus modify id int(2) auto_crement;

表级约束 constraint

create table stus(
	id int(2),
    name varchar(20),
    -- 表级约束，写在表的最后面
    -- 表级约束名：表名_列字段名_约束条件
    constraint stus_id_pk primary key(id)
)

唯一约束 Unique（uk）

约束插入数据值，使之是唯一的

-- 列级约束
create table stus(
	id int(),
    name varchar(20) unique
)

-- 表级约束
create table stus(
	id int(),
    name varchar(20),
    constraint stus_name_uk unqiue(name)
)

检查约束 Check（CK）,mysql不支持

约束插入的数据，是否符合给定的条件

create table stus(
    id int(2),
    name varchar(20),
    gender varchar(10) check(gender in ('男','女'))
)

枚举类型 enum()

约束插入的数据，是否是枚举里的数据

create table stus(
	id int(2),
    name varchar(20),
    -- 不为空，且默认为女
	gender enum('男','女') not null default '女'
)

触发器

Oracle中触发事件
外键约束 Foreign key（FK）
- 外键约束定义在两个表的两个字段或者一个表的两个字段上，
- 用来保证给相关的两个字段的关系
- 外键所在的表就是子表，子表引用数据的表就是主表
- 子表的外键列引用主表的主键列
外键的约束格式：

foreign key（需要设为外键的字段）references 其他表（需要关联的字段）

references：关联映射的含义

**示例：**stus 学生表（子表），major 专业表（主表）

-- 学生表（子表）
create table stus(
	sid int(3),
    name varchar(20) not null,
    major_id int(2),
	-- 设置sid为这张表的主键
    constraint stus_sid_pk primary kty (sid),
    -- 将major_id设置为这张表的外键，映射的是 major表中的mid
    constraint stus_mid_fk foreign key (major_id) references major(mid)
);
-- 专业表（主表）
create table major(
	mid int(2) primary key,
    name varchar(20)
)

向子表插入外键数据时，如果主表中，并没有这样一条外键数据，就会报错；
删除专业表中的主键值时，如果子表中有数据用了这个外键值，就会报错

删除约束条件

alter table stus drop foreign key stus_mid_fk;

添加约束条件

-- 设置外键
alter table stus add constraint stus_mid_fk foreign key (major_id) references major(mid) on delete set null;
-- on delete set null
当映射关联表中的数据删除时，主表被关联的数据会设置为null
-- on delete cascade
级联删除（当关联映射表数据被删除时，被关联的表中对应的数据也会跟着删除）