mysql数据库运维_开源数据库MySQL DBA运维实战3

DML数据操作语句

插入数据INSERT

完整插入

INSERT INTO 表名 VALUES (值1,值2,值3…值n);

部分插入

INSERT INTO 表名(列名，列名) VALUES (值1,值2);

更新数据UPDATE

UPDATE 表名 SET 列名=值 WHERE CONDITION;

删除数据DELETE

DELETE FROM 表名 WHERE CONDITION(条件);

操作示例

mysql> create database school;

Query OK, 1 row affected (0.00 sec)

mysql> use school;

Database changed

mysql> create table student(

-> id int,

-> name varchar(20),

-> age int

-> );

Query OK, 0 rows affected (0.01 sec)

mysql> insert into student values (1,'zhangshan',20);

Query OK, 1 row affected (0.01 sec)

mysql> insert into student (id,name) values (2,'wangwu');

Query OK, 1 row affected (0.01 sec)

mysql> select * from student;

+------+-----------+------+

| id | name | age |

+------+-----------+------+

| 1 | zhangshan | 20 |

| 2 | wangwu | NULL |

+------+-----------+------+

2 rows in set (0.00 sec)

mysql> update student set id=2 where name='zhangshan';

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from student;

+------+-----------+------+

| id | name | age |

+------+-----------+------+

| 2 | zhangshan | 20 |

| 2 | wangwu | NULL |

+------+-----------+------+

2 rows in set (0.00 sec)

mysql> delete from student where name='zhangshan';

Query OK, 1 row affected (0.00 sec)

mysql> select * from student;

+------+--------+------+

| id | name | age |

+------+--------+------+

| 2 | wangwu | NULL |

+------+--------+------+

1 row in set (0.00 sec)

DQL查询数据语句(SELECT 查询操作)

简单查询

查看所有列

SELECT * FROM 表名;

查部分列

SELECT 列1，列2，列3 FROM 表名;

单条件查询

SELECT * FROM 表名 WHERE 条件;

查询排序

升序

SELECT * FROM 表名 ORDER BY 列名 ASC;

降序

SELECT * FROM 表名 ORDER BY 列名 DESC;

限制查询的记录数

SELECT * FROM 表名 ORDER BY 列名 DESC LIMIT 3; //降序前3

操作示例

mysql> select * from student;

+------+-----------+------+

| id | name | age |

+------+-----------+------+

| 2 | wangwu | NULL |

| 1 | zhangshan | 12 |

| 3 | lisi | 15 |

+------+-----------+------+

3 rows in set (0.00 sec)

mysql> select id from student;

+------+

| id |

+------+

| 2 |

| 1 |

| 3 |

+------+

3 rows in set (0.00 sec)

mysql> select * from student where id=1;

+------+-----------+------+

| id | name | age |

+------+-----------+------+

| 1 | zhangshan | 12 |

+------+-----------+------+

1 row in set (0.00 sec)

mysql> select * from student order by age asc;

+------+-----------+------+

| id | name | age |

+------+-----------+------+

| 2 | wangwu | NULL |

| 1 | zhangshan | 12 |

| 3 | lisi | 15 |

+------+-----------+------+

3 rows in set (0.00 sec)

mysql> select * from student order by age desc;

+------+-----------+------+

| id | name | age |

+------+-----------+------+

| 3 | lisi | 15 |

| 1 | zhangshan | 12 |

| 2 | wangwu | NULL |

+------+-----------+------+

3 rows in set (0.00 sec)

mysql> select * from student order by age desc limit 2;

+------+-----------+------+

| id | name | age |

+------+-----------+------+

| 3 | lisi | 15 |

| 1 | zhangshan | 12 |

+------+-----------+------+

2 rows in set (0.00 sec)