MySql 学习笔记

最新推荐文章于 2024-03-26 13:11:36 发布

qq_25966587

最新推荐文章于 2024-03-26 13:11:36 发布

阅读量219

点赞数

原文链接：https://www.runoob.com/mysql/mysql-tutorial.html

版权

sql语句专栏收录该内容

3 篇文章 0 订阅

订阅专栏

MySql 学习笔记

RDBMS（关系数据库管理系统）术语
MySql 数据类型
基础语法
事务
索引
临时表
复制表
序列
SQL 注入
导出数据
运算符
函数

RDBMS（关系数据库管理系统）术语

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

MySql 数据类型

数值类型

包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC)，以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)。

关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。

BIT数据类型保存位字段值，并且支持MyISAM、MEMORY、InnoDB和BDB表。

作为SQL标准的扩展，MySQL也支持整数类型TINYINT、MEDIUMINT和BIGINT。下面的表显示了需要的每个整数类型的存储和范围。

数值类型范围

时间和日期类型

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。

每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。
时间和日期类型

字符串类型

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。

注意：

char(n) 和 varchar(n) 中括号中 n 代表字符的个数，并不代表字节个数，比如 CHAR(30) 就可以存储 30 个字符。
CHAR 和 VARCHAR 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。
BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。
BLOB 是一个二进制大对象，可以容纳可变数量的数据。有 4 种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。
有 4 种 TEXT 类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型，可存储的最大长度不同，可根据实际情况选择。

基础语法

创建数据库表

CREATE TABLE table_name (column_name column_type);

以下例子中我们将在 RUNOOB 数据库中创建数据表runoob_tbl：

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 设置编码。

删除数据库表

DROP TABLE table_name ;

ALTER命令

添加表字段

ALTER TABLE table_name ADD i INT;

执行以上命令后，i 字段会自动添加到数据表字段的末尾。
如果你需要指定新增字段的位置，可以使用MySQL提供的关键字 FIRST (设定位第一列)， AFTER 字段名（设定位于某个字段之后）。

删除表字段

ALTER TABLE table_name DROP i;

修改字段类型及名称

如果需要修改字段类型及名称, 你可以在ALTER命令中使用 MODIFY 或 CHANGE 子句。

例如，把字段 c 的类型从 CHAR(1) 改为 CHAR(10)，可以执行以下命令:
ALTER TABLE testalter_tbl MODIFY c CHAR(10);

使用 CHANGE 子句, 语法有很大的不同。在 CHANGE 关键字之后，紧跟着的是你要修改的字段名，然后指定新字段名及类型。尝试如下实例：

ALTER TABLE table_name CHANGE i j BIGINT;
ALTER TABLE table_name CHANGE j j INT;

设置字段默认值

指定字段 j 为 NOT NULL 且默认值为100 。
ALTER TABLE table_name
MODIFY j BIGINT NOT NULL DEFAULT 100;

如果你不设置默认值，MySQL会自动设置该字段默认为 NULL

修改默认值

ALTER TABLE table_name ALTER i SET DEFAULT 1000;

删除默认值

ALTER TABLE table_name ALTER i DROP DEFAULT

修改表名

ALTER TABLE old_table_name RENAME TO new_table_name ;

插入数据

插入一条数据

INSERT INTO table_name ( field1, field2,…fieldN ) VALUES ( value1, value2,…valueN );

插入多条数据

INSERT INTO table_name (field1, field2,…fieldN) VALUES (valueA1,valueA2,…valueAN),(valueB1,valueB2,…valueBN),(valueC1,valueC2,…valueCN)…;

插入结果集

INSERT INTO table_name (field1, field2,…fieldN)
SELECT field1, field2,…fieldN FROM table_name

如果数据是字符型，必须使用单引号或者双引号，如：“value”。

查询

SELECT column_name,column_name FROM table_name [WHERE Clause] [LIMIT N] [ OFFSET M]

查询语句中你可以使用一个或者多个表，表之间使用逗号(,)分割，并使用WHERE语句来设定查询条件。
SELECT 命令可以读取一条或者多条记录。
使用星号（*）来代替其他字段，SELECT语句会返回表的所有字段数据
使用 WHERE 语句来包含任何条件。
使用 LIMIT 属性来设定返回的记录数。
通过OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0
左连接 LEFT JOIN：获取左表所有记录，即使右表没有对应匹配的记录。
右连接 RIGHT JOIN：与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。
内连接（内连接,或等值连接） INNER JOIN：获取两个表中字段匹配关系的记录。

更新

UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]

删除

DELETE FROM table_name [WHERE Clause]

delete，drop，truncate 都有删除表的作用，区别在于：

delete 和 truncate 仅仅删除表数据，drop 连表数据和表结构一起删除，打个比方，delete 是单杀，truncate 是团灭，drop 是把电脑摔了。
delete 是 DML 语句，操作完以后如果没有不想提交事务还可以回滚，truncate 和 drop 是 DDL 语句，操作完马上生效，不能回滚，打个比方，delete 是发微信说分手，后悔还可以撤回，truncate 和 drop 是直接扇耳光说滚，不能反悔。
执行的速度上，drop>truncate>delete，打个比方，drop 是神舟火箭，truncate 是和谐号动车，delete 是自行车。

UNION 操作符

描述

MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据

语法

SELECT expression1, expression2, … expression_n
FROM tables
[WHERE conditions]
UNION [ALL | DISTINCT]
SELECT expression1, expression2, … expression_n
FROM tables
[WHERE conditions];

expression1, expression2, … expression_n: 要检索的列。
tables: 要检索的数据表。
WHERE conditions: 可选，检索条件。
DISTINCT: 可选，删除结果集中重复的数据。默认情况下 UNION 操作符已经删除了重复数据，所以 DISTINCT 修饰符对结果没啥影响。
ALL: 可选，返回所有结果集，包含重复数据。

排序

SELECT field1, field2,…fieldN
FROM table_name1, table_name2…
ORDER BY field1 [ASC [DESC][默认 ASC]], [field2…] [ASC [DESC][默认 ASC]]

分组

SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。其中 function可以替换为COUNT。

NULL值处理

IS NULL: 当列的值是 NULL,此运算符返回 true。
IS NOT NULL: 当列的值不为 NULL, 运算符返回 true。
<=>: 比较操作符（不同于 = 运算符），当比较的的两个值相等或者都为 NULL 时返回 true。
关于 NULL 的条件比较运算是比较特殊的。你不能使用 = NULL 或 != NULL 在列中查找 NULL 值。
在 MySQL 中，NULL 值与任何其它值的比较（即使是 NULL）永远返回 NULL，即 NULL = NULL 返回 NULL 。
MySQL 中处理 NULL 使用 IS NULL 和 IS NOT NULL 运算符。
IFNULL(字段,0)：如果该字段值为NULL，则会返回0，若该字段值不为NULL，则会返回该字段值
ISNULL(字段):判断该字段值是否为NULL,若为NULL返回1，若不为NULL返回0

REGEXP 正则表达式

下表中的正则模式可应用于 REGEXP 操作符中。
在这里插入图片描述

实例

查找name字段中以’st’为开头的所有数据：

SELECT name FROM person_tbl WHERE name REGEXP ‘^st’;

查找name字段中以’ok’为结尾的所有数据：

SELECT name FROM person_tbl WHERE name REGEXP ‘ok$’;

查找name字段中包含’mar’字符串的所有数据：

SELECT name FROM person_tbl WHERE name REGEXP ‘mar’;

查找name字段中以元音字符开头或以’ok’字符串结尾的所有数据：

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

事务

在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。

事务控制语句：

BEGIN 或 START TRANSACTION 显式地开启一个事务；
COMMIT 也可以使用 COMMIT WORK，不过二者是等价的。COMMIT 会提交事务，并使已对数据库进行的所有修改成为永久性的；
ROLLBACK 也可以使用 ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；
SAVEPOINT identifier，SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT；
RELEASE SAVEPOINT identifier 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；
ROLLBACK TO identifier 把事务回滚到标记点；
SET TRANSACTION 用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。

MYSQL 事务处理主要有两种方法：

用 BEGIN, ROLLBACK, COMMIT来实现
BEGIN 开始一个事务
ROLLBACK 事务回滚
COMMIT 事务确认
直接用 SET 来改变 MySQL 的自动提交模式:
SET AUTOCOMMIT=0 禁止自动提交
SET AUTOCOMMIT=1 开启自动提交

语法

# 语法
start transaction;
sql 语句 ...
[rollback]	# 回滚
sql 语句 ...
sql 语句 ...
commit

实例

无保存点的事务

START TRANSACTION;
INSERT INTO academy
 ( id, `name`, `code`, gmt_create, gmt_modified ) 
 SELECT
MAX( a.id ) + 1 AS id,
'事务' AS `name`,
'' AS `code`,
SYSDATE( ) AS gmt_create,
SYSDATE( ) AS gmt_modified 
FROM
	academy a;
COMMIT;  -- ROLLBACK;

带保存点的事务

START TRANSACTION;
UPDATE academy a 
SET a.`name` = '事务保存点' 
WHERE
	a.`name` LIKE '%事务%';
SAVEPOINT a;
DELETE 
FROM
	academy 
WHERE
	`name` LIKE '%事务%';
SAVEPOINT b;
ROLLBACK TO a # 回滚到自动地点
COMMIT;  -- ROLLBACK;

索引

基础知识

索引分单列索引和组合索引。
- 单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。
- 组合索引，即一个索引包含多个列。
实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。
索引的缺点：
- 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。
- 建立索引会占用磁盘空间的索引文件。

显示索引信息

SHOW INDEX FROM table_name;

普通索引

创建普通索引

方法一：

CREATE INDEX indexName ON table_name(username(length));

如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。

方法二：修改表结构(添加索引)

ALTER table tableName ADD INDEX indexName(columnName)

方法三：创建表的时候直接指定

CREATE TABLE mytable(  
   ID INT NOT NULL,   
   username VARCHAR(16) NOT NULL,  
   INDEX [indexName] (username(length))  
);

删除索引的语法

DROP INDEX [indexName] ON mytable;

唯一索引

它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

创建唯一索引

方法一：

CREATE UNIQUE INDEX indexName ON mytable(username(length))

方法二：修改表结构

ALTER table mytable ADD UNIQUE [indexName] (username(length))

方法三：创建表的时候直接指定

CREATE TABLE mytable(  
   ID INT NOT NULL,   
   username VARCHAR(16) NOT NULL,  
   UNIQUE [indexName] (username(length))   
);

ALTER 命令

添加索引

普通索引

添加普通索引，索引值可出现多次

ALTER TABLE tbl_name ADD INDEX index_name (column_list)

唯一索引

索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）

ALTER TABLE tbl_name ADD UNIQUE index_name (column_list)

主键

主键，这意味着索引值必须是唯一的，且不能为NULL。

ALTER TABLE tbl_name ADD PRIMARY KEY (column_list)

FULLTEXT 用于全文索引

ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list)

删除

ALTER TABLE testalter_tbl DROP INDEX c;

使用 ALTER 命令添加和删除主键

主键只能作用于一个列上，添加主键索引时，你需要确保该主键默认不为空（NOT NULL）。实例如下：

ALTER TABLE testalter_tbl MODIFY i INT NOT NULL;
ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);

你也可以使用 ALTER 命令删除主键：

ALTER TABLE testalter_tbl DROP PRIMARY KEY;

删除主键时只需指定PRIMARY KEY，但在删除索引时，你必须知道索引名。

临时表

MySQL 临时表在我们需要保存一些临时数据时是非常有用的。临时表只在当前连接可见，当关闭连接时，Mysql会自动删除表并释放所有空间。
临时表在MySQL 3.23版本中添加，如果你的MySQL版本低于 3.23版本就无法使用MySQL的临时表。不过现在一般很少有再使用这么低版本的MySQL数据库服务了。
MySQL临时表只在当前连接可见，如果你使用PHP脚本来创建MySQL临时表，那每当PHP脚本执行完成后，该临时表也会自动销毁。
如果你使用了其他MySQL客户端程序连接MySQL数据库服务器来创建临时表，那么只有在关闭客户端程序时才会销毁临时表，当然你也可以手动销毁。

创建和删除临时表与创建和删除数据库表大体相同，具体语法如下：

删除： DROP TABLE table_name;
创建：

 CREATE TEMPORARY TABLE SalesSummary (
    product_name VARCHAR(50) NOT NULL,
    total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00, 
    total_units_sold INT UNSIGNED NOT NULL DEFAULT 0
    );

复制表

如果我们需要完全的复制MySQL的数据表，包括表的结构，索引，默认值等。如果仅仅使用CREATE TABLE … SELECT 命令，是无法实现的。

复制表结果，索引，默认值等：

本章节将为大家介绍如何完整的复制MySQL数据表，步骤如下：

使用 SHOW CREATE TABLE 命令获取创建数据表(CREATE TABLE) 语句，该语句包含了原数据表的结构，索引等。
复制以下命令显示的SQL语句，修改数据表名，并执行SQL语句，通过以上命令将完全的复制数据表结构。
如果你想复制表的内容，你就可以使用INSERT INTO … SELECT 语句来实现。

只复制表结构到新表：

方法一：

CREATE TABLE targetTable LIKE sourceTable;

方法二：

create table targetTable select * from sourceTable where 1=2

复制表结构及数据到新表

create table targetTable select * from sourceTable

序列

MySQL 序列是一组整数：1, 2, 3, …，由于一张数据表只能有一个字段自增主键，如果你想实现其他字段也实现自动增加，就可以使用MySQL序列来实现。

AUTO_INCREMENT 创建序列

CREATE TABLE  insect(
          id INT UNSIGNED NOT NULL AUTO_INCREMENT,
           PRIMARY KEY (id),
           name VARCHAR(30) NOT NULL, # type of insect
           date DATE NOT NULL, # date collected
           origin VARCHAR(30) NOT NULL # where collected
);

获取AUTO_INCREMENT值

在MySQL的客户端中你可以使用 SQL中的LAST_INSERT_ID( ) 函数来获取最后的插入表中的自增列的值。

重置序列

如果你删除了数据表中的多条记录，并希望对剩下数据的AUTO_INCREMENT列进行重新排列，那么你可以通过删除自增的列，然后重新添加来实现。不过该操作要非常小心，如果在删除的同时又有新记录添加，有可能会出现数据混乱。操作如下所示：

ALTER TABLE insect DROP id;
ALTER TABLE insect
        ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,
        ADD PRIMARY KEY (id);

设置序列的开始值

一般情况下序列的开始值为1，但如果你需要指定一个开始值100，那我们可以通过以下语句来实现：

CREATE TABLE insect
 (
         id INT UNSIGNED NOT NULL AUTO_INCREMENT,
         PRIMARY KEY (id),
         name VARCHAR(30) NOT NULL, 
         date DATE NOT NULL,
         origin VARCHAR(30) NOT NULL
)engine=innodb auto_increment=100 charset=utf8;

或者你也可以在表创建成功后，通过以下语句来实现：

ALTER TABLE t AUTO_INCREMENT = 100;

SQL 注入

防止SQL注入，我们需要注意以下几个要点：

永远不要信任用户的输入。对用户的输入进行校验，可以通过正则表达式，或限制长度；对单引号和双"-"进行转换等。
永远不要使用动态拼装sql，可以使用参数化的sql或者直接使用存储过程进行数据查询存取。
永远不要使用管理员权限的数据库连接，为每个应用使用单独的权限有限的数据库连接。
不要把机密信息直接存放，加密或者hash掉密码和敏感的信息。
应用的异常信息应该给出尽可能少的提示，最好使用自定义的错误信息对原始错误信息进行包装
sql注入的检测方法一般采取辅助软件或网站平台来检测，软件一般采用sql注入检测工具jsky，网站平台就有亿思网站安全平台检测工具。MDCSOFT SCAN等。采用MDCSOFT-IPS可以有效的防御SQL注入，XSS攻击等。