mysql语句的基本操作

432qw

实践练习环境：直接在生产环境中操作
OS：CentOS6.8
具体操作流程如下：
Last login: Wed Aug 10 08:07:15 2016 from ********
欢迎登录*************CentOS服务器!
[sky@sky9896 ~]$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 114094
Server version: 5.5.49-cll-lve MySQL Community Server (GPL) by Atomicorp
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> create database a  #创建数据库a
    -> ;
Query OK, 1 row affected (0.08 sec)
mysql> show databases;  #显示所有数据库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| a                  |
| back20150625ultrax |
| cacti              |
| cacti20151220      |
| cacti20160104      |
| feifeicms          |
| mysql              |
| performance_schema |
| phpcom             |
| study              |
| syslog             |
| test               |
| test1              |
| tt                 |
| ultrax             |
+--------------------+
16 rows in set (0.16 sec)
mysql> use a   #打开数据库
Database changed
mysql> show tables;
Empty set (0.00 sec)
mysql> create table a1(id int);  #创建a1表
Query OK, 0 rows affected (0.22 sec)
mysql> show tables;   #显示所有表；
+-------------+
| Tables_in_a |
+-------------+
| a1          |
+-------------+
1 row in set (0.00 sec)
mysql> describe a1  #显示表结构
    -> ;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> show engines; #查看引擎
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)
[sky@sky9896 ~]$ mysql -u root-p  #连接数据库
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 114209
Server version: 5.5.49-cll-lve MySQL Community Server (GPL) by Atomicorp
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> exit   #退出
Bye
mysql> use a;
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> create table a2(
    -> id int unsigned not null  auto_increment, #不能为空，自动增加数值
    -> name char(40) not null default ' ', #不能为空，默认为空
    -> info char(200) null,
    -> primary key(id));#设置id为主键
Query OK, 0 rows affected (0.12 sec)
mysql> describe a2;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | char(40)         | NO   |     |         |                |
| info  | char(200)        | YES  |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> show tables;
+-------------+
| Tables_in_a |
+-------------+
| a1          |
| a2          |
+-------------+
2 rows in set (0.00 sec)
mysql> insert into a2(id,name,info)values('1','wuhaiming','参加项管考试 ');  #向a2表插入一条记录
Query OK, 1 row affected, 1 warning (0.07 sec)
mysql> select * from a2;
+----+-----------+--------+
| id | name      | info   |
+----+-----------+--------+
|  1 | wuhaiming | ?????? |
+----+-----------+--------+
1 row in set (0.00 sec)
mysql> select  id from a2;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)
mysql> insert into a2 values(2,'a2','sky9890');
Query OK, 1 row affected (0.04 sec)
mysql> select * from a2;
+----+-----------+---------+
| id | name      | info    |
+----+-----------+---------+
|  1 | wuhaiming | ??????  |
|  2 | a2        | sky9890 |
+----+-----------+---------+
2 rows in set (0.00 sec)
mysql> insert into a2(id,name)values('','whm');
Query OK, 1 row affected, 1 warning (0.06 sec)
mysql> select * from a2;
+----+-----------+---------+
| id | name      | info    |
+----+-----------+---------+
|  1 | wuhaiming | ??????  |
|  2 | a2        | sky9890 |
|  3 | whm       | NULL    |
+----+-----------+---------+
3 rows in set (0.00 sec)
mysql> describe a2;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | char(40)         | NO   |     |         |                |
| info  | char(200)        | YES  |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> insert into a2 values('','a6','sky9890'),('','a7',);#插入一条记录
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 ')' at line 1
mysql> insert into a2 values('','a6','sky9890'),('','a7','sky'); #连续插入两条记录
Query OK, 2 rows affected, 2 warnings (0.17 sec)
Records: 2  Duplicates: 0  Warnings: 2
mysql> select * from a2;
+----+-----------+---------+
| id | name      | info    |
+----+-----------+---------+
|  1 | wuhaiming | ??????  |
|  2 | a2        | sky9890 |
|  3 | whm       | NULL    |
|  4 | a6        | sky9890 |
|  5 | a7        | sky     |
+----+-----------+---------+
5 rows in set (0.00 sec)
mysql> describe a1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> describe a2;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | char(40)         | NO   |     |         |                |
| info  | char(200)        | YES  |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> select * from a1;
Empty set (0.00 sec)
mysql> insert into a1(id) select id from a2;
Query OK, 5 rows affected (0.06 sec)
Records: 5  Duplicates: 0  Warnings: 0
mysql> select * from a1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+
5 rows in set (0.00 sec)
mysql> select * from a2;
+----+-----------+---------+
| id | name      | info    |
+----+-----------+---------+
|  1 | wuhaiming | ??????  |
|  2 | a2        | sky9890 |
|  3 | whm       | NULL    |
|  4 | a6        | sky9890 |
|  5 | a7        | sky     |
+----+-----------+---------+
5 rows in set (0.00 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| a                  |
| back20150625ultrax |
| cacti              |
| cacti20151220      |
| cacti20160104      |
| feifeicms          |
| mysql              |
| performance_schema |
| phpcom             |
| study              |
| syslog             |
| test               |
| test1              |
| tt                 |
| ultrax             |
+--------------------+
16 rows in set (0.00 sec)
mysql> drop database b; #删除b数据库
ERROR 1008 (HY000): Can't drop database 'b'; database doesn't exist
mysql> show tables;
+-------------+
| Tables_in_a |
+-------------+
| a1          |
| a2          |
+-------------+
2 rows in set (0.00 sec)
mysql> drop tables  a1;#删除a1表
Query OK, 0 rows affected (0.09 sec)
mysql> show tables;
+-------------+
| Tables_in_a |
+-------------+
| a2          |
+-------------+
1 row in set (0.00 sec)
mysql> delete from a2 where id=5 or info='sky'; #删除一条记录
Query OK, 1 row affected (0.05 sec)
mysql> select * from a2;
+----+-----------+---------+
| id | name      | info    |
+----+-----------+---------+
|  1 | wuhaiming | ??????  |
|  2 | a2        | sky9890 |
|  3 | whm       | NULL    |
|  4 | a6        | sky9890 |
+----+-----------+---------+
4 rows in set (0.00 sec)
mysql> update a2 set  info='sky' where id=1; #更新一个字段值
Query OK, 1 row affected (0.07 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> select * from a2;
+----+-----------+---------+
| id | name      | info    |
+----+-----------+---------+
|  1 | wuhaiming | sky     |
|  2 | a2        | sky9890 |
|  3 | whm       | NULL    |
|  4 | a6        | sky9890 |
+----+-----------+---------+
4 rows in set (0.00 sec)
mysql> SHOW VARIABLES LIKE 'character%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
mysql> SET collation_database = utf8 ;
ERROR 1273 (HY000): Unknown collation: 'utf8'
mysql> set character_set_database=utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> update a2 set  info='吴海明' where id=1;         
Query OK, 1 row affected, 1 warning (0.06 sec)
Rows matched: 1  Changed: 1  Warnings: 1
mysql> select * from a2;
+----+-----------+---------+
| id | name      | info    |
+----+-----------+---------+
|  1 | wuhaiming | ???     |
|  2 | a2        | sky9890 |
|  3 | whm       | NULL    |
|  4 | a6        | sky9890 |
+----+-----------+---------+
4 rows in set (0.00 sec)
mysql> alter table a2 rename a1 #修改表名
    -> ;
Query OK, 0 rows affected (0.06 sec)
mysql> show tables;
+-------------+
| Tables_in_a |
+-------------+
| a1          |
+-------------+
1 row in set (0.00 sec)
mysql> select * from a2;      
ERROR 1146 (42S02): Table 'a.a2' doesn't exist
mysql> select * from a2;
ERROR 1146 (42S02): Table 'a.a2' doesn't exist
mysql> select * from a1;
+----+-----------+---------+
| id | name      | info    |
+----+-----------+---------+
|  1 | wuhaiming | ???     |
|  2 | a2        | sky9890 |
|  3 | whm       | NULL    |
|  4 | a6        | sky9890 |
+----+-----------+---------+
4 rows in set (0.00 sec)
mysql> alter table a1 change info information char(200);#更改字段名
Query OK, 4 rows affected (0.26 sec)
Records: 4  Duplicates: 0  Warnings: 0
mysql> describe a1;
+-------------+------------------+------+-----+---------+----------------+
| Field       | Type             | Null | Key | Default | Extra          |
+-------------+------------------+------+-----+---------+----------------+
| id          | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name        | char(40)         | NO   |     |         |                |
| information | char(200)        | YES  |     | NULL    |                |
+-------------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> alter table a1 add time date;#在末尾填加列
Query OK, 4 rows affected (0.26 sec)
Records: 4  Duplicates: 0  Warnings: 0
mysql> describe a1;
+-------------+------------------+------+-----+---------+----------------+
| Field       | Type             | Null | Key | Default | Extra          |
+-------------+------------------+------+-----+---------+----------------+
| id          | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name        | char(40)         | NO   |     |         |                |
| information | char(200)        | YES  |     | NULL    |                |
| time        | date             | YES  |     | NULL    |                |
+-------------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> alter table a1 drop time; #删除列
Query OK, 4 rows affected (0.26 sec)
Records: 4  Duplicates: 0  Warnings: 0
mysql> describe a1;
+-------------+------------------+------+-----+---------+----------------+
| Field       | Type             | Null | Key | Default | Extra          |
+-------------+------------------+------+-----+---------+----------------+
| id          | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name        | char(40)         | NO   |     |         |                |
| information | char(200)        | YES  |     | NULL    |                |
+-------------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> alter table a1 add time date first;#插入到第一列
Query OK, 4 rows affected (0.24 sec)
Records: 4  Duplicates: 0  Warnings: 0
mysql> describe a1;                       
+-------------+------------------+------+-----+---------+----------------+
| Field       | Type             | Null | Key | Default | Extra          |
+-------------+------------------+------+-----+---------+----------------+
| time        | date             | YES  |     | NULL    |                |
| id          | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name        | char(40)         | NO   |     |         |                |
| information | char(200)        | YES  |     | NULL    |                |
+-------------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> select * from a1;
+------+----+-----------+-------------+
| time | id | name      | information |
+------+----+-----------+-------------+
| NULL |  1 | wuhaiming | ???         |
| NULL |  2 | a2        | sky9890     |
| NULL |  3 | whm       | NULL        |
| NULL |  4 | a6        | sky9890     |
+------+----+-----------+-------------+
4 rows in set (0.00 sec)
mysql> alter table a1 add nian year after time;#插入到指定列后
Query OK, 4 rows affected (0.25 sec)
Records: 4  Duplicates: 0  Warnings: 0
mysql> describe a1;
+-------------+------------------+------+-----+---------+----------------+
| Field       | Type             | Null | Key | Default | Extra          |
+-------------+------------------+------+-----+---------+----------------+
| time        | date             | YES  |     | NULL    |                |
| nian        | year(4)          | YES  |     | NULL    |                |
| id          | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name        | char(40)         | NO   |     |         |                |
| information | char(200)        | YES  |     | NULL    |                |
+-------------+------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql> grant all on cacti.* to 'a1'@'localhost' identified by '123456';#授权
Query OK, 0 rows affected (0.02 sec)
mysql> exit
Bye
mysql> grant create,select on *.* to  'a2'@'localhost' identified by '123'; #授权
Query OK, 0 rows affected (0.00 sec)
mysql> revoke select on  *.*  from  'a2'@'localhost'; #撤回权限