mysql 基本操作二 -云博客

mysql 基本操作二

# 数据库 2024-05-05 20:32 0 40 来源：云博客

1.查询数据

MariaDB [jason]> select * from runoob_tbl limit 2 offset 2;

默认是从偏移量为0 处开始查村数据，通过指定offset 可以从offset 处开始取数

2.where 语句

MariaDB [jason]> select * from runoob_tbl where runoob_title like ‘%python%‘;+-----------+--------------+---------------+-----------------+| runoob_id | runoob_title | runoob_author | submission_date |+-----------+--------------+---------------+-----------------+| 2 | 学习python | 菜鸟教程 | 2019-02-10 || 4 | 学习python | 菜鸟教程 | 2019-02-10 |+-----------+--------------+---------------+-----------------+2 rows in set (0.00 sec)MariaDB [jason]> select * from runoob_tbl where runoob_title like ‘%Python%‘;+-----------+--------------+---------------+-----------------+| runoob_id | runoob_title | runoob_author | submission_date |+-----------+--------------+---------------+-----------------+| 2 | 学习python | 菜鸟教程 | 2019-02-10 || 4 | 学习python | 菜鸟教程 | 2019-02-10 |+-----------+--------------+---------------+-----------------+2 rows in set (0.00 sec)

mysql where 条件不区分大小写，若要区分大小写则采用下面的写法

MariaDB [jason]> select * from runoob_tbl where binary runoob_title like ‘%Python%‘;Empty set (0.00 sec)MariaDB [jason]> select * from runoob_tbl where binary runoob_title like ‘%python%‘;+-----------+--------------+---------------+-----------------+| runoob_id | runoob_title | runoob_author | submission_date |+-----------+--------------+---------------+-----------------+| 2 | 学习python | 菜鸟教程 | 2019-02-10 || 4 | 学习python | 菜鸟教程 | 2019-02-10 |+-----------+--------------+---------------+-----------------+2 rows in set (0.00 sec)

3.update 更新表记录

MariaDB [jason]> update runoob_tbl set runoob_title=‘java‘, runoob_author =‘jason‘ where runoob_id = 2;ERROR 2006 (HY000): MySQL server has gone awayNo connection. Trying to reconnect...Connection id: 33Current database: jasonQuery OK, 1 row affected (0.04 sec)Rows matched: 1 Changed: 1 Warnings: 0MariaDB [jason]> select * from runoob_tbl;+-----------+--------------+---------------+-----------------+| runoob_id | runoob_title | runoob_author | submission_date |+-----------+--------------+---------------+-----------------+| 2 | java | jason | 2019-02-10 || 3 | 学习scala | 菜鸟教程 | 2019-02-10 || 4 | 学习python | 菜鸟教程 | 2019-02-10 || 5 | 学习scala | 菜鸟教程 | 2019-02-10 |+-----------+--------------+---------------+-----------------+

4.删除表记录 delete

delete 语句如果不指定where 条件则会删除表内所有数据

MariaDB [jason]> delete from runoob_tbl where runoob_id = 2;Query OK, 1 row affected (0.00 sec)MariaDB [jason]> select * from runoob_tbl;+-----------+--------------+---------------+-----------------+| runoob_id | runoob_title | runoob_author | submission_date |+-----------+--------------+---------------+-----------------+| 3 | 学习scala | 菜鸟教程 | 2019-02-10 || 4 | 学习python | 菜鸟教程 | 2019-02-10 || 5 | 学习scala | 菜鸟教程 | 2019-02-10 |+-----------+--------------+---------------+-----------------+3 rows in set (0.00 sec)MariaDB [jason]> delete from runoob_tbl;Query OK, 3 rows affected (0.00 sec)MariaDB [jason]> select * from runoob_tbl;Empty set (0.00 sec)

5.like 语句

MariaDB [jason]> select * from runoob_tbl where runoob_title like ‘%python‘;+-----------+--------------+---------------+-----------------+| runoob_id | runoob_title | runoob_author | submission_date |+-----------+--------------+---------------+-----------------+| 1 | 学习python | 菜鸟教程 | 2019-02-11 |+-----------+--------------+---------------+-----------------+

6.union and union all

MariaDB [jason]> select runoob_title as a from runoob_tbl union all select runoob_author as a from runoob_tbl order by a;+--------------+| a |+--------------+| dong || jason || 学习java || 学习linux || 学习python || 学习scala || 菜鸟教程 || 菜鸟教程 |+--------------+8 rows in set (0.00 sec)MariaDB [jason]> select runoob_title from runoob_tbl union all select runoob_author from runoob_tbl;+--------------+| runoob_title |+--------------+| 学习python || 学习scala || 学习java || 学习linux || 菜鸟教程 || 菜鸟教程 || jason || dong |+--------------+8 rows in set (0.00 sec)MariaDB [jason]> select runoob_title from runoob_tbl union select runoob_author from runoob_tbl;+--------------+| runoob_title |+--------------+| 学习python || 学习scala || 学习java || 学习linux || 菜鸟教程 || jason || dong |+--------------+7 rows in set (0.00 sec)MariaDB [jason]> select runoob_title from runoob_tbl union distinct select runoob_author from runoob_tbl;+--------------+| runoob_title |+--------------+| 学习python || 学习scala || 学习java || 学习linux || 菜鸟教程 || jason || dong |+--------------+7 rows in set (0.00 sec)

union 与 union distinct 功能相同，会把union 后的结果去重，union all 则不去重。

如果有order 则order 是对全局结果的排序

MariaDB [jason]> select runoob_title from runoob_tbl union all select runoob_author from runoob_tbl order by runoob_author;ERROR 1054 (42S22): Unknown column ‘runoob_author‘ in ‘order clause‘MariaDB [jason]> select runoob_title as a from runoob_tbl union all select runoob_author as a from runoob_tbl order by a;+--------------+| a |+--------------+| dong || jason || 学习java || 学习linux || 学习python || 学习scala || 菜鸟教程 || 菜鸟教程 |+--------------+8 rows in set (0.00 sec)

7.排序 order by

MariaDB [jason]> select * from runoob_tbl order by runoob_id asc;+-----------+--------------+---------------+-----------------+| runoob_id | runoob_title | runoob_author | submission_date |+-----------+--------------+---------------+-----------------+| 1 | 学习python | 菜鸟教程 | 2019-02-11 || 2 | 学习scala | 菜鸟教程 | 2019-02-11 || 3 | 学习java | jason | 2019-02-11 || 4 | 学习linux | dong | 2019-02-11 |+-----------+--------------+---------------+-----------------+4 rows in set (0.01 sec)MariaDB [jason]> select * from runoob_tbl order by runoob_id desc;+-----------+--------------+---------------+-----------------+| runoob_id | runoob_title | runoob_author | submission_date |+-----------+--------------+---------------+-----------------+| 4 | 学习linux | dong | 2019-02-11 || 3 | 学习java | jason | 2019-02-11 || 2 | 学习scala | 菜鸟教程 | 2019-02-11 || 1 | 学习python | 菜鸟教程 | 2019-02-11 |+-----------+--------------+---------------+-----------------+4 rows in set (0.00 sec)

8.group by

group by 可以用于sum，count，avg 等聚合函数

首先看一下要操作的表中的数据

MariaDB [jason]> select * from employee_tbl;+----+--------+---------------------+--------+| id | name | date | singin |+----+--------+---------------------+--------+| 1 | 小明 | 2016-04-22 15:25:33 | 1 || 2 | 小王 | 2016-04-20 15:25:47 | 3 || 3 | 小丽 | 2016-04-19 15:26:02 | 2 || 4 | 小王 | 2016-04-07 15:26:14 | 4 || 5 | 小明 | 2016-04-11 15:26:40 | 4 || 6 | 小明 | 2016-04-04 15:26:54 | 2 |+----+--------+---------------------+--------+6 rows in set (0.00 sec)

MariaDB [jason]> select name,count(*) from employee_tbl group by name;+--------+----------+| name | count(*) |+--------+----------+| 小丽 | 1 || 小明 | 3 || 小王 | 2 |+--------+----------+3 rows in set (0.00 sec)

with rollup 可以实现再分组统计数据的基础之上再进行相同的统计

MariaDB [jason]> select name,sum(singin) from employee_tbl group by name with rollup;+--------+-------------+| name | sum(singin) |+--------+-------------+| 小丽 | 2 || 小明 | 7 || 小王 | 7 || NULL | 16 |+--------+-------------+4 rows in set (0.00 sec)

但是最后一行的name 为null ，我们可以用coalesce 为其赋值，

coalesce（name,‘总数‘）,如果name 为null 则用 ‘总数’ 来命名

MariaDB [jason]> select coalesce(name,‘总数‘),sum(singin) from employee_tbl group by name with rollup;+-------------------------+-------------+| coalesce(name,‘总数‘) | sum(singin) |+-------------------------+-------------+| 小丽 | 2 || 小明 | 7 || 小王 | 7 || 总数 | 16 |+-------------------------+-------------+4 rows in set (0.00 sec)

9.join

MariaDB [jason]> select * from runoob_tbl a join employee_tbl b on a.runoob_id=b.id;+-----------+--------------+---------------+-----------------+----+--------+---------------------+--------+| runoob_id | runoob_title | runoob_author | submission_date | id | name | date | singin |+-----------+--------------+---------------+-----------------+----+--------+---------------------+--------+| 1 | 学习python | 菜鸟教程 | 2019-02-11 | 1 | 小明 | 2016-04-22 15:25:33 | 1 || 2 | 学习scala | 菜鸟教程 | 2019-02-11 | 2 | 小王 | 2016-04-20 15:25:47 | 3 || 3 | 学习java | jason | 2019-02-11 | 3 | 小丽 | 2016-04-19 15:26:02 | 2 || 4 | 学习linux | dong | 2019-02-11 | 4 | 小王 | 2016-04-07 15:26:14 | 4 |+-----------+--------------+---------------+-----------------+----+--------+---------------------+--------+4 rows in set (0.00 sec)

MariaDB [jason]> select * from runoob_tbl a left join employee_tbl b on a.runoob_id=b.id;+-----------+--------------+---------------+-----------------+------+--------+---------------------+--------+| runoob_id | runoob_title | runoob_author | submission_date | id | name | date | singin |+-----------+--------------+---------------+-----------------+------+--------+---------------------+--------+| 1 | 学习python | 菜鸟教程 | 2019-02-11 | 1 | 小明 | 2016-04-22 15:25:33 | 1 || 2 | 学习scala | 菜鸟教程 | 2019-02-11 | 2 | 小王 | 2016-04-20 15:25:47 | 3 || 3 | 学习java | jason | 2019-02-11 | 3 | 小丽 | 2016-04-19 15:26:02 | 2 || 4 | 学习linux | dong | 2019-02-11 | 4 | 小王 | 2016-04-07 15:26:14 | 4 |+-----------+--------------+---------------+-----------------+------+--------+---------------------+--------+4 rows in set (0.00 sec)

MariaDB [jason]> select * from runoob_tbl a right join employee_tbl b on a.runoob_id=b.id;+-----------+--------------+---------------+-----------------+----+--------+---------------------+--------+| runoob_id | runoob_title | runoob_author | submission_date | id | name | date | singin |+-----------+--------------+---------------+-----------------+----+--------+---------------------+--------+| 1 | 学习python | 菜鸟教程 | 2019-02-11 | 1 | 小明 | 2016-04-22 15:25:33 | 1 || 2 | 学习scala | 菜鸟教程 | 2019-02-11 | 2 | 小王 | 2016-04-20 15:25:47 | 3 || 3 | 学习java | jason | 2019-02-11 | 3 | 小丽 | 2016-04-19 15:26:02 | 2 || 4 | 学习linux | dong | 2019-02-11 | 4 | 小王 | 2016-04-07 15:26:14 | 4 || NULL | NULL | NULL | NULL | 5 | 小明 | 2016-04-11 15:26:40 | 4 || NULL | NULL | NULL | NULL | 6 | 小明 | 2016-04-04 15:26:54 | 2 |+-----------+--------------+---------------+-----------------+----+--------+---------------------+--------+6 rows in set (0.00 sec)