本文主要介绍mysql常用的db、table简单语句，全部为mysql指令，方便大家查阅。
连接数据库是用mysql命令，可以后面接--help查看参数使用说明，我列举下我用到过的参数。
| 参数 | 描述 |
| --help | 查看帮助 |
| -h, --host= | 服务器域名或者ip |
| -p, --password= | 连接密码 |
| -P, --port= | 端口号，默认3306 |
| -u, --user= | 用户名 |
| -S, --socket= | 指定socket文件 |
| -e | 在shell中直接执行并输出 |
这里列举几个连接的例子：
#默认root用户无密码连接本地mysql
root@localhost [~] mysql
#使用密码连接
root@localhost [~] mysql -hlocalhost -uroot -p111111
#连接3307端口
root@localhost [~] mysql -hlocalhost -uroot -p111111 -P3307
#密码含有特殊字符如叹号，会报错
root@localhost [~] mysql -p123!456
-bash: !456: event not found
#可以转义
root@localhost [~] mysql -p123\!456
#可以引号
root@localhost [~] mysql -p'123\!456'
#可以命令提示行输入
root@localhost [~] mysql -p
Enter password:
root@localhost [~] mysql
#使用密码连接
root@localhost [~] mysql -hlocalhost -uroot -p111111
#连接3307端口
root@localhost [~] mysql -hlocalhost -uroot -p111111 -P3307
#密码含有特殊字符如叹号，会报错
root@localhost [~] mysql -p123!456
-bash: !456: event not found
#可以转义
root@localhost [~] mysql -p123\!456
#可以引号
root@localhost [~] mysql -p'123\!456'
#可以命令提示行输入
root@localhost [~] mysql -p
Enter password:
127.0.0.1和localhost的区别：
127.0.0.1是指本机的ip地址，localhost是指本地服务器。
localhot不经网卡传输，因此无防火墙限制。
127.0.0.1会占用网卡、网络资源。
使用localhost，mysql客户端会尝试用本地unix socket（linux）/pip（windows）连接，速度比TCP/IP连接快。
使用127.0.0.1，则会使用TCP/IP连接。
mysql --socket仅支持本地连接，连接时不需要指明端口。
查看数据库，用show databases。
创建数据库，用create。
更多show命令，参加mysql show指令深入解析。
在创建数据表之前，首先要进入到具体数据库中，创建语句依旧用create。
创建一个数据库，包含账号、姓名。
mysql> create table ranshy_user (
id INT AUTO_INCREMENT PRIMARY KEY,
name varchar(20)
);
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+------------------------+
| Tables_in_test |
+------------------------+
| ranshy_user |
+------------------------+
1 row in set (0.00 sec)
id INT AUTO_INCREMENT PRIMARY KEY,
name varchar(20)
);
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+------------------------+
| Tables_in_test |
+------------------------+
| ranshy_user |
+------------------------+
1 row in set (0.00 sec)
查看表信息有多种方法：
1. desc 表名;
2. describe 表名;
3. show columns from 表名;
4. show create table 表名;
5. use information_schema; select * from columns where table_name='表名';
mysql> desc ranshy_user;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> describe ranshy_user;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)
mysql> show columns from ranshy_user;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> show create table ranshy_user;
+-------------+---------------------------------+
| Table | Create Table |
+-------------+---------------------------------+
| ranshy_user | CREATE TABLE `ranshy_user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------------+---------------------------------+
1 row in set (0.00 sec)
mysql> use information_schema; select * from columns where table_name='ranshy_user';
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
+---------------+--------------+-------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+-----------------+-------------+------------+----------------+---------------------------------+----------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | ORDINAL_POSITION | COLUMN_DEFAULT | IS_NULLABLE | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH | NUMERIC_PRECISION | NUMERIC_SCALE | CHARACTER_SET_NAME | COLLATION_NAME | COLUMN_TYPE | COLUMN_KEY | EXTRA | PRIVILEGES | COLUMN_COMMENT |
+---------------+--------------+-------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+-----------------+-------------+------------+----------------+---------------------------------+----------------+
| def | test | ranshy_user | id | 1 | NULL | NO | int | NULL | NULL | 10 | 0 | NULL | NULL | int(11) | PRI | auto_increment | select,insert,update,references | |
| def | test | ranshy_user | name | 2 | NULL | YES | varchar | 20 | 60 | NULL | NULL | utf8 | utf8_general_ci | varchar(20) | | | select,insert,update,references | |
+---------------+--------------+-------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+-----------------+-------------+------------+----------------+---------------------------------+----------------+
2 rows in set (0.02 sec)
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> describe ranshy_user;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)
mysql> show columns from ranshy_user;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> show create table ranshy_user;
+-------------+---------------------------------+
| Table | Create Table |
+-------------+---------------------------------+
| ranshy_user | CREATE TABLE `ranshy_user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------------+---------------------------------+
1 row in set (0.00 sec)
mysql> use information_schema; select * from columns where table_name='ranshy_user';
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
+---------------+--------------+-------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+-----------------+-------------+------------+----------------+---------------------------------+----------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | ORDINAL_POSITION | COLUMN_DEFAULT | IS_NULLABLE | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH | NUMERIC_PRECISION | NUMERIC_SCALE | CHARACTER_SET_NAME | COLLATION_NAME | COLUMN_TYPE | COLUMN_KEY | EXTRA | PRIVILEGES | COLUMN_COMMENT |
+---------------+--------------+-------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+-----------------+-------------+------------+----------------+---------------------------------+----------------+
| def | test | ranshy_user | id | 1 | NULL | NO | int | NULL | NULL | 10 | 0 | NULL | NULL | int(11) | PRI | auto_increment | select,insert,update,references | |
| def | test | ranshy_user | name | 2 | NULL | YES | varchar | 20 | 60 | NULL | NULL | utf8 | utf8_general_ci | varchar(20) | | | select,insert,update,references | |
+---------------+--------------+-------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+-----------------+-------------+------------+----------------+---------------------------------+----------------+
2 rows in set (0.02 sec)
mysql创建库表需要声明utf8，否则可能会出现存储乱码、显示乱码等问题。
这是我的一个demo，供自己平时参考：
/*
RanShy MySQL Data Tranfer
Date:2016-01-23 10:07:10
*/
SET NAMES utf8;
CREATE DATABASE d_ranshy /*!40100 DEFAULT CHARACTER SET utf8 */;
USE d_ranshy;
CREATE TABLE `t_ranshy` (
`id` varchar(24) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'id（主键）',
`info` varchar(30) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '信息',
`status` tinyint(4) unsigned NOT NULL DEFAULT '0' COMMENT 'ok-0 bad-1 good-2',
`create_time` bigint(20) unsigned NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
RanShy MySQL Data Tranfer
Date:2016-01-23 10:07:10
*/
SET NAMES utf8;
CREATE DATABASE d_ranshy /*!40100 DEFAULT CHARACTER SET utf8 */;
USE d_ranshy;
CREATE TABLE `t_ranshy` (
`id` varchar(24) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'id（主键）',
`info` varchar(30) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '信息',
`status` tinyint(4) unsigned NOT NULL DEFAULT '0' COMMENT 'ok-0 bad-1 good-2',
`create_time` bigint(20) unsigned NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
说明，SET NAMES utf8 相当于以下三句话：
SET character_set_client = utf8;
SET character_set_results = utf8;
SET character_set_connection = utf8;
Leave a Reply