MySQL学习2

DML

数据管理语言,对数据增删改

新增

指定列添加数据

insert into staff(id,code,name,salary) VALUE(1,'10001','李四',9000);

自动递增列可以不指定;自动递增列不会回撤，不会补齐;自动递增列从该列最大值递增

not null列必须要指定数据

insert into staff(code,name,salary) VALUES('10002','王五',10000),('10003','赵六',11000);

 可以不指定列，但是需要将全部列指定数据

INSERT into staff value(12,'10012','王维',3000);

 删除数据  delete

delete from staff where id=1;

  修改/编辑  update

update staff set name='李清照' WHERE id=5;
UPDATE staff set salary=salary+2000 where name='李清照';
UPDATE staff set name='王维',salary=salary-2000 where id=12;

清空表

delete from staff;-- 删除每一行数据
TRUNCATE staff;-- 清空表  性能更优越

 查询  MQL

SELECT 1;
select now();
-- 查询表格
-- 指定列名查询
select name,salary from staff;
-- 使用*代替所有的列
select * from staff;
-- 使用as 指定别名  列 方法结果 表 视图
select name as `姓名` from staff;

-- where 指定条件语句
select * from staff where id=15；
select * from staff where id!=1;-- 不等于
select * from staff where id<>1;-- 不等于
 
-- 大于小于
select * from staff where id>20; 
select * from staff where id>=20; 
select * from staff where id<20; 
select * from staff where id<=20; 

-- 对null值的判断
SELECT * from staff where salary is NULL;
SELECT * from staff where salary IS NOT NULL;
SELECT * from staff where salary <=> NULL;-- 等于null

-- 多条件 and or in  not in
select * from staff where id<=100 AND salary>=1000;
SELECT * FROM staff where salary>100 OR salary<500;
SELECT * from staff where id IN (1,3,5,7,8,10);
SELECT * from staff where id NOT IN (1,3,5,7,8,10);

-- between and 
SELECT * from staff WHERE salary BETWEEN 5000 and 10000;

-- 模糊查询 LIKE  %任意个数任意字符  _有且只有一个字符
select * from staff where name like '%a%'; 
select * from staff where name like '张%';-- 姓张的员工
select * from staff where name like '张_';-- 张某

-- 是否存在 exists
SELECT * from staff WHERE exists (SELECT * FROM staff WHERE id=1);

-- any All
SELECT * from staff where salary > any(SELECT 25);-- 大于结果集内任意一个结果
SELECT * from staff where salary > ALL(SELECT 25);-- 大于结果集内所有结果

-- 排序 order by
select * from staff order by salary
-- 正序  asc  ;  倒叙  desc
select * from staff order by salary asc;
select * from staff order by salary DESC;
SELECT * FROM staff ORDER BY salary asc,code DESC;

-- 拼接（合并）查询结果
-- UNION会将重复的结果去除
select name,salary FROM staff UNION SELECT code,name  from staff;-- 查询的列的数量必须一致
-- union all不会去除重复结果
select name,salary FROM staff UNION SELECT code,name  from staff;

-- 去重  对整个查询结果去重
select DISTINCT salary,name FROM staff
-- 部分查询
select * from staff order by salary desc limit 3;-- 查询前三名
-- 查询第三名到第五名 limit start,count;
select * from staff order by salary desc limit 2,3; 

-- case when then
select *,case WHEN salary<15000 THEN '薪资较低'
WHEN salary<=20000 THEN '薪资正常'
ELSE '薪资较高'
end 
from staff;

SELECT *,CASE salary
WHEN 12000 THEN
	'还行'
WHEN 15000 THEN
    '挺高'
WHEN 18000 THEN
    '哇哦'
WHEN 20000 THEN
    '好厉害'
END AS `level` from staff;

-- 分组  聚合函数  将多个数据聚合成一个
select max(salary) from staff;-- 最大值
select min(salary) from staff;-- 最小值
select avg(salary) from staff;-- 平均数
select sum(salary) from staff;-- 求和
select count(salary) from staff;-- 个数

-- 分组查询
select department,avg(salary) as 平均薪资,sum(salary) as 薪资总和 from staff group by department;
-- 分组筛选
-- having 对分组之后的数据再筛选
select department from staff group by department having avg(salary)<50000;

-- null  忽略
select department,avg(salary),count(salary),count(*) from staff GROUP BY department

连接查询

外连接

左外连接  left join
右外连接  right join
全外链接  full join; mysql不支持全外连接

select a.Tname,b.Cname from teacher as a 
left JOIN course as b on a.tid=b.tid;
select a.Tname,b.Cname from teacher as a 
right JOIN course as b on a.tid=b.tid;

 

左连接以左表为主表,会显示所有数据.右表为副表,只会显示与主表有关的数据;右连接相反

内连接 

内连接inner join  也可以写成join; 只显示有对应关系的数据

select a.Tname,b.Cname from teacher as a 
INNER JOIN course as b on a.tid=b.tid;

交叉连接 cross join

子查询

-- 子查询
SELECT sname from student where sid IN(
  SELECT sid from sc where score<60
);
-- 将子查询当作表进行查询
SELECT Sname from (SELECT * from student where sid=01) as B;