mysql 表创建操作
查看帮助
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
查看使用的存储引擎
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)
版权声明：本文为rdchenxi原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。