ubuntu学习笔记5_MySQL基础功能及配置

1、删除即添加MySQL中的账户

SELECT user,host,password FROM user；

+------------------+----------------+-------------------------------------------+
| user             | host           | password                                  |
+------------------+----------------+-------------------------------------------+
| root             | localhost      | *30E5C36397DB0B3544FFAADC97A8D9C7CEDD3B39 |
| root             | sazabi-MS-7751 | *30E5C36397DB0B3544FFAADC97A8D9C7CEDD3B39 |
| root             | 127.0.0.1      | *30E5C36397DB0B3544FFAADC97A8D9C7CEDD3B39 |
| root             | ::1            | *30E5C36397DB0B3544FFAADC97A8D9C7CEDD3B39 |
|                  | localhost      |                                           |
|                  | sazabi-MS-7751 |                                           |
| debian-sys-maint | localhost      | *9069E49CA5B1AB9087698A501A9D02FB8F9AFD7A |
+------------------+----------------+-------------------------------------------+

这里面包含了很多的非root用户，我们只让root用户可以在本地登陆。因此要先删除不必要的账户

首先要删除非root用户

mysql> DELETE FROM user WHERE user !='root';

mysql> SELECT user,host,password FROM user;
+------+----------------+-------------------------------------------+
| user | host           | password                                  |
+------+----------------+-------------------------------------------+
| root | localhost      | *30E5C36397DB0B3544FFAADC97A8D9C7CEDD3B39 |
| root | sazabi-MS-7751 | *30E5C36397DB0B3544FFAADC97A8D9C7CEDD3B39 |
| root | 127.0.0.1      | *30E5C36397DB0B3544FFAADC97A8D9C7CEDD3B39 |
| root | ::1            | *30E5C36397DB0B3544FFAADC97A8D9C7CEDD3B39 |
+------+----------------+--------------------------------------

创建账户：

mysql> GRANT ALL ON *.* TO rick@localhost IDENTIFIED BY 'secretpassword';
Query OK, 0 rows affected (0.00 sec)


mysql> SELECT user,host,password FROM user;
+------+----------------+-------------------------------------------+
| user | host           | password                                  |
+------+----------------+-------------------------------------------+
| root | localhost      | *30E5C36397DB0B3544FFAADC97A8D9C7CEDD3B39 |
| root | sazabi-MS-7751 | *30E5C36397DB0B3544FFAADC97A8D9C7CEDD3B39 |
| root | 127.0.0.1      | *30E5C36397DB0B3544FFAADC97A8D9C7CEDD3B39 |
| root | ::1            | *30E5C36397DB0B3544FFAADC97A8D9C7CEDD3B39 |
| rick | localhost      | *F89FFE84BFC48A876BC682C4C23ABA4BF64711A4 |
+------+----------------+-------------------------------------------+

在每次进入相应账户时，需要使用

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

先确定要使用的数据库，不然则输入相应mysql指令会显示

ERROR 1046 (3D000): No database selected

而可使用的数据库可以通过

mysql> show databases
    -> ;

来显示出来如下：

mysql> show databases
    -> ;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| rick               |
| test               |
+--------------------+
5 rows in set (0.00 sec)

创建新的数据库

1、进入要创建数据库的账户；

2、创建数据库

mysql> CREATE DATABASE sazabi;
Query OK, 1 row affected (0.00 

3、使用新创建的数据库

mysql> use sazabi;
Database changed

4、创建表

mysql> CREATE TABLE children(
    -> childnu INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY,
    -> fname VARCHAR(30),
    -> AGE INTEGER);
Query OK, 0 rows affected (0.07 sec)

5、插入数据

mysql> INSERT INTO children(fname,age) VALUES("Jenny",21);
Query OK, 1 row affected (0.02 sec)


mysql> INSERT INTO children(fname,age) VALUES("Mike",19);
Query OK, 1 row affected (0.02 sec)

mysql> SELECT childnu,fname,age FROM children;
+---------+-------+------+
| childnu | fname | age  |
+---------+-------+------+
|       1 | Jenny |   21 |
|       2 | Mike  |   19 |
+---------+-------+------+
2 rows in set (0.00 sec)