Mysql basics: You must Know, you must Know

“This is the 16th day of my participation in the November Gwen Challenge. See details of the event: The Last Gwen Challenge 2021”.

The sample table

mysql> DESC one_piece;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | char(10)    | NO   |     | NULL    |       |
| pirates | char(10)    | NO   |     | NULL    |       |
| name    | char(10)    | NO   |     | NULL    |       |
| age     | int(11)     | YES  |     | NULL    |       |
| post    | varchar(10) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
Copy the code

Pick up where you left off!

Insert data

Insert full row

Inserting a full row with INSERT Requires specifying the table name and the value inserted into the new row.

mysql> INSERT INTO one_piece
    -> VALUES('1'.->        The Straw Hat Pirates.->        'luffy'.->        'age'.->        '团长'.->        '1500000000');
Copy the code

Pay attention to

One value must be supplied per column, null values are usedNULL
Columns must be populated in the order in which they appear in the table definition

Insert part row

The recommended INSERT method is to specify the column names of the table. You can also omit columns, that is, provide values for some columns and none for others.

The omitted column must satisfy one of the following conditions:

This column is defined as allowedNULLValue (no value or null value).
Give a default value in the table definition (if no value is given, the default value will be used).

If NULL or default values are not allowed in the table and the table value is omitted, the DBMS will generate an error message and the corresponding row cannot be successfully inserted.

Now insert a row into the one_piece table as well.

mysql> INSERT INTO one_piece(id,
    ->                       pirates,
    ->                       name)
    -> VALUES('1'.->        The Straw Hat Pirates.->        'luffy');
Copy the code

No matter which INSERT syntax is used, the number of VALUES must be correct. If you do not provide column names, you must provide a value for each table column; If column names are provided, each column listed must be given a value. Otherwise, an error message is generated and the corresponding row cannot be inserted successfully.

Copy from one table to another

There is one type of data insertion that does not use INSERT statements. You want to copy the contents of a table to a brand new table (the table created in the run).

mysql> CREATE TABLE one_pieceCopy AS
    -> SELECT * FROM one_piece;
Copy the code

anySELECTBoth options and clauses can be used, includingWHERE 和 GROUP BY.
Joins can be used to insert data from multiple tables.
No matter how many tables you retrieve data from, data can only be inserted into one table.

Why it’s useful: It’s a great tool for copying tables before experimenting with new SQL statements. Copying first allows you to test your SQL code on the replicated data without affecting the actual data.

Second, update data

UPDATE (modify) the data in the table using the UPDATE statement. There are two ways to use UPDATE:

Updates a specific row in a table
Update all rows in the table

Use it explicitly to update specific rows or all rows.

Subqueries can be used in the UPDATE statement to UPDATE column data with data retrieved from the SELECT statement.

Update single row single column

Update Luffy’s bounty to 10000000000

mysql> UPDATE one_piece
    -> SET bounty = 10000000000
    -> WHERE name = 'luffy';
Copy the code

Update single row and multiple columns

When updating multiple columns, only one SET command is used, with each column = value pair separated by a comma (no comma is used after the last column).

mysql> UPDATE one_piece
    -> SET bounty = 10000000000.->     age = 'the'
    -> WHERE name = 'luffy';
Copy the code

Update all rows

Do not use the WHERE constraint, which updates all rows in the table.

mysql> UPDATE one_piece
    -> SET bounty = 10000000000.->     age = 'the'
Copy the code

Deletes the value in the column

If the table definition allows NULL, you can set the value of a column to NULL if you want to delete it. (Note the difference between deleting a column value (preserving the column structure) and deleting a column (deleting it completely).)

mysql> UPDATE one_piece
    -> SET bounty = NULL
    -> WHERE name = 'luffy';
Copy the code

Delete data

DELETE data from a table using a DELETE statement.

There are two ways to use DELETE:

Deletes a specific row from a table
Delete all rows from the table

Use it explicitly to delete specific rows or all rows.

To delete a single

Delete row whose name is luffy from one_piece.

mysql> DELETE FROM one_piece
    -> WHERE name = 'luffy';
Copy the code

Delete all rows

Delete all lines from Customers. Do not drop the table itself.

mysql> DELETE FROM one_piece;
Copy the code

If you want to delete all rows from a TABLE, it is recommended to use the TRUNCATE TABLE statement, which does the same job but is faster (because data changes are not recorded).

Note that TRUNCATE is a data definition language (DDL) and cannot be rolled back after running the TRUNCATE command. Therefore, back up data in the current table before running the TRUNCATE command.

mysql> TRUNCATE TABLE one_piece;
Copy the code

Four, constraints,

The constraint

A DBMS enforces referential integrity by imposing constraints on database tables. Most constraints are defined in TABLE definitions, using either CREATE TABLE or ALTER TABLE statements.

A primary key

A primary key is a special constraint that ensures that the values in a column (or group of columns) are unique and never change. Without a primary key, it can be very difficult to safely UPDATE or DELETE a particular row without affecting other rows.

Primary key conditions:

Any two rows have different primary key values.
Each row has a primary key value (that is, not allowed in the columnNULLValue).

Define the primary key when creating the table.

CREATE TABLE teacher
(
	id INT(11) PRIMARY KEY,
	teacher_name VARCHAR(10));Copy the code

Add a primary key using ALTER TABLE.

ALTER TABLE teacher
ADD CONSTRAINT PRIMARY KEY(id);
Copy the code

Delete the primary key constraint.

ALTER TABLE teacher DROP PRIMARY KEY;
Copy the code

A foreign key

A foreign key is a column in a table whose value must be listed in the primary key of another table. Foreign keys are an extremely important part of ensuring referential completeness.

Select teacher_id from teacher_id where teacher_id = teacher_id and teacher_id = teacher_id;

Define foreign keys at table creation time.

CREATE TABLE student
(
	stu_id INT(11) PRIMARY KEY,
	teacher_id INT(11) REFERENCES teacher(id),
	stu_name VARCHAR(10));Copy the code

Add a foreign key using ALTER TABLE.

ALTER TABLE student
ADD CONSTRAINT teacher_id_id
FOREIGN KEY (teacher_id) REFERENCES teacher(id);
Copy the code

The use of foreign keys can effectively prevent accidental deletes, such as deleting information from the teacher table in the above two tables, if the id is also present in the student table, Mysql prevents the delete operation. Of course, you can also enable the cascading delete feature, so that all relevant information is deleted when deleted.

Remove the foreign key

ALTER TABLE student DROP FOREIGN KEY teacher_id_id;
Copy the code

The only constraints

Unique constraints are used to ensure that the data in a column (or set of columns) is unique. They are similar to primary keys, but with the following important differences.

Tables can contain multiple unique constraints, but only one primary key is allowed per table.
A unique constraint column may containNULLValue.
Unlike primary keys, unique constraints cannot be used to define foreign keys.

Define unique constraints when creating a table.

CREATE TABLE student
(
	stu_id INT(11) PRIMARY KEY,
	teacher_id INT(11) REFERENCES teacher(id),
	stu_name VARCHAR(10));Copy the code

Add unique constraints using ALTER TABLE.

ALTER TABLE student
ADD CONSTRAINT unique_id UNIQUE(stu_id);
Copy the code

Remove the uniqueness constraint.

ALTER TABLE student DROP INDEX unique_id;
Copy the code

The check constraint

Check constraints are used to ensure that data in a column (or set of columns) meets a specified set of conditions.

Common uses:

Check the minimum or maximum.
Specify the scope.
Only specific values are allowed.

Let’s create a check constraint to restrict the gender column to male and female.

Define check constraints when creating a table.

CREATE TABLE student
(    
    stu_id INT(11) PRIMARY KEY,
	gender VARCHAR(1) CHECK(gender IN('male'.'woman')));Copy the code

Add check constraints using ALTER TABLE.

ALTER TABLE student ADD CONSTRAINT check_gender CHECK(gender in ('male'.'woman'));
Copy the code

Delete check constraints.

ALTER TABLE student DROP CHECK check_gender;
Copy the code

The foundation to the end of today, the content is more basic, advanced take time to do!

This is what I want to share today. Search Python New Horizons on wechat, bringing you more useful knowledge every day. More organized nearly a thousand sets of resume templates, hundreds of e-books waiting for you to get oh!