SQL

来自刘东华的维基
跳转至: 导航搜索

概述

SQL.

语法

INSERT

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 some_column=some_value;

DELETE

DELETE FROM TABLE_NAME
WHERE some_column=some_value;

DISTINCT

SELECT DISTINCT column_name,column_name
FROM TABLE_NAME;

VIEW

CREATE VIEW view_name AS
SELECT column_name(s)
FROM TABLE_NAME
WHERE condition

ALTER

mysql reference: http://dev.mysql.com/doc/refman/5.7/en/alter-table.html

-- example: change column name and type 
ALTER TABLE wp_dash_staff_info CHANGE id id BIGINT;
 
-- drop column
ALTER   TABLE   <table_name>  DROP  COLUMN   <column_name>

INDEX

CREATE [UNIQUE] INDEX index_name
ON TABLE_NAME (column_name)
 
SHOW {INDEX | INDEXES | KEYS}
    {FROM | IN} tbl_name
    [{FROM | IN} db_name]
    [WHERE expr]
 
DROP INDEX index_name ON tbl_name
    [algorithm_option | lock_option] ...

MySQL/MariaDB

官方参考:https://dev.mysql.com/doc/

用法

-- Create and drop database
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
    [create_specification] ...
 
DROP {DATABASE | SCHEMA} [IF EXISTS] db_name
 
-- 查看表状态
SHOW TABLE STATUS FROM your_database LIKE 'your_table';
 
-- 执行SQL脚本文件,https://dev.mysql.com/doc/refman/5.7/en/mysql-batch-commands.html
mysql> SOURCE file.sql

备份

# 将MySQL数据库压缩备份
mysqldump -hhostname -uusername -ppassword databasename | gzip > backupfile.sql.gz
 
# 还原压缩的MySQL数据库
gunzip < backupfile.sql.gz | mysql -uusername -ppassword databasename

配置

  • 修改数据存放目录

参考:http://qtlinux.blog.51cto.com/3052744/1353944
错误:mysqld: Can't find file: './mysql/plugin.frm(权限导致,对比默认目录设置)

常见问题

  • Specified key was too long; max key length is 767 bytes