SQL-regular expressions and constraints

The usage of regular expressions is very flexible. You can choose appropriate characters, special characters, quantifiers, groupings, etc. according to specific needs to build matching rules. At the same time, regular expressions also support some advanced features, such as greedy mode, non-greedy mode, modifiers, etc., which can further expand the functions of regular expressions.

There are many detailed uses of regular expressions. You can choose the appropriate regular expression and corresponding function to operate according to specific needs.

1. Operation 1

The code is as follows (example):

-- ^ matches at the beginning of the string
SELECT 'abc' REGEXP '^a';
-- $ starts matching at the end of the string
SELECT 'abc' REGEXP 'a$';
SELECT 'abc' REGEXP 'c$';
-- . matches any character
SELECT 'abc' REGEXP '.b';
SELECT 'abc' REGEXP '.c';
SELECT 'abc' REGEXP 'a.';
-- [...] matches any single character within brackets
SELECT 'abc' REGEXP '[xyz]';
SELECT 'abc' REGEXP '[xaz]';

2. Operation 2

The code is as follows (example):

-- [^...] Note that ^ matches the meaning of negation only within [], and elsewhere it means matching at the beginning
SELECT 'a' REGEXP '[^abc]';
SELECT 'x' REGEXP '[^abc]';
SELECT 'abc' REGEXP '[^a]';
-- a* matches 0 or more a's, including the empty string. Can be used as a placeholder. Can be matched with or without specified characters.
to data
SELECT 'stab' REGEXP '.ta*b';
SELECT 'stb' REGEXP '.ta*b';
SELECT '' REGEXP 'a*';
-- a + matches 1 or more a, but does not include the null character
SELECT 'stab' REGEXP '.ta + b';
SELECT 'stb' REGEXP '.ta + b';

3. Operation 3

The code is as follows (example):

-- [^...] Note that ^ matches the meaning of negation only within [], and elsewhere it means matching at the beginning
SELECT 'a' REGEXP '[^abc]';
SELECT 'x' REGEXP '[^abc]';
SELECT 'abc' REGEXP '[^a]';
-- a* matches 0 or more a's, including the empty string. Can be used as a placeholder. Can be matched with or without specified characters.
to data
SELECT 'stab' REGEXP '.ta*b';
SELECT 'stb' REGEXP '.ta*b';
SELECT '' REGEXP 'a*';
-- a + matches 1 or more a, but does not include the null character
SELECT 'stab' REGEXP '.ta + b';
SELECT 'stb' REGEXP '.ta + b';

4. Operation 4

The code is as follows (example):

-- a{m,n} matches m to n a, including m and n
SELECT 'auuuuc' REGEXP 'au{3,5}c';
SELECT 'auuuuc' REGEXP 'au{4,5}c';
SELECT 'auuuuc' REGEXP 'au{5,10}c';
-- (abc) abc is used as a sequence match. If it is not enclosed in parentheses, it will be matched with a single character. If you want to match multiple characters
Matching as a whole requires the use of parentheses, so parentheses are suitable for all the above situations.
SELECT 'xababy' REGEXP 'x(abab)y';
SELECT 'xababy' REGEXP 'x(ab)*y';
SELECT 'xababy' REGEXP 'x(ab){1,2}y';

2. Constraints

SQL constraints are rules used to limit the integrity and consistency of data in a database table. They define the conditions and restrictions allowed for operations on the data in the table. Constraints can be applied to columns of a table or to the entire table.

Classification of constraints:

1. Primary Key Constraint: Used to identify unique records in the table. Primary key constraints require that the values in the column are unique and not null.
2. Foreign Key Constraint: used to establish relationships between tables. Foreign key constraints require that the value of a column in one table must be the primary key value of another table.
3. Unique Constraint: Used to ensure that the values in the column are unique. A unique constraint requires that the values in the column cannot be repeated.
4. Not Null Constraint: Used to ensure that the value in the column is not null. The non-null constraint requires that the value in the column cannot be NULL.
5. Check Constraint: Used to define a range or condition for values in a column. A check constraint requires that the values in a column meet specified conditions.

The role of constraints:

1. Data integrity: Constraints can ensure that the data in the table meets specific conditions and rules, preventing invalid or inconsistent data from entering the database.
2. Data consistency: Constraints can ensure that the relationships between tables are correct and avoid data redundancy and inconsistency.
3. Data security: Constraints can prevent illegal operations on data in the table and protect data security.

Usage of constraints:

1. Create constraints: When creating a table, you can use the CREATE TABLE statement to define constraints. For example, you can use the PRIMARY KEY keyword to define primary key constraints, the UNIQUE keyword to define unique constraints, and the FOREIGN KEY keyword to define foreign key constraints, etc.
2. Modify constraints: You can use the ALTER TABLE statement to modify the constraints of the table. For example, you can use the ADD CONSTRAINT keyword to add new constraints, use the DROP CONSTRAINT keyword to delete constraints, etc.
3. Disabling constraints: Constraints can be disabled using the ALTER TABLE statement. For example, you can use the DISABLE CONSTRAINT keyword to disable a constraint so that you can operate around the constraint if needed.
4. Enable constraints: Constraints can be enabled using the ALTER TABLE statement. For example, you can use the ENABLE CONSTRAINT keyword to enable a constraint so that it can be re-enabled after being disabled.

To summarize, SQL constraints are rules used to limit the integrity and consistency of data in a database table. They can be applied to table columns or the entire table to ensure data validity and security by defining specific conditions and rules.

1. Primary key constraints

Add a single column primary key

Method 1:
--In the create table statement, pass the primary key
-- Specify the primary key while defining the field. The syntax is as follows:
create table table name (
...
<field name> <data type> primary key
...
)

Way 1- Implement:
create table emp1(
eid int primay key,
name VARCHAR(20),
deptId int,
salary double
);

Method 2:
--Specify the primary key after defining the field. The syntax format is as follows:
create table table name (
...
[constraint <constraint name>] primary key [field name]
);

Way 2- Implementation:
create table emp2(
eidINT,
name VARCHAR(20),
deptId INT,
salary double,
constraint pk1 primary key(id)
);

Add a multi-column primary key (joint primary key)

Syntax:
create table table name (
...
primary key (field 1, field 2, ..., field n)
);

accomplish:
create table emp3(
namevarchar(20),
deptId int,
salary double,
primary key(name,deptId)
);

Add a primary key by modifying the table structure

Primary key constraints can be created not only when creating the table, but also when modifying the table.

grammar;
create table table name (
...
);
alter table <table name> add primary key (field list);

accomplish:
-- Add single column primary key
create table emp4(
eid int,
namevarchar(20),
deptId int,
salary double,
);
alter table emp4 add primary key (eid);

Delete primary key constraints

Format:
alter table <data table name> drop primary key;

accomplish:
-- Delete single column primary key
alter table emp1 drop primary key;
-- Delete the joint primary key
alter table emp5 drop primary key;

2. Self-growth constraint

In MySQL, when the primary key is defined as self-increasing, the value of the primary key no longer requires the user to enter data, but is automatically assigned by the database system according to the definition. Each time a record is added, the primary key will automatically increase with the same step size.
Implement primary key auto-increment by adding the auto_increment attribute to the field

Syntax:
Field name data type auto_increment

accomplish:
create table t_user1(
id int primary key auto_increment,
namevarchar(20)
);

Features:

• By default, the initial value of auto_increment is 1. Every time a new record is added, the field value is automatically increased by 1.
• Only one field in a table can use the auto_increment constraint, and the field must have a unique index to avoid repeated serial numbers (that is, the primary key or part of the primary key).
• Fields constrained by auto_increment must have the NOT NULL attribute. Fields constrained by auto_increment can only be integer types (TINYINT, SMALLINT, INT, BIGINT, etc.).
• The maximum value of the auto_increment constraint field is constrained by the data type of the field. If the upper limit is reached, auto_increment will fail.

Specify the initial value of the auto-increment field

--Method 1, specified when creating the table
create table t_user2 (
id int primary key auto_increment,
namevarchar(20)
)auto_increment=100;

--Method 2, specify after creating the table
create table t_user3 (
id int primary key auto_increment,
namevarchar(20)
);
alter table t_user2 auto_increment=100;

Changes in delete and truncate’s auto-incremented columns after deletion:
delete: The data will automatically grow starting from the breakpoint.
truncate: The data will automatically grow from the default starting value.

3. Non-null constraint

Syntax:
Method 1: <field name><data type> not null;
Method 2: alter table table name modify field type not null;

Add non-null constraint
--Method 1, specified when creating the table
create table t_user6 (
id int ,
name varchar(20) not null,
address varchar(20) not null
);

--Method 2, specify later
create table t_user7 (
id int ,
name varchar(20) , -- specifies a non-null constraint
address varchar(20) -- specifies a non-null constraint
);
alter table t_user7 modify name varchar(20) not null;
alter table t_user7 modify address varchar(20) not null;

Remove non-null constraints:
-- alter table table name modify field type
alter table t_user7 modify name varchar(20);
alter table t_user7 modify address varchar(20);

4.Unique constraint

The code is as follows (example):

Syntax:
Method 1: <field name> <data type> unique
Method 2: alter table table name add constraint constraint name unique(column);

Adding a unique constraint - Way 1
-- Specified when creating the table
create table t_user8 (
id int ,
name varchar(20) ,
phone_number varchar(20) unique -- specifies unique constraints
);

Adding a unique constraint - Way 2
create table t_user9 (
id int ,
name varchar(20) ,
phone_number varchar(20) -- Specify unique constraints
);
alter table t_user9 add constraint unique_ph unique(phone_number);

Remove unique constraint
-- alter table <table name> drop index <unique constraint name>;
alter table t_user9 drop index unique_ph;

5.Default constraints

Syntax
Method 1: <field name> <data type> default <default value>;
Method 2: alter table table name modify column name type default default value;

Add default constraints - Way 1

create table t_user10 (
id int ,
name varchar(20) ,
address varchar(20) default Beijing’ -- specifies default constraints
);

Add default constraints - Way 2
--alter table table name modify column name type default default value
create table t_user11 (
id int ,
name varchar(20) ,
address varchar(20)
);
alter table t_user11 modify address varchar(20) default Beijing’;

Remove default constraints
-- alter table <table name> modify column <field name> <type> default null;
alter table t_user11 modify column address varchar(20) default null;

6. Zero padding constraints

• When inserting data, when the length of the field value is less than the defined length, the corresponding 0 will be added in front of the value.
• zerofill defaults to int(10)
• When using zerofill, the unsigned (unsigned) attribute is automatically added by default. After using the unsigned attribute, the value range is 2 times the original value. For example, signed is -128~ + 127, and unsigned is 0~256.

The code is as follows (example):

create table t_user12 (
id int zerofill, -- zero fill constraint
namevarchar(20)
);

delete
alter table t_user12 modify id int;

Summary

MySQL view
The above is what I will talk about today. I have learned about regular expressions and constraints, including regular expression characters and special characters, the classification of constraints, the role of constraints, and the usage of constraints.