MySQL学习笔记
时间：2015-04-09 23:48:23
收藏：0
阅读：389
1、关系型数据库管理系统
RDBMS（Relational Database Management System）
1.1、关系型数据库的三大范式
范式简单的说就是，一张数据表的表结构所符合的某种设计标准的级别。
第一范式（1NF）：符合1NF的关系中的每个属性都不可再分。
第二范式（2NF）：2NF在1NF的基础之上，消除了非主属性对于码的部分函数依赖。
第三范式（3NF）：3NF在2NF的基础之上，消除了非主属性对于码的传递函数依赖。
简单理解：
第一范式：属性不可分割。（属性就是表中的字段）
第二范式：要有主键，要求其他字段都依赖于主键。（附：主键的判断办法，如果主键确定了，其他所有的键就确定了）
第三范式：要消除传递依赖，方便理解，可以看做是“消除冗余”。
1.2、术语
数据库：表的集合
数据表：数据矩阵
行：
列：
主键：主键是唯一的。一张表中只能包含一个主键。主要用来查询。
外键：用于关联两个表。
索引：对表中一列或多列数据进行排序的结构。
2、安装MySQL
Linux/UNIX
平台
MySQL
- MySQL服务器。你需要该选项，除非你只想连接运行在另一台机器上的MySQL服务器。
MySQL-client
- MySQL 客户端程序，用于连接并操作Mysql服务器。
MySQL-devel
- 库和包含文件，如果你想要编译其它MySQL客户端，例如Perl模块，则需要安装该RPM包。
MySQL-shared
- 该软件包包含某些语言和应用程序需要动态装载的共享库(libmysqlclient.so*)，使用MySQL。
MySQL-bench
- MySQL数据库服务器的基准和性能测试工具。
Windows
平台
下载安装包
3、数据类型
|
类型
|
大小
|
范围（有符号）
|
范围（无符号）
|
用途
|
备注 |
|
TINYINT
|
1 字节
|
(-128, 127)
|
(0, 255)
|
小整数值
|
|
|
SMALLINT
|
2 字节
|
(-32768, 32767)
|
(0, 65535)
|
大整数值
|
|
|
MEDIUMINT
|
3 字节
|
(-8388608, 8388607)
|
(0, 16777215)
|
大整数值
|
|
|
INT 或
INTEGER
|
4 字节
|
(-2147483648, 2147483647)
|
(0, 4294967295)
|
大整数值
|
|
|
BIGINT
|
8 字节
|
(-9233372036854775808
, 9223372036854775807)
|
(0, 18446744073709551615)
|
极大整数值
|
|
|
FLOAT
|
4 字节
|
(-3.402823466E+38, 1.175494351E-38)
, 0
, (1.175494351E-38, 3.402823466351E+38)
|
0
, (1.175494351E-38, 3.402823466E+38)
|
单精度
浮点数值
|
|
|
DOUBLE
|
8 字节
|
(1.7976931348623157E+308, 2.2250738585072014E-308)
, 0
, (2.2250738585072014E-308, 1.7976931348623157E+308)
|
0
, (2.2250738585072014E-308, 1.7976931348623157E+308)
|
双精度
浮点数值
|
|
|
DECIMAL
|
对 DECIMAL(M, D)
如果 M > D，
为 M+2，
否则为 D+2
|
依赖于 M 和 D 的值
|
依赖于 M 和 D 的值
|
小数值
|
|
|
DATE
|
3 字节
|
1000-01-01/9999-12-31
|
|
日期值
|
YYYY-MM-DD
|
|
TIME
|
3 字节
|
‘-838:59:59‘/‘838:59:59‘
|
|
时间值或持续时间
|
HH:MM:SS
|
|
YEAR
|
1 字节
|
1901/2155
|
|
年份值
|
YYYY
|
|
DATETIME
|
8 字节
|
1000-01-01 00:00:00
/9999-12-31 23:59:59
|
|
混合日期和时间值
|
YYYY-MM-DD
HH:MM:SS
|
|
TIMESTAMP
|
8 字节
|
1970-01-01 00:00:00/2037 年某时
|
|
混合日期和时间值，时间戳
|
YYYYMMDD
HHMMSS
|
|
CHAR
|
0-255 字节
|
|
|
定长字符串 |
|
|
VARCHAR
|
0-255 字节
|
|
|
变长字符串
|
|
|
TINYBLOB
|
0-255 字节
|
|
|
不超过 255 个字符的二进制字符串
|
|
|
TINYTEXT
|
0-255 字节
|
|
|
短文本字符串
|
|
|
BLOB
|
0-65535 字节
|
|
|
二进制形式的长文本数据
|
|
|
TEXT
|
0-65535 字节
|
|
|
长文本数据
|
|
|
MEDIUMBLOB
|
0-16777215 字节 |
|
|
二进制形式的中等长度文本数据
|
|
|
MEDUIMTEXT
|
0-16777215 字节
|
|
|
中等长度文本数据
|
|
|
LONGBLOB
|
0-4294967295 字节
|
|
|
二进制形式的极大文本数据
|
|
|
LONGTEXT
|
0-4294967295 字节
|
|
|
极大文本数据
|
|
4、常用命令
|
分类
|
说明
|
命令
|
备注
|
|
连接
|
登录服务器 |
mysql -h1.2.3.4 -u root -p
|
|
|
显示
|
库列表
|
SHOW DATABASES
|
|
|
|
表列表
|
SHOW TABLES
|
|
|
|
表的结构
|
SHOW CREATE TABLE table_name
DESCRIBE table_name
|
|
|
|
表的属性信息
|
SHOW COLUMNS FROM table_name
|
|
|
|
表的索引信息
|
SHOW INDEX FROM table_name
|
|
|
|
表的状态信息 |
SHOW STATUS LIKE ‘table_name‘
|
|
|
|
服务器 的状态信息
|
SHOW STATUS
|
|
|
|
服务器配置信息
|
SHOW VARIABLES
|
|
|
|
服务器的版本信息
|
SELECT VERSION()
|
|
|
|
当前库名
|
SELECT DATABASE()
|
|
|
|
当前用户名
|
SELECT USER()
|
|
|
选择
|
库
|
USE database_name
|
|
|
新建
|
库
|
CREATE DATABASE database_name
|
|
|
|
表
|
CREATE TABLE table_name (column_name column_type)
|
|
|
|
临时表
|
CREATE TEMPORARY TABLE table_name (column_name column_type)
|
临时表只在当前连接有效，连接断开后，临时表自动销毁。
|
|
插入
|
行 |
INSERT INTO table_name (field1, field2, ...fieldN) VALUES (value1, value2, ...valueN) |
|
| 删除 |
库
|
DROP DATABASE database_name
|
|
|
|
行
|
DELETE FROM table_name [WHERE clause_etc]
|
|
|
|
表 |
DROP TABLE table_name
|
|
| 查询 | 行 |
SELECT field1, field2, ...fieldN FROM table_name1, table_name2, ...
[WHERE clause_etc]
[OFFSET M]
[LIMIT N]
|
|
|
查询（ 联表）
|
行（内联结）
|
隐式：
SELECT table1.column table2.column FROM table1, table2
WHERE table1.column2 = table2.column2
显式：
SELECT table1.column FROM table1 INNER JOIN table2 ON table1.column2 = table2.column2
|
仅支持 SELECT UPDATE DELETE
|
|
|
行（外左联结） |
SELECT table1.column FROM table1 LEFT
JOIN table2 ON table1.column2 = table2.column2
|
table1 中的记录全部保留，如果 table2 中无对应的，则默认留空 |
|
|
行（外右联结）
|
SELECT table1.column FROM table1 RIGHT JOIN table2
ON table1.column2 = table2.column2
|
table2 中的记录全部保留，如果 table1 中无对应的，则默认留空
|
|
更新
|
行
|
UPDATE table_name SET field1=value1, field2=value2
[WHERE clause_etc]
|
|
|
修改
|
添加列
|
ALTER TABLE table_name ADD column_name type
ALTER TABLE table_name ADD column_name
type FIRST
ALTER TABLE table_name ADD column_name
type AFTER column2
|
添加到最后一列 （默认）
添加到第一列
添加到指定列 column2 之后
|
|
|
删除列
|
ALTER TABLE table_name DROP column_name
|
|
|
|
修改列（类型）
|
ALTER TABLE table_name MODIFY column_name
type
|
只修改列字段的类型
|
|
|
修改列（名称）
|
ALTER TABLE table_name CHANGE old_column column_name
type
|
同时修改列字段的名称和类型
|
|
|
表
|
ALTER TABLE table_name RENAME TO table_name2
|
修改表名
|
|
限定条件
|
偏移量
|
OFFSET offset_number
|
|
|
|
行数
|
LIMIT limit_number
|
|
|
|
任意条件 |
WHERE clause_etc
|
|
|
|
正则表达式
|
WHERE column REGEXP ‘exp‘
|
|
|
排序
|
升序
|
ASC
|
|
|
|
降序
|
DESC |
|
|
索引
|
列
|
CREATE [UNIQUE] INDEX index_name ON table_name(column(length))
ALTER table_name ADD [UNIQUE] INDEX [index_name] ON ( column(length))
DROP INDEX [index_name] ON table_name
|
column 如果是 CHAR, VCHAR 类型，length 可以小于字段长度；
如果是 BLOB, TEXT 类型，必须指定 length。
|
|
|
列（组合）
|
|
|
|
|
|
|
|
下表中，假设 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
|
|
IS NULL
|
检测值是否为NULL |
|
|
IS NOT NULL
|
检测值是否不为NULL
|
|
|
操作符
|
描述
|
备注 |
|
AND
|
连接两个条件，表示左边与右边的条件必须同时成立，结果才成立
|
|
|
OR
|
连接两个条件，表示左边与右边的条件任意一个成立，结果就成立
|
|
|
LIKE
|
条件匹配，通常与 % 一起使用达到模糊查询的效果
|
WHERE feild LIKE ‘%abc‘
|
7、正则表达式
|
模式
|
描述
|
备注
|
|
^
|
匹配输入字符的 开始位置。如果设置了 RegExp 对象的 Multiline 属性，
^ 也匹配 ‘\n‘ 或 ‘\r‘ 之后的位置。
|
|
| $ |
匹配输入字符的结束位置。如果设置了 RegExp 对象的 Multiline 属性，
^ 也匹配 ‘\n‘ 或 ‘\r‘ 之前的位置。
|
|
|
[...]
|
字符集合。匹配所包含的任意一个字符。
|
例如，‘[abc]‘ 可以匹配 ‘plain‘ 中的 ‘a‘。
|
|
.
|
匹配除 ‘\n‘ 之外的任何单个字符。
|
要匹配包括 ‘\n‘ 在内的任何字符，请使用象 ‘[.\n]‘ 的模式。 |
|
[^...]
|
负值字符集合。匹配未包含的任意字符。
|
例如，‘[^abc]‘ 可以匹配 ‘plain‘ 中的 ‘p‘ ‘l‘ ‘i‘ ‘n‘
|
|
{n}
|
n 是一个非负整数。
匹配前面表达式确定的 n 次。
|
例如，‘o{2}‘ 能匹配 ‘food‘
|
|
{n, m}
|
m n 均为非负整数，其中 n <= m。
匹配前面表达式最少 n 次，最多 m 次。
|
|
|
p1|p2
|
匹配 p1 或 p2。 |
例如，‘z|food‘ 能匹配 ‘z‘ 或 ‘food‘
|
|
*
|
匹配前面的子表达式零次或多次 。
等价于 {0,}
|
例如，‘zo*‘ 能匹配 ‘z‘ ‘ozz‘。
|
|
+
|
匹配前面的子表达式一次或多次。 等价于 {1,} |
|
8、事务
用来保证一组
sql 命令，全部执行，如果失败回滚到执行前的状态。
事务必须满足4个条件（ACID）：Atomicity（原子性）、Consistency（稳定性）、Isolation（隔离性）、durability（可靠性）
在
MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
使用关键词
AUTO_INCREMENT 来定义一个整数列自增。
防止
SQL 注入，需要注意以下几点：
- 永远不要信任用户的输入。对用户的输入进行校验，可以通过正则，或限制长度；对单引号和双"-"进行转换等。
- 永远不要使用动态拼装 sql，可以使用参数化的 sql 或者直接使用存储过程进行数据查询存取。
- 永远不要使用管理员权限的数据库连接，为每个应用使用单独的权限有限的数据库连接。
- 不要把机密信息直接存放，加密或者 hash 掉密码和第三的信息。
- 应用的异常信息应该给出尽可以少的提示，最好使用自定义的错误信息对原始错误信息进行包装。
- sql 注入的检测方法一般采取辅助软件或网站平台来检测，软件一般采用 sql 注入检测工具。
原文：http://blog.csdn.net/shishuo365/article/details/44967091
评论（0）