3

4：数据操作-MySQL

 1 year ago
source link: https://blog.51cto.com/yeatsliao/5938502
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

4.1 插入数据

1. 插入数据

 insert into 表名 （可以省略）values （不可省略的内容）

mysql> insert into teacher (id, name, phone, address) values(1,'Frank', '188888888', 'ShangHai');
Query OK, 1 row affected (0.00 sec)

注意：使用*来查询的效率非常低；在表名后面的括号中内容应与values后内容一 一对应，如果表名后面没有内容，就必须按照字段的顺序插入数据

mysql> insert into teacher values(3, 'Tom', '100000000', 'Nanjing');
Query OK, 1 row affected (0.00 sec)

mysql> select * from teacher;
+----+-------+-----------+----------+
| id | name  | phone     | address  |
+----+-------+-----------+----------+
|  1 | Frank | 188888888 | ShangHai |
|  3 | Tom   | 100000000 | Nanjing  |
+----+-------+-----------+----------+
2 rows in set (0.00 sec)

展示表结构

mysql> desc teacher;
+---------+--------------+------+-----+----------+----------------+
| Field   | Type         | Null | Key | Default  | Extra
 |
+---------+--------------+------+-----+----------+----------------+
| id      | int(11)      | NO   | PRI | NULL     | auto_increment |
| name    | varchar(30)  | NO   |     | NULL     |
 |
| phone   | varchar(20)  | YES  |     | NULL     |
 |
| address | varchar(100) | YES  |     | 暂时未知 |
 |
+---------+--------------+------+-----+----------+----------------+
4 rows in set (0.00 sec)

注意：可以看到 id 和 name 是不能为空的，而且id是自动增长的，在address中如果使用default那么将直接显示“暂时未知”

2. 数据的自动添加：NULL

mysql> insert into teacher values(NUll,'Tom',NULL,NULL);
Query OK, 1 row affected (0.00 sec)

mysql> select * from teacher;
+----+-------+-----------+----------+
| id | name  | phone     | address  |
+----+-------+-----------+----------+
|  1 | Frank | 188888888 | ShangHai |
|  3 | Tom   | 100000000 | Nanjing  |
|  4 | Tom   | NULL      | NULL     |
+----+-------+-----------+----------+

3. default的使用

mysql> insert into teacher values(NULL, 'Jerry',Null, default);
Query OK, 1 row affected (0.00 sec)

mysql> select * from teacher;
+----+-------+-----------+----------+
| id | name  | phone     | address  |
+----+-------+-----------+----------+
|  1 | Frank | 188888888 | ShangHai |
|  3 | Tom   | 100000000 | Nanjing  |
|  4 | Tom   | NULL      | NULL     |
|  5 | Jerry | NULL      | default  |
|  6 | Jerry | NULL      | 暂时未知 |
+----+-------+-----------+----------+
5 rows in set (0.00 sec)

4. 省略自增

mysql> insert into teacher (name, phone, address)values('Jerry',Null, default);
Query OK, 1 row affected (0.00 sec)

mysql> select * from teacher;
+----+-------+-----------+----------+
| id | name  | phone     | address  |
+----+-------+-----------+----------+
|  1 | Frank | 188888888 | ShangHai |
|  3 | Tom   | 100000000 | Nanjing  |
|  4 | Tom   | NULL      | NULL     |
|  5 | Jerry | NULL      | default  |
|  6 | Jerry | NULL      | 暂时未知 |
|  7 | Jerry | NULL      | 暂时未知 |
+----+-------+-----------+----------+
6 rows in set (0.00 sec)

注意：如果在表名后面不写括号中的内容，自动增长规则将会填写内容，
但名字是非NULL的，其不能省略，也不能使用NULL代替

4.2 一次性插入多条数据

mysql> insert into teacher values(NULL, 'TOM_1', NULL, default),(NULL, 'Jerry_1', NULL, default);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from teacher;
+----+---------+-----------+----------+
| id | name    | phone     | address  |
+----+---------+-----------+----------+
|  1 | Frank   | 188888888 | ShangHai |
|  3 | Tom     | 100000000 | Nanjing  |
|  4 | Tom     | NULL      | NULL     |
|  5 | Jerry   | NULL      | default  |
|  6 | Jerry   | NULL      | 暂时未知 |
|  7 | Jerry   | NULL      | 暂时未知 |
|  8 | TOM_1   | NULL      | 暂时未知 |
|  9 | Jerry_1 | NULL      | 暂时未知 |
+----+---------+-----------+----------+
8 rows in set (0.00 sec)

4.3 删除数据

delete from 表名 where 选定的字段名称=你要删除的字段相应信息

mysql> delete from teacher where id = 9;
Query OK, 1 row affected (0.00 sec)

mysql> select * from teacher;
+----+-------+-----------+----------+
| id | name  | phone     | address  |
+----+-------+-----------+----------+
|  1 | Frank | 188888888 | ShangHai |
|  3 | Tom   | 100000000 | Nanjing  |
|  4 | Tom   | NULL      | NULL     |
|  5 | Jerry | NULL      | default  |
|  6 | Jerry | NULL      | 暂时未知 |
|  7 | Jerry | NULL      | 暂时未知 |
|  8 | TOM_1 | NULL      | 暂时未知 |
+----+-------+-----------+----------+
7 rows in set (0.00 sec)

所选字段需要慎重考虑，有多重复字段可能会导全部删除

mysql> delete from teacher where name = 'Jerry';
Query OK, 3 rows affected (0.00 sec)

mysql> select * from teacher;
+----+-------+-----------+----------+
| id | name  | phone     | address  |
+----+-------+-----------+----------+
|  1 | Frank | 188888888 | ShangHai |
|  3 | Tom   | 100000000 | Nanjing  |
|  4 | Tom   | NULL      | NULL     |
|  8 | TOM_1 | NULL      | 暂时未知 |
+----+-------+-----------+----------+
4 rows in set (0.00 sec)

同样名为Jerry的数据被删除，如果你只想删除一条内容，反而导致了多个数据被删除，造成误删，所以在删除的时候要慎重考虑选择的字段

2. 条件删除

mysql> delete from teacher where id > 4;
Query OK, 1 row affected (0.00 sec)

mysql> select * from teacher;
+----+-------+-----------+----------+
| id | name  | phone     | address  |
+----+-------+-----------+----------+
|  1 | Frank | 188888888 | ShangHai |
|  3 | Tom   | 100000000 | Nanjing  |
|  4 | Tom   | NULL      | NULL     |
+----+-------+-----------+----------+
3 rows in set (0.00 sec)

4.4 清空表

1. 删表跑路

当你的老板让你生气了，并且你已经交了辞职书，这个时候你想要删库跑路报复你的老板，这是你必须学会的技能（当然这是开玩笑的 ，请勿删库跑路，否则将负法律责任）

mysql> delete  from teacher;
Query OK, 3 rows affected (0.00 sec)

mysql> select * from teacher;
Empty set (0.00 sec)

这是一个比较低效率的方法，因为它需要遍历整个表然后一个一个删除，这样可能还没删完库就被抓了

2. 删节跑路

接下来的办法就比较高效了，你可以写完就立马跑路

mysql> truncate table teacher;
Query OK, 0 rows affected (0.02 sec)

mysql> select * from teacher;
Empty set (0.00 sec)

truncate 语句是销毁一个表，然后再克隆个完全一样的空表，它不需要遍历删除，这样就非常的快，老板还没回过神来，你就已经跑路了

4.5 小细节（delete 和 truncate 的区别）

  • 与自增的问题有关，当你使用delete清空表的时候，自增不会再次从1开始，而是你上次删掉的最后一个数据开始
  • 而使用truncate就不会出现这样一个情况，它是属于报废了原来的表，新克隆了个一样的表，可以看作是原来的空表
  • 所以当我们要清空表的时候就需要使用truncate，这样你就可以避免这个错误

4.6 更新数据

1. 基础更新

update 表名 set 要修改的字段名=要修改成的数据 where 定位字段名=相应数据

mysql> update teacher set name='Frank' where id=3;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from teacher;
+----+---------+-------+----------+
| id | name    | phone | address  |
+----+---------+-------+----------+
|  1 | TOM_1   | NULL  | 暂时未知 |
|  2 | Jerry_1 | NULL  | 暂时未知 |
|  3 | Frank   | NULL  | 暂时未知 |
+----+---------+-------+----------+
3 rows in set (0.00 sec)

注意：where可以是任何一个字段的数据，删除数据时用where要慎重考虑选择定位字段，选择唯一确定的字段，消除不必要的麻烦

2. 多条更新

对多个数据进行更改，就是多加几个逗号

update 表名 set 要修改的字段名=要修改成的数据，要修改的字段名=要修改成的数据，··· where 定位字段名=相应数据

mysql> update teacher set name='Frank',phone=123456,address='ShangHai'
    -> where id = 3;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from teacher;
+----+---------+--------+----------+
| id | name    | phone  | address  |
+----+---------+--------+----------+
|  1 | TOM_1   | NULL   | 暂时未知 |
|  2 | Jerry_1 | NULL   | 暂时未知 |
|  3 | Frank   | 123456 | ShangHai |
+----+---------+--------+----------+
3 rows in set (0.00 sec)

3. 条件更新

在有很多时候，当在满足某一条件的时候进行更新

updata 表名 set 要修改的字段名=要修改成的数据 where 定位字段名=相应数据 or 定位字段名=相应数据

满足or前后两个命令的数据都进行数据更新

mysql> update teacher set name='Frank' where id=1 or id=2;
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select * from teacher;
+----+-------+--------+----------+
| id | name  | phone  | address  |
+----+-------+--------+----------+
|  1 | Frank | NULL   | 暂时未知 |
|  2 | Frank | NULL   | 暂时未知 |
|  3 | Frank | 123456 | ShangHai |
+----+-------+--------+----------+
3 rows in set (0.00 sec)

可以看到id1和id2都进行了改变

4. 没有where

没有where的定位会使得表得数据全部更新，这个如果犯了这个错误可能就会被老板炒鱿鱼，涉及到涉及SQL注入

mysql> update teacher set name='Tom';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select * from teacher;
+----+------+--------+----------+
| id | name | phone  | address  |
+----+------+--------+----------+
|  1 | Tom  | NULL   | 暂时未知 |
|  2 | Tom  | NULL   | 暂时未知 |
|  3 | Tom  | 123456 | ShangHai |
+----+------+--------+----------+
3 rows in set (0.00 sec)

4.7 查询表数据（基本）

1. 单个查询

select 要查询的字段名 from 表名

mysql> select phone from teacher;
+--------+
| phone  |
+--------+
| NULL   |
| NULL   |
| 123456 |
+--------+
3 rows in set (0.00 sec)

2. 多个查询

select 要查询的字段名，要查询的字段名··· from 表名

mysql> select name ,phone from teacher;
+------+--------+
| name | phone  |
+------+--------+
| Tom  | NULL   |
| Tom  | NULL   |
| Tom  | 123456 |
+------+--------+
3 rows in set (0.00 sec)

3. 全部查询

select * from 表名

mysql> select * from teacher;
+----+------+--------+----------+
| id | name | phone  | address  |
+----+------+--------+----------+
|  1 | Tom  | NULL   | 暂时未知 |
|  2 | Tom  | NULL   | 暂时未知 |
|  3 | Tom  | 123456 | ShangHai |
+----+------+--------+----------+
3 rows in set (0.00 sec)

4.8 SQL语句区分

数据库的基础部分已经结束了，总结SQL语句区分为

  • DDL（data definition language 数据库定义语言）
  • DML（data manipiation language 数据操纵语言）
  • DCL (data control language 数据控制语言 )
  • DDL：create、alter、drop、show
  • DML: insert、update、delete、select
  • DCL：grant、revoke

从数据库的操作到表和数据的操纵基本上都是有上述八个单词延申出来的命令，分别都表示着增、改、删、查，以及数据库用户权限控制

4.9 表结束语

日积月累打好基础，多练习！

4.10 字符集编码问题

1. 查询字符集编码

mysql> show variables like 'character_set_%';
+--------------------------+---------------------------------------------------------+
| Variable_name            | Value
                    |
+--------------------------+---------------------------------------------------------+
| character_set_client     | gbk
                    |
| character_set_connection | gbk
                    |
| character_set_database   | gbk
                    |
| character_set_filesystem | binary
                    |
| character_set_results    | gbk
                    |
| character_set_server     | latin1
                    |
| character_set_system     | utf8
                    |
| character_sets_dir       | C:\Program Files\MySQL\MySQL Server 5.7\share\charsets\ |
+--------------------------+---------------------------------------------------------+
8 rows in set, 1 warning (0.00 sec)

2. 修改字符集编码

mysql> set character_set_client=gbk;
Query OK, 0 rows affected (0.00 sec)

注意：企业里字符集编码一定是utf-8，用gbk会出现中文乱码现象


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK