sql思维导图

三大数据类型：字符串：char、varchar。数值：int、float。日期：date、time、datetime、timestamp

增：insert into 表名(字段名1，字段名2)values(值1，值2);

删：delete from 表名 where 字段名=字段指定的值;

改：update user set gender=”女” where id=4;

查：基本查询：select * from 表名;

查询字句：where：select * from u where id=1;

排序： 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;

全连接：

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 s1.*,s2.*,su.* from stu s1

inner join score s2 on s1.no=s2.no

left join sub s3 on s2.subno=s3.subno

where st.no>0;

子查询，单值

select * from emp where salary > (select salary from emp where emp_name=”1”);

子查询，多值

select * from emp where dept_id = (select dept_id from emp where emp_name=”2”)

and salary > (select salary from emp where emp_name = “3”);

子查询，中间表

select * from (select * from emp where dept_id > 2 and salary > 5000) e;

创建视图：

create view v_auther(id,name)

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);

组合索引

create index index_ct on article(content,dt);

create table if not exists article(id int not null auto_increment,

title varchar(30) unique,

content varchar(50),

dt date,

primary key(id),

unique index index_title(title)

index index_cd(content,dt) 

)auto_increment=0 default charset=utf8;

1，2，3，4，5，回滚操作执行代码的顺序

开启事务#1

begin;

插入语句#2

insert into runoob_transation_test(id)value(‘5’);

insert into runoob_transation_test(id)value(6);

提交事务 提交后不能回滚

commit;

回滚#4

rollback;

查询#3 #5

select * from runoob_transation_test;