MySQL数据库(四)

沙海
沙海
沙海
165
文章
2
评论
2018年1月27日12:59:54 评论 93 9252字阅读30分50秒

01. 条件查询-比较运算符

/*
    条件查询
    条件查询可以查询出满足条件要求的数据。
    条件查询要结合运算符去使用。
    运算符用作条件筛选。
    比较运算符
        >:大于
        >=:大于等于
        <: 小于
        <=: 小于等于
        =: 等于
        !=:不等于
        <>: 不等于
    条件查询的格式
        select * from 表名 where 条件;
*/
# 查询math分数大于80分的学生的所有信息
SELECT * FROM student WHERE math > 80;

# 查询math分数大于80分的学生的姓名以及年龄
SELECT `name`, age FROM student WHERE math > 80;

# 查询english分数小于或等于80分的学生
SELECT * FROM student WHERE english <= 80;

# 查询age等于20岁的学生
SELECT * FROM student WHERE age = 20;

# 查询age不等于20岁的学生
SELECT * FROM student WHERE age != 20;
SELECT * FROM student WHERE age <> 20;

02. 条件查询-逻辑运算符

/*
    逻辑运算符
    and: 与, 相当于java中的&&。 
    or: 或, 相当于java中的||
    not: 非, 相当于java中的!
    in(...):包含。 只要包含其中之一就算满足条件。 比如 id in(1,3,5) 表示只要id包含1,3,5其中之一即可。
*/
# 查询age大于35且性别为男的学生(两个条件同时满足)
SELECT * FROM student WHERE age > 35 AND sex = '男';

# 查询age大于35或性别为男的学生(两个条件其中一个满足)
SELECT * FROM student WHERE age > 35 OR sex = '男';

# 查询id是1或3或5的学生
SELECT * FROM student WHERE id = 1 OR id = 3 OR id = 5;

# 使用in的方式完成
# 查询id是1或3或5的学生
SELECT * FROM student WHERE id IN(1,3,5);

# 查询id不是1或3或5的学生
SELECT * FROM student WHERE id NOT IN(1,3,5);

03. 条件查询-范围查询

/*
    范围查询 between ... and ...
    比如: between 3 and 10, 表示数据在3到10之间。并且包含3也包含10.
*/
# 查询english成绩大于等于75,且小于等于90的学生
SELECT * FROM student WHERE english >= 75 AND english <= 90;
SELECT * FROM student WHERE english BETWEEN 75 AND 90; -- 在75和90之间,包含75和90

04. 条件查询-模糊匹配

/*
    模糊查询 like
    模糊查询要结合通配符去使用。
        %:表示任意个任意的字符。
        _:表示一个任意的字符。
    格式:
        select * from 表名 where 字段 like '通配符字符串';
    % 小技巧
        '马%': 匹配以马开头的
        '%马': 匹配以马结尾的
        '%马%': 匹配包含马的。
*/
# 查询姓马的学生
# 查询姓名以马开头的学生。
# 马开头的字符串的特点 -> 第一个字符固定是马, 剩下可以有任意个任意字符。
SELECT * FROM student WHERE `name` LIKE '马%';

# 查询姓名中包含'德'字的学生
# 包含德。  前面可以有任意个任意的字符。 中间是德。 后面也可以有任意个任意的字符。
SELECT * FROM student WHERE `name` LIKE '%德%';

# 查询姓马,且姓名有三个字的学生
# 姓名的第一个字必须是马, 第二个字和第三个字可以是任意的字符。
SELECT * FROM student WHERE `name` LIKE '马__';

05. 排序查询

/*
    排序查询
    可以对查询出的结果根据指定的字段进行排序。
    排序查询使用order by关键字。
    格式:
        select * from 表名 order by 要排序的字段 [asc | desc];
    asc: 表示升序排序。
    desc: 表示降序排序。
    asc和desc可以省略,如果省略,默认是升序排序。

    组合排序: 可以根据两个字段进行排序。  先根据字段1进行排序,如果字段1有相同的数据,那么
    再根据字段2进行排序。
    格式:
        select * from 表名 order by 要排序的字段 [asc | desc], 要排序的字段 [asc | desc];
*/
# 查询所有学生的数据, 并按照年龄升序排序。
SELECT * FROM student ORDER BY age ASC;
# ASC也可以省略,如果省略默认是升序
SELECT * FROM student ORDER BY age;
# 查询所有数据, 并按照年龄降序排序。
SELECT * FROM student ORDER BY age DESC;
# 查询所有的学生数据,并按照年龄降序排序,如果年龄相同再按照数学成绩升序排序。
SELECT * FROM student ORDER BY age DESC, math ASC;

06. 聚合函数

/*
    聚合函数。
    我们之前查询数据都是横向查询。聚合函数是用来纵向查找。

    count(列):统计该列有多少条数据。 不统计null值。
    max(列):  统计该列数据的最大值
    min(列):  统计该列数据的最小值
    sum(列): 统计该列数据的总和
    avg(列):  统计该列数据的平均值。

    聚合函数要用在要查询的字段位置
    select 聚合函数 from 表名;
*/
# 查询id这一列有多少条数据
SELECT COUNT(id) FROM student;

# 查询english这一列有多少条数据
SELECT COUNT(english) FROM student;

# 查询student表有多少条数据。
SELECT COUNT(*) FROM student;

# 查询student表中math数学成绩的最高分
SELECT MAX(math) FROM student;

# 查询student表中math数学成绩的最低分
SELECT MIN(math) FROM student;

# 查询student表中所有人的数学总成绩。
SELECT SUM(math) FROM student;

# 查询student表中数学成绩的平均分
SELECT AVG(math) FROM student;

07. 分组查询

/*
    查询每个性别的数学总成绩。
    如果要查询每个性别的数学总成绩,可以对数据根据性别进行分组,分组之后再查询总成绩。
    如果要进行分组,可以使用group by关键字。
    格式:
        group by 字段名;
    分组查询一定要结合聚合函数去使用。
    
    having 和 where的区别。
    他们都用作条件筛选, 区别如下:
        1. where用在分组之前进行过滤, having用在分组之后进行过滤
        2. where后面不能使用聚合函数, having后面可以使用聚合函数。
*/
# 查询每个性别的数学总成绩。
# 根据性别进行分组。
# 查询数学总成绩。
# 如果进行了分组,那么使用聚合函数查询出来的就是每个组的数据。
SELECT sex, SUM(math) FROM student GROUP BY sex;

# 查询男女各多少人(查询每个性别各有多少人)
SELECT sex, COUNT(*) FROM student GROUP BY sex;

# 查询年龄大于25岁的人,按性别分组,统计每组的人数 (统计每个性别中有多少大于25岁的人)
# 需要做的操作: 
# 查询条件为 年龄大于25
# 按性别进行分组。
# 统计人数(最终要查询的东西)
SELECT sex,COUNT(*) FROM student WHERE age > 25 GROUP BY sex;

# 查询年龄大于25岁的人,按性别分组,统计每组的人数,并只显示性别人数大于2的数据
SELECT sex,COUNT(*) FROM student WHERE age > 25 GROUP BY sex HAVING COUNT(*) > 2;

08. 分页查询

/*
        分页查询
        使用limit关键字
        格式:
            limit m, n
            m: 表示从第几条数据开始显示 (从0开始数)
            n: 表示一次显示几条数据。
*/ 
# 准备数据
INSERT INTO student(id,NAME,age,sex,address,math,english) VALUES
(9,'唐僧',25,'男','长安',87,78),
(10,'孙悟空',18,'男','花果山',100,66),
(11,'猪八戒',22,'男','高老庄',58,78),
(12,'沙僧',50,'男','流沙河',77,88),
(13,'白骨精',22,'女','白虎岭',66,66),
(14,'蜘蛛精',23,'女','盘丝洞',88,88);

# 分页显示,一页显示五条数据
# 第一页
SELECT * FROM student LIMIT 0,5;  -- 从第0条开始显示,显示5条

# 第二页
SELECT * FROM student LIMIT 5,5;

# 第三页
SELECT * FROM student LIMIT 10,5;

12. 约束-主键约束

/*
    约束:可以对某一列的数据进行限制。
    主键约束 primary key
    主键约束可以保证该列的数据唯一且不能为空。
    主键只是做一个标记,除此之外没有特殊作用。

    一个表中最多只能有一个主键, 并且从表的设计角度来说。
    每个表都应该有一个主键。
    主键不建议是业务数据。 
*/
# 主键的添加方式一 (常用)
# 在创建表的时候直接添加主键约束。
# 字段名 数据类型(长度) [约束]
CREATE TABLE person (
    id INT PRIMARY KEY, -- primary key 表示对id加上了主键约束
    first_name VARCHAR(20), 
    last_name VARCHAR(20),
    address VARCHAR(20)
);
# 添加数据
INSERT INTO person (id, first_name, last_name, address) VALUES (1, '三丰', '张', '武当');
INSERT INTO person (id, first_name, last_name, address) VALUES (2, '师太', '灭绝', '峨眉');
# id已经添加了主键约束,里面的数据不能重复
INSERT INTO person (id, first_name, last_name, address) VALUES (2, '婆婆', '金华', '光明顶');
# id是主键,也不能是null
INSERT INTO person (id, first_name, last_name, address) VALUES (NULL, '婆婆', '金华', '光明顶');

# 主键的添加方式二
# 创建表之后使用alter语句进行添加
# 创建表,没有主键
CREATE TABLE person (
    id INT,
    first_name VARCHAR(20), 
    last_name VARCHAR(20),
    address VARCHAR(20)
);

# 使用alter语句添加主键
# alter table 表名 add primary key (列名);
ALTER TABLE person ADD PRIMARY KEY (id);

# 主键的删除
# alter table 表名 drop primary key;
ALTER TABLE person DROP PRIMARY KEY;

# 联合主键
# 多个列共同组成一个主键
# 添加联合主键的格式
# alter table 表名 add primary key (列名,列名);
ALTER TABLE person ADD PRIMARY KEY (first_name, last_name);

13. 约束-自动增长列

/*
    自动增长列 auto_increment
    使用auto_increment 修饰的列里面的数据系统可以自动维护。 
    如果我们向表中添加数据, 添加数据时忽略自动增长列进行添加, 那么系统会自动维护该列数据,把该列
    数据自动加1.
    自动增长列一定要对整数列使用。
    一般都会对主键设置自动增长,让系统自动去维护主键的值。
*/
# 删除之前的表
DROP TABLE person;
# 给列添加自动增长
# 在创建表的时候直接进行添加。
# 给主键加上自动增长。
CREATE TABLE person (
    id INT PRIMARY KEY AUTO_INCREMENT, -- 主键并且自动增长。
    `name` VARCHAR(20), 
    address VARCHAR(20)
);

# 添加数据,并且忽略主键进行添加。
INSERT INTO person (`name`, address) VALUES ('张三丰', '武当山');
INSERT INTO person (`name`, address) VALUES ('张无忌', '光明顶');
INSERT INTO person (`name`, address) VALUES ('大幂幂', '北京');

# 添加数据,指定主键列
INSERT INTO person (id, `name`, address) VALUES (10, '刘备', '蜀国');

# 不指定主键,那么会自动维护
INSERT INTO person (`name`, address) VALUES ('达芬奇', '意大利');

# delete删除所有数据
# 是逐条删除所有数据,不会重置自增长序列
DELETE FROM person;

# truncate删除所有数据
# 直接摧毁表,然后新建一张一模一样的表, 会重置自增长序列
TRUNCATE TABLE person;

14. 唯一约束

/*
    唯一约束: unique
    可以保证某一列的值不能重复。
*/
# 删除原来的perosn表
DROP TABLE person;

# 在创建表的时候直接对某一列添加唯一约束。
# 字段名 数据类型(长度) [约束]
CREATE TABLE person (
    id INT PRIMARY KEY AUTO_INCREMENT, -- 主键自动增长
    `name` VARCHAR(20) UNIQUE, --  执行, 使用unique进行唯一约束
    address VARCHAR(20)
);

# 添加数据
INSERT INTO person (`name`, address) VALUES ('刘备', '中国');
INSERT INTO person (`name`, address) VALUES ('关二爷', '也是中国');
# name已经使用unique进行了唯一约束,不能添加重复数据。
INSERT INTO person (`name`, address) VALUES ('关二爷', '还是中国');

INSERT INTO person (`name`, address) VALUES ('张三爷', '还是中国');

# unique不能限制多个null值。 
INSERT INTO person (`name`, address) VALUES (NULL, '还是中国');
INSERT INTO person (`name`, address) VALUES (NULL, '还是中国');

# 删除唯一约束
# alter table 表名 drop index 列名;
ALTER TABLE person DROP INDEX `name`;

15. 约束-非空约束

/*
    非空约束 not null
    可以保证某一列的数据不能为null
*/
# 添加约束
# 在创建表的之后直接给某个列添加一个非空约束
CREATE TABLE person (
    id INT PRIMARY KEY AUTO_INCREMENT, 
    `name` VARCHAR(20) NOT NULL, -- 对name使用非空约束,保证该列的值不能为null
    address VARCHAR(20)
);

# 添加数据
INSERT INTO person (`name`, address) VALUES ('刘备', '蜀国');
# name已经使用了非空约束,不能为null值。
INSERT INTO person (`name`, address) VALUES (NULL, '蜀国');
INSERT INTO person (`name`, address) VALUES ('刘备', '蜀国');

16. 约束-默认值

/*
    默认值约束 default
    default可以给某列数据提供一个默认值, 如果在添加的时候不向该列添加数据,该列数据会是默认值。
    格式:
        create table 表名(
            字段名 数据类型(长度) default 默认值
        );
*/
# 创建表, 给name字段添加默认值
CREATE TABLE person(
    id INT PRIMARY KEY AUTO_INCREMENT, 
    `name` VARCHAR(20) DEFAULT '佚名', -- 表示该列的默认值是佚名
    address VARCHAR(20)
);
# 添加数据
INSERT INTO person (address) VALUES ('我是谁我在哪');
INSERT INTO person (`name`,address) VALUES ('王叔叔', '我是王叔叔,我在隔壁');

# 查询
SELECT * FROM person;

18. 一对多(多对一)关系的介绍

参考下图:

MySQL数据库(四)

19. 一对多关系的实现

/*
    一对多的关系(部门和员工)
    一个部门可以有多个员工,一个员工只能属于一个部门,这个就是一对多
    一对多建表原则是建立两张表   
        a. 部门表
        b. 员工表
    在员工表中提供一个列叫做部门id,用来和部门表产生关系。
*/
# 创建部门表
CREATE TABLE department (
    id INT PRIMARY KEY AUTO_INCREMENT, 
    `name` VARCHAR(20), -- 部门名称
    description VARCHAR(20) -- 部门描述
);

# 创建员工表
CREATE TABLE employee (
    id INT PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(20), -- 员工姓名
    age INT, -- 员工年龄
    dept_id INT -- 所属部门id
);
# 添加外键约束。
# 让员工表中dept_id和部门表中的id产生一个强制性关联
# 外键约束要添加在从表上
# 格式
# alter table 从表名 add [constraint 约束名] foreign key (外键列名) references 主表(主键);
ALTER TABLE employee ADD CONSTRAINT e_d_fk FOREIGN KEY (dept_id) REFERENCES department(id);

# 删除外键
# alter table 表名 drop foreign key 外键名称;
ALTER TABLE employee DROP FOREIGN KEY e_d_fk;

# 向部门表添加数据
INSERT INTO department (`name`, description) VALUES ('武当','都是道士');
INSERT INTO department (`name`, description) VALUES ('峨眉','尼姑很多');
INSERT INTO department (`name`, description) VALUES ('少林','都是秃子');

# 向员工表添加数据
INSERT INTO employee (`name`, age, dept_id) VALUES ('张三丰', 100, 1);
INSERT INTO employee (`name`, age, dept_id) VALUES ('张翠山', 90, 1);
INSERT INTO employee (`name`, age, dept_id) VALUES ('灭绝师太', 98, 2);
# 如果向从表中添加数据,如果指定的外键列的数据在主表中不存在,那么添加失败
INSERT INTO employee (`name`, age, dept_id) VALUES ('张无忌', 95, 10);
# 外键列可以添加null值。
INSERT INTO employee (`name`, age, dept_id) VALUES ('张无忌', 95, NULL);
# 如果删除主表中的数据时, 如果该数据被从表使用着,那么无法删除
DELETE FROM department WHERE id = 1;

20. 多对多关系的介绍

参考下图:

MySQL数据库(四)

21. 多对多关系

/*
    多对多的关系(学生和课程)
    多对多建表原则是建立三张表。
        a.学生表
        b.学科表
        c.中间表
    使用中间表和另外两张表产生关联关系。
*/
# 创建学生表
CREATE TABLE student (
    id INT PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(20),
    age INT
);
# 创建学科表
CREATE TABLE `subject` (
    id INT PRIMARY KEY AUTO_INCREMENT, 
    `name` VARCHAR(20), -- 学科名称
    description VARCHAR(200) -- 学科描述 
);
# 建立中间表,用来和学生表和学科表产生关系
CREATE TABLE stu_sub(
    stu_id INT, -- 学生id
    sub_id INT  -- 学科id
)
# 外键约束要加在中间表上
# alter table 中间表 add [constraint 约束名称] foreign key (外键列名) references 主表(主键列名);
# 使用中间表中的stu_id和student表中的主键id产生关联关系
ALTER TABLE stu_sub ADD FOREIGN KEY (stu_id) REFERENCES student(id);
# 使用中间表中的sub_id和subject表中的主键id产生关联关系
ALTER TABLE stu_sub ADD FOREIGN KEY (sub_id) REFERENCES `subject`(id);

# 添加数据
# 向学生表添加数据
INSERT INTO student (`name`, age) VALUES ('张无忌',12);
INSERT INTO student (`name`, age) VALUES ('张三丰',13);
INSERT INTO student (`name`, age) VALUES ('东方不败',14);

# 向学科表中添加数据
INSERT INTO `subject` (`name`, description) VALUES ('九阳神功', '绝世神功');
INSERT INTO `subject` (`name`, description) VALUES ('葵花宝典', '神奇的武功');
INSERT INTO `subject` (`name`, description) VALUES ('太极拳', '以柔克刚');

# 向中间表添加关联关系
INSERT INTO stu_sub (stu_id, sub_id) VALUES (1, 1);
INSERT INTO stu_sub (stu_id, sub_id) VALUES (1, 3);
INSERT INTO stu_sub (stu_id, sub_id) VALUES (2, 3);

# 如果向中间表中添加数据, 数据在主表中不存在,添加失败。
INSERT INTO stu_sub (stu_id, sub_id) VALUES (12, 3);

# 删除主表中的数据时,如果该数据被使用着,无法删除。
DELETE FROM student WHERE id = 1;

22. 一对一关系的介绍

参考下图:

MySQL数据库(四)

继续阅读
weinxin
资源分享QQ群
本站是一个IT技术分享社区, 会经常分享资源和教程; 分享的时代, 请别再沉默!
沙海
  • 版权声明:本站是一个IT技术分享社区,沙海技术团队的技术分享社区,我们会经常分享资源和教程。
  • 转载请注明:MySQL数据库(四) - Java秀 ☜(ˆ▽ˆ)
C语言速查手册
0001-基于Java的在线考试系统设计与实现
0012-基于Java的班级信息管理系统的设计与实现源码
0002-基于Java Web的瓷器商城系统的设计与实现
匿名

发表评论

匿名网友 填写信息

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: