图解MySQL数据库安装与实际操作
- WBOYOriginal
- 2016-06-07 16:13:45956browse
本文主要讲述的是图解MySQL数据库安装与实际操作的介绍， 你是否对获得图解MySQL数据库安装与实际实际操作感到十分头疼？如果是这样子的话，以下的文章将会给你相应的解决方案。 1.图解MySQL数据库安装和操作：初始的数据库 (和PHP搭配之最佳组合)数据库的安
本文主要讲述的是图解MySQL数据库安装与实际操作的介绍， 你是否对获得图解MySQL数据库安装与实际实际操作感到十分头疼？如果是这样子的话，以下的文章将会给你相应的解决方案。
1.图解MySQL数据库安装和操作：初始的数据库
(和PHP搭配之最佳组合)数据库的安装和操作
width="505" height="420" />
b.直接表示：select name '姓名' from students order by age
2.精确查找:
a.用in限定范围：select * from students where native in ('湖南', '四川')
b.between...and：select * from students where age between 20 and 30
c.“=”：select * from students where name = '李山'
d.like:select * from students where name like '李%' (注意查询条件中有“%”，则说明是部分匹配，而且还有先后信息在里面，即查找以“李”开头的匹配项。所以若查询有“李”的所有对象，应该命令：'%李%';若是第二个字为李，则应为'_李%'或'_李'或'_李_'。)
e.[]匹配检查符：select * from courses where cno like '[AC]%' (表示或的关系，与"in(...)"类似，而且"[]"可以表示范围，如：select * from courses where cno like '[A-C]%')
3.对于时间类型变量的处理
a.smalldatetime：直接按照字符串处理的方式进行处理，例如：
select * from students where birth > = '1980-1-1' and birth
4.图解MySQL数据库安装和操作.集函数
a.count()求和，如：select count(*) from students (求学生总人数)
b.avg(列)求平均，如：select avg(mark) from grades where cno=’B2’
c.max(列)和min(列)，求最大与最小
5.分组group
常用于统计时，如分组查总数：
<ol class="dp-xml"> <li class="alt"><span><span>select gender,count(sno) </span></span></li> <li><span>from students </span></li> <li class="alt"><span>group by gender </span></li> </ol>
(查看男女学生各有多少)
注意：从哪种角度分组就从哪列"group by"
对于多重分组，只需将分组规则罗列。比如查询各届各专业的男女同学人数 ，那么分组规则有：届别(grade)、专业(mno)和性别(gender)，所以有"group by grade, mno, gender"
<ol class="dp-xml"> <li class="alt"><span><span>select grade, mno, gender, count(*) </span></span></li> <li><span>from students </span></li> <li class="alt"><span>group by grade, mno, gender </span></li> </ol>
通常group还和having联用，比如查询1门课以上不及格的学生，则按学号(sno)分类有：
<ol class="dp-xml"> <li class="alt"><span><span>select sno,count(*) from grades </span></span></li> <li> <span>where mark</span><span class="tag"><span class="tag-name">60</span><span> </span></span> </li> <li class="alt"><span>group by sno </span></li> <li> <span>having count(*)</span><span class="tag">></span><span>1 </span> </li> </ol>
6.UNION联合
合并查询结果，如：
<ol class="dp-xml"> <li class="alt"><span><span>SELECT * FROM students </span></span></li> <li><span>WHERE name like ‘张%’ </span></li> <li class="alt"><span>UNION [ALL] </span></li> <li><span>SELECT * FROM students </span></li> <li class="alt"><span>WHERE name like ‘李%’ </span></li> </ol>
7.图解MySQL数据库安装和操作.多表查询
a.内连接
<ol class="dp-xml"> <li class="alt"><span><span>select g.sno,s.name,c.coursename </span></span></li> <li> <span>from grades g JOIN students s ON </span><span class="attribute">g.sno</span><span>=s.sno </span> </li> <li class="alt"> <span>JOIN courses c ON </span><span class="attribute">g.cno</span><span>=c.cno </span> </li> </ol>
(注意可以引用别名)
b.外连接
b1.左连接
<ol class="dp-xml"> <li class="alt"><span><span>select courses.cno,max(coursename),count(sno) </span></span></li> <li> <span>from courses LEFT JOIN grades ON </span><span class="attribute">courses.cno</span><span>=</span><span class="attribute-value">grades</span><span>.cno </span> </li> <li class="alt"><span>group by courses.cno </span></li> </ol>
左连接特点：显示全部左边表中的所有项目，即使其中有些项中的数据未填写完全。
左外连接返回那些存在于左表而右表中却没有的行，再加上内连接的行。
b2.右连接
与左连接类似
b3.全连接
<ol class="dp-xml"> <li class="alt"><span><span>select sno,name,major </span></span></li> <li> <span>from students FULL JOIN majors ON </span><span class="attribute">students.mno</span><span>=</span><span class="attribute-value">majors</span><span>.mno </span> </li> </ol>
两边表中的内容全部显示
c.自身连接
<ol class="dp-xml"> <li class="alt"><span><span>select c1.cno,c1.coursename,c1.pno,c2.coursename </span></span></li> <li> <span>from courses c1,courses c2 where </span><span class="attribute">c1.pno</span><span>=</span><span class="attribute-value">c2</span><span>.cno </span> </li> </ol>
采用别名解决问题。
d.交叉连接
<ol class="dp-xml"><li class="alt"><span><span>select lastname+firstname from lastname CROSS JOIN firstanme </span></span></li></ol>
相当于做笛卡儿积
8.嵌套查询
a.用关键字IN,如查询李山的同乡：
<ol class="dp-xml"> <li class="alt"><span><span>select * from students </span></span></li> <li> <span>where native in (select native from students where </span><span class="attribute">name</span><span>=’ 李山’) </span> </li> </ol>
b.使用关键字EXIST,比如，下面两句是等价的：
<ol class="dp-xml"> <li class="alt"><span><span>select * from students </span></span></li> <li> <span>where sno in (select sno from grades where </span><span class="attribute">cno</span><span>=’B2’) </span> </li> <li class="alt"><span>select * from students where exists </span></li> <li><span>(select * from grades where </span></li> <li class="alt"> <span class="attribute">grades.sno</span><span>=</span><span class="attribute-value">students</span><span>.sno AND </span><span class="attribute">cno</span><span>=’B2’) </span> </li> </ol>
9.关于排序order
a.对于排序order，有两种方法：asc升序和desc降序
b.对于排序order,可以按照查询条件中的某项排列，而且这项可用数字表示，如：
<ol class="dp-xml"> <li class="alt"><span><span>select sno,count(*) ,avg(mark) from grades </span></span></li> <li><span>group by sno </span></li> <li class="alt"> <span>having avg(mark)</span><span class="tag">></span><span>85 </span> </li> <li><span>order by 3 </span></li> </ol>
10.图解MySQL数据库安装和操作.其他
a.对于有空格的识别名称，应该用"[]"括住。
b.对于某列中没有数据的特定查询可以用null判断，如select sno,courseno from grades where mark IS NULL
c.注意区分在嵌套查询中使用的any与all的区别，any相当于逻辑运算“||”而all则相当于逻辑运算“&&”
d.注意在做否定意义的查询是小心进入陷阱：
如，没有选修‘B2’课程的学生 ：
<ol class="dp-xml"> <li class="alt"><span><span>select students.* </span></span></li> <li><span>from students, grades </span></li> <li class="alt"> <span>where </span><span class="attribute">students.sno</span><span>=</span><span class="attribute-value">grades</span><span>.sno </span> </li> <li> <span>AND grades.cno </span><span class="tag"><span class="tag">></span><span> ’B2’ </span></span> </li> </ol>
上面的查询方式是错误的，正确方式见下方：
<ol class="dp-xml"> <li class="alt"><span><span>select * from students </span></span></li> <li><span>where not exists (select * from grades </span></li> <li class="alt"> <span>where </span><span class="attribute">grades.sno</span><span>=</span><span class="attribute-value">students</span><span>.sno AND </span><span class="attribute">cno</span><span>=</span><span class="attribute-value">'B2'</span><span>) </span> </li> </ol>
11.关于有难度多重嵌套查询的解决思想：
如，选修了全部课程的学生：
<ol class="dp-xml"> <li class="alt"><span><span>select * </span></span></li> <li><span>from students </span></li> <li class="alt"><span>where not exists ( select * </span></li> <li><span>from courses </span></li> <li class="alt"><span>where NOT EXISTS </span></li> <li><span>(select * </span></li> <li class="alt"><span>from grades </span></li> <li> <span>where </span><span class="attribute">sno</span><span>=</span><span class="attribute-value">students</span><span>.sno </span> </li> <li class="alt"> <span>AND </span><span class="attribute">cno</span><span>=</span><span class="attribute-value">courses</span><span>.cno)) </span> </li> </ol>
最外一重：从学生表中选，排除那些有课没选的。用not exist。由于讨论对象是课程，所以第二重查询从course表中找，排除那些选了课的即可。