数据库02

1、count 使用

  • 1.1 查询employee表中记录数:

SELECT COUNT(*) FROM employee;

  • 1.2 查询员工表中有绩效的人数:

SELECT COUNT(performance) FROM employee;

  • 1.3 查询员工表中月薪大于2500的人数:

SELECT COUNT(*) FROM employee WHERE salary > 2500;

  • 1.4 统计月薪与绩效之和大于5000元的人数:

SELECT COUNT(*) FROM employee WHERE IFNULL(salary,0)+IFNULL(performance,0)>5000;

  • 1.5 查询有绩效的人数，和有管理费的人数:

SELECT COUNT(performance),COUNT(manage) FROM employee;

2、SUM 和MAX和MIN 的使用

  • 1、查询所有雇员月薪和

SELECT SUM(salary) FROM employee;

  • 2、查询所有雇员月薪和,以及所有雇员绩效和

SELECT SUM(salary),SUM(performance) FROM employee;

  • 3、 查询所有雇员月薪+绩效和

SELECT SUM(salary+IFNULL(performance,0)) FROM employee;

  • 4、统计所有员工平均工资

SELECT AVG(salary) FROM employee;

  • 5、 查询最高工资和最低工资

SELECT MAX(salary),MIN(salary) FROM employee;

3、分组查询

  • 1、 将查询结果按照1个或者多个字段进行分组,字段值相同的为1组

SELECT gender FROM employee GROUP BY gender;

  • 2、把相同的数据去掉

SELECT gender,GROUP_CONCAT(name) FROM employee GROUP BY gender;

4、分组查询-使用

  • 1、在一组里面所有男性名字和女性名字

SELECT gender,GROUP_CONCAT('name') FROM employee GROUP BY gender;

  • 2、 所有男性工资和女性工资

SELECT gender,GROUP_CONCAT(salary) FROM employee GROUP BY gender;

  • 3、对某一个部门进行分组,查看每个部门薪资信息都罗列出来

SELECT department,GROUP_CONCAT(salary) FROM employee GROUP BY department;

5、GROUP BY + 聚合函数

  • 1、查看部门平均薪资

SELECT department,GROUP_CONCAT(salary),SUM(salary) FROM employee GROUP BY department;

  • 2、查看每个部门的最高薪资

SELECT department,GROUP_CONCAT(salary),MAX(salary) FROM employee GROUP BY department;

  • 3、查看每个部门的最低薪资

SELECT department,GROUP_CONCAT(salary),MIN(salary) FROM employee GROUP BY department;

  • 4、查询每个部门的部门名称和每个部门的工资和

SELECT department,GROUP_CONCAT(salary),SUM(salary) FROM employee GROUP BY department;

  • 5、查询每个部门的部门名称和每个部门的人数

SELECT department,GROUP_CONCAT(name),COUNT(*) FROM employee GROUP BY department;

  • 6、查询每个部门的部门名称以及每个部门工资大于1500的人数

SELECT name,salary FROM employee WHERE salary>1500;

SELECT department,GROUP_CONCAT(salary),COUNT(*) FROM employee WHERE salary>1500 GROUP BY department;

6、GROUP BY + having

  • 1、把所有部门进行分组

SELECT department FROM employee GROUP BY department;

  • 2、每一组有哪些薪资,以及薪资总和

SELECT department,GROUP_CONCAT(salary),SUM(salary) FROM employee GROUP BY department;

  • 3、完成

SELECT department,GROUP_CONCAT(salary),SUM(salary) FROM employee GROUP BY department HAVING SUM(salary)>9000;

7、HAVING和WHERE的区别

HAVING是在分组后对数据进行过滤;
WHERE是在分组前对数据进行过滤;
HAVING后面可以使用分组函数(统计函数);
WHERE后面不可以使用分组函数;
WHERE是对分组前记录的条件,如果某行记录没有满足where子语句的条件,那么这行记录不参与分组;而HAVING是对分组后的数据约束;

  • 1、查询工资大于2000的,工资总和大于6000的部门名称以及工资和;
  1. 各部门工资
  2. 各部门工资总和
  3. 各部门工资总和大于6000
  4. 各部门工资总和大于6000降序排序
  • 2.、查询工资大于2000

SELECT salary FROM employee WHERE salary>2000;

  • 3、工资总和

SELECT department,GROUP_CONCAT(salary),SUM(salary) FROM employee WHERE salary > 2000 GROUP BY department HAVING SUM(salary)>9000;
ORDER BY SUM(salary) DESC;

8、书写顺序，执行顺序

书写顺序SELECT --> FROM --> WHERE --> GROUP BY --> HAVING --> ORDER BY --> LIMIT

执行顺序FROM => WHERE => GROUP BY => HAVING => SELECT => ORDER BY => LIMIT

分页

  • 1、 LIMIT

SELECT * FROM employee
LIMIT (curPage-1)*pageSize,pageSize;

数据完整性

  • 1、完整性分类：

实体完整性
域完整性
引用完整性

  • 2、什么是实体完整性

表中的一行(一条记录)代表一个实体(entity)

  • 3、实体完整性的作用

标识每一行数据不重复.行级约束

  • 4、约束类型

主键约束(primary key)
唯一约束(unique)
自动增长(auto_increment)

1、 主键约束

  • 1、特点:

每个表中要有一个主键
数据唯一,且不能为null

  • 2、添加方式:

CREATE TABLE 表名(字段名1 数据类型 primary key,字段名2 数据类型);

  • 3、举例:创建person表

CREATE TABLE person(ID BIGINT primary key,NAME VARCHAR(50))

CREATE TABLE 表名(字段1 数据类型,字段名2 数据类型,primary key(要设置主键的字段));

  • 4、举例创建

CREATE TABLE person(
ID BIGINT,
name VARCHAR(50),
age INT,
primary key(ID)
);

2、联合主键：两个字段数据同时相同时,才违反联合主键约束

  • 1、创建联合主键

CREATE TABLE 表名(字段1 数据类型,字段2 数据类型,primary key(主键1,主键2));

  • 2、举例

CREATE TABLE student(
ID BIGINT,
snum BIGINT,
name VARCHAR(50),
primary key(ID,snum))

  • 3、先创建表再添加主键

CREATE TABLE student(sid INT,name VARCHAR(50));

  • 4、如果表中你之前已经添加了数据,数据不符合主键规则就会报错

ALTER TABLE student ADD CONSTRAINT primary key(sid);

3、唯一约束

  • 1、特点

指定列的数据不能重复
可以为空值

  • 2、格式:

CREATE TABLE students(
id INT primary key,
name VARCHAR(50) UNIQUE
)

4、自动增长列

  • 1、特点

指定列的数据自动增长
即使数据删除,还是从删除的序号继续往下

  • 2、格式

CREATE TABLE 表名(
字段名1 数据类型 primary key AUTO_INCREMENT,
字段2 数据类型 UNIQUE);

  • 3、举例

CREATE TABLE 表名(
字段名1 数据类型 primary key AUTO_INCREMENT,
字段2 数据类型 UNIQUE);

5、域完整性

  • 1、使用

限制此单元格的数据正确,不对照此列的其他单元格比较
域代表当前的单元格

  • 2、数据类型

数值类型
日期类型
字符串类型

  • 3、非空约束(NOT NULL)

CREATE TABLE stu(
id　int primary key auto_increment,
name varchar(20) unique not null,
gender char(1) default '男'
)

  • 4默认值约束(DEFAULT)

CREATE TABLE 表名(
字段名1 数据类型 primary key AUTO_INCREMENT,
字段2 数据类型 UNIQUE NOT NULL,
字段3 数据类型 DEFAULT '默认值');

  • 5、举例:

CREATE TABLE students(
id INT primary key AUTO_INCREMENT,
name VARCHAR(20) UNIQUE NOT NULL,
gender CHAR(1) DEFAULT '男'
);

6、参照完整性

    1. 什么是参照完整性

(1)是指表与表之间的一种对应关系
(2)通常情况下可以通过设置两表之间的主键、外键关系,或者编写两表的触发器来实现
(3)有对应参照完整性的两张表格,再对他们进行数据插入,更新,删除的过程中,系统都会将被修改表格与另外一张对应表格进行对照,从而阻止一些不正确的数据的操作

  • 2、数据库的主键和外键类型一定要一致

  • 3、参照完整性添加
    （1）通过一张表里面的某个字段,关联另外一张表

（2）对已有表添加外键关系

ALTER TABLE score ADD CONSTRAINT sc_st_fk FOREIGN KEY(sid) REFERENCES student(id);

7、多表查询

  • 1. 一对一

一夫一妻(开发中使用比较少)

  • 2、一对多关系

一个人可以拥有多辆汽车,要求查询某个人拥有的所有车辆
创建Person表

  • 人

CREATE TABLE person(
p_id INT PRIMARY KEY AUTO_INCREMENT,
p_name VARCHAR(50),
p_age INT,
p_sex CHAR(1)
);

  • 车

CREATE TABLE car(
c_id INT PRIMARY KEY AUTO_INCREMENT,
c_name VARCHAR(50),
c_color VARCHAR(25),
p_id INT,
CONSTRAINT fk_Person FOREIGE KEY(c_id) REFERENCES person(p_id)
);

  • 3、多对多关系

例如:学生选课,一个学生可以选修多门课程,每门课程可以供多个学生选择
再例如: 老师和学生的多对多关系(一个老师有多个学生,一个学生有多个老师)

1、创建老师表
CREATE TABLE teacher(
t_id INT PRIMARY KEY AUTO_INCREMENT,
t_name VARCHAR(50),
t_age INT,
t_genger CHAR(1) DEFAULT '男'
);

2、创建学生表
CREATE TABLE students(
s_id INT PRIMARY KEY AUTO_INCREMENT,
s_name VARCHAR(50) NOT NULL,
s_age INT,
s_gender CHAR(1) DEFAULT '男'
);

3、中间表(创建学生与老师 关系表)
CREATE TABLE tea_stu_rel(
t_id INT,
s_id INT
);

4、添加外键
ALTER TABLE tea_stu_rel
ADD CONSTRAINT fk_tid
FOREIGE KEY(t_id)
REFERENCES teacher(t_id);

ALTER TABLE tea_stu_rel
ADD CONSTRAINT fk_sid
FOREIGE KEY(sid)
REFERENCES students(sid);

8、合并结果集UNION与UNION_ALL

  • 1、UNION
    合并时去除重复记录

  • 2、 UNION ALL
    合并时不去除重复记录

  • 3、格式

SELECT * FROM 表1 UNION SELECT * FROM 表2;
SELECT * FROM 表1 UNION ALL SELECT * FROM 表2;

  • 4、创建表

CREATE TABLE A(name VARCHAR(10),score INT);
CREATE TABLE B(name VARCHAR(10),score INT);

INSERT INTO A VALUES('a',10),('b',20),('c',30);
INSERT INTO A VALUES('a',10),('b',20),('c',40);

  • 4、 UNION

SELECT * FROM A
UNION
SELECT * FROM B;

  • 5、UNION ALL

SELECT * FROM A
UNION ALL
SELECT * FROM B;

  • 6、 注意事项

被合并的两个结果集:列数,列类型必须相同

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 199,711评论 5赞 468
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 83,932评论 2赞 376
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 146,770评论 0赞 330
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 53,799评论 1赞 271
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 62,697评论 5赞 359
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 48,069评论 1赞 276
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 37,535评论 3赞 390
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 36,200评论 0赞 254
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 40,353评论 1赞 294
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,290评论 2赞 317
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,331评论 1赞 329
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,020评论 3赞 315
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 38,610评论 3赞 303
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,694评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 30,927评论 1赞 255
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 42,330评论 2赞 346
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 41,904评论 2赞 341

推荐阅读更多精彩内容

  • 数据库(数据查询)
    1、count 使用 1.1 查询employee表中记录数: SELECT COUNT(*) FROM empl...
    埃菲尔上的铁塔梦i阅读 3,996评论 0赞 1
  • 15天的OCP培训笔记
    第一天 7月13日OCP笔记： Oracle Ocp11g准备资料： OracleFundmentals 书 管理...
    fjxCode阅读 2,778评论 0赞 4
  • mysql数据库的基本操作
    一、基本增删改查二、创建索引的几种方式三、单表查询四、连表查询 一、基本增删改查 1.数据库的增删改查 show ...
    清风徐来_简阅读 424评论 0赞 1
  • MYSQL基础
    MYSQL 基础知识 1 MySQL数据库概要 2 简单MySQL环境 3 数据的存储和获取 4 MySQL基本操...
    Kingtester阅读 7,755评论 5赞 116
  • ［1001夜］4月27日
    我知道未来会有不少困境，但仍需坚持、努力，和负责。这是我的生命习题之一。 2015年4月27日 于上海大众书局
    璐姑娘阅读 1,610评论 0赞 1