MySQL的基础实战篇(上)

最新推荐文章于 2024-01-18 18:57:13 发布

一亩地

最新推荐文章于 2024-01-18 18:57:13 发布

阅读量357

点赞数

分类专栏： ❤ 数据库 -----关系型数据库MySQL 文章标签： MySQL MySQL的基础实战篇

本文链接：https://blog.csdn.net/Aaroun/article/details/80422929

版权

本文是MySQL基础实战篇的上部分，涵盖了环境准备、数据库与表的创建、SQL操作（增删改查）、数据类型介绍、外键约束、以及简单的查询操作。通过实例展示了如何创建数据库和表，包括主键、外键、数据类型的使用，以及使用WHERE、LIKE、BETWEEN等进行数据过滤。此外，还介绍了ORDER BY、LIMIT、GROUP BY、HAVING等用于数据排序和分组的方法。

摘要由CSDN通过智能技术生成

MySQL基础实战篇

环境准备

本篇不会讲解如何去安装mysql，也没有这样的必要，网上这方面的资料随处可见，因此这里的环境搭建主要的我们可能在后面会使用到的数据库和表结构，本篇将采用电商网站最常见的四张表,如下：

+-------------------+
| Tables_in_webshop |
+-------------------+
| items             |
| orderdetail       |
| orders            |
| user              |
+-------------------+

商品表结构items(简化版)：

+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| id         | int(11)     | NO   | PRI | NULL    | auto_increment |
| name       | varchar(32) | NO   |     | NULL    |                |
| price      | float(10,1) | NO   |     | NULL    |                |
| detail     | text        | YES  |     | NULL    |                |
| pic        | varchar(64) | YES  |     | NULL    |                |
| createtime | datetime    | NO   |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+

订单详情表结构orderdetail(简化版):

+-----------+---------+------+-----+---------+----------------+
| Field     | Type    | Null | Key | Default | Extra          |
+-----------+---------+------+-----+---------+----------------+
| id        | int(11) | NO   | PRI | NULL    | auto_increment |
| orders_id | int(11) | NO   | MUL | NULL    |                |
| items_id  | int(11) | NO   | MUL | NULL    |                |
| items_num | int(11) | YES  |     | NULL    |                |
+-----------+---------+------+-----+---------+----------------+

订单表结构orders(简化版)：

+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int(11)      | NO   | PRI | NULL    | auto_increment |
| user_id    | int(11)      | NO   | MUL | NULL    |                |
| number     | varchar(32)  | NO   |     | NULL    |                |
| createtime | datetime     | NO   |     | NULL    |                |
| note       | varchar(100) | YES  |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+

用户表结构user(简化版):

+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int(11)      | NO   | PRI | NULL    | auto_increment |
| username | varchar(32)  | NO   |     | NULL    |                |
| birthday | date         | YES  |     | NULL    |                |
| sex      | char(1)      | YES  |     | NULL    |                |
| address  | varchar(256) | YES  |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+

它们间的关系如下图，即一个订单详情orderdetail可以有多个商品items，但只能属于一个订单orders，而一个订单orders也只能属于一个user用户，一个user用户可以拥有多个orders订单。

这里我们使用mysql提供的命令行窗口监视器对mysql数据库进行各项操作。

数据库与表的创建以及SQL增删改查

数据库和表的创建与删除

通过以下命令链接上mysql监听器：

mysql -u root -p
********(输入密码)

我们可以通过以下语句来创建数据库和删除数据库并查看当前有哪些数据库(大小不敏感)

#创建数据库
CREATE DATABASE 数据库名称
#删除数据库
DROP DATABASE 数据库名称
#查看当前所有数据库
SHOW DATABASES

先查看当前有哪些数据库：

mysql> show databases; <-----------查看当前数据库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |          
+--------------------+
5 rows in set (0.00 sec)

其中information_schema、performance_schema、mysql、sys 都是mysql自动创建的数据库，如下给出这几库的简单信息：

information_schema数据库又称为信息架构，数据表保存了MySQL服务器所有数据库的信息。如数据库名，数据库的表，表栏的数据类型与访问权限等。
performance_schema数据库主要用于收集数据库服务器性能参数，以便优化mysql数据库性能。
mysql数据库是存储着已MySQL运行相关的基本信息等数据管理的数据库。
sys 数据库是mysql5.7增加的，通过这个库可以快速的了解系统的元数据信息
这个库可以方便DBA发现数据库的很多信息，提供解决性能瓶颈的信息。

而test数据库则是一个测试数据库可有可无。ok，就此打住，现在通过创建和删除一个名为debug的数据库来演示数据库的创建语句。

mysql> create database debug; <------创建数据库
Query OK, 1 row affected (0.01 sec) <------代表执行成功
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| debug              |<------已被创建的数据库
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
6 rows in set (0.00 sec)

使用DROP DATABASE语句删除数据库：

mysql> drop database debug ; <------删除数据库
Query OK, 0 rows affected (0.02 sec)

mysql> show databases;       <------重新查看数据库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

表的创建与删除

表的操作语句如下：

#创建表
CREATE TABLE 表名 (字段名1 数据类型 [其他可选项],
                  字段名2 数据类型 [其他可选项],
                  ......                    )
#删除表
DROP TABLE 表名

#显示表的数据结构
DESC 表名

#查看数据库中所有的表
SHOW TABLES

创建表的最基本的3点是：

表的名称
表字段名称
每个字段的数据类型

现在利用上述的SQL操作语言，先创建一个名称webshop的数据库，并使用USE 关键字选择该数据库，然后创建前面 items 、orderdetail、orders、user 四张表，ok，先创建数据库，操作如下：

mysql> create database webshop; <------创建webshop数据库
Query OK, 1 row affected (0.00 sec)

mysql> use webshop <------使用use关键字选择webshop数据库
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

创建 items 表，语句如下:

CREATE TABLE `items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) NOT NULL COMMENT '商品名称',
  `price` float(10,1) NOT NULL COMMENT '商品定价',
  `detail` text COMMENT '商品描述',
  `pic` varchar(64) DEFAULT NULL COMMENT '商品图片',
  `createtime` datetime NOT NULL COMMENT '生产日期',
  PRIMARY KEY (`id`)  <---------------------------指明items的唯一主键字段
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

其中id是唯一主键，使用关键字 PRIMARY KEY 进行指定，并且不能为空，因此使用 NOT NULL 标识非空，而 AUTO_INCREMENT 选项代表该id为自动增长从1开始。在其他列中如name中还使用到了 COMMENT 来标识name的含义。每个列中使用到诸如int(11)、varchar(32)、float(10,1)、text、datetime 等数据类型对每个字段的数据存储类型进行标明（关于数据类型后面会说明）。在表创建的结尾，使用 ENGINE=InnoDB 来说明该items表在mysql数据库中使用的引擎为InnoDB（mysql数据库中提供多种数据库引擎供选择，而InnoDB是具备事务功能的引擎，后面还能见到它，这里暂且打住），通过 CHARSET=utf8 指定该表的字符集，到此创建表的语句就完成了。接着创建其他3张表：

#user表创建语句
CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(32) NOT NULL COMMENT '用户名称',
  `birthday` date DEFAULT NULL COMMENT '生日',
  `sex` char(1) DEFAULT NULL COMMENT '性别',
  `address` varchar(256) DEFAULT NULL COMMENT '地址',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=37 DEFAULT CHARSET=utf8;


#订单表orders创建语句
CREATE TABLE `orders` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL COMMENT '下单用户id',
  `number` varchar(32) NOT NULL COMMENT '订单号',
  `createtime` datetime NOT NULL COMMENT '创建订单时间',
  `note` varchar(100) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`),
  CONSTRAINT `FK_orders_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;


#订单详情表orderdetail创建语句
CREATE TABLE `orderdetail` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `orders_id` int(11) NOT NULL COMMENT '订单id',
  `items_id` int(11) NOT NULL COMMENT '商品id',
  `items_num` int(11) DEFAULT NULL COMMENT '商品购买数量',
  PRIMARY KEY (`id`),
  <--------创建外键约束----------->
  CONSTRAINT `FK_orderdetail_1` FOREIGN KEY (`orders_id`) REFERENCES `orders` (`id`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `FK_orderdetail_2` FOREIGN KEY (`items_id`) REFERENCES `items` (`id`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION

外键的约束

在订单详情表orderdetail表的创建语句中使用到如下语句：

  CONSTRAINT `FK_orderdetail_1` FOREIGN KEY (`orders_id`) REFERENCES `orders` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `FK_orderdetail_2` FOREIGN KEY (`items_id`) REFERENCES `items` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION

其中orders_id 和 items_id 分别的订单表orders和商品表items的主键，像这种属于其他表主键又存在于orderdetail表中的字段，称之为orderdetail的外键字段，使用外键的好处是可以使得两张表存在关联，保证数据的一致性和实现一些级联操作；如每次购物时必须存在相对应的items_id商品数据才能创建订单详情的数据，因为没有商品也没有所谓的订单详情了，而每次可能会购买多种商品，而每种商品也将生成不同订单详情，而客户的购买行为属一次购买，因此订单详情汇聚成一个整体的订单（orders_id），也就是说一个订单详情只能属于一个订单，而一个订单可以拥有多个订单详情。在MySQL中，InnoDB引擎类型的表支持了外键约束，而外键的使用条件如下：
1.两个表必须使用InnoDB引擎
2.外键列必须建立了索引(关于索引后面分析，主键创建时会自动创建索引)，MySQL 4.1.2以后的版本在建立外键时会自动创建索引
3.外键关系的两个表的列必须是数据类型相似，也就是可以相互转换类型的列，比如int和tinyint可以，但int和char则不可以；
外键的定义语法：

[CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, …)
REFERENCES tbl_name (index_col_name, …)
[ON DELETE {
   RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
[ON UPDATE {
   RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]

实例对照：

 CONSTRAINT `FK_orderdetail_1` FOREIGN KEY (`orders_id`) REFERENCES `orders` (`id`) 
 ON DELETE NO ACTION ON UPDATE NO ACTION <-----默认行为，可以不写

注意该语法可以在 CREATE TABLE 和 ALTER TABLE 时使用，CONSTRAINT symbol，指明了约束标识符，在SQL排错时可能有不错的表现，如果不指明CONSTRAINT symbol，MYSQL会自动生成一个名字。两表间的更新删除时数据的同步可以使用ON DELETE、ON UPDATE 来表明相互间删除和更新事件触发后的影响，可设参数以下参数，假设主表是orders，从表是orderdetail。

RESTRICT、NO ACTION(默认行为)
删除：从表数据记录不存在时，主表数据记录才可以删除，如当从表orderdetail的数据被删除后主表的orders的数据才能被删除，否则无法删除。删除从表数据，主表数据不变
更新：从表记录数据不存在时，主表数据才可以更新。当更新从表数据，主表数据不变
CASCADE（级联）
删除：删除主表数据时自动删除从表数据。删除从表数据，主表数据不变
更新：更新主表数据时自动更新从表数据。更新从表数据，主表数据不变
SET NULL
删除：删除主表数据时自动更新从表对于数据值为NULL。删除从表数据，主表数据不变
更新：更新主表数据时自动更新从表数据值为NULL。更新从表数据数据，主表不变

到此，4张表都创建完成，我们使用show tables 语句来查看数据库中的表：

mysql> show tables;
+-------------------+
| Tables_in_webshop |
+-------------------+
| items             |
| orderdetail       |
| orders            |
| orders-dely       |
|

最低0.47元/天解锁文章

一亩地

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
MySQL的基础实战篇(上)

MySQL基础实战篇环境准备本篇不会讲解如何去安装mysql，也没有这样的必要，网上这方面的资料随处可见，因此这里的环境搭建主要的我们可能在后面会使用到的数据库和表结构，本篇将采用电商网站最常见的四张表,如下：+-------------------+| Tables_in_webshop |+-------------------+| items || order...
复制链接

扫一扫

专栏目录