【MySQL】 Basic operations for MySQL -01

The environment

CMD command line

MySQL 8.0.25

Enter the MySQL

I’m going to go to MySQL first, and I’m going to go to the command line.

Viewing all databases

Enter SHOW DATABASES; All view the database.

Creating a database

Enter CREATE DATABASE DATABASE name; Create a new database

I created a database called Test.

CREATE DATABASE test2; CREATE DATABASE test2;

Deleting a Database

DROP DATABASE test2; Delete:

As you can see, I forgot to type in; MySQL thinks I’m not finished. Much like Java, an instruction has to; The end is the end.

Accessing the database

Input the USE test; You can access the database that we created.

Create table

Enter CREATE TABLE… You can create tables in a database, a library can have many tables, but not directly create tables, must be created when the specification is defined:

We enter:

CREATE TABLE my_table (
	`my_id` INT UNSIGNED AUTO_INCREMENT,
	`my_title` VARCHAR(100) NOT NULL,
	`my_author` VARCHAR(40) NOT NULL,
	`submission_date` DATE,
	PRIMARY KEY ( `my_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
Copy the code

My_id indicates the name of the column, INT indicates the element type of the column, UNSIGNED indicates that the numeric type is UNSIGNED, and AUTO_INCREMENT indicates that the column element has an autoincrement attribute. VARCHAR(n) indicates a variable length string, where n is the number of characters. NOT NULL indicates that the column element does NOT allow NULL values.

DATE is a time and DATE variable in the format YYYY-MM-DD, for example, 2021-07-09. The PRIMARY KEY defines the column my_ID as the PRIMARY KEY, whose value uniquely identifies each row in the table. ENGINE sets the storage ENGINE, and CHARSET sets the encoding.

After typing, there is:

View all data tables

Enter SHOW TABLES; To view all tables in the current database:

Just one. Let’s create another one

CREATE TABLE my_table2 (
	`my_id` INT UNSIGNED AUTO_INCREMENT,
	`my_title` VARCHAR(100) NOT NULL,
	`my_author` VARCHAR(40) NOT NULL,
	`submission_date` DATE,
	PRIMARY KEY ( `my_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
Copy the code

Then SHOW TABLES; Kangkang:

The watch becomes two.

Delete table

Enter DROP TABLE my_table2;

Look at:

There’s only one data table left.

Insert data

Enter the following to pass data to my_table:

INSERT INTO my_table (my_title, my_author, submission_date) VALUES (" 新 高 心 ", "新 高 心 ", NOW());Copy the code

Insert data into mysql

1) Insert into (primary key, primary key, primary key);

2) replace into indicates insert into the database. If there is a PrimaryKey or unique index in the database, replace into with new data. If there is no data in the database, insert into the database.

3) insert ignore into indicates that the current new data is ignored if the same record already exists.

View table data

Enter SELECT * FROM my_table; View all data in the table,

It’s kind of like Excel. The NOW() function automatically retrieves the current time.

Let’s go ahead and insert some data:

INSERT INTO my_table (my_title, my_author, submission_date) VALUES (" 新 ", "新 ", NOW());Copy the code

INSERT INTO my_table (my_title, my_author, submission_date) VALUES (" 21-07-09", "21-21-07-09 ");Copy the code

INSERT INTO my_table (my_title, my_author, submission_date) VALUES (" pipa ", "baijui ", NOW());Copy the code

Then enter SELECT * FROM my_table; Take a look at:

It’s starting to look a little bit better now.

We can also view only certain columns, such as my_title column, SELECT my_title FROM my_table; There are:

Conditional search WHERE clause

SELECT * FROM my_table WHERE my_author=” “; You can restrict the query conditions:

Only data from my_author=” Du Fu “is displayed.

SELECT * FROM my_table WHERE my_id <= 2;

Only data whose my_id is less than or equal to 2 is displayed.

SELECT * FROM my_table WHERE my_id BETWEEN 1 AND 2; :

Look only at my_id in the range 1 to 2.

Note: ON is similar to WHERE, but the ON clause filters what the join table displays and does not affect the number of entries in the query result. The WHERE clause filters the results of multiple join tables so that the records that meet the criteria are retained, so it affects the final query count.

UPDATE data

UPDATE my_table SET my_title=’ ch_title ‘WHERE my_id=3; , update data:

SELECT * FROM my_table; :

We change the line id=3, happy rain on spring Night into spring hope.

DELETE data DELETE

DELETE FROM my_table WHERE my_id=3; DELETE FROM my_table WHERE my_id=3;

Look at theSELECT * FROM my_table;:

The row with id=3 is deleted.

UPDATE and DELETE can be dangerous operations. If you do not add WHERE, DELETE deletes all rows.

DELETE FROM my_table;

SELECT * FROM my_table; :

It’s gone. It’s all gone.

Let’s add them back.

INSERT INTO my_table (my_title, my_author, submission_date) VALUES (" 新 高 心 ", "新 高 心 ", NOW());Copy the code

INSERT INTO my_table (my_title, my_author, submission_date) VALUES (" 新 ", "新 ", NOW());Copy the code

INSERT INTO my_table (my_title, my_author, submission_date) VALUES (" 21-07-09", "21-21-07-09 ");Copy the code

INSERT INTO my_table (my_title, my_author, submission_date) VALUES (" pipa ", "baijui ", NOW());Copy the code

My_id can be seen as being incremented from 5.

The LIKE clause

The WHERE clause can specify any condition. LIKE is the same thing as =.

The %X card matches characters ending in X.

SELECT * FROM my_table WHERE my_author LIKE '%甫';

We find all characters in my_author that end in ‘just’.

The UNION operator

SHOW TABLES;To display our table, there is only one:Let’s add a table, my_table2:

CREATE TABLE my_table2 (
	`my_id` INT UNSIGNED AUTO_INCREMENT,
	`my_title` VARCHAR(100) NOT NULL,
	`my_author` VARCHAR(40) NOT NULL,
	`submission_date` DATE,
	PRIMARY KEY ( `my_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
Copy the code

Insert data into my_table2:

INSERT INTO my_table2 (my_title, my_author, submission_date) VALUES (" 新 高 三 山 ", "新 高 三 山 ", NOW());Copy the code

INSERT INTO my_table2 (my_title, my_author, submission_date) VALUES (" 中 山 高 三 级 ", "中 山 高 三 级 ", NOW());Copy the code

INSERT INTO my_table2 (my_title, my_author, submission_date) VALUES ("长 城 歌 ", "baijui ", NOW());Copy the code

INSERT INTO my_table2 (my_title, my_author, submission_date) VALUES (" 月 ", "月 "," 月 ", NOW());Copy the code

See my_table2:

SELECT * FROM my_table2;

UNIONYou can combine the twoSELECTOr moreSELECTThe simplest way to combine the results of

SELECT * FROM my_table
UNION
SELECT * FROM my_table2;
Copy the code

Or it

SELECT * FROM my_table WHERE my_author=" "UNION SELECT * FROM my_table2 WHERE my_author=" ";Copy the code

Let’s pick out my_author=” Du Fu “.

You can also use LIKE with %X% to match all characters containing X

SELECT * FROM my_table WHERE my_author LIKE '% white %' UNION SELECT * FROM my_table2 WHERE my_author LIKE '% white %';Copy the code

Look for lines with “white” in the my_author name.

The AND operator

We can add conditions to WHERE with AND. These conditions have logic AND relationships:

SELECT * FROM my_table2 WHERE my_author LIKE '% lee %' AND my_author LIKE '% white %'; SELECT * FROM my_table2 WHERE my_author LIKE '% lee %' AND my_author LIKE '% white %';

Find all columns in my_table2 where my_author contains “lee” and contains “white” characters.

The OR operator

Conditions can be added to WHERE with OR. These conditions have logic OR relationships:

SELECT * FROM my_table2 WHERE my_author LIKE '% lee %' OR my_author LIKE '% du %';

ANDOperator priority ratioORIt has to be high. If I don’t put it in parentheses,ANDThe statement will be executed first.

The IN operator

For illustration purposes, let’s review my_table and my_table2:

SELECT * FROM my_table;

SELECT * FROM my_table2;

Let’s add some more data to my_table2:

INSERT INTO my_table2 (my_title, my_author, submission_date) VALUES (" chibi ", "sushi ", NOW());Copy the code

INSERT INTO my_table2 (my_title, my_author, submission_date) VALUES (" 中 华 中 心 ", "sushi ", NOW());Copy the code

Look at the my_table2 dataSELECT * FROM my_table2;:

The IN operator can represent an inclusion relation. The parentheses IN() specify a range of conditions, such as:

SELECT my_id, my_title, my_author FROM my_table2 WHERE my_author IN(SELECT my_author FROM my_table);

First we specify query objects my_id, my_title, my_author, not submission_date.

Second, we pass WHERE.. IN(…) In my_table2, we only query my_authors that have an intersection with my_author of my_table

We can also use more complex conditions IN() :

SELECT my_id, my_title, My_author FROM my_table2 WHERE my_author IN(SELECT my_author FROM my_table WHERE my_author LIKE '% lee %');

The NOT operator

The NOT operator can be used to negate a condition, acting as a logical non.

We found the same parts of my_table2 and my_author IN my_table using IN. The NOT operator can find different parts

SELECT * FROM my_table2 
WHERE my_author NOT IN(SELECT my_author FROM my_table);
Copy the code

NOT is the opposite of IN, BETWEEN, and EXISTS.

Sort ORDER BY

Before we get to sorting, let’s create another table with ids that don’t increment:

CREATE TABLE my_table3 (
	`my_id` INT UNSIGNED,
	`my_title` VARCHAR(100) NOT NULL,
	`my_author` VARCHAR(40) NOT NULL,
	`submission_date` DATE,
	PRIMARY KEY ( `my_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
Copy the code

INSERT INTO my_table3 (my_id, my_title, my_author, submission_date) VALUES (2, "第 三 册 ", "2017-04-12"), (1, "Drink lake attendance after the rain", "su shi", "2017-04-12"), (4, "operator", "su shi", "2015-05-01"), (3, "recent", "su shi", "2016-03-06");Copy the code

So if we look at this query, we can see that there’s no auto-increment, but we’re sorting by primary key because we’ve set primary key.

ORDER BY lets you reorder the table, for example BY ascending submission_date.

SELECT * FROM my_table3 ORDER BY submission_date ASC;

For example, sort by descending order my_id.

SELECT * FROM my_table3 ORDER BY my_id DESC;

reference

MySQL > MySQL

MySQL Must Know must Know