Database Basics 001

1. SQL classification

(1)DDL(data definition Language) statement: A definition language that defines the definitions of different data segments, databases, tables, columns, indexes, and other database objects. Common keywords:create, DROP, alter , and so on.

(2)DML(data manipulation Language) statement: Database manipulation statements for adding or deleting database records and checking data integrity. Commonly used include INSERT, delete, update, select , and so on.

(3)DCL(Data Control Language) statement: A statement that controls the level of permission and access to different data segments directly. These statements define the database, table, field, user's access rights, and security level. Key keywords include grant, revoke , and so on.

2. The most significant difference between DDL and DML is that DML is only an operation on the internal data of the table, not the definition of the table, the modification of the structure, and no other objects involved.

3. Create a Database

Mysql–u root–p

MySQL on behalf of the client command,-U followed by the connected database user,-p indicates the need to enter a secret
Code

CREATE DATABASE dbname

4. Delete Database

Drop Database dbname;

5. Create a table

CREATE TABLE tablename (column_name_1 column_type_1 constraints, column_name_2 column_type_2 constraints, ... column_ Name_n column_type_n constraints)

Because the table name of MySQL exists on disk in the form of a directory, the character of the table name can be allowed with any directory name
The character. COLUMN_NAME is the name of the column, Column_type is the data type of the column, Contraints is the column's approximate
Beam conditions.

6, look at the definition of the table

DESC TableName

Although the DESC command can view the table definition, its output is incomplete, in order to see a more comprehensive set of tables
The SQL statement that creates the table, you can use the following command:

mysql> Show CREATE table emp \g;

1. Row ***************************table:empcreate table:create Table ' emp ' (' ename ' varchar) DEFAULT NULL, ' HireDate ' da Te default null, ' Sal ' decimal (10,2) default null, ' Deptno ' int (2) default Null,key ' Idx_emp_ename ' (' ename ')) Engine=innod B DEFAULT CHARSET=GBK1 Row in Set (0.02 sec) Error:no Query specifiedmysql>

From the Create SQL statement in the table above, you can see the engine (storage engine) of the table in addition to the table definition.
and CharSet (character set) and other information. The "\g" option means that records can be arranged vertically in a field,
Longer records are easier to display.

7. Delete a table

DROP TABLE TableName

8, modify the table

(1) Modifying table types

ALTER TABLE tablename MODIFY [COLUMN] column_definition [First | After Col_name]

For example, modify the Ename field definition of the table emp to change varchar (10) to varchar (20):

ALTER TABLE EMP Modify ENAME varchar (20);

(2) Add table field

ALTER TABLE tablename ADD [COLUMN] column_definition [First | After Col_name]

For example, the table EMP adds a new field of age, type int (3):

ALTER TABLE EMP Add column age int (3);

(3) Delete the table field with the following syntax:

ALTER TABLE tablename DROP [COLUMN] Col_name

For example, delete the field age:

ALTER TABLE EMP drop column age;

(4) Renaming of fields

ALTER TABLE tablename Change [COLUMN] Old_col_name column_definition
[first| After Col_name]

For example, rename age to Age1 and modify the field type to int (4):

ALTER TABLE EMP change age Age1 int (4);

(5) Modifying the order of the fields

In the field additions and modifications Syntax (add/cnahge/modify) that are described earlier, there is an option to First|after
column_name, this option can be used to modify the position of the field in the table, and the default Add new field is added to the
The last position of the table, and change/modify does not change the position of the field by default.
For example, add the new field birth date to the ename:

ALTER TABLE EMP Add birth date after ename;

Modify the field age to put it first:

ALTER TABLE EMP Modify age int (3) first;

(6) Renaming of tables

ALTER TABLE tablename RENAME [to] New_tablename

For example, rename the table emp to EMP1

ALTER TABLE EMP Rename EMP1;

9. DML statements

DML operations refer to the Operations of table records in the database, including inserts (insert) for table records, Updates (update),

Delete (delete) and query (select) are the most frequently used actions that developers use every day. The following will enter them in turn
The introduction of the line.

(1) Inserting records

INSERT into TableName (field1,field2,...... fieldn) VALUES (value1,value2,...... valuesn);

For example, insert the following record into the table emp: ename is zzx1,hiredate to 2000-01-01,sal for 2000,deptno
For 1, the command executes as follows:

mysql> INSERT INTO EMP (ENAME,HIREDATE,SAL,DEPTNO) VALUES (' zzx1 ', ' 2000-01-01 ', ' 2000 ', 1);

(2) Update record

UPDATE tablename SET Field1=value1,field2.=value2,......fieldn=valuen [WHERE CONDITION]

For example, change the salary (SAL) in the table emp ename to "Lisa" from 3000 to 4000:

Update emp Set sal=4000 where ename= ' Lisa ';

In MySQL, the update command can update data in multiple tables at the same time, with the following syntax:

UPDATE t1,t2...tn Set T1.FIELD1=EXPR1,TN.FIELDN=EXPRN [WHERE CONDITION]

(3) Deleting records

DELETE from TableName [WHERE CONDITION]

(4) Inquiry record

SELECT * FROM TableName [WHERE CONDITION]

(a) Querying for records that are not duplicates

Sometimes you need to remove the records from the table and display them, you can use the DISTINCT keyword to achieve:

SELECT DISTINCT deptno from EMP;

(b) Conditional enquiry

(c) Sequencing and restrictions

Implemented with the keyword ORDER by, the syntax is as follows:

SELECT * FROM TableName [WHERE CONDITION] [ORDER by Field1 [desc| Asc],field2
[desc| ASC],......FIELDN [desc| ASC]]

DESC and ASC are sort order keywords, and DESC indicates descending order BY field, ASC means ascending
If you do not write this keyword is sorted by default in ascending order. Order by can be followed by several different sort fields, and
And each sort field can have a different sort order.
For example, the records in the EMP table are displayed according to the salary level:

SELECT * from emp order by Sal;

For a sorted record, you can use the LIMIT keyword if you want to display only a subset, not all of them
, the syntax for LIMIT is as follows:
SELECT ... [LIMIT Offset_start,row_count]

Where Offset_start represents the starting offset of the record, and Row_count represents the number of rows displayed.

For example, the first 3 records in the EMP table that are sorted by Sal are displayed:

SELECT * from emp order by Sal limit 3;

If you want to show that the EMP table is sorted by Sal and starts with the second record, 3 records are displayed:

SELECT * from emp ORDER by Sal limit 1, 3;

Limit is often used in conjunction with order by to make a paginated display of records. Note: Limit is the syntax after MySQL extended SQL92 and cannot be generalized on other databases.

(d) Aggregation

In many cases, we need to do some summary operations, such as counting the number of people across the company or counting everyone in each department.

The aggregate operation of SQL.
The syntax for the aggregation operation is as follows:
SELECT [Field1,field2,...... fieldn] Fun_name
From TableName
[WHERE Where_contition]
[GROUP by Field1,field2,...... fieldn
[With ROLLUP]]
[Having where_contition]
Make the following description of its parameters.
? Fun_name represents the aggregation operation to be done, which is the aggregate function, which is commonly used with SUM, COUNT (*) (
Max (max), min (minimum).
? The GROUP by keyword represents the fields to be aggregated by category, such as the number of employees to be classified by department, department
It should be written behind group by.
? With ROLLUP is an optional syntax that indicates whether the aggregated results of the classification are re-aggregated.
? The HAVING keyword indicates that the result of the classification is then filtered by the condition.

Note: The difference between having and where is that having is a condition filter for the result of the aggregation, and where is the
The record is filtered before it is closed, and if the logic allows, we use where possible to filter the records as far as the result
Set reduction, will be the efficiency of the aggregation greatly improved, and finally according to the logic to see if have to re-filter.

For example, to count the total number of companies in the EMP table:

Select COUNT (1) from EMP;

On this basis, the number of individual departments should be counted:

Select Deptno,count (1) from the EMP group by DEPTNO;

To be more detailed, it is necessary to count the number of departments and the total number of people:

Select Deptno,count (1) from the EMP group by DEPTNO with rollup;

Departments with a population of more than 1 persons:

Select Deptno,count (1) from EMP Group by DEPTNO have count (1) >1;

Finally, the total salary, maximum and minimum salary of all employees of the company are counted:

Select sum (SAL), Max (Sal), Min (sal) from EMP;

(e) Table connection

When you need to display fields from multiple tables at the same time, you can use table joins to implement such a feature.
From the big class up, the table joins into the inner and outer joins, the main difference between them is that the inner connection only selects two tables
Match each other's records, and the outer joins will pick up other mismatched records. What we use most is the inner connection.
For example, the name of all employees and the department name are queried, as employee names and departments are stored in the table emp and
Dept, therefore, you need to use a table connection to query:

Select Ename,deptname from emp,dept where Emp.deptno=dept.deptno;

The outer joins are divided into left and right connections, as defined below.
? Left join: Contains all the records in the left table or even the records that do not match it in the right table
? Right connection: Contains all records in the right-hand table, even those that do not match it in the left table

For example, query all user names and department names in the EMP:

Select Ename,deptname from the EMP left JOIN dept on Emp.deptno=dept.deptno;

(f) Recording of joint

We often encounter such an application, the data of two tables according to a certain query criteria, the results are merged
To show up together, this time, you need to use the Union and the Union ALL keyword to implement such a function, the specific words
Law is as follows:

SELECT * from T1
union| UNION All
SELECT * FROM T2
......
union| UNION All
SELECT * from TN;

The main difference between union and union all is that union all merges the result set directly, and the Union is the
The result after the UNION all is DISTINCT once, removing the result after the duplicate record.

10. DCL Statement

DCL statements are primarily used by DBAs to manage object permissions in the system, and are seldom used by general developers.

Database Basics 001