《MY SQL》数据表查询操作

Hello! 我是做事very 小心的小新！很高兴又见到你。上期我们讲了《MY SQL》的内容。这期我们来学习新的技能：数据表查询操作。
CREATE DATABASE readbook;
USE readbook;

CREATE TABLE depts
(
d_no INT NOT NULL PRIMARY KEY auto_increment,
d_name VARCHAR(50),
d_location VARCHAR(100)
);

CREATE TABLE employees
(
e_no INT NOT NULL PRIMARY KEY,
e_name VARCHAR(100) NOT NULL,
e_gender CHAR(2) NOT NULL,
dept_no INT NOT NULL,
e_job VARCHAR(100) NOT NULL,
e_salary SMALLINT NOT NULL,
hirdate DATE,
CONSTRAINT dno_fk foreign key(dept_no)
REFERENCES depts(d_no)
);

INSERT INTO depts VALUES(10,‘accounting’,‘shanghai’),
(20,‘research’,‘beijng’),
(30,‘sales’,‘shenzhen’),
(40,‘operations’,‘fujian’);

INSERT INTO employees VALUES(1001,‘smith’, ‘m’,20, ‘clerk’,800,‘2005-11-12’),
(1002, ‘allen’,‘f’,30,‘salesman’,1600,‘2003-05-12’),
(1003, ‘ward’,‘f’,30,‘salesman’,1250,‘2003-05-12’),
(1004, ‘jones’,‘m’,20,‘manager’,2975,‘1998-05-18’),
(1005, ‘martin’,‘m’,30,‘salesman’,1250,‘2001-06-12’),
(1006, ‘blake’,‘f’,30,‘manager’,2850,‘1997-02-15’),
(1007, ‘clark’,‘m’,10,‘manager’,2450,‘2002-09-12’),
(1008, ‘scott’,‘m’,20,‘analyst’,3000,‘2003-05-12’),
(1009, ‘king’,‘f’,10,‘president’,5000,‘1995-01-01’),
(1010, ‘turner’,‘f’,30,‘salesman’,1500,‘1997-10-12’),
(1011, ‘adams’,‘m’,20,‘clerk’,1100,‘1999-10-05’),
(1012, ‘james’,‘m’,30,‘clerk’,950,‘2008-06-15’);

SELECT e_no,e_name,e_salary FROM employees;

SELECT * FROM employees where dept_no in (10,20);

SELECT * FROM employees WHERE e_salary BETWEEN 800 AND 2500;

SELECT * FROM employees WHERE dept_no = 20;

SELECT dept_no,MAX(e_salary) FROM employees GROUP BY dept_no;

SELECT d_no, d_location FROM depts WHERE d_no =
(SELECT dept_no FROM employees WHERE e_name = ‘blake’);

SELECT e_no, e_name, dept_no, d_name, d_location FROM employees,depts
WHERE depts.d_no = employees.dept_no;

SELECT dept_no, COUNT(’*’) FROM employees GROUP BY dept_no;

SELECT e_job,SUM(e_salary ) FROM employees GROUP BY e_job;

SELECT dept_no, AVG(e_salary) FROM employees GROUP BY dept_no;

SELECT * FROM employees WHERE e_salary<1500;

SELECT e_name, dept_no, e_salary FROM employees ORDER BY dept_no DESC, e_salary DESC;

SELECT * FROM employees WHERE e_name REGEXP ‘¹’;

SELECT * FROM employees WHERE YEAR(CURDATE()) -YEAR(hirdate) >=15;

---

1. as ↩︎