数据库的基本知识

数据库的基本知识
数据库的高阶知识
一、创建数据库和数据表

1.1 创建数据库相关代码

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

复制代码

1.2 创建数据表

  数据库是由多个数据表构成的，每张数据表中存储着不同的字段，每个字段由不同的字段名和记录构成，而且每个字段有本身的数据结构和束缚条件。
  在之前创建的test数据库中，创建一个员工信息表emp。
部门ID部门名称员工数p01财务部20p02销售部100p03运营部40

1. -- 使用test数据库
2. use test;
3. -- 创建员工信息表
4. CREATE TABLE emp(
5.                         depid char(3),
6.                         depname varchar(20),
7.                         peoplecount int);                       
8. -- 查看表是否创建成功
9. show tables
10. -- 删除数据表
11. drop table emp

复制代码

1.3 束缚条件

  束缚是在表上强制执行的数据检验规则，用来包管创建的表的数据完整和精确。
mysql数据库常用的束缚条件：
束缚条件说明语法PRIMARY KEY主键束缚字段名 数据类型 PRIMARY KEYNOT NULL非空束缚字段名 数据类型 NOT NULLUNIQUE唯一束缚字段名 数据类型 UNIQUEAUTO_INCREMENT自增字段字段名 数据类型 AUTO_INCREMENTDEFAULT默认值字段名 数据类型 DEFAULT 默认值 1.3.1 主键束缚

  主键，一列（或一组列），其值可以或许唯一区分表中每个行。主键束缚：包管表中每行记录都不重复。
  主键，又称为"主码”，是数据表中一列或多列的组合。 主键的作用就是用来体现一个特定的行，没有主键，更新大概删除表中特定的行会比较困难，由于没有安全的方法包管只涉及相关的行。 利用主键，可以或许惟一地标识表中的一条记录，而且可以结合外键来 界说不同数据表之间的关系，还可以加快数据库查询的速率。
  原则上，表中的任何列都可以作为主键，只需要满足以下条件：

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

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

1. # 主键约束
2. -- 单字段主键
3. CREATE TABLE emp(
4.                         depid char(3) PRIMARY KEY,
5.                         depname varchar(20),
6.                         peoplecount int);
7. -- drop table emp               
8. -- 多字段主键
9. CREATE TABLE emp(
10.                         depid char(3) PRIMARY KEY,
11.                         depname varchar(20),
12.                         peoplecount int,
13.                         primary key (depid,depname));

复制代码

1.3.2 非空束缚

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

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

复制代码

1.3.3 唯一性束缚

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

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

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

复制代码

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

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

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

复制代码

1.3.5 自增字段

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

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

复制代码

1.4 手工建表

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

复制代码

二、数据查询功能

2.1 sql 查询的7个关键词

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

1. select <select_list>
2. from <table_list>
3. [where <where_condition>]
4. [group by <group_by_list>]
5. [having <having_condition>]
6. [order by <order_by_list> asc|desc]
7. [limit <limit_number>]

复制代码

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

1. create table second_hand_houses(
2.                                                         frame char(10) not null,
3.                                                         ftype char(255),
4.                                                         size int,
5.                                                         region char(10) not null,
6.                                                         floor int,
7.                                                         drection char(10) not null,
8.                                                         bulit_data char(10) not null,
9.                                                         price_unit int)

复制代码

2.1.1 select

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

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

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

复制代码

2.1.4 group by

   主要用于统计时的分组操作，通常和聚合函数一起搭配利用，主要有以下五种：
聚合函数功能说明count用于统计记录数函数内传递*，体现统计数据表的记录数；如果是某个字段，则统计字段的记录数，如果需要去重，需在字段前加关键字distinctsum用于数值型数据的求和需要留意的是变量求和之后，仍然具有业务寄义avg用于数值型数据的均值盘算同上min用于盘算数值型数据的最小值-max用于盘算数值型数据的最大值-

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

复制代码

2.1.5 having

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

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

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

复制代码

2.1.6 order by

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

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

复制代码

2.1.7 limit

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

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

复制代码

2.2 with语句

   with语句是 SQL 中用于界说临时结果集的一种语法。它可以让复杂的查询变得更清楚、更易读，而且可以重复利用这些临时结果集。WITH 语句通常用于简化嵌套子查询，大概在递归查询中处理条理结构数据。

1. WITH cte_name (column1, column2, ...) AS (
2.     -- 子查询
3.     SELECT ...
4. )
5. SELECT ... FROM cte_name;
7. cte_name: CTE 的名称，可以在后续查询中引用。
8. (column1, column2, ...): 可选项，为 CTE 的结果集指定列名。如果省略，列名将来自子查询的 SELECT 语句。
9. AS (...): 定义 CTE 的子查询。

复制代码

利用场景
1、简化复杂查询：
当查询中有多个嵌套子查询时，可以利用 WITH 语句将每个子查询提取为 CTE，使查询更易读。
2、递归查询：
WITH 语句支持递归查询，常用于处理条理结构数据（如构造结构、树形结构等）。
3、重复利用结果集：
如果某个子查询在多个地方被利用，可以将其界说为 CTE，避免重复编写相同的子查询。

1. WITH order_totals AS (
2.     SELECT
3.         customer_id,
4.         SUM(amount) AS total_amount
5.     FROM orders
6.     GROUP BY customer_id
7. )
8. SELECT
9.     c.customer_name,
10.     ot.total_amount
11. FROM customers c
12. LEFT JOIN order_totals ot ON c.customer_id = ot.customer_id;

复制代码

三、数据库的增编削

3.1 数据库的增操作

   数据库的增操作主要涉及数据库的增长、数据表的增长、表记录增长以及表字段增长等。数据库的增长非常简朴，就是新创建一个数据库;表记录的增长指的就是新增表的数据行，可以是在已有表的基础上增长记录，也可以是将查询结果生存为新的表;字段的增长是指在原有表的基础上新增一些字段。
（1）新建数据库

1. create database <database_name>

复制代码

（2）新建表

1. create table table_name (column_name column_type)

复制代码

（3）在原有表的基础上增长记录
   在表中增长记录的条件是包管原数据表一定存在。

1. #创建学生信息表
2. CREATE TABLE stu_info (
3.     id INT AUTO_INCREMENT PRIMARY KEY, -- 学生ID，自增主键
4.     iname VARCHAR(20),                 -- 学生姓名
5.     gender CHAR(1),                    -- 性别（M: 男, F: 女）
6.     department VARCHAR(10),            -- 所属院系
7.     age INT,                           -- 年龄
8.     province VARCHAR(10),              -- 省份
9.     email VARCHAR(50),                 -- 邮箱
10.     mobilephone CHAR(11)               -- 手机号（11位）
11. );
12. # create table stu_order like stu_info 创建相似的表
13. INSERT INTO stu_info (iname, gender, department, age, province, email, mobilephone)
14. VALUES
15. ('周八', 'M', '计算机', 21, '北京', 'zhouba@example.com', '13800138005'),
16. ('武勇', 'M', '生物', 21, '浙江', 'wuyong@example.com', '13800138049');

复制代码

（4）增长列

1. alter table stu_info add column hobby char(20) default 0

复制代码

3.2 数据库的删操作

   数据库的删操作主要包含数据库的删除、数据表的删除、表记录删除、字段删除类型。数据库的删除就是直接删掉整个数据库;表记录的删除是指按照某些条件删除数据表中的记录，大概直接清空数据表的所有记录;字段的删除与字段的增长恰好相反，就是根据实际情况将表中某些字段删掉。
（1）数据库删除

1. drop database <databsae_name>

复制代码

（2）数据表删除

1. drop table <table_name>

复制代码

（3）按条件删除表记录

1. #删除学生表在，名为“张三“和”李四”的记录
2. delete from stu_info  #保留自增字段记录；如果需要删除部分数据或需要事务支持，使用 DELETE。
3. where iname in("张三","李四")
5. #删除学生表中email 字段
6. alter table stu_info drop email
8. #truncate #彻底清除字段表；如果需要快速清空整个表，使用 TRUNCATE
9. truncate table stu_info

复制代码

3.3 数据库的改操作

   数据库的改操作主要是指修改表中错误记录、修改字段类型以及修改表名称、字段名称等。
(1) 修改表名

1. alter table stu_info rename to stu_summary

复制代码

（2）修改字段

1. alter table stu_info rename to stu_summary

复制代码

（3）修改字段类型

1. alter table stu_summary change age age varchar(10)
2. alter table stu_summary change gender sex varchar(10)
4. alter table stu_summary modify column age varchar(10)
5. desc stu_summary #查看表字段类型

复制代码

（4）修改单个记录

1. update stu_summary set email='zhangyong@163.com' where iname ='张勇'

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。

继续阅读请点击广告