1、数据表是数据库的重要内容，首先打开数据库。
USE DATABASE;
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| t2 |
| test |
+--------------------+
5 rows in set (0.00 sec)
mysql> USE test;
Database changed
mysql> SELECT DATABASE(); #显示当前打开的数据库
+------------+
| DATABASE() |
+------------+
| test |
+------------+
1 row in set (0.00 sec)
2、创建数据表
CREATE TABLE [IF NOT EXISTS] table_name(
column_name（列名称） data_type,
....
)
mysql> CREATE TABLE tb1(
-> usename VARCHAR(20),
-> age TINYINT UNSIGNED,
-> salary FLOAT(8,2) UNSIGNED
-> );
Query OK, 0 rows affected (0.11 sec)
3、查看数据表
mysql> SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| tb1 |
+----------------+
1 row in set (0.00 sec)
mysql> SHOW TABLES FROM mysql;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
.
.
4、查看数据表结构
mysql> SHOW COLUMNS FROM tb1;
+---------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------------------+------+-----+---------+-------+
| usename | varchar(20) | YES | | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
| salary | float(8,2) unsigned | YES | | NULL | |
+---------+---------------------+------+-----+---------+-------+
5、插入记录
mysql> INSERT tb1 VALUES('Tom', 23, 6785.23);
Query OK, 1 row affected (0.03 sec)
查看数据表中插入的记录
mysql> SELECT * FROM tb1;
+---------+------+---------+
| usename | age | salary |
+---------+------+---------+
| Tom | 23 | 6785.23 |
+---------+------+---------+
1 row in set (0.00 sec)
mysql> INSERT tb1(usename, salary) VALUES('john', 10003.56);
Query OK, 1 row affected (0.02 sec)
mysql> SELECT * FROM tb1;
+---------+------+----------+
| usename | age | salary |
+---------+------+----------+
| Tom | 23 | 6785.23 |
| john | NULL | 10003.56 |
+---------+------+----------+
2 rows in set (0.00 sec)
|
c创建数据库 |
CREATE DATABASE t1; |
创建数据表 |
CREATE TABLE tb1( ); |
|
删除数据库 |
DROP DATABASE t1; |
查看数据表结构 |
SHOW COLUMNS FROM tb1; |
|
查看所有的数据库 |
SHOW DATABASES; |
插入记录 |
INSERT tb1 VALUES(); |
|
选择数据库 |
USE t1; |
记录查看(以网格形式呈现) |
SELECT * FROM tb1(G); |
|
查看当前使用的数据库 |
SELECT DATABASE(); |
查看数据库中所有的数据表 |
SHOW TABLES; |
查看创建数据库的信息
mysql> SHOW CREATE DATABASE test;
+----------+-----------------------------------------------------------------+
| Database | Create Database |
+----------+-----------------------------------------------------------------+
| test | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+-----------------------------------------------------------------+
查看创建数据表的信息
mysql> SHOW CREATE TABLE t2;
| t2 | CREATE TABLE `t2` (
`id` smallint(5) unsigned NOT NULL,
`name` varchar(20) CHARACTER SET latin1 DEFAULT NULL,
`password` varchar(39) CHARACTER SET latin1 NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `password` (`password`),
UNIQUE KEY `name` (`name`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------