mysql语句的基本操作
实践练习环境：直接在生产环境中操作
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'; #撤回权限
上一篇：数据库事务的四大特性和隔离级别
下一篇：用户和权限管理