MYSQL Review

Mysql note
Docs :https://dev.mysql.com/doc/refman/5.7/en/date-calculations.html

获取数据库版本，当前日期
Select version(),current_date;


获取当前用户
Select user()


如果不想使用当前输入的sql，输入. \c




查看已有数据库 show databases
使用数据库 use database_name
数据库授权
GRANT ALL ON menagerie.* TO ‘mysql_user_name’@‘your_client_host’;

GBK: create database test2 DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;

UTF8: CREATE DATABASE test2 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci
建表 create tables table_Name()
查看有哪些表 show tables
查看表结构 describe table_name


数据导入 load data


向某一表插入数据


表中取出数据 Retrieving Information from a Table
SELECT what_to_select
FROM which_table
WHERE conditions_to_satisfy;

取出不重复的数据


删除表中数据 delete from table_name where


更新表中数据 update table_name set … where



修改表结构

Alter table table_name add column column_name …

排序。order by

逆序DESC

顺序 ASC

简称 as name

空 is not null / is null

时间计算
官方文档URL：https://dev.mysql.com/doc/refman/5.7/en/date-calculations.html

当前 日期 CURDATE()
计算时间差值 TIMESTAMPDIFF()


MONTH() returns a number between 1 and 12. And MOD(something,12) returns a number between 0 and 11. So the addition has to be after the MOD(), otherwise we would go from November (11) to January (1).

Pattern matching

use like or not like

% 模糊匹配

regexp or not regex

^ 匹配开头

To force a REGEXP comparison to be case-sensitive, use the BINARY keyword to make one of the strings a binary string. This query matches only lowercase b at the beginning of a name:


$ 匹配结尾


精确匹配包含字符



查询包含某一字符



聚合查询

count 技术原理：依据数据id计数


可用group by 对 结果进行分组
作用，可看出 某一名称对应的某一值对比
group by 隐患


Max 求最大


连接
内连接


show 语句
show … from table from db

Source filename
Executing SQL Statements from a Text File

去除重复列限制

1、通过natural join 去除所有重复
2、通过 using() 函数指定某一列不重复

using 等效

a LEFT JOIN b USING (c1, c2, c3)
a LEFT JOIN b ON a.c1 = b.c1 AND a.c2 = b.c2 AND a.c3 = b.c3



数据的导入导出

Server 端文件 路径
LOAD DATA INFILE ‘data.txt’ INTO TABLE db2.my_table;

Sql help
查看哪些函数
Help ‘functions’
查看语句用法 结构：help + 语句字符串 help ‘create table’

TRANSACTION

Four basic operation

BEGIN [WORK]
COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
SET autocommit = {0 | 1}

ACID：

原子性（Atomicity )、
一致性（Consistency )、
隔离性（Isolation) 、
持久性（Durability),

视图

语法
Create view as + query_result