编辑代码

CREATE DATABASE test;
use test;
CREATE TABLE student (
	id int,
	name varchar(255),
	address varchar(255),
	city varchar(255)
);
CREATE TABLE FDJH (
	id int,
	x1 varchar(255),
	x2 varchar(255),
	x3 varchar(255),
    salary varchar(255),
    rate varchar(255)

);
INSERT INTO student VALUES (1, '张三', '李四家隔壁', '杭州');
INSERT INTO student VALUES (2, '李四',  '张三家隔壁', '北京');
INSERT INTO student VALUES (3, '猪猪侠1', '洛阳城里1', '洛阳1');
INSERT INTO student VALUES (4, '猪猪侠2', '洛阳城里2', '洛阳2');
INSERT INTO student VALUES (5, '猪猪侠3', '洛阳城里3', '洛阳3');
INSERT INTO student VALUES (6, '猪猪侠4', '洛阳城里4', '洛阳4');
INSERT INTO student VALUES (7, '猪猪侠5', '洛阳城里5', '洛阳5');
INSERT INTO student VALUES (8, '猪猪侠6', '洛阳城里6', '洛阳6');

INSERT INTO FDJH VALUES (1, 'zhuzhu', '隔壁', '杭州', 500, null);
INSERT INTO FDJH VALUES (2, '李四',  '隔壁', '北京', 500, null);
INSERT INTO FDJH VALUES (3, '猪猪侠1', '洛阳', '洛阳', 600, null);
INSERT INTO FDJH VALUES (4, '猪猪侠2', '西安', '郑州', 600, 0.2);
INSERT INTO FDJH VALUES (5, '猪猪侠3', '西安', '西安', 600, 0.4);
INSERT INTO FDJH VALUES (6, '猪猪侠4', '北京', '西安', 700, 0.4);
INSERT INTO FDJH VALUES (7, '猪猪侠5', '北京', '西安', 700, 0.4);
INSERT INTO FDJH VALUES (8, '猪猪侠6', '北京', '郑州', 600, null);
-- select 字段名 from 表名; 

-- 筛选表中所有的列
-- SELECT * FROM test.FDJH;

-- 筛选表中部分字段 
-- SELECT id, x1, x2 FROM test.FDJH;

-- 给表中列进行重命名
-- SELECT id, x1 emp_id, x2 FROM test.FDJH;
-- SELECT id, x1 AS emp_id, x2 FROM test.FDJH;
-- SELECT id, x1, x2 "emp_id" FROM test.FDJH;


-- 去除表中某列x2中重复的数据值
-- select distinct x2 from test.FDJH
select id, x1, salary "月工资", salary * (1+rate) * 12 "年工资" from test.FDJH;


select id, x1, salary "月工资", salary * (1+ifnull(rate,0)) * 12 "年工资" from test.FDJH;