MySQL知识

创建表：
insert into departments value(6,'行政部',null);
===================================================
查看当前打开的数据库
select database();
===================================================
描述表：
describe xs_2;
===================================================
修改表结构：
alter table xs_2
add 奖学金等级 tinyint,
drop column 姓名;
===================================================
增加数据:
insert into departments value(6,'行政部',null);
===================================================
修改表数据：
update xs 
set 总学分=总学分+10;
==================================================
删除记录：
delete xs from xs 
where 学号='081123';
==================================================
删除表：
alter table xs_2
add 奖学金等级 tinyint,
drop column 姓名;
==================================================
去重操作:
select distinct Sex as 性别,DepartmentID as 部门号 
from employeesr;
===================================================
排序操作:
select EmployeeID as 员工身份号码,InCome as 收入
from salary 
where InCome>2000 
order by InCome;
===================================================
替换查询结果中的数据操作:
select Name as 姓名,
case 
when Sex = '1' then '男'
when Sex = '0' then '女'
end as 性别
from employees ;
===================================================
多表连接替换查询结果操作:
select Name as 姓名,Address as 住址,
case 
when InCome<2000 then '低收入'
when InCome>3000 then '高收入' 
else '中等收入'
end as 收入水平
from employees join salary;
===================================================
累计行数操作:
select count(*) as 员工总数
from employees;
===================================================
求平均值操作:
select avg(InCome) as 月收入平均值 
from salary;
===================================================
求最大值操作:
select max(InCome) as 月薪最高 
from salary;
===================================================
求最小值操作:
select min(InCome) as 月薪最高 
from salary;
===================================================
求总数操作:
select sum(InCome) as 月薪总数 
from salary;
===================================================
模式匹配操作:
select * 
from employees 
where name like '王%';
===================================
select 学号,姓名,专业名
from xs 
where 学号 like '%0_';【查看学号倒数第二位为零的学号】
===================================
select 学号,姓名
from xs 
where 学号 like '%#_%' escape '#';
===================================================
子查询:
select * 
from employees 
where DepartmentID in
(
select DepartmentID
from departments 
where DepartmentName='市场部'
);
===================================================
连接查询的使用:
select employees.*,salary.* 
from employees join salary on employees.EmployeeID=salary.EmployeeID;
===================================================
分类统计人数:
select Sex,count(Sex)
from employees 
group by Sex;
===================================================
左连接:
select xs.*,课程号 
from xs left join xs_kc on xs.`学号`=xs_kc.`学号`;
===================================================
右连接:
select xs.*,课程号 
from xs right join xs_kc on xs.`学号`=xs_kc.`学号`;
===================================================
查询空值语句:
select * 
from xs 
where 备注 is null;
===================================================
and,or的使用:
select 姓名,专业名,性别 
from xs 
where 专业名='计算机' and 性别=1;
================================
select 姓名,专业名,性别 
from xs 
where 专业名='计算机' or 专业名='通行工程';
===================================================
all的使用:
select 姓名,出生日期 
from xs 
where 出生日期>all
(
select 出生日期 
from xs 
where 专业名='信息工程' 
);
==================================================
正向排序:
select * 
from xs_kc 
order by 成绩;
====================
逆向排序:
select * 
from xs_kc 
order by 成绩 desc;
==================================================
limit 字句使用:
select * 
from xs_kc 
order by 成绩 desc
limit 5;
========
limit 3,5;<从第三行开始，输出5行>
==================================================
视图使用:<注意！！多表连接时,查看设计表栏位字符集,排列顺序是否一致>
create view xs_1 
as 
select 学号,姓名,出生日期 
from xs;
==================================================