-sql study of five knowledge systems-day three

1. SQL Constraint action

PRIMARY KEY constraint: guarantees the validity of the inserted data.
For example, the sex column, can only be "male" "female",
The input "ABC" is invalid, so you can add a constraint
ALTER TABLE table name
Add constraint chk_sex Check (gender in (' Male ', ' female ') is constrained.

FOREIGN KEY constraints: the role of establishing a foreign key is to ensure the integrity of the data, as in table A has a primary key AA field, table B has a BB field, when a is the main table, B is from the table, the primary key is the foreign key from the table, that is, the BB field in the B table from the AA field. Limit constraints to achieve completeness on the data.

eg
Classes and students are a one-to-many relationship, and the student table is constrained by the primary key (CLASS_ID) in the class table (class) as the foreign key to the student table (student).
ALTER TABLE student
Add constraint Fk_student_class foreign key (class_id) references class (CLASS_ID)
(Note: The Foreign key field in the student table must be consistent with the field data type in the class table as the foreign key, but its name can be different)

2. Index

Concepts: indexes, which use indexes to quickly access specific information in a database table. An index is a structure that sorts the values of one or more columns in a database table.

#创建普通索引

CREATE INDEX Index_userid on userbase (userid);

ALTER TABLE userbase ADD INDEX index_username (username);

#创建唯一索引-The unique index is that when you create an index, the limit index value must be a unique value, and the index of that type can query a record more quickly.

Note: When you create a unique index, the unique index creates a failure if the field in the table has a null value. 】

CREATE UNIQUE INDEX Index_userid on userbase (userid);
ALTER TABLE userbase ADD UNIQUE INDEX index_username (username);

#创建全文索引-Full-text indexing is primarily associated with fields of the data type char, VARCHAR, and text in the database, so that you can query the field of a large string type more quickly.

CREATE INDEX Index_userid on userbase (userid);
ALTER TABLE userbase ADD UNIQUE INDEX index_username (username);

3. Mysql operator

Arithmetic operators: + 、-、 *,/, (DIV),%, (MOD)--note:/and DIV are Division operators,/Results save four decimal results by default, DIV saves integer results by default.

Eg:select * FROM table where the F1 is null;--f1 null data.

Comparison operators:

4. Regular expressions

--Eg:

A. SELECT '/webproxy/src/main/webapp/css/report.css ' REGEXP ' ^/web.*css$ ';

SELECT '/webproxy/src/main/webapp/css/report.css ' REGEXP ' ^/web|css$ ';

B. The query finds all the names start with a vowel and ' OK ' ends:

SELECT name from Person_tbl WHERE NAME REGEXP ' ^[aeiou]|ok$ '; --"|" for or relationship

De|abc

Match de or ABC

^ matches the beginning of the string

$ Match ending part of string

. Match any one of the characters in the string

[Character set] matches any character in a character set

[^ Character set] matches any character in a character set

str1|str2|str3 matches any string of str1, STR2, STR3

* match characters, including 0 and one

+ Match character, contains 1

string {n} string N times

string {m,n} string appears at least M times and occurs up to N times

4.0 Regular Expression Match validation

The regexp check always returns 0 (no match) or 1 (match), you can test the expression with a text string of RegExp , and experiment with them.

Eg:select '/webproxy/src/main/webapp/css/report.css ' REGEXP ' ^/web ';

4.1 $ can be compared with a specific character or the end of a string

Eg:select * from userbase WHERE username REGEXP ' 5$ ' ORDER by username; --can be used for matching purposes.

-sql study of five knowledge systems-day three