mysql详细教程大全_maysq教程

SQL简介：

SQL（Structured Query Language）：是一种结构化查询语言，用于访问和处理关系型数据库系统的计算机标准语言。

 

关系型数据库系统(RDBMS：Relational Database Management System)

表

RDBMS中的数据存储在称为表的数据库对象中。这个表基本上是一个相关数据条目的集合，它由许多列和行组成。下图是adver_user（广告主表）的示例：

 

字段

　每个表都被分解成更小的实体，称为字段。adver_user表中的字段由：广告主ID、广告主名称、广告主手机号和创建时间组成。

　字段是表中的列，用于维护有关表中每条记录的特定信息。

列

　列是表中的垂直实体，其中包含与表中特定字段关联的所有信息。

行数据

　记录也称为数据行，即表中存在的每个单独的条目。例如，上面的adver_user表中有3条记录。

MySQL数据库服务器

一：下载

安装地址：https://dev.mysql.com/downloads/mysql

 

二：解压并启动MySQL服务器

将下载好的压缩包解压。并以管理员身份打开命令行，进入 MySQL 服务器安装的目录：D:\software\mysql-5.7.18-winx64\bin ，执行以下命令启动 MySQL。

① mysqld -install

② net start mysql

SQL标准命令：

DDL（数据定义语言）

    数据定义语言用于改变数据库结构，包括创建、更改和删除数据库对象。用于操纵表结构的数据定义语言命令有：

CREATE TABLE-- 创建（在数据库中创建新表、表视图或其他对象）

ALTER TABLE-- 更改 （修改现有的数据库对象，如表）

DROP TABLE-- 删除  （删除数据库中的整个表、表或其他对象的视图）

DML（数据操纵语言）

    数据操纵语言用于检索、插入和修改数据，数据操纵语言是最常见的SQL命令。

    数据操纵语言命令包括：

INSERT-- 插入 （创建记录）

DELETE-- 删除 （删除记录）

UPDATE-- 修改（修改记录）

SELECT -- 检索 （从一个或多个表检索某些记录）

DCL（数据控制语言）

    数据控制语言为用户提供权限控制命令。

    用于权限控制的命令有：

GRANT-- 授予权限

REVOKE-- 撤销已授予的权限

数据库教程

数据库的连接

mysql -h 数据库IP地址 -P 端口号 -u 用户名 -p 密码

数据库的相关操作

一、查询已有数据库列表

show databases;

二、创建数据库

CREATE DATABASE 数据库名字 [库选项];

#CREATE DATABASE test_table charset utf8;

三、查看数据库的创建语句

show create database 数据库名字;

四、修改数据库

注意：数据库的名字不可修改。

alter database 数据库名字 [库选项];

五、删除数据库：危险操作！！！

drop database [if exists] 数据库名字;

 

六、进入/使用数据库

use 数据库名;

数据表的相关操作

一、表的创建

创建表的语句格式：

Create table [if not exists] 表名(

字段名字 数据类型 [列约束],

字段名字 数据类型 [列约束] ##最后一行不需要逗号

)[表选项];

创建一个名为“person”的表：

CREATE TABLE IF NOT EXISTS `person` (

   `id` INT UNSIGNED AUTO_INCREMENT,

   `name` VARCHAR (10) NOT NULL DEFAULT “Tom”,

   `age` int (3) NOT NULL,

   `birthday` DATE,

   PRIMARY KEY (`id`)

)ENGINE=InnoDB DEFAULT CHARSET=utf8;

约束（Constraints）

NOT NULL - 指示某列不能存储 NULL 值。eg：写在列中

UNIQUE - 保证某列的每行必须有唯一的值。eg：UNIQUE (列名)

PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。eg：RIMARY KEY (列名)

FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。eg：FOREIGN KEY (该表列名) REFERENCES 外表名(外表列名)

CHECK - 保证列中的值符合指定的条件。eg：CHECK(id > 0)

DEFAULT - 规定没有给列赋值时的默认值。eg：写在列中

AUTO_INCREMENT- 会在新记录插入表中时生成一个唯一的数字。eg：写在列中

Tip:

字符集: charset/character set 具体字符集; ##保证表中数据存储的字符集

校对集: collate 具体校对集; ## utf8mb4_general_ci

存储引擎: engine 具体的存储引擎(InnoDB和MyISAM)

列属性：NULL/NOT NULL, DEFAULT, PRIMARY KEY, UNIQUE KEY, AUTO_INCREMENT, COMMENT等

二、查询数据库中的所有表

##查询所有数据表

show tables;

##搜索带有关键词的数据表

show tables like '%关键词%';

三、查看创建表时的语句

show create table 表名 \G

四、查看表结构

desc/describe/show columns from 表名;

五、修改数据表

数据表可修改的部分：表结构 和 字段

表结构

表名：

rename table 老表名 to 新表名;

表选项：字符集、校对集和存储引擎

alter table 表名 表选项 [=] 值;

表字段

新增字段：

alter table 表名 add [column] 字段名 数据类型 [列属性] [位置];

重命名字段

alter table 表名 change 旧字段 新字段名 数据类型 [属性] [位置]

删除字段：危险操作！！！

alter table 表名 drop 字段名; 

六、删除数据表:危险操作！！！

drop table [if exists] 表名1,表名2...; ##可以一次性删除多张表

数据操作

新增数据

INSERT INTO 表名 VALUES (值列表)[,(值列表)]; ## 可以一次性插入多条记录

INSERT INTO 表名 (字段列表) VALUES (值列表)[,(值列表)];

查看数据

SELECT */字段列表 FROM 表名 [WHERE 条件];

更新数据

UPDATE 表名 SET 字段 = 值 [WHERE条件]; #建议都有where: 要不然是更新全部

删除数据

DELETE FROM 表名 [WHERE条件]; #建议都有where: 要不然是删除全部

清空表中所有数据

要删除表中的所有数据，可使用不带WHERE子句的DELETE语句。 但是对于具有几百万行的大表，DELETE语句很慢且效率不高。

要快速删除大表中的所有行，使用以下TRUNCATE TABLE语句：

TRUNCATE TABLE 表名;

数据类型(列类型)

SQL中将数据类型分成了三大类: 数值类型, 字符串类型和时间日期类型

数值型

数值型数据: 都是数值

系统将数值型分为整数型和小数型.

整数型

存放整型数据: 在SQL中因为更多要考虑如何节省磁盘空间, 所以系统将整型又细分成了5类:

tinyint: 迷你整型,使用一个字节存储, 表示的状态最多为256种(常用)

smallint: 小整型,使用2个字节存储,表示的状态最多为65536种

mediumint: 中整型, 使用3个字节存储

int: 标准整型, 使用4个字节存储(常用)

bigint: 大整型,使用8个字节存储

小数型

带有小数点或者范围超出整型的数值类型.

SQL中: 将小数型细分成两种: 浮点型和定点型

浮点型: 小数点浮动, 精度有限,而且会丢失精度

定点型: 小数点固定, 精度固定, 不会丢失精度

浮点型

浮点型数据是一种精度型数据: 因为超出指定范围之后, 会丢失精度(自动四舍五入)

浮点型: 理论分为两种精度

Float: 单精度, 占用4个字节存储数据, 精度范围大概为7位左右

Double: 双精度,占用8个字节存储数据, 精度方位大概为15位左右

定点型

定点型: 绝对的保证整数部分不会被四舍五入(不会丢失精度),小数部分有可能(理论小数部分也不会丢失精度)

时间日期类型

Datetime: 时间日期, 格式是YYYY-mm-dd HH:ii:ss,表示的范围是从1000到9999年,有0值: 0000-00-00 00:00:00

Date: 日期,就是datetime中的date部分

Time: 时间(段), 指定的某个区间之间, -时间到+时间

Timestamp: 时间戳, 并不是时间戳,只是从1970年开始的YYYY-mm-dd HH:ii:ss格式与datetime完全一致

Year: 年份,两种形式, year(2)和year(4): 1901-2156

字符串类型

在SQL中,将字符串类型分成了6类: char、varchar、text、blob、enum和set

定长字符串(char)

定长字符串: char, 磁盘(二维表)在定义结构的时候,就已经确定了最终数据的存储长度.

char(L): L代表length, 可以存储的长度, 单位为字符, 最大长度值可以为255.

char(4): 在UTF8 环境下,需要4 * 3 = 12个字节

变长字符串(varchar)

变长字符串: varchar, 在分配空间的时候, 按照最大的空间分配: 但是实际上最终用了多少,是根据具体的数据来确定.

varchar(L): L表示字符长度 理论长度是65536个字符, 但是会多处1到2个字节来确定存储的实际长度: 但是实际上如果长度超过255,既不用定长也不用变长, 使用文本字符串text

varchar(10): 的确存了10个汉字, utf8环境, 10 * 3 + 1 = 31(bytes)

存储了3个汉字: 3 * 3 + 1 = 10(bytes)

定长与变长的存储实际空间(UTF8)

文本字符串

如果数据量非常大, 通常说超过255个字符就会使用文本字符串

文本字符串根据存储的数据的格式进行分类: text和blob

Text: 存储文字(二进制数据实际上都是存储路径)

Blob: 存储二进制数据(通常不用)

枚举字符串

枚举: enum, 事先将所有可能出现的结果都设计好, 实际上存储的数据必须是规定好的数据中的一个.

枚举的使用方式

定义: enum(可能出现的元素列表); //如enum(‘男’,’女’,’不男不女’,’妖’,’保密’);

使用: 存储数据,只能存储上面定义好的数据

集合字符串

集合跟枚举很类似: 实际存储的是数值,而不是字符串(集合是多选)

集合使用方式:

定义: Set(元素列表)

使用: 可以使用元素列表中的元素(多个), 使用逗号分隔

SQL高级教程

WHERE子句

如需有条件地从表中选取数据，可将 WHERE 子句添加到 SELECT 语句中。WHERE 子句也可以运用于 SQL 的 DELETE 或者 UPDATE 命令。

下表中的A为10，B为20

如果想在 MySQL 数据表中读取指定的数据，WHERE 子句是非常有用的。使用主键来作为 WHERE 子句的条件查询是非常快速的。如果给定的条件在表中没有任何匹配的记录，那么查询不会返回任何数据。

当需要多条件才能确定数据时，可使用AND或者OR运算符来确定数据。

 

在WHERE子句中字符串比较查询是不区分大小写的。若想进行大小写区分可使用BINARY关键字

LIKE操作符

SQL LIKE 子句中使用百分号(%)字符来表示任意字符，类似于UNIX或正则表达式中的星号 (*)。如果没有使用百分号(%), LIKE 子句与等号（=）的效果是一样的。

LIKE子句需要编写在WHERE子句中。

表达式 LIKE pattern

通配符(pattern)

在 SQL 中，通配符与 LIKE 操作符一起使用。SQL 通配符用于搜索表中的数据。在 SQL 中，可使用以下通配符：

 

ORDER BY 关键字

ORDER BY 关键字用于对结果集按照一个列或者多个列进行排序。

ORDER BY 关键字默认按照升序（ASC）对记录进行排序。如果需要按照降序对记录进行排序，可以使用 DESC 关键字。

可支持多字段同时排序，但 DESC或者 ASC 只对它紧跟着的第一个列名有效，其他不受影响，仍然是默认的升序。

IN 操作符

IN 操作符允许在 WHERE 子句中规定多个值。IN运算符是一个逻辑运算符，用于将值与一组值进行比较。 如果值在值集内，则IN运算符返回true。否则，它返回false或unknown。

expression IN (value1,value2,...)

子查询

子查询（subquery）是嵌套在另一个查询中的查询，例如：SELECT，INSERT，UPDATE或DELETE语句。放在括号内的查询称为子查询，它也称为内部查询或内部选择。 包含子查询的查询称为外部查询或外部选择。

可以在许多地方使用子查询，例如：

① 使用IN或NOT IN运算符

表达式 IN/NOT IN (subquery)

② 比较运算符中

表达式 =/>/=/<=/<>/!= (subquery)

③ 使用EXISTS或NOT EXISTS运算符

表达式 EXISTS/ NOT EXISTS (subquery)

④ 使用ANY或ALL运算符

表达式 ANY/ALL(subquery)

⑤ 在FROM子句中 。

问题：找到坪地商圈（商圈ID=1）的所有店铺信息

商圈表：

店铺与商圈关系表：

BETWEEN 操作符

BETWEEN 操作符选取介于两个值之间的数据范围内的值。这些值可以是数值、文本或者日期。BETWEEN运算符是逻辑运算符。 它返回true，false或unknown值。 BETWEEN运算符用于SELECT，UPDATE或DELETE语句中以查找范围内的值。

以下说明了BETWEEN运算符的语法：

expression BETWEEN low AND high;

在上面语法中，

·表达式是在低和高定义的范围内测试的表达式。

·low和high可以是表达式或文字值，要求low的值小于high的值。

如果表达式大于或等于(>=)low值且小于或等于(<=)high值，则BETWEEN运算符返回true。

连接(JOIN)

SQL JOIN 子句用于把来自两个或多个表的行结合起来，基于这些表之间的共同字段。

别名

SQL别名用于在执行查询期间为表或列分配临时名称。 有两种类型的别名：表别名和列别名。

连接类型

INNER JOIN(内连)：如果表中有至少一个匹配，则返回行

LEFT JOIN（左连）：即使右表中没有匹配，也从左表返回所有的行

RIGHT JOIN：即使左表中没有匹配，也从右表返回所有的行

 

UNION 操作符

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。

请注意，UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个SELECT 语句中的列的顺序必须相同。数据库系统首先执行两个SELECT语句来处理查询。然后，它将两个单独的结果集合并为一个，并消除重复的行。 为了消除重复的行，数据库系统对每列的组合结果进行排序，并扫描它以查找彼此相邻的匹配行。

要保留结果集中的重复行，请使用UNION ALL运算符

假设我们有两个结果集A(1,2)和B(2,3)。 下图说明了A UNION B结果：

以下图片说明了A UNION ALL B的结果：

GROUP BY 语句

GROUP BY 语句用于结合聚合函数，根据一个或多个列对结果集进行分组。分组是使用数据库时必须处理的最重要任务之一。 要将行分组，请使用GROUP BY子句。GROUP BY子句是SELECT语句的可选子句，它根据指定列中的匹配值将行组合成组，每组返回一行。

经常将GROUP BY与MIN，MAX，AVG，SUM或COUNT等聚合函数结合使用，以计算为每个分组提供信息的度量。

SELECT

    column1,

    column2,

    聚合函数(column3)

FROM

    table1

GROUP BY

    column1,

    column2;

LIMIT 子句

要检索查询返回的行的一部分，请使用LIMIT和OFFSET子句。常用于获取分页数据。

在使用LIMIT子句时，使用ORDER BY子句确保返回的行按指定顺序非常重要。LIMIT子句在ORDER BY子句的后面。并非所有数据库系统都支持LIMIT子句，因此，LIMIT子句仅在某些数据库系统中可用，例如MySQL，PostgreSQL，SQLite，Sybase SQL Anywhere和HSQLDB。

SELECT 

    column_list

FROM

    table1

ORDER BY column_list

LIMIT 限制数量 OFFSET 偏移量;

NULL 值

NULL在SQL中很特殊。 NULL表示数据未知的值，可以简单理解为表示：不适用 或不存在的值。 换句话说，NULL表示数据库中缺少数据。NULL值是特殊的，因为任何与NULL值的比较都不会导致true或false。不能使用比较运算符的等于(=)将值与NULL值进行比较。

IS NULL 和 IS NOT NULL 运算符

要确定表达式或列的值是否为NULL，请使用IS NULL运算符，如下所示：

表达式 IS NULL;

如果表达式的结果为NULL，则IS NULL运算符返回true; 否则它返回false。

要检查表达式或列是否不为NULL，请使用IS NOT运算符：

表达式 IS NOT NULL;

如果表达式的值为NULL，则IS NOT NULL返回false; 否则它返回true

NOT 运算符

要反转任何布尔表达式的结果，请使用NOT运算符。

NOT [布尔表达式]

可与LIKE，BETWEEN，IN和EXISTS等运算符进行反向操作。

表达式 NOT LIKE pattern

表达式 NOT BETWEEN low AND high;

表达式 NOT IN (value1,value2,...)

表达式 NOT EXISTS (subquery)

DISTINCT运算符

当您使用SELECT语句查询表中的一部分列时，可能会得到重复数据项。要从结果集中删除重复数据项，请在SELECT子句中插入DISTINCT运算符。

SELECT DISTINCT

    column1, column2, ...

FROM

    table1;

如果在DISTINCT运算符后使用一列，则数据库系统使用该列来计算重复。如果使用两列或更多列，数据库系统将使用这些列的组合进行重复检查。

SQL 函数

SQL聚合函数

MySQL提供了许多聚合函数，包括AVG，COUNT，SUM，MIN，MAX等。除COUNT函数外，其它聚合函数在执行计算时会忽略NULL值。

AVG()函数

AVG()函数计算一组值的平均值。它计算过程中是忽略NULL值的。

COUNT() 函数

COUNT() 函数返回匹配指定条件的行数。

SUM() 函数

SUM()函数返回一组值的总和，SUM()函数忽略NULL值。如果找不到匹配行，则SUM()函数返回NULL值。

MAX() 函数

MAX()函数返回一组值中的最大值。

MIN() 函数

MIN()函数返回一组值中的最小值，

HAVING 子句

在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与聚合函数一起使用。

HAVING 子句可以让我们筛选分组后的各组数据。

语法：

SELECT column_name, 聚合函数(column_name)

FROM table_name

WHERE column_name operator value

GROUP BY column_name

HAVING 聚合函数(column_name) operator value

问题一：找到所有会员付款金额大于10的总金额记录

时间函数

CURDATE() 和 CURRENT_DATE()：返回当前日期。

SELECT CURDATE(); #2019-09-23

SELECT CURRENT_DATE(); #2019-09-23

CURRENT_TIME() 和 CURTIME()：返回当前时间。

SELECT CURRENT_TIME(); #14:21:12

SELECT CURRENT_TIME(); #14:21:12

CURRENT_TIMESTAMP() 和 NOW()：返回当前日期和时间。

SELECT CURRENT_TIMESTAMP(); #2019-09-23 14:21:12

SELECT NOW(); #2019-09-23 14:21:12

DATE_FORMAT(d,f)：按表达式 f的要求显示日期 d。

SELECT DATE_FORMAT('2019-09-23 14:21:12','%Y-%m-%d'); # 2019-09-23 02:21:12 PM

LENGTH，CHAR_LENGTH和CHARACTER_LENGTH函数

LENGTH()函数：获取以字节为单位的字符串长度

LENGTH(str);

CHAR_LENGTH()和CHARACTER_LENGTH()函数：获取字符串的长度，以字符为单位计算长度

CHAR_LENGTH(str);

CHARACTER_LENGTH(str);

CONCAT函数

CONCAT()函数：字符串 s1,s2 等多个字符串合并为一个字符串。合并多个字符串。

CONCAT(s1,s2...sn)

IFNULL(v1,v2) 函数

IFNULL(v1,v2)：函数用于判断第一个表达式是否为 NULL，如果为 NULL 则返回第二个参数的值，如果不为 NULL 则返回第一个参数的值。

IFNULL(expression, alt_value)