20180925-常用的MySQL语句

注意事项

• 行末加分号

• 连接：mysql -u root -p
• 退出：exit
• 选择某个数据库：use XX;
• 创建表CREATE TABLE table_name (column_name column type);

CREATE TABLE tbl(
   id INT NOT NULL AUTO_INCREMENT,
   title VARCHAR(100) NOT NULL,
   author VARCHAR(40) NOT NULL,
   submission_date DATE,
   PRIMARY KEY ( id )
);

• 删除表
  DROP TABLE tbl;
• 插入数据

INSERT INTO tbl (title, author, submission_date)
VALUES
("Learn JAVA", "John", NOW());

INSERT INTO tbl (title, author, submission_date)
VALUES
("Learn Python", "Poul", "2018-01-01");

• 查询数据
  SELECT field1, field2,...fieldN table_name1, table_name2... [WHERE Clause] [OFFSET M ][LIMIT N]
• 查询所有SELECT * FROM tbl;
• 查询部分字段

SELECT title, author FROM tbl;

• 条件查询
  SELECT field1, field2,...fieldN table_name1, table_name2... [WHERE condition1 [AND [OR]] condition2.....

SELECT * FROM tbl WHERE author="John";

• 更新查询
  UPDATE table_name SET field1=new-value1, field2=new-value2 [WHERE Clause]

UPDATE tbl SET title="Learning Oracle" where id=2;

• 删除查询
  DELETE FROM table_name [WHERE Clause]

DELETE FROM tbl WHERE id=1;

• Like Clause
  SELECT field1, field2,...fieldN table_name1, table_name2... WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'

SELECT * FROM tbl WHERE author LIKE '%ul';

• 排序结果
  SELECT field1, field2,...fieldN table_name1, table_name2... ORDER BY field1, [field2...] [ASC [DESC]]

SELECT * FROM tbl ORDER BY author ASC;