常用SQL语句

1.数据库操作

1）建立数据库

CREATE DATABASE test;

2）显示数据库

SHOW DATABASES;

3）选择数据库

USEE test; 

4）删除数据库

DROP DATABASE test;

---

2.数据表操作

1）建立数据表

CREATE TABLE IF NOT EXISTS `test`(

`id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY ,

`title` VARCHAR(100) NOT NULL ,

`author` VARCHAR(40) NOT NULL ,

`content` VARCHAR(500) NOT NULL ,

`datetime` DATETIME DEFAULT CURRENT_TIMESTAMP

)ENGINE = InnoDB DEFAULT CHARSET = utf8;

2）显示数据表

DESCRIBE(DESC) test;

3）改名数据表

RENAME TABLE test TO test1;

4）删除数据表

DROP TABLE test;

5）修改字段

ALTER TABLE test ADD c1 INT;

ALTER TABLE test ADD C1 INT FIRST;

ALTER TABLE test ADD C1 INT AFTER id;

ALTER TABLE test DROP c1;

ALTER TABLE test CHANGE c1 c2 INT;

ALTER TABLE test MODIFY title CHAR(100);

---

3.数据操作

1）插入数据

INSERT INTO test (title , author , content) VALUES('a1' , 'b1' , 'c1') , ('a2' , 'b2' , 'c2') ;

2）查看数据

SELECT title , author FROM test ;

3）更新数据

UPDATE test SET title = 'a3' WHERE Id = 1 ;

4）删除数据

DELETE FROM test WHERE Id = 1 ;

5）重复数据

SELECT COUNT(*) as repetitions , title , author FROM test GROUP BY title , author HAVING repetitions > 1;

6）去重数据

SELECT DISTINCT title , author FROM test ;