Database notes 1

1. Relationship between SQL, DB and DBMS

DB: the DataBase exists as a file on the hard disk

SQL: structured query language, high-level language, compiled and executed

DBMS: DataBase Manager System Mysql, SQLservice, Oracle, DB2, Syba se

Relationship: DBMS executes SQL statements and then operates DB data

2. Tables and general SQL statements

Table: database basic group units

DQL (data query language): select statement

DML (data operation language): insert, update, delete

DDL (data definition language): create, alter, drop

TCL (transaction control language): commit transaction, rollback transaction

DCL (data control language): grant authorization, revoke authority

View databases: show databases;

Create database: create database database name;

View tables: show tables;

Initialization data: path of source sql file;

Delete database: drop database database name;

expand:

View the database used: select database();

View mysql version number: select version;

End a statement: \ c

3. Query statement (DQL)

Simple omission

Query criteria:

... is a number: between... and... Is a closed interval

... is the character: between... and... Is the left closed right open interval

Null: it is not a value and cannot be judged by the equal sign: is null and is not null.

Keyword in: in (100200): it is a specific number, not an interval.

Fuzzy query like:% 1_

Case: the name contains: Use escape characters:_ To query

Ascending: ASC (default)

Descending order: desc

Order:

select 
	field						3
from
	Table name						1
where
	condition						2
order by
	....				      4

order by It's the last one

4. Grouping function

5 grouping functions: count, sum, avg, max, min

Grouping function: multiline processing function

When the grouping function encounters null: null is automatically ignored

Grouping functions cannot be directly followed by where conditions: group by is executed after where

select		5
	..			
from		1
	..
where		2
	..
group by	3
	..
having		4
    ..
order by    6
	..

What is the difference between count(*) and count (a field)?

count(*): counts the total number of entries

Count (a field): indicates the total amount of null data in this field

The database stipulates that the operation structure with null participation must be null

Case: calculate the annual salary of employees

mysql> select ename ,(sal+comm)*12 as 'Annual salary'from emp;
+--------+----------+
| ename  | Annual salary     |
+--------+----------+
| SMITH  |     NULL |
| ALLEN  | 22800.00 |
| WARD   | 21000.00 |
| JONES  |     NULL |
| MARTIN | 31800.00 |
| BLAKE  |     NULL |
| CLARK  |     NULL |
| SCOTT  |     NULL |
| KING   |     NULL |
| TURNER | 18000.00 |
| ADAMS  |     NULL |
| JAMES  |     NULL |
| FORD   |     NULL |
| MILLER |     NULL |
+--------+----------+
14 rows in set (0.00 sec)

mysql> select ename ,(sal+ifnull(comm,0))*12 as 'Annual salary'from emp;
+--------+----------+
| ename  | Annual salary     |
+--------+----------+
| SMITH  |  9600.00 |
| ALLEN  | 22800.00 |
| WARD   | 21000.00 |
| JONES  | 35700.00 |
| MARTIN | 31800.00 |
| BLAKE  | 34200.00 |
| CLARK  | 29400.00 |
| SCOTT  | 36000.00 |
| KING   | 60000.00 |
| TURNER | 18000.00 |
| ADAMS  | 13200.00 |
| JAMES  | 11400.00 |
| FORD   | 36000.00 |
| MILLER | 15600.00 |
+--------+----------+
14 rows in set (0.00 sec)

if null is an empty processing function and belongs to a single processing function

5. group by and having

Group by: group by a certain field or some fields

having: filtering the grouped data again

A grouping function is used in combination with group by,

Note: when grouping group by is used, select can only be followed by grouping functions and fields participating in grouping

Case: average salary per job?

select
	job,
	avg(sal)
from
	emp
group by
	job;

Case: find out the maximum salary of each department and display data greater than 2900

mysql> select max(sal),deptno from emp group by deptno;
+----------+--------+
| max(sal) | deptno |
+----------+--------+
|  3000.00 |     20 |
|  2850.00 |     30 |
|  5000.00 |     10 |
+----------+--------+
3 rows in set (0.00 sec)

mysql> select max(sal),deptno from emp where sal>2900 group by deptno;
+----------+--------+
| max(sal) | deptno |
+----------+--------+
|  3000.00 |     20 |
|  5000.00 |     10 |
+----------+--------+
2 rows in set (0.00 sec)

Case: find out the average salary of each department and display data greater than 2900

mysql> select avg(sal),deptno from emp group by deptno;
+-------------+--------+
| avg(sal)    | deptno |
+-------------+--------+
| 2175.000000 |     20 |
| 1566.666667 |     30 |
| 2916.666667 |     10 |
+-------------+--------+
3 rows in set (0.00 sec)

mysql> select avg(sal),deptno from emp group by deptno having avg(sal)>2000;
+-------------+--------+
| avg(sal)    | deptno |
+-------------+--------+
| 2175.000000 |     20 |
| 2916.666667 |     10 |
+-------------+--------+
2 rows in set (0.00 sec)

Summary: complete SQL statement

select
	..	5
from 
	..	1
where
	..	2
group by 
	..	3
having
	..	4
order by
	..	6