mysql查询语句

个人blog，欢迎关注加收藏

select

基本使用

1. 语法：select [distinct] *[column1,column2,column3,...] from tablename;

• 过滤重复的数据，用关键字：distinct
• *表示查询所有列，如果不需要所有列，只需将所查询的列的字段名写出select name,sex,salary from worker;
• select语句可以对列进行运算select name,chinese+english+math as '总分' from student;
• 字段名 as 别名：可以给字段起别名
• where 字段名=值：表示条件，多个条件时可以用and/or/not表逻辑,where 字段名1=值1 and 字段名2=值2，如：select distinct name,chinese+english+math as '总分' from student where name like '蓝%' and chinese+english+math > 200;
• having:having 条件;,where 与 having的区别：where只能查去数据表中已有的字段，having只能查已经在select后筛选出来的字段。如select distinct name,chinese+english+math as 'total' from student having name like '蓝%' and total > 200;
• where/having语句中常用的运算符：
  • >、<、>=、<=、=、!=等常用的
  • where 字段名 between 值1 and 值2;，包含值1和值2，在某个值范围之间，select distinct name,chinese+english+math as 'total' from student having name like '蓝%' and total between 200 and 299;
  • where 字段名 in(值1,值2,值3,...);表示某个字段的值存在于in()包含的值中，即满足条件，select distinct name,chinese+english+math as 'total' from student having name like '蓝%' and total in(294,299);
  • where 字段名 like/not like '%值%';表示模糊查询，%表示任意多个字符，如select * from student where name like '蓝%';，表示查询所有姓蓝的人，not like表示否定
  • is null：表示是否为空，select * from test9 where name == null;是错误的写法,select * from test9 where name is null;
• order by子句：
  • 基本语法：select * from 表名 order by 字段名 desc/asc;
  • order by子句一般写在sql语句的最后
  • desc表示倒序，asc表示升序，默认为升序
  • 多个字段排序：select * from 表名 order by 字段名1 desc/asc,字段名2 desc/asc;
  • 可以对别名进行排序：select name,chinese+english+math as 'total' from student order by total desc;

2. 聚合函数

• count(字段名)：表示某列满足条件的记录有多少条，count(*)查询所有数据,select count(*) as nums from student;=====>nums | 9，一共9个学生
  • count(*)和count(列名)的区别，count(列名)当该列的值为null时，不计数，而count(*)不会忽略null，求总人数，一定要用count(*)
• sum(字段名)：表示某列满足条件的记录的总和，基本语法：select sum(列名) from 表名;，如：select sum(chinese) from student;
  • 当列的值存在null时，单列操作sum(列名)没问题，但是多列操作结果不准确sum(列1+列2+…)，因为某一行数据：数值+null = null，不会被sum计入和，正确处理：select sum(ifnull(列1,0.0)+ifnull(列2,0.0)+ifnull(列3,0.0)) from student;
• round(数值,精度)：对数值四舍五入,如：select round(sum(chinese)/count(*),2) from student;
• ifnull(值1,值2)：如果值1==null，则取值2，如果值1!=null，就取值1，
  select ifnull(chinese,0) from student where name='蓝湛';
• avg(列名)：表示某列满足条件的记录的平均数， select avg(math) from student;，多列操作：select avg(ifnull(列1,0.0)+ifnull(列2,0.0)+ifnull(列3,0.0)) from student;
• max(列名)：表示某列满足条件的中数值的最大值，select max(math) from student;，多列操作：select max(ifnull(列1,0.0)+ifnull(列2,0.0)+ifnull(列3,0.0)) from student;
• min(列名)：表示某列满足条件的中数值的最小值，select min(math) from student;，多列操作：select min(ifnull(列1,0.0)+ifnull(列2,0.0)+ifnull(列3,0.0)) from student;
• group by：对数据（列）进行分组统计
  • 语法：select column1,column2,column3,... from tablename group by 列名;，多列分组：select column1,column2,column3,... from tablename group by 列名1,列名2,列名3,...;
  • 一般与having + 条件搭配使用，如select column1,column2,column3,... from tablename group by 列名 having 条件;

3. mysql函数

• dual是一个亚元表（内置表），可以用来做测试表
• 日期函数：
  • current_date()：select current_date() from dual;，显示当前日期
  • current_time()：select current_time() from dual;，显示当前时间
  • current_timestamp()：select current_timestamp() from dual;，显示当前日期+时间，等同效果的函数有:now()，如：select now() from dual;
  • date(datetime)/date(列名)：返回datetime时间值的日期部分：select date('2019-8-12 15:18:13') from dual;，还有相似的year(datetime)、month(datetime)、day(datetime)返回年、月、日，select year(now()) from dual;
  • date_add(date1,interval value date_type)：在date1的基础上加上value这个值的时间，date_type可以是year、month、week、day、minute、second、…，如select date_add('2019-8-12' ,interval 1 day) from dual;，在2019-8-12的基础上加1天
  • date_sub(date1,interval value date_type)：在date1的基础上减去value这个值的时间，如select date_sub('2019-8-12' ,interval 1 day) from dual;，在2019-8-12的基础上减1天
  • datediff(day1,day2)：计算两个日期间的时间差（day1-day2），按天返回，如：select datediff(now(),'1949-10-1') from dual;
  • timediff(time1,time2)：表示24h内两个时间的差(time1-time2)，如select timediff(now()+10,now()) from dual;
  • unix_timestamp()：返回一个秒数（整数），从1970-1-1 00:00:00到现在的秒数（当前时间的时间戳），如：select unix_timestamp() from dual;
  • from_unixtime(时间戳,日期格式)：将一个时间戳转成你指定的一个日期格式，select from_unixtime(unix_timestamp(),'%Y-%m-%d %H:%m:%s') from dual;
• 项目中保存时间：
  • 用date/datetime/timestamp保存时间
  • 用int unsigned来保存时间戳（多）
• 字符串函数：
  • charset(str)：返回字符串对应的字符集。select charset(english) from student;
  • concat(str1,str2,str3,…)：连接字符串，select concat(name,'\'s english score is ',english) from student;，注意js中concat()是拼合数组的，array1.concat(array2,array3,array4,...)
  • ucase(str)/lcase(str)：分别将字母转成大写/小写，select ucase('abc') from dual;,select lcase('ABC') from dual;
  • left(str,len)：从str左边开始取，取出len长度的字符串,select left('蓝湛魏婴',2) from dual;
  • right(str,len)：从str右边开始取，取出len长度的字符串,select right('蓝湛魏婴',2) from dual;
  • length(str)：计算str字符串的长度，select length('蓝湛魏婴') from dual;====>8，但是返回的是字节数
  • replace(str,str_find,str_replace)：在str字符串中找到str_find，替换成str_replace，如：select replace(name,'蓝愿','蓝思追') from student;，注意php中字符串的替换是：str_replace(str_find,str_replace,str)
  • substring(str,start,length)：在str中从start的位置截取length长度的字符串，注意：start是从1开始的，如select substring(name,1,1) from student;，与js不同，js中str.substring(start,stop)；从start位置截取到stop位置，且不包括stop
• 数学函数：
  • abs(number)：求值的绝对值，select abs(-10) from dual;
  • bin(number)：将number十进制转成二进制,select bin(10) from dual;
  • ceiling(number):将number向上取整， select ceiling(-2.131) from dual;====>-2
  • floor(number)：将number向下取整， select floor(-2.931) from dual;====>-3
  • format(number,精度)：将数字精确到某个精度，select format(12342.176123,2) from dual;，等同于round(number,精度)，会将数值四舍五入
  • round(number,精度)：将数字精确到某个精度，select round(12342.176123,2) from dual;与format的区别，format会用,分隔整数位的三个数字，而round()不会
  • mod(num1,num2)：求num1%num2的余数
  • rand()返回一个0.0-1.0的随机数
• 流程控制函数（了解）：
  • ifnull(值1,值2)：如果值1==null，则取值2，如果值1!=null，就取值1，select ifnull(chinese,0) from student where name='蓝湛';
  • if(expr1,expr2,expr3)：如果expr1为真，则返回expr2，否则返回expr3，select if(true,'expr2','expr3') from dual;
  • case…end：当有多个分支判断时

  case
  when expr1 then ...
  when expr2 then ...
  when expr3 then ...
  else ...
  end
  
  

  如：

  select name,
  (
    case
    when salary <=500 then salary * 1.2
    when 500<salary and salary <=1000 then salary * 1.1
    when 5999<salary then salary * 1
    else salary
    end  
  )
  as salary
  from
  worker;
  

• 其他函数：
  • user()：查看当前用户名，select user() from dual;
  • database()：你当前正在操作的数据库，select database() from dual;
  • md5(str)：将一个字符串进行md5加密，select md5('password') from dual;
  • password(str)：将一个字符串加密，select password('password') from dual;

4. where子句加强：

• 日期类型可以比较：select name,entry_date from worker where entry_date > '1982-1-1';
• 查询名字中第三个字符为’o’的员工： select * from worker where name like '__o%';，_可以用作占位符

5. order by 列名 desc/asc加强：
   多列排序：select * from worker order by entry_date, salary desc;
6. 分页：

• 基本语法：limit start,length
• 分页重要的两个参数：$curren{t}_{p}age当前页码,$page_size,每页条数
• select * from student limit ($current_page - 1)*$page_size,$page_size，eg：查询第一页的内容select * from student limit 0,3;，不要where关键字，数据第一条的检索是0开始的
• 和order by搭配使用，先写order by再写limit

7. 聚合函数加强：

• 子查询：查询最高工资的员工的信息，select * from worker where salary = (select max(salary) from worker);、select * from worker where salary > (select avg(salary) from worker);
• count(distinct 列名)count支持去除重复性数据

8. 语句中同时出现group by、having、order by ，关键字排序：select avg(salary) as my_avg from worker group by emp having my_avg > 1000 order by my_avg desc;，排序是：group by->having->order by