数据库的基本知识

数据库的基本知识
数据库的高阶知识

一、创建数据库和数据表

1.1 创建数据库相关代码

#创建数据库相关代码
CREATE DATABASE test;  -- 创建名为test的数据库
show CREATE DATABASE test; -- 查看创建好的数据库
show DATABASES;   -- 查看所有的数据库列表
use test;  -- 使用创建好的数据库
drop DATABASE test; -- 删除数据库

1.2 创建数据表

  数据库是由多个数据表构成的，每张数据表中存储着不同的字段，每个字段由不同的字段名和记录构成，并且每个字段有自己的数据结构和约束条件。
  在之前创建的test数据库中，创建一个员工信息表emp。

部门ID	部门名称	员工数
p01	财务部	20
p02	销售部	100
p03	运营部	40

-- 使用test数据库
use test;
-- 创建员工信息表
CREATE TABLE emp(depid char(3),depname varchar(20),peoplecount int);			
-- 查看表是否创建成功
show tables
-- 删除数据表
drop table emp

1.3 约束条件

  约束是在表上强制执行的数据检验规则，用来保证创建的表的数据完整和正确。
mysql数据库常用的约束条件：

约束条件	说明	语法
PRIMARY KEY	主键约束	字段名 数据类型 PRIMARY KEY
NOT NULL	非空约束	字段名 数据类型 NOT NULL
UNIQUE	唯一约束	字段名 数据类型 UNIQUE
AUTO_INCREMENT	自增字段	字段名 数据类型 AUTO_INCREMENT
DEFAULT	默认值	字段名 数据类型 DEFAULT 默认值

1.3.1 主键约束

  主键，一列（或一组列），其值能够唯一区分表中每个行。主键约束：保证表中每行记录都不重复。
  主键，又称为"主码”，是数据表中一列或多列的组合。 主键的作用就是用来表示一个特定的行，没有主键，更新或者删除表中特定的行会比较困难，因为没有安全的方法保证只涉及相关的行。 使用主键，能够惟一地标识表中的一条记录，并且可以结合外键来 定义不同数据表之间的关系，还可以加快数据库查询的速度。
  原则上，表中的任何列都可以作为主键，只需要满足以下条件：

• 任何两行都不具有相同的主键值（唯一）
• 每个行都必须具有一个主键值，且主键值不允许NULL值（非空）

  主键分为两种类型:单字段主键和多字段主键。对于单字段主键，主键通常定义在表的一列上，比如:对于下面的员工表来说部门ID就可以作为主键。对于多字段主键，必须保证构成主键的所有列值的组合是唯一的(单个列值可以不唯一)。

# 主键约束
-- 单字段主键
CREATE TABLE emp(depid char(3) PRIMARY KEY,depname varchar(20),peoplecount int);
-- drop table emp		
-- 多字段主键
CREATE TABLE emp(depid char(3) PRIMARY KEY,depname varchar(20),peoplecount int,primary key (depid,depname));

1.3.2 非空约束

  非空约束，指的是字段的值不能为空。语法：字段名 字段类型 not null

CREATE TABLE emp(depid char(3) PRIMARY KEY,depname varchar(20) not null ,peoplecount int);

1.3.3 唯一性约束

  唯一性约束，要求该列的值必须是唯一的，语法：字段名 数据类型 unique；

• 允许为空，但只能出现一个空值；
• 一个表中可以有多个字段声明为唯一值；
• 唯一约束确保数据表的一列或几列不出现重复值

CREATE TABLE emp(depid char(3) PRIMARY KEY,depname varchar(20) not null ,peoplecount int unique);

异同点	主键	唯一键
不同点	设置为主键的字段不能为NULL	可以为NULL
不同点	一张表有且仅有一个主键	可以有多个唯一键
相同点	设置为主键的字段值不能重复	唯一键字段值不能重复
相同点	构造为主键的字段可以是多个	唯一键字段值可以是多个

1.3.4 默认约束

  默认约束，指定某个字段的默认值。如果新插入一条记录时没有为默认约束字段赋值，那么系统就回自动为这个字段赋值为默认约束设定的值。语法：字段名 数据类型 default 默认值

CREATE TABLE emp(depid char(3) PRIMARY KEY,depname varchar(20) not null DEFAULT '-' ,peoplecount int unique);

1.3.5 自增字段

  自增字段：一个表只能有一个自增字段，自增字段必须为主键的一部分，默认情况下从1开始自增。
创建含有各种约束条件的数据表

# 创建一个含有各种约束条件的数据表
create table exp (id int primary key auto_increment,fname varchar(4) not null,math int default 0,minmax float unique);

1.4 手工建表

create table fruits(f_id char(10) not null,s_id int not null default 0,f_name char(255),f_price decimal(8,2) not null,primary key(f_id));
insert into fruits(f_id,s_id,f_name,f_price) values ("a1",101,"apple",5.2),("a2",102,"orange",10.2);				

二、数据查询功能

2.1 sql 查询的7个关键词

  在实际工作中，使用最多的就是数据库的查询功能了，在数据查询过程中，通常会用到以下7个核心关键词：

select <select_list>
from <table_list>
[where <where_condition>]
[group by <group_by_list>]
[having <having_condition>]
[order by <order_by_list> asc|desc]
[limit <limit_number>]

  其中，select 和 from 两个关键词时必选项，也就是说，只要是数据查询功能，都需要使用这两个关键词，其他的关键词，可以根据查询实际情况有针对性的选择和使用。二手房数据链接 ：https://pan.quark.cn/s/586cf3750a24

create table second_hand_houses(frame char(10) not null,ftype char(255),size int,region char(10) not null,floor int,drection char(10) not null,bulit_data char(10) not null,price_unit int)

2.1.1 select

   select主要作用是告诉数据库，在提取数据时需要选择的字段名称。这里的字段名称可以是数据表已经有的名称，也可以是依据已有名称衍生出来的名称。

2.1.2 from

  from主要作用就是告诉数据库，在提取数据时需要选择的数据源（数据表）名称，通常select和from两个关键词一起使用

2.1.3 where

#查询北京所有2室1厅的二手房信息
select * from second_hand_houses where ftype = '2室1厅'#查询朝阳区房价单价在10000以上的二手房名称，户型，面积，朝向，楼层
select frame,ftype,size,drection,floor
from second_hand_houses
where region ='朝阳区'
and price_unit>50000

2.1.4 group by

   主要用于统计时的分组操作，通常和聚合函数一起搭配使用，主要有以下五种：

聚合函数	功能	说明
count	用于统计记录数	函数内传递*，表示统计数据表的记录数；如果是某个字段，则统计字段的记录数，如果需要去重，需在字段前加关键字distinct
sum	用于数值型数据的求和	需要注意的是变量求和之后，仍然具有业务含义
avg	用于数值型数据的均值计算	同上
min	用于计算数值型数据的最小值	-
max	用于计算数值型数据的最大值	-

#查询各地区下二手房数量、总的可居住面积、最大单价
select region,count(*) as counts,sum(size) as tot_size,max(price_unit) as max_price
from second_hand_houses
group by region#查询各地区下每年二手房数量、总的可居住面积、最大单价
select region,bulit_data,count(*) as counts,sum(size) as tot_size,max(price_unit) as max_price
from second_hand_houses
group by region,bulit_data

2.1.5 having

   HAVING 用于对分组（GROUP BY）后的结果进行过滤。它通常与聚合函数（如 COUNT、SUM、AVG、MAX、MIN 等）一起使用，用于筛选满足条件的分组。
与 WHERE 的区别：

• WHERE：在分组之前过滤数据，作用于单行记录。
• HAVING：在分组之后过滤数据，作用于分组结果。

#按照地区、户型、楼层和阳台朝向分组统计东城区、朝阳区二手房的平均单价和总数量
select region,ftype,floor,drection,avg(price_unit) as avg_price,count(*) as counts
from second_hand_houses
where region in ("东城区","朝阳区")
group by region,ftype,floor,drection
having avg_price >50000;

2.1.6 order by

   用于对查询结果进行排序。你可以指定一个或多个字段，并选择升序（ASC，默认）或降序（DESC）排列。

#按面积降序，单价升序的方式查询所有2室1厅的二手房信息
select * from second_hand_houses
where ftype ="2室1厅"
order by size desc,price_unit

2.1.7 limit

   用于限定查询返回的记录行数，可以选择返回前几行，中间几行，或者是末尾几行，需要注意的是这个关键词后面最多可以写两个整数型的值：
limit n：表示返回查询结果的前n行
limit m,n：表示从第m+1行开始，连续返回n行的数据

#查询建筑时间最悠久的前5套二手房
select * from second_hand_houses
order by bulit_data
limit 5#查询出东城区，1980年建的二手房，并且单价在2-3名
select * from second_hand_houses
where region ="丰台区" and bulit_data = "2008"
order by price_unit desc
limit 1,2