mysql數據操作

select concat('<名字:',name,'>    ','<薪資:',salary,'>') from employee;
select distinct depart_id from employee;
select name,salary*12 annual_salary from employee;

# 1:單條件查詢
SELECT name FROM employee WHERE post = 'sale';
# 2:多條件查詢
SELECT name, salary FROM employee WHERE post = 'teacher' AND salary > 10000;
# 3:關鍵字BETWEEN AND
SELECT name, salary FROM employee WHERE salary BETWEEN 10000 AND 20000;
SELECT name, salary FROM employee WHERE salary NOT BETWEEN 10000 AND 20000;
# 4:關鍵字IS NULL(判斷某個字段是否為NULL不能用等號，需要用IS)
SELECT name, post_comment FROM employee WHERE post_comment IS NULL;
SELECT name, post_comment FROM employee WHERE post_comment IS NOT NULL;
SELECT name, post_comment FROM employee WHERE post_comment = '';  #注意''是空字符串，不是null
ps：
    執行
    update employee set post_comment = '' where id = 2;
    再用上條查看，就會有結果了
# 5:關鍵字IN集合查詢
SELECT name, salary FROM employee WHERE salary = 3000 OR salary = 3500 OR salary = 4000 OR salary = 9000;
SELECT name, salary FROM employee WHERE salary IN(3000, 3500, 4000, 9000);
SELECT name, salary FROM employee WHERE salary NOT IN(3000, 3500, 4000, 9000);
# 6:關鍵字LIKE模糊查詢
通配符’ % ’
SELECT * FROM employee WHERE name LIKE 'eg%';
通配符’_’
SELECT * FROM employee WHERE name LIKE 'al__';

select name,age from employee where post = 'teacher';
select name,age from employee where post='teacher' and age > 30;
select name,age,salary from employee where post='teacher' and salary between 9000 and 10000;
select * from employee where post_comment is not null;
select name,age,salary from employee where post='teacher' and salary in (10000,9000,30000);
select name,age,salary from employee where post='teacher' and salary not in (10000,9000,30000);
select name,salary*12 from employee where post='teacher' and name like 'jin%';
mysql> select name,salary*12 as year_salary from employee where post='teacher' and name like 'jin%';

mysql> select post,group_concat(name) from employee group by post;
mysql> select post,count(id) from employee group by post;
mysql> select sex,count(id) from employee group by sex;
mysql> select post,avg(salary) from employee group by post;
mysql> select post,max(salary) from employee group by post;
mysql> select post,min(salary) from employee group by post;
mysql> select sex,avg(salary) from employee group by sex;

mysql> select post,group_concat(name),count(id) from employee group by post having count(id) < 2;
mysql> select post,avg(salary) from employee group by post having avg(salary) > 10000;
mysql> select post,avg(salary) from employee group by post having avg(salary) > 10000 and avg(salary) <20000;

mysql> select * from employee ORDER BY age asc,hire_date desc;
mysql> select post,avg(salary) from employee group by post having avg(salary) > 10000 order by avg(salary) asc;
mysql> select post,avg(salary) from employee group by post having avg(salary) > 10000 order by avg(salary) desc;

SELECT * FROM employee ORDER BY salary DESC LIMIT 3;  # 默認初始位置為0
SELECT * FROM employee ORDER BY salary DESC LIMIT 0, 5;  # 從第0開始，即先查詢出第一條，然后包含這一條在內往后查5條
SELECT * FROM employee ORDER BY salary DESC LIMIT 5, 5;  # 從第5開始，即先查詢出第6條，然后包含這一條在內往后查5條

mysql> select * from  employee limit 0,5;              #顯示第1到5條記錄
mysql> select * from  employee limit 5,5;              #顯示第5到10條記錄
mysql> select * from  employee limit 10,5;             #顯示第10到15條記錄

SELECT * FROM employee WHERE name REGEXP '^ale';
SELECT * FROM employee WHERE name REGEXP 'on$';
SELECT * FROM employee WHERE name REGEXP 'm{2}';

create table department(id int,name varchar(20));
create table employee(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
age int,
dep_id int
);
#插入數據
insert into department values(200,'技術'),(201,'人力資源'),(202,'銷售'),(203,'運營');
insert into employee(name,sex,age,dep_id) values
('egon','male',18,200),
('alex','female',48,201),
('wupeiqi','male',38,201),
('yuanhao','female',28,202),
('liwenzhou','male',18,200),
('jingliyang','female',18,204)
;

mysql> select * from department;
+------+--------------+
| id   | name         |
+------+--------------+
|  200 | 技術         |
|  201 | 人力資源     |
|  202 | 銷售         |
|  203 | 運營         |
+------+--------------+
mysql> select * from employee;
+----+------------+--------+------+--------+
| id | name       | sex    | age  | dep_id |
+----+------------+--------+------+--------+
|  1 | egon       | male   |   18 |    200 |
|  2 | alex       | female |   48 |    201 |
|  3 | wupeiqi    | male   |   38 |    201 |
|  4 | yuanhao    | female |   28 |    202 |
|  5 | liwenzhou  | male   |   18 |    200 |
|  6 | jingliyang | female |   18 |    204 |
+----+------------+--------+------+--------+
mysql> select * from employee,department;

mysql> select employee.id,employee.name,employee.age,employee.sex,department.name from employee inner join department on employee.dep_id=department.id;
+----+-----------+------+--------+--------------+
| id | name      | age  | sex    | name         |
+----+-----------+------+--------+--------------+
|  1 | egon      |   18 | male   | 技術         |
|  2 | alex      |   48 | female | 人力資源     |
|  3 | wupeiqi   |   38 | male   | 人力資源     |
|  4 | yuanhao   |   28 | female | 銷售         |
|  5 | liwenzhou |   18 | male   | 技術         |
+----+-----------+------+--------+--------------+
mysql> select employee.id,employee.name,employee.age,employee.sex,department.name from employee,department where employee.dep_id=department.id;

mysql> select employee.id,employee.name,department.name as depart_name from employee left join department on employee.dep_id=department.id;
+----+------------+--------------+
| id | name       | depart_name  |
+----+------------+--------------+
|  1 | egon       | 技術         |
|  5 | liwenzhou  | 技術         |
|  2 | alex       | 人力資源     |
|  3 | wupeiqi    | 人力資源     |
|  4 | yuanhao    | 銷售         |
|  6 | jingliyang | NULL         |
+----+------------+--------------+

mysql> select employee.id,employee.name,department.name as depart_name from employee right join department on employee.dep_id=department.id;
+------+-----------+--------------+
| id   | name      | depart_name  |
+------+-----------+--------------+
|    1 | egon      | 技術         |
|    2 | alex      | 人力資源     |
|    3 | wupeiqi   | 人力資源     |
|    4 | yuanhao   | 銷售         |
|    5 | liwenzhou | 技術         |
| NULL | NULL      | 運營         |
+------+-----------+--------------+

select * from employee left join department on employee.dep_id = department.id
union
select * from employee right join department on employee.dep_id = department.id
;
+------+------------+--------+------+--------+------+--------------+
| id   | name       | sex    | age  | dep_id | id   | name         |
+------+------------+--------+------+--------+------+--------------+
|    1 | egon       | male   |   18 |    200 |  200 | 技術         |
|    5 | liwenzhou  | male   |   18 |    200 |  200 | 技術         |
|    2 | alex       | female |   48 |    201 |  201 | 人力資源     |
|    3 | wupeiqi    | male   |   38 |    201 |  201 | 人力資源     |
|    4 | yuanhao    | female |   28 |    202 |  202 | 銷售         |
|    6 | jingliyang | female |   18 |    204 | NULL | NULL         |
| NULL | NULL       | NULL   | NULL |   NULL |  203 | 運營         |
+------+------------+--------+------+--------+------+--------------+
#注意 union與union all的區別：union會去掉相同的紀錄

#示例1：以內連接的方式查詢employee和department表，并且employee表中的age字段值必須大于25,即找出年齡大于25歲的員工以及員工所在的部門
select employee.name,department.name from employee inner join department on employee.dep_id = department.id where age > 25;
+---------+--------------+
| name    | name         |
+---------+--------------+
| alex    | 人力資源     |
| wupeiqi | 人力資源     |
| yuanhao | 銷售         |
+---------+--------------+
#示例2：以內連接的方式查詢employee和department表，并且以age字段的升序方式顯示
select employee.id,employee.name,employee.age,department.name from employee,department where employee.dep_id = department.id and age > 25 order by age asc;
+----+---------+------+--------------+
| id | name    | age  | name         |
+----+---------+------+--------------+
|  4 | yuanhao |   28 | 銷售         |
|  3 | wupeiqi |   38 | 人力資源     |
|  2 | alex    |   48 | 人力資源     |
+----+---------+------+--------------+

#查詢平均年齡在25歲以上的部門名
select id,name from department where id in (select dep_id from employee group by dep_id having avg(age) > 25);
#查看技術部員工姓名
select name from employee where dep_id in (select id from department where name='技術');
#查看不足1人的部門名(子查詢得到的是有人的部門id)
select name from department where id not in (select distinct dep_id from employee);

#查詢大于所有人平均年齡的員工名與年齡
mysql> select name,age from emp where age > (select avg(age) from emp);
#查詢大于部門內平均年齡的員工名、年齡
select t1.name,t1.age from emp t1
inner join
(select dep_id,avg(age) avg_age from emp group by dep_id) t2 on t1.dep_id = t2.dep_id where t1.age > t2.avg_age;

#department表中存在dept_id=203，Ture
mysql> select * from employee where exists (select id from department where id=200);
#department表中存在dept_id=205，False
mysql> select * from employee where exists (select id from department where id=204);