MySQL笔记

字数 1664 · 2018-03-26

#sql

CRUD

CRUD 分别代表CREATE，READ，UPDATE，DELETE。

1
2
3
4

insert into <table_name> (column1 ... ) values (value1 ...)
select * from <table_name> [where ...]
update <table_name> set <column_name>=<value> [where ...]
delete from <table_name> [where ...]

JOIN

https://www.w3schools.com/sql/sql_join.asp

LEFT JOIN

Return all rows from the left table, and the matched rows from the right table

1
2
3
4
5
6

select c1.cat_name l1,c2.cat_name l2,c3.cat_name l3,c3.cat_id id 
from np_goods_category c1 
    left join np_goods_category c2 on c2.cat_parent_id = c1.cat_id
    left join np_goods_category c3 on c3.cat_parent_id = c2.cat_id
where c1.cat_grade = 1 and c1.cat_delflag = '0' and c2.cat_delflag = '0' and c3.cat_delflag = '0'
order by c1.cat_id,c2.cat_id,c3.cat_id

RIGHT JOIN

Return all rows from the right table, and the matched rows from the left table

UNION & UNION ALL

  • UNION performs a DISTINCT on the result set, eliminating any duplicate rows.
  • UNION ALL does not remove duplicates, and it therefore faster than UNION.

表操作

1
2

-- 对表添加字段
alter table <table_name> add <column_name> <datatype>

1
2

-- 删除表
drop table <table_name>

1
2

-- 清空表
truncate table <table_name>

修改auto_increment值

1

alter table <table_name> AUTO_INCREMENT = <value>

1
2

-- 获取全部表名
show tables

索引

1
2
3
4

-- 查询指定表的索引
show index from <table_name>
-- 添加索引
create index <index_name> on <table_name>(column_name)

MySql Infomation Function

LAST_INSERT_ID()

1
2

-- 查询最近插入的自增ID
select LAST_INSERT_ID();

1
2

-- 替换文本
update <table> set <field> = replace(<field>,<origin>,<target>)

表信息查询

1
2
3
4
5
6

-- 查询全部表状态
SHOW TABLE STATUS;
-- 查询表信息，字段，字段类型，是否可以为空，主键信息，默认值等。
DESCRIBE <table_name>
-- 查询详细的单表信息，包括除DESCRIBE可以显示的信息外，还包括字符集，权限信息和备注。
SHOW FULL COLUMNS FROM <table_name>

数据类型

整型

MySQL数据类型 含义（有符号）
tinyint(m) 1个字节 范围(-128~127)
smallint(m) 2个字节 范围(-32768~32767)
mediumint(m) 3个字节 范围(-8388608~8388607)
int(m) 4个字节 范围(-2147483648~2147483647)
bigint(m) 8个字节 范围(+-9.22*10的18次方)

取值范围如果加了unsigned，则最大值翻倍，如tinyint unsigned的取值范围为(0~256)。
int(m)里的m是表示SELECT查询结果集中的显示宽度，并不影响实际的取值范围，没有影响到显示的宽度，不知道这个m有什么用。

浮点型

(float和double)

MySQL数据类型 含义
float(m,d) 单精度浮点型 8位精度(4字节) m总个数，d小数位
double(m,d) 双精度浮点型 16位精度(8字节) m总个数，d小数位

设一个字段定义为float(5,3)，如果插入一个数123.45678,实际数据库里存的是123.457，但总个数还以实际为准，即6位。

定点数

浮点型在数据库中存放的是近似值，而定点类型在数据库中存放的是精确值。
decimal(m,d) 参数m<65 是总个数，d<30且 d<m是小数位。

字符串

(char,varchar,_text)

MySQL数据类型 含义
char(n) 固定长度，最多255个字符
varchar(n) 固定长度，最多65535个字符
tinytext 可变长度，最多255个字符
text 可变长度，最多65535个字符
mediumtext 可变长度，最多2的24次方-1个字符
longtext 可变长度，最多2的32次方-1个字符

char和varchar：
1.char(n) 若存入字符数小于n，则以空格补于其后，查询之时再将空格去掉。所以char类型存储的字符串末尾不能有空格，varchar不限于此。
2.char(n) 固定长度，char(4)不管是存入几个字符，都将占用4个字节，varchar是存入的实际字符数+1个字节（n<=255）或2个字节(n>255)，所以varchar(4),存入3个字符将占用4个字节。
3.char类型的字符串检索速度要比varchar类型的快。

varchar和text：
1.varchar可指定n，text不能指定，内部存储varchar是存入的实际字符数+1个字节（n<=255）或2个字节(n>255)，text是实际字符数+2个字节。
2.text类型不能有默认值。
3.varchar可直接创建索引，text创建索引要指定前多少个字符。varchar查询速度快于text,在都创建索引的情况下，text的索引似乎不起作用。

二进制数据

(_Blob)

  1. _BLOB 和 _TEXT 存储方式不同，_TEXT 以文本方式存储，英文存储区分大小写，而 _Blob 是以二进制方式存储，不分大小写。
  2. _BLOB 存储的数据只能整体读出。
  3. _TEXT 可以指定字符集，_BLOB 不用指定字符集。

日期时间类型

MySQL数据类型 含义
date 日期 ‘2008-12-2’
time 时间 ‘12:25:36’
datetime 日期时间 ‘2008-12-2 22:06:44’
timestamp 时间戳

DATE 和 DATETIME 支持的时间范围是 1000-01-01 ~ 9999-12-31
TIMESTAMP 支持的范围则是 1970-01-01 ~ 2038-01-19

TIMESTAMP 受时区的影响

timestamp的自动初始化及自动更新

使用 DEFAULT CURRENT_TIMESTAMP 及 ON UPDATE CURRENT_TIMESTAMP 子句来实现。

1
2
3
4
5

CREATE TABLE `bookmark` (
  -- ...其他字段
  `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
)

时间的比较

1

select * from table1 where create_time > '2017-7-10'

timestamp的自动初始化及自动更新

数据类型的属性

MySQL关键字 含义
NULL 数据列可包含NULL值
NOT NULL 数据列不允许包含NULL值
DEFAULT 默认值
PRIMARY KEY 主键
AUTO_INCREMENT 自动递增，适用于整数类型
UNSIGNED 无符号
CHARACTER SET name 指定一个字符集

常用函数

字符串操作

连接字符串

1
2
3
4
5

-- ab
select concat('a','b');

-- a,b,c
select concat_ws(',','a','b','c');

截取字符串

1
2

-- 123456
select select substr('a123456',2);

列表取值
ELT(index, string1, string2, string3,…)

1
2
3
4

-- 取第二个值
select elt(2,'Amy','Lucy','Larry','John','Jimmy','Lily'); -- Lucy
-- 随机取一个
select elt(0.5 + rand()*6 ,'Amy','Lucy','Larry','John','Jimmy','Lily');

数学运算

随机数

1
2

-- 0.47247766002312086
select rand();

四舍五入

1
2

-- ROUND(number, 保留小数位数)
select round(30.55,1); -- 30.6

浮点转整型

1
2
3
4
5

-- 返回一个小于等于参数的最大整数
select floor(30.75); -- 30
-- 四舍五入
select round(30.55); -- 31
select round(30.45); -- 30

Date操作

DATE_ADD

1
2

-- 加2天
select DATE_ADD(now(),INTERVAL 2 DAY);

存储过程

MySQL存储过程学习笔记

日志查看

MySQL日志主要包括：错误日志、查询日志、慢查询日志、事务日志和二进制日志。

1
2

-- 可以使用这个SQL查看日志相关的配合，比如日志文件目录。
SHOW  GLOBAL VARIABLES LIKE '%log%';

general_log - 查询日志，默认为关闭

慢查询日志

超过 long_query_time 时长的查询会被写入日志。
相关的配置有 slow_query_log slow_query_log_file

可以使用 set global slow_query_log=1 配置，也可以在 my.cnf 中配置：

1
2
3

[mysqld]
slow_query_log=1
log-slow-queries [=  DIR/[filename] ]

下面是一条慢查询日志，日志会记录sql发起的时间，用户，IP，查询时间，SQL语句等信息。

1
2
3
4
5

# Time: 180402 18:59:42
# User@Host: root[root] @  [192.168.10.56]  Id:   945
# Query_time: 7.041423  Lock_time: 0.000047 Rows_sent: 1  Rows_examined: 1109532
SET timestamp=1522666782;
call sync_add_state;

二进制日志

二进制日志（Binary Log）也叫作变更日志，主要用于记录数据库变动的事件，比如新建表或是改动表数据，并且会记录语句发生时间、执行时长、操作的数据等等。

binlog_format 用于配置二进制日志的格式。二进制日志的格式有三种：

  • STATEMENT - 记录语句（默认格式）
  • ROW - 记录表的行变化，可以确保数据准确
  • MIXED - 默认使用STATEMENT模式，在特定情况下会自动切换到ROW模式

一些查看二进制日志的SQL语句

1
2
3
4

-- 查看当前服务器的二进制日志
show binary logs; 
-- 查看二进制日志内容
show binlog events[IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]

也可以使用mysqlbinlog来查看二进制日志。

1

mysqlbinlog mysql-bin.000140 | less

二进制日志不能直接删除，可以使用下面的命令进行操作。

另删除前要做好备份。

1

PURGE { BINARY | MASTER } LOGS {TO 'log_name' | BEFORE datetime_expr }

mysql日志详细解析 - http://blog.51cto.com/

Privileges

1

GRANT permission_type ON database.table TO 'username'@'localhost';

TestDB

https://github.com/datacharmer/test_db

employees schema