Mysql 基础 (一)_mysql

•     1.2 断开服务器

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