MySQL Notes (2)

Constraints on SQL

A constraint is a restriction that ensures the integrity and uniqueness of a table's data by restricting the data on the table's rows or columns. 、

1 Constraint classification

A constraint is a restriction that ensures the integrity and uniqueness of a table's data by restricting the data on the table's rows or columns.

In MySQL, there are usually these kinds of constraints:

Constraint type:	PRIMARY Key	Default Value	only	FOREIGN Key	Non-empty
Key words:	PRIMARY KEY	DEFAULT	UNIQUE	FOREIGN KEY	Not NULL

The Delete library statement is:

1 DROP databases < database name >

2 PRIMARY Key

The primary key (PRIMARY key) is used to constrain a row in the table, and as a unique identifier for the row, the primary key is the key to the exact row in one table. The primary key cannot be duplicated and cannot be empty.

1 int (ten) primary key 2 Constraint < PRIMARY key name > PRIMARY key (primary key)  eg constraint <app_pk> primary KEY (name)3 constraint < PRIMARY key name > PRIMARY key (primary key, PRIMARY key) eg constraint <app_pk> primary KEY (Id,name)     4
     the composite primary key. A primary key can be a column in a table, or it can be identified by two or more columns in a table

3 Default value constraints

The default value constraint, default, specifies that when a column with a default constraint is blank, the Insert data is empty, and the defaults are used.

1 int (default'ten',      people_num has the defaults constraint, 10

The default constraint is only reflected when the INSERT statement is used, in the INSERT statement, if the position of the default constraint does not have a value, then the position will be populated with the value of default, such as a statement:

1 # Normal Insert Data 2 INSERT into Department (dpt_name,people_num) VALUES ('dpt1',one-to-one); 3 4 #插入新的数据, People_num is empty, default value is used 5 INSERT into Department (dpt_name) VALUES ('dpt2');

4 UNIQUE constraints

A unique constraint is simple, which specifies that the value of a column specified in a table must not have duplicate values, that is, each value in this column is unique.

1 Unique (phone), unique   constraint, phone's value only

Insert fails if there is a unique constraint when the newly inserted data and the existing data are duplicated by the INSERT statement, for example:

1 INSERT into people VALUES (110110); 2 INSERT into people VALUES (110110,);

5 FOREIGN KEY constraints

The foreign key (FOREIGN key) ensures both data integrity and the relationship between tables.

A table can have multiple foreign keys, and each foreign key must REFERENCES (reference) The primary key of another table, the column that is constrained by the foreign key, and the value must have a corresponding value in the column it references.

Insert fails if the value that is constrained by the foreign key does not correspond in the Reference column at insert:

1 INSERT into ABC VALUES (110111);

After you see change 110111 to 110110, the insert succeeds:

6 Non-null constraints

A non-null constraint (NOT NULL), which can be understood by a name, is a non-null constrained column that must not be empty when inserting a value.

1 int (ten),                              no non-null constraint 2int(a) not NULL,           with non-null constraint

Violations of non-null constraints in MySQL, will not error, there will only be warnings, such as the following statement:

  #INSERT success Age is empty because there is no non-null constraint, NULL is shown in the table insert into pp (id, phone) values (01,110 );  #警告 phone is not null constrained, the value is empty, the table shows 0insert into pp  (id, age) values ( 02,110< Span class= "Hljs-number" >         

As a result, a warning appears, but the data is still inserted successfully, ( 5.5.50版本) 5.6 MySQL with the above version will error and prohibit inserting data that does not conform to the non-null constraint):

At this point the contents of the PP table are:

Detailed SELECT statement

In a database operation statement, the most frequently used, and also considered the most important, is the SELECT query statement. The preceding SELECT * FROM table_name; statement is used to view all the contents of a table.

and SELECT with a variety of restrictive terms with the use of keywords, with a variety of rich features, the experiment will be detailed introduction.

1 Basic SELECT statement

The basic format of the SELECT statement is:

1 SELECT the column name to query from the table name WHERE the restriction condition;

If you want to query all the contents of a table, the column name you want to query is represented by an asterisk number, which means that * you want to query all the columns in the table.

Sometimes you just need to see the specified columns of a table, such as to see the name and age of the people table:

1 SELECT name,age from people;

2 Mathematical symbol conditions

SELECT statements often have a WHERE constraint that is used to achieve a more accurate query. Where restrictions can have mathematical symbols ( =,<,>,>=,<= ), we have just queried the name and age, and now make a slight change:

1 SELECT name,age from people WHERE age>;

Filter out results with age greater than 25.

Or find the name,age and phone of a person named Mary:

1 SELECT name,age,phone from people WHERE name='Mary';

3 "and" and "or"

From these two words you can understand their role. There can be more than one limit behind where, and depending on the logical relationship between conditions, you can connect with or (or) and and (and) :

1  -  - 2 SELECT name,age from people WHERE age< OR age>;

1  -  - 2 SELECT name,age from people WHERE age> and age<;

The constraints age>25 and age<30 , if required to contain the two digits 25 and 30, can be replaced by age between and:

4 in and not in

Keywords in and not are as obvious as their names, and are used to filter results in a range of " in" or "not ", for example, we want to query the DPT3 Or a dpt4 person:

1 SELECT NAME,AGE,PHONE,IN_DPT from the employee WHERE IN_DPT in ('dpt3',' Dpt4 ');

The effect of not in is that, as in the following command, the person who is not dpt1 or not DPT3 is queried:

1 SELECT NAME,AGE,PHONE,IN_DPT from employee WHERE IN_DPT isn't in ('dpt1', c14> 'dpt3');

5 wildcard characters

The keyword like is used in SQL statements and wildcards, which represent unknown characters. The wildcard characters in SQL are the _ and % . _it represents an unspecified character and represents an unspecified % character.

For example, to remember only the first four digits of the phone number 1101, and then two forgotten, you can use two _ wildcard characters instead:

1 ' 1101__ ';

In another case, such as remembering only the first letter of the name and not knowing the length of the name, a wildcard is used % instead of a variable character:

1 ' j% ';

This will find the person with the first letter J .

6 Sorting the results

Sometimes you might want to sort the results by a column, which uses the order by sort keyword. By default, the result oforder by is arranged in ascending order, while the keyword ASC and DESC can be used to specify ascending or descending sorting.

For example, we sort by salary in descending order, the SQL statement is:

1 SELECT name,age,salary,phone from employee ORDER by salary DESC;

7 SQL built-in functions and calculations

SQL allows you to calculate the data in a table. For this, SQL has 5 built-in functions that operate on the results of a SELECT:

function Name:	COUNT	SUM	AVG	MAX	MIN
Role:	Count	Sum	Averaging	Maximum Value	Minimum value

Where the Count function can be used for any data type (because it is just a count), and the SUM and AVG functions can only be computed for numeric class data types, MAX and MIN can be applied to numeric, string, or datetime data types.

Specific examples, such as calculating the maximum and minimum value of a salary, use a statement like this:

1 SELECT MAX (Salary) as max_salary,min (salary) from employee;

Use as keyword to rename values

8 Sub-query

The SELECT statement discussed above covers only the data in one table, but sometimes you have to work with multiple tables to get the information you need. For example: Want to know the department of the employee named "Tom" has done several projects. Employee information is stored in the employee table, but the project information is stored in the project table.

For such a situation, we can use subqueries:

1 SELECT Of_dpt,count (proj_name) as Count_project from Project 2 WHERE OF_DPT in 3 (SELECT IN_DPT from employee WHERE name='Tom');

Subqueries can also be extended to layers 3, 4, or more.

9 Connection Query

When working with multiple tables, subqueries are only useful when the results come from a table. However, if you need to display data from two or more tables, you must use a join operation.

The basic idea of a connection is to manipulate two or more tables as a new table, as follows:

1 SELECT Id,name,people_num 2 From employee,department 3 WHERE employee.in_dpt = department.dpt_name4 ORDER by ID;

The other Connection statement format is using the join on syntax, which is equivalent to the following statement:

1 SELECT Id,name,people_num 2 From employee JOIN Department 3 on employee.in_dpt = department.dpt_name4 the ORDER by ID;

The result is the same as the statement just now.

MySQL Notes (2)