《学习》3简单查询

-- 查看查询数据显示
SELECT * FROM student;

-- 显示一部分信息的查询
SELECT sname 姓名,sscore 成绩,saddress 家庭住址 FROM student;

-- 查询的基本条件 > < <= >= = != <>
SELECT * FROM student WHERE sscore<=80;
/*成绩不等于82的学生信息*/
SELECT * FROM student WHERE sscore!=82;
SELECT * FROM student WHERE sscore<>82;
SELECT * FROM student WHERE NOT sscore=82;

-- between...and.. 范围 包括两头 not不包括
SELECT * FROM student WHERE sscore BETWEEN 70 AND 82;
SELECT * FROM student WHERE sscore NOT BETWEEN 70 AND 82;
/*也可以用于查询日期*/
SELECT * FROM student WHERE sbirthday BETWEEN '1990-1-1' AND '2010-12-31';

-- 基本条件 like条件 % _（这两个符号只认like）
-- 查询姓名是两个字的学生信息两个 _
SELECT * FROM student WHERE sname LIKE '__';
/*姓张的学生信息*/
SELECT * FROM student WHERE sname LIKE '张_';
/*名字里有飞的学生信息*/
SELECT * FROM student WHERE sname LIKE '%飞%';
/*注意是= 这里的 % 不是like里面的含义*/
SELECT * FROM student WHERE sname = '%飞%';

/*删除姓李的学生信息*/
DELETE FROM student WHERE sname LIKE '%李%';

-- 基本条件 in() not in
SELECT * FROM student WHERE sno IN(201602);

SELECT * FROM student WHERE sno NOT IN(201602);

-- 基本条件 is null运算
SELECT * FROM student WHERE sscore IS NULL;
-- 将没有成绩的学生成绩修改为0
UPDATE student SET sscore=0 WHERE sscore IS NULL;

-- 名字为空跟名字没设置
SELECT * FROM student WHERE sname='';
SELECT * FROM student IS NULL;

*********************************************************
-- 跨库查询
USE mysql;
SELECT * FROM db.student;

SELECT sname,sage FROM db.student;

//查询几行或者从第几行到第几行
SELECT sname,sage FROM db.student LIMIT 3;

SELECT sname,sage FROM db.student LIMIT 2,4;