DQL笔记

DQL查询

1.起别名查询

mysql> select name,age from teacher;
+------+-----+
| name | age |
+------+-----+
| zls  |  29 |
| wls  |  30 |
+------+-----+

不推荐，效率不高

给teacher 表取别名为t，查询name和age两列信息
select t.name,t.age from teacher as t;
等效于select name,age from teacher;

mysql> select t.name,t.age from teacher as t;
+------+-----+
| name | age |
+------+-----+
| zls  |  29 |
| wls  |  30 |
+------+-----+
2 rows in set (0.00 sec)

mysql> select name,age from teacher;
+------+-----+
| name | age |
+------+-----+
| zls  |  29 |
| wls  |  30 |
+------+-----+
2 rows in set (0.00 sec)

表列也可以起别名

mysql> select t.name as n,t.age as a from teacher t;
+-----+----+
| n   | a  |
+-----+----+
| zls | 29 |
| wls | 30 |
+-----+----+
2 rows in set (0.00 sec)

as可以不加，用空格隔开也表示起别名

2.添加组外界依赖

表中某列依赖于另一张表得某列，须在创建表时增加这一行

constraint FK_gradeid foreign key(gradeid) references grade(gradeid)

删除外键，用修改表的方法

alter table subject drop foreign key FK_gradeid2;

3.更改表里的内容

update 表名 set 要修改内容的列名=要改的内容 where 判断条件（要改的行的定位）
如

update grade set subjectno=1 where gradeid=1;

在gradeid=1那一行将subjectno的值改成1

4.distinct关键字

去除重复

select  distinct studentno from result;

去除result表中studentno值相同的记录

5.select可以用表达式

select studentresult*0.7 from result;

在result表中查找studentresult的值并把值*0.7

练习：

select subjectname '课程名称',classhour '总课时',classhour/10 '均课时/天' from subject;

SELECT语法

SELECT [ALL|DISTINCT]
{ *|table.*|[ table.field1 [ as alias1][, table.field2 [as alias2]][, ...]] }FROM table_name [as table_alias]
[left|out|inner join table_name2] #联合查询
[ WHERE ...]#指定结果需满足的条件
[ GROUP BY ...]#指定结果按照哪几个字段来分组
[ HAVING ...]#过滤分组得记录必须满足得次要条件
[ ORDER BY...]#指定查询记录按一个或者多个条件排序
[ LIMIT { [ offset,] row_count | row_count OFFSET offset ]] ;#指定查询的记录从哪条至哪条

6.where检索

查询出考满分的学生信息

select * from result where studentresult=100;

查询科目1考100分的学生信息

select * from result where studentresult=100 and subjectno =1;

找出成绩在70一下和100分的学生信息

select * from result where studentresult <=70 or studentresult = 100;

找出科目1学生成绩不是100的学生信息

select * from result where subjectno=1 and studentresult != 100;
select * from result where subjectno=1 and studentresult not in (100);

查找不为空的信息

select * from teacher where name is not null;

模糊查询

%数学——表示以数学结尾
数学%——表示以数学开头
%数学%——表示含数学的

select * from subject where subjectname like '%数学%';

精确查询

_是精确查找，字符一一对应

select * from student where studentname like '李_';

in查询

查询学过1，2，3号科目的学生

select distinct from result where subjectno in(1,2,3);
select distinct studentno from result where subjectno in(1,2,3);

科目1为100或98的成绩的学生

select * from result where subjectno = 1 and studentresult in (98,100);
select * from result where subjectno = 1 and (studentresult = 98 or studentresult = 100);