Field Constraints and Functions

1. Field Constraints

1. What is the purpose of field constraints?

Constraints are conditional restrictions imposed on data by a database to ensure that the data meets business rules.

2 What are the types of field constraints?

  1. Primary Key Constraint (PRIMARY KEY)

  2. Uniqueness Constraint (UNIQUE)

  3. Non-null constraint (NOT NULL)

  4. Check Constraints (CHECK)

  5. Foreign Key Constraint (FOREIGN KEY)

Here we will only detail the primary key constraints, unique constraints, and non-null constraints commonly used in development.

1.1 Primary Key Constraint

  1. Primary key constraint: If a primary key constraint is added to a column, the column is the primary key. The primary key is unique and cannot be empty. Normally, each table has a primary key.
  2. Add a primary key constraint, such as setting id as the primary key:
create table abc(
id int primary key auto_increment
);
insert into abc values(null);
insert into abc values(null);
insert into abc values(null);
select * from abc;

  1. Primary key auto-increment policy: When the primary key is a numeric type, you can set a primary key auto_increment policy for easy maintenance., after setting the primary key auto-increment policy, the database will save an AUTO_INCREMENT variable value in the table with an initial value of 1. When an id value is required and we do not need to specify it, the database is responsible for getting an id value from AUTO_INCREMENT and inserting it into the table as a primary key value. Each time the AUTO_INCREMENT value is used up, it will increase by 1. AUTO_INCREMENT=1

1.2 Non-null Constraint

  1. Non-empty constraint: If a non-empty constraint is added to a column, the value of the column cannot be empty, but it can be repeated.

  2. Add a non-empty constraint, such as a non-empty constraint for password:

create table user(
id int primary key auto_increment,
password varchar(50) not null
);
show tables;
insert into user values(null,null);//Non-empty constraint not met
insert into user values(null,123;);//OK

1.3 Unique Constraint

  1. Unique constraint: If a unique constraint is added to a column, the value of that column must be unique (that is, it cannot be repeated), but it can be empty.

  2. Add unique constraints, such as unique constraints and non-empty constraints for username:

create table test(
id int primary key auto_increment,
username varchar(50) unique--Unique Constraint
);
show tables;
insert into test values(null,'lisi');
insert into test values(null,'lisi');--username Make the value unique,Repeated errors
select * from test;

1.4 Check Constraints (CHECK, CK)

  1. Check constraints refer to setting some filter conditions on a data column so that it can be saved when the filter conditions are met or an error occurs if they are not.
  2. Syntax format: CHECK <expression>
    <expression>refers to an SQL expression that specifies the qualifying conditions that need to be checked.
  3. If a CHECK constraint clause is placed after the definition of a column in a table, this constraint is also known as a column-based CHECK constraint.
  4. When updating table data, the system checks whether the updated rows satisfy the restrictions in the CHECK constraint. MySQL can use simple expressions to implement the CHECK constraint, or complex expressions to allow restrictions, such as adding subqueries to the restrictions.

Note: CHECK constraint clauses are also referred to as table-based CHECK constraints if they are placed after all column definitions, primary key constraints, and foreign key definitions. This constraint can set constraints on multiple columns in a table at the same time.

  1. Use:
    The syntax rules for setting check constraints when creating tables are as follows:
    CHECK (<Check Constraints>)
    [Instance 1] Creating a tb_emp7 data table in the test_db database requires the salary field value to be greater than 0 and less than 10,000, and the SQL statement entered and the results of the run are shown below.

    mysql> CREATE TABLE tb_emp7
    -> (
    -> id INT(11) PRIMARY KEY,
    -> name VARCHAR(25),
    -> deptId INT(11),
    -> salary FLOAT,
    -> CHECK(salary>0 AND salary<100),
    -> FOREIGN KEY(deptId) REFERENCES tb_dept1(id)
    -> );
    Query OK, 0 rows affected (0.37 sec)
    

    The syntax rules for setting check constraints when modifying tables are as follows:
    ALTER TABLE tb_emp7 ADD CONSTRAINT <Check Constraint Name> CHECK (<Check Constraint>)
    [Instance 2] Modify the tb_dept data table, requiring the id field value to be greater than 0, and the SQL statement entered and the run results are shown below.

    mysql> ALTER TABLE tb_emp7
        -> ADD CONSTRAINT check_id
        -> CHECK(id>0);
    Query OK, 0 rows affected (0.19 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    

    The syntax rules for deleting check constraints when modifying tables are as follows:

    ALTER TABLE <Data Table Name> DROP CONSTRAINT <Check Constraint Name>;
    

1.5 Foreign Key Constraint (FOREIGN KEY)

The MySQL foreign key constraint (FOREIGN KEY) is used to establish links between data in two tables, which can be one column or multiple columns. A table can have one or more foreign keys.

2. Basic Functions

lower

SELECT 'ABC',LOWER('ABC') from dept; --Data to lowercase

upper

select upper(dname) from dept --Uppercase Data

length

select length(dname) from dept --Length of data

substr

SELECT dname,SUBSTR(dname,1,3) FROM dept; --Intercept[1,3]

concat

select dname,concat(dname,'123') X from dept --Stitching data

replace

select dname,replace(dname,'a','666') X from dept --hold a Replace characters with 666

ifnull

select ifnull(comm,10) comm from dept2 #Judging, if comm is null, replace with 10

round & ceil & floor

round rounds, ceil rounds up, floor rounds down

  • Rounding directly
select comm,round(comm) from emp
  • Round and reserve a decimal
select comm,round(comm,1) from emp
  • ceil rounds up, floor rounds down
select comm,ceil(comm) ,floor(comm) from emp

uuid

SELECT UUID()
Return results uuid: a6b836ca-1573-11ec-915d-107b44337844

now

select now() -- Years, Days, Hours and Seconds 
Return results now(): 2021-09-14 23:52:45

select curdate() --Year and Day
 Return results curdate(): 2021-09-14
select curtime() --Hours and seconds
 Return results curtime(): 23:55:37

year & month & day

  • minute() minutes second() seconds at hour()
select now(),hour(now()),minute(now()),second(now()) from customer ;
  • year() year month() month day() day
select now(),year(now()),month(now()),day(now()) from customer ;

Escape Character

 ' As sql Sentence symbols, single-handed content will be disorderly, can be escaped

select 'ab'cd' -- Single quotation mark is a SQL Special characters of statements

select 'ab\'cd' --When there are single quotes in the data,Use one\Escape to Normal Character

Tags: Database SQL

Posted on Tue, 14 Sep 2021 16:56:02 -0400 by pandhandp