三大数据类型:字符串:char、varchar。数值:int、float。日期:date、time、datetime
增:insert into
删:delete from
改:update
查:基本查询:select * from 表名;
查询字句:where:select * from u where
排序:select * from u order by id;
select * from u order by id desc;
分组:select * from u group by id;
去重:select distinct name form u;
内连接:select * from web w inner join web2 w2 on w.id=w2.id;
左连接:select * from web w left join web2 w2 on w.id=w2.id;
右连接:select * from web w right join web2 w2 on w.id=w2.id;
全连接:左连接 union 右连接
select * from web w left join web2 w2 on w.id=w2.id
union
select * from web w right join web2 w2 on w.id=w2.id;
三表关联查询:
select st.*,sc.*,su.* from stu st
inner join score sc on st.no=sc.no
left join sub su on sc.subno=su.subno
where st.no>0;
子查询,单值
select * from emp where salary > (select salary from emp where emp_name=”王强”);查询工资大于王强的数据
子查询,多值
select * from emp where dept_id = (select dept_id from emp where emp_name=”张建国”)
and salary > (select salary from emp where emp_name = “孙岩”);
子查询,中间表
select * from (select * from emp where dept_id > 2 and salary > 5000) e;
创建视图:逻辑上的虚拟表
create view v_auther(编号,姓名)
as
select * from auther
with check option;
查询视图和查询表一样
创建索引
create index index_name on emp(emp_name); 在emp表的emp_name字段创建索引,索引名为index_name
添加unique说明它的值不能重复
单独创建唯一索引
create unique index index_name on emp(emp_name);
修改表创建唯一索引
alter table article add unique index index_title(title);
开启事务
begin;
插入语句
insert into runoob_transation_test(id)value(‘5’);
insert into runoob_transation_test(id)value(6);
提交事务 提交后不能回滚
commit;
回滚
rollback;
查询
select * from runoob_transation_test;
触发器1 将student表的stuid和username字段插入到cj表的相对应字段中(下同)
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,88,77,99)
触发器2
delimiter $
create trigger ins_stu
after insert on student for each row
begin
insert into cj(number,stu_id,stu_name,math,chinese,english)
values(1,new.stuid,new.username,88,77,99);
end $
delimiter ;
执行触发过程 这里的stuid和username的字段值会插入到cj表
insert into student(username,password,birthday)values
(‘张三’,’22222’,’2016-08-23’);
创建存储过程 两张表会显示两个结果
delimiter $
create procedure testa()
begin
select * from student;
select * from cj;
end $
delimiter ;
1.避免使用in或not in,因为是不连续的范围,不会使用索引,可以使用between ... and ...
select * from emp where age in(100,150,200);
select * from emp where age between 100 and 200;
2.尽量不使用select into,因为会导致表锁定,其他用户就不能使用此表
3.未使用索引
select * from T1 where nextprocess = 1 and processed in (8,32,45);
强制使用索引
select * from T1 force index(IX_ProcessID)where nextprocess = 1 and processed in (8,32,45);
4. 模糊查询where like,母打头'l%'可以使用索引,非字母打头'%l%'不会使用索引
不会使用索引,会进行全表扫描的情况
select * from T2 where name like ’%L%’;
select * from T2 where substring(name,2,1)=’L’; -- 截取name从第二个字符开始截取一个字符
会使用索引的情况
select * from T1 where name like ‘L%’;
5. 尽量不要修改主键字段
6. 当修改varchar型字段时,尽量使用相同长度内容的值代替
7. 尽量最小化对于含有update触发器的表的update操作
8. 避免update将要复制到其他数据库的列
9. 避免update建有很多索引的列 修改值索引就要重新排序 尽量不修改带有索引的字段
10.避免update在where字句条件中的列 例如:update a set name='n' where name = 'a'