sql复习
3大数据类型
字符串
varchar char
日期
data time year
数值
int float double
增删查改
增:insert into user(name,gender) values ("zhangsan","nan")
删:delete from user where name="zhangsan"
改: update user set sex="女" where name="张三"
查:
select * from user
select * from user where name="张三" //where查询
select * from user order by id asc //根据id排序 order by 升序asc 降序 desc
select * from user group by id //跟据id分组
select distinct * from user where name="张三" //去重查询distinct
表关联查询
内连接 inner join ... on
select * from user u1 inner join user2 u2 on u1.id=u2.id
左连接 left join ... on 以左表为基础,会将左表的所有字段信息列出,而右表只列出与on后条件符合的部分信息
select * from user u1 left join user2 u2 on u1.id=u2.id
右连接 right join ... on
select * from user u1 right join user2 u2 on u1.id=u2.id
全连接 左连接 union 右连接
子查询
单个值
SELECT * FROM studentcourse WHERE id >(SELECT id FROM student WHERE studentname="张无忌")
多个值
SELECT * FROM studentcourse WHERE id IN(SELECT id FROM student )
多个值作为中间值
SELECT * FROM (SELECT id,studentname FROM student) e //e是别名,如果不创建别名会报错
视图
创建视图:CREATE VIEW s_stu (编号,姓名) AS SELECT id,studentname FROM student WITH CHECK OPTION
索引
普通索引
CREATE INDEX 字段 ON table(column(length))
复合索引
子主题 1
事务
#开启事务
BEGIN;
#插入语句
INSERT INTO ab VALUES('7'),(8)
#提交事务
COMMIT;
#回滚
ROLLBACK;
触发器
#触发器
CREATE TRIGGER ins_stu
AFTER INSERT ON student FOR EACH ROW
INSERT INTO cj(NUMBER,stu_id,stu_name,math,chinese,english)
VALUES(1,new.stuid,new.username,87,98,78)
#执行触发
INSERT INTO student (username,PASSWORD,birthday)
VALUES('张三','111111','2016-08-23')
存储过程
#创建存储过程
DELIMITER $
CREATE PROCEDURE testb()
BEGIN
#声明变量类型
DECLARE un VARCHAR(32) DEFAULT '';
#给username变量赋值
SET un='xiaoxaio';
#将查询结果赋值给un变量
SELECT username INTO un FROM student WHERE stuid=3;
#查询un变量,返回
SELECT un;
END $
DELIMITER ;
#调用存储过程
CALL testb()
查询优化
避免的情况 != , < > ,is null , in not null, in , not in
消除顺序读取,使用索引
模糊查询where like,字母打头‘l%’会使用索引,非字母打头‘%l‘不会使用索引
避免使用or
#尽量少用in , not in 使用between
避免索引字段进行函数操作
复合索引,必须使用第一个字段