Mysql SQL基础

1：创建表

CREATE TABLE user10(
id SMALLINT UNSIGNED KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL UNIQUE,
password CHAR(32) NOT NULL,
email VARCHAR(50) NOT NULL DEFAULT 'a@a.com',
age TINYINT UNSIGNED DEFAULT 18,
addr VARCHAR(200) NOT NULL DEFAULT '北京',
salary FLOAT(6,2),
regTime INT UNSIGNED,
face CHAR(100) NOT NULL DEFAULT 'default.jpg',
sex ENUM('男','女','保密') DEFAULT '保密'
);

修改表名

ALTER TABLE user10 RENAME TO user11;
RENAME TABLE user11 TO user10;

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| slave              |
| sys                |
| test               |
| test1              |
| test3              |
| test4              |
| work1              |
| work2              |
+--------------------+
11 rows in set (0.00 sec)

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1             |
| use8           |
+----------------+
2 rows in set (0.00 sec)

mysql> rename table t1 to t2;
Query OK, 0 rows affected (0.01 sec)

mysql> alter table t2 rename to t3;
Query OK, 0 rows affected (0.02 sec)

mysql>

2：查看表结构

mysql> describe user10;
+----------+----------------------------+------+-----+-------------+----------------+
| Field    | Type                       | Null | Key | Default     | Extra          |
+----------+----------------------------+------+-----+-------------+----------------+
| id       | smallint unsigned          | NO   | PRI | NULL        | auto_increment |
| username | varchar(20)                | NO   | UNI | NULL        |                |
| password | char(32)                   | NO   |     | NULL        |                |
| email    | varchar(50)                | NO   |     | a@a.com     |                |
| age      | tinyint unsigned           | YES  |     | 18          |                |
| addr     | varchar(200)               | NO   |     | 北京        |                |
| salary   | float(6,2)                 | YES  |     | NULL        |                |
| regTime  | int unsigned               | YES  |     | NULL        |                |
| face     | char(100)                  | NO   |     | default.jpg |                |
| sex      | enum('男','女','保密')     | YES  |     | 保密        |                |
+----------+----------------------------+------+-----+-------------+----------------+
10 rows in set (0.01 sec)

mysql>