Mysql 基础 (一)

Mysql 基础 (1)

1 连接与断开服务器

1.1 连接服务器

 mysql -h host -u user -p

1.2 断开服务器

mysql> QUIT

1.3 修改提示符号

C:\Users\admin>mysql -uroot -p123456 --prompt \h;
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 22 to server version: 5.0.22-community-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

localhost;

当进去mysql里面时候可以改成这样

改成 u那么用命令prompt\u注意没有分号；

localhost;prompt \u
PROMPT set to '\u'
root
root

2 查询mysql 当前版本

mysql> select version();
+---------------------+
| version()           |
+---------------------+
| 5.0.22-community-nt |
+---------------------+
1 row in set

查询当前时间

(1)

mysql> select current_date;
+--------------+
| current_date |
+--------------+
| 2015-07-05   |
+--------------+
1 row in set

(2)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2015-07-05 20:54:20 |
+---------------------+
1 row in set

(3)当前用户

mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set

（4）可以用逗号,隔开查询

mysql> select current_date,user();
+--------------+----------------+
| current_date | user()         |
+--------------+----------------+
| 2015-07-05   | root@localhost |
+--------------+----------------+
1 row in set

2 创建并且使用数据库

2.1查询当前用户下所有数据库

CREATE ｛DATABASE|SCHEMA｝【IF NOT EXISTS】 db_name [DEFAULT] CHARACTER SET [=] charset_name

其中花括号是必须的，中括号是可选的

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| t2                 |
| test               |
| wordp              |
+--------------------+
5 rows in set

如果你没数据库的权限，可以让管理员执行下列命令

mysql> GRANT ALL ON menagerie.* TO 'your_mysql_name'@'your_client_host';

这里your_mysql_name是分配给你的MySQL用户名，your_client_host是所连接的服务器所在的主机。

2.2创建数据库

mysql> create database t1;
Query OK, 1 row affected

使用该数据库，用 use命令

mysql> use t1;
Database changed

2.3查看数据库的信息，看当前使用的是哪个数据库

mysql> select database
();
+------------+
| database() |
+------------+
| t1         |
+------------+
1 row in set

查看数据库编码方式

mysql> show create DATABASE t1;
+----------+-------------------------------------------------------------+
| Database | Create Database                                             |
+----------+-------------------------------------------------------------+
| t1       | CREATE DATABASE `t1` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-------------------------------------------------------------+

2.4 修改数据库

ALTER{DATABASE|SCHEMA} [db_name]

[DEFAULT] CHARACTER SET [=] charset_name

2.5 删除数据库

DROP {DATABASE|SCHEMA} [IF EXISTS] db_name

3 创建表

看下当前t1数据库下面的表

mysql> show tables;
Empty set

mysql> create table pet(name varchar(20),
    -> owner varchar(20),
    -> species varchar(20),
    -> sex char(1),
    -> birth DATE,
    -> death DATE);
Query OK, 0 rows affected

mysql> describe pet;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name    | varchar(20) | YES  |     | NULL    |       |
| owner   | varchar(20) | YES  |     | NULL    |       |
| species | varchar(20) | YES  |     | NULL    |       |
| sex     | char(1)     | YES  |     | NULL    |       |
| birth   | date        | YES  |     | NULL    |       |
| death   | date        | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
6 rows in set

1 用load 命令把文本文件添加到表中或者用insert语句添加数据到表中

mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet
    -> LINES TERMINATED BY '\r\n';

Mysql 基础 (一)

1.2 断开服务器

相关推荐