一、Navicat for mysql打开命令行窗口
二、常用操作
- 查看数据库
mysql> SHOW DATABASES;
+------------------------+
| Database |
+------------------------+
| db_library_manager_web |
| db_student_manager_web |
| information_schema |
| mysql |
| performance_schema |
| phpmyadmin |
+------------------------+
6 rows in set
- 创建数据库
【温馨提示：这里有个容易犯错的点，就是不加分号，在mysql编码里是少不了分号滴，实在忘记了补上就ok,如下两例所示】
mysql> CREATE DATABASE sweet_test1
-> ;
Query OK, 1 row affected
mysql> CREATE DATABASE sweet_test2;
Query OK, 1 row affected
mysql> SHOW DATABASES;
+------------------------+
| Database |
+------------------------+
| db_library_manager_web |
| db_student_manager_web |
| information_schema |
| mysql |
| performance_schema |
| phpmyadmin |
| sweet_test1 |
| sweet_test2 |
+------------------------+
8 rows in set
- 创建数据表
在创建数据表之前，需要先指定具体是哪个数据库，再创建数据表；
mysql> USE sweet_test1;
Database changed
#创建数据表bd_1,bd_2
mysql> CREATE TABLE bd_1(id INT(11),name VARCHAR(25) );
Query OK, 0 rows affected
mysql> CREATE TABLE bd_2(id INT(11),name VARCHAR(25),sex BOOLEAN,salary float);
Query OK, 0 rows affected
#查看数据表
mysql> SHOW TABLES;
+-----------------------+
| Tables_in_sweet_test1 |
+-----------------------+
| bd_1 |
| bd_2 |
+-----------------------+
2 rows in set
#查看数据表bd_1 基本结构
mysql> DESCRIBE bd_1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(25) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set
#查看数据表bd_1 详细结构
mysql> show create table bd_1;
+-------+--------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------------------+
| bd_1 | CREATE TABLE `bd_1` (
`id` int(11) DEFAULT NULL,
`name` varchar(25) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+--------------------------------------------------------------------------------------------------------------------------+
1 row in set
创建数据表时，还要定义不同的约束，如：
主键约束：primary key
外键约束：constraint <外键名> foreign key references <表名><主键列>
非空约束：NOT NULL
唯一性约束：UNIQUE
默认约束：DEFAULT <默认值>
自增约束： AUTO_INCREMENT