2018-04-20-2章-数据表的操作

mysql-数据表的操作

概述

数据表(或称表)时数据库最重要的组成部分之一,是其他对象的基础.

USE

• 打开数据库
• USE 数据库名称

2-6 创建数据表

(数据表创建的时候一定不能脱离实际的项目)

CREATE TABLE [IF NOT EXISTS] table_name(

​ column_name data_type,

​ …

)

mysql> create table user(
    -> username varchar(20),
    -> age tinyint unsigned,
    -> salary float(8,2) unsigned
    -> );
Query OK, 0 rows affected (0.09 sec)

年龄和薪资 不可能为负值，所以设无符号值。

2-7 查看数据表

SHOW TABLES [FROM db_name]

[LIKE ‘pattern’ |WHERE expr]

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| user           |
+----------------+
1 row in set (0.00 sec)

2-8 查看数据表结构

SHOW COLUMNS FROM tbl_name

mysql> show columns from `user`;
+----------+---------------------+------+-----+---------+-------+
| Field    | Type                | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| username | varchar(20)         | YES  |     | NULL    |       |
| age      | tinyint(3) unsigned | YES  |     | NULL    |       |
| salary   | float(8,2) unsigned | YES  |     | NULL    |       |
+----------+---------------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

---

2-9 mysql添加和删除字段

mysql> alter table test add t_extract varchar(30);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc test;

+-----------+---------------------+------+-----+---------+----------------