@EncyKe
2016-10-27T06:00:50.000000Z
字数 7855
阅读 3483
数据库
MySQL 开发技巧着重于 DML:SELECT
,INSERT
,UPDATE
,DELETE
。
# 安装 MySQL 服务端、核心程序
sudo apt-get install mysql-server
# 安装 MySQL 客户端
sudo apt-get install mysql-client
sudo netstat -tap | grep mysql
数据类型 | 大小(字节) | 用途 | 格式 |
---|---|---|---|
INT | 4 | 整数 | |
FLOAT | 4 | 单精度浮点数 | |
DOUBLE | 4 | 双精度浮点数 | |
ENUM | 单选,比如性别 | ENUM('a', 'b', 'c') | |
SET | 多选 | SET('1', '2', '3') | |
DATE | 3 | 日期 | YYYY-MM-DD |
TIME | 3 | 时间点或持续时间 | HH:MM:SS |
YEAR | 1 | 年份值 | YYYY |
CHAR | 0~255 | 定长字符串 | |
VARCHAR | 0~255 | 变长字符串 | |
TEXT | 0~65535 | 长文本数据 |
VARCHAR
、TEXT
、DATE
、TIME
、ENUM
等类型的数据也需要单引号修饰;INT
、FLOAT
、DOUBLE
等则不需要;CHAR
和 VARCHAR
的区别:CHAR
的长度是固定的,而 VARCHAR
的长度是可以变化的。 CHAR (10)
,表示存储的字符将占 10 个字节(包括 7 个空字符),而同样的 VARCHAR(12)
则只占用 3 个字节的长度,12 只是最大值,当你存储的字符小于 12 时,按实际长度存储。ENUM
和 SET
的区别:ENUM
类型的数据的值,必须是定义时枚举的值的其中之一,即单选,而 SET
类型的值则可以多选。约束类型 | 主键约束 | 默认约束 | 唯一约束 | 外键约束 | 非空约束 |
---|---|---|---|---|---|
关键字 | PRIMARY KEY | DEFAULT | UNIQUE | FOREIGN KEY | NOT NULL |
CREATE TABLE <表名>(...)
内部声明。列名a 数据类型(数据长度) PRIMARY KEY
CONSTRAINT <主键名> PRIMARY KEY (<主键列名>)
CONSTRAINT <主键名> PRIMARY KEY (<主键列一名,主键列二名>)
列名a 数据类型(数据长度) DEFAULT '数据'
UNIQUE (列名)
CONSTRAINT <外键名> FOREIGN KEY (<外键列名>) REFERENCES <表名>(<列名>)
列名a 数据类型(数据长度) NOT NULL
函数名 | COUNT | SUM | AVG | MAX | MIN |
---|---|---|---|---|---|
作用 | 计数 | 求和 | 求平均值 | 最大值 | 最小值 |
一种与表有关的结构,它的作用相当于书的目录,可以根据目录中的页码快速找到所需的内容。
当表中有大量记录时,若要对表进行查询,没有索引的情况是全表搜索:将所有记录一一取出,和查询条件进行一一对比,然后返回满足条件的记录。这样做会消耗大量数据库系统时间,并造成大量磁盘 I/O 操作。
而如果在表中已建立索引,在索引中找到符合查询条件的索引值,通过索引值就可以快速找到表中的数据,可以大大加快查询速度。
从一个或多个表中导出来的表,是一种虚拟存在的表。它就像一个窗口,通过这个窗口可以看到系统专门提供的数据,这样,用户可以不用看到整个数据库中的数据,而只关心对自己有用的数据。
创建视图的语句,后半句是一个 SELECT 查询语句,所以视图也可以建立在多张表上,只需在 SELECT 语句中使用子查询或连接查询。
# 打开 MySQL 服务
sudo service mysql start
# 使用 root 用户登录
mysql -u root
show databases;
use <数据库名>
示例:use information_schema
;
show tables;
quit
或者
exit
CREATE DATABASE <数据库名>;
示例:CREATE DATABASE mysql_firstdatabase;
。
CREATE TABLE <表名>
(
<列名a> 数据类型(数据长度),
<列名b> 数据类型(数据长度),
<列名c> 数据类型(数据长度)
);
INSERT INTO 表名(列名a, 列名b, 列名c) VALUES(值1, 值2, 值3);
示例:
INSERT INTO employee(id, name, phone) VALUES(01,'Tom',110110110);
# 或者:
INSERT INTO employee VALUES(02, 'Jack', 119119119);
# 或者:
#表中 Rose 的 phone 为 NULL
INSERT INTO employee(id, name) VALUES(03, 'Rose');
DROP DATABASE <库名>;
DROP TABLE <表名>;
ENAME TABLE 原名 TO 新名;
# 或者:
ALTER TABLE 原名 RENAME 新名;
# 或者:
ALTER TABLE 原名 RENAME TO 新名;
ALTER TABLE 表名 ADD COLUMN 列名 数据类型 约束;
# 或者:
ALTER TABLE 表名 ADD 列名 数据类型 约束;
# 在 AFTER 后的列名右边插入
ALTER TABLE 表名 ADD 列名 数据类型 约束 AFTER 列名;
# 使用 FIRST 使之放在第一列
ALTER TABLE 表名 ADD 列名 数据类型 约束 FIRST;
ALTER TABLE 表名 DROP COLUMN 列名;
# 或者:
ALTER TABLE 表名 DROP 列名;
ALTER TABLE 表名 CHANGE 原列名 新列名 数据类型 约束;
# CHANGE 语句中原列名和新列名相同时,指定新的数据类型或约束即可修改数据类型或约束;
ALTER TABLE 表名字 MODIFY 列名字 新数据类型;
# 或者:
ALTER TABLE 表名字 MODIFY 列名字 新数据类型;
UPDATE 表名字 SET 列1=值1,列2=值2 WHERE 条件;
DELETE FROM 表名字 WHERE 条件;
ALTER TABLE 表名 ADD INDEX 索引名 (列名);
# 或者:
CREATE INDEX 索引名 ON 表名 (列名);
索引的效果是加快查询速度,当表中数据不够多的时候是感受不出它的效果的。使用 SELECT 语句查询的时候,语句中 WHERE 里面的条件,会自动判断有没有可用的索引。
CREATE VIEW 视图名(列a, 列b, 列c) AS SELECT 列1, 列2, 列3 FROM 表名字;
LOAD DATA INFILE '文件路径' INTO TABLE 表名;
SELECT 列1, 列2 INTO OUTFILE '文件路径和文件名' FROM 表名字;
mysqldump -u root 库名>备份文件名;
mysqldump -u root 库名 表名>备份文件名;
source <.sql路径>
# 或者:
# 备份的整个数据库
mysqldump -u root 库名<备份文件名;
SELECT name, age FROM employee;
SELECT name, age FROM employee WHERE age>25;
SELECT name, age, phone FROM employee WHERE name='Mary';
# 筛选出大于 25,小于 30 的age
SELECT name, age FROM employee WHERE age>25 AND age<30;
# 筛选出大于等于 25,小于等于 30 的 age
SELECT name, age FROM employee WHERE age BETWEEN 25 AND 30;
# 在 dpt3 或 dpt4 中
SELECT name, age, phone, in_dpt FROM employee WHERE in_dpt IN ('dpt3', 'dpt4');
# 既不在 dpt3 也不在 dpt4 中
SELECT name, age, phone, in_dpt FROM employee WHERE in_dpt NOT IN ('dpt1', 'dpt3');
# _ 通配一个未定字符
SELECT name, age, phone FROM employee WHERE phone LIKE '1101__';
# %通配不定个未定字符
SELECT name, age, phone FROM employee WHERE name LIKE 'J%';
# 对结果排序
SELECT name, age, salary, phone FROM employee ORDER BY salary DESC;
# 函数计算
SELECT MAX(salary) AS max_salary,MIN(salary) FROM employee;
SELECT of_dpt, COUNT(proj_name) AS count_project FROM project
WHERE of_dpt IN
(SELECT in_dpt FROM employee WHERE name='Tom');
SELECT id, name, people_num
FROM employee, department
WHERE employee.in_dpt = department.dpt_name
ORDER BY id;
此举等同于 JOIN ON 语句:
SELECT id, name, people_num
FROM employee JOIN department
ON employee.in_dpt = department.dpt_name
ORDER BY id;
SELECT 列名
FROM 表1
INNER JOIN 表2
ON 表1.值 = 表2.值;
SELECT 列名
FROM 表1
LEFT JOIN 表2
ON 表1.值 = 表2.值
WHERE 表2.值 IS NULL;
SELECT 列名
FROM 表1
RIGHT JOIN 表2
ON 表1.值 = 表2.值
WHERE 表1.值 IS NULL;
SELECT 列名
FROM 表1
LEFT JOIN 表2
ON 表1.值 = 表2.值
UNION JOIN
SELECT 列名
FROM 表2
RIGHT JOIN 表1
ON 表1.值 = 表2.值;
交叉连接 (CROSS)
: 即两表叉乘(笛卡尔积连接);
SELECT 列名
FROM 表1
CROSS JOIN 表2;
UPDATE 表1
SET 键='值'
WHERE 表1.列名
IN
(SELECT 表2.列名
FROM 表1
LEFT JOIN 表2
ON 表1.列名 = 表2.列名);
UPDATE 表1
JOIN
(SELECT 表2.列名
FROM 表1
JOIN 表2
ON 表1.列名 = 表2.列名) 表2
ON 表1.列名 = 表2.列名
SET 表1.列名 = '值';
示例:将 user1 表中 user_name 与 user2 表中 user_name 相同的 user1 表的 over 替换成 user2 表中的 over 值;
UPDATE user1
SET over='齐天大圣'
WHERE user1.user_name
IN
(SELECT b.user_name
FROM user1 a
LEFT JOIN user2 b
ON a.user_name = b.user_name);
↓↓
UPDATE user1 a
JOIN
(SELECT b.user_name
FROM user1 a
JOIN user2 b
ON a.user_name = b.user_name) b
ON a.user_name = b.user_name
SET a.over = '齐天大圣';
SELECT 列名,
(
SELECT 列名
FROM 表2
WHERE 表1.列名 = 表2.列名
)
AS 新列名
FROM 表1;
SELECT 列名
FROM 表1
LEFT JOIN 表2
ON 表1.列名 = 表2.列名;
示例:
SELECT a.user_name, a.over,
(
SELECT over
FROM user2 b
WHERE a.user_name = b.user_name
)
AS over2
FROM user1 a;
↓↓
SELECT a.user_name, a.over, b.over
FROM user1 a
LEFT JOIN user2 b
ON a.user_name = b.user_name;
示例:查询出四人组中打怪最多的日期;
SELECT a.user_name, b.timestr, b.kills
FROM user1 a
JOIN user_kills b
ON a.id = b.user_id
WHERE b.kills = (SELECT MAX(c.kills)
FROM user_kills c
WHERE c.user_id = b.user_id);
↓↓
SELECT a.user_name, b.timestr, MAX(b.kills)
FROM user1 a
JOIN user_kill b
ON a.id=b.user_id
GROUP BY a.user_name;
或者:
SELECT a.user_name, b.timestr, b.kills
FROM user1 a
JOIN user_kills b ON a.user_id = b.user_id
JOIN user_kills c ON c.user_id = b.user_id
GROUP BY a.user_name, b.timestr, b.kills
HAVING b.kills = MAX(c.kills);
示例:分类聚合方式查询每一个用户某一个字段数据最大的两条数据;
SELECT a.user_name, b.timestr, b.kills
FROM user1 a
JOIN user_kills b
ON a.id = b.user_id
WHERE user_name = '孙悟空'
ORDER BY b.'kills' DESC
LIMIT 2;
↓↓
SELECT d.user_name, c.ctimestr, kills
FROM
(SELECT user_id, timestr, kills,
(
SELECT count(*)
FROM user_kills b
WHERE b.user_id = a.user_id AND a.kills <= b.kills
)
AS cnt
FRP, user_kills a
GROUP BY user_id, timestr, kills) c
JOIN user1 d
ON c.user_id = d.id
WHERE cnt <= 2;