DQL 查询数据

数据条件查询

1.排序
select * from stu order by score;-- 默认ASC
select * from stu order by score DESC;

2.聚合函数 -- (注意:排除null值)
count:计算个数
select count(age) from stu;
select count(IFNULL(birthday,"1990-01-09")) from stu;
select count(*) from stu;
Max: 计算最大值
select max(score) from stu;
Min:最小值
select min(score) from stu;
Sum:和
select sum(age) from stu;
avg:平均值
select avg(age) from stu;

3.分组查询:
Select sex , AVG(age) FROM stu GRoup by sex;
Select sex , AVG(age),count(id) FROM stu GRoup by sex;
mysql> Select sex , AVG(age),count(id) FROM stu where age > 35 GRoup by sex;
Select sex , AVG(age),count(id) FROM stu where age > 35 GRoup by sex having count(id) > 2;
Select sex , AVG(age),count(id) 人数 FROM stu where age > 35 GRoup by sex having 人数 > 2;
/*
1.where 在分组之前进行限定,如果不满足条件,则不参与分组;having在分组之后进行限定,如果不满足结果,则不会被查询出来.
2.where 后不可以跟聚合函数的判断,having 可以;
*/

4.分页查询
select * from stu limit 0,3;-- 0开始,3条;第一页

/*
Limit: 只能在mysql,里面使用!
*/

5.DQL 查询表中的记录
Select
+ 字段名(,)
from
+ 表名列表
where
+ 条件列表
group by
+ 分组字段
having
+ 分组之后的条件
order by
+ 排序
limit
+ 分页

2.基础查询

1. 多个地段的查询
   select name,age from student;
2. 去除重复
   select distinct address from student;--distinct
3. 计算列
   select name,math,english,math + english from student;--使用+
   select name,math,english,math + ifnull(english,0) from student; ifnull(english,默认值)
4. 起别名
   select name,math,english,math + ifnull(english,0) as 总分 from student;
   select name,math,english,math + ifnull(english,0) 总分 from student;
   select name 姓名,math 数学,english 英语,math + ifnull(english,0) 总分 from student;

3.条件查询
1.where 子句后跟条件
2.运算符

< <= >= = <> !=
select * from student where age > 20;
between...and..
select * from student where between 20 and 30;
In
select * from student where age in (22,18);
Like
* 占位符
:单个任意字符
select * from student where name like '化%';
select * from student where name like '___';
%:多个任务字符
select * from student where name like '马%';
select * from student where name like '%马%';
Is null
select * from student where English is null;
And 或者 &&
or 或者 ||
not 或者 !
select * from student where English is not null;

约束;

1.主键约束 primary key --:1.非空且唯一;2.一张表只能有一个主键
创建表时,添加主键约约束
create table student(id int primary key,name varchar(20));
删除主键
alter table student drop primary key;
添加主键
alter table student modify id int primary key;

1.1 自动增长
    某列是数值,使用auto_increment 可以来完成值的自动增长;
创建时,添加自增长
    create table student(id int primary key auto_increment,name varchar(20));
删除子增长
    alter table student modify id int;
添加子增长
    alter table student modify id int primary key auto_increment;   

2.非空约束 not null
创建表时,添加约束
create table person(id int, name varchar(20) not null);
删除字段的非空约束
alter table person modify name varchar(20);
创建表后,添加约束
alter table person modify name varchar(20) not null;

3.唯一约束 unique
创建表时,添加约束
create table person(id int, name varchar(20) unique);
null值比较特殊,null值认为是不重复.
删除唯一约束
alter table person drop index phone_number;
添加唯一约束
alter table person modify phone_number varchar(20) phone_number;

4.外键约束 foreign key
创建表时,添加外键
create table class(class_id int,name varchar(20));
create table student(id int primary key auto_increment,name varchar(20),class_id int,constraint stu_class_fk foreign key (class_id) references department(id));
删除外键
alterr table student drop foreign key stu_class_fk;
添加外键
alter table student add constraint stu_class_fk foreign key (class_id) references department(id));

5.级联操作
设置级联,更新级联
alter table student add constraint stu_class_fk foreign key (class_id) references department(id)) on update cascade;

级联删除
1.删除外键
2.级联的删除
alter table student add constraint stu_class_fk foreign key (class_id) references department(id)) on delete cascade;

6.数据库备份
mysqldump -uroot -p1 db1 > /Users/yt_lwf/Desktop/11/a.sql
7.还原数据库
source /Users/yt_lwf/Desktop/11/a.sql;
8.多表查询

• 笛卡尔积
• 多表查询的分类:

  1. 内链查询:
     1.隐式
     select * from emp,dept where emp.dept_id = dept.id;
     select * from emp,dept where emp.'dept_id' = dept.'id';
     select emp.name,gender,dept.name from emp,dept where emp.dept_id = dept.id;
     select t1.name,t1.gender,t2.name from emp t1,dept t2 where t1.dept_id = t2.id;

     2.显式
     语法: select 字段列表 from 表名1 inner join 表名2 on 条件;
     select * from emp inner join dept on emp.dept_id = dept.id;
     select * from emp join dept on emp.dept_id = dept.id; -- 省略 inner

  2. 外链查询:
     1.左外连接
     语法: select 字段列表 from 表1 left [outer] join 表2 on 条件;
     select t1.,t2.name from emp t1 left join dept t2 on t1.dept_id = t2.id;
     查询左表(emp)的全部数据 和 dept 的交集数据
     2.右外连接
     语法: select 字段列表 from 表1 right [outer] join 表2 on 条件;
     select t1.,t2.name from emp t1 left join dept t2 on t1.dept_id = t2.id;
     查询右表(dept)的全部数据 和 emp 的交集数据

  3. 子查询: 查询中,嵌套查询
     select * from emp where salary=(select max(salary) from emp);
     1.子查询是单行单列
     * 可以作为条件,使用运算符去判断 (运算符, > < = ....);
     select * from emp where salary<(select avg(salary) from emp);
     2.子查询是多行单列
     * 可以作为条件,使用运算符去判断
     select * from emp where dept_id in (select id from dept where name in ("财务部","市场部"));
     3.子查询是多行多列
     *可以作为一张的虚拟表
     select * from dept t1,(select * from emp where join_date > '2011-11-11') t2 where t1.id = t2.dept_id;