使用MySQL创建表格(实例)

最新推荐文章于 2021-02-02 05:52:23 发布

陨尘oO

最新推荐文章于 2021-02-02 05:52:23 发布

阅读量6.1k

点赞数 14

文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/weixin_38610643/article/details/112727787

版权

用sql语句生成以下表格：
在这里插入图片描述

mysql> create database book;
Query OK, 1 row affected (0.01 sec)

mysql> use book;
Database changed
mysql> create table reader(
    -> card_id char(18),
    -> name varchar(10),
    -> sex enum('男','女'),
    -> age tinyint,
    -> tel char(11),
    -> balance decimal(7,3)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> desc reader;
+---------+-------------------+------+-----+---------+-------+
| Field   | Type              | Null | Key | Default | Extra |
+---------+-------------------+------+-----+---------+-------+
| card_id | char(18)          | YES  |     | NULL    |       |
| name    | varchar(10)       | YES  |     | NULL    |       |
| sex     | enum('男','女')   | YES  |     | NULL    |       |
| age     | tinyint           | YES  |     | NULL    |       |
| tel     | char(11)          | YES  |     | NULL    |       |
| balance | decimal(7,3)      | YES  |     | NULL    |       |
+---------+-------------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

mysql> show tables from book;
+----------------+
| Tables_in_book |
+----------------+
| reader         |
+----------------+
1 row in set (0.00 sec)

四种查看数据库表的方式：
a. show tables [from <database_name>];
b. show colums from <table_name>;
c. desc/describe <table_name>；
d. show create table <table_name>;

1.在表中插入第一条数据：
命令：insert into <table_name> values();

mysql> insert into reader values
    -> ('610111199901011000','Sara','female',22,'19991149827',300);
Query OK, 1 row affected (0.01 sec)

mysql> select * from reader;
+--------------------+------+--------+------+-------------+---------+
| card_id            | name | sex    | age  | tel         | balance |
+--------------------+------+--------+------+-------------+---------+
| 610111199901011000 | Sara | female |   22 | 19991149827 | 300.000 |
+--------------------+------+--------+------+-------------+---------+
1 rows in set (0.00 sec)

在表中同时插入两条数据

mysql> insert into reader values
    -> ('610111199801011001','John','male',23,'13861578234',300),
    -> ('610111199701011002','Tony','male',24,'13617823041',300);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into reader values
    -> ('610111199601011003','Lily','female',25,'18992318099',300),
    -> ('610111199501011004','Raza','male',26,'13116289309',300);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from reader;
+--------------------+------+--------+------+-------------+---------+
| card_id            | name | sex    | age  | tel         | balance |
+--------------------+------+--------+------+-------------+---------+
| 610111199901011000 | Sara | female |   22 | 19991149827 | 300.000 |
| 610111199801011001 | John | male   |   23 | 13861578234 | 300.000 |
| 610111199701011002 | Tony | male   |   24 | 13617823041 | 300.000 |
| 610111199601011003 | Lily | female |   25 | 18992318099 | 300.000 |
| 610111199501011004 | Raza | male   |   26 | 13116289309 | 300.000 |
+--------------------+------+--------+------+-------------+---------+
5 rows in set (0.00 sec)

给表中的部分item插入数据
命令： insert into <table_name> <column_name1, [column_name2, …]> values( );

mysql> insert into reader(name, sex) values 
    -> ('Frank','male'),
    -> ('Tom','male');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from reader;
+--------------------+-------+--------+------+-------------+---------+
| card_id            | name  | sex    | age  | tel         | balance |
+--------------------+-------+--------+------+-------------+---------+
| 610111199901011000 | Sara  | female |   22 | 19991149827 | 300.000 |
| 610111199801011001 | John  | male   |   23 | 13861578234 | 300.000 |
| 610111199701011002 | Tony  | male   |   24 | 13617823041 | 300.000 |
| 610111199601011003 | Lily  | female |   25 | 18992318099 | 300.000 |
| 610111199501011004 | Raza  | male   |   26 | 13116289309 | 300.000 |
| NULL               | Frank | male   | NULL | NULL        |    NULL |
| NULL               | Tom   | male   | NULL | NULL        |    NULL |
+--------------------+-------+--------+------+-------------+---------+
7 rows in set (0.00 sec)