CREATE DATABASE 数据库名;
drop database <数据库名>;
use RUNOOB;
CREATE TABLE IF NOT EXISTS `runoob_tbl`(
`runoob_id` INT UNSIGNED AUTO_INCREMENT,
`runoob_title` VARCHAR(100) NOT NULL,
`runoob_author` VARCHAR(40) NOT NULL,
`submission_date` DATE,
PRIMARY KEY ( `runoob_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE table_name ;
INSERT INTO table_name ( field1, field2,...fieldN )
VALUES
( value1, value2,...valueN );
SELECT column_name,column_name
FROM table_name
[WHERE Clause]
[LIMIT N][ OFFSET M]
更新数据
UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]
DELETE FROM table_name [WHERE Clause]
SELECT field1, field2,...fieldN
FROM table_name
WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'
union
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION [ALL | DISTINCT]
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];
SELECT field1, field2,...fieldN FROM table_name1, table_name2...
ORDER BY field1 [ASC [DESC][默认 ASC]], [field2...] [ASC [DESC][默认 ASC]]
SELECT name FROM person_tbl WHERE name REGEXP '^st';
修改表
修改表名
ALTER TABLE testalter_tbl RENAME TO alter_tbl;
删除字段
ALTER TABLE testalter_tbl DROP i;
增加字段
ALTER TABLE testalter_tbl ADD i INT;
修改字段类型
ALTER TABLE testalter_tbl MODIFY c CHAR(10);
修改字段名
ALTER TABLE testalter_tbl CHANGE i j BIGINT;
修改字段默认值
ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;
删除默认值
ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;
查看数据表类型
SHOW TABLE STATUS LIKE 'testalter_tbl'\G
获取表的完整结构
SHOW CREATE TABLE runoob_tbl \G;
临时表
CREATE TEMPORARY TABLE SalesSummary
复制表
INSERT INTO clone_tbl (runoob_id,
-> runoob_title,
-> runoob_author,
-> submission_date)
-> SELECT runoob_id,runoob_title,
-> runoob_author,submission_date
-> FROM runoob_tbl;
导出
SELECT * FROM passwd INTO OUTFILE '/tmp/runoob.txt'
mysqldump -u root -p RUNOOB runoob_tbl > dump.txt
and or || not
