数据库基础知识总结

不知道为什么编辑好的格式,显示出来就没了 !!(╯’ - ')╯︵ ┻━┻

一、数据库概述
1、数据库,存储、维护、管理数据的集合(仓库)。
2、DB:database 数据库,使用仓库存储数据的。
3、DBMS:数据库管理系统 软件。
4、CRUD
create 增加
Retrieve 读取(重新得到数据)
update 更新
delete 删除
二、sql语句
sql:结构化查询语言。
sql分类:
DDL:数据定义语言(定义数据库/表/修改表结构,关键字:create,alter,drop等 )
DML:数据操作语言(操作数据库中表的数据,关键字:insert,delete,update等) 增删改
DQL:数据查询语言(查询操作,关键字:select,from,where等) 查
DCL:数据控制语言(用户管理,权限控制)
掌握 DML DQL
三、DDL:数据定义语言(操作定义数据库/创建及修改表结构,create,alter,drop等)
1、创建数据库:
create database 数据库名;
create database 数据库名 character set 字符集;
2、查看所有数据库:
show databases;
3、查看某个数据库的定义的信息:
show create database 数据库名;
4、删除数据库:
drop database 数据库名;
5、查看当前正在使用的数据库:
select database();
6、切换数据库:
use 数据库名;
7、创建表:
create table 表名(
字段名1 数据类型[长度] [约束],
字段名2 数据类型[长度] [约束],

字段名n 数据类型[长度] [约束]
);
8、查看数据库中的所有表:
show tables;
9、查看表结构:
desc 表名;
10、删除表:
drop table 表名;
11、修改表:
1)添加列
alter table 表名 add 列名 类型(长度) [约束];
2)修改列的类型长度及约束
alter table 表名 modify 列名 类型(长度) 约束;
3)修改列名
alter table 表名 change 旧列名 新列名 类型(长度) 约束;
4)删除列
alter table 表名 drop 列名;
5)改表名
rename table 表名 to 新表名;
6)改表的字符集(不使用)
alter table 表名 character set 字符集;
四、DML:数据操作语言(操作表中数据,insert,delete,update等)
1、增加
insert into 表名 (列名1,列名2,列名3) values(值1,值2,值3);
2、修改
update 表名 set 列名1=值1…字段名n=值n where 条件;
3、删除
delete from 表名 where 条件;
4、清空表:
1)delete from 表名;
逐行删除,效率低;不清空auto_increment记录数
2)truncate 表名;
先摧毁表,然后按照原结构再创建一个新表,效率高;auto_increment将置为零,从新开始
5、约束:
1)主键约束:primary key唯一标识表中一条记录。一个表中只能有一个主键。主键会自动添加唯一约束、非空约束
create table product(
pid int primary key,
…其它字段…
)
删除主键约束:
ALTER TABLE 表名 DROP PRIMARY KEY;
2)自动增长:auto_increment
create table product(
pid int primary key auto_increment,

)
删除自增长:
alter table 表名 modify 列名 类型(长度);(修改列时,后面不加约束就会清空字段的约束,主键除外)
3)非空约束:NOT NULL
create table product(
pid int primary key,
pname varchar(200) not null,

)
删除非空约束 :
ALTER TABLE 表名 MODIFY 列名 数据类型[长度];(后面不出现not null约束即可,就表示删除了not NULL约束)
4)唯一约束:unique表示本列的值是唯一的
create table product(
pid int primary key,
pname varchar(200) unique,

)
***如果向pname字段添加重复的值,数据库会抛出异常。
***如果字段设置了唯一约束,可以写入"空字符串",但只能有一条。
***也可以写入NULL值,可以写入多条。

删除唯一约束:
ALTER TABLE 表名 DROP INDEX 名称;
***如果添加唯一约束时,没有设置约束名称,默认是当前字段的字段名
***主键与唯一约束的区别:
主键:代表:唯一、非空;一个表只能有一个主键;
唯一:只代表:唯一;可以有多个NULL值;一个表可以有多个字段被设置为唯一约束;
5)默认约束:default 值;
***可以设置某列的默认值,在添加数据时,可以不指定这列的数据,而使用默认值。
create table student(
id int primary key auto_increment,
stuName varchar(20) not null,
sex char(5) default ‘男’
)
在添加时,如果要使用默认值:
INSERT INTO student VALUES(NULL,‘bbb’,DEFAULT);
删除默认约束:
ALTER TABLE 表名 MODIFY 列名 数据类型[长度] (后面不要出现default关键字即可)
6)外键约束:
1).作用:设置在"从表"的外键字段上,可以强制外键字段的值必须参考主表中的主键字段的值。
2).设置外键约束:
alter table 从表 add [constraint] [外键名称] foreign key (从表外键字段名) references 主表 (主表的主键);
3).使用外键目的:
保证数据完整性
4).删除外键
alter table 从表 drop foreign key 外键名称;

五、DQL数据查询语言(查询操作,select,from,where等)
1、查询数据:select 字段名 from 表名 where 字段的筛选条件
1、简单查询:
1).查询所有字段的所有记录:
select * from product;
2).查询部分字段的所有记录:
select pname,price from product;
3).使用别名:
a).列别名(AS可省略):
SELECT pname AS ‘商品名称’ , price AS ‘价格’ FROM product;
b).表别名:
SELECT p.pname,p.price FROM product p;//一般在多表中使用别名
4).去掉重复值
SELECT DISTINCT price FROM product;
5).对查询结果进行运算:
例如:将所有查询结果的商品的价格加100显示:
select pname,price + 100 from product;
注意:只对本次查询结果进行更改,原数据没有更改。
2.条件查询:
1).比较运算符:
1).">":大于。例如:查询价格大于2000元的商品 --针对数值类型查询
select * from product where price > 2000;
2)."<":小于。例如:查询价格小于2000元的商品 --针对数值类型查询
select * from product where price < 2000;
3).">=":大于等于。例如:查询价格大于等于2000元的商品 --针对数值类型查询
select * from product where price >= 2000;
4)."<=":小于等于。例如:查询价格小于等于2000元的商品 --针对数值类型查询
select * from product where price <= 2000;
5)."<>":不等于。例如:查询价格不等于2000元的商品 --针对各种类型
select * from product where price <> 2000;
“!=”:不等于 --针对各种类型
select * from product where price != 2000;
6)."=" :等于.例如:查询价格等于2000元的商品 --针对各种类型
select * from product where price = 2000;
2).逻辑运算符:
1).and : 语义:并且
例如:查询所有商品价格大于2000元的电脑类商品
select * from product where price > 2000 and categoryName = ‘电脑’;
2).or : 语义:或者
例如:查询所有商品价格大于2000元,或者价格低于1000元的所有商品
select * from product where price > 2000 or price < 1000;
3).not : 语义:非
例如:查询商品价格不大于2000元的所有商品
select * from product where not(price > 2000);
注意:如果多个and和or运算,中间不要加逗号,可以使用()改变运算顺序。
例如:查询所有价格大于2000元的电脑类商品或者服装类商品
select * from product where price > 2000 and (categoryName = ‘电脑’ or categoryName = ‘服装’);
3).范围查询:between … and …(可以用于数值类型,也可以用于日期类型)
1).用于查询数值范围:between(包含)…and(包含)…
例如:查询价格在1000元(包含)到2000元(包含)之间的所有商品
select * from product where price >= 1000 and price <= 2000;
或者
select * from product where price between 1000 and 2000;
2).用于查询日期范围:
例如:查询生产日期在2017年1月份的所有商品
select * from product where proDate between ‘2017-01-01’ and ‘2017-01-31’;
4).多个值的判断:in(值列表)
例如:查询商品价格为200元,500元,1000元,2000元的商品信息
select * from product where price = 200 or price = 500 or price = 1000 or price = 2000;
或者使用in查询
select * from product where price in (200,500,1000,2000);
5).模糊查询:like
两个通配符:
1)"%" : 任意的0到多个字符;
2)"_":任意的1个字符
例如:查询商品名称中包含"花"的商品信息
select * from product where pname like ‘%花%’;
查询商品名称中以"花"字开头的商品:
select * from product where pname like ‘花%’;
商品名称以"花花"开头,全名是四个字的商品:
select * from product where pname like ‘花花__’;
6).查询空字段:
1).添加一条记录时,不添加的字段可以指定为NULL值,例如:
insert into product values(14,‘果10’,200,‘食品’,NULL);
要查询所有"生产日期"没有添加的所有商品:
select * from product where proDate IS NULL;
2).添加一条记录时,值为’’:
insert into product values(15,‘果11’,300,’’,null);
要查询出来这条记录:
select * from product where proDate = ‘’;
03.SQL高级查询_排序:
1).使用的关键字:order by 字段名 ASC(升序–默认) / DESC(降序)
例如:查询所有商品,要求结果按价格从小到大排序
SELECT * FROM product ORDER BY proDate ASC;
2).注意:升序可以不写asc关键字,例如:
select * from product order by proData;//升序
3).排序:
升序(ASC):从小到大;
降序(DESC):从大到小;
4).对多列进行排序:
例如:对多列进行排序:按金额排序,如果金额相同,按生产日期升序排序
SELECT * FROM product ORDER BY price ASC,proDate ASC;
先按第一个字段排序,在第一个字段值相同的情况下,再按第二个字段排。
5).如果有查询条件,写法:
select * from 表名 where 条件 order by 字段 … ;
04.SQL高级查询_聚合函数:
1.我经常会有需求,对某列进行汇总,这就需要使用"聚合函数";
2.今天我们掌握的五个聚合函数:
a).count(/字段名):统计指定列不为NULL的记录行数–任何数据类型
例如:查询电脑类别的商品,共有多少种
SELECT COUNT(
) FROM product WHERE categoryName = ‘电脑’;
b).sum(列名):计算指定列的数值和,如果指定列类型不是数值类型,那么计算结果为0–数值类型的列
例如:查询电脑类商品的价格总数是多少?
select sum(price) from product where categoryName = ‘电脑’;
c).max(列名):计算指定列的最大值,如果指定列是字符串类型,那么使用字符串排序运算–数值类型、日期类型
例如:查询电脑类商品的最高价格?
select max(price) from product where categoryName = ‘电脑’;
d).min(列名):计算指定列的最小值,如果指定列是字符串类型,那么使用字符串排序运算
例如:查询电脑类商品的最低价格?
select min(price) from product where categoryName = ‘电脑’;
e).avg(列名):计算指定列的平均值,如果指定列类型不是数值类型,那么计算结果为0
例如:查询电脑类商品的平均价格?
select avg(price) from product where categoryName = ‘电脑’;
注意:计算记录的总数量时,不包含NULL的记录。
所以如果计算的列中有NULL值,则结果不准确。
3.注意:聚合查询的结果,只能包含"聚合结果列",不要包含其他列,要包含,其结果是无意义的。
聚合的结果是"计算的结果",跟某行数据无关,所以不能关联显示其它字段。
05.SQL高级查询_分组:
1.分组:使用关键字GROUP BY,对某列中"相同的值"作为一组,进行分组。分组只是手段,后续经常需要进行汇总:
2.例如:一条语句查询出每种商品的最高价格是多少?
SELECT categoryName,MAX(price) FROM product GROUP BY categoryName;
练习:查询每种商品的价格的总和
SELECT categoryName,SUM(price) FROM product GROUP BY categoryName;
练习:查询每种商品的商品数量是多少
SELECT categoryName,COUNT(*) FROM product GROUP BY categoryname;
3.注意:
1).分组查询的结果字段中,只能包含"分组字段",“聚合结果字段”。不能再包含其他字段,如果包含,其结果也是无意义的。
4.having子句:
1).由于where不能对聚合后的结果进行筛选。所以要对聚合后的结果进行筛选,需要使用having子句。

例如:查询每种商品的价格总额,结果保留大于1000元的。
select categoryName,sum(price) from product group by categoryName having sum(price) > 1000;
5.对多列进行分组:
收支流水表:trans
id 收支项 账户 金额
1 工资收入 工商银行 1000
2 红包收入 工商银行 500
3 收入 交通银行 3000
4 支出 工商银行 300
5 支出 交通银行 770
需求:查询出每个账户的收支总额,分别是多少?
账户 收支项 总金额
工商银行 收入 1500
工商银行 支出 300
交通银行 收入 3000
交通银行 支出 770
select 账户,收支项,sum(金额) from trans group by 账户,收支项;//先按账户分,再按收支项分。
06.SQL语句的执行顺序:
1).from
2).where
3).group by
4).having
5).select
6).distinct
7).order by
SQL语句的编写顺序:
select … from … where … group by … having … order by …;
07.分页查询:(mysql方言)
1).基本语句:select * from 表名 limit M,N;
M值:从第几条(第一条记录为0)记录开始取。
N值:取几条记录
2).例如:查询所有的商品,每页显示5条:
第一页:
select * from product limit 0,5;
第二页:
select * from product limit 5,5;
第三页:
select * from product limit 10,5
固定算法:
select * from product limit (当前的页数 - 1) * 每页显示的条数
3).注意:M值和N值,只要是正数,不会抛异常,可能会返回空结果集。
但如果是负数,会抛异常。
08.备份和恢复数据库:
1).备份:在要备份的数据库上右键–>备份/导出–>以SQL转储文件备份数据库
2).恢复:在SQLYog左侧右键–>导入–>从SQL转储文件导入数据库
09.多表_分表的作用:
1.在制作表时要注意:一个表只描述一件事情。如果需要描述多件事情,可以创建多表,然后通过某个字段去引用另一个表的数据。
这样可以使每个表的数据单独管理,互不影响。
2.分表后:
主表:被其它表引用的表;
从表:引用其它表的表;
3.作用:
避免主键冲突,减少数据冗余
10.多表_表和表之间的关系:
1.一对多关系【最常用】:
1).应用场景:客户和订单;分类和商品;部门和员工
客户表:主表 订单表:从表 外键
----------------------------------------------------------------------------------------
客户ID 登录名 支付宝 订单ID 订单时间 总金额 客户ID
001 zhangsan xxx 001 xxx xxx 001
002 xxx xxx 001
2).建表原则:在从表(多方)创建一个字段,字段作为外键指向主表(一方)的主键.
2.多对多关系【较常用】:
1).应用场景:订单和商品、用户和角色
订单表 商品表:
-----------------------------------------------------------------------------------------
订单ID 订单日期 总金额 商品ID 名称 单价
d001 2017-07-04 100 p001 奥利奥 5.5
d002 2017-07-05 200 p002 红牛 4
p003 啤酒 2.00
订单_商品_关系表
订单Id 商品ID 数量 总价
d001 p001 2 11
d001 p002 3 12
d001 p003
d002 p001
2).建表原则:需要创建第三张表,中间表中至少两个字段,这两个字段分别作为外键指向各自一方的主键。
3.一对一关系【不建议用】:
1).客户信息表: 地址表
姓名 性别 年龄 地址ID id 省 市 区 街道门牌
张三 男 22 01 01 北京 北京 顺义 99号
李四 女 23 02 02 河北 廊坊 安次 88号
------------------------------------------------------------------------------------------------
合并为一个客户表:
姓名 性别 年龄 省 市 区 街道门牌
张三 男 22 北京 北京 顺义 99号
李四 女 23 河北 廊坊 安次 88号
11.外键约束:
1).作用:设置在"从表"的外键字段上,可以强制外键字段的值必须参考主表中的主键字段的值。
2).设置外键约束:
alter table 从表 add [constraint] [外键名称] foreign key (从表外键字段名) references 主表 (主表的主键);
3).使用外键目的:
保证数据完整性
4).删除外键
alter table 从表 drop foreign key 外键名称;
mysql方言
limit ? 取前面?个
limit 0,5 从0开始,取5个
12.多表查询:
1).多表查询_交叉查询【笛卡尔积,了解即可】
查询结果=左表的总记录数 * 右表的总记录数 – 笛卡尔积
2).多表查询_内连接查询【重点掌握】
1.隐式内连接【常用】:
1).格式:select 字段列表 from 表1,表2 where 表1和表2的等值关系;
2).例如:查询商品信息,要显示所对应类别信息
select * from products , category where products.category_id = catetory.cid;
只保留两个表的部分字段,使用表别名:
SELECT p.pname,p.price,c.cname FROM products p , category c WHERE p.category_id = c.cid;

2.显式内连接:

1).格式:select 字段列表 from 表1 INNER JOIN 表2 ON 等值关系;
2).例如:查询商品信息,要显示所对应类别信息
select * from products p inner join category c on p.category_id = c.cid;
注意:
1.内连接的查询结果:两个表中的等值记录;
2.两种内连接都可以再添加其它where条件:
隐式内连接:select … from 表1,表2 where 等值条件 and 其它条件…
显示内连接:select … from 表1 inner join 表2 on 等值条件 where 其它条件…
3.两种查询的格式说明:
隐式内连接:select … from 表1,表2 on 等值条件//错误
显示内连接:select … from 表1 inner join 表2 where 等值条件//OK的

3).多表查询_外连接查询【重点掌握】

1.左外连接查询:

1).格式:select 字段列表 from 表1 left join 表2 on 等值关系;
2).查询结果:左表的所有记录,和右表的等值记录;
3).例如:需求:查询出所有商品(包括没有类别的商品),有类别的商品要显示类别名称。
SELECT * FROM products p LEFT JOIN category c ON p.category_id = c.cid;

2.右外连接查询:
1).格式:select 字段列表 from 表1 right join 表2 on 等值关系;
2).查询结果:右表的所有记录,和左表中的等值记录;
3).例如:需求:查询出所有的商品类别,如果类别下有商品的,要同时显示商品信息;
SELECT * FROM products p RIGHT JOIN category c ON p.category_id = c.cid;

4).子查询【重点掌握】
1.在一个查询内部,可以再写一个查询,这个写在内部的查询就叫:子查询;
2.子查询的结果可以作为另一个查询:判断条件,表使用。
3.例子:查询价格高于"劲霸"的商品信息;
SELECT * FROM products WHERE price > (SELECT price FROM products WHERE pname = ‘劲霸’);
4.练习:
1).查询化妆品类别的商品信息
a).使用多表连接查询:
select * from products p , category c where p.category_id = c.cid and c.cname = ‘化妆品’;
b).使用子查询(单表查询)
SELECT * FROM products WHERE category_id = (SELECT cid FROM category WHERE cname = ‘化妆品’);
c).使用子查询作为第三张表:select * from (子查询)
SELECT * FROM products p ,(SELECT * FROM category WHERE cname = ‘化妆品’) c WHERE p.category_id = c.cid;
2).查询所有"家电","服饰"类商品的信息:
select * from products where category_id = 1 or category_id = 2;
改进:
select * from products where category_id in (1,2);
改进:
select * from products where category_id in (select cid from category where cname in (‘家电’,‘服饰’));

三表联查:
1.隐式内连接:使用user表,role表,user_role表进行测试
select * from users u , role r,user_role ur where u.uid = ur.uid and ur.rid = r.rid;
2.显式内连接:
select * from users u inner join user_role ur on u.uid = ur.uid inner join role r on ur.rid = r.rid;

六、数据完整性、数据完整性
数据完整性= 准确性+正确性。
保证数据完整性需要加约束。
七、约束
实体完整性(行级别)
主键约束 唯一 自增
域完整性 (列级别)
数据类型 not null 默认约束
引用完整性
外键约束。

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值