基础的SQL查询语句总结

1:select name from pet where birth<'1980-08-09' and (csd='sx' or csd='bj');

2:select name,birth from pet where death is null;

3:select name from pet order by name desc;//asc升序，desc是降序

4:select name from pet order by name limit 0,5;//ID下标从零开始显示5条记录

*5:select name from pet order rand() limit 1;//随机产生一个记录；

6：三个时间函数year()/month()/dayofmonth()//年月日
select name from pet where month(birth)='7' and dayofmonth(birth)=30;

7:to_days()将时间转换为天数
select name to_days(death)-to_days(birth) from pet;

8:select @name:=name from pet;

9:select count(*) from pet where sex='m';//计算女生有多少人;
select sex,count(*) from pet group by sex;

10:select * from pet where birth=(select min(birth) from pet);

11:select name,to_days(death)-to_days(birth) as age from pet;//将后面的该名为age
1:连接MySQL数据库服务器:shell>mysql -h localhost -u root -p hhy;
2:断开MySQL数据库服务器:Windows exit;/Linux quit;
3:查询MySQL数据库的版本和日期：mysql>SELECT VERSION(), CURRENT_DATE;
4:mysql用作一个简单的计算器:SELECT SIN(PI()/4), (4+1)*5;
5:你可以在一行上输入多条语句，只需要以一个分号间隔开各语句：SELECT VERSION(); SELECT NOW();
6:如果你决定不想执行正在输入过程中的一个命令，输入\c取消它：
7:要想将文本文件“pet.txt”装载到pet表中，使用这个命令：
  mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet;
  请注意如果用Windows中的编辑器（使用\r\n做为行的结束符）创建文件，应使用：
  mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet
    -> LINES TERMINATED BY '\r\n';
8:SELECT * FROM pet WHERE species = 'snake' OR species = 'bird'; or或者
9:SELECT * FROM pet WHERE species = 'dog' AND sex = 'f'; and并且
10：SELECT DISTINCT owner FROM pet;多个一样的只输出一个distinct
11:SELECT name, birth FROM pet ORDER BY birth DESC;默认排序是升序
12:SELECT * FROM pet WHERE name LIKE '_';匹配数据库中的一个字符的名字，有三个下划线就是三个字符；
13：SELECT * FROM pet WHERE name REGEXP '^b';匹配开头为b的用户regexp
14：SELECT * FROM pet WHERE name REGEXP 'fy$';匹配结尾为fy的用户
15：SELECT * FROM pet WHERE name REGEXP 'w';匹配包含w的用户
16：SELECT * FROM pet WHERE name REGEXP '^.....$';
    SELECT * FROM pet WHERE name REGEXP '^.{5}$';匹配用户名称正好为5个字符的
17：SELECT COUNT(*) FROM pet;计算机表中有几条数据

18：SELECT owner, COUNT(*) FROM pet GROUP BY owner;计算每个用户有的数量
18：SELECT species, COUNT(*) FROM pet GROUP BY species;计算机没种动物的数量