mysql 基本操作二
2024-08-31 13:53:13
1.查询数据
MariaDB [jason]> select * from runoob_tbl limit offset ;
默认是从偏移量为0 处开始查村数据，通过指定offset 可以从offset 处开始取数
2.where 语句
MariaDB [jason]> select * from runoob_tbl where runoob_title like '%python%';
+-----------+--------------+---------------+-----------------+
| runoob_id | runoob_title | runoob_author | submission_date |
+-----------+--------------+---------------+-----------------+
| | 学习python | 菜鸟教程 | -- |
| | 学习python | 菜鸟教程 | -- |
+-----------+--------------+---------------+-----------------+
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 |
+-----------+--------------+---------------+-----------------+
| | 学习python | 菜鸟教程 | -- |
| | 学习python | 菜鸟教程 | -- |
+-----------+--------------+---------------+-----------------+
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 |
+-----------+--------------+---------------+-----------------+
| | 学习python | 菜鸟教程 | -- |
| | 学习python | 菜鸟教程 | -- |
+-----------+--------------+---------------+-----------------+
rows in set (0.00 sec)
3.update 更新表记录
MariaDB [jason]> update runoob_tbl set runoob_title='java', runoob_author ='jason' where runoob_id = ;
ERROR (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:
Current database: jason Query OK, row affected (0.04 sec)
Rows matched: Changed: Warnings: MariaDB [jason]> select * from runoob_tbl;
+-----------+--------------+---------------+-----------------+
| runoob_id | runoob_title | runoob_author | submission_date |
+-----------+--------------+---------------+-----------------+
| | java | jason | -- |
| | 学习scala | 菜鸟教程 | -- |
| | 学习python | 菜鸟教程 | -- |
| | 学习scala | 菜鸟教程 | -- |
+-----------+--------------+---------------+-----------------+
4.删除表记录 delete
delete 语句如果不指定where 条件则会删除表内所有数据
MariaDB [jason]> delete from runoob_tbl where runoob_id = ;
Query OK, row affected (0.00 sec) MariaDB [jason]> select * from runoob_tbl;
+-----------+--------------+---------------+-----------------+
| runoob_id | runoob_title | runoob_author | submission_date |
+-----------+--------------+---------------+-----------------+
| | 学习scala | 菜鸟教程 | -- |
| | 学习python | 菜鸟教程 | -- |
| | 学习scala | 菜鸟教程 | -- |
+-----------+--------------+---------------+-----------------+
rows in set (0.00 sec) MariaDB [jason]> delete from runoob_tbl;
Query OK, 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 |
+-----------+--------------+---------------+-----------------+
| | 学习python | 菜鸟教程 | -- |
+-----------+--------------+---------------+-----------------+
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 |
| 菜鸟教程 |
| 菜鸟教程 |
+--------------+
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 |
+--------------+
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 |
+--------------+
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 |
+--------------+
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 (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 |
| 菜鸟教程 |
| 菜鸟教程 |
+--------------+
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 |
+-----------+--------------+---------------+-----------------+
| | 学习python | 菜鸟教程 | -- |
| | 学习scala | 菜鸟教程 | -- |
| | 学习java | jason | -- |
| | 学习linux | dong | -- |
+-----------+--------------+---------------+-----------------+
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 |
+-----------+--------------+---------------+-----------------+
| | 学习linux | dong | -- |
| | 学习java | jason | -- |
| | 学习scala | 菜鸟教程 | -- |
| | 学习python | 菜鸟教程 | -- |
+-----------+--------------+---------------+-----------------+
rows in set (0.00 sec)
8.group by
group by 可以用于sum，count，avg 等聚合函数
首先看一下要操作的表中的数据
MariaDB [jason]> select * from employee_tbl;
+----+--------+---------------------+--------+
| id | name | date | singin |
+----+--------+---------------------+--------+
| | 小明 | -- :: | |
| | 小王 | -- :: | |
| | 小丽 | -- :: | |
| | 小王 | -- :: | |
| | 小明 | -- :: | |
| | 小明 | -- :: | |
+----+--------+---------------------+--------+
rows in set (0.00 sec)
MariaDB [jason]> select name,count(*) from employee_tbl group by name;
+--------+----------+
| name | count(*) |
+--------+----------+
| 小丽 | |
| 小明 | |
| 小王 | |
+--------+----------+
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) |
+--------+-------------+
| 小丽 | |
| 小明 | |
| 小王 | |
| NULL | |
+--------+-------------+
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) |
+-------------------------+-------------+
| 小丽 | |
| 小明 | |
| 小王 | |
| 总数 | |
+-------------------------+-------------+
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 |
+-----------+--------------+---------------+-----------------+----+--------+---------------------+--------+
| | 学习python | 菜鸟教程 | -- | | 小明 | -- :: | |
| | 学习scala | 菜鸟教程 | -- | | 小王 | -- :: | |
| | 学习java | jason | -- | | 小丽 | -- :: | |
| | 学习linux | dong | -- | | 小王 | -- :: | |
+-----------+--------------+---------------+-----------------+----+--------+---------------------+--------+
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 |
+-----------+--------------+---------------+-----------------+------+--------+---------------------+--------+
| | 学习python | 菜鸟教程 | -- | | 小明 | -- :: | |
| | 学习scala | 菜鸟教程 | -- | | 小王 | -- :: | |
| | 学习java | jason | -- | | 小丽 | -- :: | |
| | 学习linux | dong | -- | | 小王 | -- :: | |
+-----------+--------------+---------------+-----------------+------+--------+---------------------+--------+
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 |
+-----------+--------------+---------------+-----------------+----+--------+---------------------+--------+
| | 学习python | 菜鸟教程 | -- | | 小明 | -- :: | |
| | 学习scala | 菜鸟教程 | -- | | 小王 | -- :: | |
| | 学习java | jason | -- | | 小丽 | -- :: | |
| | 学习linux | dong | -- | | 小王 | -- :: | |
| NULL | NULL | NULL | NULL | | 小明 | -- :: | |
| NULL | NULL | NULL | NULL | | 小明 | -- :: | |
+-----------+--------------+---------------+-----------------+----+--------+---------------------+--------+
rows in set (0.00 sec)
10. null 值的处理
mysql 中判断值是否为null 不能用 = null，<> null, 要用 is null 或者is not null, null = null 返回false ，null 与任何值的比较都是false，null 值的比较要用 <=>,null <=> null返回true
11. mysql 中的正则表达式
12.事务
- 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
- 事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
- 事务用来管理 insert,update,delete 语句
MariaDB [jason]> begin;
Query OK, rows affected (0.00 sec) MariaDB [jason]> insert into runoob_tbl(
-> runoob_title,runoob_author,submission_date
-> )
-> values
-> ("学习php","jason",now()),
-> ("学习c++","dong",now());
Query OK, rows affected, warnings (0.01 sec)
Records: Duplicates: Warnings: MariaDB [jason]> select * from runoob_tbl ;
+-----------+--------------+---------------+-----------------+
| runoob_id | runoob_title | runoob_author | submission_date |
+-----------+--------------+---------------+-----------------+
| | 学习python | 菜鸟教程 | -- |
| | 学习scala | 菜鸟教程 | -- |
| | 学习java | jason | -- |
| | 学习linux | dong | -- |
| | 学习php | jason | -- |
| | 学习c++ | dong | -- |
+-----------+--------------+---------------+-----------------+
rows in set (0.00 sec) MariaDB [jason]> rollback;
Query OK, rows affected (0.00 sec) MariaDB [jason]> select * from runoob_tbl ;
+-----------+--------------+---------------+-----------------+
| runoob_id | runoob_title | runoob_author | submission_date |
+-----------+--------------+---------------+-----------------+
| | 学习python | 菜鸟教程 | -- |
| | 学习scala | 菜鸟教程 | -- |
| | 学习java | jason | -- |
| | 学习linux | dong | -- |
+-----------+--------------+---------------+-----------------+
rows in set (0.00 sec) MariaDB [jason]> insert into runoob_tbl(
-> runoob_title,runoob_author,submission_date
-> )
-> values
-> ("学习php","jason",now()),
-> ("学习c++","dong",now());
Query OK, rows affected, warnings (0.01 sec)
Records: Duplicates: Warnings: MariaDB [jason]> commit;
Query OK, rows affected (0.00 sec) MariaDB [jason]> select * from runoob_tbl ;
+-----------+--------------+---------------+-----------------+
| runoob_id | runoob_title | runoob_author | submission_date |
+-----------+--------------+---------------+-----------------+
| | 学习python | 菜鸟教程 | -- |
| | 学习scala | 菜鸟教程 | -- |
| | 学习java | jason | -- |
| | 学习linux | dong | -- |
| | 学习php | jason | -- |
| | 学习c++ | dong | -- |
+-----------+--------------+---------------+-----------------+
rows in set (0.00 sec)
最新文章
- jquery html动态添加的元素绑定事件详解
- spring整合struts2
- Mysql备份系列（2）--mysqldump备份（全量+增量）方案操作记录
- centos6搭建VPN
- 2. XAML
- lintcode ：nth to Last Node In List 链表倒数第n个节点
- Firefox 火狐网址生成二维码扩展推荐
- SQL语句基本概念
- CentOS7设置IP地址
- hdu5391 Zball in Tina Town(威尔逊定理)
- winzip15.0许可证
- MySQL timestamp NOT NULL插入NULL的问题
- 基于HTML5 Canvas 实现商场监控
- python_鸡兔同笼问题
- [测试题]wows
- [NOI2009]变换序列
- 关于配置ssh免密码登录后，仍提示输入密码
- 数据看板superset在Windows环境下安装
- SpringCloud Config（分布式配置中心）
- ELK新手教程（二）