mysql命令

mysql命令 一、查询 表 关于同志近三年现实表现材料材料类招标技术评分表图表与交易pdf视力表打印pdf用图表说话 pdf 内容: select * from `tablename` select `nicheng` from `tablename` select distinct `nicheng` from `tablename` 二、where查询: select `id` from `tablename` where `id`>10 or (`id`<5) 三、in用法: select * from `tablename` where `nicheng` in ('萧明', '秦城') 四、between用法: select * from `tablename` where `id` between '003' and '009' 五、排序查询(ASC由小往大/DESC由大往小): select id , mima from `tablename` where `nicheng`='萧明' order by `id` ASC limit 0 , 30 六、查询表中数据项数: select count(*) from `tablename` 七、查询字段中内容数量: select `nicheng`, count(*) from `tablename` group by `nicheng` 模糊查询 SQL的模式匹配允许使用“_”匹配任何单个字符，而 “%”匹配任意数目字符(包括零个字符): 萧”开头的数据: 一、查询以“ select * from `tablename` where `nicheng` like "萧%" 二、查询以“城”结尾的数据: select * from `tablename` where `nicheng` like "%城" 三、查询包含一个“敬”的数据: select * from `tablename` where `nicheng` like "%敬%" 四、查询正好包含3个字符的名字，使用“_”模式字符: select * from `tablename` where `nicheng` like "___" 正则表达式查询: 五、查询以z或Z开头的数据: select * from `tablename` where `nicheng` regexp "^[zZ]" select * from `tablename` where `nicheng` regexp "^[a-zA-Z]" 六、查询以g或G结尾的数据: select * from `tablename` where `nicheng` regexp "g$" 多表查询: 一、多表查询: $sql=mysql_query("select * from `tb_student`,`tb_sscore` where tb_=tb_ and tb_=$sno",$conn); 二、表的别名查询统计: $sql=mysql_query("select * from `tb_student` as tb_s,`tb_sscore` as tb_c where tb_=tb_ and tb_=$sno",$conn); 三、合并结果集: $sql=mysql_query("select `userid`,`username`,`sex`,`age` from `tb_worker1` union select `ygid`,`name`,`sex`,`age` from `tb_worker2`",$conn); 联合查询: 一、多表联合查询: $sql=mysql_query(" select * from `tablename` where `nicheng` like "%敬%" union select * from `info` where `nicheng` like "张%"",$conn); 二、联合查询结果排序: $sql=mysql_query("select * from `tb_1` union all select * from `tb_2` order by `age` desc",$conn); 三、条件联合查询: $sql=mysql_query("select `pubname` from `tb_pub` group by `pubname` having `pubname`='人民邮电出版社' or `pubname`='机械工业出版社' union all select `pubname` from `tb_pub` group by `pubname` having `pubname`<>'人民邮电出版社' and `pubname`<>'机械工业 出版社'",$conn); SELECT t1 . * , , FROM uchome_tagspace t1 LEFT JOIN uchome_mtag t2 ON = WHERE =1 多表查询 一、简约嵌套查询: $sql=mysql_query("select * from `tb_student_score_info` where `sid` in (select `sid` from `tb_student_info` where `sid`=$sno)",$conn); 二、复杂嵌套查询: $sql=mysql_query("select * from `tb_laborage` where `name` in (select `name` from `tb_dept` where `name` in (select `name` from `tb_personnel` where `knowledge`='本科'))",$conn); 三、嵌套查询应用: $sql=mysql_query("select * from `tb_laborage` where `jbgz` ".$_POST[tj]." any(select `jbgz` from `tb_laborage` where name='".$_POST[name]."')",$conn); 四、IN子查询: 1、限定范围查询: $sql=mysql_query("select * from `tb_student_score` where `sid` in (select `sid` from `tb_student_score` where `sid` between $from and $to) ",$conn); 2、查询记录信息: $sql=mysql_query("select * from `tb_bookinfo` where `bookname` in (select `bookname` from `tb_bookinfo` where `bookname` like '%$bookname%') ",$conn); 排序分组 一、对数据进行降序查询: $sql=mysql_query("select * from `tb_sp` order by `sl` desc",$conn); 二、对数据进行多条件排序查询: $sql=mysql_query("select * from `tb_goods` order by `sl` desc,dj asc",$conn); 三、对统计结果进行排序: $sql=mysql_query("select *,sum(num) as totalnum from `tb_bookinfo` group by `bookname` order by `totalnum` desc",$conn); 四、单列数据分组统计: $sql=mysql_query("select *,sum(num) as totalnum from `tb_bookinfo` group by `bookname` order by `totalnum` desc",$conn); 五、多列数据分组统计: $sql=mysql_query("select *,sum(xcsl) as xc,sum(x ssl) as xs from `tb_book1` group by `bookname` ",$conn); 六、多表分组统计: $sql=mysql_query("select *,sum(tb_xs.x ssl) as xsl,sum(tb_) as kcl from `tb_bk`,`tb_xs` where tb_=tb_ group by `bookname` ",$conn); 聚集函数 一、汇总统计查询: $sql=mysql_query("select sum(yy) as sumyy,sum(yw) as sumyw,sum(sx) as sumsx,sum(ls) as sumls from `tb_student_score` ",$conn); 二、均值统计查询: $sql=mysql_query("select avg(yy) as avgyy,avg(yw) as avgyw,avg(sx) as avgsx,avg(ls) as avgls from `tb_student_score` ",$conn); 三、最小值统计查询: $sql=mysql_query("select * from `tb_gemsell` where `outprice-inprice` in (select min(`outprice-inprice`) as minsell from `tb_gemsell`) ",$conn); 四、最大值统计查询: $sql=mysql_query("select * from `tb_sale` where `sale` in (select max(sale) from `tb_sale` where month(xdate)=$yue and year(xdate)=2005)",$conn); 五、统计大于某值记录: $sql=mysql_query("select count(*) as total from `tb_sale` where `sale`>$num",$conn);