mysql 多表联合视图_MySQL之单表、多表查询、连接、视图

select 查询类型:单表查询、多表查询，子查询

select * from tb_name; 查询表中所有字段

select field1,field2 from tb_name; 投影，只显示每个实体中的field1、field2

select * from tb_name where 条件； where进行条件查询

例：

>select name ,age from students;

>select name,age from students where age >10;

>select name,age from students where gender='F';

还可以让相同的值只显示一次进行查询：

>select distinct gender from students; 这样最多显示两个结果M和F

from字句：要查询的关系 表、多个表及其他select语句

where字句：布尔关系式 > < = >= <= + - * and or not between...and...等

例：

>select name,age from students where age+1>20;

>select name from students where age>20 and gender='F';

>select name,age,gender from students where not age>20 and not gender='M';

查询年龄不大于20，且不为‘M’的name，age,gender从students表中

或者

>select name,age,gender from students where not(age>20 or gender='M');

>select name,age,gender from students where age>=20 and age<=25;

或者

>select name,age,gender from students where age between 20 and 25;

like '通配符'

%:表示任意长的任意字符

_:表示任意单个字符

例：

>select name,age,gender from students where name like 'y%'

查询所有以y开头的name，显示name,age,gender从students表中

>select name,age，gender from students where name like 'y____';

查询所有以y开头后面跟着四个字符的

>select name,age,gender from students where name like '%zhao%';

查询所有名称中包含zhao的

rlike '正则biaodashi'

>select name,age,gender from students where name rlike '^[znx