数据库理论与实践(二)

本次文章主要记录和分享的是数据库查询语言，这是我们日常最常用的语言。会分享一个数据库搭建的例子cdb，该数据库文件可用于我之前创建的Springboot项目中。

DQL（数据查询语言）

DQL: 数据查询语言，用来 查询 数据库中表的记录

语法例子：

SELECT
	字段列表
FROM
	表名字段
WHERE
	条件列表
GROUP BY
	分组字段列表
HAVING
	分组后的条件列表
ORDER BY
	排序字段列表
LIMIT
	分页参数

上述语法再DQL 执行顺序：

FROM -> WHERE -> GROUP BY -> HAVING ->SELECT -> ORDER BY -> LIMIT

基础查询

查询多个字段：

SELECT 字段1, 字段2, 字段3, ... FROM 表名;
SELECT * FROM 表名;

查询并设置别名：

SELECT 字段1 [ AS 别名1 ], 字段2 [ AS 别名2 ], 字段3 [ AS 别名3 ], ... FROM 表名;
SELECT 字段1 [ 别名1 ], 字段2 [ 别名2 ], 字段3 [ 别名3 ], ... FROM 表名;

去除重复记录（去重）：

SELECT DISTINCT 字段列表 FROM 表名;

条件查询

条件：

 语法例子：

-- 从员工表里找出年龄在20到30之间
select * from employee where age between 20 and 30;
select * from employee where age >= 20 and age <= 30;

聚合查询（聚合函数）

常见聚合函数：

语法例子：

SELECT count(id) from employee where workaddress = "广东省";

分组查询

语法例子：

SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1, 字段2 排序方式2;

排序查询

语法例子：

-- 根据年龄升序排序
SELECT * FROM employee ORDER BY age ASC;
SELECT * FROM employee ORDER BY age;
-- 两字段排序，根据年龄升序排序，入职时间降序排序
SELECT * FROM employee ORDER BY age ASC, entrydate DESC;

分页查询

语法例子：

-- 查询第一页数据，展示10条
SELECT * FROM employee LIMIT 0, 10;
-- 查询第二页
SELECT * FROM employee LIMIT 10, 10;

cdb案例

首先要明确我们的系统项目需要提供什么样的服务，我计划设计的是一个用于社区人员车辆维护的系统，项目里我想通过对不同的角色展示不同的菜单页面，那么这个系统会产生“车辆、用户、角色、菜单、服务记录以及员工请假”这类的实体。确定实体后再确定每个实体的属性。

依次执行代码：

<----创建用户列表
CREATE TABLE `c_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `password` varchar(100) DEFAULT NULL,
  `email` varchar(50) DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `status` int(1) DEFAULT NULL,
  `avatar` varchar(200) DEFAULT NULL,
  `gender` varchar(8) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

<----创建角色列表
CREATE TABLE `c_role` (
  `role_id` int(11) NOT NULL AUTO_INCREMENT,
  `role_name` varchar(50) DEFAULT NULL,
  `role_desc` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`role_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;

<----创建菜单列表
CREATE TABLE `c_menu` (
  `menu_id` int(11) NOT NULL AUTO_INCREMENT,
  `component` varchar(100) DEFAULT NULL,
  `path` varchar(100) DEFAULT NULL,
  `redirect` varchar(100) DEFAULT NULL,
  `name` varchar(100) DEFAULT NULL,
  `title` varchar(100) DEFAULT NULL,
  `icon` varchar(100) DEFAULT NULL,
  `parent_id` int(11) DEFAULT NULL,
  `is_leaf` varchar(1) DEFAULT NULL,
  `hidden` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`menu_id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4;

<----创建服务记录列表
CREATE TABLE `c_menu` (
  `sever_id` int(11) NOT NULL AUTO_INCREMENT,
  `id` int(11) DEFAULT NULL,
  `car_id` int(11) DEFAULT NULL,
  `sever_desc` varchar(100) DEFAULT NULL,
  `sever_time` varchar(100) DEFAULT NULL,
  `sever_fee` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`sever_id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4;

<----创建车辆列表
CREATE TABLE `c_car` (
  `car_id` int(11) NOT NULL AUTO_INCREMENT,
  `id` int(11) DEFAULT NULL,
  `car_brand` varchar(50) DEFAULT NULL,
  `car_time` varchar(100) DEFAULT NULL,
  `status` int(1) DEFAULT NULL,
  PRIMARY KEY (`car_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;

<----创建请销假列表
CREATE TABLE `c_leave` (
  `leave_id` int(11) NOT NULL AUTO_INCREMENT,
  `leave_time` varchar(100) DEFAULT NULL,
  `leave_res` varchar(100) DEFAULT NULL,
  `id` int(11) DEFAULT NULL,
  PRIMARY KEY (`leave_id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4;

<----创建用户角色映射表
CREATE TABLE `c_user_role` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `role_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4;

<----创建角色菜单映射表
CREATE TABLE `x_role_menu` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `role_id` int(11) DEFAULT NULL,
  `menu_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4;