表基本操作

创建表

Create table student(
    Sno char(9),
    Same char(20),
    Ssex char(2),
    Sage smallint,
    Sdept char(20)
);
Create table course(
    Cno char(4),
    Cname char(40),
    Cpno char(4),
    Ccredit smallint
);
Create table sc(
    Sno char(9),
    Cno char(4),
    Grade smallint
);
Insert into student values('200215121','李勇','男',20,'cs');
Insert into student values('200215122','刘晨','女',19,'cs');
Insert into student values('200215123','王敏','女',18,'ma');
Insert into student values('200515124','张立','男',19,'is');  
Insert into course values ('1','数据库','5',4);
Insert into course values ('2','数学',NULL,2);    
Insert into course values('3','信息系统','1',4);
Insert into course values('4','操作系统','6',3);
Insert into course values('5','数据结构','7',4);
Insert into course values('6','数据处理',NULL,2);
Insert into course values('7','pascal语言','6',4);
Insert into sc values('200215121','1',92);
Insert into sc values('200215121','2',85);
Insert into sc values('200215121','3',88);
Insert into sc values('200215122','2',90);
Insert into sc values('200215122','3',80);

查看表

    SHOW TABLES [FROM db_name] [LIKE 'pattern'|WHEAR expr];

查看列（表的组织结构）

    SHOW COLUMNS FROM tb_name;
    DESC tb_name;

以上两条语句都可以。

查看表的内容

    SELECT * FROM tb_name;

插入数据

    INSERT [INTO] tb_name [(col_name,...)] VALUES(val,...);

如果不指明任何字段名，那么插入的数据必须要包含所有的字段。

插入记录

共有三种Insert语句。

INSERT

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

values是标准SQL关键字，value是MySQL自己的。
插入语句可以一次性插入多条记录。
值可以是：
- 函数
- 数学表达式
- 等

默认值

在创建数据表的时候可以设置DEFAUTL。

CREATE TABLE users(
    id SAMLLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    username SAMLLINT VARCHAR(20) NOT NULL,
    password SAMLLINT VARCHAR(20) NOT NULL,
    age TINYINT UNSIGNED NOT NULL DEFAULT 10,
    sex BOOLEAN
);

INSERT的时候如果为自动编号的字段（比如上表中主键id）赋值的话，可写作NULL，或DEFAULT就会采用默认值。
如果字段设置了DEFAULT（比如上表中age），那么值直接写DEFAULT就行，注意此时，不能为NULL。例如：

INSERT users VALUES(NULL,'TOM','123',DEFAULT,1);

INSERT…SET

INSERT [INTO] tb_name SET col_name={expr|DEFAULT},...

采用了子查询。例如，上表中插入：

INSERT users SET username='Ben',password='456';

因为id，和age都有默认值，所以可以不写。而sex允许为空，也可以不写。

INSERT…SELECT*

将查找的结果写入记录中。

INSERT test(username) SELECT username FROM users WHERE age >=30;

删除记录

同样分为：

单表删除
多表删除

DELETE FROM tb_name [WHERE where_condition];

没有where部分则会删除全部记录。

更新记录-单表更新

更新记录分为：

单表更新
多表更新（要涉及连接）

单表更新

UPDATE [LOW_PRIORITY] [IGNORE] tb_reference 
SET col_name1={expr|DEFAULT} [,col_name2={expr|DEFAULT}]...
[WHERE where_condition]

不写where会更新全部的记录。

更新记录-连接（多表更新）

多表更新

UPDATE table_references 
SET col_name1={expr1|DEFAULT}
[,col_name2={expr2|DEFAULT}]...
[WHERE where_condition]

CREATE…SELECT

创建数据表同时将查询结果写入到数据表

CREATE TABLE [IF NOT EXISTS]tb_name
[(create_definetion,...)]
select_statement

比如：

CREATE TABLE tall (
    tall_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    num INT
) SELECT 身高 AS num FROM
    av
GROUP BY 身高;

创建新表tall，并从av表中选取身高字段插入到tall表的num字段。

连接

语法结构

table_reference
{[INNER|CROSS] JOIN|{LELT|RIGHT}[OUTER] JOIN}
table_reference
ON conditional_expr

ON为条件。
三种连接，即：

内连接
左外连接
右外连接

内连接

显示左表及右表符合连接条件的记录（交集）

左外连接

显示左表的全部记录及右表符合连接条件的记录

右外连接

类别左外连接

UPDATE..JOIN..SET

比如

UPDATE actor AS a
        INNER JOIN
    tall AS b ON a.tall = b.num 
SET 
    a.tall = b.tall_id;

依据实际情况，选择是否使用别名。（如果两个表中进行比较的字段名相同，则需要别名）
该语句完成了一个替换，将actor表中具体的身高数值，替换为tall表中身高的对应id。

修改数据表

所有的数据表的修改SQL语句的开头都是ALTER TABLE+表名称。

给表改名

ALTER TABLE tb_name RENAME {TO|AS} tb_new_name;

另外有个写法是：

RENAME TABLE tb_name TO tb_new_name;

这句可以批量修改多个表的名称。

尽量不要随意修改表的名称。对视图有影响。

添加/删除列

插入列

ALTER TABLE tb_name ADD [COLUMN] col_name column_definition [FIRST|AFTER col_name];

默认会插入到表的最下面，指定FIRST，则置于最上面，或者用AFTER col_name 指定为col_name的下面。

删除列

ALTER TABLE tb_name DROP col_name,DROP.....;

可以一次性删除多个列。

修改列

修改列定义

即修改列的属性。

ALTER TABLE tb_name MODIFY col_name column_definition [FIRST|AFTER col_name];

比如：

ALTER TABLE test ADD id SMALLINT unsigned KEY auto_increment first;

key可以表示主键，无需primary。fitrst表示插入的位置。

如果要修改的列已经是主键了，则修改列定义的时候再加上主键字段会报错。

修改列名

ALTER TABLE tb_name CHANGE col_name col_new_name column_definition [FIRST|AFTER col_name];

修改列名时，新列名后面要指明数据类型。

修改列顺序

其实同上，具体比如：

ALTER TABLE `user` CHANGE `join_time` `join_time` DATE NULL DEFAULT NULL AFTER `role`;

把join_time列放到role列后面。

修改约束

PRIMARY KEY

添加主键约束

ALTER TABLE tb_name ADD [CONSTRAIN[symbol]] PRIMARY KEY [index.type] (index_col_name,.....);

删除主键约束

ALTER TABLE tb_name DROP [index.type] PRIMARY KEY;

不需要指定列名，因为每个表只有一个主键。
注意！只有一个主键并不意味着主键只有一列。

UNIQUE

添加唯一约束

ALTER TABLE tb_name ADD [index.type] unique(col_name);

删除唯一约束

ALTER TABLE tb_name DROP [index.type] unique(col_name);

DEFAULT

ALTER TABLE tb_name ALTER [COLUMN] col_name {SET DEFAULT literal|DROP DEFAULT};
for example:
ALTER TABLE users ALTER age SET DEFAULT 20;

FOREIGN KEY

添加外键约束

ALTER TABLE tb_name ADD [CONSTRAIN[symbol]] FOREIGN KEY [index_name](index_col_name,...)reference_definition;
for example:
ALTER TABLE users add FOREIGN KEY (pid) REFERENCE provinces (id);

删除外键约束

ALTER TABLE tb_name DROP FOREIGN KEY fk_symbol;

技术博客

SQL表基本操作