一、库操作
1、创建数据库
CREATE DATABASE dbname;CREATE DATABASE my_test;CREATE DATABASE IF NOT EXISTS dbname;CREATE DATABASE IF NOT EXISTS my_test;
2、查看数据库
SHOW DATABASES;SHOW CREATE DATABASE dbname;SHOW CREATE DATABASE my_test;
3、选择数据库
USE dbname;USE my_test;
4、删除数据库
DROP DATABASE dbname;DROP DATABASE my_test;
二、表操作
1、表(创建、修改、查看、删除)
CREATE TABLE table_name(column_name1 data_type(size),column_name2 data_type(size),column_name3 data_type(size),....);CREATE TABLE User(user_id varchar(255),user_name varchar(255),age int,address varchar(255));
CREATE TABLE table_new LIKE table_old;CREATE TABLE table_new AS SELECT column1,column2… FROM table_old WHERE 1=1;
ALTER TABLE old_table_name RENAME TO new_table_name;ALTER TABLE User RENAME TO new_User;
DESC table_name;DESC User;
SHOW CREATE TABLE table_name;SHOW CREATE TABLE User;
DROP TABLE table_name;DROP TABLE User;DROP TABLE IF EXISTS table_name;DROP TABLE IF EXISTS User;
2、列(添加、修改、删除)
ALTER TABLE table_name ADD column_name data_type(size);ALTER TABLE User ADD City varchar(255);
ALTER TABLE table_name DROP column_name;ALTER TABLE User DROP City;
ALTER TABLE table_name MODIFY column_name data_type(size);ALTER TABLE User MODIFY City varchar(512);
3、主键(添加、删除)
ALTER TABLE table_name ADD primary key(column_name);ALTER TABLE table_name ADD primary key(UserID);ALTER TABLE table_name DROP primary key(column_name);ALTER TABLE table_name DROP primary key(UserID);
4、索引(创建、删除)
CREATE INDEX index_name ON table_name(column_name);CREATE UNIQUE INDEX index_name ON table_name(column_name);CREATE INDEX index_name ON table_name(column_name1,column_name2,...);DROP INDEX index_name;
5、视图(创建、删除)
CREATE view viewname as select statementDROP view viewname
6、表数据插入(INSERT)、删除(DELETE)、修改(UPDATE)、查询(SELECT)
INSERT INTO table_nameVALUES (value1,value2,value3,...);INSERT INTO UserVALUES ('101','张三',20,'广东');INSERT INTO table_name (column1,column2,column3,...)VALUES (value1,value2,value3,...);INSERT INTO User (user_id, user_name, age)VALUES ('101','张三',20);
DELETE FROM table_nameWHERE condition;DELETE FROM UserWHERE user_id='101';
UPDATE table_nameSET column1 = value1, column2 = value2, ...WHERE condition;UPDATE UserSET age = 21, address='北京'WHERE user_id='101';
SELECT column1, column2, ... FROM table_name;SELECT user_id, user_nameFROM User;SELECT * FROM table_name;SELECT *FROM User;
SELECT DISTINCT column1, column2, ... FROM table_name;SELECT DISTINCT user_id, user_nameFROM User;
SELECT column1, column2, ...FROM table_nameWHERE condition;SELECT user_id, user_nameFROM UserWHERE user_name='张三';
SELECT * FROM UserWHERE address='广东省'AND age > 20;SELECT * FROM UserWHERE address='广东省'OR address='北京';