mysql语句的基本操作

2024-04-02 19:55

短信预约 -IT技能 免费直播动态提醒

实践练习环境：直接在生产环境中操作

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

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> 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> 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

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> 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> 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> 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> 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> 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> alter table a1 add time date;#在末尾填加列
Query OK, 4 rows affected (0.26 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> alter table a1 drop time; #删除列
Query OK, 4 rows affected (0.26 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> alter table a1 add time date first;#插入到第一列
Query OK, 4 rows affected (0.24 sec)
Records: 4 Duplicates: 0 Warnings: 0

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> 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'; #撤回权限

免责声明：

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

阅读原文内容投诉