实践练习环境:直接在生产环境中操作
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’; #撤回权限