mysql创建数据表book_mysql 表创建操作

查看帮助

mysql> help contents;

You asked for help about help category: "Contents"

For more information, type 'help ', where is one of the following

categories:

Account Management

Administration

Compound Statements

Data Definition

Data Manipulation

Data Types

Functions

Functions and Modifiers for Use with GROUP BY

Geographic Features

Help Metadata

Language Structure

Plugins

Procedures

Storage Engines

Table Maintenance

Transactions

User-Defined Functions

Utility

查看使用的存储引擎

show engines \g;

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

| Engine | Support | Comment | Transactions | XA | Savepoints |

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

| CSV | YES | CSV storage engine | NO | NO | NO |

| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |

| MyISAM | YES | MyISAM storage engine | NO | NO | NO |

| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |

| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |

| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |

| ARCHIVE | YES | Archive storage engine | NO | NO | NO |

| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |

| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |

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

查看数据库

mysql> show databases;

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

| Database |

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

| information_schema |

| mysql |

| performance_schema |

| sys |

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

4 rows in set (0.00 sec)

创建数据库

mysql> create database test;

Query OK, 1 row affected (0.00 sec)

mysql> show databases;

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

| Database |

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

| information_schema |

| mysql |

| performance_schema |

| sys |

| test |

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

5 rows in set (0.01 sec)

进入test数据库

mysql> use test;

Database changed

删除数据库

mysql> drop database test;

Query OK, 0 rows affected (0.31 sec)

查看

mysql> show databases;

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

| Database |

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

| information_schema |

| mysql |

| performance_schema |

| sys |

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

4 rows in set (0.00 sec)

表的操作，查看数据的表

mysql> create database test;

Query OK, 1 row affected (0.05 sec)

mysql> use test;

Database changed

mysql> show tables;

Empty set (0.00 sec)

mysql支持的数据类型

数字类型

用的比较多的INT(整数)和DOUBLE(小数)；Numeric(10,2)是指字段是数字型，长度为10，小数为2位

字符串类型

常用的CHAR 、VARCHAR 、TEXT、LONGTEXT

时间类型

创建表格式create table test(字段名称 字段类型,字段名称 字段类型……)

mysql> create table tt(num int(6),name varchar(10), sex varchar(2), age int, shcooldat date);

Query OK, 0 rows affected (0.93 sec)

查看创建的表

mysql> show tables;

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

| Tables_in_test |

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

| tt |

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

1 row in set (0.00 sec)

查看表里所有字段值；因为无数据

mysql> select * from tt;

Empty set (0.00 sec)

查看表结构

mysql> desc tt;

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

| Field | Type | Null | Key | Default | Extra |

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

| num | int(6) | YES | | NULL | |

| name | varchar(10) | YES | | NULL | |

| sex | varchar(2) | YES | | NULL | |

| age | int(11) | YES | | NULL | |

| shcooldat | date | YES | | NULL | |

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

5 rows in set (0.00 sec)

--Filed 这张表的所有的字段

--Type 字段的类型

--Null 代表是否可以为空，也就是插入数据的时候某一个字段可不可以为空

--Key

--Defaule 什么都不插入的时候默认为空

建一个表

mysql> create table book(num int,name varchar(10),datel date,price double(5,2));

Query OK, 0 rows affected (0.16 sec)

mysql> desc book;

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

| Field | Type | Null | Key | Default | Extra |

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

| num | int(11) | YES | | NULL | |

| name | varchar(10) | YES | | NULL | |

| datel | date | YES | | NULL | |

| price | double(5,2) | YES | | NULL | |

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

4 rows in set (0.00 sec)

数据类型中的 double 长度控制可以是两个长度一个是总长度，一个是小数点后面的长度。

新建一个表；

mysql> create table books(

-> book_id INT,

-> title VARCHAR (50),

-> author VARCHAR (50));

Query OK, 0 rows affected (0.14 sec)

mysql> show tables;

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

| Tables_in_test |

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

| books |

| tt |

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

2 rows in set (0.00 sec)

修改新表字段内容操作

alter table book

change column book_id book_id INT AUTO_INCREMENT PRIMARY KEY, #这个book_id 表示将要修改现有的列，该句子余下的部分用于指定一个新列。

change column author author_id INT,

add column description TEXT,

add column genre ENUM('novel','poetry','drama') ,

add column publisher_id INT,

add column pub_year VARCHAR (4),

add column isbn VARCHAR (20);

Query OK, 0 rows affected (0.28 sec)

Records: 0 Duplicates: 0 Warnings: 0

在test 数据库里查看mysql库里的表

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.00 sec)