mysql 库基础命令汇总
1 登录mysql数据库
[root@db02--52 ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.6.34-log Source distribution
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
2 查看当前登录的用户：
mysql> select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.20 sec)
mysql>
3 创建数据库haha，并查看已建库完整语句
mysql> create database haha; 创建数据库haha
Query OK, 1 row affected (0.00 sec)
mysql> show create database haha;查看刚健的数据库
+----------+---------------------------------------------------------------+
| Database | Create Database |
+----------+---------------------------------------------------------------+
| haha | CREATE DATABASE `haha` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+---------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show databases; 查看所有的数据库
+--------------------+
| Database |
+--------------------+
| information_schema |
| haha |
| mysql |
| oldboy |
| oldgril |
| performance_schema |
| xinpan |
| xu |
+--------------------+
8 rows in set (0.00 sec)
mysql>
4 创建用户hehe 使之可以管理数据库haha
mysql> create user hehe@'localhost' identified by 'oldboy123'
-> ;创建本地用户hehe 授权数据库密码
Query OK, 0 rows affected (0.06 sec)
mysql> grant all on haha.* to hehe@'localhost';haha为数据库，hehe为用户
Query OK, 0 rows affected (0.00 sec)
5 查看创建的用户hehe拥有哪些权限
mysql> show grants for hehe@'localhost';查看创建的用户hehe拥有哪些权限
+-------------------------------------------------------------------------------------------------------------+
| Grants for hehe@localhost |
+-------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'hehe'@'localhost' IDENTIFIED BY PASSWORD '*FE28814B4A8B3309DAC6ED7D3237ADED6DA1E515' |
| GRANT ALL PRIVILEGES ON `oldboy`.* TO 'hehe'@'localhost' |
| GRANT ALL PRIVILEGES ON `hehe`.* TO 'hehe'@'localhost' |
| GRANT ALL PRIVILEGES ON `haha`.* TO 'hehe'@'localhost' |
+-------------------------------------------------------------------------------------------------------------+
4 rows in set (0.03 sec)
6 查看当前数据库里有哪些用户。
mysql>
mysql> select user,host from mysql.user;
+-----------+------------+
| user | host |
+-----------+------------+
| keke | % |
| oldboy | % |
| xinjia | % |
| mha | 10.0.0.% |
| rep | 10.0.0.% |
| root | 127.0.0.1 |
| bbs | 172.16.1.% |
| wordpress | 172.16.1.% |
| root | db02--52 |
| haha | localhost |
| hehe | localhost |
| oldboy | localhost |
| root | localhost |
| system | localhost |
+-----------+------------+
14 rows in set (0.00 sec)
mysql>
6 进入oldboy数据库
mysql> use oldboy
Database changed
mysql>
7 查看当前所在的数据库
mysql> select database();
+------------+
| database() |
+------------+
| oldboy |
+------------+
1 row in set (0.00 sec)
mysql>
8 创建一张表xiaoke 字段id和name varchar（16）
mysql> create table xiaoke ( id int(4) not null, name varchar (16) not null);
Query OK, 0 rows affected (0.63 sec)
mysql>
9查看建表结构以及表结构的sql语句
mysql> desc xiaoke 查看表的结构
-> ;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(4) | NO | | NULL | |
| name | varchar(16) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.03 sec)
查看表结构的sql语句
mysql> show full columns from xiaoke; 查看表结构的sql语句
+-------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+-------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| id | int(4) | NULL | NO | | NULL | | select,insert,update,references | |
| name | varchar(16) | utf8_general_ci | NO | | NULL | | select,insert,update,references | |
+-------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
2 rows in set (0.00 sec)
mysql>
10 插入一条数据“1，aini”
mysql> insert into test(id,name) values(1,'aini');
Query OK, 1 row affected (0.02 sec)
mysql> select * from test;
+----+------+--------+
| id | age | name |
+----+------+--------+
| 1 | NULL | oldboy |
| 1 | NULL | oldboy |
| 1 | NULL | aini |
+----+------+--------+
3 rows in set (0.02 sec)
mysql>
11 再批量插入2行数据“2，xiaoxu”“3，xiaoxin”
mysql> insert into test(id,name) values(2,'xaioke'),(3,'xiaoxin');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from test;
+----+------+---------+
| id | age | name |
+----+------+---------+
| 1 | NULL | oldboy |
| 1 | NULL | oldboy |
| 1 | NULL | aini |
| 2 | NULL | xaioke |
| 3 | NULL | xiaoxin |
+----+------+---------+
5 rows in set (0.00 sec)
12 查询名字为oldboy的记录
mysql> select * from test where name='oldboy';
+----+------+--------+
| id | age | name |
+----+------+--------+
| 1 | NULL | oldboy |
| 1 | NULL | oldboy |
+----+------+--------+
2 rows in set (0.00 sec)
mysql>
13 把数据库id等于1的名字oldboy更改为oldgril
mysql> select * from test where name='oldboy';
+----+------+--------+
| id | age | name |
+----+------+--------+
| 1 | NULL | oldboy |
| 1 | NULL | oldboy |
+----+------+--------+
2 rows in set (0.00 sec)
mysql> updata test set name='oldgirl' where id='1';
mysql> select * from test;
+----+------+---------+
| id | age | name |
+----+------+---------+
| 1 | NULL | oldgirl |
| 1 | NULL | oldgirl |
| 1 | NULL | oldgirl |
| 2 | NULL | xaioke |
| 3 | NULL | xiaoxin |
+----+------+---------+
5 rows in set (0.00 sec)
mysql>
未完待续。。。。。