基本语句
- 查看数据存放目录
SHOW VARIABLES LIKE 'datadir'
- 创建新表
CREATE TABLE new_table LIKE single_table
- 查看表结构
SHOW CREATE TABLE new_table
- 如果数据已经存在则忽略
insert ignore into table
- update语句中where是强制语句
UPDATE score SET score=44 WHERE number=20180103
- replace:如果包含主键并且存在,而删除再插入,否则是新增
REPLACE INTO single_table (id,key1,key2) VALUES(1,'a',220)
- 如果主键重复则进行更新操作
INSERT INTO single_table (id,key1,key2) VALUES(1,'a',220) ON DUPLICATE KEY UPDATE key3='888'
- order by 第二页
SELECT key1,key2,key3 FROM single_table ORDER BY 2 DESC
- avg
- 创建新用户
CREATE USER IF NOT EXISTS 'aaa'@'%'
IDENTIFIED WITH mysql_native_password
BY 'aaa'
WITH MAX_QUERIES_PER_HOUR 500
MAX_UPDATES_PER_HOUR 100;
- 另存为表
-- 不存在表
CREATE TABLE aaa AS SELECT * FROM single_table
-- 存在表
INSERT INTO aaa SELECT * FROM single_table
-- 复制表,包含索引
CREATE TABLE eee (LIKE single_table)
-- 存储json字符串
SELECT emp_no,details->'$.data.title.id' FROM emp_details
INSERT INTO emp_details
VALUES(3,'{
"status": "0000",
"message": "success",
"data": {
"title": {
"id": "001",
"name" : "白菜"
},
"content": [
{
"id": "001",
"value":"你好 白菜"
},
{
"id": "002",
"value":"你好 萝卜"
}
]
}
}');
