1.插入
insert into <表名> [ ( <字段名1>[,..<字段名n > ])] values ( 值1 )[, ( 值n ) ]；
mysql中可以同时插多个值：
insert into dept values(2, 'dept2' , 'leader2'),(3, 'dept3' , 'leader3'),(4, 'dept4' , 'leader4');
insert into <表名> select ' ', ' ';
insert into stu select 10, ' mike ';
select last_insert_id(); --取自动增长列的值
2.删除
delete from 表名 where 表达式; 不加条件，默认删除所有数据
delete from stu ; --- 删除了所有的数据，但是下次插入会按上次的编号
truncate table stu; ---连编号也一起清空
删除dba部门的员工
delete from emp where deptID in (select deptID from dept where deptName = 'dba'); #只有一条数据也可用=
3.更新
子查询更新：
update stu set stuID=10 where deptID = ( select deptID from dept where deptName = 'computer');
不建议使用子查询
select deptID into @a from dept where deptName = ‘computer’;
update … where deptID = @a;
多表连接：
update stu s, dept d set salary = salary * 1.1 where s.deptID = d.deptID && d.deptName = 'computer';
4.查询
select <字段1，字段2，...> from < 表名 > where < 表达式 >;
mysql> select * from myclass where name='david';
mysql> select * from myclass limit 2; or 也可以用在前面top
mysql> select * from myclass order by id limit 2; ---查询表myclass中前2行数据
---别名的应用[as]，修改显示（可以用中文）
---条件查询：
select * from stu2 where stuAge>20 && deptID=1;
select * from stu2 where stuID in (1,3,10);
---模糊查看
%：任意字符，_单个字符，[ ]符号正则表达式
select * from user where u_name like '%三%'
---分组排序
select * from emp order by salary desc limit 3;
select * from emp order by salary limit 1, 3; -- 从第1条开始取3条, 从第0条开始算
---聚合函数与group by
select sum(salary), avg(salary), max(salary), min(salary), count(1) from emp;
select deptID, count(1) from emp group by deptID;
select deptID, count(1) as s from emp where age > 20 group by deptID having s > 2; ---二次筛选
aixocm $a = md5(aixocm) ---判断密码是否一致
select count(1) from user where userName = 'tom' && pwd='$a';
---多表查询
select empName, deptName from emp, dept where emp.deptID = dept.deptID;
select empName, deptName from emp inner join dept on emp.deptID = dept.deptID;
-- 学生表, 课程表, 选课表
select stuName, courseName, score from student, course, sc where sc.stuID = stu.stuID and sc.courseID = course.courseID;
(用inner join + on也可) 通过选课表把三个表串连起来，一般primary key (stuID,courserID)组成复合主键
左外联,显示所有学生的情况
select stuName,coursersName,score from stu left join sc on stu.stuID = sc.stuId left join courses on sc.cousersID = cousers.cousersID;
select 执行顺序：
from----> (inner/left/right) join on --->where(一次筛选) --->group by(根据某列把表又分成字表)----->having(二次筛选)
--->聚合函数---->order by ---->top ---->select
记录联合
union all -- union去重复
select * from stu union all select * from stu2;
sql注释语句 ---test # comment /* comment */