SQL与关系数据库基本操作

作者: 薛落花随泪绽放 | 来源:发表于2022-08-16 14:45 被阅读0次

12 mysql基本
SparkSQL编程实战
go 操作数据库
SQL与关系数据库基本操作
MongoDB
数据库
sql与代数关系运算之间的关系
Shell脚本操作-6
四、SQL与关系数据库基本操作
2.SQL语句介绍

一、SQL概述

1. SQL的特点

①不是某个特定数据库供应商专有的语言。
②简单易学
③是一种强有力的语言
不区分大小写

2. 组成

①数据定义语言(DDL)
用于对数据库及数据库中的各种对象进行创建、删除、修改等操作
主要包括CREATE、ALTER、DROP。
②数据操纵语言(DML)
包括SELECT、INSERT、UPDATE、DELETE。
③数据控制语言(DCL)
主要用于安全管理。
包括GRANT、REVOKE。
④嵌入式和动态SQL规则
⑤SQL调用和会话规则
调用包括SQL例程和调用规则，以便提高SQL的灵活性、有效性、共享性。

二、MySQL预备知识

1. MySQL使用基础

目前有LAMP和WAMP两种架构方式。
在MySQL中，
①一个关系对应一个基本表
②一个或多个基本表对应一个存储文件
③一个表可以有若干个索引，索引页存放在存储文件中

image.png

2. 基本语言要素

①常量
②变量
临时存储数据。分为用户变量和系统变量，用户变量前常添加一个符号“@”，大多数系统变量前添加两个“@”符号。
③运算符
④表达式
⑤内置函数

image.png

三、数据定义

image.png

1. 数据库模式定义

①创建数据库
使用CREATE DATABASE，语法

CREATE {DATABASE|SCHEMA}[IF NOT EXISTS]db_name
[DEFAULT]CHARACTER SET[=]charset_name
|[DEFAULT]COLLATE[=]collation_name

例如

create database xue;

②选择数据库
例如：

use xue;

③修改数据库
使用ALTER DATABASEH或ALTER SCHEMA

ALTER {DATABASE|SCHEMA}[db_name]
alter_speciication ...
例如：
ALTER DATABASE xue
default character set gb2312
default collate gb2312_chinese_ci;

④删除数据库
使用DROP DATABASE

DROP {DATABASE | SCHEMA}{IF EXISTS}db_name;

⑤查看数据库
使用SHOW DATABASES或SHOW SCHEMA;

2. 表定义

①创建表
语法：

CREATE[TEMPORARY] TABLE tb1_name
(
字段名1 数据类型 [列级完整性约束条件][默认值]
[,字段名2 数据类型 [列级完整性约束条件][默认值]]
[,...]
  [,表级完整性约束条件]
)[ENGINE=引擎类型

例如

mysql> use xue;
Database changed
mysql> create table customers
    -> (
    -> cust_id int not null auto_increment,
    -> cust_name char(50) not null,
    -> cust_sex char(1) not null default 0,
    -> cust_address char(50) null,
    -> cust_contact char(50) null,
    -> primary key(cust_id)
    -> );
Query OK, 0 rows affected (0.01 sec)

若添加“TEMPORARY"表示为临时表，
②更新表
使用ALTER TABLE语句。
⑴ADD[COLUMN]子句，增加新列

mysql> alter table customers
    -> add column cust_city char(10) not null default 'Wuhan' after cust_sex;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

⑵CHANGE [COLUMN]子句，修改列的名称或数据类型

mysql> alter table customers
    -> change column cust_sex sex char(1) null default 'M';
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

⑶ALTER[COLUMN]子句，修改默认值

mysql> alter table customers
    -> alter column cust_city set default 'Beijing';
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

⑷MODIFY [COLUMN]子句，只会修改数据类型，不会干涉列名

mysql> alter table customers
    -> modify column cust_name char(20) first;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

⑸DROP [COLUMN]子句

mysql> alter table customers
    -> drop column cust_contact;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

⑹RENAME [TO]子句，修改表名

mysql> alter table customers
    -> rename to backup_customers;
Query OK, 0 rows affected (0.01 sec)

③重命名表
除了使用ALTER TABLE，可以直接使用RENAME TABLE

mysql> rename table backup_customers to customers;
Query OK, 0 rows affected (0.00 sec)

④删除表
使用DROP TABLE

DROP [TEMPORARY] TABLE [IF EXISTS]
tb1_name[,tb1_name]...
[RESTART|CASADE]

⑤查看表
使用SHOW TABLES;
⑴显示表的名称

SHOW [FULL] TABLES[{FROM|IN}db_name]
[LIKE 'pattern'|WHERE expr]

mysql> show tables;
+---------------+
| Tables_in_xue |
+---------------+
| customers     |
+---------------+
1 row in set (0.00 sec)

⑵显示表的结构

SHOW [FULL]COLUMNS [FROM|IN] tb1_name [{FROM|IN}tb1_name]
[LIKE 'pattern'|WHERE expr]
或
{DESCRIBE | DESC} tb1_name [col_name|wild]

mysql> desc customers;
+--------------+----------+------+-----+---------+----------------+
| Field        | Type     | Null | Key | Default | Extra          |
+--------------+----------+------+-----+---------+----------------+
| cust_id      | int(11)  | NO   | PRI | NULL    | auto_increment |
| cust_name    | char(20) | YES  |     | NULL    |                |
| cust_sex     | char(1)  | YES  |     | M       |                |
| cust_city    | char(10) | NO   |     | Beijing |                |
| cust_address | char(50) | YES  |     | NULL    |                |
+--------------+---------

3. 索引定义

索引以文件形式存储。
在提高查询速度的同时，会降低更新表的速度。
①创建索引
⑴使用CREATE INDEX语句(不能创建主键)

CREATE [UNION] IDNEX index_name
ON tb1_name (index_col_name,...)
例1
mysql> create index index_customers
    -> on customers(cust_name(3) asc);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
例2，根据客户姓名列和客户id号创建一个组合索引
mysql> create index index_cust
    -> on customers(cust_name,cust_id);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

⑵使用CREATE TABLE语句创建索引

例如
mysql> create table seller
    -> (
    -> seller_id int not null auto_increment,
    -> seller_name char(50) not null,
    -> seller_address char(50) null,
    -> seller_contact char(50) null,
    -> product_type int(5) null,
    -> sales int null,
    -> primary key(seller_id,product_type),
    -> index index_seller(sales)
    -> );
Query OK, 0 rows affected (0.03 sec)

⑶使用ALTER TABLE

mysql> alter table seller
    -> add index index_seller_name(seller_name);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

②查看索引
使用SHOW INDEX
③删除索引
⑴使用DROP INDEX语句

DROP INDEX index_name ON tb1_name

mysql> drop index index_cust on customers;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

⑵使用ALTER TABLE删除

四、数据更新

1. 插入数据

使用INSERT语句。
①使用INSERT...VALUES语句插入单行或多行元组数据

mysql> insert into customers
    -> values(901,'张三','F','北京市','朝阳区');
Query OK, 1 row affected (0.00 sec)

mysql> insert into customers(cust_id,cust_name,cust_sex,cust_address,cust_contact)
    -> values(0,'李四',default,'武汉',null);
Query OK, 1 row affected (0.00 sec)

②使用INSERT...SET语句插入部分列值数据

mysql> insert into customers
    -> set cust_name='李四',cust_address='武汉市',cust_sex=default;
Query OK, 1 row affected (0.00 sec)

③使用INSERT...SELECT语句插入子查询数据

2. 删除数据

使用DELETE FROM语句

DELETE FROM tb1_name
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]

mysql> delete from customers
    -> where cust_name='王五';
Query OK, 0 rows affected (0.00 sec)

3. 修改数据

使用UPDATE语句

UPDATE tb1_name
  SET col_name1 = {expr1|default}[,col_name2={expr2|default}]...
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]

mysql> update customers
    -> set cust_address='武汉市'
    -> where cust_name='张三';
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

五、数据查询

1. SELECT语句

SELECT
[ALL|DISTINCT|DISTINCTROW]
select_expr[,select_expr ...]
FROM table_reference
[WHERE where_condition]
[GROUP BY {col_name | expr | position}
  [ASC|DESC],...[WITH ROLLUP]]
[HAVING where_conditon]
[ORDER BY {col_name | expr | position}
  [ASC|DESC],...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]

image.png

2. 列的选择与指定

①选择指定的列

mysql> select cust_name,cust_sex,cust_address
    -> from customers;
+-----------+----------+--------------+
| cust_name | cust_sex | cust_address |
+-----------+----------+--------------+
| 张三      | F        | 武汉市       |
| 李四      | M        | 武汉市       |
+-----------+----------+--------------+
2 rows in set (0.00 sec)

mysql> select * from customers;
+---------+-----------+----------+--------------+--------------+
| cust_id | cust_name | cust_sex | cust_contact | cust_address |
+---------+-----------+----------+--------------+--------------+
|     901 | 张三      | F        | 北京市       | 武汉市       |
|     903 | 李四      | M        | NULL         | 武汉市       |
+---------+-----------+----------+--------------+--------------+
2 rows in set (0.00 sec)

②定义并使用列的别名

mysql> select cust_name,cust_address as 地址,cust_contact
    -> from customers;
+-----------+--------+--------------+
| cust_name | 地址   | cust_contact |
+-----------+--------+--------------+
| 张三      | 武汉市 | 北京市       |
| 李四      | 武汉市 | NULL         |
+-----------+--------+--------------+
2 rows in set (0.00 sec)

③替换查询结果集中的数据

CASE
  WHEN 条件1 THEN 表达式1
    WHEN 条件2 THEN 表达式2
    ...
ELSE 表达式
END[AS]column_alias

例如
mysql> select cust_name,
    -> case
    -> when cust_sex='M' then'男'
    -> else '女'
    -> end as 性别
    -> from customers;
+-----------+------+
| cust_name | 性别 |
+-----------+------+
| 张三      | 女   |
| 李四      | 男   |
+-----------+------+
2 rows in set (0.03 sec)

④计算列值

例如
mysql> select cust_name,cust_sex,cust_id+100
    -> from customers;
+-----------+----------+-------------+
| cust_name | cust_sex | cust_id+100 |
+-----------+----------+-------------+
| 张三      | F        |        1001 |
| 李四      | M        |        1003 |
+-----------+----------+-------------+
2 rows in set (0.02 sec)

⑤聚合函数
如果与GROUP BY一起使用，聚合函数对所有列起作用，反之只产生一行作为结果。
除COUNT函数外，聚合函数都会忽略空值。
MySQL常用聚合函数表

image.png

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

①交叉连接
又称笛卡尔积。使用关键字CROSS JOIN连接两张表。

select * from tb1 cross join tb2;

②内连接(系统默认的表连接）
通过INNER JOIN(可省略INNER)

SELECT some_cloumnes
from table1
inner join
table2
on some_conditions;

mysql> select *
    -> from tb_student inner join tb_score
    -> on tb_student.studentNo = tb_score.studentNo;

③外连接
⑴左外连接
使用LEFT OUTER JOIN
⑵右外连接
使用RIGHT OUTER JOIN

4. WHERE子句与条件查询

①比较运算

image.png

例如
mysql> select * from customers
    -> where cust_sex='M';

②判定范围
用于范围判定的关键字有“BETWEEN"和"IN”两个
⑴BETWEEN...AND

mysql> select * from customers
    -> where cust_id between 903 and 912;

⑵IN

mysql> select * from customers
    -> where cust_id in(903,906,908);

③判定空值
使用IS NULL

mysql> select cust_name from customers
    -> where cust_contact is null;

④子查询
ⅰ表子查询，返回的结果集是一个表
ⅱ行子查询，是带有一个或多个值的一行数据
ⅲ列子查询，是一列数据，该列可以有一行或多行，每行只有一个值
ⅳ标量子查询，是一个值。

1.结合“IN”使用子查询
mysql> select studentNo,studentName
    -> from tb_student
    -> where studentNo in(select studentNo from tb_score where score>80);

2.结合比较运算符使用子查询

5. GROUP BY子句与分组数据

语法格式

GROUP BY {col_name | expr | position}[ASC | DESC],...[WITH ROLLUP]

例1
mysql> select cust_address,cust_sex,count(*) as '人数'
    -> from customers
    -> group by cust_address,cust_sex;
+--------------+----------+------+
| cust_address | cust_sex | 人数 |
+--------------+----------+------+
| 武汉市       | F        |    1 |
| 武汉市       | M        |    1 |
+--------------+----------+------+
2 rows in set (0.03 sec)

例2
mysql> select cust_address,cust_sex,count(*) as '人数'
    -> from customers
    -> group by cust_address,cust_sex
    -> with rollup;
+--------------+----------+------+
| cust_address | cust_sex | 人数 |
+--------------+----------+------+
| 武汉市       | F        |    1 |
| 武汉市       | M        |    1 |
| 武汉市       | NULL     |    2 |
| NULL         | NULL     |    2 |
+--------------+----------+------+
4 rows in set (0.00 sec)

6. HAVING子句

HAVING语句与WHERE语句非常相似，HAVING语句支持WHERE语句中的所有操作符和句法，两者的区别如下：
①WHERE语句主要用于过滤数据行，HAVING语句主要用户过滤分组
②HAVING语句中的条件可以包含聚合函数，而WHERE语句不可以
③WHERE语句在数据分组前进行过滤，HAVING语句则在数据分组之后

mysql> select cust_name,cust_address
    -> from customers
    -> group by cust_address,cust_name
    -> having count(*)<=3;
+-----------+--------------+
| cust_name | cust_address |
+-----------+--------------+
| 李四      | 武汉市       |
| 张三      | 武汉市       |
+-----------+--------------+
2 rows in set (0.03 sec)

7. ORDER BY子句

将结果集中的数据行按一定的顺序进行排列。

ORDER BY [col_name | expr | position][ASC | DESC],...

mysql> select cust_name,cust_sex from customers
    -> order by cust_name desc,cust_address desc;
+-----------+----------+
| cust_name | cust_sex |
+-----------+----------+
| 张三      | F        |
| 李四      | M        |
+-----------+----------+
2 rows in set (0.00 sec)

需要注意
ⅰORDER BY子句中可以包含子查询
ⅱ当对空值进行排序时，ORDER BY子句会将该空值作为最小值来对待。
ⅲ若在ORDER BY子句中指定多个列进行排序，则会按照这些列从左至右进行排序
ⅳ在使用ORDER BY子句时，通常也会使用ORDER BY子句。

image.png

8. LIMIT子句

LIMIT {[offset,] row_count | row_count OFFSET offset}

mysql> select cust_id,cust_name from customers
    -> group by cust_id
    -> limit 4,3;
Empty set (0.00 sec)
也可以写成
mysql> select cust_id,cust_name from customers
    -> group by cust_id
    -> limit 3 offset 4;

六、视图

是从一个或多个表或者其他视图中通过查询语句导出的表。
与基本表的区别：
①视图不是数据库中的真实表，而是一张虚拟表
②视图的内容是由用于查询操作的SQL语句来定义的，它的列数据与行数据均来自于定义视图的查询所应用的真实表，并且这些数据是在应用视图时动态生成的
③视图不是以数据集的形式存储在数据库中，它所对应的数据实际上是存储在视图所引用的真实表。
④视图是用来查看存储在别处的数据的一种虚拟表，而其自身并不存储数据

使用视图的有点
①集中分散数据
②简化查询语句
③重用SQL语句
④保护数据安全
⑤共享所需数据
⑥更改数据格式

1. 创建视图

语法

CREATE VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]

mysql> create or replace view customers_view
    -> as
    -> select * from customers
    -> where cust_sex='M'
    -> with check option;
Query OK, 0 rows affected (0.00 sec)

2. 删除视图

语法

DROP VIEW [IF EXISTS]
view_name[,view_name]...
[RESTRIC | CASCADE]

3. 修改视图定义

ALTER VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]

4. 查看视图定义

SHOW CREATE VIEW view_name;

5. 更新视图数据

⑴使用INSERT语句通过视图向基本表插入数据

mysql> insert into customers_view
    -> values(909,'周明','M','武汉市','洪山区');
Query OK, 1 row affected (0.00 sec)

⑵使用UPDATE语句通过视图修改基本表的数据

mysql> update customers_view
    -> set cust_address='上海市'
    -> ;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

⑶使用DELETE语句通过视图删除基本表的数据

mysql> delete from customers_view
    -> where cust_name='周明';
Query OK, 1 row affected (0.03 sec)

6. 查询视图数据

mysql> select cust_name,cust_address
    -> from customers_view
    -> where cust_id=905;

image.png

mysql> create database db_test;
Query OK, 1 row affected (0.00 sec)

mysql> use db_test;
Database changed

mysql> create table content
    -> (
    -> content_id int not null auto_increment primary key,
    -> subject varchar(200),
    -> words varchar(1000),
    -> username varchar(50),
    -> face varchar(50),
    -> email varchar(50),
    -> createtime datetime
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into content
    -> (subject,words,username,face,email,createtime)
    -> values
    -> ('MySQL问题请教','MySQL中对表数据的基本操作有哪些？','MySQL初学者','face.jpg','tom@gmail.com','22/08/16 15:05');
Query OK, 1 row affected (0.00 sec)

mysql> update content
    -> set words='如何使用INSERT语句'
    -> where username='MySQL初学者';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> delete from content where username='MySQL初学者';

12 mysql基本
SQL：关系数据库语言标准，STRUCT QUERY LANGUAGE，基本所有关系数据库都使用这套语言进行操作 ...
SparkSQL编程实战
Spark SQL DataFrame 的创建以及基本操作 DataFrame可以理解成关系型数据库中的表，它与 ...
go 操作数据库
内容 1 database/sql基本操作2 gorm基本操作 1 database/sql 初始化一个数据库连接...
SQL与关系数据库基本操作
一、SQL概述 1. SQL的特点 ①不是某个特定数据库供应商专有的语言。②简单易学③是一种强有力的语言不区分大小...
MongoDB
关系型数据库和非关系型数据库表就是关系或者说表与表之间存在关系所有的关系型数据库都需要通过 sql 语言来操作 ...
数据库
SQL语句增删改查 SQL表关系 SQLiteDatabase操作SQLite数据库 SQLiteOpenHelp...
sql与代数关系运算之间的关系
# sql与代数关系运算之间的关系关系实际上是“组域”上的笛卡尔积的一个子集，所以sql对关系数据库上的操作实际...
Shell脚本操作-6
Shell操作数据库MySQL SQL基本操作安装MySql数据库连接数据库 mysql -u root -p...
四、SQL与关系数据库基本操作
1. SQL概述结构化查询语言（Structured Query Language，SQL）是专门用来与数据库通...
2.SQL语句介绍
1. 关系型数据库的常见组件 2. SQL语言规范 3. SQL语句分类 4. SQL语句构成 5. 数据库操作 ...

SQL与关系数据库基本操作

一、SQL概述

1. SQL的特点

2. 组成

二、MySQL预备知识

1. MySQL使用基础

2. 基本语言要素

三、数据定义

1. 数据库模式定义

2. 表定义

3. 索引定义

四、数据更新

1. 插入数据

2. 删除数据

3. 修改数据

五、数据查询

1. SELECT语句

2. 列的选择与指定

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

4. WHERE子句与条件查询

5. GROUP BY子句与分组数据

6. HAVING子句

7. ORDER BY子句

8. LIMIT子句

六、视图

1. 创建视图

2. 删除视图

3. 修改视图定义

4. 查看视图定义

5. 更新视图数据

⑴使用INSERT语句通过视图向基本表插入数据

⑵使用UPDATE语句通过视图修改基本表的数据

⑶使用DELETE语句通过视图删除基本表的数据

6. 查询视图数据

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读