05.数据表的创建与简单操作

数据表的创建

使用数据库

mysql> USE zoom;
Database changed

查看数据库信息

mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| zoom       |
+------------+
1 row in set (0.00 sec)

创建表

mysql> CREATE TABLE little(
username VARCHAR(20), 
age TINYINT UNSIGNED,
weight FLOAT(5,2) UNSIGNED
);
Query OK, 0 rows affected (0.41 sec)

显示表

mysql> SHOW TABLES;
+----------------+
| Tables_in_zoom |
+----------------+
| little         |
+----------------+
1 row in set (0.00 sec)

建表时字段的默认值

mysql> CREATE TABLE default_(
    -> age TINYINT UNSIGNED  DEFAULT 2
    -> );
Query OK, 0 rows affected (0.03 sec)
mysql> INSERT default_ VALUES();
Query OK, 1 row affected (0.00 sec)

mysql> SELECT*FROM default_;
+------+
| age  |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

查看表结构SHOW COLUMNS FROM 或DESC

mysql> SHOW COLUMNS FROM little;
+----------+---------------------+------+-----+---------+-------+
| Field    | Type                | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| username | varchar(20)         | YES  |     | NULL    |       |
| age      | tinyint(3) unsigned | YES  |     | NULL    |       |
| weight   | float(5,2) unsigned | YES  |     | NULL    |       |
+----------+---------------------+------+-----+---------+-------+
3 rows in set (0.02 sec)

显示指定数据库的表

mysql> SHOW TABLES FROM mysql;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| engine_cost               |
| event                     |
| func                      |
| general_log               |
| gtid_executed             |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| innodb_index_stats        |
| innodb_table_stats        |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| server_cost               |
| servers                   |
| slave_master_info         |
| slave_relay_log_info      |
| slave_worker_info         |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
31 rows in set (0.01 sec)

简单操作

插入数据（所有字段均要赋值）

mysql> INSERT little VALUES('dog',37,12.77);
Query OK, 1 row affected (0.01 sec)

为指定列赋值

mysql> INSERT little(username,age) VALUES('cat',27);
Query OK, 1 row affected (0.36 sec)

查询

mysql> SELECT*FROM little;
+----------+------+--------+
| username | age  | weight |
+----------+------+--------+
| dog      |   37 |  12.77 |
| cat      |   27 |   NULL |
+----------+------+--------+
2 rows in set (0.00 sec)