基于MySQL的数据库操作实验

发表于 | 分类于
字数统计: 2,389

安装MySQL

在MySQL官网下载社区版，我下载的是8.0.17版本。安装时选择Developer Default 一路执行，可以看到有安装上Server/Workbench/For Excel/Shell/Router 以及 C++/Python/NET等多种语言Connector

默认使用TCP/IP协议，端口为3306

设置超级用户root的密码l**3

创建一个普通用户–我的名字–123456 ，如果把host改成localhost 将只能在本地登录，身份设置为DB Admin

windows下服务名为MySQL80 我喜欢手动开启不常用的服务，这里没有选择开机启动

Router暂时用不到，没有配置

安装完毕自动启动了一个shell和一个workbench

基本操作

登录

workbench→Database→connect to database→输入用户名→store in…输入密码，进入主界面

新建数据库

选择schemas中的一个数据库右键create schema ,命名创建

修改数据库

选中要修改的数据库，右键alter schema

删除数据库

选中要修改的数据库，右键drop schema

新建、修改、删除数据表

注意：所有的操作最后都需要进行刷新才能看到效果，右键数据表refresh all

选中要建表的数据库中的tables, 右键新建。点击column name 下的空白格按需求新建表项，Apply之后左侧就会有相应的信息

修改和删除同数据库操作

添加用户

选择server→users and privileges →查看已登录账号的Administrative roles中是否有useradmin权限，若有→add acount

安装PowerDesigner

一路next，地区选USA，最后覆盖一下dll文件

基本操作

新建概念模型

右键workspace→new→conceptual data model

新建完之后选中右边的entity, 然后点击中间空白处新建一个Entity ,右键properties进行属性Attributes设置

建立Entity之间的联系，就是用relationship 和inheritance 把关系连起来

注：概念模型与逻辑模型

建立物理模型

工具栏tools→generate physical data model →选择相应的DBMS

注意：先建立了概念模型之后才能生成相应的物理模型

生成完毕会多一个物理模型编辑窗口，选中关系连线如relationship，右键properties，可以在preview中查看代码。在join中选择要建立联系的属性

检验模型的正确性

tools → check model

配置数据源

windows系统 控制面板 — 管理工具 — ODBC数据源 — 用户DSN — 添加 MySQL OBDC 8.0 Unicode Driver

填上名称，主机127.0.0.1 管理员账密 ，用Test测试一下连接 ，不是开机启动的话，要在services.msc 服务中开启 MySQL80 。连接成功后选择一个数据库

由物理模型自动生成MySQL表

选中物理模型编辑页，打开工具栏 database → connect →ODBC machine datasource →选择配置的数据源，输入账密。

连接成功后， database → generate database

生成的SQL语句要进行正确性验证之后，才能执行真正生成数据库。打开MySQL workbench , open SQL Script运行

refresh all 即可在MySQL看到来自Powerdesigner的模型生成的数据库

在实验楼中完成操作部分实验

实验中值得注意的地方

CHAR 和 VARCHAR 的区别

CHAR 的长度是固定的，而 VARCHAR 的长度是可以变化的，比如，存储字符串 “abc”，对于 CHAR(10)，表示存储的字符将占 10 个字节(包括 7 个空字符)，而同样的 VARCHAR(12) 则只占用4个字节的长度，增加一个额外字节来存储字符串本身的长度，12 只是最大值，当你存储的字符小于 12 时，按实际长度存储。

ENUM和SET的区别

ENUM 类型的数据的值，必须是定义时枚举的值的其中之一，即单选，而 SET 类型的值则可以多选。

SQL约束

MySQL通常的约束：主键(primary key)、默认值(default)、唯一(unique)、外键(foreign key)、非空(not null)

一个表可以有多个外键，每个外键必须 REFERENCES (参考) 另一个表的主键，被外键约束的列，取值必须在它参考的列中有对应值。外键既能确保数据完整性，也能表现表之间的关系。

如 FOREIGN KEY(sid) REFERENCES student(sid)

常用操作语句

单表操作
1
2
3
4
5
6
SELECT name,age FROM employee WHERE age>25 AND age<30; // 不包含边界
SELECT name,age FROM employee WHERE age BETWEEN 25 AND 30 ; // 包含
SELECT name,age,phone,in_dpt FROM employee WHERE in_dpt IN ('dpt1','dpt3'); // 从不连续的范围中找
SELECT name,age,phone FROM employee WHERE name LIKE 'J%';
SELECT name,age,salary,phone FROM employee ORDER BY salary DESC;
SELECT MAX(salary) AS max_salary,MIN(salary) FROM employee; // as重命名

关键字 LIKE 用于实现模糊查询，常见于搜索功能中。和 LIKE 联用的通常还有通配符，代表未知字符。SQL中的通配符是 _ 和 % 。其中 _ 代表一个未指定字符，% 代表不定个未指定字符

默认情况下，ORDER BY 的结果是升序排列，而使用关键词 ASC 和 DESC 可指定升序或降序排序

5 个内置函数： COUNT 函数可用于任何数据类型(因为它只是计数)，而 SUM 、AVG 函数都只能对数字类数据类型做计算，MAX 和 MIN 可用于数值、字符串或是日期时间数据类型

多表操作
子查询

只有在结果来自一个表时才有用。

1
2
3
4
SELECT of_dpt,COUNT(proj_name) AS count_project 
FROM project GROUP BY of_dpt 
HAVING of_dpt IN
(SELECT in_dpt FROM employee WHERE name='Tom');

HAVING 关键字可以的作用和 WHERE 是一样的，都是说明接下来要进行条件筛选操作。区别在于 HAVING 用于对分组后的数据进行筛选

连接查询

结果来自多个表时，要进行连接

1
2
3
4
5
6
7
8
9
SELECT id,name,people_num
FROM employee,department
WHERE employee.in_dpt = department.dpt_name
ORDER BY id;
//or
SELECT id,name,people_num
FROM employee JOIN department
ON employee.in_dpt = department.dpt_name
ORDER BY id;
综合应用

例：使用连接查询的方式，查询出各员工所在部门的人数与工程数，工程数命名为 count_project。

1
2
3
4
SELECT name, people_num, COUNT(proj_name) AS count_project
FROM employee, department, project
WHERE in_dpt = dpt_name AND of_dpt = dpt_name
GROUP BY name, people_num;
数据库操作

目前 Mysql 没有提供重命名数据库名称的方法。

重命名表名称

1
2
3
4
5
RENAME TABLE 原名 TO 新名字;

ALTER TABLE 原名 RENAME 新名;

ALTER TABLE 原名 RENAME TO 新名;

新建：create 删除：drop

列操作
1
2
3
ALTER TABLE 表名字 ADD COLUMN 列名字 数据类型 约束;
或：
ALTER TABLE 表名字 ADD 列名字 数据类型 约束;

新增加的列，被默认放置在这张表的最右边。如果要把增加的列插入在指定位置，则需要在语句的最后使用AFTER关键词(“AFTER 列1” 表示新增的列被放置在 “列1” 的后面)。如果想放在第一列的位置，则使用 FIRST 关键词

如 ALTER TABLE employee ADD weight INT(4) DEFAULT 120 AFTER age;

删除列: 用drop代替add

重命名: 重命名语句后面的 “数据类型” 不能省略。 

1
ALTER TABLE 表名字 CHANGE 原列名 新列名 数据类型 约束;

当原列名和新列名相同的时候，指定新的数据类型或约束，就可以用于修改数据类型或约束

另外一种改变数据类型的方式

1
ALTER TABLE 表名字 MODIFY 列名字 新数据类型;
值操作
1
2
UPDATE 表名字 SET 列1=值1,列2=值2 WHERE 条件;
DELETE FROM 表名字 WHERE 条件;

注意都要加上WHERE关键字，不用的话相当于对整个表进行操作，即整列修改或删除表中所有记录

其他操作
索引

建立索引可以加快查询速度。

1
2
3
4
5
ALTER TABLE 表名字 ADD INDEX 索引名 (列名);
//or
CREATE INDEX 索引名 ON 表名字 (列名);
//查看索引
SHOW INDEX FROM 表名字;

在使用 SELECT 语句查询的时候，语句中 WHERE 里面的条件，会自动判断有没有可用的索引。

视图

作为提供给用户的虚拟表。

  • 数据库中只存放了视图的定义，而没有存放视图中的数据，这些数据存放在原来的表中；
  • 使用视图查询数据时，数据库系统会从原来的表中取出对应的数据；
  • 视图中的数据依赖于原来表中的数据，一旦表中数据发生改变，显示在视图中的数据也会发生改变；
  • 在使用视图的时候，可以把它当作一张表。
1
CREATE VIEW 视图名(列a,列b,列c) AS SELECT 列1,列2,列3 FROM 表名字;

表的操作语句同样适用于视图。

导入

纯数据文件：LOAD DATA INFILE '文件路径和文件名' INTO TABLE 表名字; 导入导出的文件都必须在指定的路径下进行，在 mysql 终端中查看路径变量：show variables like '%secure%'; 在移动至该路径时需要权限，所以实现了安全的导入。

SQL语句：source *.sql 可以在文件中包含更多的操作

导出

只保存数据

SELECT 列1，列2 INTO OUTFILE '文件路径和文件名' FROM 表名字; 不能有同名文件

备份与恢复

把数据库的结构，包括数据、约束、索引、视图等全部另存为一个文件。

mysqldump 是 MySQL 用于备份数据库的实用程序。它主要产生一个 SQL 脚本文件，其中包含从头重新创建数据库所必需的命令 CREATE TABLE INSERT 等。