Summary of Basic SQL operations

1.SQL Introduction
Structured Query Language
(Hierarchical model, mesh model, relational model)

The relational model is the current mainstream (Oralce,mysql MSSQL)

SQL Standard: ANSI (1992 1997 2002 ISO)

Dialect and SQL standard
Learner: SQL standard
Dialect: Support your current database only

2. Common Database
Oracle MySQL SQL Server SQLite

MySQL (Focus)

Installation
Client---->server------> Library------> table
3. Building a library
1. Log in to MySQL
Mysql-u root-p

2. Create a database
Create database name;
Create Database day14;

--Specifying a character set when creating a database
Create DATABASE day14 character Set GBK;

3. View all databases on the server
show databases;
4. View the code that created the database
Show create database name;
Show CREATE Database day13;

5. Modify the encoding of a database
ALTER DATABASE name character set character encoding COLLATE character set validation rules;
ALTER DATABASE DAY13 character set UTF8 collate utf8_general_ci;

6. Deleting a database
drop database name;
Drop database day13;

7. Use a database (future-built tables are placed in this library)
Use database name;
Use DAY13;
4. Build a table
1. Build a table
CREATE TABLE Table name (
Field name (column name) data type,
Field name (column name) data type,
Field name (column name) data type
);

Float (5,2): Represents a total length of 5, where the scale is 2 bits and the integer digits are 3 bits

Example: CREATE TABLE Employee (
ID int,
Name varchar (20),
Gender char (4),
Birthday datetime,
Entry_date datetime,
Job varchar (100),
Salary float (7,2),
Resume Longtext
);

2.
Displays all data tables under the current data
Show tables;

3.
View the code that created the table
Show create table table name;
Show create table employee;

4. View the table details and display the results as a list
DESC table name;
DESC employee;
Describe employee;

Use DAY14;

5. Modifying a table's columns
1. Add a column
ALTER TABLE name add column name type;

2. Modify the data type of a column
ALTER TABLE name modify column name new data type;

3. Delete a column
ALTER TABLE name drop column name;

4. Modify the table name
Rename table name to new table name;

5. Modifying the character set of a table
ALTER TABLE name character set character set encoding

6. Modify column names
ALTER TABLE name change column old column name new row name new data type;

7. Delete Table structure
drop table name;

1. Add a column image
ALTER TABLE employee add image Longblob;
2. Modify the job column so that it has a length of 60
ALTER TABLE employee Modify job varchar (60);
3. Delete the image column
ALTER TABLE employee drop image;
4. Change the table name to user
Rename table employee to user;

5. Modify the table's character set to GBK
ALTER TABLE user character set GBK;

6. Name of column is modified to username
ALTER TABLE user change column name username varchar (30);

ALTER CREATE DROP:DDL Language (data definition language)

INSERT UPDATE DELETE:D ml language (data manipulation language)

SELECT:D QL Language (data query language)
SELECT * from employee;

5.CRUD operation
1. Insert:
insert into table name [(column name 1, column name 2)] VALUES (value 1, value 2);
Character and date data should be enclosed in single quotes.
INSERT into employee (Id,name,gender,birthday,salary,entry_date,resume)
VALUES (1, ' cgx ', ' Male ', ' 1982-10-1 ', 10000, ' 2000-10-1 ', ' good service work ');

INSERT INTO employee
VALUES (2, ' AJ ', ' Women ', ' 1982-10-1 ', ' 2000-10-1 ', ' actors ', 100, ' good service ');

2. Handling garbled problems of adding and querying data
Show variables like ' character% ';
Set character_set_client=gbk;//processing the encoding that the client submits data to the server
The encoding of the result of the Set character_set_results=gbk;//processing server response in the client

2.
Delete data
Delete from table name "Where condition"
Delete from employee where name= ' 2 jiao ';

Delete from employee;//clears all records in the table
TRUNCATE TABLE employee;//clears all records in the table

Difference:
1.truncate table Deletes data more efficiently (destroys the entire table structure before rebuilding the table)
2.delete Delete all records, one bar is deleted
3.truncate table can only delete all
4.delete can selectively delete part of the record

3. Update
Update table name set column name = value, column name 2= value 2 where condition;
Update employee set Name= ' 2 jiao ', gender= ' man ' where name= ' AJ ';

4. Enquiry
Select column name [as] change column name, column name from table name "Where Condition order by sort field (ASC (default) ascending | DESC) "
Use * to represent all columns
Distinct can eliminate duplicate data (when queried)
Order BY if more than one sort field is specified, the first field is prioritized, and if the first field is the same value, the second field sort is considered

Example:
Query the information for all students in the table.
SELECT * from student;
Check the names of all the students in the table and the corresponding English scores.
Select Name,english from student;
Filter repeating data in a table (English score (only once for the same score)).
Select distinct 中文版 from student;
Add 10 extra-long points to all students ' math scores. Display
Select name,math+10 from student;
Select name as name, math+10 as math score from student;
Count each student's total score.
Select Name,chinese+math+english from student;
Use aliases to represent student scores.
Select name Name, Chinese+math+english total from student;

Sort: Order by sort field (ASC (default ascending) desc descending)
Use aliases to represent student scores and rank from highest to lowest
Select name Name, Chinese+math+english total score from student order by total: desc;

Search for student's grades with name Harry
SELECT * FROM student where name= ' Harry ';
Query students with English scores greater than 90 points
SELECT * FROM student where english>90;
Find all students with a total score greater than 200
SELECT * FROM student where (chinese+math+english) >200;

Operator
(<>! =)
Between ... And ... is a closed interval (with the head including the tail)
In (set) refers to a collection of elements can be
Like statement,% represents 0 or more arbitrary characters, _ represents a character, example first_name like ' _a% '

is null; Represents a column if the value is NULL SELECT * from student where name is null;

And Or not

Example:
Check the English score between 80-90 students.
SELECT * FROM student where 中文版 between and 90;
SELECT * FROM student where english>=80 and english<=90;
Query math scores for 89,90,91 's classmates.
SELECT * FROM student where math in (89,90,91);
SELECT * FROM student where math=89 or math=90 or math=91;
All students surnamed Li are queried for their grades.
SELECT * FROM student where name like ' Li% ';
Query Math >80, the language of >80 students.
SELECT * FROM student where math>80 and chinese>80;

Sort:
Example:
Sort the math scores after the output.
SELECT * FROM student order by math;

SELECT * FROM Student ORDER by math ASC, second sort field (ASC|DESC);
The second sort field is introduced when the first field takes the same value, the second sort field is enabled (dictionary shun)
SELECT * FROM Student ORDER by math ASC, name DESC;
Sort the output after the total score, and then output in order from high to low
Select name Name, Chinese+math+english total score from student order by total: desc;
Sort out the grades of students surnamed Li
SELECT * FROM student where name like ' Li% ' order by chinese+math+english

6. Multi-table operation
Entity integrity constraints:
Specifies that a row of the table (that is, each record) is the only entity in the table. Entity integrity is implemented through the table's primary key (primary key).
Characteristics of PRIMARY key: uniqueness, non-nullability
Primary KEY classification: Physical primary key, logical primary KEY (business primary key) in the actual development of the main use is the business primary key
CREATE TABLE Student (
ID int PRIMARY KEY,
Name varchar () unique NOT NULL,
Chinese float,
中文版 float,
Math float
);

INSERT into student (Id,chinese,english,math) values (1,89,78,90);
INSERT into student (Id,chinese,english,math) values (4,67,98,56);
INSERT into student (Id,name,chinese,english,math) VALUES (3, ' Harry ', 87,78,77);
INSERT into student (Id,name,chinese,english,math) VALUES (4, ' Lee ', 88,98,90);
INSERT into student (Id,name,chinese,english,math) VALUES (5, ' disparities Wealth ', 82,84,67);
INSERT into student (Id,name,chinese,english,math) VALUES (6, ' Zhang Jinbao ', 55,85,45);
INSERT into student (Id,name,chinese,english,math) VALUES (7, ' Huang Rong ', 75,65,30);

Auto_increment: The type that is only for this column is an integer, which represents auto-growth (1,2,3,4,5 grows sequentially)
: It should be compatible with primary key
CREATE TABLE Score (
ID int primary KEY auto_increment,
中文版 Float (5,2)
);

Insert INTO score (中文版) values (67);
Insert INTO score (中文版) values (89);
Insert INTO score (中文版) values (98);

Delete from score where id = 3;
INSERT into score (Chinese) values: # ID starting from 4 if you want to automatically grow from 1, you can delete the table before adding data

Data redundancy: Data duplication

Domain integrity constraints: Ensure that a field meets the requirements
NOT NULL
Unique: only (invalid for null value)

CREATE TABLE Orders (
ID int PRIMARY KEY,
OrderNo varchar (+) NOT NULL,
Price Float (8,1)
);
INSERT into orders (Id,orderno,price) values (1,null,1000);

drop table orders;
CREATE TABLE Orders (
ID int PRIMARY KEY,
OrderNo varchar (+) Unique,
Price Float (8,1)
);
INSERT into orders (Id,orderno,price) VALUES (1, ' 123 ', 1000);
INSERT into orders (Id,orderno,price) values (2,null,1000);
INSERT into orders (Id,orderno,price) VALUES (3,null,4000), #可以添加进去, invalid for null values
Solve the problem? You can add unique and not NULL to this column at the same time
drop table orders;
CREATE TABLE Orders (
ID int PRIMARY KEY,
OrderNo varchar (+) unique NOT NULL,
Price Float (8,1)
);
INSERT into orders (Id,orderno,price) VALUES (1, ' 123 ', 1000);
INSERT into orders (Id,orderno,price) VALUES (2, ' 123 ', 1000);

Referential integrity constraints
Problem? There is a score in the score table (its student number is not in the students table)
Order (the customer number of the order should be found in the Customer table)
Workaround: Add a foreign key to the table
Add foreign key when building table: Constraint foreign Key name foreign key (foreign key field name) References primary key table (primary key field)
First build the table and then add the foreign key:
ALTER TABLE name add Constarint fk_employee_dept_id foreign key (dept_id) references departement (ID)

Table-to-table relationships:
One-to-one (a person can only have one ID number)
One-to-many (a customer can place multiple orders, one order can only be one customer) (the most used in development)
CREATE TABLE Cust (
ID int PRIMARY KEY,
Name varchar (NOT NULL)
);

INSERT INTO Cust values (1, ' cgx ');
INSERT INTO Cust values (2, ' AJ ');
INSERT INTO Cust values (3, ' zxc ');

drop table orders;
CustomerID can not be written at will, it should be from the Cust table in the ID of this column value
CustomerID also known as the Foreign key column (Foreign key field)
CREATE TABLE Orders (
ID int PRIMARY KEY,
OrderNo varchar (100),
Totalprice Float (7,2),
CustomerID int,
Constraint Fk_orders_customerid foreign KEY (CustomerID) references Cust (ID)
);
INSERT into orders values (1, ' wqewq234324 ', 1000,4); #出错
INSERT into orders values (1, ' wqewq234324 ', 1000,3); #成功

Delete from Cust where id=3; #删除失败 because it now has an associated subkey, and when the parent is deleted, the associated subkey is
The parent item cannot be found, so the deletion failed
Solve:
First, clear the subkey, and then delete yourself
Delete from orders where customerid=3;
Delete from Cust where id=3;

Second, remove the foreign key
ALTER TABLE name drop FOREIGN key foreign key name;
ALTER TABLE orders drop foreign key fk_orders_customerid;

Delete from Cust where id=3;

Many-to-many (a teacher can teach multiple students, a student can also be taught by multiple teachers)
Use an intermediate table to describe the relationship:
Federated primary Key: Primary key (Field 1, Field 2)

Connection query:
Cross join: Returns the Cartesian product of all data rows in the join table without an ON clause.
SELECT * from A crosses join B; (explicit cross-joins)
SELECT * from A, B; (Implicit cross-connect)

INNER JOIN (INNER JOIN): Returns the data rows in the join table that meet the join criteria and query criteria.
SELECT * FROM A inner join B on (join condition) WHERE condition
Example:
Find all orders for Edison
SELECT * FROM customer A INNER join orders B on (a.id=b.customer_id) where a.name= ' Edison ';
Find out all the people who have placed orders and order information
SELECT * FROM customer A INNER join orders B on (a.id=b.customer_id)

Outer joins: The left outer join (right-outer join), and the outer join on the outside. Unlike an inner join, an outer join returns not only data rows that meet the join condition and query criteria in the join table, but also data rows that meet the query criteria but do not meet the join criteria in the left table (when the left outer join) or the right table (when the right outer joins).
SELECT * from A LEFT join B on (join condition) WHERE condition
Features: The left table can be connected to the right table on the record display, but also the left table is not connected to all other records are also displayed
Example:
SELECT * from the customer left join orders on (CUSTOMER.ID=ORDERS.CUSTOMER_ID);

SELECT * from A right join B on (join condition) WHERE condition
Feature: Keep the records in the right table
Example:
SELECT * from the customer right join orders on (CUSTOMER.ID=ORDERS.CUSTOMER_ID);
SELECT * FROM orders right join customer on (customer.id=orders.customer_id);

Sub-query
SELECT * from table name where field name in (select field name from table name where condition); (the query that is placed outside is called the parent query, and the query that is placed inside is called a subquery)
A subquery can appear in the Select, from, where section.
Example: Querying all orders for Edison
Select ID from customer where name= ' Edison Chen ';
Select id,order_number,price,customer_id from Orders
where customer_id in (select ID from customer where name= ' Edison ');
Federated queries
SELECT * FROM A
Union
SELECT * FROM B
Combine records in Table A with records in table B and eliminate duplicate records

SELECT * FROM Orders where price>200
Union
SELECT * FROM Orders where customer_id in (select ID from customer where name= ' Edison ');

Report Query
SELECT * FROM table name where Condition GROUP by Group Field having (condition after grouping)

Aggregation functions:
COUNT (): Number of statistics
SUM (): Sum
AVG (): Averaging
Max () max value
Min () Min value

Example:
How many students are there in a class?
Select COUNT (*) from student;
How many students with a statistical math score greater than or equal to 90?
Select COUNT (*) from student where math>=90;
What is the number of people with total statistics greater than 250?
Select COUNT (*) from student where (math+chinese+english) >250;
Ask for a class math average score?
Select AVG (math) from student;

To find the average score of a class total
Select AVG (math+chinese+english) from student;
Select SUM (math+chinese+english)/count (*) from student;

To find the highest and lowest grades of class
Select Max (math+chinese+english) Highest score, min (math+chinese+english) lowest score from student;

Displays the total price of each type of item after sorting the items in the order form
Select Product, SUM (price) from ORDERSS group by product;
Inquire about the purchase of several kinds of goods, and each kind of total price more than 100 of goods
Select Product, SUM (price) from the Orderss group by product have (SUM (price) >100);

7. Data Backup and restore
1. Database backup:
Windows command line:
Mysqldump-h Localhost-u root-p[can also add password] Day14>d:/day14.sql

2. Restore
1.WINDOWS command
Mysql-u Root-psorry database name (to pre-exist) <test.sql (SQL file location)

2.MYSQL command
1. Ensure that the database exists
Create Database day14;
2.source SQL file All locations
SOURCE D:/mysql.sql;

Summary of Basic SQL operations