数据库入门（mySQL）：数据操作与查询

insert into table_name(field1,field2,field3,...fieldn)
valuses(value1,value2,value3,...valusen);

#查看表信息
describe users;
#给users增加一条数据
insert into users(name,age,phone,password) values ("张三",22,"12345678901","abcdefg");
#查询校验
select * from users;

insert into users(name,age,phone,password) values
("王五",23,"12345678903","abcdefg"),
("程程",24,"12345678904","abcdefg");
//插入多条数据同样可以插入部分数据和插入完整数据时可以不写字段名，规则与插入单条数据一样

insert into table_name1(field11,field12,field13,...field1n)
select (fileld21,field22,field23,...field2n)
from table_name2
where ...
//从数据表table_name2中查询插入到数据表table_name1中。

update table_name
set field1=value1,
field2=value2
where condition;
//condition指定满足条件的数据记录

update users
set password="gfedcba"
where phone="12345678904";

delete from table_name where condition;
//condition表示要删除的数据记录的条件，符合条件的所有数据记录都会被删除

delete from users where id=4;

//查询所有字段、所有数据记录
select * from table_name
//查询所有数据记录的指定字段
select field1,field2,...fieldn from table_name
//查询符合条件的数据记录的所有字段(condition表示查询数据记录的条件)
select * from table_name where condition
//查询符合条件的数据记录的指定字段
select field1,field2,...fieldn from table_name condition

//通过distinct去掉重复数据是匹配所有查询字段是否同时重复，类是查询内容即为查询条件
select distinct field1,field2,...fieldn table_name;

//比如通过月薪查询某个员工的年薪(月薪字段：sal)
select name, sal*12 from employee;

select name, sal*12 as yearsalary from employee;

//查询月薪小于3500的员工
select name,sal from employee where sal<3500;
//查询月薪3500到5000的员工
select name,sal from employee where sal=>3500 && sal<=5000;
select name,sal from employee where sal=>3500 and sal<=5000;

select * from employee where sal between 2000 and 5000;

//查询电话为空的用户信息
select * from users where phone is null;

//查询领取奖金的雇员
select * from employee where comm is not null;

//例如查询雇员名字为“张三”和“李四”的信息
select * from employee where name in("张三","李四");//通过或运算符（||）或者or命令实现
select * from employee where name="张三" or name="李四";
select * from employee where name="张三" || name="李四";

//例如查询雇员名字不是“张三”和“李四”的信息
select * from employee where name not in("张三","李四");//通过不等于加上逻辑与运算符（and）实现
select * from employee where name!="张三" && name!="李四";
select * from employee where name!="张三" and name!="李四";

//下面这两个查询语句查询的结果一致
select * from employee where name in("张三","李四");
select * from employee where name in("张三","李四",null);//但是如果使用null作为非集合数据查询，则不会查询到任何数据
select * from employee where name not in("张三","李四",null);//查询结果为空
select * from employee where name not in(null);//查询结果为空

//查询以“A”开头的employee数据表中ename字段的数据记录
select * from employee where ename like "A%"
//查询以“A”开头且只有两个字符的employee数据表中ename字段的数据记录
select * from employee where ename like "A_"

//查询除第二个字符为“A”的employee数据表字段ename的数据记录
select * from employee where ename not like "_A%";

//匹配不含有数字5的薪资雇员信息
select * from employee where sal not like "%5%";
//如果使用“%%”作为匹配条件会将所有数据记录查询出来

select field1,field2,...fieldn from table_name
where condition
order by fileldm1 [ASC|DESC] [,fileldm2 [ASC|DESC]]
//fileldm表示按照该字段排序
//ASC表示升序排序
//DESC表示降序排序//并且可以按照单字段排序或者多字段排序

//按照月薪升序排序，月薪字段sal不能为空（因为默认升序排序，所以最后的asc关键字可写不写）
select name,sal from employee where sal is not null order by sal;

//先根据月薪升序排序，然后再根据名字降序排序
select name,sal from employee order by sal asc,name desc;

select field1,field2,...fieldn from table_name
where condition
limit offset_start,row_count
//offset_start表示记录的起始偏移量
//row_count查询的总记录数//只写一个限制数值时默认为查询的总记录数
select * from employee limit 3;//表示只查询前三条数据
select * from employee limit 2,3;//表示从第二条数据后面（第三条数据开始）查询三条数据

select function(field)
from table_name
where condition

#统计记录条数
select count(name) from employee ;
select count("sal") from employee ;#这样的查询也会有结果，但是不会忽略null值
select count(sal) from employee ;#sal字段值为null时不会被统计
#统计计算平均值
select avg(sal) from employee;
#统计计算求和
select sum(sal) from employee;
#统计最大值和最小值(统计时会忽略null值记录)
select max(sal),min(sal) from employee;

select * from table_name where condition group by field;

select * from students group by class_id;

select class_id,group_concat(student_name) from students group by class_id;

select class_id,student_name from students group by class_id,student_name;

select class_id,student_name from students order by class_id;

//语法
SELECT field1 field2 ...fieldn
FROM tablename1
UNION | UNION ALL
SELECT field1 field2 ...fieldn
FROM tablename2
UNION | UNION ALL
SELECT field1 field2 ...fieldn
FROM tablename3
...
//示例实现
select name,sex from a_table union select * from b_table;

//语法
SELECT field1 field2 ...fieldn from join_tablename1
CROSS JOIN join_tablename2
//示例
select * from a_table cross join b_table;

//语法
SELECT field1 field2 ...fieldn
FROM join_tablename1 INNER JOIN join_tablename2 [INNER JOIN join_tablenamen]
ON join_condition
//示例：等值连接查询
select * from a_table inner join b_table on a_table.n = b_table.n;
//示例：不等值连接查询
select * from a_table inner join b_table on a_table.n != b_table.n;
//示例：自然连接查询
select * from a_table natural join b_table;//与等值连接查询结果一致（内部实现也一样，都是先通过笛卡尔积合并在筛选数据）
select * from a_table,b_table where a_table.n=b_table.n;

//语法
SELECT field1 field2 ...fieldn
FROM join_tablename1 LEFT|RIGHT|FULL [OUTER] JOIN join_tablename2
ON join_condition
//示例：左外连接查询
select * from a_table left join b_table on a_table.n = b_table.n;
//示例：右外连接查询
select * from a_table right join b_table on a_table.n = b_table.n;
//示例：全外连接查询（MySQL不支持）
select * from a_table full join b_table on a_table.n = b_table.n;

//查询雇员表（t_employee）中工资比SMITH的全部雇员信息
select * from t_employee where sal>(select sal from t_employee where ename='SMITH');

//查询工资和职位与SMITH一样的员工信息
select * from t_employee where (sal,job)=(select sal,job from t_employee where ename="SMITH");

//查询雇员表的数据记录，这些数据记录的部门编号(deptno)必须是在部门表中出现的
select * from t_employee where deptno in(select deptno from t_employee);

//查询雇员信息，工资不低于职位MANAGER的雇员信息
select * from t_employee where sal>=ANY(select sal from t_employee where job="MANAGER");

//查询雇员信息，雇员工资要与职位MANAGER的工资
select * from t_employee where sal>ALL(select sal from t_employee where job="MANAGER");

//查询已有员工的部门信息
select * from t_dept where exists(select * from t_employee where t_employee.deptno=t_dept.deptno);
//对比查询没有员工的部门信息
select * from t_dept where deptno not in(select deptno from t_employee);

//查询部门信息：部门编号、部门名称、部门地址、部门雇员人数、部门平均工资
select deptno,dname,loc,number,average
from t_dept inner join
(select deptno dno,count(empno) number,AVG(sal) average
from t_employee group by dno) e on t_dept.deptno=e.dno;