【MySQL数据库基础三】数据查询语句

Select查询语句

查询语句常用关键字select，有三点常用功能：单表查询，多表连接查询，子查询，接下来的所有介绍都是对此表做操作

//主表，两个字段，id(主键自增)和name（非空） create table tml ( tml_id int auto_increament, tml_name varchar(255) not null, primary key(tml_id) ); //从表，3个字段，id(主键自增)和name以及java_tml(参考主表name的外键) creat table student ( stu_id int auto_increment primary key, stu_name varchar(255), #指定java_tml 参照到tml_id列 java_tml int , constraint student_tml_fk foreign key (java_tml) references tml_table (tml_id) //为外键约束指定名称 ); 对此表进行操作

单表查询语句

用到的关键字如下：SQL的运算符、concat、as

#执行单表查询 select student_name //选择列，如果全部选择，可以用*来代替 from student_table //选择的数据表 where java_tml>3 //对行设置条件 #可以在select语句中使用算数运算符（某种意义上说数据列也是一种变量） select * from student_table where student_id*3>4; #使用concat函数进行字符串连接运算 select concat(tml_name,'xyz') from tml_table; #对于mysql而言，无论是算数表达式中用null，还是连接运算中用null都会导致结果为null select concat(tml_name,null) from tml_table; //返回结果为null #为数据列或者表达式起个别名 select tml_id+5 as MY_ID from tml_table； #选出新的列（6，7，8….）并且为其取一个别名MY_ID select tml_id+5 "MY_ID" from tml_table; #为多列以及表起别名 select tml_id+5 MY_ID, tml_name //为多列起别名 from tml_table t; //为表起别名 #用distinct除去多列组合的重复值 select distinct tml_id, tml_name from tml_table; //只有其组合的全部值都相同时（1，tianmaolin）,(1,tianmaolin )，这里是且的关系

多表查询语句

包括SQL92规范的等值连接，非等值连接，外连接，广义笛卡尔积以及SQL99规范的，交叉连接，自然链接，using子句链接，on子句连接，全外链接或者左右外连接, 用到的关键字如下：cross join、right join、left join、full join、inner join、nature join、on、using

等值连接

select s.*，teacher_name //查询所有学生的资料和对应老师的姓名 from student_table s,teacher_table t //指明来源表并且给表起别名 where s.java_teacher=t.teacher_id and student_name is not null //指明连接条件

非等值连接

select s.*，teacher_name //查询所有学生的资料和对应老师的姓名 from student_table s,teacher_table t //指明来源表并且给表起别名 where s.java_teacher>t.teacher_id //指明连接条件

广义笛卡尔积

select s.*，teacher_name //查询所有学生的资料和对应老师的姓名 from student_table s,teacher_table t //指明来源表并且给表起别名

外连接

select s.*，teacher_name //查询所有学生的资料和对应老师的姓名 from student_table s,teacher_table t //指明来源表并且给表起别名 //右外链接，都返回满足条件的值，右外连接还返回右表中不符合连接条件查询的数据行，如果左边没有对应的则返回null值 where s.java_teacher=t.teacher_id （*）

交叉链接

select s.*，teacher_name //查询所有学生的资料和对应老师的姓名 from student_table s //from后只跟一个表名 cross join teacher_table t //相当于广义笛卡尔积

自然连接

select s.*，teacher_name //查询所有学生的资料和对应老师的姓名 from student_table s //from后只跟一个表名 nature join teacher_table t //将会把两个表中所有的同名列都选出来

using子句连接

select s.*，teacher_name //查询所有学生的资料和对应老师的姓名 from student_table s //from后只跟一个表名 join teacher_table t //这里也可以使用nature join代替join将会把两个表中所有的同名列都选出来 using （两个表中的同名列） //指明条件

on子句链接

select s.*，teacher_name //查询所有学生的资料和对应老师的姓名 from student_table s //from后只跟一个表名 join teacher_table t //这里也可以使用nature join代替join将会把两个表中所有的同名列都选出来 on s.java_teacher=t.teacher_id //on子句链接完全可以代替等值和非等值连接，条件任意

右外连接

select s.*，teacher_name //查询所有学生的资料和对应老师的姓名 from student_table s //from后只跟一个表名 //右外链接，都返回满足条件的值，右外连接还返回右表中不符合连接条件查询的数据行，如果左边没有对应的则返回null值 right join teacher_table t on s.java_teacher=t.teacher_id

全外连接

select s.*，teacher_name //查询所有学生的资料和对应老师的姓名 from student_table s //from后只跟一个表名 full join teacher_table t //全外连接将会把两个表中所有符合条件的记录全部列出 on s.java_teacher=t.teacher_id

子查询语句

把子查询当成数据表（临时视图），用到的关键字如下：in、any、all

select * from (select * from student_table) t where t.java_teacher>1

把子查询当成过滤条件

select * from student_table where java_teacher>(select teacher_id from teacher_table where teacher_name='yeeku') ;//返回的子查询值被当作标量使用 ******************************* select * from student_table where studetn_id in (select teacher_id from teacher_table) ;//将要返回一组值，然后与studetn_id比较，一样的被选出来 ******************************* select * from student_table where studetn_id >any (select teacher_id from teacher_table) ;//要求大于值列表中的最小值 ******************************* select * from student_table where studetn_id >all (select teacher_id from teacher_table) ;//要求大于值列表中的最大值

查询SQL运算符

查询中用到的关键字归纳如下：

赋值运算符，不是等号而是冒号等号（：=）比较运算符，有如下的一些关键字：between、in、like、is null.逻辑运算符，有如下的一些关键字：and、not、or排序关键字，order by，asc和desc

接下来详细介绍

比较运算符

关键字between，用于比较两个值之间，注意两个值都是大等于和小等于的关系

select * from student_table where student_id between 2 and 4; // (两个值不能相等，并且两个值都是大等于和小等于的关系)

取出student_id>=2和java_tml<=2的所有记录

select * from student_table where 2 between student_id and java_tml;

关键字in，选出括号里这两列值为2的所有记录，这里是或的关系

select * from student_table where 2 in (student_id，java_tml);

关键字like， like用于模糊查询,mysql中有两个常用通配符：下划线（_）和百分号（%），下划线代表一个字符，通配符代表多个字符，通常有以下语法的应用

#查询所有姓孙的同学 select *from student_table where student_name like '孙%'； #查询所有名字不是两个字符的所有学生 select * from student_table where not student_name like '__'; #要查询带有下划线和百分号通配符的数据通常使用escape关键字 select * from student_table where student_name like ‘\_%’ escape '\'; #使用like关键字和and（且）or（或）的组合，来指定多个条件的查找 //选出student_name 是两个字符并且student_id>3的所有记录 select * from student_table where student_name like '__' and student_id>3 ;

关键字is null，is null判断是否为空

select *from student_table where student_name is null; //将选择出表中所有为空的记录

逻辑运算符

按照not>and>or按照优先级排序，所有的比较运算符都比逻辑运算符优先

排序关键字

关键字：order by , desc(降序), asc(升序) 默认按照升序排列

强制依据java_teacher按照降序排列，当java_teacher相同时，依据student_name按照升序排序 select * from student_table order by java_teacher desc , student_name;

数据库函数

函数用来对数据进行复杂的操作，分为单行函数（对每行输入值单独计算，一行输入对一个输出），多行函数（多行同时计算，多行输入对一个输出，也称为聚集函数或分组函数）。

单行函数

用到的关键字如下：char_length 、sin 、curtime、curdate、now、MD5、ifnull、nullif、isnull、if 、case、when、then，单行函数有如下的特征

单行函数的参数可以是变量，常量或者数据列，每行可以接受多个参数，但只返回一个值。使用单行函数可以改变参数的数据类型。单行函数支持嵌套使用，即内层函数的返回值是外层函数的参数。

单行函数有如下几类:

日期时间函数，数值函数，字符函数。 选出tml_table表中tml_name列的字符长度 select char_length (tml_name) from tml_table; #计算tml_name列的字符长度的sin值 select sin (char_length (tml_name)) from tml_table; #计算1.57的sin值 select sin (1.57); select curtime(); 获取当前时间 select curdate(); 获取当前日期 select now(); 获取当前日期和时间 select date_add ( now(), interval 1 day/hour/minute/second/mirsecond/week/month/quarter/year) //为当前的时间添加时间 位函数，流程控制函数，加密解密函数，信息函数 select MD5（‘testing’） //MD5是加密函数 转换函数，主要用于完成数据类型的转化 处理null的函数 #如果student_name（expr1）这一列为null，则返回'没有名字'（expr2） select ifnull（student_name,'没有名字'） from student_table; #如果expr1=expr2,返回null否则返回expr1 select nullif（student_name,'张三'） from student_table; //如果student_name等于张三，则返回null否则返回expr1 #如果student_name列为null，则返回没有名字，否则返回有名字 select if (isnull(student_name), '没有名字'，'有名字') from student_table; //isnull(expr1)如果expr1为null则为true，如果为true则返回没有名字 流程控制函数，主要用于完成流程控制 select student_name, case java_teacher //按照老师的id编号返回对应的学生列表 case value when compare_value1 then result1 when compare_value2 then result2 else result end from student_table 如果value的值等于之后的value1则返回result1 的结果，之后的同理。

多行函数

用到的关键字如下：count、sum、distinct、max、min、avg、group by、having

#计算student_table表中的记录条数 select count(*) //*表示该表中记录的行数 from student_table; //注意*和distinct（不计算重复条数）不能同时使用 #计算java_teacher列中有多少个值 select count(distinct java_teacher) from student_table; #统计所有student_id的总和 select sum(student_id) from student_table; #选出表中student_id的最大值 select max（student_id) from student_table; #选出表中student_id的最小值 select min（student_id) from student_table; #avg计算时为了避免空值计算使用以下步骤 select avg（ifnull(java_teacher,0)) from student_table;

分组关键字group by

select * from student_table //选中所有数据 group by java_teacher //按照java_teacher值相同的进行分组 havaing count(*)>2; //选出记录条数大于2的分组

集合运算

实际上就是把多个select进行组合,两个结果集所包含的数据列数量和数据类型必须一致，用到的关键字如下：union、union all、not in、on

union并运算

DISTINCT删除结果集中重复的数据。默认情况下 UNION 操作符已经删除了重复数据，所以 DISTINCT 修饰符对结果没啥影响。ALL可选，返回所有结果集，包含重复数据

SELECT expression1, expression2, ... expression_n FROM tables [WHERE conditions] UNION [ALL | DISTINCT] SELECT expression1, expression2, ... expression_n FROM tables [WHERE conditions];

注意union和union all的区别

minus差运算

查找出所有学生表中减去与老师表中id，姓名相同的记录。in表示的是标量值相等，using表示同名字的列

select student_id ,student_name from student_table where ( student_id ,student_name) not in (select student_id,student_name from student_table); //

intersect交运算

查找出所有学生表中和老师表中记录相同的项

select student_id ,student_name from student_table join teacher_table on (student_id=teacher_id and student_name=teacher_name) (select student_id,student_name from student_table); where teacher_name like '李%' and student_id<4; //两个表各自的细分条件 on代表的是不同表之间的限定条件，where表示的是每个表各自的限定条件