跳至主要內容

mysql教程

Moments大约 28 分钟

mysql教程

数据库存储引擎

MyISAM

  • 不需要事务支持（不支持）
  • 并发相对较低（锁定机制问题）
  • 数据修改相对较少（阻塞问题），以读为主
  • 数据一致性要求不是非常高
  • 尽量索引（缓存机制）
  • 调整读写优先级，根据实际需求确保重要操作更优先
  • 启用延迟插入改善大批量写入性能
  • 尽量顺序操作让insert数据都写入到尾部，减少阻塞
  • 分解大的操作，降低单个操作的阻塞时间
  • 降低并发数，某些高并发场景通过应用来进行排队机制
  • 对于相对静态的数据，充分利用Query Cache可以极大的提高访问效率
  • MyISAM的Count只有在全表扫描的时候特别高效，带有其他条件的count都需要进行实际的数据访问

InnoDB

  • 需要事务支持（具有较好的事务特性）
  • 行级锁定对高并发有很好的适应能力，但需要确保查询是通过索引完成
  • 数据更新较为频繁的场景
  • 数据一致性要求较高
  • 硬件设备内存较大，可以利用InnoDB较好的缓存能力来提高内存利用率，尽可能减少磁盘 IO
  • 主键尽可能小，避免给Secondary index带来过大的空间负担
  • 避免全表扫描，因为会使用表锁
  • 尽可能缓存所有的索引和数据，提高响应速度
  • 在大批量小插入的时候，尽量自己控制事务而不要使用autocommit自动提交
  • 合理设置innodb_flush_log_at_trx_commit参数值，不要过度追求安全性
  • 避免主键更新，因为这会带来大量的数据移动

mysql数据库链接

error_reporting(E_ALL);
date_default_timezone_set("PRC");
$host = "localhost";
$user = "root";
$pwd = "123456";
$conn = mysql_connect($host, $user, $pwd) or die("connect Faild");

mysql显示所有数据库

$result = mysql_query("show databases", $conn);
while($myarr = mysql_fetch_array($result, MYSQL_ASSOC)){
    print_r($myarr);
    echo nl2br("\n");
}

mysql语句

#增加
INSERT INTO tbl_name () VALUES();    
#修改
UPDATE tbl_name SET name='Moments';
#查询
SELECT * FROM tbl_name;
#删除
DELETE FROM tbl_name WHERE name='Moments';

PDO如何使用

$dbms = 'mysql';
$host = 'localhost';
$dbName = '';
$user = 'root';
$pwd = '123456';
$dsn = "$dbms:host=$host;dbname=$dbName";
try{
    $pdo = new PDO($dsn, $user, $pwd);
    $query = "select * from dede_archives limit 0, 30";
    $result = $pdo->prepare($query);
    $result->execute();
    while($res = $result->fetch()){
        print_r($res);
    }
}catch(PDOException $e){
    die($e->getMessage());
}

高级MySQL

新建表

CREATE TABLE IF NOT EXISTS `t1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `content` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

删除表

DROP TABLE `t1`;

插入表

insert into `t1` (`title`, `content`) values ('A', 'Hello World!');
insert into `t1` (`title`, `content`) values ('B', 'Hello World!');
insert into `t1` (`title`, `content`) values ('C', 'Hello World!');
insert into `t1` (`title`, `content`) values ('D', 'Hello World!');
insert into `t1` (`title`, `content`) values ('E', 'Hello World!');
insert into `t1` (`title`, `content`) values ('F', 'Hello World!');
insert into `t1` (`title`, `content`) values ('G', 'Hello World!');

复制表

全表复制

// 复制完整的字段结构和索引复制到表A中来。
CREATE TABLE `t2` LIKE `t1`;
INSERT INTO `t2` (`title`, `content`) SELECT `title`, `content` from `t1`;

部分表复制

// 复制表的字段结构，但不复制表中的索引。可以填充数据。
CREATE TABLE `t2` AS SELECT `title` FROM `t1` where id > 5;

索引操作

创建索引

// 索引
CREATE INDEX `index_name` ON `t2` (`title`);
ALTER TABLE `t2` ADD INDEX `index_name` (`title`);
// 唯一索引
CREATE UNIQUE INDEX `index_name` ON `t2` (`title`);
// 主键
ALTER TABLE `t2` ADD `id` INT NOT NULL;
ALTER TABLE `t2` MODIFY `id` INT NOT NULL;
ALTER TABLE `t2` ADD PRIMARY KEY (`id`);

删除索引

// 删除索引/唯一索引
DROP INDEX `index_name` ON `t2`;
// 删除主键
ALTER TABlE `t2` DROP PRIMARY KEY;

查询索引

// 查询索引/唯一索引
SHOW INDEX FROM `t2`;
// 查询主键
SELECt * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME='t2';

MySQL视图

视图：通过一个条件，把一部分数据从一张表里面提取出来，形成一张中间表，这张表就是视图。

创建视图

create view view_name as select * from t1 order by id desc limit 10;

查询视图

select * from view_name;

删除视图

DROP VIEW view_name;

MySQL常用函数

绝对值

select ABS(-100);
// 结果
100

取余

select MOD(15, 7);
// 结果
1

向下取整

select FLOOR(1.8);
// 结果 
1
select FLOOR(-1.8);
// 结果
-2

向上取整

select CEIL(1.8);
// 结果
2
select CEIL(-1.8);
// 结果
-1

四舍五入

select ROUND(1.58);
// 结果
2
select ROUND(1.48);
// 结果
1
select ROUND(-1.58);
// 结果
-2

字符串，取ASCII码值

select ASCII('A');
// 结果
65

字符串，连接符

select CONCAT('My', 'SQL');
// 结果
MySQL

字符串，长度

select LENGTH('Hello World!');
// 结果
12

字符串，清空前后空格

select TRIM(' bar ');

字符串，替换

select REPLACE('www.php.com', 'com', 'net');
// www.php.net

正则表达式

select 'Hello' REGEXP '^Hello$';
// 结果
1
select * from t1 where title regexp '^Hello';

日期，秒

select second(now());

日期，分

select minute(now());

日期，时

select hour(now());

日期，获取天

select day(now());

日期，星期

// (1=星期天，2=星期一, …7=星期六)
select DAYOFWEEK(NOW());
select DAYOFWEEK(created_at);

日期，月

select month(now());

日期，年

select year(now());

日期，增加，减少

// 进行日期增加的操作，可以精确到秒
date_add(date, interval expr type)
// 进行日期减少的操作，可以精确到秒
date_sub(date, interval expr type)

日期，当前日期

select curdate()

日期格式化

select date_format(now(), '%Y-%m-%d %H:%i:%s');

系统信息，版本号

select version()

系统信息，当前数据库名

select database()

数据库操作

Ubuntu安装

sudo apt-get install mysql-server
sudo apt-get install mysql-client

登录数据库

查找数据库所在文件夹位置

sudo find / -name 'mysql'

登录数据库

alias mysql=/usr/local/mysql-5.6.35-macos10.12-x86_64/bin/mysql
mysql -p localhost -u root -p123456

操作

use pay; // 使用pay数据库
show tables '%00%'; // 显示所有包含00的数据表
desc 00_history; // 显示表信息

索引干嘛用的

  • 加快数据访问速度(select, delete, update)
  • 提供约束(UNIQUE, FOREIGN KEY)
  • 没有索引，数据也可以访问到，但是会很慢

索引的类型

  • BTREE Indexes --MySQL大部分是这种类型(Innodb)
  • RTREE Indexes --MyISAM only, for GIS
  • HASH Indexes --MEMORY, NDB
  • FULLTEXT Indexes --MyISAM, InnoDB 5.6

BTREE Index用于哪些操作

  • KEY = 5 (Point SELECT)
  • KEY > 5 (RANGE SELECT)
  • 5<KEY<10 (closed range)

字符串索引

  • 本质上没差别，“AAAA” < “AAAB”(collation)
  • LIKE是特殊的范围查询
  • LIKE “ABC%” 等同于“ABC[LOWEST]”<KEY<“ABC[HIGHEST]”-
  • LIKE “%ABC”无法使用索引

索引的开销

  • 索引很消耗资源，不添加不必要的索引。
  • 对于写：索引的更新往往是最主要的开销。
  • 对于读：索引会占用内存跟硬盘空间。

EXPLAIN关键字介绍

用于理解语句的执行计划

适用于SELECTs, INSERTs, UPDATEs,

REPLACEs, DELETEs and connections

文档

http://dev.mysql.com/doc/refman/5.6/en/explain-output.html

EXPLAIN Example

MariaDB [nlwiktionary]> EXPLAIN SELECT * FROM page WHERE  page_title = 'Dutch'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: page
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 90956
Extra: Using where
1 row in set (0.00 sec)
type: 一般分all, const, range, ref
possible_keys: 可能用的索引 
key: 实际使用的索引
key_len:  索引的大小
rows: 遍历的行数

索引 Innodb Tables

  • 数据按主键聚集
  • 主键隐藏添加在所有索引的后面 KEY(A)等同于KEY(A,ID)

MySQL如何使用索引

  • 数据查找
  • 排序
  • 避免读取“数据”
  • 特殊的优化

索引用于数据查找

SELECT * FROM EMPLOYEES WHERE　LAST_NAME=“Smith” 可以使用索引 (LAST_NAME)

SELECT * FROM EMPLOYEES WHERE LAST_NAME=“Smith” AND DEPT=“Accounting” 可使用索引 (DEPT,LAST_NAME)

索引含有多个字段

考虑有索引 (A,B,C)，请留意字段的顺序

以下情况能够使用索引

  • A>5
  • A=5 AND B>6
  • A=5 AND B=6 AND C=7
  • A=5 AND B IN (2,3) AND C>5

以下情况不能使用索引

  • B>5
  • B=6 AND C=7

​以下情况只能使用到索引的部分字段

  • A>5 AND B=2 - 只能用上index(A)
  • A=5 AND B>6 AND C=2 - 只能用上index(A,B)

索引用于排序

SELECT * FROM PLAYERS ORDER BY SCORE DESC LIMIT 10 --INDEX(SCORE)

SELECT * FROM PLAYERS WHERE COUNTRY=“US” ORDER BY SCORE DESC LIMIT 10 --INDEX(COUNTRY, SCORE)

多字段索引与排序

考虑有索引(A, B)，留意字段顺序

以下情况能使索引用于排序

  • ORDER BY A
  • A=5 ORDER BY B
  • ORDER BY A DESC, B DESC
  • A>5 ORDER BY A

以下情况不能使用索引用于排序

  • ORDER BY B - 第二个字段上面排序
  • A>5 ORDER BY B – 第一个字段是范围查询
  • A IN(1,2) ORDER BY B - 第一个字段是IN范围查询
  • ORDER BY A ASC, B DESC - 排序字段的顺序不一致

使用多个索引

MySQL一般情况一次查询只能使用一个索引。

MySQL能使用多个索引，index merge。

SELECT * FROM TBL WHERE A=5 AND B=6

能分别单独使用索引(A)或者(B)

但是使用索引(A,B)效果会更好

SELECT * FROM TBL WHERE A=5 OR B=6

这个语句能用上索引(A),(B); (A,B)在这里用不上

前缀索引

ALTER TABLE TITLE ADD KEY(TITLE(20));

索引TEXT/BLOB时用到

前缀索引更小

不能用作covering index

需要考虑用作索引前缀的字符长度限制

  • latin1 767
  • utf8 255
  • utf8mb4 191
  • innodb_large_prefix

MySQL是如何选择索引的？

  • 每次查询动态选择
  • 估算走索引需要查询的行数
  • 根据“Cardinality”的状态

索引策略

给最频繁的语句加索引 --要整体来看，而不是一条一条语句添加

尽可能扩展索引，而不是新增索引

WHERE条件跟JOIN都能用上索引是最好的，

新增索引后要验证索引是否生效，是否对能提升性能

例子：

  • SELECT * FROM TBL WHERE A=5 AND B=6
  • SELECT * FROM TBL WHERE A>5 AND B=6
  • 应该新增索引(B,A)

技巧1：范围查询改为枚举类型

考虑索引(A,B)

SELECT * FROM TBL WHERE A BETWEEN 2 AND 4 AND B=5 -- 只能用到前半部分

SELECT * FROM TBL WHERE A IN (2,3,4) AND B=5 --能用上整个索引

技巧2：增加假的条件

考虑索引KEY (GENDER,CITY)

SELECT * FROM PEOPLE WHERE CITY=“NEW YORK” --无法使用索引

SELECT * FROM PEOPLE WHERE GENDER IN (“M”,”F”) AND CITY=“NEW YORK” -- 能用索引

对于Gender, Status, Boolean 类型非常有效

技巧3：Unionizing Filesort

考虑索引KEY(A,B)

SELECT * FROM TBL WHERE A IN (1,2) ORDER BY B LIMIT 5; --无法使用索引用作排序

(SELECT * FROM TBL WHERE A=1 ORDER BY B LIMIT 5) UNION ALL (SELECT * FROM TBL WHERE A=2 ORDER BY B LIMIT 5) ORDER BY B LIMIT 5; --能使用索引用作排序，没有file_sort

Bug: order by limit

select * from tb where a=1 and b =2 order by c limit 10

有时候不能正确的选择索引，这时候需要指定索引或者改为

select * from tb where a=1 and b =2 order by c+0 limit 10

Example

select * from gp_attend_outside_11 where gid = 1899 and status = 0 and parent_id = 0 and user_id = 4283298

SELECT * FROM home_topics WHERE deleted = '0' AND pid = '0' AND user_id IN ( '590842346' , '18307584' , '511367073' , '511013577' , '510963766' , '337067800' ) ORDER BY create_time DESC LIMIT 0 , 15;

SELECT * FROM home_topics WHERE deleted = '0' AND pid = '0' AND ( tid IN ( '49647' , '141927' , ....) OR user_id IN ( '27006139' , '510030789' , '335837268' , '511178550' , '363484762' , .... ) ) ORDER BY create_time DESC LIMIT 0 , 15;

SELECT SUM(file_size) FROM u_user_file_39 WHERE user_id = '3399719' AND area_id = 1;

SQL简介

  • SQL是结构化查询语言,Structured Query Language.

数据库表

一个数据库通常包含一个或多个表,每个表由一个名字标识. 表包含带有数据的记录.

use mydb; // 命令用于选择数据库.
set names utf8; // 命令用于设置使用的字符集.
select * from mytable; // 读取数据表信息.
  • SQL对大小写不敏感.

常用SQL语句

  • select // 从数据库中提取数据
  • update // 更新数据库中的数据
  • delete // 从数据库中删除数据
  • insert into // 向数据库中插入新数据
  • create database // 创建新数据库
  • alter database // 修改数据库
  • create table // 创建新表
  • alter table // 变更数据库表
  • drop table // 删除表
  • create index // 创建索引
  • drop index // 删除索引

SELECT语句

从数据库中选择数据.

SELECT column_name, column_name
FROM table_name;

SELECT DISTINCT语句

用于返回唯一不同的值

SELECT DISTINCT column_name, column_name
FROM table_name;

WHERE子句

用于提取满足筛选条件的记录

SELECT column_name, column_name
FROM table_name
WHERE column_name operator value;
运算符描述
=等于
<>不等于

|大于 <|小于 =|大于等于 <=|小于等于 BETWEEN|在某个范围内 LIKE|搜索某种模式 IN|指定对某个列的多个可能值

AND,OR运算符

用于基于一个以上的条件记录进行过滤

ORDER BY

用于对结果进行排序

  • ASC升序排列
  • DESC降序排列
SELECT column_name,column_name
FROM table_name
ORDER BY column_name,column_name ASC|DESC;

INSERT INTO语句

用于向表中插入新记录

INSERT INTO table_name
VALUES (value1, value2, value3);

INSERT INTO table_name (column1, column2, column3)
VALUES (value1, value2, value3)

UPDATE语句

用于更新表中的记录

UPDATE table_name
SET column1=value1, column2=value2
WHERE column_name = value;

DELETE语句

DELETE语句用于删除表中的记录

DELETE FROM table_name
WHRE column_name = value;

SQL进阶

LIMIT语法

select limit 子句用于规定要返回的记录数目.

mysql语法

SELECT column_name(s)
FROM table_name
LIMIT number;

LIKE语法

'%a'    //以a结尾的数据
'a%'    //以a开头的数据
'%a%'    //含有a的数据
'_a_'    //三位且中间字母是a的
'_a'    //两位且结尾字母是a的
'a_'    //两位且开头字母是a的

SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern;

通配符

在sql中,通配符与sql like操作一起使用

%	替代 0 个或多个字符
_	替代一个字符
[charlist]	字符列中的任何单一字符
[^charlist]或[!charlist]	不在字符列中的任何单一字符

IN操作符

IN操作符允许WHERE子句中规定多个值

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...);

BETWEEN操作符

用于选取介于两个值之间的数据范围内的值

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

别名

通过使用SQL,可以为表名称或列名指定别名.

创建别名的原因,可读性,规避重名的问题.

// 在下面的情况下，使用别名很有用：
在查询中涉及超过一个表
在查询中使用了函数
列名称很长或者可读性差
需要把两个列或者多个列结合在一起

// 列的 SQL 别名语法
SELECT column_name AS alias_name
FROM table_name;
// 表的 SQL 别名语法
SELECT column_name(s)
FROM table_name AS alias_name;

JOIN连接

JOIN操作是把两个或多个表的行做笛卡尔积结合起来.

  • INNER JOIN 如果表中有至少一个匹配,则返回行
  • LEFT JOIN 左表必须存在,右表可以为空
  • RIGHT JOIN 右表必须存在,左边可以为空
  • FULL JOIN 只要其中一个表中存在匹配,则返回行
SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;
// INNER JOIN与JOIN是相同的
SELECT column_name(s)
FROM table1
JOIN table2
ON table1.column_name=table2.column_name;

LEFT JOIN左连接

LEFT JOIN关键字从左表返回所有的行,即使右表中没有匹配. 如果右表中没有匹配,则结果为NULL.

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;

RIGHT JOIN右连接

RIGHT JOIN关键字从右表返回所有的行,即使左表中没有匹配. 如果左表中没有匹配,则结果为NULL.

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name=table2.column_name;

UNION合并操作

UNION操作符用于合并两个或多个SELECT语句的结果集.

UNION内部的每个SELECT语句必须拥有相同数量的列. 列也必须拥有相似的数据类型.

使用UNION命令时需要注意，只能在最后使用一个ORDER BY命令.

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

INSERT INTO SELECT语句

INSERT INTO SELECTY语句从一个表复制数据,然后把数据插入到一个已存在的表中. 目标表中任何已存在的行都不会受影响.

INSERT INTO table2
(column_name(s))
SELECT column_name(s)
FROM table1;
WHERE ...;

创建数据库

CREATE DATABASE语句用于创建数据库.

CREATE DATABASE dbname;

创建数据表

CREATE TABLE语句用于创建数据库中的表.

CREATE TABLE Persons
(
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);

SQL约束(Constraints)

SQL约束用于规定表中的数据规则.

如果存在违反约束的数据行为,行为会被约束终止.

// 在 SQL 中，我们有如下约束：
NOT NULL - 指示某列不能存储 NULL 值。
UNIQUE - 保证某列的每行必须有唯一的值。
PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。
CHECK - 保证列中的值符合指定的条件。
DEFAULT - 规定没有给列赋值时的默认值。

CREATE TABLE table_name
(
column_name1 data_type(size) constraint_name,
column_name2 data_type(size) constraint_name,
column_name3 data_type(size) constraint_name,
....
);

FOREIGN KEY约束

外键约束用于预防破坏表之间连接的行为.

外键约束也能防止非法数据插入外键列,因为它必须是它指向的那个表中的值之一.

CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
)

ALTER TABLE Orders
ADD FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)

ALTER TABLE Orders
DROP FOREIGN KEY fk_PerOrders

UNIQUE约束

UNIQUE约束唯一标识数据库表中的每条记录.

UNIQUE和PRIMARY KEY约束均为列或列集合提供了唯一的保证.

每个表中可以有多个UNIQUE约束,但是每个表中只能有一个PRIMARY KEY约束.

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
UNIQUE (P_Id)
)

ALTER TABLE Persons
ADD UNIQUE (P_Id)

ALTER TABLE Persons
DROP INDEX uc_PersonID

PRIMARY KEY约束

PRIMARY KEY约束唯一标识数据库表中的第条记录.

主键必须包含唯一的值.

主键列不能包含NULL值.

每个表都应该有一个主键,并且每个表中能有一个主键.

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (P_Id)
)

ALTER TABLE Persons
ADD PRIMARY KEY (P_Id)

ALTER TABLE Persons
DROP PRIMARY KEY

CHECK约束

check约束用于限制列中的值的范围.

如果对单个列定义check约束,那么该列只允许特定的值.

如果对一个表定义check约束,那么此约束会基于行中其他列的值在特定的列中对值进行限制.

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CHECK (P_Id>0)
)

// 当表被创建时
alter table Persons
add check (p_id>0)
// 定义多个列的check约束
ALTER TABLE Persons
ADD CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')

DEFAULT约束

default约束用于向列中插入默认值.

如果没有支付宝其他的值,那么会将默认值添加到所有的新记录.

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255) DEFAULT 'Sandnes'
)

alter table Persons
alter city set default '0'

alter table Persons
alter city drop default

CREATE INDEX语句

create index语句用于在表中创建索引

在不读取整个表的情况下,索引使数据库应用程序可以更快地查找数据.

在表中创建索引,可以快速高效地查询数据.

用户无法看到索引,它们只能被用来加速搜索/查询.

更新一个包含索引的表需要比更新一个没有索引的表花费更多的时间, 这是由于索引本身也需要更新. 因此,理想的做法是仅仅在常常被搜索的列上面创建索引.

create index index_name
on table_name (column_name)

alter table table_name drop index index_name

// 删除表
drop table table_name
// 清空表
truncate table table_name
// 添加列
alter table table_name
modify column column_name datatype
// 改变数据类型实例
alter table Persons
alter column column_name year
// 删除列
alter table table_name
drop column column_name

AUTO INCREMENT字段

Auto-increment会在新记录插入表中时生成一个唯一的数字.

我们通常希望在每次插入新记录时,自动地创建主键字段的值.

CREATE TABLE Persons
(
ID int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (ID)
)

视图(Views)

视图是可视化的表.

在sql中,视图是基于sql语句的结果集的可视化的表.

视图包含行和列,就像一个真实的表. 视图中的字段就是来自一个或多个数据库中的真实的表中的字段. 你可以向视图添加sql函数,where以及join语句,也可以呈现数据,就像这些数据来自于某个单一的表一样.

create view view_name as 
select column_name(s)
from table_name
where condition

create view [current product list] as 
select id, name
from Products
where id = 1
// 查询上面的视图
select * from [current product list]
// 删除视图
drop view [current product list]

Date函数

  • now() // 返回当前的日期和时间
  • curdate() // 返回当前的日期
  • curtime() // 返回当前的时间
  • date() // 提取日期或日期/时间表达式的日期部分
  • extract() // 返回日期/时间的单独部分
  • date_add() // 向日期添加指定的时间间隔
  • date_sub() // 从日期减去指定的时间间隔
  • datediff() // 返回两个日期之间的天数
  • date_format() // 用不同的格式显示日期/时间

date数据类型

date YYYY-MM-DD datetime YYYY-MM-DD HH:MM:SS timestamp YYYY-MM-DD HH:MM:SS year YYYY/YY

extract()函数用于返回日期/时间的单独部分,比如年,月,日,小时,分钟.

select extract(YEAR from orderDate) as orderyear,
extract(MONTH from orderDate) as orderMonth,
extract(DAY from orderDate) as orderDay,
from orders
where orderId = 1

date_add()函数用于向日期添加指定的时间间隔

select orderId, DATE_ADD(orderDate, INTERVAL 30 DAY) as orderPayDate
from orders

NULL值

NULL值代表遗漏的未知数据

默认情况下,表的列可以存放NULL值

select id from Persons
where name is NULL

select id from Persons
where name is NOT NULL

SQL函数

聚合函数

  • avg() // 返回平均值
  • count() // 返回行数
  • first() // 返回第一个记录的值
  • last() // 返回最后一个记录的值
  • max() // 返回最大值
  • min() // 返回最小值
  • sum() // 返回总和

标题函数

  • ucase() // 将某个字段转换为大写
  • lcase() // 将某个字段转换为小写
  • mid() // 从某个文本字段提取字符(select mid(column_name, start, [length]) from table_name)
  • SubString(字段, 1, end) // 从某个文本字段提取字符
  • len() // 返回某个文本字段的长度
  • round() // 对某个数值字段进行指定小数位数的四舍五入
  • now() // 返回当前的系统日期和时间
  • format() // 格式化某个字段的显示方式

avg函数

select avg(column_name) as avg_name from table_name

count函数

select count(column_name) from table_name
select count(distinct name) as count_name from table_name

first函数

select column_name from table_name
order by column_name asc
limit 1

last函数

select column_name from table_name
order by column_name desc
limit 1

max函数

select max(id) as max_id from table_name

min函数

select min(id) as min_id from table_name

sum函数

select sum(id) as sum_id from table_name

group by语句

group by语句用于结合聚合函数,根据一个或多个列对结果集进行分组.

// 分组统计
select count(id), type from table_name
group by type

having子句

在sql中增加having子句原因是,where关键字无法与聚合函数一起使用.

having子句可以让我们筛选分组后的各组数据.

select column_name, aggregate_function(column_name)
from table_name
where column_name operator value
group by column_name
having aggregate_function(column_name) operator value

// 查找总访问量大于200的网站
select webSites.name, webSites.url, sum(access_log.count) as nums 
from (access_log inner join webSites
on access_log.site_id = webSites.id
group by webSites.name
having sum(access_log.count) > 200

ucase函数

ucase函数把字段的值转换为大写

select ucase(name) as site_title, url
from webSites

lcase函数

lcase函数把字段的值转换为小写.

select lcase(name) as site_title, url
from webSites

mid函数

mid函数用于从文本字段中提取字符.

select mid(name, 1, 4) as shortTitle
from webSites

length函数

length函数返回文本字段中值的长度.

select name, length(url) as lenUrl
from webSites

round函数

round函数用于把数值字段舍入为指定的小数位数.

select round(column_name, decimals) from table_name

now函数

now函数返回当前系统的日期和时间.

select name, url, now() as tt
from webSites

format函数

format函数用于对字段的显示进行格式化.

select name, url, date_format(now(), '%Y-%m-%d') as tt
from webSites

mysql优化

  • 避免子查询。
  • 有顺序的读取。
  • 避免重复的读写。
  • 设置自增id。
  • 避免使用SELECT *之类的语句。
  • 合理设置字段类型。
  • 读写分离。
  • 分区、分表。
  • 建立合适的索引。
  • 避免使用耗费资源的操作null。
  • 不要使用rand()。
  • 尽量不要使用OR查询。
  • 指定要查询的记录条数limit n。
  • 开启查询缓存。
  • 前期对数据进行合理的评估，进行分区、分表。
  • 避免使用%前缀的模糊查询。
  • 避免使用mysql自带函数。
  • 使用多从解决查询慢的问题。

MySQL性能优化的最佳20+条经验

使用查询缓存优化你的查询

// 查询缓存不开启
$r = mysql_query("SELECT username FROM user WHERE signup_date >= CURDATE()");
// 开启查询缓存
$today = date("Y-m-d");
$r = mysql_query("SELECT username FROM user WHERE signup_date >= '$today'");

上面两条SQL语句的差别就是 CURDATE() ，MySQL的查询缓存对这个函数不起作用。 所以，像 NOW() 和 RAND() 或是其它的诸如此类的SQL函数都不会开启查询缓存， 因为这些函数的返回是会不定的易变的。

EXPLAIN 你的 SELECT 查询

使用 EXPLAIN 关键字可以让你知道MySQL是如何处理你的SQL语句的。 这可以帮你分析你的查询语句或是表结构的性能瓶颈。

我们可以看到，前一个结果显示搜索了 7883 行，而后一个只是搜索了两个表的 9 和 16 行。 查看rows列可以让我们找到潜在的性能问题。

当只要一行数据时使用 LIMIT 1

当你查询表的有些时候，你已经知道结果只会有一条结果,在这种情况下，加上 LIMIT 1 可以增加性能。 这样一样，MySQL数据库引擎会在找到一条数据后停止搜索，而不是继续往后查找下一条符合记录的数据。

// 没有效率的：
$r = mysql_query("SELECT * FROM user WHERE country = 'China'");
if (mysql_num_rows($r) > 0) {
    // ...
}
// 有效率的：
$r = mysql_query("SELECT 1 FROM user WHERE country = 'China' LIMIT 1");
if (mysql_num_rows($r) > 0) {
    // ...
}

为搜索字段建索引

索引并不一定就是给主键或是唯一的字段。 如果在你的表中，有某个字段你总要会经常用来做搜索，那么，请为其建立索引吧。

另外，你应该也需要知道什么样的搜索是不能使用正常的索引的。 例如，当你需要在一篇大的文章中搜索一个词时，如： “WHERE post_content LIKE ‘%apple%’”， 索引可能是没有意义的。你可能需要使用MySQL全文索引 或是自己做一个索引（比如说：搜索关键词或是Tag什么的）

在Join表的时候使用相当类型，并将其索引

如果你的应用程序有很多 JOIN 查询，你应该确认两个表中Join的字段是被建过索引的。 这样，MySQL内部会启动为你优化Join的SQL语句的机制。 而且，这些被用来Join的字段，应该是相同的类型的。

// 在state中查找company
$r = mysql_query("SELECT company_name FROM users
    LEFT JOIN companies ON (users.state = companies.state)
    WHERE users.id = $user_id");
// 两个 state 字段应该是被建过索引的，而且应该是相同的类型，相同的字符集。

千万不要 ORDER BY RAND()

想打乱返回的数据行？随机挑一个数据？真不知道谁发明了这种用法，但很多新手很喜欢这样用。 但你确不了解这样做有多么可怕的性能问题。

如果你真的想把返回的数据行打乱了，你有N种方法可以达到这个目的。 这样使用只让你的数据库的性能呈指数级的下降。 这里的问题是：MySQL会不得 不去执行RAND()函数（很耗CPU时间）， 而且这是为了每一行记录去记行，然后再对其排序。就算是你用了Limit 1也无济于事（因为要排序）

// 千万不要这样做：
$r = mysql_query("SELECT username FROM user ORDER BY RAND() LIMIT 1");
// 这要会更好：
$r = mysql_query("SELECT count(*) FROM user");
$d = mysql_fetch_row($r);
$rand = mt_rand(0,$d[0] - 1);
$r = mysql_query("SELECT username FROM user LIMIT $rand, 1");

避免 SELECT *

从数据库里读出越多的数据，那么查询就会变得越慢。 并且，如果你的数据库服务器和WEB服务器是两台独立的服务器的话，这还会增加网络传输的负载。

所以，你应该养成一个需要什么就取什么的好的习惯。

// 不推荐
$r = mysql_query("SELECT * FROM user WHERE user_id = 1");
$d = mysql_fetch_assoc($r);
echo "Welcome {$d['username']}";
// 推荐
$r = mysql_query("SELECT username FROM user WHERE user_id = 1");
$d = mysql_fetch_assoc($r);
echo "Welcome {$d['username']}";

永远为每张表设置一个ID

我们应该为数据库里的每张表都设置一个ID做为其主键， 而且最好的是一个INT型的（推荐使用UNSIGNED），并设置上自动增加的AUTO_INCREMENT标志。

就算是你 users 表有一个主键叫 “email”的字段，你也别让它成为主键。使用 VARCHAR 类型来当主键会使用得性能下降。 另外，在你的程序中，你应该使用表的ID来构造你的数据结构。

而且，在MySQL数据引擎下，还有一些操作需要使用主键，在这些情况下，主键的性能和设置变得非常重要，比如，集群，分区……

在这里，只有一个情况是例外，那就是“关联表”的“外键”，也就是说，这个表的主键，通过若干个别的表的主键构成。 我们把这个情况叫做“外键”。比 如：有一个“学生表”有学生的ID，有一个“课程表”有课程ID， 那么，“成绩表”就是“关联表”了，其关联了学生表和课程表，在成绩表中，学生ID和课 程ID叫“外键”其共同组成主键。

使用 ENUM 而不是 VARCHAR

ENUM 类型是非常快和紧凑的。在实际上，其保存的是 TINYINT，但其外表上显示为字符串。 这样一来，用这个字段来做一些选项列表变得相当的完美。

拆分大的 DELETE 或 INSERT 语句

如果你有一个大的处理，你定你一定把其拆分，使用 LIMIT 条件是一个好的方法。

while (1) {
    //每次只做1000条
    mysql_query("DELETE FROM logs WHERE log_date <= '2009-11-01' LIMIT 1000");
    if (mysql_affected_rows() == 0) {
        // 没得可删了，退出！
        break;
    }
    // 每次都要休息一会儿
    usleep(50000);
}

mysql如何用order by 自定义排序

idname
1stan
2kyle
3kenny
4cartman
SELECT * FROM mytable WHERE id IN (3,2,1,4) ORDER BY FIELD(id,3,2,1,4)
idname
3kenny
2kyle
1stan
4cartman

数据库事务

数据库事务是数据库管理系统执行过程中的一个逻辑单位，由一个有限的数据库操作序列构成。

事务的目的、作用

  • 为数据库操作序列提供一个从失败中恢复到正常状态的方法，即使异常仍能保持一致性的方法。
  • 当多个应用程序在并发访问数据时，可以在这些应用程序之间提供一个隔离方法，以防止彼此的操作互相干扰。

数据库事务的四个特性(ACID)

  • 原子性(Atomicity):事务作为一个整体被执行，包含在其中的对数据库的操作要么全部被执行，要么都不执行。
  • 一致性(Consistency):事务应确保数据库的状态从一个一致状态转变为另一个一致状态。 一致状态的含义是数据库中的数据应满足完整性约束。
  • 隔离性(Isolation):多个事务并发执行时，一个事务的执行不应影响其他事务的执行。
  • 持久性(Durability):已被提交的事务对数据库的修改应该永久保存在数据库中。

数据库主从分离

数据库的读写分离的好处

  • 将读操作和写操作分离到不同的数据库上，避免主服务器出现性能瓶颈；
  • 主服务器进行写操作时，不影响查询应用服务器的查询性能，降低阴塞，提高并发；
  • 数据拥有多个容灾副本，提高数据安全性，同时当主服务器故障时，可立即切换到其他服务器，提高系统可用性；

读写分离的基本原理

主数据库处理事务性增、改、删操作(INSERT、UPDATE、DELETE)操作，而从数据库处理SELECT查询操作。 数据库复制被用来把事务性操作导致的变更同步到其他从数据库。

以SQL为例，主库同写数据、读数据。读库仅负责读数据。每次有写库操作，同步更新到读库。 写库就一个，读库可以有多个，采用日志同步的方式实现主库和多个读库的数据同步。

上次编辑于:
贡献者: Moments
Copyright © 2023 Moments