數(shù)據(jù)條件查詢

1.排序
select * from stu order by score;-- 默認(rèn)ASC
select * from stu order by score DESC;

2.聚合函數(shù) -- (注意:排除null值)
count:計(jì)算個(gè)數(shù)
select count(age) from stu;
select count(IFNULL(birthday,"1990-01-09")) from stu;
select count(*) from stu;
Max: 計(jì)算最大值
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) 人數(shù) FROM stu where age > 35 GRoup by sex having 人數(shù) > 2;
/*
1.where 在分組之前進(jìn)行限定,如果不滿足條件,則不參與分組;having在分組之后進(jìn)行限定,如果不滿足結(jié)果,則不會(huì)被查詢出來.
2.where 后不可以跟聚合函數(shù)的判斷,having 可以;
*/

4.分頁查詢
select * from stu limit 0,3;-- 0開始,3條;第一頁

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

5.DQL 查詢表中的記錄
Select
+ 字段名(,)
from
+ 表名列表
where
+ 條件列表
group by
+ 分組字段
having
+ 分組之后的條件
order by
+ 排序
limit
+ 分頁

2.基礎(chǔ)查詢

1. 多個(gè)地段的查詢
   select name,age from student;
2. 去除重復(fù)
   select distinct address from student;--distinct
3. 計(jì)算列
   select name,math,english,math + english from student;--使用+
   select name,math,english,math + ifnull(english,0) from student; ifnull(english,默認(rèn)值)
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 數(shù)學(xué),english 英語,math + ifnull(english,0) 總分 from student;

3.條件查詢
1.where 子句后跟條件
2.運(yùn)算符

< <= >= = <> !=
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
* 占位符
:單個(gè)任意字符
select * from student where name like '化%';
select * from student where name like '___';
%:多個(gè)任務(wù)字符
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.一張表只能有一個(gè)主鍵
創(chuàng)建表時(shí),添加主鍵約約束
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 自動(dòng)增長(zhǎng)
    某列是數(shù)值,使用auto_increment 可以來完成值的自動(dòng)增長(zhǎng);
創(chuàng)建時(shí),添加自增長(zhǎng)
    create table student(id int primary key auto_increment,name varchar(20));
刪除子增長(zhǎng)
    alter table student modify id int;
添加子增長(zhǎng)
    alter table student modify id int primary key auto_increment;   

2.非空約束 not null
創(chuàng)建表時(shí),添加約束
create table person(id int, name varchar(20) not null);
刪除字段的非空約束
alter table person modify name varchar(20);
創(chuàng)建表后,添加約束
alter table person modify name varchar(20) not null;

3.唯一約束 unique
創(chuàng)建表時(shí),添加約束
create table person(id int, name varchar(20) unique);
null值比較特殊,null值認(rèn)為是不重復(fù).
刪除唯一約束
alter table person drop index phone_number;
添加唯一約束
alter table person modify phone_number varchar(20) phone_number;

4.外鍵約束 foreign key
創(chuàng)建表時(shí),添加外鍵
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.級(jí)聯(lián)操作
設(shè)置級(jí)聯(lián),更新級(jí)聯(lián)
alter table student add constraint stu_class_fk foreign key (class_id) references department(id)) on update cascade;

級(jí)聯(lián)刪除
1.刪除外鍵
2.級(jí)聯(lián)的刪除
alter table student add constraint stu_class_fk foreign key (class_id) references department(id)) on delete cascade;

6.數(shù)據(jù)庫備份
mysqldump -uroot -p1 db1 > /Users/yt_lwf/Desktop/11/a.sql
7.還原數(shù)據(jù)庫
source /Users/yt_lwf/Desktop/11/a.sql;
8.多表查詢

• 笛卡爾積
• 多表查詢的分類:

  1. 內(nèi)鏈查詢:
     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)的全部數(shù)據(jù) 和 dept 的交集數(shù)據(jù)
     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)的全部數(shù)據(jù) 和 emp 的交集數(shù)據(jù)

  3. 子查詢: 查詢中,嵌套查詢
     select * from emp where salary=(select max(salary) from emp);
     1.子查詢是單行單列
     * 可以作為條件,使用運(yùn)算符去判斷 (運(yùn)算符, > < = ....);
     select * from emp where salary<(select avg(salary) from emp);
     2.子查詢是多行單列
     * 可以作為條件,使用運(yùn)算符去判斷
     select * from emp where dept_id in (select id from dept where name in ("財(cái)務(wù)部","市場(chǎng)部"));
     3.子查詢是多行多列
     *可以作為一張的虛擬表
     select * from dept t1,(select * from emp where join_date > '2011-11-11') t2 where t1.id = t2.dept_id;