数据库基础

Mysql 客户端

• 连接本机服务器，登录服务器

命令行
mysql -uroot -p
[输入密码root]

show 查看数据库

• show databases

show database;

use 进入数据库

• use 库名

ues test;

show 查看数据表

• show tables

show tables;

desc 查看表结构

• desc 表名

desc student;

退出登录，断开连接

• exit、quit、\q三种方式

exit;
quit;
\q

create 建库建表

删除

• 删除:drop 类型 名字;
• 判断: if exists 名字;

-- 如果现有db1库，则删除db1库。
drop database if exists db1;
-- 如果现有stu表，则删除。
drop table if exists stu;

建库

• 创建：create database 名字；
• 字符集：charset 字符编码类型

create database db1 charset utf8;

建表

• 创建：create table 名字(参数)；

-- 创建stu表
create table stu(
	字段1 数据类型，
	字段2 数据类型，
	字段3 数据类型
);

数据类型

数字

标记

• unsingned 无符号，只有正数
• zerofill 配合显示位数，如果不足，补0

类型

• tinyint
• smallint
• int
  • zerofill int(3); 在查询结果中，不足3位按三位显示，超出3位正常显示
• bigint
• float
• double：运算不精确
• decimal：显示指定位数
  • decimal(8,2);总共8位，整数6位，小数2位，使用需要指定位数

字符串

• char：定长字符串，存储访问效率高，最长255个字节
  • char(6)；
    不足补空格。超出6，根据数据库设置，可能出错，也可能截断
• varchar：变长字符串，存储访问效率比char低，最长65535个字节
• text：长文本类型，最长65535个字节

日期时间

• data 年月日
• time 时分秒
• datetime 年与日时分秒
• timestamp
  • 时间戳
  • 与datetime 存储相同的数据
  • timestamp 最大表示 2038 年
  • timestamp 在插入、修改数据时，可以自动更新成系统当前时间

sql

结构化的查询语言
Structured Query Language

• DDL - 数据定义语言（建库建表）
• DML - 数据操作（增删改）
• DQL - 数据查询（select）

中文

• set name 编码类型
• 把客户端编码告诉服务器，服务器就可以做正确的编码转换

set names gbk;

insert 插入数据

• insert into 表 values(数据)；
• insert into 表(字段1,字段4,字段2) values(值1,值4,值2),(值1,值4,值2),(值1,值4,值2)；给表中指定字段添加数据，可以同时添加多个

insert into stu values(5,'张三','男','1996-8-4');
select * from stu;

insert into stu(id,name) values(7,'李四');
insert into stu(id,name) values(8,'王五'),(9,'赵六'),(10,'钱七');
select * from stu;

update 修改数据

• update 表 set 字段1=值1，字段3=值3 where 字段=值；
• set 字段=值；设置指定字段的值
• where 条件；

update stu
set gender='女', birthday='1998-8-4'
where id=7;
select * from stu;

delete 删除数据

• delete from 表 where 条件；
• 删除满足条件的数据

delete from stu
where id>8;
select * from stu;

select 查询

• select * from 表名；查询表中所有字段
• select 字段1[, 字段3] from 表; 查询表中指定字段

select * from stu;
select id,name from stu;

distinct 去重查询

• select distinct 字段 from 表；去除字段重复值
• select distinct 字段1,字段2 form 表；去除字段组合的重复值

select distinct id from stu;
select distinct id,name from stu;

where 子句

• 设置过滤条件
• 过滤条件先执行，效率高

符号	效果
=	等值过滤
<>	不等过滤
> >= < <=
between 小值 and 大值	大于等于 小的值 并且 小于等于 大的值,[小值,大值]
in(值1,值2,值3…	在指定的一组值中取值
is null , is not null	是null，不是null
like	字符串模糊匹配，%通配多个字符，_通配单个字符，%,_,\转义
not	not between and , not in(…) , is not null , not like
and	并且
or	或者

order by 子句

• 对查询结果进行排序
• order by 字段1；
  按字段1升序排列
• order by 字段1,字段2；
  按字段1升序排列，字段1相同的再按字段2升序排列
• asc 升序(默认)
• desc 降序

order by 字段1；
order by 字段1 desc,字段2；
order by 字段1 desc,字段2 desc；

查询执行顺序

1. where 过滤
2. 选取字段
3. order by 排序

单引号

• 字符串内容中的单引号，用两个单引号转义

insert into stu(id,name)
values (45,'I''m Xxx');

sql 注入攻击

• 通过在sql语句中，注入单引号，来改变sql语句结构
• 用户填写的内容中，所有单引号，都改成两个单引号

函数

字符串函数

• char_length(字符串) 字符数
• length(字符串) 字节数
• left(字符串, length) 获得左侧指定长度字符
• substring(字符串, start, length) 截取字符串，下标从1开始
• instr(字符串, 子串) 查找子串位置
• concat(s1,s2,s3…) 字符串连接
• lpad(字符串,length,字符) 在左侧填充
• replace(字符串1,子串, 字符串2) 替换字符串1中子串成字符串2

数字函数

• ceil(数字) 向上取整到个位
• floor(数字) 向下取整到个位
• round(数字, 2) 四舍五入到小数点2位
• truncate(数字, 2) 舍弃到小数点2位
• rand() 随机数[0,1)

日期函数

• now() 当前日期时间 年月日时分秒
• currdate() 当前日期 年月日
• currtime() 当前时间 时分秒
• extract(日期 from 字段) 抽取指定的字段的值
• date_add(日期, interval 字段 值) 在指定字段上加一个值
• datediff(日期1, 日期2) 两个日期之间相差的天数

null 值函数

• ifnull(a, b) a不是null返回a，a是null返回b

多行函数

• 对多行进行处理，得到一个结果
• 多行函数不能和其他普通字段一起查询
• 多个多行函数可以一起查询
• 会自动忽略null值

方法

• sum(字段) 和
• avg(字段) 平均
• max(字段) 最大
• min(字段) 最小
• count(*) 行数
• count(distinct 字段) 去除重复再计数

group by 子句：分组求多行函数

• 按指定字段中相同的值进行分组
• 分组后分别求多行函数
• 被分组的字段，可以分组多行函数一起查询
• group by 字段; 按字段相同值分组
• group by 字段1,字段2; 按字段1,字段2组合的相同值分组

-- 每个部门的平均工资
select avg(工资) 
from 表 
group by 部门;

select 部门,abg(工资) 
from 表 
where 部门 is not null 
group by 部门;

having 子句

• 用来对多行函数结果进行过滤
• having 和 where 作用相同，都是条件过滤
• where 过滤普通条件，最早执行
• having 过滤多行函数结果，分组，求万多行函数后，才执行
• having 跟在 group by 后面

子查询

嵌套查询

• 条件子查询
• from 子查询，行内视图
• select 子查询

条件子查询

• 一个查询的查询结果，作为另一个查询的过滤条件

select .. where a=(select ...)

• 单值子查询
  • = > >= < <=
• 多值子查询
  • in

  • all 比最大值大

  • any 比最小值大

• 多列子查询
  • where (a,b) in (select …)
  • where (a,b) = (select …)

from子查询、行内视图

• 从查询的查询结果，再查询

select ... from (select ... ) t;

约束

限制字段中的取值

• 主键
• 外键
• 非空
• 唯一
• 检查

主键

数据表中，一行数据的唯一标识

• 不重复

• 不能是 null 值

• 自动生产索引

• 字段 数据类型 primary key; 主键约束

• 一般使用 “非业务数据” 来作为主键

  • 自动增加：整数类型主键，可以设置自动生产自增的值
    • auto_increment

    create table xuesheng (
    	id int primary key auto_increment,
    	...);
    

    • 修改表中字段属性成自增

    -- 修改表，把主键id修改成自增
    alter table xuesheng
    modify id int auto_increment;
    

    • last_insert_id(); 获得刚刚产生的自增值
    • 只获得当前会话产生的自增值

        select last_insert_id();
      

  • 随机生成

外键

• 限制一个字段，只能取指定的主键字段中，存在的值
• 自动生产索引
• 标记：foreign key(列名)
• 标记：references 外键表名(外键列名)

alter table xuesheng
add(
	ban_id int,
	foreign key(ban_ji) references banji(id)
);

非空

• 标记： not null； 值不能为空

create table xuesheng(
	...
	name varchar(20) not null
	,,,
);

唯一

• 标记：unique

create table xuesheng(
	name varchar(20) unique
)

检查

• 设置检查条件，约束字段的取值
• mysql 不支持
• mariadb 支持检查约束
• 标记：check(约束)

create table xuesheng(
	...
	age int,
	gender char(1),
	check(age>=7 && age<=60),
	check(gender in('男','女'))
);

多表连接查询

• 两张表，按指定条件，连接成一张表
• 从连接的结果表中查询
• 自连接，一张表，看做是两张表来连接

select a.id, b.name
from 表1 a , 表2 b
where 连接条件;

外连接

• 内连接

  • 只查询满足连接条件的数据

• 外连接

  • 不满足连接条件的数据也要查询
  • 左外连接：查询左侧表条件外数据
  • 右外连接：查询右侧表条件外数据
  • 全外连接：双侧表条件外数据，Mysql不支持
    

• 外连接，非标准sql 连接语句，每种数据库连接语法都不同

  • sql server
    where a.id(+) = b.xid +号在哪边就是什么连接
  • oracle
    where a.id *=b.xid *号在哪边就是什么连接
  • Mysql:没有外连接的非标准语法

• 标准的表连接语法

  • select …
    from a join b
    on(a.id=b.xid)
    join c
    on(…)
  • left right 标记 左右外连接标记

事物

• 事物由一组 sql 语句组成
• 事物的 sql 语句整体成功，整体失败

A --> B 转账
update 账户 set money=money-100
where id='A';
update 账户 set money=money+100
where id='B';

• 数据操作最小单元是事物，而不是 sql 语句

启动事物

• begin
• start transaction
• 事物启动后，执行的数据增删改操作，会暂时记录在一个日志文件中

提交事物

• commit
• 事物日志中记录的数据操作，在数据表中生效
• 清空日志

回滚事物

• rollback
• 直接清空日志

事物特性 ACID

• A - Atomic
  原子性，数据操作的最小单元是事务，而不是 sql 语句
• C - Consistency
  一致性，事务完成前后，数据要保持逻辑的一致性
  A+B=100 转账前
  A+B=100 转账后
• I - Isolation
  隔离性，一个事务操作数据时，其他事务要等待
• D - Durancy
  持久性，事务成功，数据被永久的保存

数据访问冲突的问题

三种问题

• 脏数据
• 不可重复读
• 幻觉读

脏数据

• 一个事务未提交的数据，被另一个事务查询到

不可重复读

• 针对数据修改 update 操作
• 查询过的数据，再次查询时，与之前的查询结果不一致

幻觉读

• 针对数据的插入和删除
• 新插入的数据，查询不到
• 删除的数据，能查询到

隔离级别

• 为了避免数据访问冲突的问题，避免数据是混乱，数据库会让事物进行隔离
• 为了兼顾数据安全，和性能，数据库提供了四种隔离级别
  • 级别越高，越安全，行能越差
  • 级别越低，越不安全，行能越高
  • read uncommitted
  • read committed
  • repeatable read 默认级别
  • serialiable 最高
  • 语法：set session transaction isolation level 级别;