MySql

作者: 明天你好向前奔跑 | 来源:发表于2017-05-12 23:51 被阅读0次

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列了，因此直接筛选即可

MySql

MySql

1 概念

2 SQL语句

2.1 SQL分类

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

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

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

3 SQL查询

3.1 简单查询

2.2 条件查询

2.3 排序

2.4 聚合函数

2.5 分组

2.6 分组后筛选(having)

2.7 分页查找(limit)

3 数据完整性

3.1 实体完整性

3.1.1 主键约束（primary key）

3.1.2 唯一约束(unique)

3.1.3 自动增长列(auto_increment)

3.2 域完整性

3.2.1 非空约束(not null)

3.2.2 默认约束(default)

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

3.4 表与表之间的关系

3.4.1 一对一

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

3.4.3 多对多

4 多表查询

4.1 连接查询【重要】

4.1.1 内连接查询

4.1.2 外连接查询

4.2 子查询【非常重要】

4.3 联合查询

5 多表查询的练习

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

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

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