【SQL 个人笔记】基本 SQL 语句、多表连接查询及关键字 LIMIT、HAVING（附练习案例）

———————————————————————————————————————————————————————

基本语句

1. 查询语句

SELECT 列名1,列名2,… FROM 表名 WHERE 查询条件

SELECT name,salary FROM t_student WHERE sex = '男';

2. 添加语句

若主键列为自动递增，则添加时不必加上主键列名，且不必给值。

INSERT INTO 表名(列名1,列名2,列名3,…) VALUES(值1,值2,值3,…);

INSERT INTO t_student(name,age,sex,salary) VALUES('张三',25,'男',5000);

3. 修改语句

UPDATE 表名 SET 列名 = 值,age = 值 WHERE 列名 = 值;

UPDATE t_student SET name = '李四',age = 36 WHERE name = '张三';

4. 删除语句

DELETE FROM 表名 WHERE 列名 = 值 AND 列名 = 值;

DELETE FROM t_student WHERE name = '张三' AND id = 2;

5. 一些命令行中的 SQL 指令

mysql -uroot -proot

show databases;

use test01;

Ctrl+C -- exit

———————————————————————————————————————————————————————

原始表创建及插入数据的代码

drop table if exists emp;
drop table if exists dept;
drop table if exists salgrade;

create table dept(deptno int(2) not null ,dname varchar(14) ,loc varchar(13),primary key (deptno));
create table emp(empno int(4)  not null ,ename varchar(10),job varchar(9),mgr int(4),hiredate date  default null,sal double(7,2),comm double(7,2),primary key (empno),deptno int(2));

create table salgrade(grade int,losal int,hisal int );

insert into dept(deptno, dname, loc) values (10, 'accounting', 'new york'); 
insert into dept(deptno, dname, loc) values (20, 'research', 'dallas'); 
insert into dept(deptno, dname, loc) values (30, 'sales', 'chicago'); 
insert into dept(deptno, dname, loc) values (40, 'operations', 'boston'); 
commit;
 
insert into emp(empno, ename, job, mgr, hiredate, sal, comm,deptno) values (7369, 'smith', 'clerk', 7902,  '1980-12-17', 800, null, 20); 
insert into emp(empno, ename, job, mgr, hiredate, sal, comm,deptno) values (7499, 'allen', 'salesman', 7698,  '1981-02-20', 1600, 300, 30); 
insert into emp(empno, ename, job, mgr, hiredate, sal, comm,deptno) values (7521, 'ward', 'salesman', 7698,  '1981-02-22', 1250, 500, 30); 
insert into emp(empno, ename, job, mgr, hiredate, sal, comm,deptno) values (7566, 'jones', 'manager', 7839,  '1981-04-02', 2975, null, 20); 
insert into emp(empno, ename, job, mgr, hiredate, sal, comm,deptno) values (7654, 'martin', 'salesman', 7698,  '1981-09-28', 1250, 1400, 30); 
insert into emp(empno, ename, job, mgr, hiredate, sal, comm,deptno) values (7698, 'blake', 'manager'