Delete and change the database

--Inserting data

INSERT into EMP values (123, ' si ', ' ', ', ');

Insert into EMP1 (empno) values (234);

Commit

--Delete data

Delete from emp where empno = 222;

Commit

--Update data

Update emp Set empno=2323, ename= ' Zhangsan ';

Update EMP1 set sal= ' 111 ' where sal= ' 1311 ';

--Querying data

SELECT * from Emp1;--shift+home key, and then press F8 to execute.

Select distinct ename, Sal from EMP1; --The query is unique (if it is two values, the union is unique.) )

Select Empno Employee number, ename as employee name from EMP; --Set the alias for the query result.

SELECT ' number is: ' | | Empno | | ' The employee's name is: ' | | ename | | ', the base salary is: ' | | Sal employee information from EMP; -The result is such a patchwork.

Select Empno, ename, (sal+200) *12 as annual salary from EMP; --The query result can be set to function.

Select COUNT (*) from EMP;

SELECT * from EMP where empno in (' 112 ', ' 211 ');

SELECT * from emp where empno = ' or empno = ' 222 ';

SELECT * from emp where empno like '%222% ';

SELECT * from emp order BY Sal Desc;

--left join: On, right join: On. Left and right connections.

Select T1.empno, t1.ename from emp T1 right join EMP1 T2 on T1.empno=t2.empno;

--group by has one principle, that is, in all columns that follow the Select, columns that do not use aggregate functions must appear behind GROUP by. Group by group, then aggregate query.

--having is typically used in conjunction with group by to filter the record values returned by the group by statement. Having exists to compensate for the lack of the WHERE keyword to be used in conjunction with aggregate functions.

Select name, sum (number) from the test group by name;

Select ID, COUNT (course) as Numcourse, AVG (score) as Avgscore from student GROUP by ID have AVG (score) >80;

Delete and change the database