MySQL 基础入门

MySQL 是最流行的关系型数据库管理系统，在 WEB 应用方面 MySQL 是最好的 RDBMS(Relational Database Management System：关系数据库管理系统)应用软件之一。

下面就开始一步一步的认识 MySQL 的真面目。

什么是数据库？

数据库（Database）是按照数据结构来组织、存储和管理数据的仓库。

每个数据库都有一个或多个不同的 API 用于创建，访问，管理，搜索和复制所保存的数据。

我们也可以将数据存储在文件中，但是在文件中读写数据速度相对较慢。

所以，现在我们使用关系型数据库管理系统（RDBMS）来存储和管理大数据量。所谓的关系型数据库，是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。

RDBMS 即关系数据库管理系统(Relational Database Management System)的特点：

1.数据以表格的形式出现
2.每行为各种记录名称
3.每列为记录名称所对应的数据域
4.许多的行和列组成一张表单
5.若干的表单组成database

RDBMS 术语

在我们开始学习MySQL 数据库前，让我们先了解下RDBMS的一些术语：

数据库: 数据库是一些关联表的集合。
数据表: 表是数据的矩阵。在一个数据库中的表看起来像一个简单的电子表格。
列: 一列(数据元素) 包含了相同类型的数据, 例如邮政编码的数据。
行：一行（=元组，或记录）是一组相关的数据，例如一条用户订阅的数据。
冗余：存储两倍数据，冗余降低了性能，但提高了数据的安全性。
主键：主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据。
外键：外键用于关联两个表。
复合键：复合键（组合键）将多个列作为一个索引键，一般用于复合索引。
索引：使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。
参照完整性: 参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件，目的是保证数据的一致性。

MySQL 为关系型数据库(Relational Database Management System), 这种所谓的"关系型"可以理解为"表格"的概念, 一个关系型数据库由一个或数个表格组成, 如图所示的一个表格:

表头(header): 每一列的名称;
列(col): 具有相同数据类型的数据的集合;
行(row): 每一行用来描述某条记录的具体信息;
值(value): 行的具体信息, 每个值必须与该列的数据类型相同;
键(key): 键的值在当前列中具有唯一性。

MySQL 安装、管理

安装过程很简单，网上也有很多资料，这里就不多讲了，具体参考：MySQL 安装

现在基本都是用可视化工具来管理数据库，这使得数据库的登录、连接变得很简单。所以这里也不做过多说明。

注意

下面要介绍的 MySQL 也是可以用可视化工具做的，不必使用命令行。可视化很简单，一看就会了，不做说明。

MySQL操作

下面我会分为数据库操作、数据表操作和数据操作（CUDR）来讲，重点是 CUDR 的讲解。

基础知识

创建数据库

语法格式：

CREATE DATABASE <数据库名>;

大部分情况我们使用下面这条命令：

CREATE DATABASE IF NOT EXISTS <数据库名> DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

该命令的作用：

如果数据库不存在则创建，存在则不创建。
创建数据库<数据库名>，并设定编码集为 utf8

删除数据库

语法格式：

drop database <数据库名>;

创建数据表

语法格式：

CREATE TABLE table_name (column_name column_type);

举个🌰：

CREATE TABLE IF NOT EXISTS `runoob_tbl`(
   `runoob_id` INT UNSIGNED AUTO_INCREMENT,
   `runoob_title` VARCHAR(100) NOT NULL,
   `runoob_author` VARCHAR(40) NOT NULL,
   `submission_date` DATE,
   PRIMARY KEY ( `runoob_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

🌰解析：

如果你不想字段为 NULL 可以设置字段的属性为 NOT NULL，在操作数据库时如果输入该字段的数据为NULL ，就会报错。
AUTO_INCREMENT定义列为自增的属性，一般用于主键，数值会自动加1。
PRIMARY KEY关键字用于定义列为主键。您可以使用多列来定义主键，列间以逗号分隔。
ENGINE 设置存储引擎，CHARSET 设置编码。

注意

表名和字段名外面的符号 ` 不是单引号，是反引号

MySQL 字段属性应该尽量设置为 NOT NULL

空值('')是不占空间的，NULL占空间

删除数据表

语法格式：

DROP TABLE table_name;

数据类型

MySQL中定义数据字段的类型对你数据库的优化是非常重要的。

MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。

数值类型

数值类型中又细分为整数类型、浮点数类型、定点数类型和位类型。

整数类型

整数类型	占用字节	范围
TINYINT	1	有符号：[-128,127] 或无符号：[0,255]
SMALLINT	2	有符号：[-32768,32767]或无符号：[0,65535]
MEDIUMINT	3	有符号：[-8333608,8388607]或无符号：[0,1677215]
INT、INTEGER	4	有符号:[-21亿多，21亿多]或无符号：[0,42亿多]
BIGINT	8	很大，19位数字的范围

记住 MySQL 的整数类型有5种，分别是 tinyint、smallint、mediumint、int 和 bigint。
另外还有两个特性：

所有整数类型都有一个可选的属性 UNSIGNED(无符号)，此时上限取值是原来的2倍。
还有一个属性是AUTO_INCREMENT(自增)，该属性只能用于整数类型。

浮点数类型

浮点数类型	占用字节	范围
FLOAT	4	[1.175494351E-38,3.402823466E+38]
DOUBLE	8	[2.2..E-308,1.7...E+308]

定点数类型

定点数类型	占用字节	范围
DECIMAL(M,D)	M+2	最大取值范围与DOUBLE相同，有效范围由M,D决定

位类型

位类型	占用字节	范围
DOUBLE	8	[2.2..E-308,1.7...E+308]

浮点数和定点数都可以用（M,D）的方式来进行表示。
（M,D）表示该值一共显示M位数字（整数位+小数位），其中D位位于小数点后面。

M：精度
D：标度
注意在一些表述货币等精确数字的场景必须使用DECIMAL类型。
BIT类型用来存放多为二进制数，数据在插入BIT类型字段时，首先会转换成二进制数。因此，直接使用SELECT命令将不会看到结果。可以用bin()或hex()函数进行读取。

字符串类型

MySQL中提供了多种对字符数据的存储类型，不同版本可能有所差异，本文以5.0版本为例。

字符串类型	描述
CHAR(M)	M为0-255之间的整数
VARCHAR(M)	M为0-65535之间的整数
TINYBLOB	允许0-255字节
BLOB	允许0-65535字节
MEDIUBLOB	允许0-167772150字节
LONGBLOB	允许0-4294967295字节
TINYTEXT	允许0-255字节
TEXT	允许0-65535字节
MEDIUMTEXT	允许0-167772150字节
LONGTEXT	允许0-4294967295字节
VARBINARY(M)	允许长度0-M个字节的边长字节字符串
BINARY(M)	允许0-M个字节的定长字节字符串

CHAR 和 VARCHAR 很类似，都用来保存 MySQL 中较短的字符串。二者的主要区别在于存储方式不同：

CHAR列的长度固定为创建时表明的长度；
而VARCHAR列中的值为可变长字符串。

比如存储字符串“abc”，CHAR(5)占用了5个字节，而VARCHAR(5)占用3个字节。

日期类型

分类：

表示年月日，用DATE
表示年月日时分秒，用DATETIME
表示时分秒，用TIME

日期时间类型	字节	范围
DATE	4	1000-01-01到9999-12-31
DATETIME	8	1000-01-01 00:00:00到9999-12-31 23:59:59
TIMESTAMP	4	19700101080001到2038年某个时刻
TIME	3	-838:59:59到838:59:59
YEAR	1	1901到2155

注意

TIMESTAMP 有一个特点就是和时区相关。

当插入日期时，会先转换为本地时区后存放；而从数据库里面取出时，也同样需要将日期转换为本地时区后显示。
查看当前时区的SQL：show variables like 'time_zone';

数据操作CUDR

插入数据

语法格式：

INSERT INTO 表名称 (列名1, 列名2,...) VALUES (值1, 值2,....);

举个🌰：

-- 向表 Persons 插入一条字段 LastName = JSLite 字段 Address = shanghai
INSERT INTO Persons (LastName, Address) VALUES ('JSLite', 'shanghai');

-- 向表 meeting 插入 字段 a=1 和字段 b=2
INSERT INTO meeting SET a=1,b=2;

-- SQL实现将一个表的数据插入到另外一个表的代码
-- 如果只希望导入指定字段，可以用这种方法：
-- INSERT INTO 目标表 (字段1, 字段2, ...) SELECT 字段1, 字段2, ... FROM 来源表;
INSERT INTO orders (user_account_id, title) SELECT m.user_id, m.title FROM meeting m where m.id=1;

删除数据

语法格式：

DELETE FROM 表名称 WHERE 列名称 = 值

举个🌰：

-- 在不删除table_name表的情况下删除所有的行，清空表。
DELETE FROM table_name
-- 或者
DELETE * FROM table_name

-- 删除 Person表字段 LastName = 'JSLite' 
DELETE FROM Person WHERE LastName = 'JSLite' 

-- 删除 表meeting id 为2和3的两条数据
DELETE from meeting where id in (2,3);

查询数据

语法格式：

SELECT 列名称 [*] FROM 表名称

举个🌰：

-- 表station取个别名叫s，表station中不包含 字段id=13或者14 的，并且id不等于4的 查询出来，只显示id
SELECT s.id from station s WHERE id in (13,14) and user_id not in (4);

-- 从表 Persons 选取 LastName 列的数据
SELECT LastName FROM Persons

-- 结果集中会自动去重复数据
SELECT DISTINCT Company FROM Orders 
-- 表 Persons 字段 Id_P 等于 Orders 字段 Id_P 的值，
-- 结果集显示 Persons表的 LastName、FirstName字段，Orders表的OrderNo字段
SELECT p.LastName, p.FirstName, o.OrderNo FROM Persons p, Orders o WHERE p.Id_P = o.Id_P 

-- gbk 和 utf8 中英文混合排序最简单的办法 
-- ci是 case insensitive, 即 “大小写不敏感”
SELECT tag, COUNT(tag) from news GROUP BY tag order by convert(tag using gbk) collate gbk_chinese_ci;
SELECT tag, COUNT(tag) from news GROUP BY tag order by convert(tag using utf8) collate utf8_unicode_ci;

更改数据

语法格式：

UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值

举个🌰：

-- update语句设置字段值为另一个结果取出来的字段
update user set name = (select name from user1 where user1 .id = 1 )
where id = (select id from user2 where user2 .name='小苏');

-- 更新表 orders 中 id=1 的那一行数据更新它的 title 字段
UPDATE `orders` set title='这里是标题' WHERE id=1;

数据过滤

`WHERE`子句

语法格式：

SELECT 列名称 FROM 表名称 WHERE 列 运算符 值

举个🌰：

-- 从表 Persons 中选出 Year 字段大于 1965 的数据
SELECT * FROM Persons WHERE Year>1965

-- MySQL 的 WHERE 子句的字符串比较是不区分大小写的。
-- 你可以使用 BINARY 关键字来设定 WHERE 子句的字符串比较是区分大小写的
SELECT * FROM Persons WHERE BINARY name="Jack"

注意

where：数据库中常用的是where关键字，用于在初始表中筛选查询。它是一个约束声明，用于约束数据，在返回结果集之前起作用。

group by：对select查询出来的结果集按照某个字段或者表达式进行分组，获得一组组的集合，然后从每组中取出一个指定字段或者表达式的值。

having：用于对where和group by查询出来的分组经行过滤，查出满足条件的分组结果。它是一个过滤声明，是在查询返回结果集以后对查询结果进行的过滤操作。

执行顺序

select –>where –> group by–> having–>order by

`AND`运算符

语法格式：

WHERE boolean_expression_1 AND boolean_expression_2

如果第一个条件和第二个条件都成立，则符合筛选条件。

举个🌰：

-- 删除 meeting 表字段 
-- id=2 并且 user_id=5 的数据  和
-- id=3 并且 user_id=6 的数据 
DELETE from meeting where id in (2,3) and user_id in (5,6);

-- 使用 AND 来显示所有姓为 "Carter" 并且名为 "Thomas" 的人：
SELECT * FROM Persons WHERE FirstName='Thomas' AND LastName='Carter';

`OR`运算符

语法格式：

WHERE boolean_expression_1 OR boolean_expression_2

如果第一个条件和第二个条件中只要有一个成立，则符合筛选条件。

举个🌰：

-- 使用 OR 来显示所有姓为 "Carter" 或者名为 "Thomas" 的人：
SELECT * FROM Persons WHERE firstname='Thomas' OR lastname='Carter'

`IN`运算符

语法格式：

SELECT "字段名"FROM "表格名"WHERE "字段名" IN ('值一', '值二', ...);

IN 操作符允许我们在 WHERE 子句中规定多个值，每个值都进行匹配。IN 取值规律，由逗号分割，全部放置括号中。

举个🌰：

-- 从表 Persons 选取 字段 LastName 等于 Adams、Carter
SELECT * FROM Persons WHERE LastName IN ('Adams','Carter')

注意

还存在NOT IN，用法与之相反。

`BETWEEN`运算符

语法格式：

expr [NOT] BETWEEN begin_expr AND end_expr;

expr 是一个表达式，它用于测试值是否在定义的范围begin_expr 和 end_expr 之间。

expr， begin_expr和end_expr必须具有相同的数据类型。

举个🌰：

-- 查找购买价格在90美元和100美元之间的产品（AND）
SELECT productName, buyPrice FROM products WHERE buyPrice >= 90 AND buyPrice <= 100;

-- 等同于下面使用 BETWEEN AND
SELECT productName, buyPrice FROM products WHERE buyPrice BETWEEN 90 AND 100;

BETWEEN...AND是给定的范围是包含第一个值和第二个值的

注意

还存在NOT BETWEEN...AND，用法与之相反。

`LIMIT`子句

LIMIT子句在SELECT语句中用于约束结果集中的行数。LIMIT子句接受一个或两个参数。两个参数的值必须为零或正整数。

SELECT column1,column2,... FROM table_name LIMIT offset, count;

语法解释如下：

offset指定第一行要返回的偏移量。第一行的偏移量（offset）为 0，而不是1；
count指定要返回最大的行数；

MySQL LIMIT子句简介

举个🌰：

-- 只有一个参数的情况，LIMIT score 相当于 LIMIT 0, score;
-- 查询students表中成绩最好的5名同学姓名
SELECT name FROM students ORDER BY score LIMIT 5;

-- 查询students表中成绩第二好的同学姓名
SELECT name FROM students ORDER BY score LIMIT 1, 1;

`LIKE`子句

语法：

SELECT field1, field2,...fieldN 
FROM table_name
WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'

WHERE 子句中可以使用等号 = 来设定获取数据的条件，而使用LIKE就是为了获取模糊的查询条件。

如果没有使用百分号 %, LIKE 子句与等号 = 的效果是一样的。

LIKE匹配/模糊匹配，会与%、_和escape(默认的转义字符是\)结合使用。

'%a'     //以a结尾的数据
'a%'     //以a开头的数据
'%a%'    //含有a的数据
'_a_'    //三位且中间字母是a的
'_a'     //两位且结尾字母是a的
'a_'     //两位且开头字母是a的
'%\_20%' //默认的转义字符是'\'
'%$_20%' ESCAPE '$' //通过 ESCAPE 子句,指定使用 $ 作为转义符

注意

MySQL 允许您结合NOT运算符和LIKE运算符来查找不匹配特定模式的字符串。

例如：查找不含“张”开头的人员信息
SELECT * FROM employees WHERE name NOT LIKE '张%'; 

补充：

MySQL 中使用 REGEXP 操作符来进行正则表达式匹配。

例如：

SELECT name FROM person_tbl WHERE name REGEXP '^[aeiou]|ok$';

由于是入门教程，此处略过。

`NULL`值处理

语法格式：

value IS [NOT] NULL

判断值是否为NULL

举个🌰：

-- 获取成绩为 NULL 的同学姓名
SELECT name FROM students WHERE score IS NULL;

-- 获取成绩不为 NULL 的同学姓名
SELECT name FROM students WHERE score IS NOT NULL;

数据排序

`ORDER BY`子句

ORDER BY 子句是对查询到的结果集排序，它的作用是：

按单列或多列对结果集排序
按升序或降序对不同列进行结果集排序

语法格式：

SELECT column1, column2,...
FROM tbl
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC],...

ASC代表升序，DESC代表降序。如果未指定排序规则，默认情况下，ORDER BY子句按升序ASC对结果集进行排序。

举个🌰：

-- students表中按成绩降序排列出来
SELECT name FROM students ORDER BY score DESC

-- students表中按成绩降序,名字升序排列出来
SELECT name FROM students ORDER BY score DESC，name ASC;

-- students表中按姓名和成绩权重值 sw 排序
SELECT name, score*weight as sw FROM students ORDER BY name, sw;

MySQL自定义排序函数FIELD()

ORDER BY子句允许您使用FIELD()函数为列中的值定义自己的排序顺序。

举个🌰：

-- 自定义 status 字段按 In Process、Cancelled、Resolved顺序进行排序
SELECT 
    orderNumber, status
FROM
    orders
ORDER BY FIELD(status,
        'In Process',
        'Cancelled',
        'Resolved');

MySQL 入门部分先告一段落，进阶部分的知识点还有聚合函数、数据分组、UNION、JOIN、嵌套子查询、索引...

这些都是后话，每一个都能单独拿出来写一篇文章。

参考：

posted @ 2020-05-05 02:09 奔跑的瓜牛阅读(639) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

瓜牛

好记性不如博客园！

MySQL 基础入门

什么是数据库？

RDBMS 术语

MySQL 安装、管理

MySQL操作

基础知识

创建数据库

删除数据库

创建数据表

删除数据表

数据类型

数值类型

字符串类型

日期类型

数据操作CUDR

插入数据

删除数据

查询数据

更改数据

数据过滤

`WHERE`子句

`AND`运算符

`OR`运算符

`IN`运算符

`BETWEEN`运算符

`LIMIT`子句

`LIKE`子句

`NULL`值处理

数据排序

`ORDER BY`子句

公告

瓜牛

好记性不如博客园！

MySQL 基础入门

什么是数据库？

RDBMS 术语

MySQL 安装、管理

MySQL操作

基础知识

创建数据库

删除数据库

创建数据表

删除数据表

数据类型

数值类型

字符串类型

日期类型

数据操作CUDR

插入数据

删除数据

查询数据

更改数据

数据过滤

WHERE子句

AND运算符

OR运算符

IN运算符

BETWEEN运算符

LIMIT子句

LIKE子句

NULL值处理

数据排序

ORDER BY子句

公告

`WHERE`子句

`AND`运算符

`OR`运算符

`IN`运算符

`BETWEEN`运算符

`LIMIT`子句

`LIKE`子句

`NULL`值处理

`ORDER BY`子句