MySQL SQL基本操作

CREATE DATABASE [IF NOT EXISTS] db_name [库选项]

-- 创建一个名称为db的数据库，并且设置字符集为utf8
CREATE DATABASE IF NOT EXISTS db CHARACTER SET utf8;

CREATE DATABASE IF NOT EXISTS `database` CHARACTER SET utf8;

CREATE DATABASE IF NOT EXISTS `数据库` CHARACTER SET utf8;

SHOW DATABASES [LIKE 'pattern']

-- 查看所有数据库
SHOW DATABASES;

-- 查看以information_开头的数据库，需要对中划线_进行转义
SHOW DATABASES LIKE 'information\_%';

-- 查看以information开头的数据库，相当于information%
SHOW DATABASES LIKE 'information_%';

SHOW CREATE DATABASE db_name

-- 查看数据库db的创建语句
SHOW CREATE DATABASE db;

ALTER DATABASE db_name [库选项]

-- 修改数据库db的字符集
ALTER DATABASE db CHARACTER SET gbk; 

DROP DATABASE [IF EXISTS] db_name

-- 删除db数据库
DROP DATABASE IF EXISTS db;

CREATE TABLE [IF NOT EXISTS] tbl_name(
    col_name type,
    col_name type    
)

-- 创建student表
CREATE TABLE IF NOT EXISTS db.student (
	id INT PRIMARY KEY NOT NULL,
	name VARCHAR(10),
	age TINYINT
)

-- 指定数据库
USE db;

-- 创建student表
CREATE TABLE IF NOT EXISTS student (
	id INT PRIMARY KEY NOT NULL,
	name VARCHAR(10),
	age TINYINT
)

SHOW [FULL] TABLES [FROM db_name] [LIKE 'pattern']

SHOW TABLES;

SHOW FULL TABLES;

-- 查看以s开头的表
SHOW TABLES LIKE 's%';

SHOW CREATE TABLE student;

DESC student;

RENAME TABLE tbl_name TO new_tbl_name
    [, tbl_name2 TO new_tbl_name2] ...

-- 将student重命名为teacher
RENAME TABLE student to teacher;

ALTER TABLE tbl_name ADD [COLUMN] col_name type [FIRST | AFTER col_name ]

-- 在name字段后面插入grade字段
ALTER TABLE class ADD COLUMN grade VARCHAR(10) AFTER name;

ALTER TABLE tbl_name MODIFY [COLUMN] col_name type [FIRST | AFTER col_name ]

-- 修改grade字段的长度
ALTER TABLE class MODIFY COLUMN grade VARCHAR(20);

ALTER TABLE tbl_name CHANGE [COLUMN] old_col_name new_col_name type [FIRST | AFTER col_name ]

-- 修改表class的num字段名称为total
ALTER TABLE class CHANGE COLUMN num total int(11);

ALTER TABLE tbl_name DROP [COLUMN] col_name

-- 删除class表的total字段
ALTER TABLE class DROP COLUMN total;

DROP TABLE [IF EXISTS] tbl_name [, tbl_name] ...

-- 删除class表
DROP TABLE IF EXISTS class;

INSERT [INTO] tbl_name [(col_name,...)] VALUES ({expr | DEFAULT},...)

-- student表插入一条数据
INSERT INTO student VALUES(1, 's1', 20);

-- 根据列名与顺序，往student表插入一条数据
INSERT INTO student(name, age) VALUES('s2', 20);

UPDATE tbl_name
    SET col_name1=expr1 [, col_name2=expr2 ...]
    [WHERE where_definition]

-- 更新student表name为s2的age字段值
UPDATE student SET age = 22 WHERE `name` = 's2';

UPDATE table_references
    SET col_name1=expr1 [, col_name2=expr2 ...]
    [WHERE where_definition]

UPDATE class, student SET student.age = 23 WHERE student.classId = class.id;

DELETE FROM tbl_nam [WHERE where_definition]

-- 删除姓名为s1的数据
DELETE FROM student WHERE student.`name` = 's1'