MySQL学习笔记-安装和基本操作

MySQL学习笔记-安装和基本操作

 

1.安装MySQL

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

 

添加/etc/my.conf配置：

1 vi /etc/my.conf
2 [mysqld]
3 default-character-set=utf8
4 character_set_server=utf8
5 [client]
6 default-character-set=utf8

 

2.启动和停止MySQL服务：

# service mysqld start
# service mysqld stop

 

3.登录和退出MySQL，设置mysql密码，允许远程登录mysql：

1 # mysql -uroot      //登录mysql

1 mysql> exit       //退出mysql

 

 

 4.设置mysql密码 

mysql>; USE mysql; 
mysql>; UPDATE user SET Password=PASSWORD('newpassword') WHERE user='root'; 
mysql>; FLUSH PRIVILEGES;

 设置mysql密码还可以用：mysql_secure_installation 命令

 

5.允许远程登录 

1 mysql -u root -p 

 

Enter Password: <your new password> 
mysql>GRANT ALL PRIVILEGES ON *.* TO '用户名'@'%' IDENTIFIED BY '密码' WITH GRANT OPTION; 

完成后就能用mysql-front远程管理mysql了。 

 

 6.修改MySQL提示符

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

 

或连接进MySQL后修改:

mysql> prompt <提示符>

 

 

5.MySQL提示符：

\D	完整的日期
\d	当前数据库
\h	服务器名称
\u	当前用户

 

例：

1 # mysql -uroot --prompt '\u@\h \d'
2 root@localhost (none)

 

 

　　或

mysql> prompt \u@\h \d>
PROMPT set to '\u@\h \d>'
root@localhost (none)>

 

 

 

6.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;