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), 这种所谓的"关系型"可以理解为"表格"的概念, 一个关系型数据库由一个或数个表格组成, 如图所示的一个表格:

img

  • 表头(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 设置编码。

注意

  1. 表名和字段名外面的符号 ` 不是单引号,是反引号
  2. MySQL 字段属性应该尽量设置为 NOT NULL
  3. 空值('')是不占空间的,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_exprend_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、嵌套子查询、索引...

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

参考:

Mysql都支持哪些数据类型?

码农教程-MySQL入门教程

写给初学者的 SQL 入门教程

21分钟MySQL基础入门

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