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 ;
