SQL语言学习
学习课程：姚远老师 https://www.bilibili.com/video/BV1B34y1R7S8?spm_id_from=333.999.0.0
SQL是Structured Query Language的缩写;
1. 创建部门表
mysql> create table dept(detpno int primary key,danme varchar(9),loc varchar(10)); Query OK, 0 rows affected (0.02 sec)
2.创建员工表
mysql> create table employees( -> empno int primary key, -> name char(10) not null, -> deptno int, -> manager int, -> hiredate date, -> salary numeric(7,2)); Query OK, 0 rows affected (0.04 sec) mysql> desc employees; +----------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------+------+-----+---------+-------+ | empno | int(11) | NO | PRI | NULL | | | name | char(10) | NO | | NULL | | | deptno | int(11) | YES | | NULL | | | manager | int(11) | YES | | NULL | | | hiredate | date | YES | | NULL | | | salary | decimal(7,2) | YES | | NULL | | +----------+--------------+------+-----+---------+-------+ 6 rows in set (0.01 sec)
3.创建经理表
mysql> create table manages( -> empno int primary key, -> title varchar(16)); Query OK, 0 rows affected (0.03 sec)
4.常用的数据类型
5.insert语句
mysql> insert into employees values(20,'liming',30,22,'2022-04-19',5000.99); Query OK, 1 row affected (0.01 sec)
mysql> insert into employees(empno,name,deptno,hiredate,salary) values(2,'suosuo',20,'2022-04-19',7000);
Query OK, 1 row affected (0.01 sec)
6.update语句
mysql> update employees set salary=9000 where empno=3; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0
7.alter语句更改列名
mysql> alter table dept change detpno deptno int; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0
8.delete语句（如果没有where子句，将删除说有的数据）
mysql> delete from employees where empno=10; Query OK, 1 row affected (0.03 sec)
9、index索引
mysql> create index empno_inx on employees(empno); Query OK, 0 rows affected, 1 warning (0.12 sec) Records: 0 Duplicates: 0 Warnings: 1
10.view视图（view视图是基于SQL语句结果集的可视化表）
mysql> create view gxemploy as select empno,salary from employees where salary>=8000; Query OK, 0 rows affected (0.01 sec) mysql> select * from gxemploy; +-------+---------+ | empno | salary | +-------+---------+ | 3 | 9000.00 | | 5 | 8000.00 | | 6 | 8000.00 | | 7 | 8000.00 | | 8 | 8000.00 | | 9 | 8000.00 | +-------+---------+ 6 rows in set (0.01 sec)
11、null值，代表遗漏的未知数据，它的作用是未知的或者不适用值的占位符，字段值是否是null值的判断不能用>,=,要用is null或者 is not null
mysql> select * from employees where manager is null; +-------+--------+--------+---------+------------+---------+ | empno | name | deptno | manager | hiredate | salary | +-------+--------+--------+---------+------------+---------+ | 2 | suosuo | 20 | NULL | 2022-04-19 | 7000.00 | +-------+--------+--------+---------+------------+---------+ 1 row in set (0.00 sec)
12、内连接
mysql> select e.empno,e.name as ename,d.empno as mno,d.name as mname from employees e inner join employees d on d.empno=e..manage; +-------+-----------+-----+-----------+ | empno | ename | mno | mname | +-------+-----------+-----+-----------+ | 3 | lele | 2 | suosuo | | 4 | kangkang | 2 | suosuo | | 5 | kangkang5 | 2 | suosuo | | 6 | kangkang6 | 2 | suosuo | | 7 | kangkang7 | 2 | suosuo | | 8 | kangkang8 | 2 | suosuo | | 9 | kangkang9 | 5 | kangkang5 | +-------+-----------+-----+-----------+ 7 rows in set (0.01 sec)
13、左连接
mysql> select e.empno,e.name,m.title as deptname from employees e left join manages m on e.deptno=m.empno; +-------+-----------+----------+ | empno | name | deptname | +-------+-----------+----------+ | 4 | kangkang | zonghe | | 5 | kangkang5 | zonghe | | 6 | kangkang6 | zonghe | | 7 | kangkang7 | zonghe | | 8 | kangkang8 | zonghe | | 9 | kangkang9 | zonghe | | 2 | suosuo | jishubu | | 3 | lele | xiaoshou | | 10 | xiayibu | NULL | +-------+-----------+----------+ 9 rows in set (0.00 sec)
14、右连接
mysql> select e.empno,e.name,m.title as deptname from employees e right join manages m on e.deptno=m.empno; +-------+-----------+----------+ | empno | name | deptname | +-------+-----------+----------+ | 2 | suosuo | jishubu | | 3 | lele | xiaoshou | | 4 | kangkang | zonghe | | 5 | kangkang5 | zonghe | | 6 | kangkang6 | zonghe | | 7 | kangkang7 | zonghe | | 8 | kangkang8 | zonghe | | 9 | kangkang9 | zonghe | | NULL | NULL | caiwu | +-------+-----------+----------+ 9 rows in set (0.01 sec)
15、count函数，count(*)统计表中的记录总数，count( 字段名)统计字段不为null的记录数
mysql> select count(*) from employees; +----------+ | count(*) | +----------+ | 9 | +----------+ 1 row in set (0.01 sec)
16、max函数，所选字段的最大值，min所选字段的最小值
mysql> select empno,max(salary) from employees; +-------+-------------+ | empno | max(salary) | +-------+-------------+ | 2 | 9000.00 | +-------+-------------+ 1 row in set (0.00 sec) mysql> mysql> select empno,min(salary) from employees; +-------+-------------+ | empno | min(salary) | +-------+-------------+ | 2 | 5000.00 | +-------+-------------+ 1 row in set (0.00 sec)
17、avg平均值，sum总和
mysql> select avg(salary) from employees; +-------------+ | avg(salary) | +-------------+ | 7444.444444 | +-------------+ 1 row in set (0.00 sec) mysql> select sum(salary) from employees; +-------------+ | sum(salary) | +-------------+ | 67000.00 | +-------------+ 1 row in set (0.00 sec)
18、group by 分组 having子句，gourp by之后用having
mysql> select deptno,avg(salary) from employees group by deptno; +--------+-------------+ | deptno | avg(salary) | +--------+-------------+ | 10 | 7666.666667 | | 20 | 7000.000000 | | 30 | 9000.000000 | | 50 | 5000.000000 | +--------+-------------+ 4 rows in set (0.00 sec) mysql> select deptno,avg(salary) from employees group by deptno having avg(salary)>7000; +--------+-------------+ | deptno | avg(salary) | +--------+-------------+ | 10 | 7666.666667 | | 30 | 9000.000000 | +--------+-------------+ 2 rows in set (0.00 sec)
