飞道的博客

数据库系统原理 - - (4)SQL与关系数据库基本操作

2020-09-30 22:09 694人阅读  评论(0)

感谢内容提供者：金牛区吴迪软件开发工作室

上一篇：数据库系统原理 - - (3)数据库设计

文章目录

四：SQL与关系数据库基本操作

1.SQL概述

a.什么是SQL

b.SQL的特点

c.SQL的组成

1.数据定义语言（Data Definition Language,DDL）

2.数据操纵语言（Data Manipulation Language, DML）

3.数据控制语言（Data Control Language, DCL）

4.嵌入式和动态SQL规则

5.SQL调用和会话规则

2.MySQL预备知识

a.MySQL使用基础

b.MySQL使用基础

c.MySQL扩展语言要素







全部的：


还有系统变量。前面加俩个@符号。



3.数据定义

a.数据库模式定义
1.创建数据库
// db_name是你要创建的数据库名字
CREATE DATABASE db_name;
CREATE SCHEMA db_name;

// 进行一些判断及配置
// []里面的内容是可加可不加的
// charset_name请自行选择，比如utf8、gb2312等等
//  | 用于分隔花括号中的选择项
CREATE {
  DATABASE | SCHEMA} [IF NOT EXISTS] db_name
[DEFAULT] CHARACTER SET [=] charset_name
|[DEFAULT] COLLATE [=] collation_name;
2.查看数据库
// like关键字用于匹配指定的数据库名称；
// where从句用于指定数据库名称查询范围的条件。
SHOW {
  DATABASES | SCHEMAS}
[LIKE 'pattern' | WHERE expr];
3.选择数据库
USE db_name;
4.修改数据库
ALTER DATABASE db_name
DEFAULT CHARACTER SET gb2312
DEFAULT COLLATE gb2312_chinese_ci;
5.删除数据库
DROP DATABASE [IF EXISTS] db_name;
b.表定义

数据表是关系数据库中最重要、最基本的单位。

创建表

重点概述：
1.一定要确定表中每个字段的数据类型
2.数据表被定义为字段的集合，按行和列的格式来存储的，每一行代表一条记录，每一列代表记录中一个字段的取值。

// TEMPORARY 临时的
// tbl_name 表名
CREATE [TEMPORARY] TABLE tbl_name
(
字段名1 数据类型[列级完整性约束条件][默认值][注释]
[, 字段名2 数据类型[列级完整性约束条件][默认值][注释]]
[, ...]
[, 表级完整性约束条件]
)[ENGINT=引擎类型];

// 示例
CREATE TABLE tbl_name
(
id bigint(20) NOT NULL AUTO_INCREMENT COMMENT '字段注释内容',
name varchar(50) NOT NULL DEFAULT 'xxx' COMMENT '字段注释内容',
address varchar(255) NULL COMMENT '字段注释内容',
createtime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '字段注释内容',
PRIMARY KEY(id) // 设置主键
);
更新表

使用ALTER TABLE语句，增加或删减列、创建或取消索引、更改原有列的数据类型、重新命名列或表，更改表的评注和表的引擎类型，为表重新创建触发器、存储过程、索引和外键等。

子句：

  • ADD[COLUMN] 子句
ALTER TABLE tbl_name
ADD COLUMN field_name field_type [{
  NULL | NOT NULL}] [DEFAULT] [AFTER] [COMMENT];

  • CHANGE[COLUMN] 子句
    修改表中列的名称或数据类型
ALTER TABLE tbl_name
CHANGE COLUMN old_field_name new_field_name new_field_type [{
  NULL | NOT NULL}] [DEFAULT] [AFTER] [COMMENT];

  • ALTER[COLUMN] 子句
    修改或删除表中指定列的默认值。
ALTER TABLE tbl_name
ALTER COLUMN field_name SET DEFAULT 'new default value';
  • MODIFY[COLUMN] 子句
    只修改指定列的数据类型，不会干涉它的列名。
ALTER TABLE tbl_name
MODIFY COLUMN field_name new_field_type;

  • DROP[COLUMN] 子句
    删除表中多余的列
// field_name 字段名
ALTER TABLE table_name
DROP COLUMN field_name;
  • 重命名表的俩种方法：
  1. ALTER TABLE… + RENAME[TO]子句
ALTER TABLE tbl_name
RENAME TO new_tbl_name;
  1. RENAME TABLE … TO …；
RENAME TABLE tbl_name TO new_tbl_name;
  • 删除表：
DROP [TEMPORARY] TABLE [IF EXISTS]
tbl_name [, tbl_name2]...
[RESTRICT | CASCADE];
  • 查看表：
// 三个语句均可查看。
SHOW COLUMNS FROM tbl_name;
DESCRIBE tbl_name;
DESC tbl_name;

  • 重置表：慎用
// 使用此sql会将你的tb_name里的数据全部清空，而后在新增数据的时候auto_increment从0开始
truncate tb_name;
c.索引定义
索引的定义：

索引是提高数据文件访问效率的有效方法

索引的弊端：

1.索引是以文件的形式存储的，如果有大量的索引，索引文件可能比数据文件更快达到最大的文件尺寸；
2.索引在提高查询速度的同时，会降低更新表的速度。

索引的分类：
  1. 普通索引 - - INDEX 或 KEY
  2. 唯一性索引 - - UNIQUE
  3. 主键 - - PRIMARY KEY

索引通常被创建成单列索引和组合索引。

索引的创建

1.使用CREATE INDEX 语句创建

CREATE [UNIQUE] INDEX index_name
ON tbl_name(field_name, [, field_name2[, ...]]);

// 示例：在数据库test的表tbl_name上，根据name列前三个字符创建一个升序索引index_name
// 拓展：ASC:升序     DESC:降序
CREATE INDEX index_name
ON test.tbl_name(name(3) ASC);

// 在数据库test的表tbl_name上，根据name列和id列创建一个组合索引index_custom
CREATE INDEX index_custom
ON test.tbl_name(name, id);

2.使用CREATE TABLE语句创建

// 创建新表的同时创建该表的主键
[CONSTRAINT[symbol]] PRIMARY KEY(field_name, ...);

// 创建新表的同时创建该表的索引
{
  INDEX | KEY}[index_name](field_name, ...);

// 修改表的同时为该表添加唯一性索引
ADD [CONSTRAINT [symbol]] UNIQUE [INDEX | KEY] [index_name](field_name, ...);

// 创建新表的同时为该表添加外键
ADD [CONSTRAINT [symbol]] FOREIGN KEY (field_name, ...);

3.使用ALTER TABLE语句创建

// 示例：使用ALTER TABLE 语句在数据库test中表tbl_name的name上添加一列非唯一的索引，取名为index_name。
ALTER TABLE test.tbl_name
ADD INDEX index_name(name);
索引的查看
SHOW {
  INDEX | INDEXES | KEYS}
{
  FROM | IN} tbl_name
[{
  FROM | IN} db_name]
[WHERE expr];
索引的删除

1.使用DROP INDEX语句

DROP INDEX index_name ON tbl_name;

2.使用ALTER TABLE语句

ALTER TABLE tbl_name
DROP index_name;

4.数据更新

a.插入数据
// 使用INSERT...VALUES语句插入单行或多行元祖数据
INSERT...VALUES
// 示例：
INSERT [INTO] tbl_name [(col_name,...)]
{
  VALUES | VALUE}({expr | DEFAULT}, ...),(...),...
// 实操：如果第一个是自动递增的id给0就行了，它会自动递增。设置default会取字段的默认值进行赋值。设置null就是null；
INSERT INTO tbl_name
VALUES(0,'xx',default,null);

// 使用INSERT...SET语句插入部分列值数据
INSERT...SET
// 示例：
INSERT [INTO] tbl_name 
SET field_name = {expr | DEFAULT},
SET field_name2 = {expr | DEFAULT};

// 使用INSERT...SELECT语句插入子查询数据
// 通俗的讲就是将查询到的内容插入到xxx。
INSERT...SELECT
// 注意点：
// 1.要保证列数一致
// 2.要保证数据类型完全一致
b.删除数据
// 使用DELETE语句删除一行或多行数据
DELETE FROM tbl_name
[WHERE where_condition]
[ORDER BY...]
[LIMIT row_count];
c.修改数据
// 使用UPDATE语句修改更新一个表中的数据
UPDATE tbl_name
SET field_name={expr1 | DEFAULT} [{
  ,field_name={expr2 | DEFAULT}}]...
[WHERE where_condition]
[PRDER BY ...]
[LIMIT row_count];

UPDATE mysql.tb_name
SET field_name='吴迪'
WHERE id=2;

5.数据查询

a.SELECT语句


b.列的选择与指定
1）普通查找
// 查询tabl_name的field_name1, field_name2字段
SELECT field_name1, field_name2
FROM tabl_name;

// 查询tabl_name的所有字段
SELECT * FROM tabl_name;
2）定义并使用列的别名
column_name [AS] column_alias
// 实操：
SELECT name,address AS dizhi FROM tbl_name;
3）替换查询结果集中的数据
CASE WHEN 条件1 THEN 表达式1
WHEN 条件2 THEN 表达式2
...
ELSE 表达式
END [AS] column_alias
FROM tbl_name;

4）计算列值

举个例子：查找数据库mysql_test的表customers中每个客户的cuse_name,cust_sex,以及对cuse_id加上数字100后的值

SELECT cuse_name,cust_sex,cuse_id+100
FROM mysql_test.customers;
5）聚合函数：

COUNT、MAX、MIN等等请自行百度。

c.FROM子句与多表连接查询

由于内容过于重要也因为内容较多，所以单独迁移了一篇文章，请点击观看：
内连接、左外连接与右外连接的区别及作用介绍

d.WHERE子句与条件查询
比较运算符

// 示例：在数据库mysql_test的表customers中查找所有男性客户的信息。
SELECT * FROM mysql_test.customers WHERE sex = '男';
判断范围
  • BETWEEN ：当查询的过滤条件被限定在值的某个范围时，可以使用关键字 “BETWEEN”；
// 示例：在数据库mysql_test的表customers中，查询客户id号在903至912之间的客户信息。
SELECT * FROM mysql_test.customers WHERE id BETWEEN 903 AND 912;
// BETWEEN...AND是包含903和912的。
  • IN：可以指定一个值的枚举表，该表中会列出所有可能的值。
// 示例：在数据库mysql_test的表customers中，查询客户id号是903、906、912的客户信息。
SELECT * FROM mysql_test.customers WHERE id IN(903,906,912);
判定空值
  • field_name IS NULL：查询field_name是空值的。
// 示例：在数据库mysql_test的表customers中，查询客户名字是空的。
SELECT * FROM mysql_test.customers WHERE name IS NULL;
  • field_name IS not NULL：查询field_name不是空值的。
// 示例：在数据库mysql_test的表customers中，查询客户名字不是空的。
SELECT * FROM mysql_test.customers WHERE name IS NOT NULL;

field_name=null;
field_name<=>null;
field_name!=null;
field_name<>null;
这些也可以判断。

子查询

  • 子查询 – 结合关键字“IN”使用的子查询
    主要用于判定一个给定值是否存在于子查询的结果集中
// 示例：查询任意所选课程成绩高于80分的学生的学号和姓名信息
SELECT id,name FROM tbl_name1 WHERE
id IN(
SELECT stuId FROM tbl_name2 WHERE
score > 80
);
  • 子查询 – 结合比较运算符使用的子查询
expression {
  =|<|<=|>|>=|<=>|<>|!=} {
  ALL|SOME ANY}(subquery)
  • 子查询 – 结合关键字“EXISTS”使用的子查询
    子查询的结果集不为空，则返回TRUE，否者返回FALSE
EXISTS(subquery);
e.GROUP BY子句与分组数据

GROUP BY{field_name | expr | position}[ASC|DESC],...[WITH ROLLUP]
// 示例:在数据库mysql_test的表customers中获取一个数据结果集，要求该结果集中分别包含每个相同地址的男性客户人数和女性客户人数。
SELECT address,sex,COUNT(*) AS 'renshu' FROM mysql_test.customers
GROUP BY address,sex;

// 示例：在数据库mysql_test的表customers中获取一个数据结果集，要求该结果集中包含每个相同地址的男性客户人数、女性客户人数、总人数以及客户的总人数。
SELECT address,sex,COUNT(*) AS 'renshu'
FROM mysql_test.customers
GROUP BY address,sex
WITH ROLLUP;
f.HAVING子句
HAVING WHERE condition;
HAVING子句 VS WHERE子句

// 示例：在数据库mysql_test的表customers中查找一类客户信息：要求在返回的结果集中，列出相同客户地址中满足客户人数少于3的所有客户姓名及其对应地址。
SELECT name,address
FROM mysql_test.customers
GROUP BY name,address
HAVING count(*) <= 3;
// 上述sql在5.5之前版本的sql是可以的，5.5之后请把GROUP BY后面的name去掉
// 因为5.5版本要求GROUP BY后面必须和SELECT后面内容一致。
g.ORDER BY子句

用来排序

ORDER BY {field_name | expr | position} [ASC | DESC],...;
// 示例：在数据库mysql_test的表customers中依次按照客户姓名和地址的降序方式输出客户的姓名和性别
SELECT name,sex FROM mysql_test.customers
ORDER BY name DESC,address DESC;

// 实战：查找某表中最后一条数据
SELECT * FROM tbl_name
ORDER BY id DESC
LIMIT 1;
ORDER BY子句与GROUP BY子句的区别

h.LIMIT子句

// 使用LIMIT子句限制被SELECT语句返回的行数
LIMIT {
  [offset] row_count | row_count OFFSET offset}
// 在数据库mysql_test的表customers中查找从第5位客户开始的3位客户的id号和姓名
SELECT id,name 
FROM mysql_test.customers
ORDER BY id
LIMIT 4,3;

6.视图

a.什么是视图

视图是数据库中的一个对象，它是数据库管理系统提供给用户的以多种角度观察数据库中数据的一种重要机制。

视图不是数据库中真实的表，而是一张虚拟的表，其自身并不存储数据。

b.使用视图的优点
  1. 集中分散数据
  2. 简化查询语句
  3. 重用SQL语句
  4. 保护数据安全
  5. 共享所需数据
  6. 更改数据格式
c.创建视图



CREATE OR REPLACE VIEW mysql_test.customers_view
AS SELECT * FROM mysql_test.customers
WHERE cust_sex='M'
WITH CHECK OPTION; // 当对视图进行增删改查的时候进行参数的验证。where后面的就是他的验证规则。
d.删除视图
// 使用DROP VIEW语句删除视图
DROP VIEW IF EXISTS mysql_test.customers_view;
e.修改视图定义
// 使用ALTER VIEW语句对已有视图的定义（结构）进行修改
ALTER mysql_test.customers_view
AS SELECT * FROM mysql_test.customers
WHERE cust_sex='M'
WITH CHECK OPTION;
f.查看视图定义
// 使用 SHOW CREATE VIEW view_name 语句查看已有视图的定义（结构）
SHOW CREATE VIEW view_name;
g.更新视图数据
// 使用INSERT语句通过视图向基本表插入数据
示例：在数据库test中，向视图customers_view插入记录：(909,'周明','M','武汉市','洪山区')
INSERT INTO test.customers_view
VALUES(909,'周明','M','武汉市','洪山区');

// 使用UPDATE语句通过视图修改基本表的数据
// 示例：将视图customers_view中所有cust_address列更新为”上海市“
UPDATE test.customers_view
SET cust_address='上海市';

// 使用DELETE语句通过视图删除基本表的数据
// 示例：删除视图customers_view中姓名为”周明“的客户信息
DELETE FROM test.customers_view
WHERE name="周明";

注意点：
向视图表增删改数据会改变基本表。

h.查询视图数据
// 在视图customers_view中查找客户id号为905的客户姓名及地址
SELECT name,address
FROM test.customers_view
WHERE id=905;

下一篇：数据库系统原理 - - (5)数据库编程


转载：https://blog.csdn.net/weixin_43606158/article/details/108821235
查看评论
* 以上用户言论只代表其个人观点，不代表本网站的观点或立场