数据库从入门到精通01

迷南。 2021-07-16 14:40 480阅读 0赞

### 文章目录 ###

*  数据库应用
 *   *  概念
     *   *  什么是数据库
         *  关系型和非关系型
         *  关系型数据库
     *  Mysql数据库
     *   *  MySQL数据存放在哪里？
         *  MySQL服务端
         *  MySQL客户端1：DOS窗口
         *  MySQL客户端2：可视化工具
     *  数据库的结构
     *   *  数据库结构
     *  SQL语句
     *   *  定义
         *  分类
     *  数据库常用操作
     *   *  建库
         *  删库
         *  查看所有数据库
     *  表的常用操作
     *   *  表设计
         *  创建表
         *  修改表
         *  删除表
         *  查看所有表
         *  查看表结构/设计表
     *  表记录的常用操作
     *   *  插入记录
         *  查询记录
         *  修改记录
         *  删除记录
         *  排序
         *  记录总数
     *  数据类型
     *   *   *  命名规则
             *  字符
             *  数字
             *  日期
             *  图片
     *  字段约束
     *   *   *  主键约束
             *  非空约束
             *  唯一约束
     *  准备数据
     *   *   *  部门表 dept
             *  员工表 emp
     *  基础函数
     *   *  lower
         *  upper
         *  length
         *  substr
         *  concat
         *  replace
         *  ifnull
         *  round & ceil & floor
         *  uuid
         *  now
         *  year & month & day
         *  转义字符

# 数据库应用 #

## 概念 ##

### 什么是数据库 ###

简而言之，就是存储数据，管理数据的仓库。

常见的数据库分为：

*  关系型数据库， Oracle、MySQL、SQLServer、Access
 *  非关系型数据库， MongoDB、Redis、Solr、ElasticSearch、Hive、HBase  
    ![在这里插入图片描述][20210429131716634.gif]

### 关系型和非关系型 ###

早期发展的数据库建立在数据的紧密关系基础之上（如：父子关系、师生关系），我们称其为关系型数据库，也称为传统数据库；现今数据库建立在数据的松散关系基础之上（如：中国人和美国人、中国人和印度人、视频、音频），我们称其为非关系型数据库nosql（not only sql）。业界总在争论nosql能否干掉传统数据库，很多初学者也有这个困惑。以我来看，两者没有矛盾，它们各有特点，根据业务情况互补才是真谛。但总的来说原来关系型数据库一统天下的格局早被打破，领土不断被蚕食，规模一再的缩小，虽然无法全面被替代，但却早已风光不在，沦落到一偶之地，Oracle的衰落就是最好的证明，早期只要是全球大企业无一例外都是部署Oracle，但现在都在去Oracle化，阿里就已经全面排斥Oracle。  
![在这里插入图片描述][20210429131922134.png]

既然干不掉，很多传统项目的还是围绕关系型数据库的居多，所以我们先来学习关系型数据库，目前最流行的关系型数据库是MySQL。

### 关系型数据库 ###

关系型数据库有特定的组织方式，其以行和列的形式存储数据，以便于用户理解。关系型数据库这一系列的行和列被称为表，一组表组成了数据库。用户通过查询来检索数据库中的数据，而查询是一个用于限定数据库中某些区域的执行代码。关系模型可以简单理解为二维表格模型，而一个关系型数据库就是由二维表及其之间的关系组成的一个数据集合。  
![在这里插入图片描述][20210429132042258.png]

## Mysql数据库 ##

1.  mysql服务端，它来处理具体数据维护，保存磁盘
2.  mysql客户端，CRUD新增，修改，删除，查询

### MySQL数据存放在哪里？ ###

在MySQL的配置文件my.ini中会进行默认配置

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTI5MzI4NzY_size_16_color_FFFFFF_t_70]

### MySQL服务端 ###

mysql-5.5.27-winx64.msi

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTI5MzI4NzY_size_16_color_FFFFFF_t_70 1]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTI5MzI4NzY_size_16_color_FFFFFF_t_70 2]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTI5MzI4NzY_size_16_color_FFFFFF_t_70 3]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTI5MzI4NzY_size_16_color_FFFFFF_t_70 4]  
Mysql数据库默认的编码是latin1等价于iso-8859-1，修改为utf-8

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTI5MzI4NzY_size_16_color_FFFFFF_t_70 5]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTI5MzI4NzY_size_16_color_FFFFFF_t_70 6]  
注意：配置完，mysql开始执行，最后一步出错有时仍可以使用，使用SQLyog工具测试，如不行，再执行安装程序，选择remove，删除，然后重新安装。同时注意必须是管理员权限。

### MySQL客户端1：DOS窗口 ###

**mysql -uroot -proot**

语法：mysql.exe执行文件

代表参数

\-u 用户名，紧接着写的

\-p 密码，紧接着写的

### MySQL客户端2：可视化工具 ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTI5MzI4NzY_size_16_color_FFFFFF_t_70 7]

## 数据库的结构 ##

### 数据库结构 ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTI5MzI4NzY_size_16_color_FFFFFF_t_70 8]

## SQL语句 ##

### 定义 ###

结构化查询语言(Structured Query Language)简称SQL(发音：/ˈes kjuː ˈel/ "S-Q-L")，是一种特殊目的的编程语言，是一种数据库查询和[程序设计语言][Link 1]，用于存取数据以及查询、更新和管理[关系数据库系统][Link 2]；同时也是[数据库脚本文件][Link 3]的扩展名。

SQL 是1986年10 月由[美国国家标准局][Link 4]（[ANSI][]）通过的[数据库][Link 5]语言美国标准，接着，国际标准化组织（[ISO][]）颁布了SQL正式国际标准。

### 分类 ###

*  DML（Data Manipulation Language）数据操纵语言

如：insert，delete，update，select（插入、删除、修改、检索）简称CRUD操新增Create、查询Retrieve、修改Update、删除Delete

*  DDL（Data Definition Language）数据库定义语言

如：create table之类

*  DCL（Data Control Language）数据库控制语言

如：grant、deny、revoke等，只有管理员才有相应的权限

*  [数据][Link 6]查询语言（[DQL][]:Data Query Language）：

注意：SQL不区分大小写

## 数据库常用操作 ##

### 建库 ###

*  创建数据库，数据库名称：cbg2011

create database cbg2011 DEFAULT CHARACTER SET utf8;

### 删库 ###

*  删除名称是cbg2011的数据库

drop database cbg2011;

### 查看所有数据库 ###

*  查看所有数据库

show databases;

## 表的常用操作 ##

使用数据库：use cbg2011;

### 表设计 ###

**门店表：**\*\* tb\_door\*\*  
![在这里插入图片描述][20210429132729575.png]  
**订单详情表：**\*\* tb\_order\_detail\*\*  
![在这里插入图片描述][2021042913274619.png]

### 创建表 ###

*  创建tb\_door表，有id,door\_name,tel字段

create table tb_door(
    id int primary key auto_increment,
    door_name varchar(100),
    tel varchar(50)
    );

### 修改表 ###

*  添加列

alter table tb_door add column money NUMERIC(7,2)

### 删除表 ###

*  删除名称是tb\_door的表

drop table tb_door;

### 查看所有表 ###

*  查看所有表

show tables;

### 查看表结构/设计表 ###

*  查看tb\_door表结构

desc tb_door;

## 表记录的常用操作 ##

### 插入记录 ###

*  向tb\_door表中插入2条记录

insert into tb_door values(null,'永和大王1店',666);
    
    insert into tb_door values(null,' 永和大王2店',888);

### 查询记录 ###

*  查询tb\_door表中的所有记录

SELECT * FROM tb_door;

### 修改记录 ###

*  修改tb\_door表中id为1的记录

update tb_door set tel=555 where id=1;

### 删除记录 ###

*  删除tb\_door表中id为2的数据

Delete from tb_door where id=2;

### 排序 ###

*  将tb\_door表记录按照tel排序

Select * from tb_door order by tel desc;

### 记录总数 ###

*  查询tb\_door表中的总记录数

Select count(*) from tb_door;

## 数据类型 ##

#### 命名规则 ####

*  字段名必须以字母开头，尽量不要使用拼音
 *  长度不能超过30个字符（不同数据库，不同版本会有不同）
 *  不能使用SQL的保留字，如where,order,group
 *  只能使用如下字符az、AZ、0~9、$ 等
 *  Oracle习惯全大写：USER\_NAME，mysql习惯全小写：user\_name
 *  多个单词用下划线隔开，而非java语言的驼峰规则

#### 字符 ####

*  char长度固定，不足使用空格填充，最多容纳2000个字符，char(11)存储abc，占11位。查询速度极快但浪费空间
 *  varchar变长字符串，最多容纳4000个字符，varchar(11)存储abc，只占3位。查询稍慢，但节省空间。Oracle为varchar2
 *  大文本: 大量文字（不推荐使用，尽量使用varchar替代）

以utf8编码计算的话，一个汉字在u8下占3个字节

注：不同数据库版本长度限制可能会有不同

#### 数字 ####

*  tinyint,int整数类型
 *  float,double小数类型
 *  numberic(5,2) decimal(5,2)—也可以表示小数,表示总共5位,其中可以有两位小数
 *  decimal和numeric表示精确的整数数字

#### 日期 ####

*  date 包含年月日
 *  time时分秒
 *  datetime包含年月日和时分秒
 *  timestamp时间戳，不是日期，而是从1970年1月1日到指定日期的毫秒数

#### 图片 ####

*  blob 二进制数据，可以存放图片、声音，容量4g。早期有这样的设计。但其缺点非常明显，数据库庞大，备份缓慢，这些内容去备份多份价值不大。同时数据库迁移时过大，迁移时间过久。所以目前主流都不会直接存储这样的数据，而只存储其访问路径，文件则存放在磁盘上。

## 字段约束 ##

#### 主键约束 ####

主键约束：如果为一个列添加了主键约束，那么这个列就是主键，主键的特点是唯一且不能为空。通常情况下，每张表都会有主键。

添加主键约束，例如将id设置为主键：

**主键自增策略**\*\* \*\*当主键为数值类型时，为了方便维护，可以设置主键自增策略（auto\_increment），设置了主键自增策略后，数据库会在表中保存一个AUTO\_INCREMENT变量值，初始值为1，当需要id值，不需要我们指定值，由数据库负责从AUTO\_INCREMENT获取一个id值，作为主键值插入到表中。而且每次用完AUTO\_INCREMENT值，都会自增1. AUTO\_INCREMENT=1

create table abc(
    id int primary key auto_increment
    );
    insert into abc values(null);
    insert into abc values(null);
    insert into abc values(null);
    select * from abc;

#### 非空约束 ####

非空约束：如果为一个列添加了非空约束，那么这个列的值就不能为空，但可以重复。

添加非空约束，例如为password添加非空约束：

create table user(
    id int primary key auto_increment,
    password varchar(50) not null
    );
    show tables;
    insert into user values(null,null);//不符合非空约束
    insert into user values(null,123;);//OK

#### 唯一约束 ####

唯一约束：如果为一个列添加了唯一约束，那么这个列的值就必须是唯一的（即不能重复），但可以为空。

添加唯一约束，例如为username添加唯一约束及非空约束：

create table test(
    id int primary key auto_increment,
    username varchar(50) unique--唯一约束
    );
    show tables;
    insert into test values(null,'lisi');
    insert into test values(null,'lisi');--username的值要唯一,重复会报错的
    select * from test;

## 准备数据 ##

#### 部门表 dept ####

CREATE TABLE dept(
    deptno int primary key auto_increment ,
    dname VARCHAR(20),
    loc VARCHAR(13)
    );
    INSERT INTO dept VALUES(null,'accounting','一区');
    INSERT INTO dept VALUES(null,'research','二区');
    INSERT INTO dept VALUES(null,'operations','二区');

#### 员工表 emp ####

<table> 
 <thead> 
  <tr> 
   <th><strong>字段名称</strong></th> 
   <th><strong>数据类型</strong></th> 
   <th><strong>是否为空</strong></th> 
   <th><strong>备注</strong></th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>empno</td> 
   <td>int</td> 
   <td></td> 
   <td>员工编号，PK主键</td> 
  </tr> 
  <tr> 
   <td>ename</td> 
   <td>varchar(10)</td> 
   <td>Y</td> 
   <td>员工名称</td> 
  </tr> 
  <tr> 
   <td>job</td> 
   <td>varchar(10)</td> 
   <td>Y</td> 
   <td>职位</td> 
  </tr> 
  <tr> 
   <td>mgr</td> 
   <td>int</td> 
   <td>Y</td> 
   <td>上级编号</td> 
  </tr> 
  <tr> 
   <td>hiredate</td> 
   <td>datetime</td> 
   <td>Y</td> 
   <td>入职时间</td> 
  </tr> 
  <tr> 
   <td>sal</td> 
   <td>double</td> 
   <td>Y</td> 
   <td>月工资</td> 
  </tr> 
  <tr> 
   <td>comm</td> 
   <td>NUMERIC(8,2)</td> 
   <td>Y</td> 
   <td>奖金</td> 
  </tr> 
  <tr> 
   <td>deptno</td> 
   <td>int</td> 
   <td>Y</td> 
   <td>所属部门 FK外键</td> 
  </tr> 
 </tbody> 
</table>

Mysql:

CREATE TABLE emp(
    empno int primary key auto_increment,
    ename VARCHAR(10),
    job VARCHAR(10),
    mgr int,
    hiredate DATE,
    sal double,
    comm NUMERIC(7,2),
    deptno int
    );

INSERT INTO emp VALUES(100,'jack','副总',NULL,'2002-05-1',90000,NULL,1);
    
    INSERT INTO emp VALUES(200,'tony','总监',100,'2015-02-02',10000,2000,2);
    
    INSERT INTO emp VALUES(300,'hana','经理',200,'2017-02-02',8000,1000,2);
    
    INSERT INTO emp VALUES(400,'leo','员工',300,'2019-02-22',3000,200.12,2);
    
    INSERT INTO emp VALUES(500,'liu','员工',300,'2019-03-19',3500,200.58,2);

## 基础函数 ##

### lower ###

SELECT 'ABC',LOWER('ABC') from dept; --数据转小写

### upper ###

select upper(dname) from dept --数据转大写

### length ###

select length(dname) from dept --数据的长度

### substr ###

SELECT dname,SUBSTR(dname,1,3) FROM dept; --截取[1,3]

### concat ###

select dname,concat(dname,'123') X from dept --拼接数据

### replace ###

select dname,replace(dname,'a','666') X from dept --把a字符替换成666

### ifnull ###

select ifnull(comm,10) comm from dept2 #判断,如果comm是null,用10替换

### round & ceil & floor ###

round四舍五入，ceil向上取整，floor向下取整

–直接四舍五入取整

select comm,round(comm) from emp

–四舍五入并保留一位小数

select comm,round(comm,1) from emp

–ceil向上取整,floor向下取整

select comm,ceil(comm) ,floor(comm) from emp

### uuid ###

SELECT UUID()

返回uuid：a08528ca-741c-11ea-a9a1-005056c00001

### now ###

select now() -- 年与日 时分秒
    
    select curdate() --年与日
    
    select curtime() --时分秒

### year & month & day ###

–hour()时 minute()分 second()秒

select now(),hour(now()),minute(now()),second(now()) from emp ;

–year()年 month()月 day()日

select now(),year(now()),month(now()),day(now()) from emp ;

### 转义字符 ###

'作为sql语句符号，内容中出现单撇就会乱套，进行转义即可

select 'ab'cd' -- 单引号是一个SQL语句的特殊字符
    
    select 'ab\'cd' --数据中有单引号时,用一个\转义变成普通字符

[20210429131716634.gif]: /images/20210716/bd934a07557d4c188899d14bb4d80a36.png
[20210429131922134.png]: /images/20210716/aa4092ca245547939750732b599483f1.png
[20210429132042258.png]: /images/20210716/4b1f5985982041aeb69821e05a230637.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTI5MzI4NzY_size_16_color_FFFFFF_t_70]: /images/20210716/c500d11feca6468ab7d7211f4b14c596.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTI5MzI4NzY_size_16_color_FFFFFF_t_70 1]: /images/20210716/d490f80baafd46168bd299de1d274a5b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTI5MzI4NzY_size_16_color_FFFFFF_t_70 2]: /images/20210716/66baec5373cd45d49fef06da2afddbf5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTI5MzI4NzY_size_16_color_FFFFFF_t_70 3]: /images/20210716/fc833624a3f24c0280e923c670ba7262.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTI5MzI4NzY_size_16_color_FFFFFF_t_70 4]: /images/20210716/b8678a7058234025a8937f5f97fd58b6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTI5MzI4NzY_size_16_color_FFFFFF_t_70 5]: /images/20210716/5de7a249b613415080f79935a433e12c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTI5MzI4NzY_size_16_color_FFFFFF_t_70 6]: /images/20210716/d068dd56b7f2415ba03b1753d77bb98d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTI5MzI4NzY_size_16_color_FFFFFF_t_70 7]: /images/20210716/fc89ab6bef9541dca7a2b8bb71269995.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTI5MzI4NzY_size_16_color_FFFFFF_t_70 8]: /images/20210716/49fb117bc12045218b0e789d3b8e5070.png
[Link 1]: https://baike.baidu.com/item/%E7%A8%8B%E5%BA%8F%E8%AE%BE%E8%AE%A1%E8%AF%AD%E8%A8%80
[Link 2]: https://baike.baidu.com/item/%E5%85%B3%E7%B3%BB%E6%95%B0%E6%8D%AE%E5%BA%93%E7%B3%BB%E7%BB%9F
[Link 3]: https://baike.baidu.com/item/%E6%95%B0%E6%8D%AE%E5%BA%93%E8%84%9A%E6%9C%AC%E6%96%87%E4%BB%B6
[Link 4]: https://baike.baidu.com/item/%E7%BE%8E%E5%9B%BD%E5%9B%BD%E5%AE%B6%E6%A0%87%E5%87%86%E5%B1%80
[ANSI]: https://baike.baidu.com/item/ANSI
[Link 5]: https://baike.baidu.com/item/%E6%95%B0%E6%8D%AE%E5%BA%93
[ISO]: https://baike.baidu.com/item/ISO
[Link 6]: https://baike.baidu.com/item/%E6%95%B0%E6%8D%AE
[DQL]: https://baike.baidu.com/item/DQL
[20210429132729575.png]: /images/20210716/3ee20a2c19a34642b9b9f929581233e6.png
[2021042913274619.png]: /images/20210716/68a88b807f3b4ff181f7c97fc11f0dad.png