【风马一族_mysql】mysql基本指令
时间：2021-07-01 10:21:17
帮助过：2人阅读
mysql>drop table registered;
Query OK, 0 rows affected (0.05 sec)
增加 alter add命令用来增加表的字段。
- 增添字段
- alter table 数据表 add 字段 参数 其他;
- mysql> alter table registered add sex char(2);
Query OK, 0 rows affected (0.33 sec)
Records: 0 Duplicates: 0 Warnings: 0
- 查看效果
- mysql> desc registered;
+------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+----------+------+-----+---------+----------------+
| id | int(8) | NO | PRI | NULL | auto_increment |
| username | char(20) | NO | | NULL | |
| password | char(20) | NO | | NULL | |
| repassword | char(20) | NO | | NULL | |
| sex | char(2) | YES | | NULL | | //增加sex 字段
+------------+----------+------+-----+---------+----------------+
5 rows in set (0.01 sec)
- mysql> desc registered;
ERROR 1146 (42S02): Table ‘twwq.registered‘ doesn‘t exist //原因表名被修改，因此原先的表名，无法使用了
- 修改字段
- alter table 数据表 change （要被修改的）字段 （修改后的）字段 参数;
- mysql> alter table registered change username name char(20);
Query OK, 0 rows affected (0.33 sec)
Records: 0 Duplicates: 0 Warnings: 0
- 查看效果
- mysql> desc registered;
+------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+----------+------+-----+---------+----------------+
| id | int(8) | NO | PRI | NULL | auto_increment |
| name | char(20) | YES | | NULL | | // username 修改成 name
| password | char(20) | NO | | NULL | |
| repassword | char(20) | NO | | NULL | |
| sex | char(2) | YES | | NULL | |
+------------+----------+------+-----+---------+----------------+
5 rows in set (0.01 sec)
- 删除字段
- alter table 数据表 drop 字段;
- mysql> alter table registered drop sex;
Query OK, 0 rows affected (0.31 sec)
Records: 0 Duplicates: 0 Warnings: 0
- 查看效果
- mysql> desc registered;
+------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+----------+------+-----+---------+----------------+
| id | int(8) | NO | PRI | NULL | auto_increment |
| name | char(20) | YES | | NULL | |
| password | char(20) | NO | | NULL | |
| repassword | char(20) | NO | | NULL | |
+------------+----------+------+-----+---------+----------------+ //sex 字段 已经被删除
4 rows in set (0.01 sec)
- 加索引
- alter table 数据表 add index 索引名（【已经存在于表的】字段名）;
- mysql> alter table registered add index sows_name(username);
Query OK, 0 rows affected (0.17 sec)
Records: 0 Duplicates: 0 Warnings: 0
- 查看效果
- mysql> desc registered;
+------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+----------+------+-----+---------+----------------+
| id | int(8) | NO | PRI | NULL | auto_increment |
| username | char(20) | NO | MUL | NULL | | //Key 字段出现 MUL
| password | char(20) | NO | | NULL | |
| repassword | char(20) | NO | | NULL | |
| sex | char(2) | YES | | NULL | |
+------------+----------+------+-----+---------+----------------+
5 rows in set (0.01 sec)
- 删除索引
- alter table 数据表 drop index 索引名;
- mysql> alter table registered drop index sows_name;
Query OK, 0 rows affected (0.15 sec)
Records: 0 Duplicates: 0 Warnings: 0
- 查看效果
- mysql> desc registered;
+------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+----------+------+-----+---------+----------------+
| id | int(8) | NO | PRI | NULL | auto_increment |
| username | char(20) | NO | | NULL | | //key 字段的内容消失
| password | char(20) | NO | | NULL | |
| repassword | char(20) | NO | | NULL | |
| sex | char(2) | YES | | NULL | |
+------------+----------+------+-----+---------+----------------+
5 rows in set (0.01 sec)
插入数据
- mysql>insert into 数据表（字段 //如果已经实现步骤1，则不用再写字段） values (根据字段的数量，变量类型、变量长度要求、是否允许为空，来进行填写内容)
- mysql> insert into registered values (1,‘sows‘,‘asqw1234‘,‘asqw1234‘),(2,‘ceo‘,‘
aswe1322‘,‘aswe1322‘),(3,‘jk‘,‘qazxsw12‘,‘qazxsw12‘);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0 // 成功插入 3条数据
查看表中的数据 //准确性的查询所需数据
- mysql>select * from 数据表; //获取表中所有数据
- mysql>select * from registered;
+----+----------+----------+--------------+
| id | username | password | repassword |
+----+----------+----------+--------------+
| 1 | sows | asqw1234 | asqw1234 |
| 2 | ceo | aswe1322 | aswe1322 |
| 3 | jk | qazxsw12 | qazxsw12 |
+----+----------+----------+--------------+
3 rows in set (0.00 sec) //获取到3条数据
- mysql>select * from 数据表 order by id limit 从哪个位置开始，一共几条;
- mysql> select * from registered order by id limit 1,2;
+----+----------+----------+------------+
| id | username | password | repassword |
+----+----------+----------+------------+
| 2 | ceo | aswe1322 | aswe1322 |
| 3 | jk | qazxsw12 | qazxsw12 |
+----+----------+----------+------------+
2 rows in set (0.00 sec) //获取两条数据
删除表中的数据
- mysql>delect from 数据表 where 条件限制 ;
- mysql> delete from registered where id=1;
Query OK, 1 row affected (0.01 sec) //删除成功
- 使用5，查看删除的效果
- mysql> select * from registered;
+----+----------+----------+------------+
| id | username | password | repassword |
+----+----------+----------+------------+
| 2 | ceo | aswe1322 | aswe1322 |
| 3 | jk | qazxsw12 | qazxsw12 |
+----+----------+----------+------------+
2 rows in set (0.00 sec) //id为1的数据被成功删除了
修改表中的数据
- mysql>update 数据表 set （要修改数据的）字段=修改后的数据 where 条件限制
- mysql> update registered set username=‘sowsceo‘ where id=2;
Query OK, 1 row affected (0.01 sec) //修改成功
Rows matched: 1 Changed: 1 Warnings: 0 //修改的数量
- 查看修改后的效果
- mysql> select * from registered;
+----+----------+----------+------------+
| id | username | password | repassword |
+----+----------+----------+------------+
| 2 | sowsceo | aswe1322 | aswe1322 | //username 从ceo 变成 sowsceo
| 3 | jk | qazxsw12 | qazxsw12 |
+----+----------+----------+------------+
2 rows in set (0.00 sec)
【风马一族_mysql】mysql基本指令
标签：
