1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
数据库支持emoji表情：
1. mysql版本5.5.3以上
2. 存储emoji表情的字段选择utf8mb4_general_ci
3. 数据库连接也需要改为utf8mb4
mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';  
+--------------------------+--------------------+  
| Variable_name            | Value              |  
+--------------------------+--------------------+  
| character_set_client     | utf8mb4            |  
| character_set_connection | utf8mb4            |  
| character_set_database   | utf8mb4            |  
| character_set_filesystem | binary             |  
| character_set_results    | utf8mb4            |  
| character_set_server     | utf8mb4            |  
| character_set_system     | utf8               |  
| collation_connection     | utf8mb4_unicode_ci |  
| collation_database       | utf8mb4_unicode_ci |  
| collation_server         | utf8mb4_unicode_ci |  
+--------------------------+--------------------+  
 rows in set (0.00 sec)

多级评论：
嵌套评论的一个数据库设计技巧
Django实现论坛梯形多级评论楼视频课程

SQL全称Structured Query Language(结构化查询语言)。SQL 是关系型数据库系统（Relation Database System）的标准语言。微软的 SQL Server 使用的是 T-SQL，Oracle 使用的是 PL/SQL，微软的 Access 中的 SQL 叫做 JET SQL （本地格式）等等。
SQL流程包括查询调度器（Query Dispatcher）、优化引擎（Optimization Engines）、经典查询引擎（Classic Query Engine）和 SQL 查询引擎（SQL Query Engine）等等。经典查询引擎用于处理所有的非 SQL 查询，而 SQL 查询引擎则不处理逻辑文件。

SQL 命令
SQL不区分大小写。用于与关系型数据库交互的标准 SQL 命令有 CREATE、SELECT、INSERT、UPDATE、DELETE 和 DROP，这些命令按用途分成如下几组：

数据定义语言

命令 描述
CREATE 创建新的表、视图或者其他数据库中的对象
ALTER 修改现存数据库对象，比如一张表
DROP 删除表、视图或者数据库中的其他对象
数据操纵语言

命令 描述
SELECT 从一张或者多张表中检索特定的数据
INSERT 创建一条新记录
UPDATE 修改记录
DELETE 删除记录
数据控制语言

命令 描述
GRANT 赋予用户特权
REVOKE 收回赋予用户的特权

注意：NULL 值是表中以空白形式出现的值，表示该记录在此字段处没有设值。

SQL 约束：
NOT NULL 约束：保证列中数据不能有 NULL 值
DEFAULT 约束：提供该列数据未指定时所采用的默认值
UNIQUE 约束：保证列中的所有数据各不相同
主键：唯一标识数据表中的行/记录
外键：唯一标识其他表中的一条行/记录
CHECK 约束：此约束保证列中的所有值满足某一条件
索引：用于在数据库中快速创建或检索数据

常用命令

  • mysql -uroot -p回车后输入密码 打开mysql命令行工具
  • show databases; 显示所有数据库
  • use mysql; 使用mysql这个数据库
  • create database Demo; 创建Demo数据库
  • use Demo; show tables; 显示所有数据表
  • create table( id INT NOT NULL PRIMARY KEY, name VARCHAR(20) NOT NULL); 创建表，注意括号
  • insert into test (id,name) values(0,”张三”); 插入数据
  • desc test; 显示表结构
  • alter table test modify id int(11) default 0; 设置test表id字段默认值为0，注意alter(不要写为alert)
  • alter table test alter column id drop default; 删除default约束
  • alter table test modify id int(11) not null unique; 标识字段的值是独一无二的
  • alter table test drop primary key; 删除主键
  • alter table test add primary key (id); 添加id为主键
  • update test set name=”haha” where id=0; 更新数据
  • delete from test where id=1; 删除一条记录
  • select * from test where name like ‘h%’; like匹配记录，百分号（%）匹配0~多个字符，下划线(_)匹配一个字符
  • SELECT NAME, SUM(SALARY) FROM CUSTOMERS GROUP BY NAME; 将name和salary划分为一组显示TODO
  • select distinct name from test; 去除所有重复记录，只返回唯一项
  • show create table test; 显示表结构文件
  • show variables like ‘character%’; 匹配显示表的各种字符编码

子查询：SELECT column_name [, column_name ] FROM table1 [, table2 ] WHERE column_name OPERATOR (SELECT column_name [, column_name ] FROM table1 [, table2 ] [WHERE])；

连接

  1. 左连接left join，返回左表中的所有记录，加上右表中匹配到的记录，或者是 NULL
  2. 内连接inner join，返回满足连接的所有记录
  3. 右连接right join，右连接会返回右表中的所有记录，加上左表中匹配到的记录，或者是 NULL
  4. 全连接FULL JOIN，将左连接和右连接的结果组合到一起
  5. 笛卡尔连接或者交叉连接返回两个或者更多的连接表中记录的笛卡尔乘积。SELECT ID, NAME, AMOUNT, DATEFROM CUSTOMERS, ORDERS;
    mysql不支持全连接，可以使用UNION ALL子句来将左连接和右连接结果组合在一起

索引

可以使用别名（Alias）来对数据表或者列进行临时命名，SELECT C.ID, C.NAME, C.AGE, O.AMOUNT FROM CUSTOMERS AS C, ORDERS AS O WHERE C.ID = O.CUSTOMER_ID;
索引是一种特殊的查询表，可以被数据库搜索引擎用来加速数据的检索。CREATE INDEX index_name ON table_name;
小的数据表不应当使用索引；
需要频繁进行大批量的更新或者插入操作的表；
如果列中包含大数或者 NULL 值，不宜创建索引；
频繁操作的列不宜创建索引。

RUNCATE TABLE table_name; 删除现有数据表中的所有数据。

视图

视图无非就是存储在数据库中并具有名字的 SQL 语句，是一种虚拟的表

  • 以用户或者某些类型的用户感觉自然或者直观的方式来组织数据；
  • 限制对数据的访问，从而使得用户仅能够看到或者修改（某些情况下）他们需要的数据；
  • 从多个表中汇总数据，以产生报表。
  1. 创建：CREATE VIEW view_name AS SELECT column1, column2….. FROM table_name WHERE [condition];
    WHERE age IS NOT NULL WITH CHECK OPTION; 这里WITH CHECK OPTION 使得视图拒绝任何 AGE 字段为 NULL 的条目。
  2. 更新：UPDATE CUSTOMERS_VIEW SET AGE = 35 WHERE name=’Ramesh’;
  3. 删除视图：DROP VIEW view_name;

having子句

SELECT column1, column2
FROM table1, table2
WHERE [ conditions ]
GROUP BY column1, column2
HAVING [ conditions ]
ORDER BY column1, column2；
HAVING的位置必须是固定的。

SELECT ID, NAME，AGE FROM CUSTOMERS GROUP BY age HAVING COUNT(age) >= 2;

事务

  1. 提交更改：
    DELETE FROM CUSTOMERS WHERE AGE = 25;
    COMMIT;
  2. 回滚：
    DELETE FROM CUSTOMERS WHERE AGE = 25;
    ROLLBACK;
  3. 保存可以回滚的状态点，而不是将整个事务都撤销：
    SAVEPOINT SAVEPOINT_NAME;
    SAVEPOINT SAVEPOINT_NAME;
  4. 删除状态点：RELEASE SAVEPOINT SAVEPOINT_NAME;
  5. 初始化数据库事务：SET TRANSACTION [ READ WRITE | READ ONLY ];

sql临时表

CREATE TEMPORARY TABLE SALESSUMMARY (…);
如果在 PHP 脚本中操作数据库，那么临时表将在脚本执行完毕时被自动销毁。如果是通过 MySQL 的客户端程序连接到 MySQL 数据库服务器的，那么临时表将会存在到关闭客户端或者手动将其删除。

sql注入

php字符转义：

1
2
3
4
5
6
if (get_magic_quotes_gpc())
{
  $name = stripslashes($name);
}
$name = mysql_real_escape_string($name);
mysql_query("SELECT * FROM CUSTOMERS WHERE name='{$name}'");

LIKE 困境：(将用户提供的 ‘%’ 和 _ 转换为字面值)

1
2
3
$sub = addcslashes(mysql_real_escape_string("%str"), "%_");
mysql_query("SELECT * FROM messages
             WHERE subject LIKE '{$sub}%'");

SQL常用函数

count(统计记录总数),max,min,avg,sum,sqrt,rand(产生0~1随机数),concat(连接两个字符串)