sql 语句

最新推荐文章于 2025-08-20 20:33:15 发布

cherry_hit_tom

最新推荐文章于 2025-08-20 20:33:15 发布

阅读量495

点赞数

CC 4.0 BY-SA版权

文章标签： sql null date

本文链接：https://blog.youkuaiyun.com/tsuliuchao/article/details/4602371

select * from pet;
insert into pet values('Liujingwei','Liuchao','cat','f','1984-04-18',null);
UPDATE pet set birth='1989-08-31' WHERE name='Slim';
select * from pet WHERE birth>'1998-1-1';
SELECT * from pet WHEREselect * from pet;
insert into pet values('Liujingwei','Liuchao','cat','f','1984-04-18',null);
UPDATE pet set birth='1989-08-31' WHERE name='Slim';
select * from pet WHERE birth>'1998-1-1';
SELECT * from pet WHERE species='dog' and sex='f';
select * from pet WHERE species='snake' or species='cat';
SELECT * from pet WHERE (species='cat' and sex='m') or (species='dog' and sex='f');
SELECT name,birth from pet;
SELECT owner from pet;
SELECT distinct owner from pet;
SELECT name,species,birth from pet where species='dog' or species='cat';
=======================================================================
select name,birth FROM pet ORDER BY birth;
select name,birth from pet order by birth desc;
select name,species,birth from pet order by species,birth desc;
========================================================================
select name,birth,curdate(),(year(curdate())-year(birth))-(right(curdate(),5)<right(birth,5)) as age from pet order by name;
select name,birth,curdate(),(year(curdate())-year(birth))-(right(curdate(),5)<right(birth,5)) as age from pet order by age;
select name,birth,curdate(),(year(curdate())-year(birth))-(right(curdate(),5)<right(birth,5)) as age FROM pet WHERE death IS NOT NULL ORDER BY age;
select name,birth ,month(birth) from pet;
SELECT name, birth FROM pet WHERE MONTH(birth) = 5;
SELECT name, birth FROM pet WHERE MONTH(birth) = MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH));
SELECT name, birth FROM pet WHERE MONTH(birth) = MOD(MONTH(CURDATE()), 12) + 1;
select 1 is null,1 is not null;
select 0 is null, 0 is not null,'' is null,'' is not null;
SELECT * FROM pet WHERE name LIKE 'b%';
SELECT * FROM pet WHERE name LIKE '%fy';
SELECT * FROM pet WHERE name LIKE '%w%';
SELECT * FROM pet WHERE name LIKE '_____';
SELECT * FROM pet WHERE name REGEXP '^b';
SELECT * FROM pet WHERE name REGEXP BINARY '^b';
SELECT * FROM pet WHERE name REGEXP 'fy$';
SELECT * FROM pet WHERE name REGEXP 'w';
SELECT * FROM pet WHERE name REGEXP '^.....$';
SELECT * FROM pet WHERE name REGEXP '^.{5}$';
=======================================================
SELECT COUNT(*) FROM pet;
SELECT owner, COUNT(*) FROM pet GROUP BY owner;
SELECT owner, COUNT(*) FROM pet;
SELECT species, COUNT(*) FROM pet GROUP BY species;
SELECT sex, COUNT(*) FROM pet GROUP BY sex;
SELECT species, sex, COUNT(*) FROM pet GROUP BY species, sex;
SELECT species, sex, COUNT(*) FROM pet WHERE species = 'dog' OR species = 'cat' GROUP BY species, sex;
SELECT species, sex, COUNT(*) FROM pet WHERE sex IS NOT NULL GROUP BY species, sex;
=================================================3.3.4.9 species='dog' and sex='f';
select * from pet WHERE species='snake' or species='cat';
SELECT * from pet WHERE (species='cat' and sex='m') or (species='dog' and sex='f');
SELECT name,birth from pet;
SELECT owner from pet;
SELECT distinct owner from pet;
SELECT name,species,birth from pet where species='dog' or species='cat';
=======================================================================
select name,birth FROM pet ORDER BY birth;
select name,birth from pet order by birth desc;
select name,species,birth from pet order by species,birth desc;
========================================================================
select name,birth,curdate(),(year(curdate())-year(birth))-(right(curdate(),5)<right(birth,5)) as age from pet order by name;
select name,birth,curdate(),(year(curdate())-year(birth))-(right(curdate(),5)<right(birth,5)) as age from pet order by age;
select name,birth,curdate(),(year(curdate())-year(birth))-(right(curdate(),5)<right(birth,5)) as age FROM pet WHERE death IS NOT NULL ORDER BY age;
select name,birth ,month(birth) from pet;
SELECT name, birth FROM pet WHERE MONTH(birth) = 5;
SELECT name, birth FROM pet WHERE MONTH(birth) = MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH));
SELECT name, birth FROM pet WHERE MONTH(birth) = MOD(MONTH(CURDATE()), 12) + 1;
select 1 is null,1 is not null;
select 0 is null, 0 is not null,'' is null,'' is not null;
SELECT * FROM pet WHERE name LIKE 'b%';
SELECT * FROM pet WHERE name LIKE '%fy';
SELECT * FROM pet WHERE name LIKE '%w%';
SELECT * FROM pet WHERE name LIKE '_____';
SELECT * FROM pet WHERE name REGEXP '^b';
SELECT * FROM pet WHERE name REGEXP BINARY '^b';
SELECT * FROM pet WHERE name REGEXP 'fy$';
SELECT * FROM pet WHERE name REGEXP 'w';
SELECT * FROM pet WHERE name REGEXP '^.....$';
SELECT * FROM pet WHERE name REGEXP '^.{5}$';
=======================================================
SELECT COUNT(*) FROM pet;
SELECT owner, COUNT(*) FROM pet GROUP BY owner;
SELECT owner, COUNT(*) FROM pet;
SELECT species, COUNT(*) FROM pet GROUP BY species;
SELECT sex, COUNT(*) FROM pet GROUP BY sex;
SELECT species, sex, COUNT(*) FROM pet GROUP BY species, sex;
SELECT species, sex, COUNT(*) FROM pet WHERE species = 'dog' OR species = 'cat' GROUP BY species, sex;
SELECT species, sex, COUNT(*) FROM pet WHERE sex IS NOT NULL GROUP BY species, sex;
=================================================3.3.4.9