Web数据库基本知识,SQL基本语法

简介

当我们谈论整个技术栈时,实际上涉及了一系列步骤,而在Web开发中,这些步骤可以被具体化为以下几个阶段:

DBMS->GUI->翻译器->查询语言

在web中具体如下:

postgreSQL->Hasura->Apollo+ts->GraphQL

具体解释

  1. DBMS(数据库管理系统):
    • 作用: 数据库管理系统允许我们直接使用SQL语言来操作数据库。
    • 类型: 分为关系型和非关系型数据库,而在这里我们使用的是关系型数据库,具体是 postgreSQL。
  2. GUI(图形化界面):
    • 作用: 为了方便可视化管理数据库,我们使用了Hasura,这是一个图形化界面,让我们更直观地管理数据库的内容。
  3. 翻译器:
    • 原因: 因为直接使用SQL语句编程操控数据库有时候比较繁琐。
    • 具体应用: 在这个技术栈中,我们使用了Apollo,这就像一个翻译官,把我们用 TypeScript(ts) 编写的语句转换成数据库能理解的 SQL 语句。
  4. 查询语言:
    • 作用: 为了使客户端能够按照自己的需求来获取数据,我们使用了GraphQL。
    • 特点: GraphQL 允许客户端明确地指定需要哪些数据,避免了一次性获取大量不必要的信息。

综合起来,整个技术栈的流程是这样的:我们通过图形界面 Hasura 管理 postgreSQL 数据库,通过 Apollo 翻译 TypeScript 代码为数据库理解的 SQL 语句,并最终使用 GraphQL 作为查询语言,使得客户端能够更精准地获取所需的数据。这样的架构有助于提高开发效率和灵活性。

数据库

数据库(DB)概述:

数据库是一种安装在操作系统上的软件,用于存储大量数据,其存储容量可达500万。数据库的主要作用是存储和管理数据。

数据库作用:

  1. 存储数据
  2. 管理数据

数据库分类:

  1. 关系型数据库(SQL):
    • 类比于Excel,数据以表的形式存储,具有行和列。
    • 代表性的关系型数据库有MySQL、Oracle、SQL Server、DB2、SQLlite。
    • 数据存储通过表之间、行和列之间的关系来进行。
  2. 非关系型数据库(NoSQL - Not Only SQL):
    • 数据以{key: value}的形式存储,强调灵活性。
    • 代表性的非关系型数据库有Redis、MongoDB。
    • 数据存储通过对象的自身属性来决定操作,支持动态更新。

SQL

简介

SQL(Structured Query Language)是一种用于管理和操作关系型数据库管理系统(RDBMS)的特定领域语言。它是一种标准化的语言,用于定义和操作关系型数据库中的数据。SQL允许用户执行诸如查询数据、插入新数据、更新现有数据和删除数据等操作。

分为四种

  • DDL:数据库定义语言(define)
  • DML:数据库操作管理语言(manage)
  • DQL:数据库查询语言(query)
  • DCL:数据库控制语言(control)

DDL

数据库

创建

create database [if not exists] new;

删除

drop database [if exists] old;

使用

use school;
-- 如果表名或字段名是一个特殊字符,就需要`name`

查看

show databases;
show create database school; -- 查看创建数据库的语句

创建

格式

create table [if not exists] `表名`(
`字段名` 列类型 [属性] [索引] [注释],
)[表类型][字符集类型][注释]
-- 行内用空格分隔,每行后面跟英文逗号,最后一行不加逗号

create table [if not exists] `student`(
`id` int(4) not null auto_increment comment '学号',
`name` varchar(30) not null default '匿名' comment '姓名',
`pwd` varchar(20) not null default '123456' comment '密码',
`sex` varchar(2) not null default '女' comment '性别',
`birthday` datetime default null comment '出生日期',
`address` varchar(100) default null comment '家庭住址',
`email` varchar(50) default null comment '邮箱',
primary key(`id`)
)engine=innodb default charset=utf8

查看

show create table student; -- 查看student数据表的定义语句
desc student; -- 显示student表的结构

修改

alter table student rename as teacher;
alter table student add age int(2); -- 增加表的字段
alter table student modify age varchar(2); -- 修改字段约束
alter table student change age year varchar(4); -- 字段重命名
alter table student drop age;

删除

drop table [if exists] student;

DML

添加

insert into student([`name`,`pwd`]) values('alice','123456'),('bob','qwerty');
-- 由于主键自增,我们可以省略
-- 如果不写表的字段,默认一一匹配

修改

update `student` set `name`='cathy', `pwd`='888888' where id = 1;
-- 不指定条件的情况下,会改动所有行

修改的值可以是常值,也可以用变量赋值

条件:where子句,执行逻辑判断

常用操作符:

  • =, >, <, >=, <=:与Java相同
  • <>或!=:不等于
  • between…and…:闭区间
  • and:&&
  • or:||

删除

delete from `student` where id=1;

清空

truncate table `student`;
-- 优于delete from `student`

与delete相比

相同点:都能删除数据,都不会删除表结构

不同点:truncate会重新设置自增列,计数器会清零

DQL

创建数据库

下面查询演示基于这个数据库

-- 创建一个school数据库
CREATE DATABASE IF NOT EXISTS `school`;
USE `school`;

-- 创建学生表
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
    `studentno` INT(4) NOT NULL COMMENT '学号',
    `loginpwd` VARCHAR(20) DEFAULT NULL,
    `studentname` VARCHAR(20) DEFAULT NULL COMMENT '学生姓名',
    `sex` TINYINT(1) DEFAULT NULL COMMENT '性别,0或1',
    `gradeid` INT(11) DEFAULT NULL COMMENT '年级编号',
    `phone` VARCHAR(50) NOT NULL COMMENT '联系电话,允许为空',
    `address` VARCHAR(255) NOT NULL COMMENT '地址,允许为空',
    `borndate` DATETIME DEFAULT NULL COMMENT '出生时间',
    `email` VARCHAR(50) NOT NULL COMMENT '邮箱账号允许为空',
    `identitycard` VARCHAR(18) DEFAULT NULL COMMENT '身份证号',
    PRIMARY KEY (`studentno`),
    UNIQUE KEY `identitycard` (`identitycard`),
    KEY `email` (`email`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- 创建年级表
DROP TABLE IF EXISTS `grade`;
CREATE TABLE `grade` (
    `gradeid` INT(11) NOT NULL AUTO_INCREMENT COMMENT '年级编号',
    `gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
    PRIMARY KEY (`gradeid`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

-- 创建科目表
DROP TABLE IF EXISTS `subject`;
CREATE TABLE `subject` (
    `subjectno` INT(11) NOT NULL AUTO_INCREMENT COMMENT '课程编号',
    `subjectname` VARCHAR(50) DEFAULT NULL COMMENT '课程名称',
    `classhour` INT(4) DEFAULT NULL COMMENT '学时',
    `gradeid` INT(4) DEFAULT NULL COMMENT '年级编号',
    PRIMARY KEY (`subjectno`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8;

-- 创建成绩表
DROP TABLE IF EXISTS `result`;
CREATE TABLE `result` (
    `studentno` INT(4) NOT NULL COMMENT '学号',
    `subjectno` INT(4) NOT NULL COMMENT '课程编号',
    `examdate` DATETIME NOT NULL COMMENT '考试日期',
    `studentresult` INT(4) NOT NULL COMMENT '考试成绩',
    KEY `subjectno` (`subjectno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 插入学生数据,这里只添加了2行,其余自行添加
INSERT INTO `student` (`studentno`, `loginpwd`, `studentname`, `sex`, `gradeid`, `phone`, `address`, `borndate`, `email`, `identitycard`)
VALUES
(1000, '123456', '张伟', 0, 2, '13800001234', '北京朝阳', '1980-1-1', 'text123@qq.com', '123456198001011234'),
(1001, '123456', '赵强', 1, 3, '13800002222', '广东深圳', '1990-1-1', 'text111@qq.com', '123456199001011233');

-- 插入成绩数据,这里仅插入了一组,其余自行添加
INSERT INTO `result` (`studentno`, `subjectno`, `examdate`, `studentresult`)
VALUES
(1000, 1, '2013-11-11 16:00:00', 85),
(1000, 2, '2013-11-12 16:00:00', 70),
(1000, 3, '2013-11-11 09:00:00', 68),
(1000, 4, '2013-11-13 16:00:00', 98),
(1000, 5, '2013-11-14 16:00:00', 58);

-- 插入年级数据
INSERT INTO `grade` (`gradeid`, `gradename`) VALUES (1, '大一'), (2, '大二'), (3, '大三'), (4, '大四'), (5, '预科班');

-- 插入科目数据
INSERT INTO `subject` (`subjectno`, `subjectname`, `classhour`, `gradeid`)
VALUES
(1, '高等数学-1', 110, 1),
(2, '高等数学-2', 110, 2),
(3, '高等数学-3', 100, 3),
(4, '高等数学-4', 130, 4),
(5, 'C语言-1', 110, 1),
(6, 'C语言-2', 110, 2),
(7, 'C语言-3', 100, 3),
(8, 'C语言-4', 130, 4),
(9, 'Java程序设计-1', 110, 1),
(10, 'Java程序设计-2', 110, 2),
(11, 'Java程序设计-3', 100, 3),
(12, 'Java程序设计-4', 130, 4),
(13, '数据库结构-1', 110, 1),
(14, '数据库结构-2', 110, 2),
(15, '数据库结构-3', 100, 3),
(16, '数据库结构-4', 130, 4),
(17, 'C#基础', 130, 1);

查询

选择

select version(); -- 查询系统版本(函数)
select 100*3-1 as 计算结果; -- 用于计算(计算表达式)
select @@auto_increment_increment; -- 查询自增的步长(变量)

查询的是一个代码表达式,参考顺序

select [all | distinct]
{* | table.* | [table.field1[as alias1]][,table.field2[as alias2]][...]}
from table_name [as table_alias]
[left | right | inner join table_name2]
[where ...]
[group by ...]
[having]
[order by ...]
[limit {[offset,]row_count | row_countOFFSET offset}];

指定查询字段

select * from student -- 查询全部学生
select `studentno`,`studentname` from student -- 查询指定字段
select `studentno` [as] 学号 from student -- 查询结果中表头用别名显示,也可以给表起别名
select concat('姓名:',studentname) [as] 新名字 from student
select `studentresult`+1 as 提分后 from result

去重

select distinct `studentno` from result-- 查询有哪些同学参加了考试

匹配

使用where条件子句

作用:检索数据中符合条件的值
返回:布尔值
逻辑运算符

  • and或&&:a and b
  • or或||:a or b
  • not或!:not a

例如

select `studentno` from result where not `studentno` = 1000

模糊

本质:比较运算符

  • is null / is not null:是否为空
  • between and
  • like:a like b,SQL匹配,若a匹配b,则结果为真
  • in:a in (a1,a2,…),a在a1,a2,…中,则结果为真,必须完全符合
-- 查询姓刘的同学
SELECT `studentno`, `studentname` FROM `student` WHERE `studentname` LIKE '刘%';

-- 查询姓刘、名为单字的同学
SELECT `studentno`, `studentname` FROM `student` WHERE `studentname` LIKE '刘_';

-- 查询姓刘、名为双字的同学
SELECT `studentno`, `studentname` FROM `student` WHERE `studentname` LIKE '刘__';

-- 查询名字中间有嘉字的同学
SELECT `studentno`, `studentname` FROM `student` WHERE `studentname` LIKE '%嘉%';

-- 查询指定的学生
SELECT `studentno`, `studentname` FROM `student` WHERE `studentno` IN (1001, 1002, 1003);

-- 查询家乡为北京的学生,注意'北京%'是非法的
SELECT `studentno`, `studentname` FROM `student` WHERE `address` IN ('北京');

-- 查询地址为空的学生
SELECT `studentno`, `studentname` FROM `student` WHERE `address` = '' OR `address` IS NULL;

连表

join on:连接查询

where:等值查询

on是对生成临时表时的判断条件(省空间),where是临时表生成后对其筛选的条件

-- 查询参加了考试的同学(要求得到其学号、姓名、科目编号、分数)
/*思路:
1、分析需求:分析查询的字段来自哪些表
2、确定使用哪种连接查询(这两个表中哪个数据是相同的?)
3、确定判断条件
*/
select s.studentno,studentname,subjectno,studentresult
from student as s
inner join result as r
on s.studentno = r.studentno

分页和排序

排序:升序asc;降序desc

select s.studentno,studentname,subjectno,studentresult
from student as s
inner join result as r
on s.studentno = r.studentno
limit 5,5 -- 第6-10条数据

DCL

授权

GRANT SELECT, INSERT ON table_name TO user_name;

收回权限

REVOKE UPDATE ON table_name FROM user_name;

拒绝权限

DENY DELETE ON table_name TO user_name;

授予带授权选项的权限

GRANT SELECT ON table_name TO user1 WITH GRANT OPTION;

创建角色

CREATE ROLE role_name;

将权限授予角色

GRANT role_name TO user_name;

从用户中撤销角色

REVOKE role_name FROM user_name;

数据库设计

要求

糟糕的数据库设计

  1. 数据冗余,浪费空间:
    • 存在重复存储相同信息的问题,导致占用不必要的存储空间。
  2. 数据插入和删除都会麻烦、异常:
    • 操作数据的插入和删除操作可能会面临困难和异常。
    • 拒绝使用物理外键,可能导致关联性不清晰,增加了管理和维护的难度。
  3. 程序的性能差:
    • 数据库设计的不良影响了程序的性能,可能导致查询、更新等操作的效率低下。

良好的数据库设计

  1. 节省内存空间:
    • 通过优化数据结构和消除冗余,确保数据库占用的内存空间更有效利用。
  2. 保证数据库的完整性:
    • 使用适当的约束和关系,确保数据的完整性,防止不一致或错误的数据存在。
  3. 方便我们开发系统:
    • 数据库设计应当有助于系统的开发和维护,而不是成为开发过程的障碍。

步骤

  1. 分析需求:
    • 详细分析业务需求,了解系统需要处理的数据和业务逻辑。
  2. 概要设计:
    • 使用概要设计阶段,绘制关系图(E-R图),以可视化的方式表示数据实体之间的关系,为后续详细设计提供基础。

三大范式

第一范式(1NF):

  • 定义: 数据库的每一列都应该是不可分割的原子数据项。
  • 例子: 在家庭信息表中,如果将"家庭人口"和"户籍"拆分成两列,违反了第一范式的要求。

第二范式(2NF):

  • 前提: 数据库表必须满足第一范式。
  • 要求: 表中每一列都应与主键相关,而不是只与主键的某一部分相关。这有助于消除部分子函数依赖。
  • 目的: 确保每张表只描述一件事情,避免冗余和不必要的复杂性。

第三范式(3NF):

  • 前提: 数据库表必须满足第一和第二范式。
  • 要求: 表中的每一列数据都应与主键直接相关,而不能间接相关。这有助于消除传递依赖。
  • 目的: 进一步规范化数据,确保数据之间的关系更清晰、更简单,避免冗余和更新异常。