首页 > 代码库 > mysql数据库基础命令汇总2
mysql数据库基础命令汇总2
14 在test表中字段name前插入age字段 类型tinyint（2）
mysql> create table test (id int(4) not null, name varchar(16) not null);
Query OK, 0 rows affected (0.36 sec)
mysql> desc test;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(4) | NO | | NULL | |
| name | varchar(16) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.10 sec)
mysql> alter table test add age tinyint(2) after id;
Query OK, 0 rows affected (0.47 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc test;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(4) | NO | | NULL | |
| age | tinyint(2) | YES | | NULL | |
| name | varchar(16) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql>
15 不退出数据库备份oldboy数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| bbs |
| mysql |
| oldboe |
| oldbou |
| oldboy |
| performance_schema |
| test |
| wordpress |
| www |
| xinjia |
| xu |
+--------------------+
12 rows in set (0.00 sec)
mysql> system mysqldump -uroot -poldboy123 -B oldboy > /opt/oldboy.sql;
Warning: Using a password on the command line interface can be insecure.
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don‘t want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.
mysql> \q
Bye
[root@root ~]# cd /opt
[root@root opt]# ll
total 8
-rw-r--r-- 1 root root 2222 May 15 11:09 oldboy.sql
drwxr-xr-x. 2 root root 4096 Mar 26 2015 rh
[root@root opt]# cat oldboy.sql
-- MySQL dump 10.13 Distrib 5.6.34, for linux-glibc2.5 (x86_64)
--
-- Host: localhost Database: oldboy
-- ------------------------------------------------------
-- Server version 5.6.34-log
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE=‘+00:00‘ */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CH
16 删除test表中的所有数据，并查看
mysql> desc test;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(4) | NO | | NULL | |
| age | tinyint(2) | YES | | NULL | |
| name | varchar(16) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> truncate test; 查看删除test表中的数据后查看的内容
Query OK, 0 rows affected (0.19 sec)
mysql>
17 删除表test和oldboy数据库并查看
mysql> show tables;
+------------------+
| Tables_in_oldboy |
+------------------+
| test |
+------------------+
1 row in set (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| bbs |
| mysql |
| oldboe |
| oldbou |
| oldboy |
| performance_schema |
| test |
| wordpress |
| www |
| xinjia |
| xu |
+--------------------+
12 rows in set (0.00 sec)
mysql> drop table test;
Query OK, 0 rows affected (0.14 sec)
mysql> show tables;
Empty set (0.00 sec)
mysql> drop databases oldboy;
ERROR 1064 (42000): 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 ‘databases oldboy‘ at line 1
mysql> drop database oldboy;
Query OK, 0 rows affected (0.12 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| bbs |
| mysql |
| oldboe |
| oldbou |
| performance_schema |
| test |
| wordpress |
| www |
| xinjia |
| xu |
+--------------------+
11 rows in set (0.00 sec)
mysql>
18 不退出数据库回复以上删除数据库
mysql> source /opt/oldboy.sql 恢复数据库
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
19 在把id列设置为主键，在name字段上创建普通索引
mysql> create table test(id int(4) not null,name char(16) not null,primary key (id));
ERROR 1050 (42S01): Table ‘test‘ already exists
mysql> create table coco(id int(4) not null,name char(16) not null,primary key (iid)); 添加主键
Query OK, 0 rows affected (0.11 sec)
mysql>
mysql> alter table coco add index index_name(name);创建普通索引
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
20 在字段name后插入手机号字段（shouji）类型char（11）
mysql> alter table coco add shouji char(11) after name;
Query OK, 0 rows affected (0.10 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc coco;
+--------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| id | int(4) | NO | PRI | NULL | |
| name | char(16) | NO | MUL | NULL | |
| shouji | char(11) | YES | | NULL | |
+--------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql>
21 所有字段上插入2条记录（自行设定数据）
mysql> insert into coco(id,name,shouji) values(1,‘keke‘,‘1211211‘),(2,‘xinpan‘,‘112322112‘);
Query OK, 2 rows affected (0.03 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> FLUSH PRIVILEGES;刷新数据库
Query OK, 0 rows affected (0.01 sec)
22 删除name列的索引
23 查询手机号以12开头的。名字为keke的记录
mysql> select * from coco where shouji like ‘12%‘ and name like ‘keke‘;
+----+------+---------+
| id | name | shouji |
+----+------+---------+
| 1 | keke | 1211211 |
+----+------+---------+
1 row in set (0.10 sec)
mysql>
24 删除oldboy用户
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.% |
| bbs | 172.16.1.8 |
| root | ::1 |
| | localhost |
| bbs | localhost |
| oldboy | localhost |
| root | localhost |
| | root |
| root | root |
+--------+------------+
15 rows in set (0.10 sec)
mysql> drop user oldboy@‘localhost‘;
Query OK, 0 rows affected (0.14 sec)
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.% |
| bbs | 172.16.1.8 |
| root | ::1 |
| | localhost |
| bbs | localhost |
| root | localhost |
| | root |
| root | root |
+--------+------------+
14 rows in set (0.00 sec)
mysql>
25 收回oldboy用户的select权限
revoke select on oldboy.* from oldboy@‘localhost‘;
shell终端执行 使用-e参数调用mysql内部命令
mysql -uroot -poldboy123 -e "show grants for root@‘localhost‘" | grep -i select
26 删除oldboy数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| bbs |
| mysql |
| oldboe |
| oldbou |
| oldboy |
| performance_schema |
| test |
| wordpress |
| www |
| xinjia |
| xu |
+--------------------+
12 rows in set (0.01 sec)
mysql> drop database oldboy;
Query OK, 4 rows affected (0.13 sec)
mysql>
本文出自 “小辛” 博客，谢绝转载！
mysql数据库基础命令汇总2