Database Basic Query method, etc.

Database: A database is a warehouse that organizes, stores, and manages data according to its structure, which is generated more than 60 years ago, with the development of information technology and the market, especially after the 1990s, data management is no longer just the storage and management of data, and transform it into a variety of data management methods that users need. There are many types of databases, ranging from the simplest tables with various data to large database systems that can store massive amounts of data, are widely used in all aspects.

We simply learned the basic way to create a database and simply query the method:

Create:

Create statement:

Such as: Create Database F21;

CREATE table EMP, etc.;

Inquire:

show databases;

Show tables;//note To use database, statement: using Database;

Here is a simple way to create a table (without adding data):

Mysql-> CREATE TABLE EMP (

Empid int Auto_increment primary KEY,

EmpName varchar NOT NULL,

Gender int,--0 and 1 indicate

Birth date);

Additions and deletions:

Look at the following statement:

ALTER TABLE EMP Modify EMPID int (20);

--Modification type;

ALTER TABLE EMP add deptname varchar (a) not null;

-Increase

ALTER TABLE EMP Change EmpName empname Vachar (20);

-Change

ALTER TABLE EMP Rename dept;

--Change the name of the table

and default ' 1 '--defaults;

Insert data:

INSERT into EMP (Empname,gender,birth) VALUES (' Ha ', 1,1993-4-5);--pay attention to the writing format;

Specify a FOREIGN key:

Foreign KEY (DEPTNO) References dept (DEPTNO);

Query statement:

Use "*" within the scope of the whole table;

such as: SELECT * from EMP;

If not specified range:

Select EmpName from EMP;

WHERE statement:

SELECT * from emp where Empid >3;

Fuzzy query:

Select EmpName from emp where empname like ' li-percent ';

--Inquire about the name of Li's surname;

Tick-back query:

SELECT DISTINCT deptno from EMP;

Sort the result set:

SELECT * from emp ORDER by deptno ASC;

--Ascending

SELECT * from emp order BY deptno Desc;

--Descending

Statistics:

Select COUNT (*) from EMP;

Several functions: (sum avg max min);

Select Deptno as ' department number ', COUNT (*) as ' number of employees ', sum (Salary) as ' payroll sum ', avg (Salary) as ' average salary ' from EMP Group by Deptno have de Ptno = 1;

Use of if:

Select EmpName, if (gender = 1, ' Male ', ' female ') from EMP;

--If gender = 1 shows male, otherwise female;

Multi-table queries:

Inline:

SELECT * FROM emp A, dept b where A.deptno = B.deptno;

Outreach: (leftist)

SELECT * FROM EMP a LEFT JOIN dept b on a.deptno = B.deptno;

(Right Union)

SELECT * from EMP a right joins dept b on a.deptno = B.deptno;

In usage:

SELECT * from EMP where deptno in (2,3);

Last use of union:

A link for two sets;

Database Basic Query method, etc.