MySQL学习笔记 初涉MySQL

1.在Linux下安装MySQL

# yum -y install mysql mysql-server mysql-devel

修改字符集：/etc/my.conf配置文件

vi /etc/my.conf[mysqld]default-character-set=utf8character_set_server=utf8[client]default-character-set=utf8

2.启动和停止MySQL服务

# service mysqld start

3.登录MySQL：

# mysql -uroot

4.修改MySQL提示符：

修改MySQL提示符

# mysql -uroot -proot --prompt '<提示符>'   //在windows中不用''

或 连接进MySQL后修改:

mysql> prompt <提示符>

常用的MySQL提示符：

例：

# mysql -uroot --prompt '\u@\h \d'root@localhost (none)//或mysql> prompt \u@\h \d>PROMPT set to '\u@\h \d>'root@localhost (none)>

5.MySQL常用命令

显示当前服务器版本号：

mysql> SELECT VERSION();+-----------+| VERSION() |+-----------+| 5.1.71    |+-----------+1 row in set (0.00 sec)

显示当前用户：

mysql> SELECT USER();   +----------------+| USER()         |+----------------+| root@localhost |+----------------+1 row in set (0.00 sec)

显示当前日期时间：

mysql> SELECT NOW(); +---------------------+| NOW()               |+---------------------+| 2016-11-20 21:32:55 |+---------------------+1 row in set (0.00 sec)

7.MySQL语句规范：

***关键字与函数名称全部大写

***数据库名称，表名称，字段名称全部小写

***SQL语句必须以分号结尾

8.数据库基本操作：

数据库创建：

mysql> CREATE DATABASE test1;

加上 IF NOT EXISTS，若已存在该数据库，会提示警告信息:

mysql> CREATE DATABASE IF NOT EXISTS test1;Query OK, 1 row affected, 1 warning (0.00 sec)

查看警告信息：.

mysql> SHOW WARNINGS;+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+| Level | Code | Message                                                                                                                                                   |+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+| Error | 1064 | You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WARNING' at line 1 |+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)

查看数据库列表：

mysql> SHOW DATABASES;+--------------------+| Database           |+--------------------+| information_schema || mysql              || test               || test1              |+--------------------+4 rows in set (0.00 sec)

显示数据库创建的时候使用的指令：

mysql> SHOW CREATE DATABASE test1;+----------+----------------------------------------------------------------+| Database | Create Database                                                |+----------+----------------------------------------------------------------+| test1    | CREATE DATABASE `test1` /*!40100 DEFAULT CHARACTER SET utf8 */ |+----------+----------------------------------------------------------------+1 row in set (0.00 sec)

创建与配置文件不一样的数据库编码方式（例GBK）：

mysql> CREATE DATABASE IF NOT EXISTS test2 CHARACTER SET gbk;Query OK, 1 row affected (0.00 sec)mysql> SHOW CREATE DATABASE test2;+----------+---------------------------------------------------------------+| Database | Create Database                                               |+----------+---------------------------------------------------------------+| test2    | CREATE DATABASE `test2` /*!40100 DEFAULT CHARACTER SET gbk */ |+----------+---------------------------------------------------------------+1 row in set (0.00 sec)

修改数据库编码方式修改：

mysql> ALTER DATABASE test2 CHARACTER SET utf8;

或

mysql> ALTER DATABASE test2 CHARACTER SET = utf8;

删除数据库：

mysql> DROP DATABASE test1;