Manipulate MySQL database 01/29 Update SLTechnology News&Howtos

Manipulate MySQL database

2025-01-29 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Shulou(Shulou.com)06/01 Report--

What is SQL?

SQL is a structured query language, a computer language used to store, manipulate, and retrieve data stored in relational databases.

SQL is the standard language of relational database system. All relational database management systems, such as MySQL, MS Access, Oracle, Sybase, Informix, postgres and SQL Server, use SQL as the standard database language.

In addition, they use different dialects, such as:

MS SQL Server uses T-SQL

Oracle uses PL/SQL

The SQL version of MS Access is called JET SQL (native format), etc.

Why use SQL?

Allows users to access data in a relational database management system.

Let the user describe the data.

Allows users to define data in the database and process data.

Allows embedding in other languages using SQL modules, libraries, and precompilers.

Allows users to create and delete databases and tables.

Allows users to create views, stored procedures, and functions in the database.

Allows users to set permissions for tables, procedures, and views

History:

1970-Dr. Edgar F. Coder of "Ted" IBM is called the father of relational database, who describes the relational model of database.

1974-structured query language appears.

1978-IBM collaborated on the idea of developing Codd and released a product called System/R.

1986-IBM developed the first prototype of a relational database and standardized it through ANSI. The first relational database was released by relational software and later Oracle.

SQL processing:

When you SQL any RDBMS, the system decides the best way to carry out your requirements and the SQL engine calculates how to parse the task.

There are various components included in the process. These components include query scheduling, optimization engine, classic query engine and SQL query engine and so on. The classic query engine processes all non-SQL queries, but the SQL query engine does not process logical files.

Here is a simple diagram showing the SQL architecture:

Sql command

Standard SQL commands are used for interaction in relational databases: CREATE, SELECT, INSERT, UPDATE, DELETE and DROP. These commands can be divided into groups based on their nature.

DDL-data definition language

Command describes CREATE to create a new table, a view of the table, or an object in the database ALTER to modify existing database objects, such as a table DROP to delete the entire table, tables or other objects or views in the database

DML-data manipulation language

Command describes SELECT retrieving a specific record from one or more tables INSERT creation record UPDATE modification record DELETE delete record

DCL-data control language

Command describes the permissions granted by GRANT to the user and REVOKE takes back the permissions granted by the user

MySQL data type

1. Integer

MySQL data type meaning (signed) tinyint (m) 1 byte range (- 128 '127) smallint (m) 2 byte range (- 32768' 32767) mediumint (m) 3 byte range (- 8388608 '8388607) int (m) 4 byte range (- 2147483648' 2147483647) bigint (m) 8 byte range (+-9.22'10 to the power of 18)

If the value range is added with unsigned, the maximum value is doubled, for example, the value range of tinyint unsigned is (0,256).

The m in int (m) represents the display width of the SELECT query result set, does not affect the actual range of values, does not affect the display width, I do not know what the use of this m.

2. Floating point type (float and double)

MySQL data type meaning float (m) single precision floating point precision (4 bytes) m total number, d decimal places double (m gravity d) double precision floating point precision (8 bytes) m total number, d decimal places

Let a field be defined as float. If you insert a number of 123.45678, what is actually stored in the database is 123.457, but the total number is still based on the actual number, that is, 6 digits.

3. Fixed point number

Floating-point types store approximate values in the database, while fixed-point types store exact values in the database.

We can provide sales representatives and updates for these customers.

To do this, you need to randomly select an employee whose position is Sales Rep from the employees table and update it to the employees table.

The following query randomly selects an employee whose position is Sales Rep from the employees table.

SELECT employeeNumberFROM employeesWHERE jobtitle = 'Sales Rep'ORDER BY RAND () LIMIT 1

To update the sales representative employee number (employeeNumber) column in the customers table, we put the above query in the SET clause of the UPDATE statement, as follows:

UPDATE customers SET salesRepEmployeeNumber = (SELECT employeeNumber FROM employees WHERE jobtitle = 'Sales Rep' LIMIT 1) WHERE salesRepEmployeeNumber IS NULL

If you query the data in the employees table after executing the above update statement, you will see that each customer has a sales representative. In other words, the following query does not return any row data.

SELECT salesRepEmployeeNumberFROM customersWHERE salesRepEmployeeNumber IS NULL

Delete table data

1. Introduction of MySQL DELETE statement

To delete data from a table, use the MySQL DELETE statement. The syntax of the DELETE statement is explained below:

DELETE FROM table_nameWHERE condition

In the above query statement

First, specify the table (table_name) to delete the data.

Second, use conditions to specify the row records to delete in the WHERE clause. If the rows match the criteria, the row records are deleted.

Note that the WHERE clause is optional. If you omit the WHERE clause, the DELETE statement deletes all rows in the table.

In addition to deleting data from the table, the DELETE statement returns the number of rows deleted.

To remove data from multiple tables using a single DELETE statement, read the DELETE JOIN statement that will be introduced in the next tutorial.

To delete all rows in the table without knowing how many rows have been deleted, you should use the TRUNCATE TABLE statement for better execution performance.

For tables with foreign key constraints, when row records are deleted from the parent table, the row records in the child table are automatically deleted by using the ON DELETE CASCADE option.

2. The example of MySQL DELETE

We will demonstrate using the employees table in the sample database (yiibaidb).

Note that once the data is deleted, it will disappear forever. Therefore, before executing the DELETE statement, you should back up the database in case you want to retrieve the deleted data.

Suppose you want to delete an employee with an officeNumber of 4, use the delete statement and the WHERE clause as the following query:

DELETE FROM employees WHERE officeCode = 4

To delete all rows in the employees table, use the DELETE statement without a WHERE clause, as follows:

DELETE FROM employees

After executing the above query, all rows in the employees table are deleted.

MySQL DELETE and LIMIT clauses

If you want to limit the number of rows to delete, use the LIMIT clause, as follows:

DELETE FROM tableLIMIT row_count

Note that the row order in the table is not specified, so when you use the LIMIT clause, you should always use the ORDER BY clause, otherwise the deleted record may not be what you expected.

DELETE FROM table_nameORDER BY c1, c2,... LIMIT row_count

Consider the customers table in the sample database (yiibaidb), which has the following table structure:

For example, the following statement sorts customers alphabetically by customer name and deletes the first 10 customers:

DELETE FROM customersORDER BY customerNameLIMIT 10

Similarly, the following DELETE statement selects customers in France (France), sorts them by credit line (creditLimit) in ascending order, and deletes the first five customers:

DELETE FROM customersWHERE country = 'France'ORDER BY creditLimitLIMIT 5

Similarly, the following DELETE statement selects customers in France (France), sorts them by credit line (creditLimit) in ascending order, and deletes the first five customers:

DELETE FROM customersWHERE country = 'France'ORDER BY creditLimitLIMIT 5

Query table record (select)

Query syntax:

SELECT * | field1,filed2... FROM tab_name WHERE conditional GROUP BY field HAVING filter ORDER BY field LIMIT limit the order in which Mysql executes sql statements:-- from where select group by having order by

Prepare data

CREATE TABLE emp (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR (20), gender ENUM ("male", "female", "other"), age TINYINT, dep VARCHAR (20), city VARCHAR (20), salary DOUBLE (7Magne2)) INSERT INTO emp (name,gender,age,dep,city,salary) VALUES ("yuan", "male", 24, "Ministry of Teaching", "Hebei Province", 8000), ("egon", "male", 34, "Ministry of Security", "Shandong Province", 8000), ("alex", "male", 28, "× × Ministry", "Shandong Province", 10000) ("Jingliyang", "female", 22, "Teaching Department", "Beijing", 9000), ("Zhang San", "male", 24, "Teaching Department", "Hebei Province", 6000), ("Li Si", "male", 32, "Security Department", "Beijing", 12000), ("Wang Wu", "male", 38 "Teaching Department", "Hebei Province", 7000), ("Zhao Liu", "male", 19, "Security Department", "Hebei Province", 9000), ("Pig Seven", "female", 24, "× × Department", "Beijing", 9000) SELECT * FROM emp;mysql > SELECT * FROM emp +-+ | id | name | gender | age | dep | city | salary | + -+ | 1 | yuan | male | 24 | Ministry of Education | Hebei Province | 8000.00 | 2 | egon | male | 34 | Ministry of Security | Shandong Province | 8000.00 | | 3 | alex | male | 28 | × × Department | Shandong Province | 10000. | | 4 | Jing Liyang | female | 22 | Teaching Department | Beijing | 9000.00 | 5 | Zhang San | male | 24 | Teaching Department | Hebei Province | 6000.00 | 6 | Lisi | male | 32 | Security Department | Beijing | 12000.00 | 7 | Wang Wu | male | 38 | Teaching Department | Hebei Province | 7000.00 | | | 8 | Zhao Liu | male | 19 | Ministry of Security | Hebei Province | 9000.00 | | 9 | Pig Qi | female | 24 | × × Bu | Beijing | 9000.00 | +-+ -+ rows in set (0.00 sec)

Where clause: filtering queries

The comparison operator can be used in where sentences: >

< >

= 24

Inquire about the information of male teachers in the teaching department

SELECT * FROM emp WHERE dep= "Teaching Department" AND gender= "male"

Order: sorting in

By the specified column, the sorted column can be either the column name in the table or the alias specified after the select statement.

Syntax:

Select * | field1,field2... From tab_name order by field [Asc | Desc]

Asc ascending, Desc descending, where asc is the default ORDER BY clause should be at the end of the SELECT statement.

Example:

Sort by age from highest to lowest

SELECT * FROM emp ORDER BY age DESC

Rank wages from low to high

SELECT * FROM emp ORDER BY salary

Group by: group query (*)

The GROUP BY statement groups the result set by a column. We can use COUNT, SUM, AVG and other functions to make related queries on the grouped columns.

Syntax:

SELECT column_name, function (column_name) FROM table_name WHERE column_name operator value GROUP BY column_name

Example:

-- inquire about the number of male and female employees

SELECT gender gender, count (*) number FROM emp5 GROUP BY gender

-- inquire about the number of people in each department

SELECT dep department, count (*) number of FROM emp5 GROUP BY dep

-- query the maximum age of each department

SELECT dep department, max (age) maximum age FROM emp5 GROUP BY dep

-- query the name of the oldest employee in each department

SELECT * FROM emp5 WHERE age in (SELECT max (age) FROM emp5 GROUP BY dep)

-- query the average salary of each department

SELECT dep department, avg (salary) maximum age FROM emp GROUP BY dep

-- inquire about the maximum wage of the staff of the Teaching Department:

SELECT dep,max (salary) FROM emp11 GROUP BY dep HAVING dep= "Teaching Department"

-- query departments with an average salary of more than 8000

SELECT dep,AVG (salary) FROM emp GROUP BY dep HAVING avg (salary) > 8000

-- query the employee names of each group

SELECT dep,group_concat (name) FROM emp GROUP BY dep

-- query the total number of employees in the company (you can treat all records as one group)

Total number of SELECT COUNT (*) employees FROM emp

-- KEY: each word after the query condition is the grouped field

Limit the number of limit records

SELECT * from ExamResult limit 1 select * from ExamResult limit 2 Magi 5;-- skip the first two to show the next five records SELECT * from ExamResult limit 2 Magi 2

Regular expression

SELECT * FROM employee WHERE emp_name REGEXP'^ yu';SELECT * FROM employee WHERE emp_name REGEXP 'yun$';SELECT * FROM employee WHERE emp_name REGEXP'm {2}'

Multi-table query

Create a tabl

CREATE TABLE emp (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR (20), salary DOUBLE (7, 2), dep_id INT) INSERT INTO emp (name,salary,dep_id) VALUES ("Zhang San", 8000pint 2), ("Li Si", 12000pint 1), ("Wang Wu", 5000Jing 2), ("Zhao Liu", 8000pint 3) ("Pig Seven", 9000jue 1), ("Wednesday", 7000pas 4), ("Cai Jiu", 7000jue 2) CREATE TABLE dep (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR (20)); INSERT INTO dep (name) VALUES ("Teaching Department"), ("sales Department"), ("personnel Department")

Mysql > select * from emp

+-- +

+-- +

| | 1 | Zhang San | 8000.00 | 2 | |

| | 2 | Li Si | 12000.00 | 1 | |

| | 3 | Wang Wu | 5000.00 | 2 | |

| | 4 | Zhao Liu | 8000.00 | 3 | |

| | 5 | Pig Qi | 9000.00 | 1 | |

| | 6 | Saturday | 7000.00 | 4 |

| | 7 | Cai Jiu | 7000.00 | 2 | |

+-- +

7 rows in set (0.00 sec)

Mysql > select * from dep

+-+ +

| | id | name |

+-+ +

| | 1 | Teaching Department |

| | 2 | sales Department |

| | 3 | personnel Department |

+-+ +

3 rows in set (0.00 sec)

1. Cartesian product query

Select * from emp,dep

Mysql > select * from emp,dep

+-+

+-+

| | 1 | Zhang San | 8000.00 | 2 | 1 | Teaching Department |

| | 1 | Zhang San | 8000.00 | 2 | 2 | sales Department |

| | 1 | Zhang San | 8000.00 | 2 | 3 | personnel Department |

| | 2 | Li Si | 12000.00 | 1 | 1 | Teaching Department |

| | 2 | Li Si | 12000.00 | 1 | 2 | sales Department |

| | 2 | Li Si | 12000.00 | 1 | 3 | personnel Department |

| | 3 | Wang Wu | 5000.00 | 2 | 1 | Teaching Department |

| | 3 | Wang Wu | 5000.00 | 2 | 2 | sales Department |

| | 3 | Wang Wu | 5000.00 | 2 | 3 | personnel Department |

| | 4 | Zhao Liu | 8000.00 | 3 | 1 | Teaching Department |

| | 4 | Zhao Liu | 8000.00 | 3 | 2 | sales Department |

| | 4 | Zhao Liu | 8000.00 | 3 | 3 | personnel Department |

| | 5 | Pig Qi | 9000.00 | 1 | 1 | Teaching Department |

| | 5 | Pig Qi | 9000.00 | 1 | 2 | sales Department |

| | 5 | Pig Qi | 9000.00 | 1 | 3 | personnel Department |

| | 6 | Wednesday | 7000.00 | 4 | 1 | Teaching Department |

| | 6 | Wednesday | 7000.00 | 4 | 2 | sales Department |

| | 6 | Wednesday | 7000.00 | 4 | 3 | personnel Department |

| | 7 | Cai Jiu | 7000.00 | 2 | 1 | Teaching Department |

| | 7 | Cai Jiu | 7000.00 | 2 | 2 | sales Department |

| | 7 | Cai Jiu | 7000.00 | 2 | 3 | personnel Department |

+-+

21 rows in set (0.00 sec)

2. Internal connection

Querying the associated data in both tables is equivalent to using conditions to filter out the correct results from the Cartesian product results.

SELECT * FROM emp,dep WHERE emp.dep_id=dep.id

SELECT * FROM emp INNER JOIN dep ON emp.dep_id=dep.id

Query results:

+-+

+-+

| | 1 | Zhang San | 8000.00 | 2 | 2 | sales Department |

| | 2 | Li Si | 12000.00 | 1 | 1 | Teaching Department |

| | 3 | Wang Wu | 5000.00 | 2 | 2 | sales Department |

| | 4 | Zhao Liu | 8000.00 | 3 | 3 | personnel Department |

| | 5 | Pig Qi | 9000.00 | 1 | 1 | Teaching Department |

| | 7 | Cai Jiu | 7000.00 | 2 | 2 | sales Department |

+-+

6 rows in set (0.00 sec)

At this point, we can query using all the fields in the two tables.

Example:

-- query the name of the department where Li Si is located.

SELECT emp.name,dep.name FROM emp INNER JOIN dep ON emp.dep_id=dep.id WHERE emp.name= "Li Si"

-- query the names of all employees in the sales department and the name of the department

-- SELECT name FROM emp WHERE dep_id in (SELECT id FROM dep WHERE name= sales Department)

SELECT emp.name,dep.name FROM emp INNER JOIN dep ON emp.dep_id=dep.id WHERE dep.name= "sales Department"

3. External connection

(1) left outer connection: on the basis of the inner connection, add the result that there is no right side on the left side.

SELECT * FROM emp LEFT JOIN dep ON dep.id=emp.dep_id

+-+ +

+-+ +

| | 2 | Li Si | 12000.00 | 1 | 1 | Teaching Department |

| | 5 | Pig Qi | 9000.00 | 1 | 1 | Teaching Department |

| | 1 | Zhang San | 8000.00 | 2 | 2 | sales Department |

| | 3 | Wang Wu | 5000.00 | 2 | 2 | sales Department |

| | 7 | Cai Jiu | 7000.00 | 2 | 2 | sales Department |

| | 4 | Zhao Liu | 8000.00 | 3 | 3 | personnel Department |

| | 6 | Saturday | 7000.00 | 4 | NULL | NULL |

+-+ +

7 rows in set (0.00 sec)

(1) Outer right connection: add the result that there is no left on the right side on the basis of the inner connection.

SELECT * FROM emp RIGHT JOIN dep ON dep.id=emp.dep_id

Mysql > SELECT * FROM emp RIGHT JOIN dep ON dep.id=emp.dep_id

+-+ +

+-+ +

| | 1 | Zhang San | 8000.00 | 2 | 2 | sales Department |

| | 2 | Li Si | 12000.00 | 1 | 1 | Teaching Department |

| | 3 | Wang Wu | 5000.00 | 2 | 2 | sales Department |

| | 4 | Zhao Liu | 8000.00 | 3 | 3 | personnel Department |

| | 5 | Pig Qi | 9000.00 | 1 | 1 | Teaching Department |

| | 7 | Cai Jiu | 7000.00 | 2 | 2 | sales Department |

+-+ +

6 rows in set (0.00 sec)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.