学习MySQL笔记(1)

前提:

数据库安装目录为：E:\work2\MySQL\db;root用户的密码为：userpassword

 

 

1、进入mysql的编辑页面：

   在dos系统中进入E:\work2\MySQL\db\bin目录下，

       

C:\Documents and Settings\hjh>e: E:\>cd "E:\work2\MySQL\db\bin"

 

E:\work2\MySQL\db\bin>

 

 

 用root用户登录，输入命令: mysql -uroot -p 回车
 然后再Enter password: 后面输入密码：userpassword 回车

 最后就进入MySQL的编辑页面。

 

E:\work2\MySQL\db\bin>mysql -uroot -p Enter password: *********** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 16 Server version: 5.1.24-rc-community MySQL Community Server (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql>

 

 

 

 2、显示所有的数据库： show databases;

 

 

mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | abccs | | mysql | +--------------------+ 3 rows in set (0.00 sec) mysql>

 

 

 

3、进入某个数据库(如mysql--- )：use mysql;

 

 

 

 

 

 

 

mysql> use mysql; Database changed mysql>

 

 

 

4、在进入某个数据库后，显示它的所有表：show tables;

 

 

mysql> use mysql; Database changed mysql> show tables; +---------------------------+ | Tables_in_mysql | +---------------------------+ | columns_priv | | db | | event | | func | | general_log | | help_category | | help_keyword | | help_relation | | help_topic | | host | | plugin | | proc | | procs_priv | | slow_log | | tables_priv | | time_zone | | time_zone_leap_second | | time_zone_name | | time_zone_transition | | time_zone_transition_type | | user | +---------------------------+ 21 rows in set (0.06 sec) mysql>

 

 

 

5、修改root用户的密码：

 

 (1)、进入到E:\work2\MySQL\db\bin目录下，

D:\work2\db\bin>

 

 

 

  输入： mysqladmin -uroot -p password 新密码

D:\work2\db\bin>mysqladmin -uroot -p password test1 Enter password:

 

 

 test1 是新密码，在Enter password：后面输入原密码userpassword

 

D:\work2\db\bin>mysqladmin -uroot -p password test1 Enter password: ******** D:\work2\db\bin>

 

 

  用新密码登录验证是否修改成功：

 

D:\work2\db\bin>mysql -uroot -p Enter password: ***** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 12 Server version: 5.1.24-rc-community MySQL Community Server (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql>

 

 

 

 

 

 

 

 

 

 

 

(2)、进入MySQL

  

D:\work2\db\bin>mysql -uroot -p Enter password: ***** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 12 Server version: 5.1.24-rc-community MySQL Community Server (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql>

 

 

 

 进入mysql数据库，

 

 

 

 

 

 然后运行：UPDATE user SET Password=PASSWORD(’new_password’) WHERE user=’root’;

 

mysql> use mysql; Database changed mysql> UPDATE user SET Password=PASSWORD('test1') WHERE user='root'; Query OK, 0 rows affected (0.00 sec) Rows matched: 2 Changed: 0 Warnings: 0 mysql>

 

 最后运行：FLUSH PRIVILEGES;

 

 

 

 

 

 

 

 

mysql> use mysql; Database changed mysql> UPDATE user SET Password=PASSWORD('test1') WHERE user=’root’; ERROR 1300 (HY000): Invalid utf8 character string: '’root’' mysql> use mysql; Database changed mysql> UPDATE user SET Password=PASSWORD('test1') WHERE user='root'; Query OK, 0 rows affected (0.00 sec) Rows matched: 2 Changed: 0 Warnings: 0 mysql> FLUSH PRIVILEGES; ERROR 1146 (42S02): Table 'mysql.servers' doesn't exist mysql>

 

 

 

6、创建数据库并且创建对新数据库有所有权限的账户

 

  使用root账户进入MySQL后，

  若数据库aaa存在，则删除： DROP DATABASE IF EXISTS aaa;

 

D:\work2\db\bin>mysql -uroot -p Enter password: ******** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 22 Server version: 5.1.24-rc-community MySQL Community Server (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> DROP DATABASE IF EXISTS aaa; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql>

 

 

 创建数据库aaa,并设置数据库的运行的语言是utf8: CREATE DATABASE IF NOT EXISTS aaa CHARACTER SET utf8;

 

 

mysql> CREATE DATABASE IF NOT EXISTS aaa CHARACTER SET utf8; Query OK, 1 row affected (0.00 sec) mysql>

 

创建账户test_user:GRANT ALL PRIVILEGES ON  aaa .* TO 'test_user'@'localhost' IDENTIFIED BY 'testpassword' WITH GRANT OPTION;

  

ALL PRIVILEGES --- 表示所有权限

'test_user'@'localhost' --- 表示主机上的test_user用户

testpassword --- 是密码

 

mysql> GRANT ALL PRIVILEGES ON aaa .* TO 'test_user'@'localhost' IDENTIFIED BY 'testpassword' WITH GRANT OPTION; Query OK, 0 rows affected (0.00 sec) mysql> GRANT ALL PRIVILEGES ON UniMonDB.* TO 'test_user'@'%' IDENTIFIED BY 'testpassword' WITH GRANT OPTION; Query OK, 0 rows affected (0.00 sec) mysql>

 

 

 

 然后再创建非本机的账户test_user：GRANT ALL PRIVILEGES ON UniMonDB.* TO 'test_user'@'%' IDENTIFIED BY 'testpassword' WITH GRANT OPTION;

 ALL PRIVILEGES --- 表示所有权限

 'test_user'@'localhost' --- 表示非主机上的test_user用户

 testpassword --- 是密码

 

mysql> GRANT ALL PRIVILEGES ON aaa .* TO 'test_user'@'localhost' IDENTIFIED BY 'testpassword' WITH GRANT OPTION; 

Query OK, 0 rows affected (0.00 sec) mysql> GRANT ALL PRIVILEGES ON UniMonDB.* TO 'test_user'@'%' IDENTIFIED BY 'testpassword' WITH GRANT OPTION; Query OK, 0 rows affected (0.00 sec) mysql>