第二章：数据库操作和配置

DaB210

已于 2024-04-30 16:58:59 修改

阅读量1.3k

点赞数 28

分类专栏： PHP 文章标签：数据库 php

于 2024-04-17 12:29:33 首次发布

本文链接：https://blog.csdn.net/DaB210/article/details/137867681

版权

PHP 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

1、MYSQL数据库的端口冲突问题：

打开my.ini配置文件，查找【port】分别将以下3处的端口号修改即可，如将3306修改为8806：
在这里插入图片描述

在这里插入图片描述

2、解决Wampserver的MYSQL数据库中文乱码问题：

（建议在创建数据库之前进行配置，MariaDB和MySQL的配置一样）

1）在Wampserver中选择MySQL——》MySQL控制台——》OK——》回车——》输入【show variables like ‘character%’;】

在这里插入图片描述

2）分别在my.ini文件中的【client】、【mysql】增加default-character-set=utf8或者default-character-set=utf8mb4，【mysqld】修改为character_set_server=utf8或者character_set_server=utf8mb4。

注：utf8mb4是utf8的一个扩展，如果数据库需要支持使用特殊符号，最好使用utf8mb4来存储，兼容性更好，但是也会导致耗费更多的存储空间。
在这里插入图片描述

3）修改完成后保存，重启服务器。重新进入控制台，输入【show variables like ‘character%’;】

查看修改情况，结果如下：
在这里插入图片描述
1、character_set_client
　　主要用来设置客户端使用的字符集。
2、character_set_connection
　　主要用来设置连接数据库时的字符集，如果程序中没有指明连接数据库使用的字符集类型则按照这个字符集设置。
3、character_set_database
　　主要用来设置默认创建数据库的编码格式，如果在创建数据库时没有设置编码格式，就按照这个格式设置。
4、character_set_filesystem
　　文件系统的编码格式，把操作系统上的文件名转化成此字符集，即把 character_set_client转换character_set_filesystem，默认binary是不做任何转换的。
5、character_set_results
　　数据库给客户端返回时使用的编码格式，如果没有指明，使用服务器默认的编码格式。
6、character_set_server
　　服务器安装时指定的默认编码格式，这个变量建议由系统自己管理，不要人为定义。
7、character_set_system
　　数据库系统使用的编码格式，这个值一直是utf8，不需要设置，它是为存储系统元数据的编码格式。
8、character_sets_dir
这个变量是字符集安装的目录。

我们只关注下列5个变量是否符合我们的要求：
character_set_client
character_set_connection
character_set_database
character_set_results
character_set_server
下列3个系统变量我们不需要关心，不会影响乱码等问题：
character_set_filesystem
character_set_system
character_sets_dir

3、如何修改MySQL的默认引擎：

在my.ini文件中的查找engine，将【default_storage_engine=MYISAM】中的MYISAM修改为InnoDB。原有的默认引擎MYISAM既不支持事务，也不支持外键，所以需要修改为InnoDB。
注意：在配置文件语句前面加;的语句是不执行的。
在这里插入图片描述

4、创建项目数据库：

（所有的SQL语句可以使用记事本先写好，再复制到控制台运行）

1）查看数据库：

显示系统中的全部数据库。
【show databases;】
在这里插入图片描述

2）创建数据库：

【create database db_name;】
【create database exbbs;】

在这里插入图片描述

3）使用数据库：

将当前数据库定位到需要使用的数据库。【use db_name;】
【use exbbs;】

在这里插入图片描述

4）查看默认数据库：

【select database();】
在这里插入图片描述

5）修改数据库：

【 alter database db_name alter_specification【alter_specification】【,alter_specification】…
default character set utf8
default collate utf8_general_ci;】

以下示例：修改数据库exbbs的默认字符集和校对规则（大小写不敏感）。
【alter database exbbs
default character set utf8
default collate utf8_general_ci;】
在这里插入图片描述

6）删除数据库：

【drop database [if exists] db_name;】
其中if exists是指在删除数据库前进行判断，只有改数据库存在时才执行drop database操作。
【drop database test1;】
在这里插入图片描述

5、数据表的操作：

1）创建数据表：

【create [temporary] table [if not exists] tbl_name
[([column_definition,…[index_definition])]
[table_option] [select_statement];】
注：[]中的关键字均可省略。其中：
temporary表示用create命令新建的表为临时表；
if not exists表示在建表前加上一个判断，只有该表目前尚不存在时才执行create table操作；
tbl_name表示要创建的表的表名；
column_definition表示列定义，包括列名、数据类型、空值声明和完整性约束；
index_definition表示表索引项定义，主要定义表的索引、主键、外键等；
table_option用于描述表的选项，此项较少用；
select_statement可以在create table语句的末尾添加一个select语句，在现有表的基础上创建表，此项较少用。

示例：创建论坛系统数据库中的用户表。
【create table user(
uId int(11) not null auto_increment,
uName varchar(20) not null,
uPass varchar(20) not null,
primary key(uId)
);】
在这里插入图片描述

2）查看数据表：

【show [full] tables [{from] in} db_name] [like ‘pattern’] where expr];】
【show tables;】
在这里插入图片描述

3）查看数据表的具体信息：

【{describe\desc} tbl_name [col_name\wild];】
【describe user;】
在这里插入图片描述

【desc user;】
在这里插入图片描述

4）数据表的修改：

【alter [ignore] table tbl_name
alter_specification [, alter_specification]…】
演示：

1）alter table user add column mail varchar(100); //增加mail字段

在这里插入图片描述

2）alter table user change column uPass upass varchar(10); //修改字段uPass为upass，长度修改为10

在这里插入图片描述

3）alter table user drop mail; //删除user表的mail字段

在这里插入图片描述

5）重命名数据表：

【rename table tbl_name to new_tbl_name;】
【rename table user to user1;】
在这里插入图片描述

6）删除数据表：

【drop [temporary] table [if exists] tbl_name [,tbl_name]…】
【drop table user1;】
在这里插入图片描述

课堂练习：
1）完成BaiYun管理论坛系统数据库中四张数据表的创建（用户表、板块表、帖子表、回帖表）。
2）将BaiYun管理论坛系统数据库中的用户表的用户编号字段设为自动增长列。

6、数据约束：

约束是一个命名规则和机制：即通过对数据的增、删、改操作加一些限制，以保证数据库的数据完整性，MySQL中支持5种约束：

1)“不为空”（not null）
2)缺省值（默认值default）
3)Unique约束
4)主键约束（主键只能有一个，但是可以是组合键）
5)外键约束**

定义约束有两种方式：列约束和表约束

1)列约束定义在一个列上，只能对该列起约束作用；
2)表约束一般定义在一个表的多个列上，要求被约束列满足一定的关系。

演示：创建论坛数据库cxbbs，使用论坛数据库cxbbs，分别创建论坛系统的数据表：用户表 tbl_user、板块表tbl_board、帖子表tbl_topic，并在每张表增加数据约束。

在这里插入图片描述

论坛系统的关系模型：

用户（用户名，登录密码，性别，注册时间，头像，用户编号）
版块（版块名称，版块编号，上级版块编号（父板块））
帖子（帖子标题，帖子内容，发帖时间，修改时间，帖子编号，用户编号，版块编号）
回帖（回帖标题，回帖内容，回帖时间、修改时间，回帖编号，用户编号，帖子编号）

create database cxbbs;
use cxbbs;
DROP TABLE IF EXISTS tbl_user;
CREATE TABLE tbl_user (
  uId INT NOT NULL AUTO_INCREMENT COMMENT '用户编号',
  uName VARCHAR(50) NOT NULL COMMENT '用户名',
  uPass VARCHAR(10) NOT NULL COMMENT '密码',
  head VARCHAR(50) NOT NULL COMMENT '头像',
  regTime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间',
  gender smallint(6) NOT NULL COMMENT '性别',
  PRIMARY KEY (uId)
) ;



DROP TABLE IF EXISTS tbl_board;
CREATE TABLE tbl_board (
  boardId INT NOT NULL AUTO_INCREMENT COMMENT '版块编号',
  boardName VARCHAR(50) NOT NULL COMMENT '版块标题',
  parentId INT NOT NULL COMMENT '父版块编号',
  PRIMARY KEY (boardId)
) ;


DROP TABLE IF EXISTS tbl_topic;
CREATE TABLE tbl_topic (
  topicId INT NOT NULL AUTO_INCREMENT COMMENT '帖子编号',
  title VARCHAR(50) NOT NULL COMMENT '帖子标题',
  content VARCHAR(1000) NOT NULL COMMENT '帖子内容',
  publishTime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '发帖时间',
  modifyTime TIMESTAMP COMMENT '修改时间',
  uId INT NOT NULL COMMENT '用户编号',
  boardId INT NOT NULL COMMENT '版块编号',
  PRIMARY KEY (topicId),
  FOREIGN KEY FK_UID (uId) REFERENCES tbl_user(uId),
  FOREIGN KEY FK_BID (boardId) REFERENCES tbl_board(boardId)
) ;

课堂练习：创建创建论坛系统的数据表：回帖表tbl_reply，并增加相关数据约束。

课后作业：课后同学们在MYSQL控制台将论坛系统四张数据表创建成功的源码截图提交超星学习通。

7、数据管理语句

1) 插入数据：

插入单行数据：

INSERT INTO tbl_user VALUES (8, 'qq', 'qq', '6.gif', '2023-03-17 22:25:34', 2);

在这里插入图片描述

INSERT INTO tbl_user (uName,uId,uPass,head,regTime,gender) VALUES ('mm',9, 'mm', '5.gif', '2023-03-18 22:25:34', 1);

在这里插入图片描述

INSERT INTO tbl_user SET
	uId=10,
	uName='ss',
	uPass='ss',
	head='6.gif',
	regTime= '2023-03-19 22:25:34',
	gender=2;

在这里插入图片描述
插入多行数据：

INSERT INTO tbl_user (uName,uId,uPass,head,regTime,gender) VALUES ('mm',12, 'mm', '5.gif', '2023-03-18 22:25:34', 1),('mm',13, 'mm', '5.gif', '2023-03-18 22:25:34', 1),('mm',14, 'mm', '5.gif', '2023-03-18 22:25:34', 1);

在这里插入图片描述

论坛系统各表插入数据（参考）

INSERT INTO tbl_user VALUES ('1', 'qq', 'qq', '6.gif', '2023-03-17 22:25:34', '2');
INSERT INTO tbl_user VALUES ('2', 'cmu', 'cmu', '6.gif', '2023-03-17 22:25:51', '2');
INSERT INTO tbl_user VALUES ('3', 'wiiliam', '123', '1.gif', '2023-03-30 08:37:58', '2');
INSERT INTO tbl_user VALUES ('4', 'wen', '123', '1.gif', '2023-03-30 08:52:26', '2');

INSERT INTO tbl_board VALUES ('1', '.NET技术', '0');
INSERT INTO tbl_board VALUES ('2', 'Java技术', '0');
INSERT INTO tbl_board VALUES ('3', 'PHP技术', '0');
INSERT INTO tbl_board VALUES ('4', '数据库技术', '0');
INSERT INTO tbl_board VALUES ('5', 'C#语言', '1');
INSERT INTO tbl_board VALUES ('6', 'WinForms', '1');
INSERT INTO tbl_board VALUES ('7', 'ADO.NET', '1');
INSERT INTO tbl_board VALUES ('8', 'Java基础', '2');
INSERT INTO tbl_board VALUES ('9', 'JSP技术', '2');
INSERT INTO tbl_board VALUES ('10', 'Servlet技术', '2');
INSERT INTO tbl_board VALUES ('11', 'Eclipse应用', '2');
INSERT INTO tbl_board VALUES ('12', 'PHP基础', '3');
INSERT INTO tbl_board VALUES ('13', 'MySQL', '3');
INSERT INTO tbl_board VALUES ('14', '娱乐', '0');
INSERT INTO tbl_board VALUES ('15', '灌水乐园', '14');

INSERT INTO tbl_topic VALUES ('1', '测试发帖', '如题', '2023-02-25 11:07:49', '2023-02-25 11:07:49', '1', '4');
INSERT INTO tbl_topic VALUES ('2', '我是新手', '请大家多多指教', '2023-02-25 11:09:34', '2023-02-25 11:09:34', '1', '5');
INSERT INTO tbl_topic VALUES ('3', 'ADO.NET测试帖1', 'ADO.NET测试帖1', '2023-02-25 11:09:34', '2023-02-25 11:09:34', '1', '6');
INSERT INTO tbl_topic VALUES ('4', '段代码是什么意思', '段代码是什么意思', '2023-02-25 11:09:34', '2023-02-25 11:09:34', '1', '7');
INSERT INTO tbl_topic VALUES ('5', '我是新手，我刚开始学习Java', 'rt', '2023-02-25 11:09:35', '2023-02-25 11:09:35', '1', '8');
INSERT INTO tbl_topic VALUES ('6', '我是新手，我刚开始学习Java', 'rt', '2023-02-25 11:09:35', '2023-02-25 11:09:35', '1', '9');
INSERT INTO tbl_topic VALUES ('7', '我是新手，我刚开始学习Java', 'rt', '2023-02-25 11:09:35', '2023-02-25 11:09:35', '1', '10');
INSERT INTO tbl_topic VALUES ('8', 'JSP论坛测试', 'rt', '2023-02-25 11:09:35', '2023-02-25 11:09:35', '1', '11');
INSERT INTO tbl_topic VALUES ('9', 'JSP论坛测试', 'rt', '2023-02-25 11:09:35', '2023-02-25 11:09:35', '1', '12');
INSERT INTO tbl_topic VALUES ('10', 'JSP论坛测试', 'rt', '2023-02-25 11:09:35', '2023-02-25 11:09:35', '1', '13');
INSERT INTO tbl_topic VALUES ('11', 'JSP论坛测试', 'rt', '2023-02-25 11:09:35', '2023-02-25 11:09:35', '1', '15');

2）修改语句：

将用户表中uId为1的用户性别修改为男性。

UPDATE tbl_user SET gender=1 WHERE uid=1;

在这里插入图片描述

3）删除语句：

将用户表中uId为3的记录删除。

DELETE FROM tbl_user WHERE uid=3;

在这里插入图片描述

8、数据查询语句

1)SELECT子句
2)FROM子句
3)WHERE子句
4)GROUP BY子句
5)HAVING子句
6)ORDER BY子句
7)LIMIT子句
8)UNION语句

1）基础查询：select语句

–1、查询发帖用户的用户名、性别和注册时间

 SELECT uName, gender, regtime FROM tbl_user;

在这里插入图片描述

–2、查询用户表中的全体用户的详细信息（“*”表示查询所有字段）

SELECT * FROM tbl_user;

在这里插入图片描述

–3、使用别名显示用户的用户名、性别和注册时间（AS子句来定义查询结果的列别名，AS可以省略）

SELECT uName '用户名',gender AS '性别', regtime AS '注册时间'
 FROM tbl_user;

在这里插入图片描述

SELECT uName '用户名',gender '性别', regtime '注册时间'
 FROM tbl_user;

在这里插入图片描述

–4、给论坛用户统一增加“cx_”前缀

 SELECT CONCAT('cx_',uName) username FROM tbl_user ;

–CONCAT函数用于连接字符串，username 是列的别名，中间省略了as。

在这里插入图片描述

SELECT CONCAT('cx_',uName)  FROM tbl_user ;

在这里插入图片描述

–5、显示用户表中的用户名和性别，要求将性别的查询结果替换为“男”或“女”

SELECT uName AS '用户名',
  CASE
    WHEN gender IS NULL THEN '保密' /*为空时显示 保密 */
    WHEN gender=1 THEN '男'        /*为1时显示 男 */
    WHEN gender=2 THEN '女'        /*为2时显示 女 */
  END AS '性别'
 FROM tbl_user;

在这里插入图片描述

–6、显示用户表中用户名不同的用户。

SELECT DISTINCT uName FROM tbl_user;

在这里插入图片描述

–7、显示用户表的用户数（聚合函数：COUNT 求个数）

函数名	说明
COUNT	统计给定表达式中所有值的数目/个数
MAX	返回给定表达式中所有值中的最大值
MIN	返回给定表达式中所有值中的最小值
SUM	返回给定表达式中所有值的和
AVG	返回给定表达式中所有值的平均值

SELECT COUNT(*) FROM tbl_user;

在这里插入图片描述

SELECT COUNT(uId) FROM tbl_user;

在这里插入图片描述

–8、使用内连接显示一个帖子发帖人和标题（两张表之间的联系，通过外键）

 SELECT uName,title
 FROM tbl_topic t INNER JOIN tbl_user u ON t.uId = u.uId;

在这里插入图片描述

–9、显示所有版块的父版块的名称（同一张表的连接）

Select * from tbl_board;

在这里插入图片描述

SELECT a.boardName AS '版块名',b.boardName AS '父版块名'
 FROM tbl_board AS a JOIN tbl_board AS b ON a.parentId = b.boardId;

在这里插入图片描述

2）Where子句：

–1、显示版块名中含有“Java”字符的版块（模糊查询：“%”代表0个或多个字符）

 SELECT * FROM tbl_board
  WHERE boardName LIKE '%java%';

在这里插入图片描述

–2、显示版块名中第二个字符为“a”的版块（模糊查询，_表示单个字符）

 SELECT * FROM tbl_board
 WHERE boardName LIKE '_a%';

在这里插入图片描述

–3、显示2023年3月注册的全部用户（范围比较，BETWEEN …AND …包括and前面的值但不包括and后面的值）

 SELECT * FROM tbl_user
 WHERE regTime BETWEEN '2023-03-01' AND '2023-04-01';

在这里插入图片描述

–4、显示Java技术和.NET技术版块下的所有子版块（范围比较：IN包括两端的值）

 SELECT * FROM tbl_board
  WHERE parentId IN (1,2);

在这里插入图片描述

–5、列出BaiYun论坛中用户名为”qq”的用户发过帖子的全部版块名称（子查询：从帖子表中首先查出qq用户曾发过帖子的全部版块编号）

SELECT boardName
FROM tbl_board
WHERE boardId IN (
SELECT boardId
FROM tbl_topic t,tbl_user u
WHERE t.uId=u.uId AND u.uName='qq');

在这里插入图片描述

–6、使用where子句显示一个帖子发帖人和标题（两张表之间的联系，通过外键uId ）

SELECT uName,title
FROM tbl_topic t ,tbl_user u where t.uId = u.uId;

在这里插入图片描述

3）分组统计：

–1、统计帖子表中各版块的发帖数量。

SELECT boardId,COUNT(topicId)
FROM tbl_topic
GROUP BY boardId;

在这里插入图片描述

–2、显示发帖数量超过1条的全部版块（HAVING必须要写在GROUP BY后面）

SELECT boardId,COUNT(topicId) as '发帖量'
FROM tbl_topic
GROUP BY boardId
HAVING COUNT(topicId)>1;

在这里插入图片描述

–3、按注册时间降序显示用户表的数据。（ASC升序 | DESC降序）

SELECT * FROM tbl_user
ORDER BY regTime DESC;

在这里插入图片描述

–4、显示最新注册的3个用户（LIMIT 子句：用于限制被SELECT语句返回的行数）

SELECT * FROM tbl_user
ORDER BY regTime DESC
LIMIT 3;

在这里插入图片描述

–5、显示C#语言版块（boardId=5）和JAVA基础版块（boardId=8）下的全部帖子（使用UNION语句实现）

SELECT topicId,boardId,publishTime,title FROM tbl_topic
WHERE boardId=5
UNION
SELECT topicId,boardId,publishTime,title FROM tbl_topic
WHERE boardId=8;