首页 > 代码库 > 【风马一族_mysql】mysql基本指令
【风马一族_mysql】mysql基本指令
数据库常用的指令:
- 创建数据库
- mysql>create database 数据库名 ;
- 显示数据库
- mysql>show databases;
- +--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| twwq |
| xhkdb |
+--------------------+
6 rows in set (0.02 sec)
- 删除数据库
- drop database 数据库名;
- mysql> drop database twwq;
Query OK, 0 rows affected (0.05 sec)
- 连接数据库
- use 数据库名;
- mysql>use twwq;
Database changed
- 查看 当前连接的数据库
- mysql>select database();
+------------+
| database() |
+------------+
| twwq |
+------------+
1 row in set (0.00 sec)
- mysql>select database();
数据表的常用指令
- 创建表
- create table 表名(<字段名1> <类型1> [,..<字段名n> <类型n>]); //警告:没有给表填加字段,会出错
- mysql> create table registered(id int(8) not null primary key auto_increment,
-> username char(20) not nul
-> password char(20) not null,
-> repassword char(20) not null);
Query OK, 0 rows affected (0.19 sec)
- 查看表
- mysql>desc 表名 ; (方式一) mysql> show columns from 表名; (方式二)
- mysql>desc registered; (方式一) mysql> show columns from 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 | |
+------------+----------+------+-----+---------+----------------+
4 rows in set (0.01 sec)
- 修改表
- rename table (原先的)表名 to (修改后的)表;
- mysql> rename table registered to sows;
Query OK, 0 rows affected (0.06 sec) - 查看效果
- mysql> desc sows;
+------------+----------+------+-----+---------+----------------+
| 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 | |
+------------+----------+------+-----+---------+----------------+
4 rows in set (0.01 sec)
- mysql> desc sows;
- 删除表
- mysql>drop table 表名; //警告:永久性删除数据表,慎用
- 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 //原因表名被修改,因此原先的表名,无法使用了
- mysql> desc registered;
- 修改字段
- 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)
- mysql> desc registered;
- 删除字段
- 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)
- mysql> desc registered;
- 加索引
- 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)
- mysql> desc registered;
- 删除索引
- 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> desc registered;
- 增添字段
- 插入数据
- 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 registered;
- 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> select * from registered order by id limit 1,2;
- mysql>select * from 数据表; //获取表中所有数据
- 删除表中的数据
- 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> select * from registered;
- 修改表中的数据
- 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> select * from registered;
- mysql> update registered set username=‘sowsceo‘ where id=2;
- mysql>update 数据表 set (要修改数据的)字段=修改后的数据 where 条件限制
【风马一族_mysql】mysql基本指令
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。