MySQL学习笔记——20170811

部分函数

• LTRIM() -> 去除左边空格 RTRIM() 去除右边空格
• ROUND() -> 四舍五入
• LENGTH() -> 获取字符串长度
• UPPER() -> 大写显示 LOWER 小写显示
• REPLACE(‘hello’,‘h’,‘w’) -> 替换
• INSERT(,)
• INSTR(‘hello’,‘e’) -> 返回e在hello中的位置 2
• CONVERT(value,type) -> 类型转换
• LEFT(‘hello’,1) RIGHT()
• SQRT(16) -> 取16的平方根
• ABS() -> 绝对值
• DAYNAME() 星期几 DAYOFMONTH() 一月中的第几天 DAYOFWEEK() DAYOFYEAR()
• YEAR() MONTH()
• DATE_ADD(‘1990-01-01’, INTERVAL 1 DAY) -> 添加1天

练习

CREATE TABLE employees (
	EmployeeID int(6) PRIMARY KEY  NOT NULL,
	eName char(10) NOT NULL,
	Education char(4) NOT NULL,
	Gender char(2) NOT NULL DEFAULT '',
	Workyear int(11) DEFAULT NULL,
	dpart_id int(3) NOT NULL,
	tel varchar(10),
	eBirth date
) ENGINE=InnoDB CHARSET=utf8;

INSERT INTO employees VALUES ('1', '张三', '专科', '男', '2', '1','023-12315','1992-01-01');
INSERT INTO employees VALUES ('2', '李四', '本科', '男', '1', '1','023-23445','1995-02-02');
INSERT INTO employees VALUES ('3', '王五', '高中', '男', '3', '1','023-22423','1993-02-15');
INSERT INTO employees VALUES ('4', '李强', '专科', '男', '3', '5','023-24254','1982-11-25');
INSERT INTO employees VALUES ('5', '将干', '专科', '男', '1', '5','023-13134','1998-11-03');
INSERT INTO employees VALUES ('6', '项棋', '本科', '女', '4', '2','023-52435','1992-12-18');
INSERT INTO employees VALUES ('7', '安和', '研究生', '女', '3', '3','023-46352','1999-03-19');
INSERT INTO employees VALUES ('8', '动力', '研究生', '女', '4', '4','023-23456','1994-04-17');
INSERT INTO employees VALUES ('9', '李安', '本科', '男', '8', '3','023-12345','1993-07-16');
INSERT INTO employees VALUES ('10', '向前看', '高中', '男', '0', '5','023-42567','1991-08-25');
INSERT INTO employees VALUES ('11', '万精油', '高中', '男', '1', '4','023-24986','1991-09-22');
INSERT INTO employees VALUES ('12', '高琪琪', '研究生', '女', '2', '1','023-23561','1992-12-25');
INSERT INTO employees VALUES ('13', '李珊珊', '本科', '女', '4', '2','023-23567','1995-11-20');
INSERT INTO employees VALUES ('14', '张安', '本科', '男', '2', '3','023-33345','1997-04-05');
INSERT INTO employees VALUES ('15', '李六', '本科', '男', '10', '3','023-23256','1997-05-05');
INSERT INTO employees VALUES ('16', '余四', '高中', '女', '1', '5','023-23568','1998-06-05');
INSERT INTO employees VALUES ('17', '周悦', '本科', '女', '2', '3','023-32566','1991-07-19');
INSERT INTO employees VALUES ('18', '周大七', '本科', '男', '2', '2','023-65324','1982-12-18');
INSERT INTO employees VALUES ('19', '牌牌琦', '研究生', '女', '5', '2','023-32152','1992-01-17');
INSERT INTO employees VALUES ('20', 'Tom', '本科', '男', '1', '1','023-77777','1995-01-05');
INSERT INTO employees VALUES ('21', 'Mike', '研究生', '男', '4', '2','023-88888','1982-03-04');
INSERT INTO employees VALUES ('22', 'Angela', '本科', '女', '1', '3','023-22222','1992-04-13');
INSERT INTO employees VALUES ('23', 'An Penny', '本科', '女', '2', '3','023-33333','1996-04-05');
INSERT INTO employees VALUES ('24', '安佩妮', '研究生', '女', '3', '3','023-44444','1998-05-12');
INSERT INTO employees VALUES ('25', '周安安', '研究生', '女', '5', '2','023-66666','1998-05-11');



-- 1、查询员工信息，查询结果要求每个员工的'姓'都用‘e’代替	
SELECT EmployeeID,INSERT(eName,1,1,'e'),Education,Gender,Workyear,dpart_id,tel,eBirth
FROM employees;

-- 2、查询名叫An Penny的员工，‘n’这个字符在她的名字里总共出现了几次
SELECT eName,LENGTH(eName)-LENGTH(REPLACE(eName,'n',''))
FROM employees
WHERE eName = 'An Penny';
-- 3、查询所有员工的姓名，要求姓名里包含‘三’，‘四’，‘五’几个字的去掉；如：张三，则输出张
SELECT REPLACE(REPLACE(REPLACE(eName,'三',''),'四',''),'五','')
FROM employees;

-- 3、查询员工电话号码，要求结果电话号码去除‘-’ 
SELECT REPLACE(tel,'-','')
FROM employees;

-- 4、查询员工电话号码，将区号和电话号码分别两列显示
SELECT LEFT(tel,INSTR(tel,'-')-1),RIGHT(tel,LENGTH(tel)-INSTR(tel,'-'))
FROM employees;

-- 5、统计6月份出生的有多少人
SELECT COUNT(EmployeeID)
FROM employees
WHERE MONTH(eBirth)=6;

-- 6、查询星期三出生员工信息
SELECT *
FROM employees
WHERE DAYNAME(eBirth)='Wednesday';

-- 7、查询员工入职不超过1年的员工
SELECT *
FROM employees 
WHERE Workyear<1;

-- 8、查询在周五出生的员工
SELECT *
FROM employees
WHERE DAYNAME(eBirth)='Friday';

-- 9、将员工张三的出生日期分别按‘年’‘月’‘日’三列输出
SELECT eName,YEAR(eBirth) '年',MONTH(eBirth) '月',DAY(eBirth) '日'
FROM employees
WHERE eName='张三';

-- 10、根据员工张三的出生日期，查询2个月后的时间
SELECT DATE_ADD(eBirth, INTERVAL 2 MONTH)
FROM employees
WHERE eName='张三';

---

多表查询

• INNER JOIN
• LEFT JOIN
• RIGHT JOIN
• CROSS JOIN
• UNION

SELECT a.*,b.* FROM joinTest a RIGHT JOIN joinTest b on a.id=b.id;

---

正则表达式

例子：

SELECT name FROM person WHERE name REGEXP '^张'; -- 查询以'张'开头的
SELECT name FROM person WHERE name REGEXP '张$'; -- 查询以'张'结尾的
SELECT name FROM person WHERE name REGEXP '[a-zA-Z]'; -- 查询包含英文字母的