mysql入门精髓

jiezi

5 年前

使用 MySQL 数据库，首先安装 MySQL 数据库，本文所有 SQL 脚本在 MySQL 上测试和执行。

安装 Mysql 服务器；
安装 Mysql workbench 客户端，可以以图形化界面管理 mysql；
安装 phpMyadmin 客户端，可以通过 bs 方式图形化管理 Mysql；

windows 下为例，win+r 进入 cmd 界面

启动服务器：net start mysql
停止服务器：net stop mysql
连接服务器：mysql -h localhost -u root -p password

create database db_bookstore; // 创建数据库：
use database db_bookstore; // 使用数据库:
drop database db_bookstore; // 删除数据库:

（1）创建表

creat table table_name(id int not null auto_increment primary key, name char(50)
) if not exists;

（2）查看表结构

show columns from tb_name from db_name;
desc table_name;

（3）删除表：

drop table tb_name if exists;
（4）修改表: alter tb_name alter/modify/change [column] …
ALTER COLUMN：设置或删除列的默认值（操作速度非常快）
例子：

alter table film alter column rental_duration set default 5;
alter table film alter column rental_duration drop default;
CHANGE COLUMN：列的重命名、列类型的变更以及列位置的移动
例子：

ALTER TABLE MyTable CHANGE COLUMN foo bar VARCHAR(32) NOT NULL FIRST;
ALTER TABLE MyTable CHANGE COLUMN foo bar VARCHAR(32) NOT NULL AFTER baz;
MODIFY COLUMN：除了不能给列重命名之外，他干的活和 CHANGE COLUMN 是一样的
例子：

ALTER TABLE MyTable MODIFY COLUMN foo VARCHAR(32) NOT NULL AFTER baz;
（5）重命名表

ALTER tb_name RENAME [AS] new_tb_name;
RENAME TABLE tb_name TO new_tb_name;
注意：[]内的是可以选择的, 包括 change，modify，alter 后满的 COLUMN

DML 语言：数据操纵语言

（1）插入

INSERT INTO tb_name(field1,…) VALUES(value1,…);

（2）修改

UPDATE tb_name SET f1=v1,f2=v2…;
（3）删除

DELETE FROM tb_name WHERE condition
5.DQL: 数据查询,SELECT 专题

SELECT f1,f2,… FROM tb_name
WHERE primary_constraint
GROUP BY grouping_columns
ORDER BY column_name DESC/ASC
HAVING secdonary_constraint
LIMIT count;
关键语法：

SELECT *
SELECT DISTINCT *
SELECT TOP count *

（1）单表查询

SELECT * FROM tb_name; // 所有字段
SELECT id,name FROM tb_name; // 指定字段
SELECT * FROM tb_name WHERE id<5 // 查询符合条件的字段
（2）IN, NOT IN, BETWEEN AND（这是指明字段的范围，后面还有一个 IN 用于子查询）

SELECT * FROM tb_name WHERE id IN(1,2,3);
SELECT * FROM tb_name WHERE id NOT IN(1,2,3);
SELECT * FROM tb_name WHERE id BETWEEN 1 AND 5;
（3）使用 LIKE + 通配符

%：匹配一个或者多个字符
_: 匹配任意单个字符

SELECT * FROM tb_name WHERE user LIKE ‘m%r_’;// 以 m 开头，中间若干个字符，r 后面一个字符结尾
（4）空值查询 IS NULL,IS NOT NULL

SELECT * FROM tb_name WHERE row IS NULL;
SELECT * FROM tb_name WHERE row IS NOT NULL;
（5）多条件查询 AND, OR

SELECT * FROM tb_name WHERE id=1 AND name=’Jack’;
SELECT * FROM tb_name WHERE id=1 OR name=’Jack’;
（6）去掉重复行

SELECT DISTINCT * FROM tb_name;
（7）使用 ORDER BY [ASC/DESC]

SELECT * FROM tb_name ORDER BY id DESC
SELECT * FROM tb_name ORDER BY id ASC
（8）GROUP BY 子句
注意：没有出现在 GROUP BY 子句中的字段，不能出现在 SELECT 子句中，聚合函数除外
如果 SELECT 子句出现 GROUP BY 中没有出现的字段，则结果只显示每一组的一条记录；
一般形式为：

SELECT field,func_name(field) FROM tb_name GROUP BY field;
（9）多字段分组：2 个字段的笛卡尔积

SELECT field1,field2 FROM tb_name GROUP BY field1,field2;
（10）LIMIT 限制结果集

SELECT * FROM db_name WHERE id<5 LIMIT 3; // 显示结果的 3 行
SELECT * FROM db_name WHERE id<5 LIMIT 5,10; // 从第 5 行开始，的 10 行
（11）聚合函数：MIN(),AGV,MAX,COUNT
聚合函数的特点是根据一组数据计算出一个值，聚合函数只能对非 NULL 值进行计算，NULL 值被忽略；

（12）连接查询：

理解：就是有至少 2 张表，查询结果是两张表满足某种条件的拼接；不带查询条件，则是 2 张表的笛卡尔积；

①内连接查询，最常见的是等值连接

SELECT * FROM tb_left,tb_right WHERE tb_left.id=tb_right.id;
SELECT * FROM tb_left INNER JOIN tb_right WHERE tb_left.id=tb_right.id;

②外连接查询：
左外连接: 内连接结果，还包含不符合条件的左表数据，右表相应列加 NULL

SELECT * FROM tb_left LEFT [OUTER] JOIN tb_right WHERE tb_left.id=tb_right.id;
右外连接：

SELECT * FROM tb_left RIGHT [OUTER] JOIN tb_right WHERE tb_left.id=tb_right.id;
全外连接：

SELECT * FROM tb_left FULL OUTER JOIN tb_right WHERE tb_left.id=tb_right.id;
（13）子查询:

①IN

SELECT * FROM tb_name WHERE id IN(SELECT id FROM tb_2);
②ANY

SELECT * FROM tb_name WHERE id<ANY(SELECT id FROM tb_2);
③ALL

SELECT * FROM tb_name WHERE id<ALL(SELECT id FROM tb_2);
④EXISTS 子查询返回一个 bool 值

SELECT FROM tb_1 WHERE row>=90 AND EXISTS(SELECT FROM tb_2 WHERE score=’ 优秀 ’);
// 如果有 score 为优秀的记录，则查询 row>=90 的记录
⑤关系运算，子查询返回唯一值

SELECT * FROM tb_name WHERE id >(SELECT id FROM tb_2 WHERE id=1);
注：子查询一般都是一个单列列表，SELECT list FROM tb_name; list 为单列列表
使用 EXISTS 时候除外；
使用关系运算符是子查询返回唯一值；

（14）合并查询结果

UNION: 合并查询结果，除去相同的行
UNION ALL : 合并查询结果，简单的把两个集合的元素混合

SELECT id,name UNION SELECT id,author; // 正确
SELECT id,name UNION SELECT id; // 错误，字段数目不同，无法 UNION
注意：前提条件是，左边和右边的字段列表的字段数必须相同

Mysql 函数

6.1 数学函数

（1）ABS(X) 取绝对值

SELECT ABS(-1); // 结果为 1
（2）取整函数

CEIL(); // 向上取整
FLOOR(); // 向下取整
（3）随机数生成器

RAND(); // 0-1 随机数
RAND(X); // 产生 0 - 1 随机数，x 相同时，随机数相同
（4）符号检测器

SIGN(X); // 返回 -1,0,1 作为参数的符号
（5）圆周率

SIGN(X); // 返回 -1,0,1 作为参数的符号
（6）小数位截断函数

TRUNCATE(X,Y) // 保留 x 后 y 位小数
（7）圆整（向最近的整数靠近）

ROUND(X) // 返回离 X 最近的整数
ROUND(X,Y) // 返回 x，后面保留 y 位小数，截断时四舍五入

             // 例如 ROUND(3.1415，3)=3.142

（8）平方、开方

POW(X,Y) // x 的 y 次方
SQRT(X); // 对 x 开平方
MOD(X,Y) // 求余数
（9）指数对数

EXP(X); // e 的 x 次方
LOG(X);
LOG10(X);
（10）弧度角度转换

DEGREES(radians) // 弧度 — 角度
RADIANS(degree) // 度数 — 弧度
（11）三角函数

复制代码
SIN(X); // x 是弧度
ASIN(X);
COS(X);
ACOS(X);
TAN(X);
ATAN(X);
COT(X);
复制代码

6.2 字符串函数

（1）长度

CHAR_LENGTH(str);
（2）转换大小写

UPPER(str);UCASE(str);
LOWER(str);LCASE(str);
（3）修剪函数（针对前导、结尾空格）

LTRIM(s);RTRIM(s);TRIM(s);// 删除引导空格；结尾空格；引导和结尾空格
TRIM(s1 from s_src); // 删除 s2 开始和结尾处的 s1
（4）替换、子串、反转

REPLACE(s,s1,s2); // 用 s2 替换 s 中的 s1
SUBSTRING(s,n,len); // s 处 n 开始长度为 len 的字符串
LEFT(s,n),RIGHT(s,n) // 返回左边或者右边长度为 n 的字符串
REVERSE(s); // 字符串倒序
更多请参考：PHP 学习路线图《MySQL 快速入门》

6.3 日期和时间函数 ———DATE
|———-TIME

（1）日期、时间

CURRENT_DATE(); // 2014-08-07
CURRENT_TIME(); // 10:30:30
（2）日期和时间（时间戳）

NOW(); // 2014-08-07 10:30:30
CURRENT_TIMESTAMP();// 2014-08-07 10:30:30

LOCALTIME(); // 2014-08-07 10:30:30
LOCALTIMESTAMP(); // 2014-08-07 10:30:30
SYSDATE(); // 2014-08-07 10:30:30
（3）以秒形式的时间戳

UNIX_TIMESTAMP(); // 当前 UNIX 时间戳
UNIX_TIMESTAMP(d);
FROM_UNIXTIME(d);
（4）年月日、时分秒

YEAR(d);
MONTH(d);
DAY(d);
HOUR(t);
MINUTE(t);
SECOND(t);
（5）工具函数

DATEDIFF(d1,d2) // d1-d2 的天数，d1 是最近的日期，d2 是早些的日期
ADDDATE(d,n);
SUBDATE(d,n);
更多请参考：PHP 学习路线图《MySQL 快速入门》

6.4 条件判断函数

6.5 系统信息函数
（1）版本号、连接数

VERSION(); // 获取版本
CONNECTION_ID(); // 获取连接数
（2）当前数据库名

DATABASE();SCHEMA(); // 当前数据库
（3）当前用户

USER();SYSTEM_USER();SESSON_USER(); // 当前用户
CURRENT_USER();
（4）字符集

CHARSET();
（5）最后插入的 ID

LAST_INSERT_ID();

6.6 加密函数

MD5(str); // 普通数据加密
PASSWORD(s); // 不可逆加密, 对用户名加密

ENCODE(str_dst,pass_str); // 用 pass_str 加密 str_dst, 返回二进制数，用 blob 类型存储
DECODE(crypt_str,psss_str);// 用 pass_str 解密 crpyt_str, 加密和解密用的 pass_str 相同

mysql入门精髓

1. Mysql 服务器操作：

2. Database 基本操作

3. DDL 表操作