mysql 常用的操作(2)
mysql与其他编辑性语言类似通常可以掺杂各种算术运算符,比较运算符,逻辑运算符 等运算符 对数据进行操作:
算术运算符:
select * from biao where eid%2=0; (取余运算符)
select eid,basic_sarary/12 as "日新" from salary; (除法) 加减乘
比较运算符:
#=,>, <,>=, <=, !=(不等于<>),<=>(安全等于)
SELECT eid,basic_salary FROM t_salary WHERE basic_salary <> 10000;
#查询basic_salary=10000,注意在Java中比较是== SELECT eid,basic_salary FROM t_salary WHERE basic_salary = 10000;
#查询commission_pct等于0.40 SELECT eid,commission_pct FROM t_salary WHERE commission_pct = 0.40; SELECT eid,commission_pct FROM t_salary WHERE commission_pct <=> 0.40;
#查询commission_pct等于NULL SELECT eid,commission_pct FROM t_salary WHERE commission_pct IS NULL; SELECT eid,commission_pct FROM t_salary WHERE commission_pct <=> NULL;
#查询commission_pct不等于NULL SELECT eid,commission_pct FROM t_salary WHERE commission_pct IS NOT NULL; SELECT eid,commission_pct FROM t_salary WHERE NOT commission_pct <=> NULL;
逻辑运算符:
#与&&,或||,非! #与 AND,或 OR ,非 NOT,异或 XOR
select * from biao where gender="男" and birthday<"1990-01-01";
select * from biao where job_id=1 or job_id=2;
三种模糊查询方式:
select from employee where name like '%冰%'; #查询名字带有冰的名字
select from emplyee where name like '李%'; #查询名字中李姓的名字
select from emplyee where name like '李_'; #查询名字中李姓的名字(名字为单字)
范围和集合:
select eid,basic_salary from t_salary where basic_salary between 9000 and 12000;
查询eid 是 1,3,5基本工资
null值判断与计算处理
select eid,commiss from t_salary where commiss is not null;
select eid,commiss from t_salary where commiss ifnull (value1,value2);
如果value1不为空,返回value1;否则返回value2
编码格式
show variables like 'character_set_database'; 查看数据库编码格式
show create table<表名>; 查看数据表的编码格式
去重关键字 (distinct)
select distinct id from employee; 查询员工的编号
select count(distinct id) from employee; 统计员工表中有几个部门
select 的五个句子
(1):where 按条件查询 where后面接条件
(2):group by 分组 group by 后面接字段名 或者条件
(3):having 筛选 having 后面接条件
(4):order by 排序 order by 后面接字段名 或者条件
(5):limit 分页 limit m,n
m表示从下标为m的记录开始查询,第一条记录下标为0,n表示取出n条出来,如果从m开始不够n条了,就有几条取几条。