数据查询,模糊查询,连接查询
一.DQL(Data Query Language,数据查询语言)
语法:SELECT [ALL | DISTINCT]
{ * | table.* | [ table.field1 [ as alias1] [, table.field2 [as alias2]][, …]] }
FROM table_name [ as table_ alias ]
[ left|out|inner join table_name2 ] #联合查询
[ WHERE … ] #指定结果需满足的条件
[ GROUP BY …] #指定结果按照哪几个字段来分组
[ HAVING …] #过滤分组的记录必须满足的次要条件
[ ORDER BY… ] #指定查询记录按一个或者多个条件排序
[ LIMIT { [ offset,] row_count | row_count OFFSET offset }] ; #指定查询的记录从哪条至哪条
1.指定查询字段
(1).查询所有学生信息 *查询所有列,但效率低
SELECT * FROM student;
(2).查询指定列(学号,姓名)
SELECT studentno,studentNAME FROM student
2.AS子句
(1).可给数据列取一个新别名:
SELECT StudentNo AS “学号” FROM student;
(2).可给表取一个新别名:
SELECT a.StudentNo FROM student AS a;
(3).可把经计算或总结的结果用另外一个新名称来代替:
SELECT Phone+1 AS Tel FROM student;
3.distinct关键字
1.去掉SELECT查询返回的记录结果中重复的记录(所有返回列的值都相同),只返回一条
2.语法:
SELECT DISTINCT 字段名1, 字段名2... FROM 表名
4.where条件语句
作用:用于检索数据表中符合条件的记录
5.模糊查询between and/like/in/null
(1)like结合使用的通配符:
%(0到任何字符)
_一个字符
SELECT studentno,studentname
FROM student
WHERE studentname LIKE '李%';
#姓李的同学,但名字只有一个字
SELECT studentno,studentname
FROM student
WHERE studentname LIKE '李_';
#姓李的同学,但名字只有两个字
SELECT studentno,studentname
FROM student
WHERE studentname LIKE '李_';
#姓名中含有‘文’字
SELECT studentno,studentname
FROM student
WHERE studentname LIKE '%文%';
注:转义字符\不能使用自己的转义符:用escape作为转义符:like’%:%%‘escape’:’
#查询学员姓名中有‘李’这个字的同学的学号和姓名 转义符
SELECT studentno,studentname
FROM student
WHERE studentname LIKE '%李%';
#查询学员姓名中有‘%’这个字的同学的学号和姓名
SELECT studentno,studentname
FROM student
WHERE studentname LIKE '%\%%';
#转义符\能不能使用自己的转义符?(:):用escape作为转义符
SELECT studentno,studentname FROM student
WHERE studentname LIKE'%:%%' ESCAPE':'
(2) in()括号里面为集合
SELECT studentno,studentname FROM student
WHERE studentno IN (1000,1001,1002);
(3)null 空
is null
is not null
#查询出生日期没有填写的同学 =null 是错误的 和null比较必须写is null
SELECT studentno,studentname FROM student
WHERE borndate IS NULL
# 查询出生日期没有填写的同学
SELECT studentno,studentname FROM student
WHERE borndate IS NOT NULL
二.连接查询
1.内连接 inner join:两个表符合关联条件的数据进行展示
SELECT studentno,studentname ,gradename
FROM student AS s
INNER JOIN grade AS g
ON g.`gradeid`=s.`studentno`
2.外连接 outer join:
左外连接 left [outer] join 以左表为基准,以左表来一一匹配,匹配不上,返回左表纪录,右表以null补充;
SELECT studentno,studentname ,gradename
FROM student AS s
LEFT JOIN grade AS g
ON g.`gradeid`=s.`studentno`
右外连接 right [outer] join 以右表为基准,以右表来一一匹配,匹配不上,返回右表纪录,左表以null补充
SELECT studentno,studentname ,gradename
FROM student AS s
RIGHT JOIN grade AS g
ON g.`gradeid`=s.`studentno`
3.自连接:自己关联自己
SELECT a.categoryName AS'父栏目名称',b.categoryName AS '子栏目名称'
FROM category a,category b
WHERE b.pid=a.categoryId
4.等值连接同内连接
SELECT student.studentno,studentname,result.studentno,studentresult
FROM student AS s,result AS r
WHERE r.studentno=s.studentno
5.非等值连接
6.全连接
左外连接
UNION
右外连接
7.子查询
SELECT stu_id '李的学号',score '李的成绩' FROM result WHERE stu_id IN(SELECT studentno FROM student WHERE studentname LIKE '李%' )
CASE 字符 WHEN 值 THEN 操作 [ELSE 操作] end
UNION :联合并去重
UNION all:联合不去重
注:#删除表数据(不带where条件的delete)
#自增当前值依然从原来基础上进行
DELETE FROM test;
#删除表全部数据(truncate)
#自增值恢复到初始值重新开始
TRUNCATE TABLE test;
#重启数据库服务后
#INNODB,MYISAM同样适用delete from清空表数据,重启数据库服务后,
#对于innodb 的表,自增列从初始值重新开始
#而 MYISAM类型的表,自增列依然从上一个自增数据基础上开始
#启动 net start mysql
#停止服务:net stop mysql