本文主要介绍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