mysql详细教程大全
SQL简介:SQL(Structured Query Language):是一种结构化查询语言,用于访问和处理关系型数据库系统的计算机标准语言。关系型数据库系统(RDBMS:Relational Database Management System)表RDBMS中的数据存储在称为表的数据库对象中。这个表基本上是一个相关数据条目的集合,它由许多列和行组成。下图是adver_us...
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)
实际存储数据 | Char(4) | Varchar(4) | Char占用字节 | Varchar(占用字节) |
ABCD | ABCD | ABCD | 4 * 3 = 12 | 4 * 3 + 1 = 13 |
A | A | A | 4 * 3 = 12 | 1 * 3 + 1 = 4 |
ABCDE |
|
| 数据超过长度 | 数据超过长度 |
文本字符串
如果数据量非常大, 通常说超过255个字符就会使用文本字符串
文本字符串根据存储的数据的格式进行分类: text和blob
Text: 存储文字(二进制数据实际上都是存储路径)
Blob: 存储二进制数据(通常不用)
枚举字符串
枚举: enum, 事先将所有可能出现的结果都设计好, 实际上存储的数据必须是规定好的数据中的一个.
枚举的使用方式
定义: enum(可能出现的元素列表); //如enum(‘男’,’女’,’不男不女’,’妖’,’保密’);
使用: 存储数据,只能存储上面定义好的数据
集合字符串
集合跟枚举很类似: 实际存储的是数值,而不是字符串(集合是多选)
集合使用方式:
定义: Set(元素列表)
使用: 可以使用元素列表中的元素(多个), 使用逗号分隔
SQL高级教程
WHERE子句
如需有条件地从表中选取数据,可将 WHERE 子句添加到 SELECT 语句中。WHERE 子句也可以运用于 SQL 的 DELETE 或者 UPDATE 命令。
下表中的A为10,B为20
操作符 | 描述 | 实例 |
= | 等号,检测两个值是否相等,如果相等返回true | (A = B) 返回false。 |
<>, != | 不等于,检测两个值是否相等,如果不相等返回true | (A != B) 返回 true。 |
> | 大于号,检测左边的值是否大于右边的值, 如果左边的值大于右边的值返回true | (A > B) 返回false。 |
< | 小于号,检测左边的值是否小于右边的值, 如果左边的值小于右边的值返回true | (A < B) 返回 true。 |
>= | 大于等于号,检测左边的值是否大于或等于右边的值, 如果左边的值大于或等于右边的值返回true | (A >= B) 返回false。 |
<= | 小于等于号,检测左边的值是否小于于或等于右边的值, 如果左边的值小于或等于右边的值返回true | (A <= B) 返回 true。 |
如果想在 MySQL 数据表中读取指定的数据,WHERE 子句是非常有用的。使用主键来作为 WHERE 子句的条件查询是非常快速的。如果给定的条件在表中没有任何匹配的记录,那么查询不会返回任何数据。
当需要多条件才能确定数据时,可使用AND或者OR运算符来确定数据。
AND | TRUE | FALSE | NULL |
TURE | TRUE | FALSE | NULL |
FALSE | FALSE | FALSE | FLASE |
NULL | NULL | FALSE | NULL |
OR | TRUE | FALSE | NULL |
TURE | TRUE | TRUE | TRUE |
FALSE | TRUE | FALSE | NULL |
NULL | TRUE | NULL | NULL |
在WHERE子句中字符串比较查询是不区分大小写的。若想进行大小写区分可使用BINARY关键字
LIKE操作符
SQL LIKE 子句中使用百分号(%)字符来表示任意字符,类似于UNIX或正则表达式中的星号 (*)。如果没有使用百分号(%), LIKE 子句与等号(=)的效果是一样的。
LIKE子句需要编写在WHERE子句中。
表达式 LIKE pattern
通配符(pattern)
在 SQL 中,通配符与 LIKE 操作符一起使用。SQL 通配符用于搜索表中的数据。在 SQL 中,可使用以下通配符:
通配符 | 描述 |
% | 替代 0 个或多个字符 |
_(英文下划线) | 替代一个字符 |
[charlist] | 字符列中的任何单一字符 |
[^charlist] 或 [!charlist] | 不在字符列中的任何单一字符 |
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运算符后 |
TURE | FALSE |
FALSE | TRUE |
NULL | NULL |
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)
更多推荐
所有评论(0)