【数据库-MySQL-从入门到精通】【学习笔记】

stoneWang_L

已于 2023-07-06 18:09:54 修改

阅读量200

点赞数

文章标签：数据库 mysql

于 2023-01-08 16:26:51 首次发布

原文链接：http://c.biancheng.net/view/7178.html

版权

鸣谢：
参考文章：https://www.cnblogs.com/wupeiqi/articles/5713315.html

1.MySQL安装

1.1 官网下载

参考：http://c.biancheng.net/view/2391.html
http://dev.mysql.com/downloads/mysql/
在这里插入图片描述

2.MySQL数据库的基本操作

参考：http://c.biancheng.net/view/2413.html

2.1 登录：

mysql -u root -p

2.2 查看所有数据库

列出当前用户可查看的所有数据库：
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| world              |
+--------------------+

2.3 创建并查看数据库

先创建一个名为 test_db 的数据库：

mysql> CREATE DATABASE test_db;
Query OK, 1 row affected (0.01 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| test_db            |
| world              |
+--------------------+
7 rows in set (0.00 sec)

mysql>

2.4 MySQL修改数据库：ALTER DATABASE用法简介

在 MySQL 中，可以使用 ALTER DATABASE 来修改已经被创建或者存在的数据库的相关参数。修改数据库的语法格式为：

ALTER DATABASE [数据库名] { 
[ DEFAULT ] CHARACTER SET <字符集名> |
[ DEFAULT ] COLLATE <校对规则名>}

语法说明如下：
ALTER DATABASE 用于更改数据库的全局特性。
使用 ALTER DATABASE 需要获得数据库 ALTER 权限。
数据库名称可以忽略，此时语句对应于默认数据库。
CHARACTER SET 子句用于更改默认的数据库字符集。

例子：

mysql> SHOW CREATE DATABASE test_db;
+----------+------------------------------------------------------------------------------------------------------------
-----------------------+
| Database | Create Database
                       |
+----------+------------------------------------------------------------------------------------------------------------
-----------------------+
| test_db  | CREATE DATABASE `test_db` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEF
AULT ENCRYPTION='N' */ |
+----------+------------------------------------------------------------------------------------------------------------
-----------------------+
1 row in set (0.00 sec)

mysql> ALTER DATABASE test_db
    -> DEFAULT CHARACTER SET gb2312;
Query OK, 1 row affected (0.01 sec)

mysql> SHOW CREATE DATABASE test_db;
+----------+-------------------------------------------------------------------------------------------------------+
| Database | Create Database                                                                                       |
+----------+-------------------------------------------------------------------------------------------------------+
| test_db  | CREATE DATABASE `test_db` /*!40100 DEFAULT CHARACTER SET gb2312 */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+-------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

2.5 MySQL删除数据库（DROP DATABASE语句）

在 MySQL 中，当需要删除已创建的数据库时，可以使用 DROP DATABASE 语句。其语法格式为：

DROP DATABASE [ IF EXISTS ] <数据库名>

语法说明如下：
<数据库名>：指定要删除的数据库名。
IF EXISTS：用于防止当数据库不存在时发生错误。
DROP DATABASE：删除数据库中的所有表格并同时删除数据库。使用此语句时要非常小心，以免错误删除。如果要使用 DROP DATABASE，需要获得数据库 DROP 权限。

例子

mysql> CREATE DATABASE test_db_del;
Query OK, 1 row affected (0.00 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| db_test            |
| db_test_db         |
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| test_db            |
| test_db_char       |
| test_db_del        |
| world              |
+--------------------+
11 rows in set (0.00 sec)

mysql> DROP DATABASE test_db_del;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| db_test            |
| db_test_db         |
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| test_db            |
| test_db_char       |
| world              |
+--------------------+
10 rows in set (0.00 sec)

mysql>

2.6 MySQL选择数据库（MySQL USE语句）

在 MySQL 中，USE 语句用来完成一个数据库到另一个数据库的跳转。
当用 CREATE DATABASE 语句创建数据库之后，该数据库不会自动成为当前数据库，需要用 USE 来指定当前数据库。其语法格式为：

USE <数据库名>

例子：

mysql> USE test_db;
Database changed
mysql>

2.6 MySQL注释：单行注释和多行注释

注释在 SQL 语句中用来标识说明或者注意事项的部分，对 SQL 的执行没有任何影响。因此，注释内容中无论是英文字母还是汉字都可以随意使用。
MySQL 注释分为单行注释和多行注释，下面分别来介绍这两种注释。

MySQL 单行注释
#注释内容
-- 注释内容
MySQL 多行注释
/*
  第一行注释内容
  第二行注释内容
*/

2.7 MySQL中SQL语句的大小写规则

SQL 语句的大小写规则与语句组成元素、引用内容和服务器所使用的操作系统有关。

1、 SQL 关键字和函数名
SQL 的关键字和函数名不区分大小写。例如，下面这些语句都是等价的：
SELECT NOW();
select now();
sElEcT nOw();

2、数据库名、表名和视图名
MySQL 用服务器主机的底层文件系统所包含的目录和文件来表示数据库和表。因此，数据库名和表名的默认大小写取决于服务器主机的操作系统在命名方面的规定。

比如 Windows 系统的文件名不区分大小写，所以运行在 Windows 系统上面的 MySQL 服务器也不用区分数据库名和表名的大小写。Linux 系统的文件名区分大小写，所以运行在 Linux 系统上的 MySQL 服务器需要区分数据库名和表名的大小写。对于 Mac OS X 平台，其文件系统中的名字是个例外，它们不区分大小写。

MySQL 会使用一个文件来表示一个视图，所以以上与表有关的也同样适用于视图。

3、存储程序的名字
存储函数、存储过程和事件的名字都不区分大小写。触发器的名字要区分大小写，这一点与标准 SQL 的行为有所不同。

4、列名和索引名
在 MySQL 里，列名和索引名都不区分大小写。例如，下面这些语句都是等价的：
SELECT name FROM student ;
SELECT NAME FROM student ;
SELECT nAmE FROM student ;

5、别名的名字
默认情况下，表的别名要区分大小写。SQL 语句中可以使用任意的大小写（大写、小写或大小写混用）来指定一个别名。如果需要在同一条语句里多次用到同一个别名，则必须让它们的大小写保持一致。

	Windows	Linux
数据库名	否（忽略大小写）	是
表名	否（忽略大小写）	是
表别名	否（忽略大小写）	是
列名	否（忽略大小写）	否（忽略大小写）
列别名	否（忽略大小写）	否（忽略大小写）
变量名	否（忽略大小写）	是

通俗的说就是，MySQL 在 Windows 系统下不区分大小写，但在 Linux 系统下默认区分大小写。因此，数据库名、表名和字段名，都不允许出现任何大写字母，避免节外生枝。
一般建议统一使用小写字母，并且 InnoDB 引擎在其内部都是以小写字母方式来存储数据库名和表名的。这样可以有效的防止 MySQL 产生大小写问题。

3.数据库常用语句

3.1 对数据库的操作

数据库就好比文件夹，数据表就是文件夹下的文件

# 创建数据表库db1
create database db1;
# 查看所有数据库
show databases;
# 删除数据库 db1
drop database db1;
# 修改
不能修改数据库，只能删除数据库，重新创建

3.2 对数据表的操作

# 创建数据表库db2
create database db2;
create database db2 default charset utf8; # 创建字符集为utf8的数据库db2
# 选择数据库db2 
use db2;
# 显示所有数据表
show tables;
# 创建数据表 t1 其中，t1包含的列为id（int类型），name（char类型，就是字符类型，10个字符）
create table t1(id int, name char(10));
create table t1(id int, name char(10)) default charset=utf8; #在上面基础上，设置表的字符集为utf8
create table t1(id int, name char(10)) engine=innodb default charset=utf8;  # 在上面基础上，设置鼠标的引擎为innodb
# innodb 支持事务，原子性操作
# myisam 不支持事务，全局索引速度快
可以换行写
create table t1(
	# 列名 类型 null/not null,
	id int, 
	name char(10)
) engine=innodb default charset=utf8; 

# 设置列可为空
create table t2(
	# 列名 类型 null/not null,
	id int not null, 
	name char(10) null
) engine=innodb default charset=utf8; 

# 设置列自增（从1开始）
# 在mysql中有一个约束，如果是自增的，必须是key
create table t3(
	# 列名 类型 null/not null,
	id int auto_increment primary key, 
	name char(10)
) engine=innodb default charset=utf8; 

auto_increment 表示：自增
primary key 表示：约束（不能重复且不能为空）；加速查找 


清空表：
delete from t3;# 清空后，主键在之前基础上自增
truncate table t1; # 清空后，主键从1开始自增


删除表：
drop table t1;

3.2 对数据表内数据的操作

查看数据
# 查看表里面的数据
select * from t1;

插入数据
# 给表中插入/新增数据
# 给t1中的两列id 和 name插入数据
insert into t1(id, name) value(1, 'root');

进度========================1-7完成，1-8待开始

3.数据库涉及概述

参考：http://c.biancheng.net/view/8039.html

3.1 数据库设计概述

数据库设计就是根据业务系统的具体需求，结合我们所选用的数据库，建立好表结构及表与表之间的管理关系，为这个业务系统构造出最优秀的数据存储模型的过程。使之能有效的对应用的数据进行存储，并高效的对已经存储的数据进行访问。

糟糕的数据库设计表现在以下几个方面：

访问数据效率低下
存在大量的数据冗余，浪费存储空间
更新和检索数据时会出现许多问题

良好的数据库设计表现在以下几方面：

访问效率高
减少数据冗余，节省存储空间，便于进一步扩展
可以使应用程序的开发变得更容易

3.2 数据库设计的基本步骤

需求分析
本阶段的主要任务是对现实世界中要处理的对象（公司、部门及企业，也可以理解成客户）进行详细调查，然后通过分析，逐步明确客户/用户对系统的需求，包括数据需求和业务处理需求。
概要设计
概要设计是数据库设计的关键，通过综合、归纳与抽象用户需求，形成一个具体 DBMS 的概念模型，也就是绘制数据库的 E-R 图。
E-R 图主要用于在项目团队内部，设计人员和客户之间进行沟通，确认需求信息的正确性和完整性。
逻辑结构设计
将 E-R 图转换为多张表，进行逻辑设计，确认各表的主外键，并应用数据库设计的三大范式进行审核，对其优化。
在这阶段，E-R 图非常重要。大家要学会根据各个实体定义的属性来画出总体的 E-R 图。
物理设计阶段
经项目组开会讨论确定 E-R 图后，根据项目的技术实现，团队开发能力及项目的成本预算，选择具体的数据库（如 MySQL 或 Oracle 等）进行物理实现。
数据库实施阶段
运用 DBMS 提供的数据语言（例如 SQL）、工具及宿主语言（例如 Java），根据逻辑设计和物理设计的结果建立数据库，编制与调试应用程序，组织数据入库，并进行试运行。
数据库运行和维护阶段
数据库应用系统经过试运行后即可投入正式运行。在运行过程中必须不断地对其进行评价、调整与修改。

数据库基础-命令行形式

连接：
	show databases;
	use {数据库名称};
	show tables;
	select * from 表名;
	默认：用户root
创建用户：
create user 'alex'@'192.168.1.1' identified by '123123';
create user 'alex'@'192.168.1.%' identified by '123123';
create user 'alex'@'%' identified by '123123';
授权：
	权限 人
	grant select,insert,update on db1.t1 to 'alex'@'%'

3.1 MySQL操作数据库和数据表

3、学习SQL语句规则
	操作文件夹(数据库)
		create database db2; // 创建db2这个数据库
		create database db3 default charset utf8; // 创建db2这个数据库，并且设置字符编码为utf8，中文编码(新版本的mysql已经默认支持了，不用特地添加配置)

		show databases;
		drop database db2;  // 删除db2这个数据库
	操作文件(数据表)
		show tables;
		create table t1(id int, name char(10));
		create table t1(id int, name char(10)) default charset=utf8;
		create table t1(id int, name char(10)) engine=innodb default charset=utf8;

		// 是否可空，null表示空，非字符串
		create table t1(
			列名 类型 null, //表示，- 不可空
			列名 类型 not null, //表示，- 可空
		)

		// 默认值，创建列时可以指定默认值，当插入数据时如果未主动设置，则自动添加默认值
		create table t1(
			列名 类型 not null auto_increment, //自增
			列名 类型 not null default, //表示，- 可空
		)
		// auto_increment 表示自增的，但是必须同时是key，
		// primary key主键:表示约束（不能重复且不能为空）;加速查找
		create table t3(id int auto_increment primary key, name char(10)) engine=innodb default charset=utf8;
		create table t2(
			id int not null auto_increment primary key,
			name char(10)
		)engine=innodb default charset=utf8;

		innodb 支持事务
		myisam 不支持事务，全局索引速度快

	清空表：
		delete from t2; // 清空后，自增ID不归零。速度慢
		insert into t2(name) value ('alex');
		truncate table t2; // 清空后，自增ID归零。速度快
	删除表：
		drop table t1;

		select * from t1;
		插入数据;
		insert into t1(id,name) values (1,'alex');
		insert into t1(id,name) values (1,'爱丽丝');

3.2 MySQL数据表基本数据类型

数据类型：
	数字：
		tinyint
		int
		bigint
		
		FLOAT
		DOUBLE
		decimal 精准的小数（底层实现是字符串实现）
	create table t1(
		id int not null auto_increment primary key,
		num decimal(10,5), //
		name char(10)
	)engine=innodb default charset=utf8;
	// decimal[(m[,d])] [unsigned] [zerofill]
    // 准确的小数值，m是数字总个数（负号不算），d是小数点后个数。 m最大值为65，d最大值为30。
	字符串：
		char(10) //速度快
		// char数据类型用于表示固定长度的字符串，可以包含最多达255个字符。其中m代表字符串的长度。
        // PS: 即使数据小于m长度，也会占用m长度
		varchar(10) //好处节省空间
		// varchars数据类型用于变长的字符串，可以包含最多达255个字符。其中m代表该数据类型所允许保存的字符串的最大长度，只要长度小于该最大值的字符串都可以被保存在该数据类型中。
		// 注：虽然varchar使用起来较为灵活，但是从整个系统的性能角度来说，char数据类型的处理速度更快，有时甚至可以超出varchar处理速度的50%。因此，用户在设计数据库时应当综合考虑各方面的因素，以求达到最佳的平衡
	// 定长字段放表前面，找起来速度快
		text
		// text数据类型用于保存变长的大字符串，可以组多到65535 (2**16 − 1)个字符。
		mediumtext
        // A TEXT column with a maximum length of 16,777,215 (2**24 − 1) characters.
        longtext
        // A TEXT column with a maximum length of 4,294,967,295 or 4GB (2**32 − 1) characters.
        上传文件：
        	文件存硬盘
        	db存路径
	时间类型：
		DATETIME
        // YYYY-MM-DD HH:MM:SS（1000-01-01 00:00:00/9999-12-31 23:59:59    Y）
	
	其他：
        enum
        // 枚举类型，
        // An ENUM column can have a maximum of 65,535 distinct elements. (The practical limit is less than 3000.)
           示例：
               CREATE TABLE shirts (
                   name VARCHAR(40),
                   size ENUM('x-small', 'small', 'medium', 'large', 'x-large')
               );
               INSERT INTO shirts (name, size) VALUES ('dress shirt','large'), ('t-shirt','medium'),('polo shirt','small');

       set
       // 集合类型
       // A SET column can have a maximum of 64 distinct members.
           示例：
               CREATE TABLE myset (col SET('a', 'b', 'c', 'd'));
               INSERT INTO myset (col) VALUES ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');