数据库管理精要-优快云博客

int:整型
double:浮点型， double(5, 2)表示最多5位，必有2位小数
char:固定字符串 char(10) 'abc       '
varchar:可变字符串 varchar(10)  'abc'
text:字符串类型
blob:字节类型
date:日期，格式为 yyyy-MM-dd
time:时间，格式为 hh:mm:ss
datetime:日期时间类型 格式为 yyyy-MM-dd hh:mm:ss
timestamp:时间戳类型 yyyy-MM-dd hh:mm:ss 会自动赋值

SQL分类

DDL(Data Defination Language)：用于定义数据库对象，库、表、列CREATE ALTER ADD MODIFY DROP
DML(Data ManipulationLanguage)：操作数据库表中的数据INSERT UPDATE DELETE 一条一条删除数据 delete from 表名 where (condition)TRUNCATE 直接将表删除掉，然后再创建一个同样的新表，删除的数据不能找回。执行速度比delete快

DQL(Data Query Language)：查询记录

SELECT
-- 返回的结果是一张虚拟表
-- 返回整张表
SELECT * from table_name;
SELECT column_list from table_name 
    where condition
    -- 对结果分组
    group by grouping_columns
    -- 分组后行条件
    having condition
    -- 对结果排序（ASC升序，DESC降序）
    order by sorting_columns ASC
    -- 结果限定
    limit offset_start, row_count

-- 聚合函数，用来做纵向操作
    select count(*) from stu; -- 计算非NULL的数据 + where
           max()
           min()
           sum(); -- 若不是数值数据，计算结果为0
           avg()
-- 分页查询
    limit 
    -- example
    select * from emp limit 0,3 -- 第一个参数：从第几行查询，第二个参数：查几行

-- 关键字顺序
select * from table_name where condition group by column_name having condition order by column_name
-- example:查询工资超过1500的员工并且工资总和超过6000的部门编号以及工资和
select deptno,SUM(sal) from emp where sal > 1500 group by deptno having SUM(sal)>6000 order by SUM(sal)
-- having后的condition可以使用聚合函数
-- where后的condition只能使用普通条件

DCL(Data Control Language)：定义访问权限SELECT

数据库使用

建表(create)-->使用(use)-->添加(insert)-->查询(select)-->删除(delete)-->修改(update)

数据完整性

保证用户输入的数据保存到数据库中是正确的。

只对insert delete drop 语句起作用，插入失败时可以检测出来。对 select不起作用

实体完整性

标识每一行不重复，七约束

主键约束(Primary Key)

唯一约束(unique)

自动增长列(auto_increment(MySQL) identity(sqlServer) sequence(oracle)

-- 1 primary key 主键必须唯一，可用于表示学号
-- 2 unique 数据不能重复
-- 3 auto_increment 给主键添加自动增长的数值，列只能为整数类型
create table stu (
    sid int primary key, -- 主键
    name varchar(20) unique -- 唯一
);

create table stu (
    sid int，
    name varchar(20)，
    primary key(id), -- 主键
);

create table stu (
    classid int,
    sid int，
    name varchar(20)，
    primary key(id, classid), -- 联合主键
);

域完整性

限制此单元格的数据正确域代表当前单元格类型：数据类型非空约束(not null)默认值约束(default) check约束（mysql不支持）check(sex='男' or sex='女')
```
create table stu (
    sid int primary key auto_increment,
    name varchar(20) unique not null,   -- 非空约束
    address varchar(50) default "西安"    -- 默认值约束
);
```

引用完整性

外键约束(foreign key)：子表引用主表的主键必须引用主表的主键

create table stu (
    sid int primary key,
    name varchar(20) not null，
    monitorid int,
    classid int
);

-- 法1
create table score(
    score double,
    sid int,
    constraint fk_stu_score_sid foreign key(sid) references stu(sid) -- 关联子表与主表中的sid
);
-- 法2
create table score(
    score double,
    sid int
);
alter table score add constraint fk_sid foreign key(sid) references stu(sid);

表之间关系

为了减少数据冗余，需要创建多个表来进行数据保存。一对一多对一（一对多）多对多

多表查询

select * from stu, score where stu.sid=score.sid; -- 多表查询，利用主外键关系来筛选数据n个表查询，至少需要n - 1个条件

select s.sid,s.name,c.score from stu s,score c where s.sid=c.sid; -- 查询多表的某几列

合并结果union union all

连接查询

内连接 [inner]join on
外连接 outer join on

left [outer] join 依赖左边的表，即from后所接的表，若右边表不存在，也可查询到

right [outer] joinfull join mysql 不支持
自然查询 natural join 自己找表中名称和类型完全相同的列作为条件的数据

-- inner可省略，默认为inner，on后接主外键关联条件
SELECT * FROM stu s INNER JOIN score c ON s.sid=c.sid where c.score > 60; 
-- 外连接
SELECT * FROM stu s LEFT JOIN score c ON s.sid=c.sid where c.score > 60; -- 以左表为基准

-- 多表查询
select * from stu s,score c,course cc where s.sid=c.sid and c.courseid=cc.courseid;
select * from stu s join score c on s.sid=c.sid join course cc on c.courseid=cc.courseid; -- 内连接

子查询嵌套查询，一个select语句中包括另一个select语句，可用于from和where后

-- 子查询
-- 查询与"lisi"同班的同学
select * from stu where classid=(select classid from stu where name="lisi")
-- 先查出"lisi"的班级号，然后查询与其相同班级号的数据

-- 单行多列查询
-- 查询与"lisi"同班性别相同的同学
 select * from stu where (classid,gender) in (select classid,gender from stu where name="lisi")

自连接查询自己连接自己
```
-- 查询"lisi"的学号，姓名，班长学号和班长姓名
select s1.id,s1.name,s1.mointorid,s2.name from stu s1, stu s2 where s1.name="lisi" and s1.monitorid=s2.id;
```
数据库的备份与恢复

mysqldump -u root -ppassword stu>/home/xx/Document/a.mysql;将stu备份为a.mysql文件

source /home/xx/Document/a.mysql; 需要登陆mysqlmysql -u root -ppassword mydb1</home/xx/Document/a.mysql 不需要登陆mysql将stu恢复为mydb1

NOTE
登录mysql时 -p后不接空格，直接写密码