Shulou(Shulou.com)06/01 Report--

The following content mainly brings you MySQl simple command handouts, the knowledge here is slightly different from books, are summed up by professional and technical personnel in the process of contact with users, have a certain experience sharing value, hope to bring help to the majority of readers.

SQL (structured Structured query Query language Language)

classification

DDL (data definition language) table definition, user definition. Create drop alter

DML (data manipulation language) adds, modifies, deletes, and queries insert update delete select for data

DCL (data Control language) grant (authorized) revoke (Recycling)

DML

Insert into table (column...) Values (value...); insert into table (column...) Values (value.). Insert into Table 1 (column...) Select column... From Table 2 update Table set column = New value,... Update table set column = new value,... Where conditional delete from table; delete from where conditional select column. From table inner | left join table on connection condition where condition group by column. Having conditional order by column asc | desc limit n, m subquery (sub query)

Information about employees seeking the highest wage

Select max (sal), e.* from emp e;-will report an error

Select max (sal) from emp e

-- the maximum value is one row, but there are two names.

Case 1: treat the subquery as a value

Decompose the problem:

Select max (sal) from emp;-- > first step: 5000 think of it as a value select * from emp where sal = 5000;-- > second step: write the main query statement select * from emp where sal = (select max (sal) from emp);-- > step 3: replace

Case 2: treat the subquery as a table

The highest-paid employee in each department

Step 1: group by department (select max (sal) msal, deptno from emp group by deptno) a / generate temporary table a, which has two columns: msal, deptno step 2: treat the above query result as a temporary table, which can be joined with other tables select * from emp b inner join an on b.deptno=a.deptno and b.sal = a.msal Step 3: substitute the subquery into select * from emp b inner join (select max (sal) msal, deptno from emp group by deptno) an on b.deptno=a.deptno and b.sal = a.msal

Case when

You can work with select to translate the values of a column according to different conditions.

Similar to if else if in java

Syntax:

Case when condition 1 then result 1 when condition 2 then result 2. Else result nend

Below 2000 shows low wages, 2000 to 3000 shows medium wages, and over 3000 shows high wages.

Select empno, ename, sal, case when sal 2000 and sal

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.