1. 初识数据库
数据库（DataBase，DB）就是将大量互相关联的数据保存下来，对这些数据统一管理，已提高数据存取效率的数据集合。用来管理数据库的计算机系统成为数据库管理系统（DataBase Management System，DBMS）。
1.1 DBMS种类
-
层次数据库（Hierarchical Database，HDB）
-
关系数据库（Relational Database，RDB）
基于关系模型，用许多表（Table）来表达数据以及数据之间的关系
- Oracle Database：甲骨文公司的RDBMS
- SQL Server：微软公司的RDBMS
- DB2：IBM公司的RDBMS
- PostgreSQL：开源的RDBMS
- MySQL：开源的RDBMS
-
面向对象数据库（Object Oriented Database，OODB）
-
XML数据库（XML Database，XMLDB）
-
键值存储系统（Key-Value Store，KVS）
-
Redis
-
MongoDB
-
1.2 RDBMS的系统结构
使用 RDBMS 时，最常见的系统结构就是客户端 / 服务器类型（C/S类型）这种结构。
2. 初识SQL
SQL是为操作数据库而开发的语言。完全基于标准 SQL 的 RDBMS 很少，通常需要根据不同的 RDBMS 来编写特定的 SQL 语句。
2.1 SQL语句分类
SQL 语句可以分为以下三类。
- DDL（Data Definition Language，数据定义语言）用来创建或者删除存储数据用的数据库以及数据库中的表等对象。
- CREATE ： 创建数据库和表等对象
- DROP ： 删除数据库和表等对象
- ALTER ： 修改数据库和表等对象的结构
- DML（Data Manipulation Language，数据操纵语言）用来查询或者变更表中的记录。
- SELECT ：查询表中的数据
- INSERT ：向表中插入新数据
- UPDATE ：更新表中的数据
- DELETE ：删除表中的数据
- DCL（Data Control Language，数据控制语言） 用来确认或者取消对数据库中的数据进行的变更，设定用户操作权限。
- COMMIT ： 确认对数据库中的数据进行的变更
- ROLLBACK ： 取消对数据库中的数据进行的变更
- GRANT ： 赋予用户操作权限
- REVOKE ： 取消用户的操作权限
2.2 SQL语句基本书写规则
- SQL语句要以分号（ ; ）结尾
- SQL 不区分关键字的大小写，但是插入到表中的数据是区分大小写的
- win 系统默认不区分表名及字段名的大小写
- linux / mac 默认严格区分表名及字段名的大小写
- 常数的书写方式是固定的
- 单词需要用半角空格或者换行来分隔
2.3 数据库的创建（ CREATE DATABASE 语句）
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
[create_option] ...
create_option: [DEFAULT] {
CHARACTER SET [=] charset_name
| COLLATE [=] collation_name
| ENCRYPTION [=] {'Y' | 'N'}
}
下面创建一个数据库
mysql> create database if not exists shop;
Query OK, 1 row affected (0.02 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| shop |
| sys |
+--------------------+
5 rows in set (0.00 sec)
2.4 表的创建（ CREATE TABLE 语句）
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
(create_definition,...)
[table_options]
[partition_options]
create_definition: {
col_name column_definition
}
column_definition: {
data_type [NOT NULL | NULL] [DEFAULT {literal | (expr)} ]
[VISIBLE | INVISIBLE]
[AUTO_INCREMENT] [UNIQUE [KEY]] [[PRIMARY] KEY]
[COMMENT 'string']
}
下面创建一张表tbl_product
mysql> create table if not exists tbl_product
-> (product_id char(4) not null,
-> product_name varchar(100) not null,
-> product_type varchar(32) not null,
-> sale_price integer,
-> purchase_price integer,
-> register_date date,
-> primary key (product_id)
-> );
Query OK, 0 rows affected (0.03 sec)
表3-1 tbl_product 表列名描述
| 商品表中的列名 | tbl_product表定义的列名 | 数据类型 | 约束 |
|---|---|---|---|
| 商品编号 | product_id | char(4) | primary key not null |
| 商品名称 | product_name | varchar(100) | not null |
| 商品种类 | product_type | varchar(32) | not null |
| 销售单价 | sale_price | integer | |
| 进货单价 | purchase_price | integer | |
| 登记日期 | register_data | date |
2.5 数据类型
在创建数据表时，我们需要为数据表的每一列都固定对应的数据类型。
MySQL中有四种基本的数据类型
- INTEGER：整数类型
- CHAR：固定长度字符串类型
- VARCHAR：不定长度字符串类型
- DATE：日期类型
2.6 约束设置
对数据表中的每一列添加约束和条件，只有满足了，才能存储。
- NOT NULL：非空约束
- primary key：主键约束
- DEFAULT：设定默认值
2.7 表删除
删除表DROP TABLE 😔
DROP [TEMPORARY] TABLE [IF EXISTS]
tbl_name [, tbl_name] ...
[RESTRICT | CASCADE]
❕ 注意：慎重删除[doge]。如果必须删的话，还是做好备份再删，可以通过mysqlbinlog找回备份时间点之后的数据恢复。👀
下面删除tbl_product表，准备跑路
mysql> drop table if exists tbl_product;
Query OK, 0 rows affected (0.01 sec)
添加列ALTER TABLE 😨
ALTER TABLE tbl_name
[alter_option [, alter_option] ...]
[partition_options]
alter_option: {
table_options
| ADD [COLUMN] col_name column_definition
[FIRST | AFTER col_name]
| ADD [COLUMN] (col_name column_definition,...)
...
}
向tbl_product中添加一列存储100位的可变长字符串的 product_name_pinyin 列
mysql> desc tbl_product;
+----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| product_id | char(4) | NO | PRI | NULL | |
| product_name | varchar(100) | NO | | NULL | |
| product_type | varchar(32) | NO | | NULL | |
| sale_price | int | YES | | NULL | |
| purchase_price | int | YES | | NULL | |
| register_date | date | YES | | NULL | |
+----------------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql> alter table tbl_product add column product_name_pinyin varchar(100);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
ALTER TABLE 语句和 DROP TABLE 语句一样，执行之后无法恢复。误添的列也可以通过 ALTER TABLE 语句删除。
删除列 ALTER TABLE
ALTER TABLE tbl_name DROP [COLUMN] col_name;
删除 product_name_pinyin 列
mysql> ALTER TABLE tbl_product DROP COLUMN product_name_pinyin;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
清空表内容
TRUNCATE [TABLE] tbl_name;
相比drop / delete，truncate清除数据速度最快。
2.8 表中插入数据
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name
[(col_name [, col_name] ...)]
{ {VALUES | VALUE} (value_list) [, (value_list)] ...
|
VALUES row_constructor_list
}
value:
{expr | DEFAULT}
value_list:
value [, value] ...
row_constructor_list:
ROW(value_list)[, ROW(value_list)][, ...]
- 向
tbl_product中插入一条数据
mysql> desc tbl_product;
+----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| product_id | char(4) | NO | PRI | NULL | |
| product_name | varchar(100) | NO | | NULL | |
| product_type | varchar(32) | NO | | NULL | |
| sale_price | int | YES | | NULL | |
| purchase_price | int | YES | | NULL | |
| register_date | date | YES | | NULL | |
+----------------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
出现支线任务：发现不能插入中文。。。(⊙﹏⊙)，肯定是字符编码问题。
mysql> select * from tbl_product;
+------------+--------------+--------------+------------+----------------+---------------+
| product_id | product_name | product_type | sale_price | purchase_price | register_date |
+------------+--------------+--------------+------------+----------------+---------------+
| 0001 | | | 6800 | 5000 | 2009-01-15 |
+------------+--------------+--------------+------------+----------------+---------------+
1 row in set (0.00 sec)
mysql> show variables like'%char%';
+--------------------------+--------------------------------+
| Variable_name | Value |
+--------------------------+--------------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | utf8mb4 |
| character_set_system | utf8mb3 |
| character_sets_dir | /usr/share/mysql-8.0/charsets/ |
+--------------------------+--------------------------------+
8 rows in set (0.02 sec)
/* character_set_client和character_set_connection都是latin1。。。 */
mysql> set character_set_client=gbk; set character_set_connection=gbk;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
改了数据库的编码，还是没有解决。是系统字符编码问题。。。。算了以后再解决吧。💤
/* 包含列清单 */
INSERT INTO tbl_product
(product_id, product_name, product_type, sale_price, purchase_price, register_date)
VALUES('0002', 'pressure cooker', 'kitchen ware', 6800, 5000, '2009-01-15');
/* 省略列清单 */
mysql> INSERT INTO tbl_product
-> VALUES('0002', 'pressure cooker', 'kitchen ware', 6800, 5000, '2009-01-15');
Query OK, 1 row affected (0.01 sec)
mysql> select * from tbl_product;
+------------+-----------------+--------------+------------+----------------+---------------+
| product_id | product_name | product_type | sale_price | purchase_price | register_date |
+------------+-----------------+--------------+------------+----------------+---------------+
| 0001 | | | 6800 | 5000 | 2009-01-15 |
| 0002 | pressure cooker | kitchen ware | 6800 | 5000 | 2009-01-15 |
+------------+-----------------+--------------+------------+----------------+---------------+
2 rows in set (0.00 sec)
- 使用INSERT … SELECT 语句从其他表复制数据。
INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name
[PARTITION (partition_name [, partition_name] ...)]
[(col_name [, col_name] ...)]
{SELECT ... | TABLE table_name}
[ON DUPLICATE KEY UPDATE assignment_list]
value:
{expr | DEFAULT}
assignment:
col_name = value
assignment_list:
assignment [, assignment] ...
INSERT INTO productcopy (product_id, product_name, product_type, sale_price, purchase_price, regist_date)
SELECT product_id, product_name, product_type, sale_price, purchase_price, regist_date
FROM tbl_product;
- DML ：插入数据
START TRANSACTION;
INSERT INTO tbl_product VALUES('0001', 'T-shirt', 'clothes', 1000, 500, '2009-09-20');
INSERT INTO tbl_product VALUES('0002', 'puncher', 'Office supplies', 500, 320, '2009-09-11');
INSERT INTO tbl_product VALUES('0003', 'sports T-shirt', 'clothes', 4000, 2800, NULL);
INSERT INTO tbl_product VALUES('0004', 'kitchen knife', 'kitchenware', 3000, 2800, '2009-09-20');
INSERT INTO tbl_product VALUES('0005', 'pressure cooker', 'kitchenware', 6800, 5000, '2009-01-15');
INSERT INTO tbl_product VALUES('0006', 'fork', 'kitchenware', 500, NULL, '2009-09-20');
INSERT INTO tbl_product VALUES('0007', 'grater', 'kitchenware', 880, 790, '2008-04-28');
INSERT INTO tbl_product VALUES('0008', 'ballpoint', 'Office supplies', 100, NULL, '2009-11-11');
COMMIT;
mysql> select * from tbl_product;
+------------+-----------------+-----------------+------------+----------------+---------------+
| product_id | product_name | product_type | sale_price | purchase_price | register_date |
+------------+-----------------+-----------------+------------+----------------+---------------+
| 0001 | T-shirt | clothes | 1000 | 500 | 2009-09-20 |
| 0002 | puncher | Office supplies | 500 | 320 | 2009-09-11 |
| 0003 | sports T-shirt | clothes | 4000 | 2800 | NULL |
| 0004 | kitchen knife | kitchenware | 3000 | 2800 | 2009-09-20 |
| 0005 | pressure cooker | kitchenware | 6800 | 5000 | 2009-01-15 |
| 0006 | fork | kitchenware | 500 | NULL | 2009-09-20 |
| 0007 | grater | kitchenware | 880 | 790 | 2008-04-28 |
| 0008 | ballpoint | Office supplies | 100 | NULL | 2009-11-11 |
+------------+-----------------+-----------------+------------+----------------+---------------+
8 rows in set (0.00 sec)
2.9 表更新
单表语法：
UPDATE [LOW_PRIORITY] [IGNORE] table_reference
SET assignment_list
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]
value:
{expr | DEFAULT}
assignment:
col_name = value
assignment_list:
assignment [, assignment] ...
多表语法：
UPDATE [LOW_PRIORITY] [IGNORE] table_references
SET assignment_list
[WHERE where_condition]
** 注意：使用 update 时要注意添加 where 条件，否则将会将所有的行按照语句修改** 😭
-- 修改所有的注册时间
UPDATE tbl_product
SET register_date = '2009-10-10';
-- 仅修改部分商品的单价
UPDATE tbl_product
SET sale_price = sale_price * 10
WHERE product_type = '厨房用具';
练习题
- 编写一条 CREATE TABLE 语句，用来创建一个包含表 1-A 中所列各项的表
tbl_addressbook（地址簿），并为 regist_no （注册编号）列设置主键约束
表1-A 表 Addressbook （地址簿）中的列
mysql> create table tbl_addressbook
-> (regist_no integer not null,
-> name varchar(128) not null,
-> address varchar(256) not null,
-> tel_no char(10),
-> mail_address char(20),
-> primary key(regist_no)
-> );
Query OK, 0 rows affected (0.04 sec)
mysql> desc tbl_addressbook;
+--------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| regist_no | int | NO | PRI | NULL | |
| name | varchar(128) | NO | | NULL | |
| address | varchar(256) | NO | | NULL | |
| tel_no | char(10) | YES | | NULL | |
| mail_address | char(20) | YES | | NULL | |
+--------------+--------------+------+-----+---------+-------+
5 rows in set (0.01 sec)
-
假设在创建练习1.1中的 tbl_addressbook 表时忘记添加如下一列 postal_code （邮政编码）了，请把此列添加到 tbl_addressbook 表中。
列名 ： postal_code
数据类型 ：定长字符串类型（长度为 8）
约束 ：不能为 NULL
mysql> alter table tbl_addressbook add column postal_code char(8) not null;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
- 编写 SQL 语句来删除 tbl_addressbook 表。
drop table if exists tbl_addressbook;
- 编写 SQL 语句来恢复删除掉的 tbl_addressbook 表。
没有备份，删除表，是无法恢复的。
130
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
