devbox
酷酷是懒虫
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

数据库(mysql简单基础入门)_创建数据库mytestdb,编码为gbk

作者：酷酷是懒虫 | 2024-07-23 10:35:23

赞

踩

创建数据库mytestdb,编码为gbk

  • 数据库,存储数据的仓库就称为数据库.(低效,不通用)

  • 我们创建的一个目录users,里面放了很多的obj文件,每个文件保存了一组信息,users就是一个数据库.

  • 数据库,只是我们管理起来要么手动,要么写一些代码进行维护

数据库管理系统DBMS(DataBaseManagementSystem)

常见的数据里管理系统:

Mysql,ORACLE,SQLServer,DB2

目前主要学习:

SQL:Structured   Query Language

sql语句是我们操作数据库的语言,通过执行sql可以完成数据库的相关操作

连接数据库的形式:

  • 1,控制台(命令行)的客户端
  • 2.第三方图形界面的软件
  • 3.IDEA这样的集成开发环境也带有连接数据库的操作
  • 4.使用JDBC连接数据库

正式进入SQL,我们主要学习的就是SQL基础

查看当前mysql有多少个数据库:

SHOW DATABASES;

创建数据库:

  1. CREATE DATABASE 数据库名字
  2. eg:创建数据库:mydb
  3. create database mydb;

数据库在创建时候可以指定字符集

  1. CREATE DATABASE 数据库名字 CHARSET=UTF8/GBK
  2. eg:创建数据库db1(字符集用户gbk),db2(字符集用utf8)
  3. create database db1 charset=gbk;
  4. create database db2 charset=utf8;
  5.  
  6.

查看数据库信息

  1. SHOW CREATE DATABASE 数据库名
  2. SHOW CREATE DATABASE db2;

删除数据库

  1. DROP DATABASE 数据库名字
  2.  
  3. drop database db1;

想要保存数据,就要选取需要保存数据的数据库,然后才能在该数据库中建立表等操作

  1. USE 数据库名
  2. eg:使用mydb这个数据库
  3. USE mydb;

练习

  1. 练习:
  2. # 1. 创建 mydb1和mydb2 数据库 字符集分别为utf8和gbk
  3. CREATE DATABASE mydb1 CHARSET=utf8;
  4. CREATE DATABASE mydb2 CHARSET=gbk;
  5. # 2. 查询所有数据库检查是否创建成功
  6. SHOW DATABASES;
  7. # 3. 检查两个数据库的字符集是否正确
  8. SHOW CREATE DATABASE mydb1;
  9. SHOW CREATE DATABASE mydb2;
  10. # 4. 先使用mydb2 再使用 mydb1
  11. USE mydb2;
  12. USE mydb1;
  13. # 5. 删除两个数据库
  14. DROP DATABASE mydb1;
  15. DROP DATABASE mydb2;
  16.
  • # 在mysql中,我们可以为不同的项目创建不同的数据库.而每个数据库中都可以创建若干张表.每张表
  • # 用来保存一组数据.比如我们为保存用户信息可以创建userinfo表.保存文章信息可以创建article表等

SQL语句分类:

  • DDL:data definition language是数据定义语言
  • DML:data manipulation language是数据操纵语言
  • DQL:Data QueryLanguage 数据查询语言
  • TCL:(Transaction Control Language)事务控制语句
  • DCL:DataControlLanguage是数据库控制语言：是用来设置或更改数据库用户或角色权限的语句，包括（grant,deny,revoke等）语句。

表相关操作
DDL语句:数据定义语言,用来操作数据库对象的
数据库对象:表,视图,索引都属于数据库对象

创建表

注:[ ]中括号中的内容可写可不写,然后VARCHHAR(n):n为数字,长度表示位数,vachar的长度表示最多占多少个字节

  1. CREATE TABLE 表明()
  2.  
  3.  列名1 类型[(长度)] [DEFAULT 默认值] [约束条件],
  4.  列名2 类型...
  5. )[CHARSET=UTF8/GBK]
  6.  
  7. eg:创建userinfo表
  8. create table userinfo(
  9.       id INT,
  10.       username   VACHAR(20),
  11.       password   VACHAR(20),
  12.       nickname   VACHAR(20),
  13.       age INT(3)
  14. )

查看创建的某一张表的详细信息

  1. SHOW CREATE TABLE 表名;
  2. show create table userinfo;

查看表结构

  1. DESC 表名;
  2. desc userinfo;

删除表

  1. DROP TABLE 表名;
  2. drop table userinfo;

删除以后要想在使用该表,需要再次创建

修改表

修改表名

  1. RENAME TABLE 原表名 TO 新表名
  2. RENAME TABLE userinfo TO user;

练习:

  1. # 1. 创建数据库mydb3 字符集gbk 并使用
  2. CREATE DATABASE mydb3 CHARSET=gbk;
  3. USE mydb3;
  4. # 2. 创建t_hero英雄表, 有名字和年龄字段 默认字符集
  5. CREATE TABLE t_hero(
  6.       id INT,
  7.       name VARCHAR(32),
  8.       age INT(3)
  9. );
  10. # 3. 修改表名为hero
  11. RENAME TABLE t_hero TO hero;
  12. # 4. 查看表hero的字符集
  13. SHOW CREATE TABLE hero;
  14. # 5. 查询表hero结构
  15. DESC hero;
  16. # 6. 删除表hero
  17. DROP TABLE hero;
  18. # 7. 删除数据库mydb3
  19. DROP DATABASE mydb3;

修改表结构:ALTER TABLE

实际开发中,通常不建议在表中含有数据是修改表结构

添加列

  1. ALTER TABLE 表名 ADD 列名 类型[长度]
  2. eg:添加一列gender性别,是vachar(10)类型
  3. ALTER TABLE user ADD gender VARCHAR(10);

在表中插入一个字段

  1. 在表的第一列添加新的列
  2. ALTER TABLE 表名 ADD 列名 列型[长度] FIRST;
  3. 在英雄表第一列插入id
  4. ALTER TABLE hero ADD id INT FIRST;
  5.  
  6. 在表中某一列之后插入一个字段
  7. ALTER TABLE 表名 ADD 列名[长度] AFTER 字段名
  8. eg:在hero 表中的username列之后插入一个性别字段
  9. alter table hero add gender varchar(10) after username;

删除表中现有的列

注意:当表中含有数据后,字段类型尽量不要改变,长度修改尽量不要减少,否则都有可能违背表中现有数据导致修改失败

更换表中字段类型

  1. ALTER TABLE 表名 CHANGE 原字段名 新字段名 新类型
  2. eg:将age的类型从int型换成varchar型
  3. ALTER TABLE hero CHANGE age age VARCHAR(10);
  4.  
  5. 将age的长度改为100
  6. alter table hero change age age varchar(100);
  7.  
  8. 将age改为gender,长度为10
  9. alter table hero change age gender varchar(10);

练习:

  1. # 1. 创建数据库mydb4 字符集utf8并使用
  2. CREATE DATABASE mydb4 CHARSET=utf8;
  3. USE mydb4;
  4. # 2. 创建teacher表 有名字(name)字段
  5. CREATE TABLE teacher(
  6.     name VARCHAR(32)
  7. );
  8. # 3. 添加表字段: 最后添加age 最前面添加id(int型) , age前面添加salary工资(int型)
  9. ALTER TABLE teacher ADD age INT(3);
  10. ALTER TABLE teacher ADD id INT FIRST;
  11. ALTER TABLE teacher ADD salary INT AFTER name;
  12.  
  13. # 4. 删除age字段
  14. ALTER TABLE teacher DROP age;
  15. # 5. 修改表名为t
  16. RENAME TABLE teacher TO t;
  17. # 6. 删除表t
  18. DROP TABLE t;
  19. # 7. 删除数据库mydb4
  20. DROP DATABASE mydb4;

小结:

DDL语言,数据定义语言,操作数据库对象

CREATE (创建表),ALTER(修改表),DROP(删除表)

数据相关操作
DML语言:数据操作语言,是对表中数据进行操作的语言,包含增,删,改操作

在下面的表中进行

  1. create table person(
  2.    name varchar(32),
  3.    age int(3)
  4. );

插入数据(INSERT INTO)

  1. INSERT INTO 表名[(字段1,字段2...)]VALUES(字段1的值,字段2的值,...);
  2. insert into person(name,age)values('詹三',22);
  3. insert into person(age,name)values(22,'旺旺');

为指定列插入的都是列的默认值,当创建没有为列声明特点的默认值时,列默认值为null

insert into person(name) values('李四');

字段名可以忽略不写,此时为全列插入,即:values需要指定每一列的值,且顺序,个数,类型必须与表中的数据对应;

insert into person values('传奇',22);

查看person表中所有数据

select *from person;

修改表操作:UPDATE语句

UPDATE 表名 SET 字段名1=新值1[,字段名2=新值2...][WHERE 过滤条件]

通常修改语句要添加where子句,用于添加过滤条件来定位要修改的记录,不添加where子句则是全表所有记录.

eg:下面的sql语句将会把表中的年龄全部改为55;

update from person  set age=55;

将李四的年龄改为23岁

update person set age=23 where name='李四';

WHERE中常用的条件=,>,>=,<,<=,<>(不等于,!=不是多有的数据库都支持)

将年龄大于40岁的人年龄改为20

update person set age=20 where age>40;

修改字段时,可以将计算表达式的结果进行修改
将每个人的年龄增加1岁

update person set age=age+1;

将年龄为20岁的人改名字为二十

update person set name='二十' where age=20;

删除数据:DELETE语句

删除名字为二十的记录

delete from person where name='二十';

删除年龄大于二十五的人

delete from person where age>25;

清空表操作
注意:这个和删除表不一样,因为删除表是表都没有了,这个表还在,就是没有数据

delete from person;

练习:

  1. 1. 创建数据库day1db 字符集utf8并使用
  2. create database day1db charset=utf8;
  3. use day1db;
  4. 2. 创建t_hero表, 有name字段 字符集utf8
  5. create table t_hero(name varchar(20))charset=utf8;
  6. 3. 修改表名为hero
  7. rename table t_hero to hero;
  8. 4. 最后面添加价格字段money, 最前面添加id字段, name后面添加age字段
  9. alter table hero add money int;
  10. alter table hero add id int first;
  11. alter table hero add age int after name;
  12. 5. 表中添加以下数据: 1,李白,50,6888 2,赵云,30,13888 3,刘备,25,6888
  13. insert into hero values(1,'李白',50,6888);
  14. insert into hero values(2,'赵云',30,13888);
  15. insert into hero values(3,'刘备',25,6888);
  16. 6. 查询价格为6888的英雄名
  17. select name from hero where money=6888;
  18. 7. 修改刘备年龄为52岁
  19. update hero set age=52 where name='刘备';
  20. 8. 修改年龄小于等于50岁的价格为5000
  21. update hero set money=5000 where age<=50;
  22. 9. 删除价格为5000的信息
  23. delete from hero where money=5000;
  24. 10. 删除表, 删除数据库
  25. drop table hero;
  26. drop database day1db;

数据类型

数字类型:

  • 整数:INT(m)和BIGINT(m),m表示的是长度,例如:m=5 存数字18,实际存储00018
  • 浮点型:DOUBLE(m,n).m表示数字长度(整体数字的长度,包含小数),n表示小数位DOUBLE(5,3) 99.999
  • #insert into xxx values(12.9984)实际插入数据时当超出精度可保存范围时,会四舍五入

字符类型:

  • CHAR(n):定长字符串,每条记录实际占用的字节空间是定长的,不足的部分补充空字符来满足长度要求. 优点:查询速度快,缺点:浪费磁盘空间
  • VARCHAR(n):(变长字符串).最多存n指定的字节对应字符,实际保存是用多少占多少(推荐使用)优点:节省磁盘空间 缺点:查询速度慢(有一个知识点,varchar中有一个或两个额外的字节来记录我们存储了几个字节,比如有一个name字段用varchar存储,有一个名字'liuy',这里有一个额外的字节1(4),记录我们存储了4个字节,为什么是一到两个,因为我们存储的字节过大,那么就可能要两个字节来存储,)
  • TEXT(n):可变长字符串,最大65535

日期时间类型:

  • DATA:保存年月日
  • TIME:保存时分秒
  • DATETIME:保存年月日时分秒
  • TIMESTAMP:时间戳,记录UTC时间,从1970-01-01 00:00:00到表示的时间之间经过的毫秒

创建下面这张表对上述的时间进行操作

  1. create table userinfo(
  2.   id int,
  3.   name varchar(32),
  4.   birth DATETIME,
  5.   salary DOUBLE(7,2)
  6. );

操作如下:

  1. INSERT INTO userinfo VALUES(1,'张三','1992-08-02 11:22:05',5000.59);
  2. # DATETIME类型在不指定时分秒时，默认为00:00:00
  3. INSERT INTO userinfo VALUES(2,'李四','1999-05-16',6500.23);
  4. # DATETIME中年月日不可以省略
  5. # INSERT INTO userinfo VALUES(3,'王五','06:15:55',1900.788);
  6.  
  7. #插入DOUBLE类型数字时，如果小数部分超过范围会进行四舍五入
  8. INSERT INTO userinfo VALUES(3,'王五','1987-04-13 06:15:55',1900.788);
  9. INSERT INTO userinfo VALUES(4,'赵六','1985-03-23',2000);
  10. INSERT INTO userinfo VALUES(5,'钱七','1986-01-22',40000);
  11. #整数超过部分会报错
  12. # INSERT INTO userinfo VALUES(6,'老八','1976-02-18',140000);

时间戳这个类型:timestamp时间戳类型当数据发生更改时会自动更新时间为当前时间,

关闭自动更新时间;和打开自动更新时间.

  1. 关闭自动更新:
  2. 语法:
  3. alter table 表名 change 原使用时间戳的字段名  现字段名 timestamp not null default current_timestamp;
  4.  
  5. 例如下面这个,product表的created字段使用的是timestamp类型,我们改为不自动更新
  6.  
  7. eg:alter table product change created created timestamp not null default current_timestamp;
  8.  
  9. 还可以打开自动更新时间
  10. 语法:alter table 表名 change 原使用时间戳的字段名  现字段名 timestamp not null default current_timestamp on update current_timestamp;
  11.  
  12. alter table product change created created timestamp not null default current_timestamp on
  13. update current_timestamp;

约束

约束是为表中某个字段添加特定的限制条件,只有符合条件的记录才可以保存

主键约束:该字段非空且唯一,用该字段的值唯一表示一条记录
非空约束:该字段的值不允许为空
外键约束:实际开发中几乎不适用外键约束

  1. create table student(
  2.   id INT AUTO_INCREMENT PRIMARY KEY,
  3.   name VARCHAR(30) NULL,
  4.   age INT(3),
  5.   gender CHAR(1)
  6.  );

由于那么为NOT NULL,所以名字必须指定值,不指定会报错(下面报错)

insert into student values(null,null,55,'m');

id本身的主键约束要求非空且唯一,但是由于有自增,所以当不指定时使用系统生成的自增值(有自增时就不要主动赋值)

insert into student values(null,'名字',22,'m');

主键有自增是,可以忽略主键字段的插入

insert into student(name,age,gender) values('敏敏',18,'f');

有非空约束的字段插入数据时不可以忽略(因为忽略的字段默认值插入NULL)

insert into student(age,gender) values(18,'f');

允许为NULL的字段可以通过显示的指定NULL作为插入值

  1. insert into student(name,age,gender) values('各个',22,NULL);
  2. 也可以不指定字段来为该字段隐式的插入NULL值
  3. insert into student(name,age) values('哈哈',33);

在下面这张表中,我们可以进行试验

  1.  
  2. CREATE TABLE teacher(
  3.      id INT PRIMARY KEY,
  4.      name VARCHAR(30) NOT NULL,
  5.      age INT(3),
  6.      gender CHAR(1)
  7. );
  8. INSERT INTO teacher VALUES(1,'张三',22,'F');
  9. # 报错，主键字段不允许插入重复的值 Duplicate entry '1' for key 'PRIMARY'
  10. # INSERT INTO teacher VALUES(1,'李四',23,'M');
  11.  
  12. # 报错，主键字段不能为NULL值 Column 'id' cannot be null
  13. # INSERT INTO teacher VALUES(NULL,'李四',23,'M');
  14.  
  15. # NULL作为条件是，要使用IS NULL 或 IS NOT NULL
  16.  
  17. # 查看性别为null的学生信息
  18. #查询不到任何数据
  19. # SELECT * FROM student WHERE gender=NULL
  20.  
  21. SELECT * FROM student WHERE gender IS NULL;
  22. SELECT * FROM student WHERE gender IS NOT NULL

数据查询语句
DQL语句:基本语法

使用DQL语句前我们先导入一张表

  1. /*
  2. SQLyog Ultimate v12.09 (64 bit)
  3. MySQL - 5.5.68-MariaDB : Database - newdb3
  4. *********************************************************************
  5. */
  6.  
  7.  
  8. /*!40101 SET NAMES utf8 */;
  9.  
  10. /*!40101 SET SQL_MODE=''*/;
  11.  
  12. /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
  13. /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
  14. /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
  15. /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
  16. CREATE DATABASE /*!32312 IF NOT EXISTS*/`empdb` /*!40100 DEFAULT CHARACTER SET utf8 */;
  17.  
  18. USE `empdb`;
  19.  
  20. /*Table structure for table `dept` */
  21.  
  22. DROP TABLE IF EXISTS `dept`;
  23.  
  24. CREATE TABLE `dept` (
  25.   `id` int(4) NOT NULL AUTO_INCREMENT,
  26.   `name` varchar(14) NOT NULL,
  27.   `loc` varchar(13) DEFAULT NULL,
  28.   PRIMARY KEY (`id`),
  29.   UNIQUE KEY `DNAME` (`name`)
  30. ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
  31.  
  32. /*Data for the table `dept` */
  33.  
  34. insert  into `dept`(`id`,`name`,`loc`) values (1,'神仙','天庭'),(2,'妖怪','盘丝洞'),(3,'普通人','北京'),(4,'赛亚人','外星球');
  35.  
  36. /*Table structure for table `emp` */
  37.  
  38. DROP TABLE IF EXISTS `emp`;
  39.  
  40. CREATE TABLE `emp` (
  41.   `id` int(4) NOT NULL AUTO_INCREMENT,
  42.   `name` varchar(10) NOT NULL,
  43.   `job` varchar(9) DEFAULT NULL,
  44.   `manager` int(4) DEFAULT NULL,
  45.   `hiredate` date DEFAULT NULL,
  46.   `sal` double(7,2) DEFAULT NULL,
  47.   `comm` double(7,2) DEFAULT NULL,
  48.   `dept_id` int(4) DEFAULT NULL,
  49.   PRIMARY KEY (`id`)
  50. ) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;
  51.  
  52. /*Data for the table `emp` */
  53.  
  54. insert  into `emp`(`id`,`name`,`job`,`manager`,`hiredate`,`sal`,`comm`,`dept_id`) values (1,'孙悟空','销售',4,'1980-12-17',800.00,NULL,1),(2,'猪八戒','销售',4,'1981-02-20',1600.00,300.00,1),(3,'沙僧','销售',4,'1981-02-22',1250.00,500.00,1),(4,'唐僧','销售经理',8,'1981-04-02',2975.00,NULL,1),(5,'刘备','项目经理',NULL,'1981-09-28',1250.00,1400.00,3),(6,'关羽','程序员',5,'1981-05-01',2850.00,NULL,3),(7,'张飞','程序员',5,'1981-06-09',2450.00,NULL,3),(8,'观音','CEO',NULL,'1981-11-17',5000.00,NULL,1),(9,'白骨精','人事',8,'1981-09-08',1500.00,0.00,2),(10,'蜘蛛精','人事',8,'1981-12-03',950.00,NULL,2),(11,'黑熊怪','市场',8,'1981-12-03',3000.00,NULL,2);
  55.  
  56. /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
  57. /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
  58. /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
  59. /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
  60.  
  61.  
  62. set names gbk;//这一句谨慎导入,可能数据库乱码,如果导入了,可以进行允许下面这句进行恢复
  63. set names utf8;

我们先改变使用的数据库

use empdb;


数据查询语句:SELECT ...FROM

SELECT 字段名1[,字段名2,...或*] FROM 表名

查看emp表中每一条记录的所有字段值

select*from emp;

查看每个员工的名字,职位,工资

select name,job,sal from emp where sal>1000;

查看职位除'人事'之外的所有员工名字,工资,职位

select name ,sal,job from emp where job <>'人事';

查看部门编号为2的员工的名字,工资,职位,部门编号?

and优先级高于or,因此可以通过()来提高or的优先级

select name,sal,job from emp where (job='人事' or job='销售')and sal>1000;

IN(列表) 值在列表中(等于列表中的其中之一)

查看职位是人事和销售的所有员工名字,工资,职位,部门编号?

select name,sal,job,dept_id from emp where job in('人事','销售');

插看职位是人事和销售以外的多有员工的名字,工资,职位,部门编号

select name ,sal,job,dept_id from emp where job not in ('人事','销售');

查看工资在2000到3000之间的员工的名字,工资,职位

BETWEEN min AND max 在两者之间 ,min与max的顺序不能改变 即:>=min AND <=max

  1. select name ,sal,job from emp where sal>=2000 and sal<=3000;
  2. 用between...and
  3. select name,sal,job from emp where sal between 2000 and 3000;

DISTINCT用于去除指定这些列的值的组合有重复的行

查看公司有多少中职位

select distinct job from emp;

多列去重,就是去除指定这些列的值的组合有重复的行

select distinct job,dept_id from emp;

练习:

  1. # 1. 查询2号部门工资高于1000的员工信息
  2. SELECT *
  3. FROM emp
  4. WHERE dept_id=2 AND sal>=1000;
  5. # 2. 查询3号部门或工资等于5000的员工信息
  6. SELECT *
  7. FROM emp
  8. WHERE dept_id=3 OR sal=5000;
  9. # 3. 查询工资在1000到2000之间的员工姓名和工资
  10. SELECT name,sal
  11. FROM emp
  12. WHERE sal BETWEEN 1000 AND 2000;
  13. # 4. 查询工资不等于3000和5000的员工信息
  14. SELECT *
  15. FROM emp
  16. WHERE sal NOT IN(3000,5000);
  17. # 5. 查询1号部门有哪几种不同的工作
  18. SELECT DISTINCT job
  19. FROM emp
  20. WHERE dept_id=1

模糊查询:LIKE
LIKE中两个通配符:_和%
_(下划线):表示任意一个字符
%(百分号):(0-多个字符)

查看姓孙员工信息?(名字的第一个字符是孙)

select *from emp where name like '孙%';

查看名字中含有'悟'的员工?

select *from emp where name like '%悟%';

查看第二个字是'骨'的员工

select *from emp where name like '_骨%';

总结:

%X%:字符串中包含X
%X:字符串以X结尾
X%:字符串以X开头
_X%:字符串第二个字符是X
%X_:倒数第二个字符是X
X%Y:字符串以X开头Y结尾
X_Y:字符串只有三个字，第一个是X，第三个是Y

练习:

  1. # 1. 查询名字姓猪的员工姓名
  2. SELECT name
  3. FROM emp
  4. WHERE name LIKE '猪%';
  5. # 2. 查询名字中包含僧的员工信息
  6. SELECT *
  7. FROM emp
  8. WHERE name LIKE '%僧%'
  9. # 3. 查询名字以精结尾的员工姓名
  10. SELECT name
  11. FROM emp
  12. WHERE name LIKE '%精';
  13. # 4. 查询工作中包含销售并且工资大于1500的员工信息
  14. SELECT *
  15. FROM emp
  16. WHERE job LIKE '%销售%'
  17. AND sal>1500;
  18. # 5. 查询工作中第二个字是售的员工姓名和工作
  19. SELECT name,job
  20. FROM emp
  21. WHERE job LIKE '_售%';
  22. # 6. 查询1号和2号部门中工作以市开头的员工信息
  23. SELECT *
  24. FROM emp
  25. WHERE dept_id IN (1,2)
  26. AND job LIKE '市%'

ORDER BY子句,根据指定的字符段排序查询结果集.该子句只能放在查询语句的最后一个子句上

查看公司所有员工的工资,且工资从低到高排序

select name,sal from emp order by sal;

按照时间排序时,越早的时间越小,越晚的时间越大

select name,sal,hiredate from emp order by hiredate;

按照升序排序,可以使用ASC关键字,但是通常不需要写,因为默认就是升序排序(从下到大)

SELECT name,sal,hiredate FROM emp ORDER BY hiredate ASC;

按照工资从大到小排序(降序),降序使用关键字DESC

SELECT *FROM emp ORDER BY sal DESC;

ORDER BY子句可以按照多个字段排序,排序优先级时将结果集按照第一个字段的值排序,保证第一个字段排序的前提下将该字段相同的记录中按照第二个字段排序,以此类推.

  1. select name ,dept_id ,sal from emp order by dept_id ,sal desc;
  2. 这里注意,第一个字段dept_id,还是默认的升序,第二个猜数降序
  3.  
  4. select name ,dept_id ,sal from emp order by dept_id desc,sal desc;
  5. 这个两个都降序

练习:

  1. # 1. 查询有领导的员工信息,按照入职日期(hiredate) 升序排序
  2. SELECT *
  3. FROM emp
  4. WHERE manager IS NOT NULL
  5. ORDER BY hiredate;
  6. 2. 查询1号部门中名字中包含八的员工信息
  7. SELECT *
  8. FROM emp
  9. WHERE dept_id=1 AND name LIKE '%八%';
  10. # 3. 查询2号和3号部门中工资低于1500的员工信息
  11. SELECT *
  12. FROM emp
  13. WHERE dept_id IN(2,3) AND sal<1500;
  14.  
  15. SELECT *
  16. FROM emp
  17. WHERE (dept_id=2 OR dept_id=3) AND sal<1500;
  18. # 4. 查询人事和程序员中工资高于2500的员工姓名,工资和工作
  19. SELECT name,sal,job
  20. FROM emp
  21. WHERE job IN('人事','程序员') AND sal>2500;
  22. # 5. 查询不是CEO的员工中工资高于2000的员工姓名,工资和工作,并且按照工资降序排序
  23. SELECT name,sal,job
  24. FROM emp
  25. WHERE job<>'CEO' AND sal>2000
  26. ORDER BY sal DESC

分页查询:

将满足条件的的数据分段分批的查询出来,这可以减少不必要的系统开销.
分页查询在SQL92里面没有 定义,这意味着不同的数据库,分页查询的方式完全不一样.
在ORDER BY 中使用LIMIT来完成(limit 参数1,参数2)
LIMIT 参数1,跳过的记录数,请求的记录数(每页显示的记录数)
LIMIT 参数2,(页数-1)*每页显示的记录数,每页显示的记录数

按照工资的降序后,没有显示3条,查看第二页

select name,sal from emp order by sal limit 3,3;

按照工资的降序后,每页显示3条,查看第三页

select name,sal from emp order by sal limit 6,3;

按照工资降序后,没有显示四条,显示第三页

select name,sal from emp order by sal limit 8,4;

查询字段可以使用表达式

查看每个员工的年薪多少?

select name,sal,sal*12 from emp;

查询时候也可以使用函数的结果作为字段(concat)

孙悟空的职位是销售  name+"的职位"+job

select concat(name,'的职位',job) from emp;

查看每个员工的工资奖金,工资+奖金

数字与NULL运算的结果是NULL,即使是加减法,不是乘除法
 

select name,sal,comm,sal+comm from emp;

NVL函数,用来替换NULL值
NVL(arg1,arg2) 当arg1不为NULL时则函数返回arg1,如果arg1为NULL则返回arg2的值

select name,sal,NVL(comm,0) from emp;

别名

我们可以为字段定义别名,也可以给表定义别名.
为字段定义别名一般多用于:
1.隐藏实际表字段名
2,为计算表达式或函数的结果只作为字段时定义可读性更好的字段名

  1. select name ename,sal salary from emp;
  2. select name ,sal*12 salary from emp;

别名支持如下语法

  1. # 字段名 别名
  2. SELECT name,sal*12 salary FROM emp;
  3. # 字段名 as 别名
  4. SELECT name as ename,sal*12 salary FROM emp;
  5. # 字段名 as '别名'
  6. SELECT name as 'ename',sal*12 salary FROM emp;
  7. # 字段名 as "别名"
  8. SELECT name as "ename",sal*12 salary FROM emp;

练习:

  1. # 1. 查询员工表中3号部门工资高于1500的员工信息
  2. SELECT * FROM emp WHERE dept_id=3 AND sal>1500
  3. # 2. 查询2号部门员工或者没有领导的员工信息
  4. SELECT * FROM emp WHERE dept_id=2 OR manager IS NULL
  5. # 3. 查询有领导的员工姓名,工资按照工资降序排序
  6. SELECT name,sal
  7. FROM emp
  8. WHERE manager IS NOT NULL
  9. ORDER BY sal DESC
  10. # 4. 查询2号和3号部门的员工姓名和入职日期hiredate按照入职日期降序排序
  11. SELECT name,hiredate
  12. FROM emp
  13. WHERE dept_id IN (2,3)
  14. ORDER BY hiredate DESC
  15. # 5. 查询名字中包含僧和包含精的员工姓名
  16. SELECT name
  17. FROM emp
  18. WHERE name LIKE '%僧%' OR name LIKE '%精%'
  19. # 6. 查询工资高于2000的工作有哪几种?
  20. SELECT DISTINCT job
  21. FROM emp
  22. WHERE sal>2000
  23. # 7. 查询工资升序第4页的2条数据
  24. SELECT *
  25. FROM emp
  26. ORDER BY sal
  27. LIMIT 6,2

聚合函数(也称为多函数),聚合函数是用来多条记录统计为一条记录的

MIN():求最小值
MAX(): 求最大值
COUTN():统计记录数
AVG():求平均值
SUM():求和

产看公司最低的员工工资

select min(sal) from emp;

查看聚合函数能使用的,并起别名

SELECT MIN(sal) 最低工资 ,MAX(sal) 最高工资,AVG(sal) 平均工资,SUM(sal) 工资总和 FROM emp;

聚合函数忽略NULL值,在AVG中比较明显可以看出这一点,以下仅对4个有奖金的人取了个平均值,而不是全部人,因为那些人的奖金为null值 

SELECT MIN(comm),MAX(comm),AVG(comm),SUM(comm) FROM emp;

聚合函数去重

查询emp表中一共有所少种工作

select count(distinct job) from emp;

练习:

  1. # 1. 查询销售的平均工资
  2. SELECT AVG(sal)
  3. FROM emp
  4. WHERE job='销售'
  5. # 2. 查询程序员的最高工资
  6. SELECT MAX(sal)
  7. FROM emp
  8. WHERE job='程序员'
  9. 3. 查询名字包含精的员工数量
  10. SELECT COUNT(*)
  11. FROM emp
  12. WHERE name LIKE '%精%'
  13. 4. 查询和销售相关的工作一个月工资总和
  14. SELECT SUM(sal)
  15. FROM emp
  16. WHERE job LIKE '%销售%'
  17.  
  18. 5. 查询2号部门的最高工资和最低工资起别名
  19. SELECT MIN(sal) 最低工资,MAX(sal) 最高工资
  20. FROM emp
  21. WHERE dept_id=2

GROUP BY子句,分组
GROUP BY 也是为统计服务的,所以是搭配在聚合函数上使用的

查看每个部门的平均工资(select子句中不在聚合函数中的其他字段必须出现在group by子句中,比如这里的dept_id)

select avg(sal),dept_id from emp grop by dept_id;

每种职位的最高工资是多少

select max(sal) ,job from emp group by job;

练习:

  1. # 1.查询每个部门的最高工资
  2. SELECT MAX(sal),dept_id FROM emp GROUP BY dept_id
  3. # 2.查询每个部门工资高于2000的人数
  4. SELECT COUNT(*)
  5. FROM emp
  6. WHERE sal>2000
  7. GROUP BY dept_id;
  8. # 3.查询每种工作的最低工资
  9. SELECT MIN(sal),job FROM emp GROUP BY job;
  10. # 4.查询1号部门和2号部门的人数
  11. SELECT COUNT(*),dept_id
  12. FROM emp
  13. WHERE dept_id IN (1,2)
  14. GROUP BY dept_id
  15. # 5.查询平均工资最高的部门id和平均工资
  16. SELECT AVG(sal),dept_id
  17. FROM emp
  18. GROUP BY dept_id
  19. ORDER BY AVG(sal) DESC
  20. LIMIT 0,1;

可以为函数或表达式字段取别名,然后利用别名排序

查看平均工资最高的部门(这个sql语句可以多看看)

  1. select avg(sal) avg,dept_id
  2. from emp 
  3. group by dept_id
  4. order by avg desc
  5. limit 0,1;

查看部门平均工资高于2000的那些部门的平均工资具体是多少

聚合函数不能写在where字句中,下面的sql是错的.

SELECT AVG(sal),dept_id FROM emp WHERE AVG(sal)>2000 GROUP BY dept_id

正确的写法使用HAVING,写在group by后面.

select avg(sal) avg,dept_id from emp group by dept_id having min>1000;

where中不能写聚合函数原因

原因是过滤时机不对.
where子句是添加过滤条件,在查询表中每条记录时,用于筛选记录(查询表的过程中用于过滤的))

想利用聚合函数的结果进行过滤时,应当已经是将表中数据查询出来(此时是where过滤的时机),并对结果集进行统计后,得到的结果集在进行过滤
HAVING子句,HAVING子句是跟在GOURP BY子句之后,对分组统计的结果再进行过滤的.

查看部门最低工资大于1000的部门的平均工资

  1. select min(sal) min,avg(sal) avg,dept_id from emp
  2. group by dept_id
  3. having min>1000;

查询每个部门的工资的总和,只查询有领导的员工,并且要求工资总和大于5400

  1. select sum(sal) sum ,dept_id from emp
  2. where manager is not null
  3. groupt by dept_id
  4. having sum>5400;

子查询:这种嵌套在其他sql语句中的查询语句被称为子查询,子查询通常用于要基于一个查询结果在进行操作的地方.

查看比公司平均工资高的那些员工的名字和工资

  1. SELECT name,sal
  2. FROM emp
  3. WHERE sal>(SELECT AVG(sal) FROM emp)

查询工资高于2号部门平均工资的员工信息

select *from emp where sal >(select avg(sal) from emp where dept_id=2);

查询比沙僧工资低的员工信息:

select *from emp where sal<(select sal from emp where name='沙僧');

查询和孙悟空同职位的员工信息

select*from emp where job =(select job from emp where name='孙悟空');

查询和公司最低工资员工同属于一个部门的员工信息

  1. select *from emp where dept_id =(select 
  2. dept_id from emp where sal=(select min(sal) from emp));

查询比2号和3号部门工资都高的员工名字和工资

  1. select name,sal from emp where sal>(
  2. select max(sal) from emp where dept_id in(2,3));
  3.  
  4. 或者
  5. select name ,sal from emp where sal>ALL(
  6. select sal from emp where dept_id in(2,3))

查询比2号和3号部门工资任意一个员工工资高的员工信息

  1. ELECT *FROM emp
  2. WHERE sal>ANY(SELECT sal FROM emp WHERE dept_id IN (2,3))

子查询分类(按查询结果集分)

单行单列子查询:(结果集只有一个值)
多行单列子查询:(结果集有多个值)
多行多列子查询:(结果集是一个表)

单行子查询通常用于过滤条件中使用.
单行单列可以配合>,>=,=,<.<=使用
多行单列可以配合ANY,ALL,IN使用
例如:
>ALL(子查询):大于子查询结果集中最大的
<ALL(子查询):小于子查询结果集中最小的
>ANY(子查询):大于子查询结果集中最小的
<ANY(子查询):小于子查询结果集中最大的
IN(子查询):等于子查询结果集中的任意一个值

多行多列子查询(结果集是一个表),通常就当多一张表使用,可以跟在FROM子句中
或者跟着DDL语句中基于一个查询结果创建表.

将1号部门员工信息单独定义一张表为emp_dept1;

  1. CREATE TABLE emp_dept1
  2. AS
  3. SELECT * FROM emp WHERE dept_id=1;

如果创建表基于的子查询中某个字段式一个表达式或函数时,要给该字段取别名,那么创建出来表的字段会以别名作为字段名.

创建一张emp_dept_sal,该表记录了每个部门的薪资情况,包含最高工资,最低工资,平均工资,工资总和,部门编号.

  1. create table emp_dept_sal 
  2. as 
  3. select max(sal) max_sal ,min(sal) min_sal,avg(sal) avg_sal,sum(sal) sum_sal,dept_id
  4. from emp
  5. group by dept_id.
创建一张表emp_annual_salary，记录每个员工的名字，工资，年薪和部门，年薪字段用:a_salary,工资用salary

  1. CREATE TABLE emp_annual_salary
  2. AS
  3. SELECT name,sal salary,sal*12 a_salary,dept_id
  4. FROM emp

名字里含精的员工年薪是多少

  1. SELECT name,a_salary
  2. FROM emp_annual_salary
  3. WHERE name LIKE '%精%'

关联查询:查询结果集中的数据来自多张表,而表与表中数据之间的对应关系就是关联关系.

两张表就可以产生关联关系了,关联关系分为三类:
1,一对一:A表中的1条记录只唯一对应B表中的1条记录
2,一对多:A表中的1条记录可以对应B表中的多条记录
3,多对多:A表中与B表双向都是一对多时,就是多对多关系

关联查询就是基于多张表联合查询数据而形成的结果集的过程,在关联查询中一个至关重要的点就是关联条件.
原则:N张表关联查询至少要有N-1个连接条件
缺失连接条件会产生笛卡尔积,该查询结果集的记录数是关联表中所有记录数乘积的结果,它通常是一个无意义的结果集,要尽量避免.

关联查询语法

  1.  SELECT 字段
  2. FROM 表A，表B[，表C，表D...]
  3. WHERE 过滤条件
  4. AND 连接条件
  5. 注意:连接条件必须与过滤条件同时成立!!

笛卡尔积:产生了44条数据,将emp表中每条记录都与dept表每条记录产生一条记录

select *from dept ,emp;

当表中出现了同名字段时,未来查询区分字段来自于那张表,我们可以在字段名前面用"表名."来识别.

  1. SELECT emp.name ,emp.sal,emp.dept_id,dept.name ,dept.loc
  2. FROM emp,dept;

当表中的字段名相同时,我们可以通过取别名,用"别名.字段名"也可以标明查询的是那张表上面的字段

  1. SELECT e.name,e.sal,e.dept_id,d.name,d.loc
  2. FROM emp e,dept d

实际关联查询要添加连接条件
连接条件最常见的就是等值连接

查看每个员工的名字,工资,部门编号以及所在的部门名称和所在地区

  1. select e.name ,e.sal ,e.dept_id,d.name,d.loc
  2. from emp e,dept d
  3. where e.dept_id=d.id;

注意:emp表上的dept_id保存的值是dept表中主键字段的值,因此emp表中dept_id与dept表id值一样的记录才会被查询出来作为一条记录显示在结果集中.
当一张表的某个字段保存另一张表的主键字段值时,这个字段就被称为"外键"
关联关系中经常用A.主键=B.外键作为连接条件

查看在天庭工作的人都有谁

  1. select e.name,e.sal ,e.job,d.name,d.loc
  2. from emp e,dept d
  3. where e.dept id=d.id
  4. and d.loc='天庭';

名字含有飞的人来自哪里

  1. SELECT e.name,d.loc
  2. FROM emp e,dept d
  3. WHERE e.dept_id=d.id
  4. AND e.name LIKE '%飞%'

天庭的最高工资

  1. SELECT MAX(e.sal),d.loc
  2. FROM emp e,dept d
  3. WHERE e.dept_id=d.id
  4. AND d.loc='天庭'
  5. GROUP BY d.loc

天庭谁的工资最高

  1. SELECT e.name FROM emp e ,dept d
  2. WHERE e.dept_id =d.id
  3. AND sal=(SELECT MAX(e.sal)
  4.            FROM emp e ,dept d
  5.            WHERE e.dept_id =d.id
  6.              AND d.loc='天庭');

查看每个地区的平均工资

  1. SELECT AVG(e.sal),d.loc
  2. FROM emp e,dept d
  3. WHERE e.dept_id=d.id
  4. GROUP BY d.loc

练习:

  1. SELECT *FROM emp;
  2. # 1. 查询工资大于等于3000的员工姓名和工资
  3. SELECT name,sal FROM emp WHERE sal>3000;
  4. # 2. 查询1号部门的员工姓名和工作
  5. SELECT name,job FROM emp WHERE dept_id=1;
  6. # 3. 查询不是程序员的员工姓名和工作(两种写法)
  7. SELECT name,job FROM emp WHERE job<>'程序员';
  8. # 4. 查询奖金等于300的员工姓名,工资和工作
  9. SELECT name,sal,job FROM emp WHERE comm IN(300);
  10. SELECT name,sal,job FROM emp WHERE comm=300;
  11. # 5. 查询1号部门工资大于2000的员工信息
  12. SELECT *FROM emp WHERE dept_id=1 AND sal>2000;
  13. # 6. 查询3号部门或工资等于5000的员工信息
  14. SELECT *FROM emp WHERE dept_id=3 OR sal=5000;
  15. # 7. 查询出CEO和项目经理的名字
  16. SELECT name FROM emp WHERE job='CEO' OR job='项目经理';
  17. SELECT name FROM emp WHERE  job IN ('CEO','项目经理');
  18. # 8. 查询工资为3000,1500和5000的员工信息
  19. SELECT *FROM  emp WHERE sal IN(3000,1500,5000);
  20. # 9. 查询工资不等于3000,1500和5000的员工信息
  21. SELECT *FROM emp WHERE sal NOT IN(3000,1500,5000);
  22. # 10. 查询工资在1000到2000之间的员工信息
  23. SELECT *FROM emp WHERE sal BETWEEN 1000 AND 2000;
  24. # 11. 查询工资在1000到2000以外的员工信息
  25. SELECT *FROM emp WHERE sal NOT BETWEEN 1000 AND 2000;
  26. # 12. 查询有领导的员工姓名和领导id
  27. SELECT name,id FROM emp WHERE manager IS NOT NULL;
  28. # 13. 查询没有领导的员工姓名和领导id
  29. SELECT name,id FROM emp WHERE manager IS NULL;
  30. # 14. 查询员工表中出现了哪几种不同的工作
  31. SELECT DISTINCT job FROM emp;
  32. # 15. 查询员工表中出现了那几个部门的id
  33. SELECT DISTINCT dept_id FROM emp;
  34. # 16. 查询姓孙的员工姓名
  35. SELECT name FROM emp WHERE name LIKE '孙%';
  36. # 17. 查询名字最后一个字是精的员工信息
  37. SELECT *FROM emp WHERE name LIKE '%精';
  38. # 18. 查询工作中包含销售的员工信息
  39. SELECT *FROM emp WHERE job LIKE '%销售%';
  40. # 19. 查询工作中第二个字是售的员工信息
  41. SELECT *FROM emp WHERE job LIKE '_售%';
  42. # 20. 查询名字中包含僧的员工并且工资高于2000的员工信息
  43. SELECT *FROM  emp WHERE name LIKE '%僧%' AND sal>2000;
  44. # 21. 查询1号和2号部门中工作以市开头的员工信息
  45. SELECT *FROM emp WHERE dept_id IN (1,2) AND job LIKE '市%';
  46. SELECT *FROM emp WHERE dept_id<>3 AND job LIKE '市%';
  47. # 22. 查询所有员工的姓名和工资 按照工资升序排序
  48. SELECT name ,sal FROM emp ORDER BY sal;
  49. # 23. 查询所有员工的姓名和工资 按照工资降序排序
  50. SELECT name,sal FROM emp ORDER BY sal DESC ;
  51. # 24. 查询所有员工姓名 工资和部门id 按照部门id降序排序,如果部门id一致则按照工资升序排序
  52. SELECT name,sal,dept_id FROM emp ORDER BY dept_id DESC  ,sal;
  53. # 25. 查询员工表中3号部门工资高于1500的员工信息
  54. SELECT *FROM emp WHERE  dept_id=3 AND sal>1500;
  55. # 26. 查询2号部门员工或者没有领导的员工信息
  56. SELECT *FROM emp WHERE  dept_id=2 OR manager IS NULL;
  57. # 27. 查询有领导的员工姓名,工资按照工资降序排序
  58. SELECT name ,sal,manager FROM emp
  59. WHERE manager IS  NOT NULL
  60. ORDER BY sal DESC ;
  61. # 28. 查询2号和3号部门的员工姓名和入职日期hiredate 按照入职日期降序排序
  62. SELECT name ,hiredate FROM emp WHERE dept_id IN(2,3) ORDER BY hiredate DESC ;
  63. # 29. 查询名字中包含僧和包含精的员工姓名
  64. SELECT name FROM emp WHERE name LIKE '%精%' OR name LIKE '%僧%';
  65. # 30. 查询工资高于2000的工作有哪几种?
  66. SELECT DISTINCT job FROM emp WHERE sal>2000;
  67. # 31. 查询工资最高的前三个员工
  68. SELECT *FROM  emp ORDER BY sal DESC LIMIT 0,3;
  69. # 32. 查询员工表按照id排序, 第2页的5条数据
  70. SELECT *FROM emp ORDER BY id LIMIT 5,5;
  71. # 33. 查询员工表按照id排序, 第3页的4条数据
  72. SELECT *FROM emp ORDER BY id LIMIT 8,4;
  73. # 34. 查询3号部门工资最低的员工姓名和工资
  74. SELECT name,sal,dept_id FROM emp WHERE dept_id=3 AND sal=(
  75.     SELECT MIN(sal) FROM emp WHERE dept_id=3);
  76. # 35. 查询工作不是人事的员工中工资降序第二页的3条数据
  77. SELECT  *FROM emp WHERE job<>'人事' ORDER BY sal DESC LIMIT 3,3;
  78. # 36. 查询没有领导的员工和3号部门的员工,工资降序取前三条
  79. SELECT *FROM emp WHERE dept_id=3 OR manager IS NULL ORDER BY sal DESC  LIMIT  0,3;
  80. # 37. 查询2号部门的最高工资
  81. SELECT MAX(sal) FROM emp WHERE dept_id=2;
  82. # 40. 查询有领导的员工中工资在1000到2000之间的人数
  83. SELECT COUNT(*) FROM emp WHERE manager IS NULL AND sal BETWEEN 1000 AND 2000;
  84. # 41. 查询3号部门的工资总和
  85. SELECT SUM(sal) FROM  emp WHERE  dept_id=3;
  86. # 42. 查询程序员和销售的总人数
  87. SELECT COUNT(*) FROM emp WHERE job IN('程序员','销售');
  88. select *from emp;
  89. # 43. 查询1号部门有领导的员工的平均工资
  90. SELECT AVG(sal) FROM emp WHERE manager IS NOT NULL AND dept_id=1;
  91. # 44. 查询1号部门的最低工资和最高工资
  92. SELECT MIN(sal) ,MAX(sal) FROM emp WHERE  dept_id =1;
  93. # 45. 查询和销售相关的工作人数
  94. SELECT COUNT(*) FROM emp WHERE job LIKE '%销售%';
  95. # 46. 查询工资不是1500和3000的员工人数
  96. SELECT COUNT(*) FROM emp WHERE  sal NOT IN(1500,3000);
  97. # 47. 查询1号部门出现了哪几种工作
  98. SELECT DISTINCT job FROM emp WHERE dept_id=1;
  99. # 48. 查询名字包含精的员工数量
  100. SELECT COUNT(*) FROM emp WHERE name LIKE '%精%';
  101. # 49. 查询和销售相关的工作一个月工资总和
  102. SELECT SUM(sal) FROM emp WHERE  job LIKE '%销售%';
  103. # 50. 查询2号部门的最高工资和最低工资起别名
  104. SELECT MIN(sal) min ,MAX(sal) MAX FROM emp WHERE  dept_id=2;
  105. # 51.查询每个部门的平均工资
  106. SELECT AVG(sal) FROM emp GROUP BY dept_id;
  107. # 52. 查询每种工作的平均工资
  108. SELECT AVG(sal) FROM emp GROUP BY job;
  109. # 53. 查询每个部门的最高工资
  110. SELECT MAX(sal) FROM  emp GROUP BY dept_id;
  111. # 54. 查询每种工作的最低工资
  112. SELECT MIN(sal) FROM emp GROUP BY  job;
  113. # 55. 查询每个部门工资高于2000的人数
  114. SELECT COUNT(*) FROM emp WHERE sal>2000 GROUP BY dept_id;
  115. # 56. 查询每个部门有领导的员工人数
  116. SELECT COUNT(*) FROM emp WHERE manager IS NOT NULL GROUP BY  dept_id;
  117. # 57. 查询1号部门每种工作的最低工资
  118. SELECT MIN(sal) FROM emp WHERE dept_id=1 GROUP BY job;
  119. # 58. 查询平均工资最高的部门id和平均工资
  120. SELECT dept_id,AVG(sal) FROM emp GROUP BY dept_id  ORDER BY AVG(sal) DESC LIMIT 0,1;
  121. # 59. 查询每个部门的平均工资,要求平均工资大于2000
  122. SELECT AVG(sal) FROM emp GROUP BY dept_id HAVING AVG(sal)>2000;
  123. # 60. 查询每种工作的人数,只查询人数大于1的
  124. SELECT COUNT(*) FROM emp GROUP BY job HAVING COUNT(*)>1;
  125. # 61. 查询每个部门的工资总和,只查询有领导的员工, 并且要求工资总和大于5400.
  126. SELECT SUM(sal) FROM emp WHERE  manager IS NOT NULL GROUP BY dept_id HAVING SUM(sal)>5400;
  127. # 62. 查询每个部门的平均工资, 只查询工资在1000到3000之间的,并且过滤掉平均工资低于2000的
  128. SELECT AVG(sal) FROM emp WHERE sal BETWEEN 1000 AND 3000 GROUP BY dept_id HAVING AVG(sal)>2000;
  129.

select查询语句顺序

select  ...from ...join...on...where....group by ...having ...order by...limit 

内连接JOIN子句 (inner join)inner可以省略

内连接与关联查询效果一致,区别是单独书写关联关系(关联条件与过滤条件分开)

JOIN...ON...子句可以写多个

语法

  1. # SELECT 字段
  2. # FROM A表 a
  3. # JOIN B表 b
  4. # ON a.xx=b.xx(连接条件)
  5. # JOIN C表 c
  6. # ON c.xxx=b.xxx或c.xxx=a.xxx
  7. # JOIN .... ON ...

查看每个员工信息以及其对应的部分信息

  1. SELECT e.name,e.job,e.manager,e.sal,d.name,d.loc
  2. FROM emp e
  3. JOIN dept d
  4. ON e.dept_id = d.id;

在内连接中,过滤条件还是写在where子句中,

  1. # SELECT 字段
  2. # FROM 表1
  3. # JOIN 表2
  4. # ON 连接条件
  5. # WHERE 过滤条件

查看工资高于1300的员工信息和所在部门信息

  1. SELECT e.name,e.job,e.manager,e.sal,d.name,d.loc
  2. FROM emp e
  3. JOIN dept d
  4. ON e.dept_id = d.id
  5. WHERE e.sal>1300

在关联查询中不满住连接条件的记录会被排除在外的

查看每个员工和部门的信息

where写法

  1. SELECT e.name,e.job,e.manager,e.sal,d.name,d.loc
  2. FROM emp e,dept d
  3. WHERE e.dept_id=d.id

关联查询写法

  1. SELECT e.name,e.job,e.manager,e.sal,d.name,d.loc
  2. FROM emp e
  3. JOIN dept d
  4. ON e.dept_id = d.id

外连接(left outer join 与right outer join)outer可以省略

如果需要在结果集中列出不满足连接条件的记录时我们需要使用外连接
外连接有:
左外连接:以LEFT JOIN 左侧表作为主表,其中的记录都要展示,不满足连接条件是来自右侧表中记录的字段值全部为NULL,
右外连接:以RIGHT JOIN右侧表作为主表,其中的记录都要展示,不满足连接条件时来自左侧表中记录的字段全部为NULL.

  1. SELECT e.name,e.job,e.manager,e.sal,d.name,d.loc
  2. FROM emp e
  3. LEFT JOIN dept d
  4. ON e.dept_id = d.id
  5.  
  6. SELECT e.name,e.job,e.manager,e.sal,d.name,d.loc
  7. FROM emp e
  8. RIGHT JOIN dept d
  9. ON e.dept_id = d.id

全连接效果,结果集包含满足连接条件的和左连接,右连接的所有数据

  1. SELECT e.name,e.job,e.manager,e.sal,d.name,d.loc
  2. FROM emp e
  3. LEFT JOIN dept d
  4. ON e.dept_id = d.id
  5. UNION
  6. SELECT e.name,e.job,e.manager,e.sal,d.name,d.loc
  7. FROM emp e
  8. RIGHT JOIN dept d
  9. ON e.dept_id = d.id

查看比本部门平均工资高的员工信息(步骤
1,查看每个部门的平均工资
2,将子查询的结果集当作表进行关联查询)

  1. 1查看每个部门的平均工资
  2. (SELECT AVG(sal) avg_sal,dept_id FROM emp GROUP BY dept_id)
  3.  
  4. 2将子查询结果集当作表进行关联查询
  5. SELECT e.name,e.sal,e.dept_id,a.avg_sal
  6. FROM emp e,(SELECT AVG(sal) avg_sal,dept_id FROM emp GROUP BY dept_id) a
  7. WHERE e.dept_id=a.dept_id
  8. AND e.sal>a.avg_sal

查看比所在地区平均工资高的员工(步骤
1,查看每个员工的工资及其所在地区
2,每个额地区的平均工资(按照loc字段分组)
 3,关联三张表查询,员工表-部门表-(第2步子查询的结果集当成表))

  1. 1:查看每个员工的工资及其所在的地区
  2. SELECT e.sal,d.loc
  3. FROM emp e,dept d
  4. WHERE e.dept_id=d.id
  5.  
  6. 2:每个地区的平均工资(按照loc字段分组)
  7. SELECT AVG(e.sal) avg_sal,d.loc
  8. FROM emp e,dept d
  9. WHERE e.dept_id=d.id
  10. GROUP BY d.loc
  11.  
  12. 3:关联三张表查询:员工表-部门表-(第2步子查询的结果集当成的表)
  13. SELECT e.name,e.sal,a.avg_sal,d.loc
  14. FROM emp e,dept d,(SELECT AVG(e.sal) avg_sal,d.loc
  15.                    FROM emp e,dept d
  16.                    WHERE e.dept_id=d.id
  17.                    GROUP BY d.loc) a
  18. WHERE e.dept_id = d.id
  19. AND d.loc = a.loc
  20. AND e.sal>a.avg_sal

内连接写法

  1. SELECT e.name,e.sal,a.avg_sal,d.loc
  2. FROM emp e
  3. JOIN dept d
  4. ON e.dept_id = d.id
  5. JOIN (SELECT AVG(e.sal) avg_sal,d.loc
  6.                    FROM emp e,dept d
  7.                    WHERE e.dept_id=d.id
  8.                    GROUP BY d.loc) a
  9. ON d.loc = a.loc
  10. WHERE
  11. e.sal>a.avg_sal

查看每个地区的最高工资是谁

  1. SELECT e.name,e.sal,a.max_sal,d.loc
  2. FROM emp e
  3.          JOIN dept d
  4.               ON e.dept_id = d.id
  5.          JOIN (SELECT MAX(e.sal) max_sal,d.loc
  6.                FROM emp e,dept d
  7.                WHERE e.dept_id=d.id
  8.                GROUP BY d.loc) a
  9.               ON d.loc = a.loc
  10. WHERE e.sal=a.max_sal

自连接:该表中的一条记录可以对应表中的其他多条记录时,就是自连接的关联关系

自连接的设计就是为了保存同样的一组属性和数据之间有存在上下级关系时(树状结构数据)
公司的员工组织结构,对于公司而言大家都属于员工,但是员工之间又存在上下级关系.

自连接;当前表的一个字段保持了当前表中的主键字段值

查看每个员工和他的领导是谁

  1. SELECT e.name,m.name
  2. FROM emp e,emp m
  3. WHERE e.manager=m.id

内连接写法

  1. SELECT e.name,m.name
  2. FROM emp e
  3. JOIN emp m
  4. ON e.manager=m.id

刘备的手下都有谁

  1. SELECT e.name
  2. FROM emp e
  3. JOIN emp m
  4. ON e.manager=m.id
  5. WHERE m.name='刘备'

谁的工资高于孙悟空的领导工资(步骤
1,孙悟空的领导是谁
2,查看谁的工资高于唐僧的)

  1. 1:孙悟空的领导工资?
  2. SELECT m.name,m.sal
  3. FROM emp e
  4. JOIN emp m
  5. ON e.manager=m.id
  6. WHERE e.name='孙悟空'
  7.  
  8. 2:查看谁的工资高于唐僧的?
  9. SELECT name,sal
  10. FROM emp
  11. WHERE sal > (SELECT m.sal
  12.             FROM emp e
  13.             JOIN emp m
  14.             ON e.manager=m.id
  15.             WHERE e.name='孙悟空')

mysql数据库的基本知识几乎都在这里了,有什么要补充的欢迎评论区补充.

mysql类型与java类型对应的数据关系

MySQL数据类型java中的数据类型
tinyint  /  smallint  /  intInteger
bigintLong
char  /  varchar  /  text 系列String
datetimeLocalDateTime

本文内容由网友自发贡献，转载请注明出处：https://www.wpsshop.cn/w/酷酷是懒虫/article/detail/869468
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号