mysql 表创建操作

查看帮助

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

mysql> help contents; You asked for help about help category: "Contents" For more information, type 'help <item>', where <item> 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

　　查看使用的存储引擎

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14

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       | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

      查看数据库

?

1 2 3 4 5 6 7 8 9 10

mysql> show databases; +--------------------+ | Database           | +--------------------+ | information_schema | | mysql              | | performance_schema | | sys                | +--------------------+ 4 rows in set (0.00 sec)

　　创建数据库

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14

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数据库

?

1 2	mysql> use test; Database changed

　　删除数据库

?

1 2	mysql> drop database test; Query OK, 0 rows affected (0.31 sec)

　　查看

?

1 2 3 4 5 6 7 8 9 10

mysql> show databases; +--------------------+ | Database           | +--------------------+ | information_schema | | mysql              | | performance_schema | | sys                | +--------------------+ 4 rows in set (0.00 sec)

　　表的操作，查看数据的表

?

1 2 3 4 5 6 7

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(字段名称 字段类型,字段名称 字段类型……)

?

1 2	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)

    查看创建的表

?

1 2 3 4 5 6 7

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

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

?

1 2	mysql> select * from tt; Empty set (0.00 sec)

查看表结构

?

1 2 3 4 5 6 7 8 9 10 11

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 什么都不插入的时候默认为空

建一个表

?

1 2 3 4 5 6 7 8 9 10 11 12

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 长度控制可以是两个长度一个是总长度，一个是小数点后面的长度。

　新建一个表；

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14

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)

　　修改新表字段内容操作

?

1 2 3 4 5 6 7 8 9 10

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库里的表

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37

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)