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

This article is about what the special form of Java mysql query statement is. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

Create a new table:

-- create student table CREATE TABLE IF NOT EXISTS stu (id TINYINT UNSIGNED AUTO_INCREMENT KEY COMMENT 'number', username VARCHAR (20) NOT NULL UNIQUE COMMENT 'student name', score TINYINT UNSIGNED NOT NULL COMMENT 'student examination score') ENGINE=INNODB INSERT stu (username, score) VALUES ('king', 95), (' queen', 75), ('zhangsan', 69), (' lisi', 78), ('wangwu', 87), (' zhaoliu', 88), ('tianqi', 98), (' ceshi', 99), ('tiancai', 50);-- create a score table CREATE TABLE IF NOT EXISTS `level` (id TINYINT UNSIGNED AUTO_INCREMENT KEY COMMENT' number', score TINYINT UNSIGNED COMMENT 'score') ENGINE=INNODB INSERT `level` (score) VALUES (90), (80), (70); 1. Subquery 1.1. SELECT field name FROM table name WHERE field name = (SELECT field name FROM table name); 1.2. The result of the inner statement query can be used as the condition of the outer statement query 1.3. The subquery SELECT * FROM user1 WHERE depId IN (1,2,3,4) raised by IN; SELECT * FROM user1 WHERE depId IN (SELECT id FROM dep); 1.4. The subquery SELECT id, username, score FROM stuWHERE score > = (SELECT score FROM `level` WHERE id = 1) is derived from the comparison operator. Subqueries raised by EXISTS

If the result after EXISTS is true, the result of the query is returned, otherwise the result is empty.

SELECT * FROM stu WHERE EXISTS (SELECT score FROM `level` WHERE id = 1); 1.6. ANY SOME ALL

Take the minimum or maximum value of the query result in parentheses after ANY | SOME | ALL.

-- ANY SOME ALLSELECT * FROM stu WHERE score > = ANY (SELECT score FROM `level`); SELECT * FROM stu WHERE score > SOME (SELECT score FROM `level`); SELECT * FROM stu WHERE score > ALL (SELECT score FROM `level`) CREATE... SELECT-- creates a user3 table and writes the information of id and username in table stu to CREATE TABLE user3 (id INT UNSIGNED AUTO_INCREMENT KEY,username VARCHAR (20)) SELECT id, username FROM stu;1.8 in user3. INSERT... SELECT-- inserts the username information from the user1 table into the user3 INSERT user3 (username) SELECT username FROM user1;1.9. CREATE TABLE Table name 1 LIKE Table name 2

Create Table 1 with the same structure as Table 2.

two。 Federated query 2.1 UNION

Remove duplicate values from both tables.

SELECT field name, … FROM table name 1UNIONSELECT field name, … FROM table name 22.2 position UNION ALL

Simply merge the two tables without removing the duplicate values.

SELECT field name, … FROM table name 1UNION ALL SELECT field name, … The table name of FROM is 2 / 3. Self-connection query 3.1. The realization form of Infinite level Classification

Create the table cate:

-- create table cateCREATE TABLE cate (id SMALLINT UNSIGNED AUTO_INCREMENT KEY COMMENT 'number', cateName VARCHAR (100) NOT NULL UNIQUE COMMENT 'category name', pId SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'parent id') -- insert top-level categories INSERT cate (cateName, pId) VALUES ('clothing', 0), ('digital', 0), ('toys', 0),-- insert subcategories of clothing ('men's wear', 1), ('women's wear', 1), ('underwear', 1),-- insert digital subcategories ('TV', 2), ('refrigerator', 2), ('washing machine', 2) -- insert subcategories of toys (Herm è s, 3), ('LV', 3), (' GUCCI', 3),-- insert subcategories of menswear ('jackets', 4), ('shirts', 4), ('pants', 4)-- insert subcategories of TVs ('LCD TVs', 7), ('plasma TVs', 7), ('Rear projection TVs', 7)

Self-connection:

-- query all the classification information and get its parent classification SELECT s.id, s.cateName AS sCateName, p.cateName AS pCateNameFROM cate AS sLEFT JOIN cate AS pON s.pId = p.id

Thank you for reading! This is the end of this article on "what is the special form of query sentence in Java mysql?". I hope the above content can be helpful to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

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.