3. Querying, adding, deleting and modifying

Posted by lkq on Fri, 21 Feb 2020 14:23:26 +0100

Article directory

First, query

MySQL basic query statement bit SELECT statement, its syntax rules are as follows

Select < field list >
	[from < table name >] (select the source of data
	[where < expression >] (filter the queried data by a certain condition)
	[group by < group expression >] (group data by a condition
	[having < expression >] (filter the grouped data according to a certain condition
	[order by < field name >] (sort based on a variable
	[limit < quantity >]; ා limit the amount of data to be printed
  • SELECT is equivalent to print statement, that is, SELECT the content of query and print
  • The field list is the variable used to select the content of the query

1. Basic query

(1) Basic usage

mysql> SELECT 3+5 AS sum;			# Print 3 + 5, AS is used to give variable names to previous variables
+-----+
| sum |
+-----+
|   8 |
+-----+
1 row in set (0.00 sec)

mysql> SELECT * 
			 FROM departments  
			 LIMIT 10;		# Print all data in data table departments, limit output to 10 rows
+---------+--------------------+
| dept_no | dept_name          |
+---------+--------------------+
| d009    | Customer Service   |
| d005    | Development        |
| d002    | Finance            |
| d003    | Human Resources    |
| d001    | Marketing          |
| d004    | Production         |
| d006    | Quality Management |
| d008    | Research           |
| d007    | Sales              |
+---------+--------------------+
9 rows in set (0.00 sec)

mysql> SELECT dept_no AS a, dept_name AS b 
			 FROM departments  
			 LIMIT 10;
+------+--------------------+
| a    | b                  |
+------+--------------------+
| d009 | Customer Service   |
| d005 | Development        |
| d002 | Finance            |
| d003 | Human Resources    |
| d001 | Marketing          |
| d004 | Production         |
| d006 | Quality Management |
| d008 | Research           |
| d007 | Sales              |
+------+--------------------+
9 rows in set (0.00 sec)

(2) Filter data WHERE

mysql> SELECT salary 
			 FROM salaries 
			 WHERE salary>60000 AND salary<70000 		# Filter the data by some conditions
			 LIMIT 10;															
+--------+
| salary |
+--------+
|  60117 |
|  62102 |
|  66074 |
|  66596 |
|  66961 |
|  65828 |
|  65909 |
|  67534 |
|  69366 |
|  60770 |
+--------+
10 rows in set (0.00 sec)
  • IN operators are often used to find keywords
  • BETWEEN AND is often used for range lookup
  • AND and AND OR are used for multi criteria queries, AND have priority over OR
  • IN has the same function as OR, but IN performs faster than OR

(3) Sort ORDER BY

mysql> SELECT salary 
       FROM salaries 
			 ORDER BY salary 							 # The ORDER BY sub statement is sorted in ascending order (ASC) by default
			 LIMIT 10;		
+--------+
| salary |
+--------+
|  38623 |
|  38735 |
|  38786 |
|  38812 |
|  38836 |
|  38849 |
|  38850 |
|  38851 |
|  38859 |
|  38864 |
+--------+
10 rows in set (0.85 sec)

mysql> SELECT salary 
			 FROM salaries 
			 ORDER BY salary DESC 					# Specify sort descending
			 LIMIT 10;		
+--------+
| salary |
+--------+
| 158220 |
| 157821 |
| 156286 |
| 155709 |
| 155513 |
| 155377 |
| 155190 |
| 154888 |
| 154885 |
| 154459 |
+--------+
10 rows in set (0.82 sec)

mysql> SELECT * 
			 FROM salaries 
			 ORDER BY salary DESC, emp_no 		# You can specify the basis for multiple sorts
			 LIMIT 10;
+--------+--------+------------+------------+
| emp_no | salary | from_date  | to_date    |
+--------+--------+------------+------------+
|  43624 | 158220 | 2002-03-22 | 9999-01-01 |
|  43624 | 157821 | 2001-03-22 | 2002-03-22 |
| 254466 | 156286 | 2001-08-04 | 9999-01-01 |
|  47978 | 155709 | 2002-07-14 | 9999-01-01 |
| 253939 | 155513 | 2002-04-11 | 9999-01-01 |
| 109334 | 155377 | 2000-02-12 | 2001-02-11 |
| 109334 | 155190 | 2002-02-11 | 9999-01-01 |
| 109334 | 154888 | 2001-02-11 | 2002-02-11 |
| 109334 | 154885 | 1999-02-12 | 2000-02-12 |
|  80823 | 154459 | 2002-02-22 | 9999-01-01 |
+--------+--------+------------+------------+
10 rows in set (0.95 sec)
  • In multi sorting, first sort by the first benchmark
  • After the first sorting, sort the same results of the first sorting according to the second benchmark, and so on
  • Default ascending, DESC must be specified for descending sort

(4) Character matching query LIKE

MySQL can use wildcards to match and find. The keyword is LIKE

  • %: matches characters of any length (including characters of length 0)

  • _: match one character at a time

  • Use BINARY case sensitive

mysql> SELECT first_name 
			 FROM employees 
			 WHERE first_name LIKE BINARY "%A%"		# Query the data with A in first name
			 																			# Remove BINARY, case insensitive (if the table is not case sensitive when it is created)
			 LIMIT 5;
+------------+
| first_name |
+------------+
| Anneke     |
| Arif       |
| Alain      |
| Adamantios |
| Alejandro  |
+------------+
5 rows in set (0.00 sec)

mysql> SELECT first_name 
			 FROM employees 
			 WHERE first_name LIKE BINARY "_a%" 	# Query the data with the second letter a in the first name
			 LIMIT 5;
+------------+
| first_name |
+------------+
| Parto      |
| Saniya     |
| Mary       |
| Patricio   |
| Kazuhito   |
+------------+
5 rows in set (0.00 sec)

(5) Query result does not repeat DISTINCT

mysql> SELECT emp_no 
			 FROM salaries 
			 ORDER BY emp_no 
			 LIMIT 10;
+--------+
| emp_no |
+--------+
|  10001 |
|  10001 |
|  10001 |
|  10001 |
|  10001 |
|  10001 |
|  10001 |
|  10001 |
|  10001 |
|  10001 |
+--------+
10 rows in set (0.00 sec)

mysql> SELECT DISTINCT emp_no 		# Use the DISTINCT keyword to de duplicate the specified field
			 FROM salaries  
			 ORDER BY emp_no 
			 LIMIT 10;
+--------+
| emp_no |
+--------+
|  10001 |
|  10002 |
|  10003 |
|  10004 |
|  10005 |
|  10006 |
|  10007 |
|  10008 |
|  10009 |
|  10010 |
+--------+
10 rows in set (0.00 sec)
  • Using DISTINCT directly can only return one specified de duplicated column, not other columns
  • To return multiple columns of de duplicated data, you can use grouping
mysql> SELECT emp_no, COUNT(*) AS total 	# COUNT is a function that calculates the total number of rows
			 FROM salaries   
			 GROUP BY emp_no 						# Group by EMP no
			 ORDER BY total DESC 
			 LIMIT 10;
+--------+-------+
| emp_no | total |
+--------+-------+
| 279953 |    18 |
| 239758 |    18 |
| 292257 |    18 |
| 235678 |    18 |
| 295842 |    18 |
| 470102 |    18 |
|  26608 |    18 |
|  16431 |    18 |
| 251839 |    18 |
| 105540 |    18 |
+--------+-------+
10 rows in set (0.68 sec)

The above statement is the total number of EMP no queries grouped by EMP No

(6) GROUP BY

  • Basic use: group by a field
mysql> SELECT id, COUNT(*) AS total 
			 FROM groupTEST 
			 GROUP BY id;				# Group by id, and all those with the same id are divided into one group
+------+-------+
| id   | total |
+------+-------+
|    1 |     4 |
|    2 |     2 |
|    3 |     6 |
+------+-------+
3 rows in set (0.00 sec)

mysql> SELECT id, GROUP_CONCAT(name) 		# The name s with the same id are spliced together by grouping
			 FROM groupTEST 
			 GROUP BY id;
+------+--------------------------+
| id   | GROUP_CONCAT(name)       |
+------+--------------------------+
|    1 | Xiong,Wang,Li,Xiao       |
|    2 | Xiong,Li                 |
|    3 | Li,Xiao,Wang,Xue,Xue,Xue |
+------+--------------------------+
3 rows in set (0.00 sec)
  • Filtering data with HAVING
  • WHERE is to filter data before grouping and HAVING is to filter data after grouping
mysql> SELECT name, COUNT(*) AS total 
			 FROM groupTEST 
			 GROUP BY name;
+-------+-------+
| name  | total |
+-------+-------+
| Li    |     3 |
| Wang  |     2 |
| Xiao  |     2 |
| Xiong |     2 |
| Xue   |     3 |
+-------+-------+
5 rows in set (0.00 sec)

mysql> SELECT name, COUNT(*) AS total 
			 FROM groupTEST 
			 GROUP BY name HAVING total<3;
+-------+-------+
| name  | total |
+-------+-------+
| Wang  |     2 |
| Xiao  |     2 |
| Xiong |     2 |
+-------+-------+
3 rows in set (0.00 sec)
  • Use WITH ROLLUP to count the number of records
  • ROLLUP and ORDER BY are mutually exclusive
mysql> SELECT name, COUNT(*) AS total 
			 FROM groupTEST 
			 GROUP BY name WITH ROLLUP;			# After using this keyword, the total number of statistics will be displayed in the last row of the table
+-------+-------+
| name  | total |
+-------+-------+
| Li    |     3 |
| Wang  |     2 |
| Xiao  |     2 |
| Xiong |     2 |
| Xue   |     3 |
| NULL  |    12 |
+-------+-------+
6 rows in set (0.00 sec)
  • Multi field group query
mysql> SELECT id, name, COUNT(*) AS total 
			 FROM groupTEST 
			 GROUP BY id, name;			# First group the first field, then group the second field. COUNT calculates the last group
+------+-------+-------+
| id   | name  | total |
+------+-------+-------+
|    1 | Li    |     1 |
|    1 | Wang  |     1 |
|    1 | Xiao  |     1 |
|    1 | Xiong |     1 |
|    2 | Li    |     1 |
|    2 | Xiong |     1 |
|    3 | Li    |     1 |
|    3 | Wang  |     1 |
|    3 | Xiao  |     1 |
|    3 | Xue   |     3 |
+------+-------+-------+
10 rows in set (0.00 sec)

(7) LIMIT query results LIMIT

LIMIT can LIMIT the location and number of output results. The basic syntax is as follows

LIMIT [position offset,] number of lines

mysql> SELECT * FROM salaries 
			 ORDER BY salary 
			 LIMIT 10;								# Top ten lines of output results
+--------+--------+------------+------------+
| emp_no | salary | from_date  | to_date    |
+--------+--------+------------+------------+
| 253406 |  38623 | 2002-02-20 | 9999-01-01 |
|  49239 |  38735 | 1996-09-17 | 1997-09-17 |
| 281546 |  38786 | 1996-11-13 | 1997-06-26 |
|  15830 |  38812 | 2001-03-12 | 2002-03-12 |
|  64198 |  38836 | 1989-10-20 | 1990-10-20 |
| 475254 |  38849 | 1993-06-04 | 1994-06-04 |
|  50419 |  38850 | 1996-09-22 | 1997-09-22 |
|  34707 |  38851 | 1990-10-03 | 1991-10-03 |
|  49239 |  38859 | 1995-09-18 | 1996-09-17 |
| 274049 |  38864 | 1996-09-01 | 1997-09-01 |
+--------+--------+------------+------------+
10 rows in set (2.08 sec)

mysql> SELECT * FROM salaries 
			 ORDER BY salary 
			 LIMIT 2, 10;							# Output 10 lines from line 2
+--------+--------+------------+------------+
| emp_no | salary | from_date  | to_date    |
+--------+--------+------------+------------+
| 281546 |  38786 | 1996-11-13 | 1997-06-26 |
|  15830 |  38812 | 2001-03-12 | 2002-03-12 |
|  64198 |  38836 | 1989-10-20 | 1990-10-20 |
| 475254 |  38849 | 1993-06-04 | 1994-06-04 |
|  50419 |  38850 | 1996-09-22 | 1997-09-22 |
|  34707 |  38851 | 1990-10-03 | 1991-10-03 |
|  49239 |  38859 | 1995-09-18 | 1996-09-17 |
| 274049 |  38864 | 1996-09-01 | 1997-09-01 |
| 473390 |  38872 | 1995-03-20 | 1995-09-22 |
|  12444 |  38874 | 1990-08-15 | 1991-08-15 |
+--------+--------+------------+------------+
10 rows in set (0.96 sec)
  • LIMIT 3, 4 and LIMIT 3 OFFSET 4 have the same effect

2. Querying with collection functions

MySQL provides some query functions to analyze and report the acquired data

(1) COUNT() the total number of rows in the calculation table

COUNT() returns the number of rows in the specified column in the result

  • COUNT(*) calculates the total number of rows in the table, whether there are values or null values
  • Count ignores null values
mysql> SELECT COUNT(*) 
			 FROM salaries 
			 WHERE salary BETWEEN 50000 AND 70000;
+----------+
| COUNT(*) |
+----------+
|  1258826 |
+----------+
1 row in set (0.61 sec)

(2) Calculates the sum of the specified column values SUM()

mysql> SELECT emp_no, salary 
			 FROM salaries 
			 WHERE emp_no BETWEEN 10000 AND 10001;
+--------+--------+
| emp_no | salary |
+--------+--------+
|  10001 |  60117 |
|  10001 |  62102 |
|  10001 |  66074 |
|  10001 |  66596 |
|  10001 |  66961 |
|  10001 |  71046 |
|  10001 |  74333 |
|  10001 |  75286 |
|  10001 |  75994 |
|  10001 |  76884 |
|  10001 |  80013 |
|  10001 |  81025 |
|  10001 |  81097 |
|  10001 |  84917 |
|  10001 |  85112 |
|  10001 |  85097 |
|  10001 |  88958 |
+--------+--------+
17 rows in set (0.00 sec)

mysql> SELECT SUM(salary) 
			 FROM salaries 
			 WHERE emp_no BETWEEN 10000 AND 10001;
+-------------+
| SUM(salary) |
+-------------+
|     1281612 |
+-------------+
1 row in set (0.00 sec)

(3) Average AVG() for the specified column

mysql> SELECT emp_no, AVG(salary) AS avg_salary 
			 FROM salaries 
			 GROUP BY emp_no 
			 ORDER BY avg_salary 
			 LIMIT 10;
+--------+------------+
| emp_no | avg_salary |
+--------+------------+
|  15830 | 39299.5000 |
| 253406 | 39332.7500 |
| 473390 | 39372.5000 |
| 281546 | 39417.2500 |
| 466226 | 39442.7500 |
| 401786 | 39453.7500 |
| 245832 | 39515.2500 |
| 230890 | 39520.0000 |
| 496197 | 39573.3333 |
| 496848 | 39584.0000 |
+--------+------------+
10 rows in set (1.08 sec)

(4) Returns the maximum value MAX() / minimum value MIN() of the specified column

  • MAX and MIN can find numbers and dates
  • You can also find the maximum value and minimum value of character type, and find according to ASCII
mysql> SELECT MAX(salary) 
			 FROM salaries;
+-------------+
| MAX(salary) |
+-------------+
|      158220 |
+-------------+
1 row in set (0.60 sec)

mysql> SELECT MIN(salary) 
			 FROM salaries;
+-------------+
| MIN(salary) |
+-------------+
|       38623 |
+-------------+
1 row in set (0.59 sec)

mysql> SELECT MIN(birth_date) 
			 FROM employees;
+-----------------+
| MIN(birth_date) |
+-----------------+
| 1952-02-01      |
+-----------------+
1 row in set (0.18 sec)

mysql> SELECT MAX(birth_date) 
			 FROM employees;
+-----------------+
| MAX(birth_date) |
+-----------------+
| 1965-02-01      |
+-----------------+
1 row in set (0.08 sec)

3. Connection query

  • When querying data, multiple tables are combined to query, which is called join query or cross table query
  • For example, table emp has a field EMP no (3 rows of data), and table dept has a field dept no (4 rows of data). When there is no constraint to query employees and departments (select EMP no, Dept no from EMP, Dept) at the same time, 12 rows of data (3 * 4) will be obtained, which is called Cartesian product
  • Join queries can be done with WHERE sub statements, but WHERE sub statements can affect query performance at some times

Create databases emp, dept, salary

mysql> CREATE TABLE emp (
  		     emp_no INT AUTO_INCREMENT KEY, 
  				 name VARCHAR(20) NOT NULL, 
  				 dept_no INT, 
  				 salary INT);
Query OK, 0 rows affected (0.04 sec)

mysql> CREATE TABLE dept (
  		     dept_no INT KEY, 
  				 dname VARCHAR(20));
Query OK, 0 rows affected (0.04 sec)

mysql> CREATE TABLE salaries (
  				 low INT, 
  				 high INT, 
  				 level INT);
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO emp VALUES
    -> (1, "Xiong", 101, 1000),
    -> (2, "Wang", 101, 1200),
    -> (3, "Li", 103, 1500),
    -> (4, "Zhao", 104, 2300),
    -> (5, "Xiao", 104, 2000),
    -> (6, "Hu", 105, 2100);
Query OK, 6 rows affected (0.02 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> INSERT INTO dept VALUES
    -> (101, "Department 1"),
    -> (102, "Department 2"),
    -> (103, "Department 3"),
    -> (104, "Department 4"),
    -> (105, "Department 5");
Query OK, 5 rows affected (0.02 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> INSERT INTO salaries VALUES
    -> (1000, 1500, 1),
    -> (1501, 2000, 2),
    -> (2001, 2500, 3),
    -> (2501, 3000, 4),
    -> (3001, 3055, 5);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

(1) Internal connection query

  • Internal connection is exactly matched, only matching data is displayed
  • Internal connections are divided into equivalent connection, non equivalent connection and self connection
  • Self join: i.e. treat a table as a different table to use join query

Equivalent connection

mysql> SELECT e.emp_no, e.name, d.dept_no, d.dname 
			 FROM emp e 								# AS can be omitted.
			 INNER JOIN dept d 
			 ON e.dept_no=d.dept_no;
+--------+-------+---------+---------+
| emp_no | name  | dept_no | dname   |
+--------+-------+---------+---------+
|      1 | Xiong |     101 | Department 1   |
|      2 | Wang  |     101 | Department 1   |
|      3 | Li    |     103 | Department 3   |
|      4 | Zhao  |     104 | Department 4   |
|      5 | Xiao  |     104 | Department 4   |
|      6 | Hu    |     105 | Department 5   |
+--------+-------+---------+---------+
6 rows in set (0.00 sec)

Non equivalent connection

mysql> SELECT e.name, e.salary, s.level 
			 FROM emp e 
			 INNER JOIN salaries s 
			 ON e.salary BETWEEN s.low AND s.high;
+-------+--------+-------+
| name  | salary | level |
+-------+--------+-------+
| Xiong |   1000 |     1 |
| Wang  |   1200 |     1 |
| Li    |   1500 |     1 |
| Zhao  |   2300 |     3 |
| Xiao  |   2000 |     2 |
| Hu    |   2100 |     3 |
+-------+--------+-------+
6 rows in set (0.01 sec)

(2) External connection query

  • In addition to displaying matching data of internal connection, external connection also displays all data of one table unconditionally, and fills in NULL value if there is no matching record in another table
  • Outer connection is divided into left outer connection and right outer connection (these two are essentially equivalent)
mysql> SELECT e.name, e.salary, s.level     			# Right outer join
			 FROM emp e 
			 RIGHT OUTER JOIN salaries s 
			 ON e.salary BETWEEN s.low AND s.high;
+-------+--------+-------+
| name  | salary | level |
+-------+--------+-------+
| Xiong |   1000 |     1 |
| Wang  |   1200 |     1 |
| Li    |   1500 |     1 |
| Zhao  |   2300 |     3 |
| Xiao  |   2000 |     2 |
| Hu    |   2100 |     3 |
| NULL  |   NULL |     4 |
| NULL  |   NULL |     5 |
+-------+--------+-------+
8 rows in set (0.00 sec)

mysql> SELECT e.name, e.salary, s.level 					# Left outer join
			 FROM salaries s 
			 LEFT OUTER JOIN emp e 
			 ON e.salary BETWEEN s.low AND s.high;
+-------+--------+-------+
| name  | salary | level |
+-------+--------+-------+
| Xiong |   1000 |     1 |
| Wang  |   1200 |     1 |
| Li    |   1500 |     1 |
| Zhao  |   2300 |     3 |
| Xiao  |   2000 |     2 |
| Hu    |   2100 |     3 |
| NULL  |   NULL |     4 |
| NULL  |   NULL |     5 |
+-------+--------+-------+
8 rows in set (0.00 sec)

(3) Compound conditional connection query

mysql> SELECT e.name, e.salary, s.level 
			 FROM emp e 
			 INNER JOIN salaries s 
			 ON e.salary BETWEEN s.low AND s.high AND s.level<=2;
+-------+--------+-------+
| name  | salary | level |
+-------+--------+-------+
| Xiong |   1000 |     1 |
| Wang  |   1200 |     1 |
| Li    |   1500 |     1 |
| Xiao  |   2000 |     2 |
+-------+--------+-------+
4 rows in set (0.00 sec)

4. Subquery

  • Subqueries are nested queries, and inner queries are the filter conditions of outer queries
  • Subqueries can be added to SELECT, UPDATE, DELETE statements

Create two tables as an example

mysql> CREATE TABLE t1 (n1 INT);
Query OK, 0 rows affected (0.14 sec)

mysql> CREATE TABLE t2 (n2 INT);
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT INTO t1 VALUES (10), (15), (20), (25);
Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t2 VALUES (11), (12), (21), (23);
Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0

(1) Subqueries with ANY/SOME and ALL keywords

  • ANY and SOME keywords are equivalent, only one condition can be satisfied
  • In the following example, n1 of t1 is compared with the result n2 of sub query one by one, as long as n1 is greater than any one of n2, the condition is satisfied
mysql> SELECT n1 FROM t1
    -> WHERE n1>ANY
    -> (SELECT n2 FROM t2);
+------+
| n1   |
+------+
|   15 |
|   20 |
|   25 |
+------+
3 rows in set (0.00 sec)
# n2 is 11, 12, 21, 22, n1 is 10, 15, 20, 25, only 10 does not meet the conditions
# 15. 20, 25 are greater than any one of 11, 12, 21, 22
  • ALL keyword is opposite to ANY, ALL conditions need to be met
mysql> SELECT n1 FROM t1
    -> WHERE n1>ALL
    -> (SELECT n2 FROM t2);
+------+
| n1   |
+------+
|   25 |
+------+
1 row in set (0.00 sec)
# n2 is 11, 12, 21, 22, n1 is 10, 15, 20, 25, only 25 meets the conditions
# Only 25 meets all conditions greater than 11, 12, 21, 22

(2) Subqueries with EXISTS/NOT EXISTS keyword

  • EXISTS keyword returns whether the result of the inner query is empty. If there is at least one result in the inner query, it returns TRUE, otherwise it returns FALSE. When it returns FALSE, the outer query does not take effect
  • NOT EXISTS is the opposite of EXISTS

In the following example, n2 of t2 is less than 30

mysql> SELECT * FROM t1
    -> WHERE EXISTS
    -> (SELECT n2 FROM t2 WHERE n2 > 30);
Empty set (0.00 sec)

mysql> SELECT * FROM t1
    -> WHERE NOT EXISTS
    -> (SELECT n2 FROM t2 WHERE n2 > 30);
+------+
| n1   |
+------+
|   10 |
|   15 |
|   20 |
|   25 |
+------+
4 rows in set (0.00 sec)

(3) Subqueries using operators

mysql> SELECT n1 FROM t1
    -> WHERE n1 IN
    -> (SELECT n2 FROM t2 WHERE n2<15);
Empty set (0.00 sec)

5. Merge query results

  • Use the UNION/UNION ALL keyword to merge the results of multiple SELECT queries
  • With ALL, duplicate lines will not be deleted and resources will be saved
mysql> CREATE TABLE fruits (id INT, price INT, name VARCHAR(10));
Query OK, 0 rows affected (0.11 sec)

mysql> INSERT INTO fruits VALUES
    -> (1, 100, "Apple"),
    -> (2, 102, "Pear"),
    -> (3, 120, "Peach"),
    -> (4, 121, "Strawberry");
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

# Query fruit with price between 100 and 110 and id 4 in fruits table
mysql> SELECT id, name 
    -> FROM fruits
    -> WHERE fruits.price BETWEEN 100 AND 110
    -> UNION ALL
    -> SELECT id, name
    -> FROM fruits
    -> WHERE fruits.id=4;
+------+--------+
| id   | name   |
+------+--------+
|    1 | Apple   |
|    2 | Pear   |
|    4 | Strawberry   |
+------+--------+
3 rows in set (0.00 sec)

Two. Insert

1. Insert data manually

The basic syntax is as follows

Insert into < table name > [field name] values < column value [,...] >;
  • If you do not specify a field name, you must insert the data in the order you defined it
mysql> CREATE TABLE t1 
			 (a INT, b INT);
Query OK, 0 rows affected (0.06 sec)

mysql> CREATE TABLE t2 
			 (a INT, b INT, c INT);
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO t1 VALUES
    -> (100, 200),
    -> (101, 300),
    -> (102, 400);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0
# Records is the number of records inserted
# Duplicates are ignored records
# Warnings indicate problematic data values

mysql> INSERT INTO t2 VALUES
    -> (103, 1, 500),
    -> (104, 2, 600),
    -> (105, 3, 700);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0
  • Specify the field name to insert. You can insert it out of order or just some fields
 mysql> INSERT INTO t2 (a, c) 
 		->	VALUES
    -> (1, 100),
    -> (2, 200);
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM t2;      # Default to NULL for columns with no value inserted
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 | NULL |  100 |
|    2 | NULL |  200 |
+------+------+------+
2 rows in set (0.00 sec)
  • A single statement inserting multiple rows of data is equal to a single statement inserting multiple rows of data, but the efficiency of a single statement is higher than that of multiple statements

2. Insert query results into the table

  • Use the combination of SELECT and INSERT statements to INSERT the results of a query into a table
mysql> INSERT INTO t1 (a, b)
    -> SELECT a, c FROM t2;
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM t1;
+------+------+
| a    | b    |
+------+------+
|    1 |  100 |
|    2 |  200 |
+------+------+
2 rows in set (0.00 sec)
  • You can also insert field names without specifying them, and insert them one by one according to the column definition order of the table
mysql> INSERT INTO t2
    -> SELECT * FROM t2;
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM t2;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 | NULL |  100 |
|    2 | NULL |  200 |
|    1 | NULL |  100 |
|    2 | NULL |  200 |
+------+------+------+
4 rows in set (0.00 sec)

3, Update data

  • Update table can update specific rows or all rows
mysql> SELECT * FROM fruits;
+------+-------+--------+
| id   | price | name   |
+------+-------+--------+
|    1 |   100 | Apple   |
|    2 |   102 | Pear   |
|    3 |   120 | Peach   |
|    4 |   121 | Strawberry   |
+------+-------+--------+
4 rows in set (0.00 sec)

mysql> UPDATE fruits 
			 SET id=1,price=1000,name="Cherry" 
			 WHERE id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM fruits;
+------+-------+-----------+
| id   | price | name      |
+------+-------+-----------+
|    1 |  1000 | Cherry    |
|    2 |   102 | Pear      |
|    3 |   120 | Peach      |
|    4 |   121 | Strawberry      |
+------+-------+-----------+
4 rows in set (0.00 sec)

mysql> UPDATE fruits 
			 SET id=1,price=1000,name="Cherry";
Query OK, 3 rows affected (0.01 sec)
Rows matched: 4  Changed: 3  Warnings: 0

mysql> SELECT * FROM fruits;
+------+-------+-----------+
| id   | price | name      |
+------+-------+-----------+
|    1 |  1000 | Cherry    |
|    1 |  1000 | Cherry    |
|    1 |  1000 | Cherry    |
|    1 |  1000 | Cherry    |
+------+-------+-----------+
4 rows in set (0.00 sec)

4, Delete data

The basic syntax is as follows

Delete from < table name > [where < condition >];

mysql> SELECT * FROM fruits;
+------+-------+-----------+
| id   | price | name      |
+------+-------+-----------+
|    1 |  1000 | Cherry    |
|    1 |  1000 | Cherry    |
|    1 |  1000 | Cherry    |
|    1 |  1000 | Cherry    |
+------+-------+-----------+
4 rows in set (0.00 sec)

mysql> DELETE FROM fruits 
			 WHERE id=1;
Query OK, 4 rows affected (0.02 sec)

mysql> SELECT * FROM fruits;
Empty set (0.00 sec)

Or you can use TRUNCATE TABLE statement, which directly deletes the original table and creates a new table, so it is more efficient

mysql> SELECT * FROM t1;
+------+
| n1   |
+------+
|   10 |
|   15 |
|   20 |
|   25 |
+------+
4 rows in set (0.00 sec)

mysql> TRUNCATE TABLE t1;
Query OK, 0 rows affected (0.03 sec)

mysql> SELECT * FROM t1;
Empty set (0.00 sec)

5, Add calculated column for table

  • Calculated column: the value of a column is calculated according to other columns

  • Both CREATE TABLE and ALTER TABLE statements support calculating columns

  • Use the GENERATED ALWAYS keyword to represent calculated columns

The basic syntax is as follows

Create table < table name >(
	< field name field type > generated always as (expression) [VIRTUAL|STORED] [constraint]
);

Examples are as follows

mysql> CREATE TABLE tab1 (
  		     a INT, 
  				 b INT, 
  				 c INT GENERATED ALWAYS AS (a*b) VIRTUAL);
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO tab1 (a, b) 
			 VALUES 
			 (2, 3);
Query OK, 1 row affected (0.02 sec)

mysql> SELECT * FROM tab1;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    2 |    3 |    6 |
+------+------+------+
1 row in set (0.00 sec)
Published 8 original articles, praised 0, visited 19
Private letter follow

Topics: MySQL ascii less

Hot Topics

©2024 Programmer Think