MENU

sql 语句简单总结—中

• March 10, 2019 • Read: 68 • python

增删改查(curd)操作小结。

数据库的备份和恢复:

备份:

mysqldump –uroot –p 数据库名 > python.sql;

例如:mysqldump -uroot -p learn_sql > demo.sql;

root@iZ2zehs1zswgl6ln27ulsrZ:~# mysqldump -uroot -p learn_sql > demo.sql;
Enter password:
root@iZ2zehs1zswgl6ln27ulsrZ:~# ls
bbr  demo.sql  epoll_unblock_http_web.py  html  python.sql  tcp.sh

恢复:

先创建一个新的空数据库;然后退出。
mysql -uroot –p 新数据库名 < python.sql;

例如:

root@iZ2zehs1zswgl6ln27ulsrZ:~# mysql -uroot -p demo < demo.sql;
Enter password:
root@iZ2zehs1zswgl6ln27ulsrZ:~# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 54
Server version: 5.7.25-0ubuntu0.16.04.2 (Ubuntu)

Copyright (c) 2000, 2019, 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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| demo               |
| learn_sql          |
| mysql              |
| performance_schema |
| python             |
| sys                |
+--------------------+
7 rows in set (0.00 sec)

mysql> use demo;
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> show tables;
+----------------+
| Tables_in_demo |
+----------------+
| classes        |
| students       |
+----------------+
2 rows in set (0.00 sec)

数据库的增删改查:

增:

格式:INSERT [INTO] tb_name [(col_name,...)] {VALUES | VALUE} ({expr | DEFAULT},...),(...),...

说明:主键列是自动增长,但是在全列插入时需要占位,通常使用0或者 default 或者 null 来占位,插入成功后以实际数据为准

全列插入:值的顺序与表中字段的顺序对应

insert into 表名 values(...)

例如:insert into students values(0,'Oliver',20,default,'女',1);

mysql> desc students;
+--------+-------------------------------------+------+-----+---------+----------------+
| Field  | Type                                | Null | Key | Default | Extra          |
+--------+-------------------------------------+------+-----+---------+----------------+
| id     | int(10) unsigned                    | NO   | PRI | NULL    | auto_increment |
| name   | varchar(20)                         | YES  |     |         |                |
| age    | tinyint(3) unsigned                 | YES  |     | 0       |                |
| height | decimal(5,2)                        | YES  |     | NULL    |                |
| gender | enum('男','女','人妖','保密')       | YES  |     | NULL    |                |
| cls_id | int(10) unsigned                    | YES  |     | 0       |                |
+--------+-------------------------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

mysql> insert into students values(0,'Oliver',20,default,'女',1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from students;
+----+--------+------+--------+--------+--------+
| id | name   | age  | height | gender | cls_id |
+----+--------+------+--------+--------+--------+
|  1 | Oliver |   20 |   NULL | 女     |      1 |
+----+--------+------+--------+--------+--------+
1 row in set (0.00 sec)



例如:insert into students values(0,'Alice',20,1.72,1,1);

枚举中 的 下标从1 开始 1---“男” 2--->"女"....

mysql> insert into students values(0,'Alice',20,1.72,1,1);
Query OK, 1 row affected (0.01 sec)

mysql> select * from students;
+----+--------+------+--------+--------+--------+
| id | name   | age  | height | gender | cls_id |
+----+--------+------+--------+--------+--------+
|  1 | Oliver |   20 |   NULL | 女     |      1 |
|  2 | Alice  |   20 |   1.72 | 男     |      1 |
+----+--------+------+--------+--------+--------+
2 rows in set (0.00 sec)

部分列插入:值的顺序与给出的列顺序对应

insert into 表名(列1,...) values(值1,...);

例如:insert into students(name,age,cls_id) values('Jack',18,2);

mysql> insert into students(name,age,cls_id) values('Jack',18,2);
Query OK, 1 row affected (0.00 sec)

mysql> select * from students;
+----+--------+------+--------+--------+--------+
| id | name   | age  | height | gender | cls_id |
+----+--------+------+--------+--------+--------+
|  1 | Oliver |   20 |   NULL | 女     |      1 |
|  2 | Alice  |   20 |   1.72 | 男     |      1 |
|  3 | Jack   |   18 |   NULL | NULL   |      2 |
+----+--------+------+--------+--------+--------+
3 rows in set (0.00 sec)

多行插入:

全列插入:insert into 表名 values(...),(...)...;
例如:insert into students values(0,'Kelly',20,default,'女',1),(0,'Alex',20,default,'女',1);

mysql> insert into students values(0,'Kelly',20,default,'女',1),(0,'Alex',20,default,'女',1);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from students;                                                  +----+--------+------+--------+--------+--------+
| id | name   | age  | height | gender | cls_id |
+----+--------+------+--------+--------+--------+
|  1 | Oliver |   20 |   NULL | 女     |      1 |
|  2 | Alice  |   20 |   1.72 | 男     |      1 |
|  3 | Jack   |   18 |   NULL | NULL   |      2 |
|  4 | Kelly  |   20 |   NULL | 女     |      1 |
|  5 | Alex   |   20 |   NULL | 女     |      1 |
+----+--------+------+--------+--------+--------+
5 rows in set (0.00 sec)

部分插入:insert into 表名(列1,...) values(值1,...),(值1,...)...;

例如:insert into students (name, gender) values ("大乔", 2),("貂蝉", 2);

mysql> insert into students (name, gender) values ("大乔", 2),("貂蝉", 2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from students;
+----+--------+------+--------+--------+--------+
| id | name   | age  | height | gender | cls_id |
+----+--------+------+--------+--------+--------+
|  1 | Oliver |   20 |   NULL | 女     |      1 |
|  2 | Alice  |   20 |   1.72 | 男     |      1 |
|  3 | Jack   |   18 |   NULL | NULL   |      2 |
|  4 | Kelly  |   20 |   NULL | 女     |      1 |
|  5 | Alex   |   20 |   NULL | 女     |      1 |
|  6 | 大乔   |    0 |   NULL | 女     |      0 |
|  7 | 貂蝉   |    0 |   NULL | 女     |      0 |
+----+--------+------+--------+--------+--------+
7 rows in set (0.00 sec)

修改:

update 表名 set 列1=值1,列2=值2... where 条件;

例如:update students set age=22, gender=1 where id=3; -- 只要id为3的 进行修改

mysql> select * from students;
+----+--------+------+--------+--------+--------+
| id | name   | age  | height | gender | cls_id |
+----+--------+------+--------+--------+--------+
|  1 | Oliver |   20 |   NULL | 女     |      1 |
|  2 | Alice  |   20 |   1.72 | 男     |      1 |
|  3 | Jack   |   18 |   NULL | NULL   |      2 |
|  4 | Kelly  |   20 |   NULL | 女     |      1 |
|  5 | Alex   |   20 |   NULL | 女     |      1 |
|  6 | 大乔   |    0 |   NULL | 女     |      0 |
|  7 | 貂蝉   |    0 |   NULL | 女     |      0 |
+----+--------+------+--------+--------+--------+
7 rows in set (0.00 sec)

mysql> ^C

^C
mysql> update students set age=22, gender=1 where id=3; -- 只要id为3的 进行修改
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from students;
+----+--------+------+--------+--------+--------+
| id | name   | age  | height | gender | cls_id |
+----+--------+------+--------+--------+--------+
|  1 | Oliver |   20 |   NULL | 女     |      1 |
|  2 | Alice  |   20 |   1.72 | 男     |      1 |
|  3 | Jack   |   22 |   NULL | 男     |      2 |
|  4 | Kelly  |   20 |   NULL | 女     |      1 |
|  5 | Alex   |   20 |   NULL | 女     |      1 |
|  6 | 大乔   |    0 |   NULL | 女     |      0 |
|  7 | 貂蝉   |    0 |   NULL | 女     |      0 |
+----+--------+------+--------+--------+--------+
7 rows in set (0.00 sec)

删除:

DELETE FROM tbname [where 条件判断];

delete from 表名 where 条件;

例如:delete from students where id=5;

mysql> select * from students;
+----+--------+------+--------+--------+--------+
| id | name   | age  | height | gender | cls_id |
+----+--------+------+--------+--------+--------+
|  1 | Oliver |   20 |   NULL | 女     |      1 |
|  2 | Alice  |   20 |   1.72 | 男     |      1 |
|  3 | Jack   |   22 |   NULL | 男     |      2 |
|  4 | Kelly  |   20 |   NULL | 女     |      1 |
|  5 | Alex   |   20 |   NULL | 女     |      1 |
|  6 | 大乔   |    0 |   NULL | 女     |      0 |
|  7 | 貂蝉   |    0 |   NULL | 女     |      0 |
+----+--------+------+--------+--------+--------+
7 rows in set (0.00 sec)

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

mysql> select * from students;
+----+--------+------+--------+--------+--------+
| id | name   | age  | height | gender | cls_id |
+----+--------+------+--------+--------+--------+
|  1 | Oliver |   20 |   NULL | 女     |      1 |
|  2 | Alice  |   20 |   1.72 | 男     |      1 |
|  3 | Jack   |   22 |   NULL | 男     |      2 |
|  4 | Kelly  |   20 |   NULL | 女     |      1 |
|  6 | 大乔   |    0 |   NULL | 女     |      0 |
|  7 | 貂蝉   |    0 |   NULL | 女     |      0 |
+----+--------+------+--------+--------+--------+
6 rows in set (0.00 sec)
Tags: python
Archives QR Code Tip
QR Code for this page
Tipping QR Code