#姓李的同学,但名字只有一个字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作为转义符SELECT studentno,studentname FROM student
WHERE studentname LIKE'%:%%'ESCAPE':'
2) IN (集合)
SELECT studentno,studentname FROM student
WHERE studentno IN(1000,1001,1002);
3) NULL 空
#查询出生日期没有填写的同学 =null 是错误的 和null比较必须写is nullSELECT studentno,studentname FROM student
WHERE borndate ISNULL# 查询出生日期没有填写的同学SELECT studentno,studentname FROM student
WHERE borndate ISNOTNULL
4)BETWEEN 值1 AND 值2:在两个值之间
SELECT studentno, studentresult FROM result
WHERE studentresult BETWEEN95AND100
6.连接查询
(1)内连接 INNER JOIN(同等值连接)
SELECT student.studentno,studentname,result.studentno,studentresult
FROM student AS s
INNERJOIN result AS r
ON r.studentno=s.studentno
(2)外连接 OUTER JOIN
1)左外连接 LEFT [OUTER] JOIN 以左表为基准,以左表来一一匹配,匹配不上,返回左表纪录,右表以null补充
SELECT student.studentno,studentname,result.studentno,studentresult
FROM student AS s
LEFTJOIN result AS r
ON r.studentno=s.studentno
2)右外连接 RIGHT [OUTER] JOIN 以右表为基准,以右表来一一匹配,匹配不上,返回右表纪录,左表以null补充
SELECT student.studentno,studentname,result.studentno,studentresult
FROM student AS s
RIGHTJOIN result AS r
ON r.studentno=s.studentno
3)三种连接的关系
INNER JOIN 内连接:返回两个表的交集
LEFT JOIN 左连接:返回左表所有行
RIGHT JOIN 右连接:返回右表所有行
(3)等值连接(同内连接)
SELECT student.studentno,studentname,result.studentno,studentresult
FROM student AS s,result AS r
WHERE r.studentno=s.studentno
(4)非等值连接(返回的行数,两个表的行数相乘)
SELECT student.studentno,studentname,result.studentno,studentresult
FROM student AS s,result AS r
(5)自连接: 自己关联自己
CREATETABLEIFNOTEXISTS category(
categoryId INT(10)AUTO_INCREMENTPRIMARYKEY,
categoryName VARCHAR(32)NOTNULL,
pid INT(10));INSERTINTO category(categoryName,pid)VALUES('软件开发',0),('美术设计',0),('数据库基础',1),('photoshop基础',2),('色彩搭配学',2),('php基础',1),('一起学python',1);SELECT a.categoryName AS'父栏目名称',b.categoryName AS'子栏目名称'FROM category a JOIN category b
ON b.pid=a.categoryId WHERE a.pid=0;
(6)全连接
左外连接
UNION
右外连接
UNION :联合并去重
UNION all:联合不去重
7.使用子查询:由里到外
SELECTSELECT stu_id r.sub_no,r.score
FROM result r WHERE stu_id=(SELECT SubjectNo FROM`subject`WHERE SubjectName='高等数学-2')AND score>80
8. ORDER BY 排序查询
对SELECT语句查询得到的结果,按某些字段进行排序
与DESC或ASC搭配使用,默认为ASC
ORDER BY score DESC,studentno#降序
SELECT SubjectName AS 课程编号,AVG(score)AS 平均分
FROM result r
INNERJOIN`subject` s
ON s.SubjectNo=r.sub_no
JOIN grade g
ON g.gradeid=s.`GradeID`GROUPBY sub_no
ORDERBY 平均分 DESC
11. HAVING 过滤分组的记录必须满足的次要条件
SELECT s.sid,AVG(scores)AS 平均分
FROM tb_student s
JOIN tb_score
ON s.sid=stu_id
GROUPBY stu_id
HAVING 平均分>60
SHOW VARIABLES LIKE'event Scheuler';SETGLOBAL Scheduler=ON;#1.关闭mysql的自动提交,默认自动提交SET autocommit=0;#关闭#2.开始事务,STARTTRANSACTION;#3.事务#4.提交事务COMMIT;#5.回滚,数据回到本次事务的初始状态ROLLBACK;#6.还原mysql数据库的自动提交SET autocommit=1;#开启
示例:
#1.建表CREATETABLE account(
id INT(4)PRIMARYKEYAUTO_INCREMENT,
NAME VARCHAR(32)NOTNULL,
cash DECIMAL(9,2)NOTNULL);#2.填数据INSERT account(`name`,cash)VALUES('A',2000),('B',10000.00);#3.事务SET autocommit=0;#关闭mysql的自动提交STARTTRANSACTION;#开始事务UPDATE account SET cash=cash-500WHERE NAME='A';UPDATE account SET cash=cash+500WHERE NAME='B';#commit;#提交ROLLBACK;#回滚SET autocommit=1;
USE MySchool;#导出SELECT studnetno,studentname
INTOOUTFILE'路径'FROM student
#导入USE test
CREATETABLE stutab(
id INT(4),
sname VARCHAR(20))LOADDATAINFILE'路径'INTOTABLE stutab(id,sname)--skip-ADD-DROP-TABLE #跳过建表
注意:备份的文件不能提前存在
七.触发器
1.定义:触发器用来在某些操作时,“自动”执行一些操作。
2.语法:
CREATETRIGGER 触发器名
BEFORE|AFTER 事件
ON 表名
FOR EACH ROW
触发器语句;
#定义一个触发器,当修改account后相应在account_history插入一条相应语句CREATETRIGGER trig_account_his
AFTERUPDATEON account
FOR EACH ROWINSERTINTO account_history (account_name,changed_cash)VALUES('A',10000);UPDATE account SET cash =cash+10000WHERE NAME='A';INSERT:new.DELETE:old.
1)insert:新增记录,没有旧纪录;
CREATETRIGGER trigger_a_insert
AFTERINSERTON account
FOR EACH ROWINSERTINTO account_history(account_name,changed_cash)VALUES(new.name,new.cash);INSERTINTO account(NAME,cash)VALUES('abc',100);
2) delete:删除旧纪录,没有新纪录;
CREATETRIGGER trigger_a_delete
AFTERDELETEON account
FOR EACH ROWINSERTINTO account_history(account_name,changed_cash)VALUES(old.name,old.cash*-1);DELETEFROM account WHERE id=8