mysql 表查询语句

DQL ：查询语句

排序查询

语法：order by 子句
排序方式:
- ASC : 升序，默认的
- DESC : 降序。
注意：
- 如果有多个排序条件，则当前边得条件值一样时，才会判断第二条件
```
SELECT * from userinfo ORDER BY age ASC,id DESC;
```

聚合函数：

将一列数据作为一个整体，进行纵向计算

count：计算个数

select count(age) from userinfo; -- 返回个数  这种方式是不会把null计算在内得
select count(ifnull(age,0)) from userinfo; -- 判断是否是null 是的话 按0来计算
select count(8) from userinfo； -- 查询记录 （不推荐）

max：计算最大值

select Max(age) from userinfo; -- 计算表中年龄最大得

min：计算最小值

select Min(age) from userinfo; -- 计算表中年龄最小的

sum：计算和

select sum(age) from userinfo; -- 计算表中所有年龄之和

avg：计算平均值

select avg(age) from userinfo; -- 计算表中平均年龄

分组查询：

语法：group by 分组字段;

注意：

分组之后查询的字段：分组字段，聚合函数

where 和 having的区别？

where 在分组之前进行限定，如不符合条件，则不参与分组。having在分组之后进行限定，如果不满足结果，则不会被查询出来；
where 条件后面是不可以跟着聚合函数的，having可以进行聚合函数的判断

实现语句

-- 根据性别来进行分组，并且查出每个组的平均年龄
select sex,avg(age) from userinfo group by sex; 
-- 根据性别来进行分组，查出每个组的平均年龄，并且查出参与平均年龄的人数
select sex,avg(age),count(age) from userinfo group by sex;
-- 根据性别来进行分组，查出每个组的平均年龄，并且查出参与平均年龄的人数 并且过滤掉大于20岁的人
select sex,avg(age),count(age) from userinfo where age>20 group by sex;
-- 同上 并且过滤参与人数小于2的组
select sex,avg(age),count(age) from userinfo where age>20 group by sex having count(age)<2;

分页查询：

语法：limit 开始的索引，每页查询条数

公式：（开始的索引-1）*每页查询条数

select * from userinfo limit 0,3; -- 第一页
select * from userinfo limit 3,3; -- 第二页
select * from userinfo limit 6,3; -- 第三页

limit 只适用于MySql;每个数据库都有自有的分页方式

约束：

概念：对表中的数据进行限定，保证表中数据的正确性，有效性和完整性；
分类：
- 主键约束：primary key
- 非空约束：not null
- 唯一约束：unique
- 外键约束

非空约束

创建表时添加约束

CREATE TABLE userinfo(
		id:INT,
    	name:VARCHAR(20) not null  -- name 为非空字段
)

创建表完成后在给表中字段添加非空字段

ALTER TABLE userinfo MODIFY NAME VARCHAR(20) NOT NULL;

删除非空字段

ALTER TABLE userinfo MODIFY NAME VARCHAR(20);

唯一约束：unique，值不能重复

创建表时，添加唯一约束

CREATE TABLE userinfo(
	id INT,
    name:VARCHAR(20) UNIQUE
)

创建完成后添加唯一约束 ( 注意：唯一约束字段可以有多个null )

ALTER TABLE userinfo MODIFY NAME VARCHAR(20) UNIQUE;

删除唯一约束

ALTER TABLE userinfo MODIFY NAME VARCHAR(20)  -- 这种写法时错误的
ALTER TABLE userinfo DROP INDEX name  -- 这个是正确的

主键约束 primary key

注意
- 含义：非空且唯一
- 一张表只能有一个主键
- 主键就是表中记录的唯一标识

在创建表时添加主键约束

CREATE TABLE userinfo(
   id INT PRIMARY KEY, -- 添加主键  
   name VARCHAR(20)  
)

创建表完成后添加主键

ALTER TABLE userinfo MODIFY id INT PRIMARY KEY;

删除主键

ALTER TABLE userinfo MODIFY id int;  -- 这种写法时错误的
ALTER TABLE userinfo DROP primary key;  -- 这个是正确的

自动增长

概念：如果某一列是int类型的，使用 auto_increment 可以完成自动增长

CREATE TABLE userinfo(
   id INT PRIMARY KEY AUTO_INCREMENT,  -- 添加自动增长
   name VARCHAR(20)  
)
-- 注意 ： 自动增长只跟上一条有关系 例如 上一条是100 下一条就是101
-- 删除自动增长
ALTER TABLE userinfo MODIFY id int;
ALTER TABLE userinfo MODIFY id int auto_increment;

外键约束 foreign key,让表与表产生关系，从而保证数据的正确性

语法

create table userinfo (
	....
    外键列，
    constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称也就是主键名称)
)

删除外键

alter table userinfo drop foreign key 外键列名称

创建表之后添加外键

alter table userinfo add constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称也就是主键名称)

级联更新操作

alter table userinfo add constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称也就是主键名称) ON update CASCADE ON DELETE CASCADE;  -- ON UPDATE CASCADE 级联更新操作  ON DELETE CASCADE 级联删除操作

多表查询

内连接

隐式内连接查询：

  ```mysql
  select * from emp,dept where emp.id=dept.rid -- emp.id 为外键  dept.rid 为主键
  
  select 
  	t1.name,
  	t1.gender,
  	t2.name
  from 
  	emp t1,
  	dept t2
  where 
  	t1.id=t2.rid;
  ```

显示内连接

  ```mysql
   select * from emp inner join dept on emp.id=dept.rid;
   select * from emp join dept on emp.id=dept.rid;  -- inner 是可选的 
  ```

内连接查询：

  1. 从哪些表中查询数据
  2. 条件是什么？
  3. 查询哪些字段

外连接查询

左外连接：

select * from emp left [outer] join dept on emp.id=dept.rid;  -- [outer] 是可选的 
  -- 左外连接查询的是左表以及其交集部分

右外连接

select * from emp right [[outer]] join dept on emp.id=dept.rid;
-- 右外连接查询的是右表以及其交集部分

子查询：嵌套查询

-- 获取最大年龄的那一条信息
select Max(age) from userinfo; -- 查出结果集 例如：56
select * from userinfo where age=56；
select * from userinfo where age=( select Max(age) from userinfo )；

子查询的结果是单行单列的。子查询是可以作为条件，使用运算符判断

-- 查询员工 工资小于平均工资的人
select * from userinfo where salary < ( select avg(salary) from userinfo )

子查询的结果是单行多列的

-- 查询某两个部门的全部员工信息
 select * from userinfo where rid in (select id from  userinfo where name="某个部门名称" or name="另一个部门名称";);

子查询的结果是多行多列的

-- 把子查询当作一个虚拟表
select * from dept t1 ,(select * from emp where join_date>"2011-11-11") t2 where t1.id=t2.rid;

更新完毕

本文由博客一文多发平台 OpenWrite 发布！

mysql基础知识

mysql 表 查询语句