sql

sql常用 -- mysql

类型

• tinyint/smallint/mediumint
• int(integer)/bigint
• float/double
• decimal(dec)
• date
• time
• datetime
• timestamp
• year
• char
• varchar
• binary
• varbinary
• tinyblob/blob (255B/64KB)
• mediumblob/longblob(16MB/4GB)
• tinytext/text
• mediumtext/longtext
• enum('value1', 'value2')
• set('value1', 'value2', ...)

---

使用子查询建表：
语法：

create table[模式名.]表名 [column[, column...]]
as subquery;

示例：

mysql> select * from tb_student;
+----+---------------+------+
| id | name          | age  |
+----+---------------+------+
|  1 | liu wei zhen  |  100 |
|  2 | liu fang zhen |  120 |
|  3 | li xiao       |  110 |
+----+---------------+------+

mysql> create table tb_student_2 
as select * from tb_student;

Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from tb_student_2;
+----+---------------+------+
| id | name          | age  |
+----+---------------+------+
|  1 | liu wei zhen  |  100 |
|  2 | liu fang zhen |  120 |
|  3 | li xiao       |  110 |
+----+---------------+------+
3 rows in set (0.00 sec)

alert table 更改表结构

示例：为表格 tb_student_2 添加1列：acore

mysql> alter table tb_student_2 add score float;

为表格 tb_student_2 添加列：hometown, favor

mysql> alter table tb_student_2 
    -> add
    -> (
    -> hometown text,
    -> favor int
    -> );

mysql> select * from tb_student_2;
+----+---------------+------+-------+----------+-------+
| id | name          | age  | score | hometown | favor |
+----+---------------+------+-------+----------+-------+
|  1 | liu wei zhen  |  100 |  NULL | NULL     |  NULL |
|  2 | liu fang zhen |  120 |  NULL | NULL     |  NULL |
|  3 | li xiao       |  110 |  NULL | NULL     |  NULL |
+----+---------------+------+-------+----------+-------+

modify 修改列定义

mysql> alter table tb_student_2 modify favor text;

drop 删除列

mysql> alter table tb_student_2 drop favor;

mysql重命名表：

mysql> alter table tb_student rename to tb_student_1;

mysql重命名列

mysql> alter table tb_student_1 change name student_name varchar(128)