MySql

MySql

1 概念

  • database：数据库，存储数据的集合。
  • 数据库管理系统：DataBase Management System,DBMS。用于建立，使用和维护数据库，对数据库
    进行统一管理和控制的大型软件，保证了数据库的完整性和安全性。

一般我们说的数据库指的是存储数据的集合和数据库管理系统。即存储,维护和管理数据。

常见的数据库：MYSQL,Oracle,DB2等等

2 SQL语句

2.1 SQL分类

  • DDL：Data Definition language，数据定义语言，用于定义数据库对象：数据库，表，列。关键字：create，show，drop,alter等。(结构)
  • DML：Data Manipulation language，用于对数据库中的记录进行更新。关键字：insert，delete，update等(数据)。
  • DQL：Data Query language，用于查询数据库中表的记录。关键字：select，from，where等。【重点】
  • DCL：Data Control language，用于定义数据库的访问权限和安全级别及创建用户。关键字：grant等。(了解即可)

2.2 对数据库的操作(create,drop,alter,show)

增：  
    create database 库名 [character set gbk collate gbk_chinese_ci];
删：
    drop database 库名；
改：
     alter database 库名 character set utf8；  修改某数据库的字符集
查：
    show databases；     显示所有的数据库
    show create database 库名；    显示某个数据库
其它：
    use 库名； 使用某个数据库
    select database();  显示当前所用的数据库

2.3 对表单的操作:（create,drop,alter,show）

以下表名均用stu代替：    
增：
    create table stu(
        字段名1 类型1(长度)[约束]，
        字段名2 类型2(长度)[约束]...
    );

    约束：
        唯一约束：unique，唯一
        非空约束:not null,非空
        主键约束：primary key    ,唯一和非空的组合。

删：
    alter table stu drop 字段名；   删除列
    drop table stu； 删除表单 
改：
    *alter table stu add 字段名 类型1(长度)[约束]；       增加一列
    *alter table stu modify 字段名 类型1(长度)[约束]；        修改列的长度和约束
    *alter table stu change 旧列名 新列名 类型1(长度)[约束]；    修改列名
    *alter table stu drop 字段名；  删除列
    *alter table stu character set utf8；        修改表的字符集
    *rename table 旧表名 to 新表名；       修改表名
查：
    show tables;    查看所有表单
    desc 表名；        查看表单结构

2.4 对表单中数据的操作(insert into,update,delete)

增：
    方式1：插入部分/所有列的值
    insert into 表名(字段名1，字段名2...字段名n) values(与前面对应的值);
    
    方式2：插入所有列的值
    insert into 表名 values(所有列的对应的值);    

    1.列名和后面values里面的列名数以及顺序必须一致。
    2.列名的类型和插入的值必须一致，插入的值不能超过最大长度。
    3.值如果是字符串或者日期必须加上''单引号。

删：
    delete from 表名 [where]...；  不加判断条件即为删除整个表格
    truncate table 表名；      删除整个表格
    
    delete和truncate的区别：
    1：delete是一行一行的删除数据，不清空auto_increment记录数，删除的数据可以一个事务中恢复
    2：truncate是直接把表格删除，再创建一个新的表格，清空auto_increment，所以效率较delete高。
    这是彻底删除，无法恢复
改：
    *update 表名 set 字段名=值，字段名=值...；
    *update 表名 set 字段名=值，字段名=值...where条件；
查：
    select */列名1,列名2.. from 表名；     查询全部/部分列的信息

3 SQL查询

3.1 简单查询

语法：select [distinct] */列名，列名 from 表名 [where条件]；
    distinct表示去重。

例子：
1.查询所有商品：   select * from 表名;
2.查询商品名和商品价格：   select name,price from 表名;
3.别名查询，使用关键字as。as可省略
    select name as '商品名' from 表名;/select name '商品名' from 表名;
4.去重查询： select distinct name from 表名;   
5.将所有商品价格加上10进行显示。
    select price+10 from 表名;

2.2 条件查询

> < <= >= = <> ： 大于，小于，小于等于，大于等于，等于，不等于 

where 等同于java中的 if，但是在where语句后进行判断操作时使用'='，而不是用'=='。
* between...and...  在...和...之间
* in(数据1，数据2...数据n);    在几个数据中
* and:  和，并且
* or：  或
* like：     模糊查询，占位符%和_,%表示占0或多个字符，_表示占一个字符
* is null：判断是否为空。(了解)

2.3 排序

order by 字段名 asc/desc ：     默认asc升序，desc为降序。ascending,descending

例子：
1：查询所有的商品，按价格进行降序排序
    select * from product order by price desc;
2：查询所有名称含有"士"的商品，并按价格降序排序
    select * from product where name like '%士%' order by price desc；

order by 的排序在筛选完成后进行，因此order by语句放在sql语句最后面。

2.4 聚合函数

聚合函数不统计null值。

* count(*|字段名); 对全部/某列的数据计数
* sum():求和
* max：求最大值
* min：求最小值
* avg：求平均值

例子：
1：获得所有商品的价格的总和：
    select sum(price) from product;
2：获得所有商品的平均价格：
    select avg(price) from product;
3:获得所有商品的个数
    select count(*) from product; 统计所有商品一般用*

2.5 分组

group by 字段名    ：
group_concat()：组合在一起

案例：group by结合聚合函数使用(聚合函数前写得字段名必须是用于分组的字段名)
-- 创建数据库mydb1
CREATE DATABASE mydb1;
-- 使用数据库mydb1
USE mydb1;
-- 创建表单product
CREATE TABLE product(
    id INT,NAME VARCHAR(50),price INT
);
-- 往表格中添加数据
INSERT INTO product VALUES(1,'苹果',20);
INSERT INTO product VALUES(2,'李子',2);
INSERT INTO product VALUES(3,'葡萄',55);
INSERT INTO product VALUES(4,'桃子',40);
INSERT INTO product VALUES(5,'西瓜',22);
INSERT INTO product VALUES(88,'冬瓜',10);
-- 将所有商品的id值变为1
UPDATE product SET id=1;
-- 将部分商品的id值变为2
UPDATE product SET id=2 WHERE price BETWEEN 10 AND 30;
-- 1:根据id字段分组，分组后统计商品的个数
SELECT id,COUNT(*) AS '商品数量' FROM product GROUP BY id;
-- 2：根据id字段分组，分组统计每组商品的平均价格，并且平均价格大于20
SELECT id,AVG(price) AS 平均价格 FROM product GROUP BY id HAVING AVG(price)>20;
-- 3:根据id字段分组，显示每组里面的成员，这个时候如果只用group by就只能显示每组的第一个成员，因此要加上group_concat()：括号内不能用*
SELECT id,GROUP_CONCAT(NAME) FROM product group by id;

1：

2：

3：

2.6 分组后筛选(having)

  • having是分组后筛选

where和having的区别

  • 1.having是在分组后对数据进行过滤.where是在分组前对数据进行过滤
  • 2.having后面可以使用聚合函数(统计函数)where后面不可以使用聚合函数。理解：没有筛选出某一列数据之前当然不能用聚合函数进行统计。
  • 3.having后面加的条件一定要与分组有关，因为他是分组后进行筛选的关键字。
  • 4.WHERE是分组前记录的条件，如果某行记录没有满足WHERE子句的条件，那么这行记录不会参加分组；而HAVING是对分组后数据的约束。

2.7 分页查找(limit)

sql查询语句完整格式：
select [distinct] */列名，列名... from 表名 where 条件 group by 按照？列名分组
order by[asc/desc] having 分组后筛选条件 limit m,n;

distinct去重，where是在分组前的筛选条件，group by一般和聚合函数或者group_concat一起使用，
单独使用没意义。order by默认排序asc，having后接分组后的筛选条件。limit中m代表从第m行开始，
显示n行。
分页显示：limit (current-1)*n,n; current为当前页面，下一页的limit格式就是这样写

3 数据完整性

为了确保用户输入的数据保存到数据库中是正确的，在创建表的时候要给表中的列添加约束。

完整性的分类：

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

3.1 实体完整性

实体中的每一行就代表一个实体。实体完整性的作用：标识的每一行记录都不重复

约束类型：主键约束(primary key),唯一约束(unique),自动增长列(auto_increment)

3.1.1 主键约束（primary key）

注意：每个表中都必须要有一个主键

特点：数据唯一，且不能为null，相当于unique和not null的结合

三种添加主键的方式：

方式一：直接在建表字段定义时添加
create table user(
    id int primary key
);

方式二：建表时在最后添加，好处时可以创建联合主键
create table user(
    id int,username varchar(50),
    primary key(id)
);

create table user(
    id int,
    name varchar(50),
    primary key(id,name)
);

方式三：通过sql语句添加：
create table user(id int,name varchar(50));
alter table user add constraint user_pk primary key(id);

3.1.2 唯一约束(unique)

特点：数据不能重复

create table user(id int,username varchar(50) unique);

3.1.3 自动增长列(auto_increment)

特点：给主键添加自动增加的数值，列只能是整数类型

create table user(
    id int primary key auto_increment,username varchar(50)
);

3.2 域完整性

域完整性的作用：限制此单元格的数据正确

域完整性约束：

  • 非空约束：not null
  • 默认值约束：default

3.2.1 非空约束(not null)

create table user(
    id int,name varchar(50) not null
);
name被限制不能为空

3.2.2 默认约束(default)

create table user(
    id int,name varchar(50),sex varchar(20) default '男'
);
insert into user values(1,'小芳','女');
insert into user values(1,'小芳',default);

3.3 引用完整性(foreign key：外键约束)

外键约束的两种添加方式：

create table student(
    sid int primary key auto_increment,
    username varchar(50) not null,
    sex varchar(10) default '男'
);

create table score(
    id int primary key auto_increment,
    score int,
    sid int
);
方式一：直接在建表时表中添加
create table score(
    id int primary key auto_increment,name varchar(50) not null,
    sid int,
    constraint fk_score_sid foreign key(sid) references student(sid)
);
    
方式二：sql语句添加外键
alter table  score add constraint fk_score foreign key(sid) references student(sid);

3.4 表与表之间的关系

外键指向主键

3.4.1 一对一

对其中任意一个表添加一个外键列，并且要给外键列添加唯一约束，否则就不是一对一而是一对多了

3.4.2 一对多(多对一)：相对而言

在多的一方添加外键列，创建外键约束指向一的表的主键。

3.4.3 多对多

需要创建一个第三方表格，至少要有两个外键列，分别指向两个表的主键

4 多表查询

多表查询有以下几种：

  • 连接查询【重要】
    • 内连接查询 --- 隐式连接、显式连接
    • 外连接查询 --- 左外连接、右外连接
  • 子查询【非常重要】
  • 联合查询

4.1 连接查询【重要】

连接查询就是求出多个表的乘积，例如t1连接t2，那么查询出的结果就是t1*t2。

连接查询会产生笛卡儿积，这样的查询结果会有大量的无用数据，所以就需要使用筛选得到可用数据

4.1.1 内连接查询

语法：

隐式内连接：
select [*][列名，列名...] from t1,t2 [where条件];
显式内连接：
select [*][类名，列名...] from t1 [inner] join t2 on [筛选条件] [where条件];
4.1.2 外连接查询

语法：

左外连接查询：
select [*][列名，列名...] from t1 left [outer] join t2 on[筛选条件][where条件];
右外连接查询：
select [*][列名，列名...] from t1 right [outer] join t2 on[筛选条件][where条件]

外连接和内连接的区别:

  • 内连接放在前面的显示作为主表，右边的作为从表。外连接则跟左右连接有关。

  • 内连接查询特点：查询结果必须满足条件，不满足条件不显示。

      例：张三的部门deptno为50，而在dept表中只有10、20、30、40部门，
  那么内连接查询结果中就不会出现“张三”这条记录，
  因为它不能满足e.deptno=d.deptno这个条件。

  • 外连接查询特点：查询出的结果存在不满足条件的可能。满足条件的显示出来，不满足条件的显示NULL。

      emp表中“张三”这条记录中，部门编号为50，
  而dept表中不存在部门编号为50的记录，
  所以“张三”这条记录不能满足e.deptno=d.deptno这条件。
  但在左连接中，因为emp表是左表，所以左表中的记录都会查询出来，
  即“张三”这条记录也会查出，但相应的右表部分显示NULL。

4.2 子查询【非常重要】

子查询就是嵌套查询，即select包含select

子查询出现的位置：
1：where后面，作为被查询条件的一部分
2：from后面，作表
具体使用见例子

当子查询结果集形式为多行单列时可以使用ALL或ANY关键字

例如：
1：查询工资高于30号部门所有人的员工信息
select * from emp where sal>(select max(sal) from emp where deptno=30);
等价于：
select * from emp where sal>ALL(select sal from emp where deptno=30);

2:查询工作和工资与MARTIN完全相同的员工信息
select * from emp where (job,sal) in (select job,sal from emp where name='martin');

3:有2个以上直接下属的员工信息
select * from emp where mgr in(select mgr from emp group by mgr having count(*)>=2);

4:求各个部门薪水最高的员工所有信息
select * from emp e,(select max(sal) as maxsal,deptno from emp group by deptno) as a 
where e.sal=a.maxsal and a.deptno=e.deptno;

4.3 联合查询

联合查询就是把两个select语句的查询结果合并到一起

被合并的两个结果：列数、列类型必须相同。

合并查询的两种方式：
union:去除重复记录
    select * from t1 union select * from t2;

union all：不去除重复记录
    select * from t1 union select * from t2;

5 多表查询的练习

-- mysql

-- 新建一个day09_exercise的数据库
CREATE DATABASE day09_exercise;
USE day09_exercise;
SELECT DATABASE();

-- 创建用户user表
CREATE TABLE USER(
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50)
);
DESC USER;
-- 创建订单表
CREATE TABLE orders(
    id INT PRIMARY KEY AUTO_INCREMENT,
    price DOUBLE,
    user_id INT
);
DESC orders;
-- 给订单表添加外键约束\  一对多，向多的表格添加外键
ALTER TABLE orders ADD CONSTRAINT user_fk FOREIGN KEY(user_id) REFERENCES USER(id);

-- 向user表中添加数据
INSERT INTO USER VALUES(3,'张三'),(4,'李四'),(5,'王五'),(6,'赵六');
SELECT * FROM USER;
-- 向orders表中插入数据
INSERT INTO orders VALUES(1,1314,3),(2,1314,3),(3,15,4),(4,315,5),(5,1014,NULL);
SELECT * FROM orders;

-- mysql练习三:
-- --查看用户为张三的订单详情
-- 思路：多表查询
-- 内连接查询---隐式查询：
SELECT * FROM USER u,orders o WHERE u.id=o.user_id AND u.username='李四';
-- 内连接查询---显式查询：
SELECT * FROM USER u INNER JOIN orders AS o ON u.id=o.user_id WHERE u.username='李四';
SELECT * FROM orders AS o INNER JOIN USER u ON u.id=o.user_id AND u.username='李四';
-- 上面查询时，on后面接and表示并且查询也可以，但不建议，推荐除了主从表的条件用on，其他条件用where
-- 在前面的作为主表，显示时出现在前面
-- 外连接查询---左连接查询
SELECT * FROM USER u LEFT OUTER JOIN orders o ON u.id=o.user_id WHERE u.username='李四';
--- 外连接查询---右连接查询
SELECT * FROM USER u RIGHT OUTER JOIN orders o ON u.id=o.user_id WHERE u.username='李四';
-- 外连接和内连接的区别：
-- 

-- 查询出订单的价格大于300的所有用户信息。
SELECT * FROM USER WHERE id=(SELECT user_id FROM orders WHERE price>300); -- 错误
-- 上面式子错误，子查询得到的结果是多个的，用id=只能等于一个数，因此用in
SELECT * FROM USER WHERE id IN (SELECT user_id FROM orders WHERE price>300);

-- 查询订单价格大于300的订单信息及相关用户的信息。
SELECT * FROM USER,orders WHERE user.id=orders.user_id AND price>300;
-- 上面两个式子，前者user中没有price列，所以要到orders里面找到price来进行筛选，
-- 而后者则是拼接后进行筛选，这个时候的表格中已经有price列了，因此直接筛选即可

sql语句具体的用法见 练习题

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

推荐阅读更多精彩内容

  • mySQL笔记
    1、MySQL启动和关闭（安装及配置请参照百度经验，这里不再记录。MySQL默认端口号：3306；默认数据类型格式...
    强壮de西兰花阅读 633评论 0赞 1
  • MySQL数据库学习day3
    回顾 字段类型(列类型)：数值型，时间日期型和字符串类型 数值型：整型和小数型(浮点型和定点型) 时间日期型：da...
    翊溪阅读 924评论 0赞 0
  • MySQL学习
    .数据库 数据库的发展: 文件系统(使用磁盘文件来存储数据)=>第一代数据库(出现了网状模型,层次模型的数据库)=...
    小Q逛逛阅读 956评论 0赞 2
  • 《MySQL技术内幕：SQL编程》书摘
    MySQL技术内幕：SQL编程 姜承尧 第1章 SQL编程 >> B是由MySQL创始人之一Monty分支的一个版...
    沉默剑士阅读 2,404评论 0赞 3
  • MySQL数据库基础01
    1.MySQL数据库 2.SQL语句 第一节课 ###1(MySQL数据库)数据库概念.avi 5...
    码了个农啵阅读 1,192评论 1赞 16