Mysql的常⽤搜索语句
MySQL常⽤查询
单表查询
①查询所有 *
css样式居中怎么写mysql> select * from student;
②查询选中字段记录
mysql> select s_name from student;
③条件查询 where
mysql> select s_name from student where s_id<5;
connect连瞳韩剧在线观看④查询后为字段重命名 as
mysql> select s_name as 名字 from student;
⑤模糊查询 like
%匹配多个字符
mysql> select s_name as 姓名 from student where s_name like ‘李%’;
匹配⼀个字符
mysql> select s_name as 姓名 from student where s_name like '李’;
制作安装linux系统的iso镜像
mysql> select s_name as 姓名 from student where s_name like ‘李__’;
表单验证属性
⑥排序（默认升序） order by 以某个字段为主进⾏排序
升序 asc （asc可以不写）
mysql> select * from student order by sc_id asc;
降序 desc
mysql> select * from student order by sc_id desc;
⑦限制显⽰数据数量 limit
limit 只接⼀个数字n时表⽰显⽰前⾯n⾏
mysql语句多表查询
mysql> select * from student limit 5;
limit 接两个数字m，n时表⽰显⽰第m⾏之后的n⾏
mysql> select * from student limit 2,4;
⑧常⽤聚合函数
mysql> select * from details;
最⼤值 max
mysql> select max(age) from details;
最⼩值 min
mysql> select min(age) from details;
求和 sum
如何利用函数指针实现跳转mysql> select sum(age) from details;
平均值 avg
mysql> select avg(age) from details;
四舍五⼊ round
mysql> select round(avg(age)) from details;
统计 count
mysql> select count(address) from details;
⑨分组查询 group by 筛选条件使⽤having，having后接条件必须是select后存在的字段
mysql> select age,count(age) from details group by age having age>30;
以age为组统计每个age的⼈数最后筛选出age⼤于30的
2、⼦查询 也叫嵌套查询
mysql> select * from details where age>(select avg(age) from details);
查询所有age⼤于平均年龄的信息
3、关联查询
①内连接 inner join
⽆条件内连接 ⼜称笛卡尔连接
mysql> select * from student inner join college;
有条件内连接 在⽆条件基础上on接条件
mysql> select * from student inner join college on sc_id=c_id;
②外连接
左外连接 left join
以左表为基准，右表没有对应数据以null填充，多余数据去除
mysql> select * from tb1 left join tb2 on id=t_id;
mysql> select * from tb2 left join tb1 on id=t_id;
右外连接 right join
以右表为基准，左表没有对应数据以null填充，多余数据去除
mysql> select * from tb1 right join tb2 on id=t_id;
mysql> select * from tb2 right join tb1 on id=t_id;
派⽣表必须命名 as
mysql> select * from (select * from details where age>30) as a left join student on d_id=s_id;
mysql> select 作者名 from 作者
-> where 作者号 in
-
> (select 作者号 from 图书
-> where 名称=‘数据库系统原理’ and 出版社号 in
-> (select 出版社号 from 出版社
-> where 出版社号=‘RMYD’));

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。