[大数据]Navicat for mysql命令行操作及常用语句

一、Navicat for mysql打开命令行窗口

二、常用操作

1. 查看数据库

mysql> SHOW DATABASES;
+------------------------+
| Database               |
+------------------------+
| db_library_manager_web |
| db_student_manager_web |
| information_schema     |
| mysql                  |
| performance_schema     |
| phpmyadmin             |
+------------------------+
6 rows in set

2. 创建数据库

【温馨提示：这里有个容易犯错的点，就是不加分号，在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

3. 创建数据表
   在创建数据表之前，需要先指定具体是哪个数据库，再创建数据表；

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