代码mysql基础_Mysql 基础 (一)_mysql

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';