SQL Advanced -2- Automatic connection

SQL typically performs join operations between different tables, but it can also perform operations within the same table. Connect to database in macOS:

Mysql -u root -p -- Enter the passwordCopy the code

Basic operation

Create and view databases

Create table user(1, 1, 2, 1, 2, 2, 3, 3, 4); , field N); // Don't forget the last semicolon// show tables;
// select * from table desc;
SQL > create table user;
// drop table user;
Alter table user rename to users; Alter table name change to users; Can be omitted to
Copy the code

mysql> create table products (id int primary key auto_increment,name varchar(50) default null, price int default null);
Copy the code

The problem

Today, I encountered a problem when inserting Chinese data into a table in the database: it is definitely a coding problem

To solve the process

Modifying the database encoding

mysql> alter table products default character set utf8; Change the database encoding to UTF8Copy the code

Although the encoding of the whole table has been modified, the encoding of the name field is still latin1, and Chinese cannot be inserted, so it needs to be modified

Previous encoding:

Modify the encoding of field name

The key word is modify

mysql> alter table products modify column name varchar(50) character set utf8;
Copy the code

The syntax format is:

Mysql >alter table < table name > modify column < column > character set utf8; -- Change the field type to UTF8Copy the code

Insert new data

Insert into < table name > < field 1, field 2,..... > values < data >Copy the code

Case 1- Can be rearranged, combined

Two kinds of combination

An ordered pair, enclosed in Angle brackets
An out-of-order pair, enclosed in curly braces

demand

The same data in the result generated by SQL self-join

select p1.name as name1, p2.name as name2
from products p1, products p2
Copy the code

Name_1 name_2 -- -- -- -- -- -- -- -- -- -- -- -- apple apple, the same result, how to remove the apple oranges orange apple banana orange orange orange banana apple banana orange the banana bananaCopy the code

Select p1.name as name1, p2.name as name2 from products p1, products p2 where p1.name <> p2.nameCopy the code

Note: In SQL, even identical tables given different names should be treated as separate tables that happen to store the same data

There is no difference between a self-join of a table and a join between different tables

extension

To get more than 3 elements, use successive extensions of multiple tables:

select P1.name as name1, P2.name as name2, P3.name as name3 from Products P1,Products P2, P2.name2 > p3.name Products P3 where p1.name > p2.name -- p2.name2 > p3.nameCopy the code

Case 2- Duplicate line problem

Finding duplicate rows

Based on a single field

Suppose we wanted to find duplicate rows by the name field (based on only one field), we could use the following statement:

Select name, price, count(*) from Products group by name;Copy the code

Filter rows less than 1:

select name, price, count(*) from Products group by name having count(*) > 1; Filter -Copy the code

Note: Group by fields that have the same value, and then display groups larger than 1

Based on multiple fields

Sometimes duplicate rows are found based on multiple fields

SELECT col1, COUNT(col1), col2, COUNT(col2), ... FROM table_name GROUP BY col1, col2, ... HAVING (COUNT(col1) > 1) and (COUNT(col2) > 1) and...Copy the code

Delete duplicate rows

By deleting duplicate rows, the result is as follows

Oracle uses the extreme value function Rowid

Delete from Products P1 where rowid < (- less than the maximum value of delete all select Max (P2. The rowid) - returns the price and name are the same product line from Products P2 the where  P1.name = P2.name and P1.price = P2.price )Copy the code

The subquery returns the row of items with the same price and name
Apples and bananas have no repeated rows
The maximum value 4 is returned, and both rows (2 and 3) less than 4 are deleted delete

Use non-equivalent connections

Delete from Products P1 where exists (select * from Products P2 where name = p2.name and name = p2.name P1.price = P2.price and P1.rowid < P2.rowid )Copy the code

Mysql to delete duplicate rows

Use the delete JOIN statement to keep the largest row, and delete all the smaller rows

Delete P1 from Products P1 inner join Products P2 where p1.id < p2.id and p1.name = p2.nameCopy the code

Case 3- Locally inconsistent columns

demand

In the same table, there may be something that doesn’t quite make sense: for example, the addresses of the Maeda couple should be the same, but the tables are different

SQL implementation

Look for records of the same family, but at different addresses

A combination of self – joining and non – equivalent joining

select distinct A1.name, A1.address from Address A1, Address A2 where a1.family_id = a2.family_id -- same family and a1.address <> a2.address; -- Different addressesCopy the code

Find records of items with equal prices but different names

select distinct P1.name, P1.price from Products P1, Select * from Products P2 where p1.price = p2.price and p1.name <> p2.name; select * from Products P2 where p1.price = p2.price and p1.name <> p2.nameCopy the code

If there is no DISTINCT in the query statement, duplicate values appear in the result

Unequal connection

Connections that use <, >, <>, etc., are called “unequal connections”. The comparison operators, such as > or <, can be used not only for numeric comparisons, but also for strings, dates, etc

SQL Advanced -2- Automatic connection