mysql基本操作

分享一些mysql常用的增删改查语句：

CREATE DATABASE runoob    #创建数据库
DROP DATABASE 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` )
)    #创建数据表
SELECT * FROM runoob_tbl    #查询数据表
SELECT * FROM tcount_tbl
DROP TABLE runoob_tbl    #删除数据表
INSERT INTO runoob_tbl(runoob_title,runoob_author,submission_date) VALUES("学习PHP","菜鸟教程",NOW())    #插入数据
INSERT INTO runoob_tbl(runoob_title,runoob_author,submission_date) VALUES("学习MYSQL","菜鸟教程",NOW())
INSERT INTO runoob_tbl(runoob_title,runoob_author,submission_date) VALUES("学习JAVA","菜鸟教程",NOW())
INSERT INTO runoob_tbl(runoob_title,runoob_author,submission_date) VALUES("学习PYTHON","菜鸟教程",'2018-08-17')
SELECT * FROM runoob_tbl WHERE runoob_title='学习PHP'    #WHERE语句
UPDATE runoob_tbl SET runoob_title='学习C' WHERE runoob_id=4    #更新数据
DELETE FROM runoob_tbl WHERE runoob_id=3    #删除数据
SELECT * FROM runoob_tbl WHERE runoob_title LIKE '%H%'     #like语句
SELECT * FROM runoob_tbl ORDER BY runoob_id DESC    #排序

SELECT * FROM tcount_tbl,runoob_tbl    #多表联合查询
SELECT * FROM runoob_tbl,tcount_tbl WHERE runoob_tbl.`runoob_author` = tcount_tbl.`runoob_author`    #多表联合查询
SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a INNER JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;    #内连接查询
SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a LEFT JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;    #左连接查询
SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a RIGHT JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;    #右连接查询

CREATE TABLE testalter_tbl(
i INT,
c CHAR(1)
)
SELECT * FROM testalter_tbl
DESC testalter_tbl
ALTER TABLE testalter_tbl DROP i    #删除列
ALTER TABLE testalter_tbl ADD i INT    #增加列
ALTER TABLE testalter_tbl DROP i
ALTER TABLE testalter_tbl ADD i INT FIRST    #指定新增列的位置
ALTER TABLE testalter_tbl DROP i;
ALTER TABLE testalter_tbl ADD i INT AFTER c;
ALTER TABLE testalter_tbl MODIFY c CHAR(10)    #修改列类型
ALTER TABLE testalter_tbl CHANGE i j INT    #修改列名和列类型
ALTER TABLE testalter_tbl MODIFY j INT NOT NULL DEFAULT 100    #修改列的约束
ALTER TABLE testalter_tbl ALTER j SET DEFAULT 1000    #修改字段的默认值
ALTER TABLE testalter_tbl ALTER j DROP DEFAULT;    #删除字段默认值
ALTER TABLE testalter_tbl RENAME TO alter_tbl    #修改表名