show databases;
create database db01;
use db01;
show tables;
create table person(name varchar(9));
drop table person;
drop database db01;
insert Into person(age,id) VALUES(33,7);
update person set name='abc', age=15 where id=1;
delete from person WHERE id = 8;
select name, age, sex from person;
> >= < <= = !=
select * from person where age >= 15 and age <= 22 and sex='men';
select * from person where age = 15 or age = 18 or name='hello';
select * from person where age not between 15 and 22;
select * from person where name in('lili','lilei','hanmeimei');
select * from person where name like 'abc%';
select * from person where age is not null;
select sex,max(age),min(age),sum(age),avg(age),count(*) from person group by sex;
select sex s, count(*) c from person where name != 'abc' group by sex having c >= 2;
select * from person order by age desc;
select distinct age from person;
select count(distinct age) from person;
select * from person order by age desc limit 1;
select * from person limit (x-1)*y, y;
select c.name cn, e.name en, e.age from com c, emp e where c.id = e.comid;
select * from com left join emp on com.id = emp.comid;
select * from emp right join com on com.id = emp.comid;
select * from com inner join emp on com.id = emp.comid;
select (select name from com where id = comid) 公司名 from emp where name = '张三';
select name from com where id = (select comid from emp where name = '王五');
select com.name from (select comid cid from emp where name = '张三') t1
left join com on t1.cid = com.id;
select * from t1
union all
select * from t2
select * from emp where name = '张三'
union
select * from emp where name = '李四'
create view com_emp_view as
select com.id cid, com.name cname, emp.id eid, emp.name ename, emp.age
from com, emp where com.id = emp.comid;
select * from com_emp_view;
drop view com_emp_view;
create table bank(
id int(10) primary key,
name varchar(10) not null,
money int(10) not null);
insert into bank values(1,'张三', 300);
insert into bank values(2,'李四', 500);
select * from bank;
show variables like '%autocommit%';
set autocommit = 0;
update bank set money = money - 100 where name = '张三';
update bank set money = money + 100 where name = '李四';
select name,
(case when id in (select fid from t5 where month(m)='04' ) then '√' else '×' end) 4月,
(case when id in (select fid from t5 where month(m)='05' ) then '√' else '×' end) 5月,
(case when id in (select fid from t5 where month(m)='06' ) then '√' else '×' end) 6月
from t4;