数据库基础小记

Havid_Ran

已于 2024-09-25 14:53:00 修改

阅读量1.1k

点赞数 20

文章标签：数据库 oracle sql

于 2024-09-18 15:28:36 首次发布

本文链接：https://blog.csdn.net/Havid_Ran/article/details/142331653

版权

第一章

数据库

1 创建CREATE DATABASE

CREATE DATABASE db_mr;
CREATE SCHEMA db_admin1;

2 创建指定字符集的数据库

CHARACTER SET = utf8;

COLLATE utf8_general_ci;
DROP DATABASE IF EXISTS db_mr;
SHOW DATABASES LIKE 'db_%';

3 SHOW命令查看MySQL服务器中的所有数据库信息

SHOW DATABASES|SCHEMAS
[LIKE '模式' WHERE 条件];

SHOW DATABASES like 'db_%'
SHOW DATABASES like 'db_'

4 选择数据库

CREATE DATABASE 语句创建数据库后，该数据库 并不会自动成为 当前数据库。若想让它

成为当前数据库，需要使用 MySQL 提供的 USE语句来实现

use 库名

5 修改数据库

可以修改被创建数据库的相关参数
ALTER DATABASE或者ALTER SCHEMA语句

ALTER DATABASE | SCHEMA 数据库名
DEFAULT CHARACTER SET = 字符集
DEFAULT COLLATER = 较对规则名称

6 删除数据库

DROP DATABASE 或者 DROP SCHEMA

语句的语法格式如下：

DROP DATABASE|SCHEMA IF EXISTS 数据库名;

创建数据表

创建数据表使用 CREATE TABLE 语句。语法如下：

CREATE TEMPORARY TABLE IF NOT EXISTS 数据表名
[(create_definition,…)][table_options] [select_statement]

但，在实际中使用最基本的格式即可，具体格式如下：

CREATE TABLE 数据表名(列名1 属性，列名2 属性…);

数据表

任务：

创建一个名为 bigdata 的数据库，并在该数据库中创建一个名为 stu_table 的数据表，该表

包括字段 id , usr, password, 和 createtime ，定义时指明类型，哪个是主键，是否自动编

号，是否可以为空等。

1 查看表结构 SHOW COLUMNS

使用 SHOW COLUMNS 语句查看

在 MySQL 中，使用 SHOW COLUMNS 语句可以查看表结构， SHOW COLUMNS 语句的基

本语法格式如下：

SHOW [FULL] COLUMNS FROM 数据表名 [FROM 数据库名];
或
SHOW [FULL] COLUMNS FROM 数据表名.数据库名;

使用 DESCRIBE 语句查看

在 MySQL 中，还可以使用 DESCRIBE 语句查看数据表结构。 DESCRIBE 语句的基本语法格

式如下：

DESCRIBE 数据表名;

其中， DESCRIBE 可以简写成 DESC 。在查看表结构时，也可以只列出某一列的信息。其语

法格式如下：

DESCRIBE 数据表名 列名;

2 修改表结构

ALTER [IGNORE] TABLE 数据表名 alter_spec[,alter_spec]...| table_options

IGNORE可选项，如果出现重复关键行，则只执行一行，其他重复的行被删除。
alter_spec子句：用于定义要修改的内容。

添加新字段及修改字段定义

在 MySQL 的 ALTER TABLE 语句中，可以通过使用如下子句来添加新字段：

ADD [COLUMN] create_definition [FIRST | AFTER column_name ]

使用如下子句可以修改已定义字段的定义：

MODIFY [COLUMN] create_definition

任务1：

添加一个新的字段 email, 类型为可变字符，最大长度 50 ，非空， user 字段的类型由可变

字符长度 30 ，改为长度 40.

修改字段名

在 MySQL 的 ALTER TABLE 语句中，使用如下子句可以修改字段名或者字段类型：

CHANGE [COLUMN] old_col_name create_definition

任务2：

将数据表 stu_table 的字段名 user 修改为 username.

删除字段

在 MySQL 的 ALTER TABLE 中，使用如下子句可以删除指定字段：

DROP [COLUMN] col_name

任务3：

将数据表 stu_table 中的字段 email 删除 .

修改表名

在 MySQL 的 ALTER TABLE 中，使用如下子句可以修改表名：

RENAME [AS] new_tbl_name

任务4：

将数据表 stu_table 名更改为 stu_tableold.

3 重命名表

在 MySQL 中，重命名数据表可以使用 RENAME TABLE 语句来实现。

RENAME TABLE 语句的基本语法格式如下：

RENAME TABLE 数据表名1 To 数据表名2

4 复制表

创建表的 CREATE TABLE 命令还有另外一种语法结构，在一张已经存在的数据表的基础上

创建一份该表的拷贝，也就是复制表。

这种用法的语法格式如下：

CREATE TABLE [IF NOT EXISTS] 数据表名
{LIKE 源数据表名 | (LIKE 源数据表名)}

如果在复制表的时候，也同时复制其中的内容，可用如下命令实现：

CREATE TABLE news3 AS SELECT * FROM news;

5 删除表

删除数据表的操作很简单，同删除数据库的操作类似，使用 DROP TABLE 语句即可实现。

DROP TABLE语句的基本语法格式如下：
DROP TABLE [IF EXISTS] 数据表名;

数据表小结

1. 如何创建数据表、查看数据结构、修改表结构、重命名表、复制表和删除表

2. 创建表和修改表很重要，且容易出现语法错误，需要多练习

3. 创建表和修改表后，一定要查看表结构，以确保操作是否正确

4. 删除表要特别小心，因为删除表同时也会删除表中的所有数据

MySQL存储引擎

存储引擎其实就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等

技术的实现方法。

因为在关系数据库中数据的存储是以表的形式存储的，所以存储引擎也可以称为表类型

（即存储和操作此表的类型）。

在 Oracle 和 SQL Server 等数据库中只有一种存储引擎，所有数据存储管理机制都是一样

的。而 MySql 数据库提供了多种存储引擎。用户可以根据不同的需求为数据表选择不同

的存储引擎，用户也可以根据自己的需要编写自己的存储引擎。

查询MySQL中支持的存储引擎

1 ．查询支持的全部存储引擎

在 MySQL 中，可以使用 SHOW ENGINES 语句查询 MySQL 中支持的存储引擎。其查询语句

如下：

SHOW ENGINES;

2 ．查询默认的存储引擎

如何想要知道当前 MySQL 服务器采用的默认存储引擎是什么，可以通过执行 SHOW

VARIABLES 命令来查看。查询默认的存储引擎的 SQL 如下：

SHOW VARIABLES LIKE 'storage_engine%';

小知识理解：主键、外键

主键保证了数据的唯一性，外键保证了数据的完整性。

主键是能确定一条记录的唯一标识，比如，一条记录包括身份正号，姓名，年龄。身份

证号是唯一能确定你这个人的，其他都可能有重复，所以，身份证号是主键。

外键用于与另一张表的关联。是能确定另一张表记录的字段，用于保持数据的一致性。

比如， A 表中的一个字段，是 B 表的主键，那他就可以是 A 表的外键。

MySQL数据类型

在 MySQL 数据库中，每一条数据都有其数据类型。 MySQL 支持的数据类型，主要分成 3

类：数字类型、字符串（字符）类型、日期和时间类型。

1 数字类型

MySQL 支持所有的 ANSI/ISO SQL 92 数字类型。这些类型包括准确数字的数据类型

（ NUMERIC 、 DECIMAL 、 INTEGER 和 SMALLINT ），还包括近似数字的数据类型

（ FLOAT 、 REAL 和 DOUBLE PRECISION ）。其中的关键词 INT 是 INTEGER 的同义词，关键

词 DEC 是 DECIMAL 的同义词。

2 字符串类型

字符串类型可以分为 3 类：普通的文本字符串类型（ CHAR 和 VARCHAR ）、可变类型

（ TEXT 和 BLOB ）和特殊类型（ SET 和 ENUM ）。它们之间都有一定的区别，取值的范围

不同，应用的地方也不同。

3 日期和时间数据类型

日期和时间类型包括： DATETIME 、 DATE 、 TIMESTAMP 、 TIME 和 YEAR 。其中的每种类型

都有其取值的范围，如赋予它一个不合法的值，将会被 “0” 代替。

第二章

基本查询语句 SELECT

1 SELECT语句的基本语法

select selection_list from 数据表名 where primary_constraint group by grouping_columns 
order by sorting_cloumns //如何对结果进行排序
having secondary_constraint //查询时满足的第二条件
limit count //限定输出的查询结果

2 查询表中的一列或多列

3 从一个表或多个表中获取数据

select 表名+列名 ......

单表查询

单表查询是指从一张表中查询所需要的数据 , 所有查询操作都比较简单。

查询所有字段

查询指定字段

查询指定数据

带 IN 关键字的查询

带 BETWEEN AND 查询

带 LIKE 的字符匹配查询

带 IS NULL 关键字查询空值

带 AND 的多条件查询

带 OR 的多条件查询

用 DISTINCT 关键字去除结果中的重复行

用 ORDER BY 关键字对查询结果排序

用 GROUP BY 关键字分组查询

用 LIMIT 限制查询结果的数量

2.1 查询所有字段

查询所有字段是指查询表中所有字段的数据。这种方式可以将表中所有字段的数据都查

询出来。在 MySQL 中可以使用 “*” 代表所有的列，即可查出所有的字段，语法格式如下：

SELECT * FROM 表名

2.2 查询指定字段

查询指定字段可以使用下面的语法格式：

SELECT 字段名 FROM 表名;

2.3 查询指定数据 WHERE

如果要从很多记录中查询出指定的记录，那么就需要一个查询的条件。设定查询条件应

用的是 WHERE 子句。通过它可以实现很多复杂的条件查询。在使用 WHERE 子句时，需要

使用一些比较运算符来确定查询的条件。

2.4 带IN关键字的查询

IN 关键字可以判断某个字段的值是否在于指定的集合中。 如果字段的值在集合中，则满

足查询条件，该记录将被查询出来 ；如果不在集合中，则不满足查询条件。其语法格式

如下：

SELECT * FROM 表名 WHERE 条件 [NOT] IN(元素1,元素2,…,元素n);

2.5 带BETWEEN AND的范围查询

BETWEEN AND 关键字可以 判断某个字段的值是否在指定的范围内 。如果字段的值在指定

范围内，则满足查询条件，该记录将被查询出来。如果不在指定范围内，则不满足查询

条件。其语法如下：

SELECT * FROM 表名 WHERE 条件 [NOT] BETWEEN 取值1 AND 取值2

2.6 带LIKE的字符匹配查询

LIKE 属于较常用的比较运算符，通过它可以实现 模糊查询 。它有两种通配符：

“%”和下划线“_”；

一、%

表示任意0个或多个字符，可匹配任意类型和长度的字符。有些情况下是中文，需用两个百分号（%%）表示

例：

将 u_name 为“张三”、“张猫三”、“三脚猫”、“唐三藏”等有“三”的记录全找出来
```
SELECT * FROM [user] WHERE u_name LIKE ‘%三%’
```
如果须要找出 u_name 中既有“三”又有“猫”的记录，请运用 and 条件
```
SELECT * FROM [user] WHERE u_name LIKE ‘%三%’ AND u_name LIKE ‘%猫%’
```
虽然能搜索出“三脚猫”，但不能搜索出符合条件的“张猫三”
```
SELECT * FROM [user] WHERE u_name LIKE ‘%三%猫%’
```
二、 _

表示任意单个字符。匹配单个任意字符，它常用来限定表达式的字符长度语句

例
只找出“唐三藏”这样 u_name 为三个字且中间一个字是“三”的；
```
SELECT * FROM [user] WHERE u_name LIKE ‘三’
```
只找出“三脚猫”这样 name 为三个字且第一个字是“三”
```
SELECT * FROM [user] WHERE u_name LIKE ‘三__’；
```

三、[ ]

表示括号内所列字符中的一个（类似正则表达式）。指定一个字符、字符串或范围，要求所匹配对象为它们中的任一个

例：

找出“张三”、“李三”、“王三”（而非“张李王三”）；
```
SELECT * FROM [user] WHERE u_name LIKE ‘[张李王]三’
```
```
SELECT * FROM [user] WHERE u_name LIKE ‘老[1-9]’
```
四、[^]

表示不在括号所列之内的单个字符。其取值和 [ ] 相同，但它要求所匹配对象为指定字符以外的任一个字符

例：
将找出不姓“张”、“李”、“王”的“赵三”、“孙三”等；
```
SELECT * FROM [user] WHERE u_name LIKE ‘[^张李王]三’
```
排除“老1”到“老4”，寻找“老5”、“老6”、……
```
SELECT * FROM [user] WHERE u_name LIKE ‘老[^1-4]’;
```

2.7 用IS NULL关键字查询空值

IS NULL 关键字可以用来判断字段的值是否为空值（ NULL ）。如果字段的值是空值，则

满足查询条件，该记录将被查询出来。如果字段的值不是空值，则不满足查询条件。其

语法格式样如下：

IS [NOT] NULL

mysql> SELECT books,row FROM tb_book WHERE row IS NULL;
+----------------------+------+
| books | row |
+----------------------+------+
| Java范例完全自学手册 | NULL |
| Android从入门到精通 | NULL |
+----------------------+------+
2 rows in set (0.00 sec)

2.8 带关键字AND的多条件查询

AND 关键字可以用来联合多个条件进行查询。使用 AND 关键字时，只有同时满足所有查

询条件的记录会被查询出来。如果不满足这些查询条件的其中一个，这样的记录将被排

除掉。

AND 关键字的语法格式如下：

select * from 数据表名 where 条件1 and 条件2 […AND 条件表达式n];

2.9 带OR的多条件查询

OR 关键字也可以用来联合多个条件进行查询，但是与 AND 关键字不同， OR关键字只要

满足查询条件中的一个，那么此记录就会被查询出来 ；如果不满足这些查询条件中的任

何一个，这样的记录将被排除掉。 OR 关键字的语法格式如下：

select * from 数据表名 where 条件1 OR 条件2 […OR 条件表达式n];

2.10 用DISTINCT关键字去除结果中的重复行

使用 DISTINCT 关键字可以去除查询结果中的重复记录，语法格式如下：

select distinct 字段名 from 表名;

2.11 用ORDER BY关键字对查询结果排序

使用 ORDER BY 可以对查询的结果进行升序（ ASC ）和降序（ DESC ）排列，在 默认情况

下，ORDER BY按升序 输出结果。如果要按降序排列可以使用 DESC 来实现。语法格式如

下：

ORDER BY 字段名 [ASC|DESC];

2.12 用GROUP BY关键字分组查询

通过 GROUP BY 子句可以 将数据划分到不同的组中，实现对记录进行分组查询。

在查询时，所查询的列必须包含在分组的列中，目的是使查询到的数据没有矛盾。

GROUP BY 通常与聚合函数一起使用。

GROUP_CONCAT()函数

函数语法：

group_concat( [DISTINCT] 要连接的字段 [Order BY 排序字段 ASC/DESC] [Separator ‘分
隔符’] )

该函数返回带有来自一个组的连接的非 NULL 值的字符串结果。

通俗点理解： group_concat() 会计算哪些行属于同一组，将属于同一组的列显示出来。要

返回哪些列，由函数参数 ( 就是字段名 ) 决定。分组必须有个标准，就是根据 group by 指定

的列进行分组。

GROUP BY 与 GROUP_CONCAT()函数一起使用

使用关键字 GROUP BY 与 GROUP_CONCAT() 函数查询，可以将每个组中的所有字段值都

显示出来。

user, talk 只要有一个不相同就会分一个组

mysql> select * from tb_book;
+----+----------------------+----------+--------+------+--------+
| id | books | talk | user | row | sort |
+----+----------------------+----------+--------+------+--------+
| 1 | PHP开发典型模块大全 | Java | mr | 12 | 模块类 |
| 2 | Java项目开发全程实录 | Java | mrsoft | 95 | 项目类 |
| 3 | Java Web从入门到精通 | Java Web | lx | 1 | 基础类 |
| 4 | Java范例完全自学手册 | Java | mr | NULL | 范例类 |
| 27 | Android从入门到精通 | Android | mr | NULL | 基础类 |
+----+----------------------+----------+--------+------+--------+
5 rows in set (0.00 sec)
mysql> select id, books,talk, user from tb_book group by user,talk;
+----+----------------------+----------+--------+
| id | books | talk | user |
+----+----------------------+----------+--------+
| 3 | Java Web从入门到精通 | Java Web | lx |
| 27 | Android从入门到精通 | Android | mr |
| 1 | PHP开发典型模块大全 | Java | mr |
| 2 | Java项目开发全程实录 | Java | mrsoft |
+----+----------------------+----------+--------+
4 rows in set (0.04 sec)

2.13 用LIMIT限制查询结果的数量

查询数据时，可能会查询出很多的记录。而用户需要的记录可能只是很少的一部分。这

样就需要来限制查询结果的数量。 LIMIT 是 MySQL 中的一个特殊关键字。 LIMIT 子句可以

对查询结果的记录条数进行限定，控制它输出的行数。

还可以用 LIMIT 从查询结果的中间部分取值。需要定义两个参数，第 1 个参数是开始读取

的第一条记录的编号（查询结果中，第一个记录的编号是 0 ，非 1 ）；第 2 个参数是要查询

的记录的个数。

3. 聚合函数查询

聚合函数的最大特点是它们根据一组数据求出一个值。

聚合函数的结果值只根据选定行中非 NULL 的值进行计算， NULL 值被忽略。

COUNT()函数

SUM()函数

AVG()函数

MAX()函数

MIN()函数

3.1 COUNT()函数

COUNT() 函数，对于除 “*” 以外的任何参数，返回所选择集合中非NULL值的行的数目；

对于参数“*”，返回选择集合中所有行的数目，包含NULL 值的行。没有 WHERE 子句的

COUNT(*) 是经过内部优化的，能够快速的返回表中所有的记录总数。

3.2 SUM()函数

SUM() 函数可以求出表中某个字段取值的总和。

3.3 AVG()函数

AVG() 函数可以求出表中某个字段取值的平均值。

mysql> select avg(row) from tb_book;
+----------+
| avg(row) |
+----------+
| 36.0000 |
+----------+
1 row in set (0.00 sec)

3.4 MAX()函数

MAX() 函数可以求出表中某个字段取值的最大值。

mysql> select max(row) from tb_book;
+----------+
| max(row) |
+----------+
| 95 |
+----------+
1 row in set (0.01 sec)

3.5 MIN()函数

MIN() 函数可以求出表中某个字段取值的最小值。

mysql> select min(row) from tb_book;
+----------+
| min(row) |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)