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 )
多个值作为中间值
视图
直接创建
CREATE VIEW <视图名> AS
索引
普通索引
create index index_user_name on PROMULGATE_CERT(user_name);
复合索引
create index idx_d on employees(first_name,birth_date,hire_date);
事务
开启，提交，回滚
start transaction ；开启一个事务
update account set money=money-100 where `name`='A'
update account set money=money+100 where `name`='B'
commit ;提交事务，事务一旦提交就不能回滚了
rollback;回滚
set autocommit=1；
触发器
插入语句触发
CREATE TRIGGER trig_student_After
ON student
FOR UPDATE
AS
PRINT 'THE TRIGGER IS AFTER'
SELECT * FROM student
存储过程
–创建存储过程
create proc 存储过程名字
as
语句
–修改存储过程
alter proc 存储过程名字
as
语句
–卸载存储过程
drop proc 存储过程名字
–调用存储过程
exec 存储过程名称
