一个优秀的设计对于数据库系统的作用:减少数据的冗余,确保数据的一致性和完整性,易于维护和扩展
实体关系图(ERD)是一种数据库设计的结构图,数据库的ERD主要包括实体,属性和关系 三个部分组成
规范化设计的作用:减少冗余,增加数据的完整性和一致性
不规范设计导致的问题:数据异常,数据冗余,插入异常,删除异常,更新异常
第一范式
满足的条件:表中的字段都是不可载分的原子属性
表需要定义一个主键
第二范式
满足的条件:满足第一范式
非主键字段必须完全依赖于主键,不能只依赖于主键的一部分
第三范式
满足的条件:满足第二范式
属性不依赖其他的非属性
DDl用于操作数据之间的关系
操作数据库
show databases; 查看所有数据库
select database();查看当前所在的数据库
create database yy; 创建数据库为yy
create database if not exists yy;
alter database yy character set utf8;修改数据库信息
drop database yy;删除数据库yy
use yy;进入yy数据库
操作表
create table if not exists yy(
id int
);创建表为yy
desc yy;查看表结构
alter table yy rename to yyj;修改表名为yyj
alter table yy add name varchar(255);添加表字段
alter table yy drop name;删除字段
drop table yy;删除表
drop table if exists yy;
show tables;显示所有表的名字
show create table yy;显示yy这个表的创建时的数据
---------------------------------------------------------------------------------------------------------------------
DML对表数据的增删改的操作
insert into yy(name) value('yy');增加表中name字段的数据
update yy set name='yy' where id =3;更新数据表yy中name字段的数据
delete from yy where id =1 ;删除yy表中id为1的数据字段
truncate table yy;清空表
------------------------------------------------------------------------------------------------------------------------------------------
DQL用于数据查询语句,用于查询表中的数据,最常用的语言
查询语句一般的顺序
select * from 表 where 条件 group by 分组字段 having 分组筛选条件 order by 排序字段 limit 分页条件;
条件查询
select * from yy;查询所有的数据,包括所有表字段
select id , name from yy where id >10 and name='yy';查询yy表中的id大于10和name为yy的字段
操作符 说明
= 等于
<> 不等于
!= 不等于
< 小于
<= 小于等于
> 大于
>= 大于等于
between 在指定的两者之间
in 在指定值中选取
not 否定条件
select * from yy where age between 10 and 20 and not name='yy';查询yy表中年龄在10到20之间name不是为yy的数据
select * from yy where name in (‘yy’,'tt');查询yy表中的名字为yy或tt的数据
去重复查询
select distinct name form yy;查询相同name字段中的一条数据
分页查询
select * from yy limit 5;查询yy表中的数据显示5条数据
sekect * from yy limit 1,5;查询yy表中的显示的数据以2开始加5条数据
排序查询
select * from yy order by id;查询yy表中以id为字段的数据升序,默认升序(asc);
select * from yy order by id desc;查询yy表中以id为字段的数据降序
空值检查查询
select * from yy where name is null;查看yy表中name字段空值的数据
模糊查询
select * from yy where name like 'y_';查询yy表中name字段以y开头的后面一个任意单一的数据
select * from yy where name like '%';查询yy表中name字段的任意数据
函数查询
select concat(name,id) from yy;查询yy表中name和id拼接的数据
select id+2 from yy;查询yy表中的id字段加2的数据
- 减
* 乘
/ 除
select trim(name) from yy;查询yy表中name字段两边没有空格的数据
rtrim 右边没有空格
ltrim 左边没有空格
select left(name,2) from yy;查询yy表中的name字段数据前二个的值
right 右边值为2
select length(name) from yy;查询yy表中的name字段数据的值个数
select upper(name) from yy;查询yy表中的name字段全部变成大写
lower 小写
select count(name) from yy;查询yy表中统计name字段的个数
avg 平均值
max 最大值
min 最小值
sum 求和
分组查询
select sex,count(*) from yy group by sex;查询yy表中性别字段分组并且统计个数
select sex,count(*) from yy group by sex having count(*) >1;查询yy表中性别字段分组并且个数大于1的数据
*having是分组查询特有的属性
子查询
select * from yy where name = (select name from yy where id =1);查询yy表中name字段为yy表中id字段为1的名字的数值的数据
联表查询
内连接
select yy.*,yyj.* from yy inner join yyj on yy.bid = yyj.id;查询yy和yyj表中yy表中的bid字段和yyj表中id字段相同的数据
select yy.*,yyj.* from yy join yyj on yy.bid = yyj.id;查询yy和yyj表中yy表中的bid字段和yyj表中id字段相同的数据
select yy.*,yyj.* from yy , yyj where yy.bid = yyj.id;隐式方法和上面的一样
别名
select y.*,j.* from yy as y , yyj as j where y.bid = j.id; 设置别名
外连接
select y.*,j.* from yy as y left join yyj as j on y.bid = j.id;
select y.*,j.* from yy as y right join yyj as j on y.bid = j.id;
-----------------------------------------------------------------------------------------------------------------------------------------
DCL用于数据的权限
授权
create user 'yy'@'localhost' identified by '197386';创建用户和密码
drop user 'yy'@'localhost';删除用户
show grants for 'root'@'localhost';查看用户的授权
grant insert,delete,update,select on yy.yy to root@localost;在root本地账户中给予yy数据库中的yy表增删改查的权限
撤销权限
revoke insert,delete,update,select on yy.yy from root@localost;;在root本地账户中取消yy数据库中的yy表增删改查的权限
--------------------------------------------------------------------------------------------------
mysql的约束
主键(primary key)是每个数据的标识,主键可以是一个或多个列的组合
满足的条件:
主键列的值必须唯一,不能重复
主键列的值不能为空,不能为null
一个表只能有一个主键
主键的作用:可以加速查找和提高数据的完整性和安全性
create table if not exists yy(
id int not null primary key,
name varchar(20)
) engine=innoDB default charset=utf8;创建表为yy的以id字段为主键,数据库引擎为innoDb,默认编码是utf8
自增长约束(auto_increment)是为数据自动增加
满足的条件:
默认情况初始值是1,每增加一条数据自动加1
一个表中只能有一个字段使用自增长约束,且字段必须有唯一索引,避免序号重复
自增长约束字段必须有not null属性
自增长约束只能是整数类型(int ,tinyint,smallint,bigint)
自增长约束只能满足数据类型的最大值,达到最大值将失效
create table if not exists yy(
id int not null auto_increment primary key,
name varchar(20)
) engine=innoDB auto_increment = 5 default charset=utf8;创建表为yy以id字段为自增长主键,自增长步长为5
alter table yy auto_increment = 6;修改yy表中自增长的步长为6
*delete数据之后自动增长从断点开始
truncate数据之后自动增长从默认起始值开始
非空约束(not null)是指字段的值不能为空
alter table yy modify name varchar(20) not null;添加yy表中name字段不能为空的约束
alter table yy modify name varchar(20);删除yy表中name字段的非空约束
唯一约束(unique)是指字段中的值不能出现重复的数据
alter table yy add unique(name);添加yy表中的name字段为唯一约束
alter table yy drop index name;删除yy表中name字段的唯一约束
默认约束(default)是指字段中的值为默认值
alter table yy modify name varchar(20) default null;
零填充约束(zerofill)插入数据时,当该字段的值的长度小于定义的长度时,会在该值的前面补上相应的0
zerofill默认为int(10)当使用zerofill时,默认会自动加unsigned(无符号)属性,使用unsigned属性后,数值范围是原值的2倍,例如,有符号为-128~+127,无符号为0~256。
alter table yy modify id int;删除yy表中id字段零填充约束