简单的SQL语句

最新推荐文章于 2024-07-07 08:52:27 发布

一明白

最新推荐文章于 2024-07-07 08:52:27 发布

阅读量67

点赞数

分类专栏： SQL 文章标签： sql dba 数据库

本文链接：https://blog.csdn.net/weixin_47949250/article/details/125197103

版权

SQL 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

查询语句
select* from tb_login;–获取表的所有数据
SELECT name,login_id FROM tb_login;–获取某几列
SELECT name FROM tb_login WHERE name = ‘静’;–获取满足条件的某列所有数据
SELECT * FROM tb_login WHERE name = ‘静’;–获取满足条件的
SELECT * FROM tb_login WHERE name != ‘静’ ;–不等于静
SELECT * FROM tb_login WHERE name = ‘静’ AND login_phone =‘140179’;–获取同时满足条件的AND
SELECT * FROM tb_login WHERE name = ‘静’ OR name = ‘晶’;–获取或者条件OR
SELECT * FROM tb_login WHERE name IN (‘静’, ‘晶’);–
SELECT * FROM tb_login WHERE name LIKE ‘%静’; --以静开头
SELECT * FROM tb_login WHERE name LIKE ‘%静’;–以静结尾
SELECT * FROM tb_login WHERE name LIKE ‘%静%’;–包含静
SELECT * FROM tb_login WHERE last_modify BETWEEN ‘2018-10-16 11:13:52’ AND ‘2018-11-30 00:00:00’;–小的放前面，范围查询
SELECT last_modify FROM tb_login WHERE last_modify BETWEEN ‘2018-10-16 11:13:52’ AND ‘2018-11-30 00:00:00’ ORDER BY last_modify;–默认升序
SELECT last_modify FROM tb_login WHERE last_modify BETWEEN ‘2018-10-16 11:13:52’ AND ‘2018-11-30 00:00:00’ ORDER BY last_modify ASC;–升序
SELECT last_modify FROM tb_login WHERE last_modify BETWEEN ‘2018-10-16 11:13:52’ AND ‘2018-11-30 00:00:00’ ORDER BY last_modify DESC;–降序

删除数据
Delete FROM tb_login WHERE name = ‘静’;

修改字段内容
UPDATE tb_login SET login_phone = ‘18236’ WHERE NAME = ‘静’ AND login_phone = ‘18760’;–一个字段修改
UPDATE tb_login SET login_phone = ‘138760’,NAME = ‘静’ WHERE NAME = ‘静’ AND login_phone = ‘12276’;–多个字段修改

多表查询
select * from
tb_login
inner join tb_user_role where tb_login.login_id=tb_user_role.login_id and tb_login.login_id=‘36240005’
–内连接inner join

select * from
tb_login
left join tb_user_role on tb_login.login_id=tb_user_role.login_id
where
tb_login.login_id=‘36208’
–左外连接left join …on

聚合函数:分组
SELECT NAME,count() FROM tb_login WHERE NAME = ‘静’ GROUP BY NAME;–count是计数函数,GROUP BY分组
SELECT last_modify ,count() FROM tb_login WHERE last_modify BETWEEN ‘2018-10-16 11:13:52’ AND ‘2018-11-30 00:00:00’ GROUP BY last_modify;
SELECT count(*) FROM tb_login WHERE last_modify BETWEEN ‘2018-10-16 11:13:52’ AND ‘2018-11-30 00:00:00’;
SELECT last_modify FROM tb_login WHERE last_modify BETWEEN ‘2018-10-16 11:13:52’ AND ‘2018-11-30 00:00:00’ GROUP BY last_modify;

去除重复数据DISTINCT
SELECT DISTINCT name,login_phone FROM tb_login WHERE name = ‘静’;