1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47
| ALTER TABLE tb_emp ADD diredate VARCHAR(20);
#插入数据 INSERT INTO tb_dept() VALUE(4,'市场部','负责市场工作');
# concat 连接 SELECT CONCAT(NAME,sex) FROM tb_emp;
# UPPER 转换大写 SELECT UPPER(NAME) FROM tb_emp WHERE dept_id=1;
# 返回字符串长度 SELECT LENGTH(NAME) FROM tb_emp WHERE dept_id=1;
# 返回部分字符 SELECT SUBSTR(NAME,2,2) FROM tb_emp WHERE dept_id=1;
# 返回当前 SELECT NOW();
# 查询时间是1981年 SELECT * FROM tb_emp WHERE YEAR(diredate) = 1981 AND MONTH(diredate) = 1982;
#插入时间 INSERT INTO tb_emp(NAME,sex,age,address,email,dept_id,diredate) VALUES('ZHOU','男',33,'香港','ZHOU@163.com',2,'1988-09-09');
INSERT INTO tb_emp(NAME,sex,age,address,email,dept_id,diredate) VALUES('CAI','女',30,'香港','CAI@163.com',2,NOW());
# 条件判断语句 SELECT NAME,sex,age '原来年龄' CASE WHEN age IS NULL THEN 100 ELSE age END AS '年龄' FROM tb_emp;
# IFNULL函数 如果字段不为NULL,则取第二个值,如果为空,择取第三个值 SELECT NAME,IFNULL(age,age+100,100) AS age2 FROM tb_emp;
# IFNULL函数 如果字段不为NULL,则直接去该值,如果为空,择取第二个值 SELECT NAME,IFNULL(age,100) AS age2 FROM tb_emp;
|