mysql

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
 
 
 
 
 
 
 
 
原文地址:https://www.cnblogs.com/luyingying/p/13260886.html