数据库基础

### 【database数据库基础】
1、什么是数据库？
保存有组织的数据的容器，简单理解为一个文件柜，有组织的管理各种文件。
2、DBMS数据库管理软件——管理数据库的软件
ORACLE、SQLSERVER、MySQL

用户——>应用系统
应用系统——（交互）数据库管理系统<——数据库管理员
数据库管理系统——数据库

【MySQL】
默认端口3306（my.ini文件中修改port）
数据库可视化工具
navicat、workbench
1、navicat安装和激活
2、课程使用的数据库脚本
//create.sql用于创建数据库与数据表
//populate.sql用户添加数据库中的数据
导入脚本：运行SQL文件
3、
数据表：表名唯一
表结构：一列或多列组成（表字段数据）
表字段约束：type、null、key、...
查看表结构语句：desc {表名}

### 【SQL语句】
关键字不区分大小写；注释符：#；--空格；/* * /；列=字段

>//查看数据库
show databases;
//切换使用某个数据库
use {库名};
//查看数据库中的数据表
show tables;
//查看数据表的表结构
desc {表名};

#### 》1、查询——select
>格式：select {[列名1],[列名2...]} from {表名}；
- 1.1结果去重——distinct

>格式：select distinct {} from {}；
- 1.2设定结果返回的行数——limit n,m
n[0,m]
返回第n行
返回从第n行开始到m行
- 1.3结果排序——order by
//默认为升序ASC(字母A~Z，数字小~大)
//降序DESC

>格式：select {[列名1],[列名2...]} from {表名} order by {[字段名1],[字段名2...]} desc；

//asc/desc只作用于其前面的字段
//多个字段名则按所列出的字段顺序排序，如:先排字段名1，再按照字段名1的顺序排字段名2...
- 1.4组合应用

>格式：select * from {表} order by {列} limit 1;
---
- 1.5结果过滤——where

>格式：select {} from {} where 列满足的条件;
- 1.5.1使用操作符：
=等于,>大于，<小于，>=大于等于，<=小于等于
<>不等于
!=不等于
__between__ n __and__ m 选择n到m的__\[n,m]__
//字符串需加引号
- 1.5.2同时满足条件——AND

>格式：select {列} from {表} where 条件1 and 条件2 and 条件3...;
- 1.5.3满足其中一个条件——OR

>格式：select {} from {} where 条件1 or 条件2 or 条件3...;
- 1.5.4指定条件范围——IN

>格式：select {} from {} where {列} in（value1，value2，...);
- 1.5.5取反——NOT

>select {} from {} where {列} not in（value1，value2，...);
> select {} from {} where {列} not between n and m;
- 1.6条件组合中的顺序
and的优先级大于or
使用（）规范和分组条件：（条件1）and (条件2）
- 1.7模糊查询——LIKE

>格式：select {} from {} where {列} like 'jet%';
- 1.7.1 匹配符
% 代表0或任意多个字符
_ 代表一个字符；一个空格代表一个字符
- 1.8、对于unll值的条件查询

>select {} from {} where {字段} is null;
select {} from {} where {字段} is not null;

__2、列的别名__
创建别名：关键字——AS

>格式：select {列名xx} as {xx} from {} where {列} like 'jet%';

__3、算数运算符__
select 数量，价格，数量\*价格 as 总价 from 订单表;
select 数量，价格，商品名 from 订单表 where 数量\*价格<=100;

__4、文本处理函数__
- 4.1拼接函数——concat(str1,str2...)
拼接的字段中有null值，则整个拼接结果为null(空）

>格式：select concat(列1，列2) from {表}

用’：‘拼接字段名
__法1__、select concat(列1，‘：’，列2) from {表}
__法2__、select concat_ws(“：”，列1，列2) from {表}
忽略unll值进行拼接——concat_ws()
- 4.2把字符串转换为大写——upper()

>格式：select upper(str列) from {表}
- 4.3把字符串转换为小写——lower()

>格式：select lower(str列) from {表}
- 4.4截取字段名——substring()
substring(字段名，n)//从第n个位置开始截取直到结束；
substring(字段名，n,m)//从第n个位置开始截取，只取m个字符。
特殊：
substring(字段名，0)//返回空
substring(字段名，-1)//从最后一位开始截取，截取方向仍为左到右。
如substring(字段名，-3)//指截取最后三位字符。
- 4.5数据长度计算——length()
为空=0长度，空格=1长度，tab=null值无长度。

>select length(str列) as 列_len from {表};
>select length("kkkkk ");

__5、日期与时间处理函数__
- 5.1获取当前日期时间函数
返回当前日期——curdata()
返回当前时间——curtime()
返回当前日期时间——now()
格式：select curdata()，curtime()，now()
- 5.2时间日期计算函数
增加一个日期（天、周等）——AddDate()
格式：select 订单日期，adddate(订单日期，30） as 订单日期30天后是哪一天 from 订单表
select 订单日期，adddate(订单日期，-30） as 订单日期30天前是哪一天 from 订单表
计算两个日期之差——DateDiff()
格式：select 订单日期，now(),datediff(订单日期，now()） as 订单日期与当前时间差 from 订单表
拓展：timestampdiff(year,now(),订单日期)订单日期与当前时间差多少年
- 5.3获取日期时间的一部分
date()\year()\month()\day()
格式：select * from 订单表 where year(订单日期）=2005 and month(订单日期）=9;
时间格式转换——date_format(date,'%Y-%m-%d')
```md
select date_format(now(),'%Y-%M-%D');
//2022-February-7th
select date_format(now(),'%y-%m-%d');
//22-02-07
```
__6、聚合函数__
- 6.1返回某列的平均值——AVG()

>格式：select avg(价格) from 订单表;
- 6.2返回某列行数——COUNT() //统计某个字段时null值不统计

>格式：select count(*) from 订单表;
//查询表中有几条数据；count（数字：1...）/count（某列）
去重计数：count(distinct *)
- 6.3返回某列最大值——MAX()

>格式：select max(价格) from 订单表;
- 6.4返回某列最小值——MIN()
- 6.5返回某列值的和——SUM()

>格式：select sum(数量*价格) from 订单表;
//注意函数的组合使用。

---

__7、分组查询——group by__
>格式：select * from 订单表 group by 商品;
//查询分组后返回的字段
- 7.1过滤分组——having
//where子句过滤指定的行，having子句过滤指定的分组，作用于group by
- 7.2select中子句的顺序
select——>from->where->group by->having->order by->limit

__8、子查询（嵌套查询）__
找出表间的关联字段
where {} in （）

__9、多表联结/自然联结__
笛卡尔乘积(无关联的表之间）
//先生成表，然后过滤
>select * from {表1（n1*m1)}，{表2(n2*m2)},......;
新表列数为n1+n2+...,行数为m1*m2*...

```md
SELECT
*
FROM
products,
vendors
WHERE
products.vend_id = vendors.vend_id;
```

__10、内联结/等值联结（两表间字段相等的测试）__
//比自然联查询结速度快，创建表时根据过滤条件创建
>INNER JOIN ON/JOIN ON

-- inner可省略
-- 联结条件使用特定的on子句，传递给on的实际条件与传递给where的相同
-- on子句是对表关联的数据过滤，where则是对表数据行的过滤

格式：
```md
SELECT
prod_id,
prod_name,
prod_price
FROM
products
INNER JOIN vendors ON products.vend_id = vendors.vend_id
ORDER BY
prod_price;
```

__//别名：列别名；表别名__
```md
SELECT
prod_id __as 产品ID__,
prod_name as 产品名称,
prod_price as 产品价格
FROM
products __a__
INNER JOIN vendors __v__ ON a.vend_id = v.vend_id
ORDER BY
prod_price;
```

__11、自联结（在同一个表中做联结，即自己和自己联结）__
//查询速度快于[子查询]-[[表联结]]

格式：
```md
SELECT
a.prod_id
FROM
products a,
products b
WHERE
a.vend_id = b.vend_id
AND b.prod_id = "DTNTR";
```
__Q：where后为单个值，如where 1003，查询成功，但不知道原因。__

__12、外联结__
- 12.1左联结——LEFT JOIN ON
以左边的表为基准按过滤条件查找右边表的记录，匹配成功则组合成一行记录，未匹配到则只显示左表数据，右表空值表示。

| id | student_name |
| -------| ------ |
| 1| li |
| 2| liu |
| 3| gao |

| id | course_name |
| -----| ------- |
| 1 | Math |
| 2 | English |
| 2 | SQL |
| 4| JAVA |

>SELECT \* FROM student s LEFT JOIN course c ON s.id=c.id;

| id | student_name | id(1) | course_name |
| ------------| -----------| ------------| -----------|
| 1 | li| 1 | Math|
| 2 | liu| 2 | English|
| 2 | liu| 2 | SQL|
| 3| gao| null | null |

- 12.2右联结——RIGHT JOIN ON
与左联结相反，以右表为基准匹配。

>SELECT \* FROM student s RIGHT JOIN course c ON s.id=c.id;

| id | student_name | id(1) | course_name |
| ------------| -----------| ------------| -----------|
| 1 | li| 1 | Math|
| 2 | liu| 2 | English|
| 2 | liu| 2 | SQL|
| | | 4 | JAVA |

#### 》2、插入——INSERT
- 2.1插入完整的行

>格式：insert into 表名(`列名1`，`列名2`,...）Values（`各个列的值`,`value1`,...）；

//反引号 \` \`可有可无
//省略列名，即表字段，则values默认对应该表的字段顺序，不能插入部分数据。
- 2.2插入部分数据：查看表结构中的字段约束，某些字段不能为空/null值，必须有该字段的值插入。
- 2.3同时插入多行数据：

>格式：insert into 表名(`列名1`，`列名2`,...）Values（`各个列的值`,`value1`,...）,（`value1`,...）,（...）；
```
[例]insert into products select prod_id,vend_id+1,prod_name,prod_price,prod_desc from products where vend_id="1003" and prod_price="50.00";
```

#### 》3、更新——UPDATE
>格式：update {表} set {[字段1=“更新的数据 ”],[字段2=“”],...} where {字段=“更改某行的数据 ”}；
//update后不加where条件则会将该列的所有数据更新。

#### 》4、删除数据——DELETE
- 4.1从表中删除某一特定行数据

>格式：delete from {表} where {字段=“条件”}；
- 4.2从表中删除所有数据，成为空表

>格式：delete from {表}；
- 4.3删除表——drop

>格式：drop table {表}；
- 4.4先删除表后重新创建一个空表——truncate

>truncate table {表}；

__备份语句：create table {表} as select * from products;__

%%
15:06 2022/2/10
记笔记转战oblsidian
%%