数据库基础知识2

左手的ㄟ右手 2022-05-11 03:28 266阅读 0赞

**上一篇博客较为详细地描述了我在mysql初步使用过程中遇到的问题及解决方法，以及对mysql的基础知识了解，以及表的简单创建插入查询等，自增列的概念与初步认识，包括数据库在多台终端间的导入导出。接下来记录下表的其他深入的操作步骤以及正则表达式的常用元字符表**

# 1. SQL 结构化查询语言 structured query language #

sql语句对于不同数据库有微小差异

1.  DDL 数据定义语言  
    create 创建表、视图、索引…  
    drop 删除表、视图、索引…  
    alter 修改表  
    truncate 截断
2.  DML 数据操控语言 (\*\*)  
    insert 插入数据  
    update 修改数据  
    delete 删除数据  
    select 查询数据
3.  DCL 数据控制语言  
    grant 授权  
    revoke 回收授权
4.  TCL 事务控制语言 transaction control  
    start transaction 开始事务（begin）  
    commit  
    rollback

# 2. DDL #

## 2.1 组合主键 ##

unique 唯一
    not null 非空
    primary key 主键 (只要有一个)
    foreign key 外键 (需要引用另一个表的主键或唯一列)
    
    |     1001 | 张三     |        1 | 企业管理 |   50 |
    |     1001 | 张三     |        1 | 企业管理 |   90 |  非法记录
    |     1001 | 张三     |        2 | 马克思   |   70 |
    |     1003 | 王五     |        2 | 马克思   |   70 |
    
    1001 + 1
    1001 + 1 非法
    1001 + 2 
    1003 + 2
    
    
      create table sc(
        	...
        	primary key (sid, cid) /*组（复）合主键*/
        );

## 2.1 alter 修改 ##

给老师表添加一列年龄 `alter table teacher add age tinyint;`  
给老师表的姓名列扩展长度 `alter table teacher modify tname varchar(30);`

## 2.2 truncate 截断 ##

delete from 表; – 如果和事务sql结合使用，是有机会恢复  
truncate table 表; – 没法和事务结合，不能恢复，效率更高

# 3. DML #

## 3.1 insert 插入数据 ##

标准语法： insert into 表(多个列…) values (多个值…);  
mysql独有的： 一次插入多行数据  
insert into 表(多个列…) values  
(多个值), (多个值), (多个值) …;  
标准SQL写法：

insert into student values(null,'张三','1990-10-10','男');
    insert into student values(null,'李四','1981-10-10','男');
    insert into student values(null,'王五','1981-11-10','女');
    insert into student values(null,'赵六','1988-10-10','男');
    insert into student values(null,'孙七','1989-01-10','女');
    insert into student values(null,'周八','1990-10-10','男');
    insert into student values(null,'张三','1990-06-10','女');

mysql写法：

insert into student values(null,'张三','1990-10-10','男'),
    	(null,'李四','1981-10-10','男'),
    	(null,'王五','1981-11-10','女'),
    	(null,'赵六','1988-10-10','男'),
    	(null,'孙七','1989-01-10','女'),
    	(null,'周八','1990-10-10','男'),
    	(null,'张三','1990-06-10','女');

标准语法：用insert插入另一张表的数据（列的个数和类型要一致）  
insert into 表1（列1,列2…） select 列1，列2… from 表2；  
`insert into s2(sid,sname) select sid,sname from student;`

## 3.2 update 对已有的数据进行更新 ##

– 更新所有的员工工资涨100  
`update emp set sal=sal+100;`

– 修改7369的员工部门为10号部门  
`update emp set deptno=10 where empno=7369;`

– 修改7000的员工部门为10号部门  
`update emp set deptno=10 where empno=7000;`  
如果条件不满足去更新不会报错，但影响行数是0

– 修改7369的员工工资为9000，职务经理

update emp set sal=9000,job='manager'
    	where empno=7369;

注意：主键列主要用于查询条件，一般不会更新

## 3.3 删除 ##

delete from 表; 全部删除  
delete from 表 where 条件; 删除符合条件的记录

– 删除不能成功，因为员工表中有记录引用了10号部门  
`delete from dept where deptno=10;`

– 删除成功，因为40号部门没有员工引用  
`delete from dept where deptno=40;`

主键所在表可以称为`主表`，而外界所在表称为`从表`  
主表中删除的记录，如果被从表引用，那么会删除失败，违反外键约束（外键约束默认行为）。

外键约束还有一种行为，主表记录删除，把从表中与之关联的记录一块删除（级联删除）  
以员工和部门为例：`foreign key(deptno) references dept(deptno) on delete cascade`  
例如： 删除10号部门，不仅会删除10号部门，而且会被从表（emp）中的10部门下所有员工也一块删除

## 3.4 select 查询 ##

select 列名… from 表名 where 条件;

### 条件 ###

1.比较运算  
表示相等的 =  
大于 >  
大于等于 >=  
小于 <  
小于等于 <=  
不等于 !=

2.逻辑运算 and（并且） or（或者） not(取反)

– 查询工资大于800 并且 部门等于20  
`where sal>800 and deptno=20;`  
– 部门=20 或者 等于30的  
`where deptno=20 or deptno=30;`

1.  null值  
    数据库中的null ，比较特殊，不能使用=来比较, 必须用 is null  
    例如：查询佣金为null的员工  
    `select * from emp where comm is null;`  
    例如：查询佣金不为null的员工  
    `select * from emp where comm is not null;`
2.  like (模糊查询)  
    例如查询以 ‘S’字母打头的员工

`select * from emp where ename like 'S%';`

通配符 % 表示匹配0~多个任意的字符  
通配符 \_ 表示匹配一个任意字符

1.  between … and … 范围匹配  
    求工资大于等于1000， 小于等于2000的员工

select * from emp where sal>=1000 and sal<=2000;
    
    select * from emp where sal between 1000 and 2000;

1.  必须按有小到大顺序
2.  between 包含等于条件

1.  in 求这一列是否在一个值列表内  
    求所有销售人员和经理  
    `select * from emp where job='SALESMAN' or job='MANAGER';`

`select * from emp where job in ('SALESMAN', 'MANAGER');`

1.  limit (mysql特有的)  
    用来限制返回的结果数

1.  limit n; n表示最多返回几条记录
2.  limit m, n; m代表偏移量（下标） 注意偏移量从0开始

1.  order by 用来给结果排序  
    语法： order by 列名 asc|desc, 列名 asc|desc, …  
    例如：按工资升序：

select * from emp order by sal;
    select * from emp order by sal asc;

按工资降序：

select * from emp order by sal desc;

其中**如果不写asc或desc，默认升序**

如果多列排序，如果第一列取值相同，再按照第二列进行排序，例如：  
先按工资排序，工资相同的，再按姓名排序：  
select \* from emp order by sal asc, ename asc; /*如果升序，两个asc都可以省略*/

1.  select 语句的顺序：  
    select … from 表 where 条件 order by 排序 limit 限制

## 3.5 函数 ##

例如 lower 函数把字符变成小写  
upper 函数把字符变成大写

常用的函数 :

1.  时间加减  
    date\_add(原始时间, 时间间隔)  
    时间间隔的语法： interval 数字 时间单位  
    数字可以是正数或负数
    
    `select date_add(now(), interval 1 day );`
2.  提取时间的某个部分  
    extract(时间部分 from 原始时间)

select extract(year from now());
    select extract(year_month from now());

返回的结果是一个数字类型

例：要获取1981年入职的员工

select * from emp where extract(year from hiredate)=1981;

3)类型转换

select cast('11' as signed)+1;
    select cast('12.55555' as decimal(5,2));

4)拼接字符串  
concat(值1, 值2, … 值n)

select concat('a', 'b', 'c');
    select concat('a', 18, 'c'); /*可以把其它类型当做字符串拼接*/

1.  求长度的函数  
    char\_length 按字符为单位统计长度  
    length 按字节为单位统计长度  
    utf8mb4编码下，汉字一个字符占三个字节，英文一个字符占一个字节
2.  标准sql：  
    case  
    when 条件1 then 值1  
    when 条件2 then 值2  
    …  
    else 值n  
    end  
    整个case…end 最后返回一个值

select sal, 
    case 
    	when sal>2000 then '高工资'
    	when sal between 1000 and 2000 then '中等'
    	else '低工资'
    end from emp;

1.  组函数  
    最大值 max(列)  
    最小值 min(列)  
    和 sum(列)  
    个数 count(列) 会排除null值  
    count(\*) 针对这次查询，看看一共有多少行  
    平均值 avg(列)
2.  去除重复  
    **distinct**

select distinct job from emp; /*去除重复后的职位有哪些*/
    select count(distinct job) from emp; /*有几种职位*/

1.  查询帮助  
    ? contents 总目录

## 3.6 分组语法 (group by 后的列取值相同的会被分为一组) ##

select … from 表 group by 列1,列2… having 分组条件  
`select deptno,count(*),max(sal) from emp group by deptno;`  
把部门编号取值相同的分为一组, 配合组函数一起使用

| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |  
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |  
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |

| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |  
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |  
| 7788 | SCOTT | ANALYST | 7566 | 1987-07-13 | 3000.00 | NULL | 20 |  
| 7876 | ADAMS | CLERK | 7788 | 1987-07-13 | 1100.00 | NULL | 20 |  
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |

| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |  
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |  
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |  
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |  
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |  
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |

查询分组后个数大于3的部门编号和个数

select deptno,count(*),max(sal) from emp 
       group by deptno having count(*)>3 order by max(sal);

**分组后 select，order by 的限制**

1.  select,order by 中的列**必须**和group by中的列一样
2.  其它没有包含在 group by语句中的列，**必须**和组函数一起用

**执行从先到后**  
where 进行一遍条件过滤 , 再执行 group by 分组, 再执行 having中的条件, 再执行select, 再执行order by, 执行limit

**语法顺序**  
select … from … where … group by … having … order by … limit;

列别名问题  
语法： select 列名 别名 …  
语法2： select 列名 as 别名 …

select deptno 部门编号,count(*) 人数,max(sal) 最高工资 from emp  
group by deptno having count(*)>3 order by max(sal);

select deptno 部门编号,count(*) 人数,max(sal) 最高工资 from emp  
group by deptno having count(*)>3 order by 人数;

select empno “员工 编号” from emp; /\* **列别名中如果存在空格等特殊符号，需要用双引号包围列别名** \*/

表别名问题  
语法 ： select … from 表 别名  
select e.\* from emp e;

多列分组  
`select deptno, job, count(*) from emp group by deptno, job;`  
/*按deptno和job这两列取值都相同的分为一组*/

| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |

| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |

| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |

| 7788 | SCOTT | ANALYST | 7566 | 1987-07-13 | 3000.00 | NULL | 20 |  
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |

| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |  
| 7876 | ADAMS | CLERK | 7788 | 1987-07-13 | 1100.00 | NULL | 20 |

| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |

| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |

| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |

补充内容：正则表达式的使用：  
|1001 | 张三 | 1990-10-10 | 男 |  
|1002 | 李四 | 1981-10-10 | 男 |  
|1003 | 王五 | 1981-11-10 | 女 |  
|1004 | 赵六 | 1988-10-10 | 男 |  
|1005 | 孙七 | 1989-01-10 | 女 |  
|1006 | 周八 | 1990-10-10 | 男 |  
|1007 | 张三 | 1990-06-10 | 女 |  
1） 替换开头的|  
^| 替换为 insert into student(sid,sname,birthday,sex) values (  
2) 替换结束的|  
|$ 替换为 );  
3）替换中间的内容，其中() 用来分组，分组后就可以使用 $1 引用第一个组的内容  
|\\s+(\[^\\s.\]+) 替换为 ,’$1’

#### 正则表达式的常用元字符表 ####

![常用元字符表1][1]

![常用元字符表2][2]

常用元字符表应用举例  
![常用元字符表使用举例][70]

[1]: /images/20220511/441ef29015e749feba33341cb2f1923e.png
[2]: /images/20220511/7fa40785e94147ad95f064555a29b98c.png
[70]: /images/20220511/e23bc7c92c9f44f1961935c866a5c1be.png