记录操作

mysql> create table t1 (id int,name varchar(20));
Query OK, 0 rows affected (0.19 sec)

insert into t1 values (1,'alex'),(2,'yuan'),(3,'nezha');
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from t1;
+------+-------+
| id   | name  |
+------+-------+
|    1 | alex  |
|    2 | yuan  |
|    3 | nezha |
+------+-------+

mysql> create table t2 (id int primary key auto_increment,sname varchar(20));
Query OK, 0 rows affected (0.17 sec)

 insert into t2 (sname) select name from t1;
select * from t2;
+----+-------+
| id | sname |
+----+-------+
|  1 | alex  |
|  2 | yuan  |
|  3 | nezha |
+----+-------+

mysql> insert into t2 (sname) select name from t1 where id < 3;
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql>
mysql>
mysql> select * from t2;
+----+-------+
| id | sname |
+----+-------+
|  1 | alex  |
|  2 | yuan  |
|  3 | nezha |
|  4 | alex  |
|  5 | yuan  |

mysql> update t2 set sname = 'boss_jin' where id = 4;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t2;
+----+----------+
| id | sname    |
+----+----------+
|  1 | alex     |
|  2 | yuan     |
|  3 | nezha    |
|  4 | boss_jin |
|  5 | yuan     |
+----+----------+
5 rows in set (0.00 sec)

mysql> select usr,host,password from mysql.user;
ERROR 1054 (42S22): Unknown column 'usr' in 'field list'
mysql> select user,host,password from mysql.user;
+-------+--------------+-------------------------------------------+
| user  | host         | password                                  |
+-------+--------------+-------------------------------------------+
| root  | localhost    | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| root  | 127.0.0.1    |                                           |
| root  | ::1          |                                           |
|       | localhost    |                                           |
| eva   | %            | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| egon2 | 192.168.16.% |                                           |
| egon1 | 192.168.16.* |                                           |
+-------+--------------+-------------------------------------------+
7 rows in set (0.00 sec)

mysql> update mysql.user set password = password('123') where user = 'egon1';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select user,host,password from mysql.user;
+-------+--------------+-------------------------------------------+
| user  | host         | password                                  |
+-------+--------------+-------------------------------------------+
| root  | localhost    | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| root  | 127.0.0.1    |                                           |
| root  | ::1          |                                           |
|       | localhost    |                                           |
| eva   | %            | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| egon2 | 192.168.16.% |                                           |
| egon1 | 192.168.16.* | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
+-------+--------------+-------------------------------------------+
7 rows in set (0.00 sec)

mysql> update mysql.user set password = '123' where user = 'egon2';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select user,host,password from mysql.user;
+-------+--------------+-------------------------------------------+
| user  | host         | password                                  |
+-------+--------------+-------------------------------------------+
| root  | localhost    | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| root  | 127.0.0.1    |                                           |
| root  | ::1          |                                           |
|       | localhost    |                                           |
| eva   | %            | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| egon2 | 192.168.16.% | 123                                       |
| egon1 | 192.168.16.* | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
+-------+--------------+-------------------------------------------+
7 rows in set (0.00 sec)

mysql> delete from t2 where id=5;
Query OK, 1 row affected (0.03 sec)

mysql> select * from t2;
+----+----------+
| id | sname    |
+----+----------+
|  1 | alex     |
|  2 | yuan     |
|  3 | nezha    |
|  4 | boss_jin |
+----+----------+
4 rows in set (0.00 sec)

mysql> delete from mysql.user where user = 'egon2';
Query OK, 1 row affected (0.00 sec)

mysql> select user,host,password from mysql.user;
+-------+--------------+-------------------------------------------+
| user  | host         | password                                  |
+-------+--------------+-------------------------------------------+
| root  | localhost    | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| root  | 127.0.0.1    |                                           |
| root  | ::1          |                                           |
|       | localhost    |                                           |
| eva   | %            | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| egon1 | 192.168.16.* | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
+-------+--------------+-------------------------------------------+
6 rows in set (0.00 sec)mysql> delete from t2 where id=5;
Query OK, 1 row affected (0.03 sec)

mysql> select * from t2;
+----+----------+
| id | sname    |
+----+----------+
|  1 | alex     |
|  2 | yuan     |
|  3 | nezha    |
|  4 | boss_jin |
+----+----------+
4 rows in set (0.00 sec)

mysql> delete from mysql.user where user = 'egon2';
Query OK, 1 row affected (0.00 sec)

mysql> select user,host,password from mysql.user;
+-------+--------------+-------------------------------------------+
| user  | host         | password                                  |
+-------+--------------+-------------------------------------------+
| root  | localhost    | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| root  | 127.0.0.1    |                                           |
| root  | ::1          |                                           |
|       | localhost    |                                           |
| eva   | %            | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| egon1 | 192.168.16.* | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
+-------+--------------+-------------------------------------------+
6 rows in set (0.00 sec)

单表查询

#简单查询
    SELECT id,emp_name,sex,age,hire_date,post,post_comment,salary,office,depart_id 
    FROM employee;

    SELECT * FROM employee;

    SELECT emp_name,salary FROM employee;

#避免重复DISTINCT
    SELECT DISTINCT post FROM employee;    

#通过四则运算查询
    SELECT emp_name, salary*12 FROM employee;
    SELECT emp_name, salary*12 AS Annual_salary FROM employee;
    SELECT emp_name, salary*12 Annual_salary FROM employee;

#定义显示格式
   CONCAT() 函数用于连接字符串
   SELECT CONCAT('姓名: ',emp_name,'  年薪: ', salary*12)  AS Annual_salary 
   FROM employee;
   
   CONCAT_WS() 第一个参数为分隔符
   SELECT CONCAT_WS(':',emp_name,salary*12)  AS Annual_salary 
   FROM employee;

   结合CASE语句：
   SELECT
       (
           CASE
           WHEN emp_name = 'jingliyang' THEN
               emp_name
           WHEN emp_name = 'alex' THEN
               CONCAT(emp_name,'_BIGSB')
           ELSE
               concat(emp_name, 'SB')
           END
       ) as new_name
   FROM
       emp;

单表查询语法
SELECT 字段1,字段2... FROM 表名
                     WHERE 条件
                     GROUP BY field
                     HAVING 筛选
                     ORDER BY field
                     LIMIT 限制条数

关键字执行的优先顺序
from      1.找到表:from
where     2.拿着where指定的约束条件，去文件/表中取出一条条记录
group by  3.将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组
having    4.将分组的结果进行having过滤
select    5.执行select
distinct  6.去重
order by  7.将结果按条件排序：order by
limit     8.限制结果的显示条数

#简单查询
    SELECT id,emp_name,sex,age,hire_date,post,post_comment,salary,office,depart_id 
    FROM employee;

    SELECT * FROM employee;

    SELECT emp_name,salary FROM employee;

#避免重复DISTINCT
    SELECT DISTINCT post FROM employee;    

#通过四则运算查询
    SELECT emp_name, salary*12 FROM employee;
    SELECT emp_name, salary*12 AS Annual_salary FROM employee;
    SELECT emp_name, salary*12 Annual_salary FROM employee;

#定义显示格式
   CONCAT() 函数用于连接字符串
   SELECT CONCAT('姓名: ',emp_name,'  年薪: ', salary*12)  AS Annual_salary 
   FROM employee;
   
   CONCAT_WS() 第一个参数为分隔符
   SELECT CONCAT_WS(':',emp_name,salary*12)  AS Annual_salary 
   FROM employee;

   结合CASE语句：
   SELECT
       (
           CASE
           WHEN emp_name = 'jingliyang' THEN
               emp_name
           WHEN emp_name = 'alex' THEN
               CONCAT(emp_name,'_BIGSB')
           ELSE
               concat(emp_name, 'SB')
           END
       ) as new_name
   FROM
       emp;

where字句中可以使用：
1. 比较运算符：> < >= <= <> !=
2. between 80 and 100 值在10到20之间
3. in(80,90,100) 值是10或20或30
4. like 'egon%'
    pattern可以是%或_，
    %表示任意多字符
    _表示一个字符 
5. 逻辑运算符：在多个条件直接可以使用逻辑运算符 and or not
#1:单条件查询
    SELECT emp_name FROM employee
        WHERE post='sale';
        
#2:多条件查询
    SELECT emp_name,salary FROM employee
        WHERE post='teacher' AND salary>10000;

#3:关键字BETWEEN AND
    SELECT emp_name,salary FROM employee 
        WHERE salary BETWEEN 10000 AND 20000;

    SELECT emp_name,salary FROM employee 
        WHERE salary NOT BETWEEN 10000 AND 20000;
    
#4:关键字IS NULL(判断某个字段是否为NULL不能用等号，需要用IS)
    SELECT emp_name,post_comment FROM employee 
        WHERE post_comment IS NULL;

    SELECT emp_name,post_comment FROM employee 
        WHERE post_comment IS NOT NULL;
        
    SELECT emp_name,post_comment FROM employee 
        WHERE post_comment=''; 注意''是空字符串，不是null
    ps：
        执行
        update employee set post_comment='' where id=2;
        再用上条查看，就会有结果了

#5:关键字IN集合查询
    SELECT emp_name,salary FROM employee 
        WHERE salary=3000 OR salary=3500 OR salary=4000 OR salary=9000 ;
    
    SELECT emp_name,salary FROM employee 
        WHERE salary IN (3000,3500,4000,9000) ;

    SELECT emp_name,salary FROM employee 
        WHERE salary NOT IN (3000,3500,4000,9000) ;

#6:关键字LIKE模糊查询
    通配符’%’
    SELECT * FROM employee 
            WHERE emp_name LIKE 'eg%';

    通配符’_’
    SELECT * FROM employee 
            WHERE emp_name LIKE 'al__';

单独使用GROUP BY关键字分组
    SELECT post FROM employee GROUP BY post;
    注意：我们按照post字段分组，那么select查询的字段只能是post，想要获取组内的其他相关信息，需要借助函数

GROUP BY关键字和GROUP_CONCAT()函数一起使用
    SELECT post,GROUP_CONCAT(emp_name) FROM employee GROUP BY post;#按照岗位分组，并查看组内成员名
    SELECT post,GROUP_CONCAT(emp_name) as emp_members FROM employee GROUP BY post;

GROUP BY与聚合函数一起使用
    select post,count(id) as count from employee group by post;#按照岗位分组，并查看每个组有多少人

#强调：聚合函数聚合的是组的内容，若是没有分组，则默认一组

示例：
    SELECT COUNT(*) FROM employee;
    SELECT COUNT(*) FROM employee WHERE depart_id=1;
    SELECT MAX(salary) FROM employee;
    SELECT MIN(salary) FROM employee;
    SELECT AVG(salary) FROM employee;
    SELECT SUM(salary) FROM employee;
    SELECT SUM(salary) FROM employee WHERE depart_id=3;

having 过滤
！！！执行优先级从高到低：where > group by > having 
1. Where 发生在分组group by之前，因而Where中可以有任意字段，但是绝对不能使用聚合函数。
2. Having发生在分组group by之后，因而Having中可以使用分组的字段，无法直接取到其他字段,可以使用聚合函数
group by+having

order by 排序
按单列排序
    SELECT * FROM employee ORDER BY salary;
    SELECT * FROM employee ORDER BY salary ASC;
    SELECT * FROM employee ORDER BY salary DESC;

按多列排序:先按照age排序，如果年纪相同，则按照薪资排序
    SELECT * from employee
        ORDER BY age,
        salary DESC;

LIMIT 限制查询的记录数
示例：
    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条

使用正则表达式查询
SELECT * FROM employee WHERE emp_name REGEXP '^ale';

SELECT * FROM employee WHERE emp_name REGEXP 'on$';

SELECT * FROM employee WHERE emp_name REGEXP 'm{2}';


小结：对字符串匹配的方式
WHERE emp_name = 'egon';
WHERE emp_name LIKE 'yua%';
WHERE emp_name REGEXP 'on$';

 多表查询

#建表
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> desc department;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+

mysql> desc employee;
+--------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-----------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
| sex | enum('male','female') | NO | | male | |
| age | int(11) | YES | | NULL | |
| dep_id | int(11) | YES | | NULL | |
+--------+-----------------------+------+-----+---------+----------------+

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 |
+----+------------+--------+------+--------+

表department与employee

sql示例

外链接语法
SELECT 字段列表
    FROM 表1 INNER|LEFT|RIGHT JOIN 表2
    ON 表1.字段 = 表2.字段;

交叉连接：不适用任何匹配条件。生成笛卡尔积

表与表之间产生了一个暴力全集 —— 笛卡儿积
    # [a,b,c],[1,2,3]
    # a1,b1,c1,a2,b2,c2,a3,b3,c3

#找两张表共有的部分，相当于利用条件从笛卡尔积结果中筛选出了正确的结果
#department没有204这个部门，因而employee表中关于204这条员工信息没有匹配出来
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   | 技术         |
+----+-----------+------+--------+--------------+

#上述sql等同于
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      | 运营         |
+------+-----------+--------------+

全外连接：在内连接的基础上增加左边有右边没有的和右边有左边没有的结果
#注意：mysql不支持全外连接 full JOIN
#强调：mysql可以使用此种方式间接实现全外连接
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;

#示例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;

子查询
#1：子查询是将一个查询语句嵌套在另一个查询语句中。
#2：内层查询语句的查询结果，可以为外层查询语句提供查询条件。
#3：子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
#4：还可以包含比较运算符：= 、 !=、> 、<等

#查询平均年龄在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);
+---------+------+
| name | age |
+---------+------+
| alex | 48 |
| wupeiqi | 38 |
+---------+------+
rows in set (0.00 sec)


#查询大于部门内平均年龄的员工名、年龄
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);
+----+------------+--------+------+--------+
| 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 |
+----+------------+--------+------+--------+

#department表中存在dept_id=205，False
mysql> select * from employee
    ->     where exists
    ->         (select id from department where id=204);
Empty set (0.00 sec)

select ... from   查找
distinct    合并相同的项
limit /limit ... offset   限制输出
order by  排序
order by ... desc   排序，降序
where/ where ... between ... and ...   条件判断输出
where ... and   多条件，同时符合
where ... or     多条件，符合其中一个
where ... in     在某一个范围中
like    通配筛选
regexp   利用正则表达式筛选
concat   字段拼接
concat ... as   字段拼接后重命名

count avg max min sum  使用函数对列进行操作
group by    分组
having   分组后筛选
where ... group by ... having   先筛选再分组再筛选
union   组合查询，多个select
union all  组合查询，不合并相同信息
Match Against 搜索，match字段，against内容
Insert into   插入
update ... set  更新内容
delete from   删除
create   创建
alter table ... add/drop   增加/删除表字段
drop 删除表/库
rename  重命名