select语句练习准备素材_select 数据准备-CSDN博客

本文通过创建和填充`dept`、`emp`和`salgrade`表格，展示了MySQL中的基本数据插入和引用。重点讲解了SELECT语句在数据库查询中的核心作用，包括如何选择特定列、连接多个表以及使用外键约束。通过对示例数据的查询，读者可以深入了解SQL查询的基本语法和应用。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

select语句在mysql语句中是重中之重

准备素材

show databases;

create database fairykunkun default charset utf8;

show databases;

use fairykunkun;

create table dept (
    deptno int primary key,
    dname varchar(50),
    loc varchar(50)
) engine = Innodb;

create table emp (
    empno int primary key,
    ename varchar(20),
    job varchar(20),
    mgr int,
    hiredate date,
    sal double,
    comm double,
    deptno int
) engine = Innodb;

alter table emp
    add constraint fk_deptno foreign key(deptno) references dept(deptno);

create table salgrade (
    grade int primary key,
    losal int,
    hisal int
) engine = Innodb;

 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');

   insert into emp(empno,ename,job,mgr,hiredate,sal,deptno)
           values(7369,'SMITH','CLERK',7902,'1980/12/17',800.00,20);
   insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
           values(7499,'ALLEN','SALESMAN',7698,'1981/2/20',1600.00,300.00,30);
   insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
           values(7521,'WARD','SALESMAN',7698,'1981/2/22',1250.00,500.00,30);
   insert into emp(empno,ename,job,mgr,hiredate,sal,deptno)
           values(7566,'JONES','MANAGER',7839,'1981/4/2',2975.00,20);
   insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
           values(7654,'MARTIN','SALESMAN',7698,'1981/9/28',1250.00,1400.00,30);  
   insert into emp(empno,ename,job,mgr,hiredate,sal,deptno)
           values(7698,'BLAKE','MANAGER',7839,'1981/5/1',2850.00,30);
   insert into emp(empno,ename,job,mgr,hiredate,sal,deptno)
           values(7782,'CLARK','MANAGER',7839,'1981/6/9',2450.00,10);
   insert into emp(empno,ename,job,mgr,hiredate,sal,deptno)
           values(7788,'SCOTT','ANALYST',7566,'1987/4/19',3000.00,20);
   insert into emp(empno,ename,job,hiredate,sal,deptno)
           values(7839,'KING','PRESIDENT','1981/11/17',5000.00,10);
   insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
           values(7844,'TURNER','SALESMAN',7698,'1981/9/8',1500.00,0.00,30);
   insert into emp(empno,ename,job,mgr,hiredate,sal,deptno)
           values(7876,'ADAMS','CLERK',7788,'1987/5/23',1100.00,20);
   insert into emp(empno,ename,job,mgr,hiredate,sal,deptno)
           values(7900,'JAMES','CLERK',7698,'1981/12/3',950.00,30);
   insert into emp(empno,ename,job,mgr,hiredate,sal,deptno)
           values(7902,'FORD','ANALYST',7566,'1981/12/3',3000.00,20);
   insert into emp(empno,ename,job,mgr,hiredate,sal,deptno)
           values(7934,'MILLER','CLERK',7782,'1982/1/23',1300.00,10);

   insert into salgrade(grade,losal,hisal)values(1,700,1200);
   insert into salgrade(grade,losal,hisal)values(2,1201,1400);
   insert into salgrade(grade,losal,hisal)values(3,1401,2000);
   insert into salgrade(grade,losal,hisal)values(4,2001,3000);
   insert into salgrade(grade,losal,hisal)values(5,3001,9999);