容易忘记的sql语句

技巧 套娃

需要选择两个同类型中偏小的 外面再套一个 min +gruop

怎么检查

第一步 看有木有group 分组的 字段是不是又问题
第二步 看嵌套查询 是否又别名 且 里面的字段是否齐全 --外面查询可能要用到里面的字段

下面有例子

round 精确小数点后两位

--增加索引 me是索引的名字
ALTER  TABLE product add index   me(fuck)

--删除列
ALTER  TABLE product DROP  fuck

--增加列
ALTER  TABLE product add  fuck1 int NOT NULL

--解释
EXPLAIN  SELECT * FROM product where id =1
--简单查询 两者是一样的
select * FROM users where gender="男" GROUP BY school
select * FROM users GROUP BY school HAVING gender='男'

-- SELECT CONCAT(RTRIM(title),RTRIM(price)) FROM item
-- SELECT (price*10) AS p FROM item 
-- SELECT AVG(price) AS p,AVG(sales)as s from item WHERE price>90
SELECT sum(score)+ avg(score)AS f FROM student GROUP BY class

--is null  left join
select Employee.name,Bonus.bonus from Employee left join Bonus on Employee.empId =Bonus.empId 
where bonus is null or bonus <1000 

---获得最大的订单数
SELECT  customer_number   FROM orders GROUP BY customer_number ORDER BY COUNT(*) DESC LIMIT 1
---在一张表中就东西  至于a和b随便返回一个
SELECT distinct b.seat_id FROM cinema a,cinema  b
WHERE a.free=1 and b.free=1 and(
a.seat_id = b.seat_id -1 
or
a.seat_id =b.seat_id +1 
)
order by b.seat_id

---就分组中最小 用min函数
SELECT player_id ,MIN(event_date)as first_login FROM Activity 	GROUP BY device_id
面试常考的string之类的

### 部门工资最高的3个员工
SELECT
    d.Name AS 'Department', e1.Name AS 'Employee', e1.Salary
FROM
    Employee e1
        JOIN
    Department d ON e1.DepartmentId = d.Id
WHERE
    3 > (SELECT
            COUNT(DISTINCT e2.Salary)
        FROM
            Employee e2
        WHERE
            e2.Salary > e1.Salary
                AND e1.DepartmentId = e2.DepartmentId
        )

---部门工资最高的员工 用in
SELECT
    Department.name AS 'Department',
    Employee.name AS 'Employee',
    Salary
FROM
    Employee
        JOIN
    Department ON Employee.DepartmentId = Department.Id
WHERE
    (Employee.DepartmentId , Salary) IN
    (   SELECT
            DepartmentId, MAX(Salary)
        FROM
            Employee
        GROUP BY DepartmentId
	)
;



String a="abvvv";
String b="abvvv";

只要是字符串一旦生成了一个 另一个就是一样的


String a="abvvv";

String b=new String("abvvv");
这两个铁定不一样

只要是给字符串赋过值了，在字符串常量池中就有了

String a=new String("ab");

String b="ab";
String c="ab";
System.out.println(b==c);

 
 答案是true
 

String a=new String("ab")+new String("vvv");;
把字符串并起来都是false

String b=new String("abvvv");
;

   System.out.println(a==b);
   
   答案是false
   

String b=new String("abvvv");
String a= "ab".concat("vvv");
false
乱拼接的都是flase


-- 最主要的就是case的使用 case 后面不加东西就是一直循环 满足一个条件case后面不加东西
select 
(
    case 
when mod(id,2)!=0 and id!=counts then id+1
when mod(id,2)!=0 and id=counts then id
else id-1
end
)as id ,
student from
seat,(select count(*)as counts from seat)as seat_counts order by id 

---update 的case使用  当谋列和条件相同时发生
update salary set sex =
case sex
when 'm' then 'f'
else 'm'
end

计算出有5个下属的员工

肯定不会让你一张表就算出来的 先是利用count函数 +group函数 计算出 然后组合表算出