mysql基本操作

页面导航:首页 > 数据库 > Mysql > mysql基本操作

mysql基本操作

来源: 作者: 时间:2016-01-25 09:47 【

1 登陆与退出(1) 登陆mysql –u用户名–p密码此命令密码是明文mysql –u用户名–pEnter Password登陆的同时指明主机号:mysql –h主机名–u用户名 –p密码登陆的同时指明端口
1. 登陆与退出

(1) 登陆

mysql –u用户名–p密码

此命令密码是明文

mysql –u用户名–p

Enter Password

登陆的同时指明主机号:

mysql –h主机名–u用户名 –p密码

登陆的同时指明端口号

mysql –h主机名–u用户名 –p密码–P端口号(3306)

登陆的同时修改命令提示符:

mysql –h主机名–u用户名 –p密码–P端口号(3306)--prompt=

命令提示符包括:

\u当前用户名

\h当前主机名

\D当前日期时间

\d当前打开的

也可以在登陆后,修改命令提示符:

Promptkaikeba>

命令提示符改为kaikeba>

登陆的同时修改命令分隔符:

 –h主机名–u用户名 –p密码–P端口号(3306)--prompt= --delimiter=

也可以再登陆之后修改命令分隔符

Delimiter @

命令结束符变为:@

在登陆的同时打开数据库:

Mysql -u用户名 -p密码 -h主机名 -D数据库名

Eg:

Mysql -uroot-proot -hlocalhost -Dtest;

(2) 退出

Exit

\q

Quit

Ctrl+C快捷键

(3) SQL规范

数据库名、表名小写;关键字、保留字、函数名称大写;

SQL命令支持折行操作,但不能将单词、名称、或成对引号折行写

当名称与保留字冲突时,需用‘’括上名称

(4) 开启输出日志的形式

\T 文件保存位置及文件名称

\t 结束输出日志

按上下键调查之前写过的命令

2. DDL操作

(1) 创建数据库

CREATE{DATABASE|SCHEMA} db_name;

CREATE{DATABASE|SCHEMA} [IF NOT EXISTS] db_name;

CREATE{DATABASE|SCHEMA} [IF NOT EXISTS] db_name [[DEFAULT] CHARACTER SET [=] 字符集];

(2) 查看当前服务器下已有的数据库

SHOW{DATABASES|SCHEMA};

(3) 查看上一步操作得到的警告

SHOW WARNINGS

(4) 查看已创建数据库的编码方式

SHOW CREATEDATABASE db_name;

综合案例1:

mysql> CREATEDATABASE randongmei;

Query OK, 1 rowaffected (0.00 sec)

mysql> SHOWDATABASES;

+--------------------+

| Database |

+--------------------+

|information_schema |

| mysql |

|performance_schema |

|randongmei |

| test |

+--------------------+

5 rows in set(0.00 sec)

mysql> CREATESCHEMA king;

Query OK, 1 rowaffected (0.00 sec)

mysql> SHOWSCHEMAS;

+--------------------+

| Database |

+--------------------+

|information_schema |

| king |

| mysql |

| performance_schema|

|randongmei |

| test |

+--------------------+

6 rows in set(0.00 sec)

mysql> CREATESCHEMA king;

ERROR 1007(HY000): Can't create database 'king'; database exists

mysql> CREATESCHEMA IF NOT EXISTS king;

Query OK, 1 rowaffected, 1 warning (0.00 sec)

mysql> SHOWWARNINGS;

+-------+------+-----------------------------------------------+

| Level | Code |Message |

+-------+------+-----------------------------------------------+

| Note | 1007 | Can't create database 'king';database exists |

+-------+------+-----------------------------------------------+

1 row in set(0.00 sec)

mysql> SHOWDATABASES;

+--------------------+

| Database |

+--------------------+

| information_schema|

| king |

| mysql |

|performance_schema |

|randongmei |

| test |

+--------------------+

6 rows in set(0.00 sec)

mysql> SHOWCREATE DATABASE randongmei;

+------------+---------------------------------------------------------------------+

| Database | Create Database |

+------------+---------------------------------------------------------------------+

| randongmei |CREATE DATABASE `randongmei` /*!40100 DEFAULT CHARACTER SET utf8 */ |

+------------+---------------------------------------------------------------------+

1 row in set(0.00 sec)

mysql> CREATEDATABASE IF NOT EXISTS test1 DEFAULT CHARACTER SET ='gbk';

Query OK, 1 rowaffected (0.00 sec)

mysql> SHOWCREATE DATABASE test1

-> ;

+----------+---------------------------------------------------------------+

| Database |Create Database |

+----------+---------------------------------------------------------------+

| test1 | CREATE DATABASE `test1` /*!40100 DEFAULTCHARACTER SET gbk */ |

+----------+---------------------------------------------------------------+

1 row in set(0.00 sec)

mysql> CREATESCHEMA IF NOT EXISTS test3 CHARACTER SET 'GBK';

Query OK, 1 rowaffected (0.00 sec)

mysql> \t

(5) 修改数据库的编码方式

ALTER{DATABASE|SCHEMA} db_name [[DEFAULT] CHARACTER SET [=] 字符集];

mysql> SHOWDATABASES;

+--------------------+

| Database |

+--------------------+

|information_schema |

| king |

| mysql |

|performance_schema |

|randongmei |

| test |

| test1 |

| test3 |

+--------------------+

8 rows in set(0.02 sec)

mysql> SHOWCREATE DATABASE test1;

+----------+---------------------------------------------------------------+

| Database |Create Database |

+----------+---------------------------------------------------------------+

| test1 | CREATE DATABASE `test1` /*!40100 DEFAULTCHARACTER SET gbk */ |

+----------+---------------------------------------------------------------+

1 row in set(0.00 sec) 

mysql> ALTERDATABASE test1 DEFAULT CHARACTER SET 'UTF8';

Query OK, 1 rowaffected (0.00 sec) 

mysql> SHOWCREATE DATABASE test1;

+----------+----------------------------------------------------------------+

| Database |Create Database |

+----------+----------------------------------------------------------------+

| test1 |CREATE DATABASE `test1` /*!40100 DEFAULT CHARACTER SET utf8 */ |

+----------+----------------------------------------------------------------+

1 row in set(0.00 sec) 

mysql> SHOWCREATE SCHEMA test3;

+----------+---------------------------------------------------------------+

| Database |Create Database |

+----------+---------------------------------------------------------------+

| test3 | CREATE DATABASE `test3` /*!40100 DEFAULTCHARACTER SET gbk */ |

+----------+---------------------------------------------------------------+

1 row in set(0.00 sec) 

mysql> ALTERDATABASE test3 CHARACTER SET 'UTF8';

Query OK, 1 rowaffected (0.00 sec) 

mysql> SHOWCREATE DATABASE test3;

+----------+----------------------------------------------------------------+

| Database |Create Database |

+----------+----------------------------------------------------------------+

| test3 | CREATE DATABASE `test3` /*!40100 DEFAULTCHARACTER SET utf8 */ |

+----------+----------------------------------------------------------------+

1 row in set(0.00 sec) 

mysql> \t

(6) 打开指定数据库

USE db_name;

(7) 得到当前已经打开的数据库

SELECT{DATABASE()|SCHEMA()}; 

mysql> SHOWDATABASES;

+--------------------+

| Database |

+--------------------+

|information_schema |

| king |

| mysql |

|performance_schema |

|randongmei |

| test |

| test1 |

| test3 |

+--------------------+

8 rows in set(0.00 sec) 

mysql> USEtest1;

Database changed

mysql> SELECTDATABASE();

+------------+

| DATABASE() |

+------------+

| test1 |

+------------+

1 row in set(0.00 sec) 

mysql> USErandongmei;

Database changed

mysql> SELECTSCHEMA();

+------------+

| SCHEMA() |

+------------+

| randongmei |

+------------+

1 row in set(0.00 sec)

mysql> \t

(8) 删除指定的数据库

DROP {DATABASE|SCHEMA} db_name;

DROP {DATABASE|SCHEMA} [IF EXISTS] db_name;

不可以一次删除多个数据库
Tags:

文章评论

最 近 更 新
热 点 排 行
Js与CSS工具
代码转换工具

<