Base statement for SQL

Use DAY15;

--Create a table
CREATE TABLE Teacher (
ID INT,
NAME VARCHAR (20)
)
--View All tables
SHOW TABLES;

DESC student;

DROP TABLE student;

CREATE TABLE Student (
ID INT,
NAME VARCHAR (20),
Gender VARCHAR (2),
Age INT
)

--******** Data *********---
--1.1 Additional data
--Insert all fields. Must sequentially insert
INSERT into student VALUES (1, ' Zhang San ', ' Male ', 20);
--Note that you cannot have fewer or more field values
--INSERT into student VALUES (2, ' John Doe ', ' female ');
--Insert Some fields
INSERT into student (Id,name) VALUES (2, ' John Doe ');

--1.2 Modifying data
--Modify all data (less recommended)
UPDATE student SET gender= ' female ';
--Modified with conditions (recommended)
UPDATE student SET gender= ' man ' WHERE id=1; --Modify the student with ID 1, change gender to male
--Modify multiple fields, note: SET field name = value, field name = value,....
UPDATE student SET gender= ' male ', age=30 WHERE id=2;

--1.3 Delete data
--Delete all data (less recommended)
DELETE from student;
--Conditional removal (recommended)
DELETE from student WHERE id=2;
--Another way
--Delete from: can delete all tables 1) can be conditionally deleted 2) can only delete the table's data, cannot delete the table's constraints 3) using delete from the deleted data may be rolled back (transaction)
--TRUNCATE TABLE: can delete all tables 1) cannot be conditionally deleted 2) that is, you can delete data from a table, or you can delete a table constraint 3) data deleted using TRUNCATE TABLE cannot be rolled back
TRUNCATE TABLE student;

CREATE TABLE Test (
ID INT PRIMARY KEY auto_increment, self-growth constraint
NAME VARCHAR (20)
)
DESC test;

--1.
DELETE from Test;
--2
TRUNCATE TABLE test;

INSERT into Test (NAME) VALUES (' Zhang San ');
INSERT into Test (NAME) VALUES (' 32 ');
INSERT into Test (NAME) VALUES (' 33 ');

SELECT * from Test;

--TRUNCATE TABLE student where id=2; Cannot bring condition

--Querying data
SELECT * from student;

--******** second, query data (SELECT) *******--
--2.1 Querying all columns
SELECT * from student;

--2.2 Query the specified column
SELECT Id,name,gender from student;

--2.3 Specify alias (AS) when querying
--Note: The table alias is frequently used in multiple table queries
SELECT ID as ' number ', name as ' name ' from student;

--2.4 Adding a constant column when querying
--Demand: Add a class column when querying the student table for "Java Employment class"
SELECT id,name,gender,age, ' Java Employment class ' as ' grade ' from student;

--2.5 merging columns when querying
--Demand: Query the overall scores of each student's servlet and JSP
SELECT Id,name, (servlet+jsp) as ' total ' from student;
--Note: Merge columns can only merge fields of numeric types
SELECT ID, (name+servlet) from student;

--2.6 Removal of duplicate records when querying (DISTINCT)
--Demand: Check the gender of the students
SELECT DISTINCT gender from student;
--Another syntax
SELECT DISTINCT (gender) from student;
--demand: Find out where students are located
SELECT DISTINCT address from student;

--2.7-piece query (where)
--2.7.1 Logical condition: and (with) or (or)
--Demand: Query ID 2, and the student named John Doe
SELECT * FROM student WHERE id=2 and name= ' John Doe '; --Intersection

--Demand: Query ID 2, or student named Zhang San
SELECT * FROM student WHERE id=2 OR name= ' Zhang San '; --and set

--2.7.2 Comparison conditions: > < >= <= = <> (not equal to) between and (equivalent to >= and <=)
--Demand: query for students with a servlet score greater than 70 points
SELECT * FROM student WHERE servlet>70;

--demand: Query JSP score is greater than or equal to 75, and less than or equal to 90 points of students
SELECT * FROM student WHERE jsp>=75 and jsp<=90;
--Another syntax
SELECT * FROM student WHERE JSP between and 90; --(after the package before package)

SELECT * FROM student WHERE gender<> ' man ';

--2.7.3 empty condition (null null string): is null/is not null/= '/<> '
--Requirements: Students who are queried for empty addresses (both null and empty strings)
--Null vs empty string
--Null: Indicates no value
--empty string: a value!
--Judging null
SELECT * FROM student WHERE address is NULL;
--Judging the empty string
SELECT * FROM student WHERE address= ';

SELECT * FROM student WHERE address is NULL OR address= '; --(including null and empty strings)

--Requirements: Querying students with addresses (not including null and empty strings)
SELECT * FROM student WHERE address was not NULL and address<> ';

--2.7.4 fuzzy condition: Like
--Usually the following replacement tags are used:
--%: denotes any character
--_: Represents a character
--demand: The student who inquires the surname ' Zhang '
SELECT * FROM student WHERE NAME like ' li% ';

--demand: A student whose name is ' Li ' and has only two characters
SELECT * FROM student WHERE NAME is like ' li _ ';

---practice--
CREATE TABLE Student2 (
ID INT,
NAME VARCHAR (20),
Chinese FLOAT,
中文版 FLOAT,
Math FLOAT
);

INSERT into Student2 (Id,name,chinese,english,math) VALUES (1, ' Zhang Xiaoming ', 89,78,90);
INSERT into Student2 (Id,name,chinese,english,math) VALUES (2, ' Li Jin ', 67,53,95);
INSERT into Student2 (Id,name,chinese,english,math) VALUES (3, ' Harry ', 87,78,77);
INSERT into Student2 (Id,name,chinese,english,math) VALUES (4, ' Lee ', 88,98,92);
INSERT into Student2 (Id,name,chinese,english,math) VALUES (5, ' disparities Wealth ', 82,84,67);
INSERT into Student2 (Id,name,chinese,english,math) VALUES (6, ' Zhang Jinbao ', 55,85,45);
INSERT into Student2 (Id,name,chinese,english,math) VALUES (7, ' Huang Rong ', 75,65,30);

--Query the information of all students in the table.
SELECT * from Student2;

--Check the names of all the students in the table and the corresponding English scores.
SELECT name,english from Student2;

--duplicate data of English scores in the filter table
SELECT DISTINCT (中文版) from Student2;

--use aliases to represent student scores.
SELECT name as ' name ', Chinese as ' language ', 中文版 as ' English ', math as ' maths ' from Student2;

--Query the student's grades named Lee
SELECT * from Student2 WHERE name= ' Lee ';

--Query students with English scores greater than or equal to 90 points
SELECT * from Student2 WHERE english>=90;

--Query all students with a total score greater than 200
SELECT * from Student2 WHERE (Chinese+english+math) >200;

--Check the English scores of all the students surnamed Li.
SELECT name,english from Student2 WHERE NAME like ' li% ';

--Query the English >80 or the total >200 students
SELECT * from Student2 WHERE english>80 OR (chinese+english+math) >200

--count each student's total score.
SELECT Id,name, (Chinese+english+math) as ' total ' from Student2;

--Add 10 extra-long points to all students ' total scores.
SELECT Id,name, (chinese+english+math+10) as ' total ' from Student2;

SELECT * from student;

--2.8 Aggregate queries (queries using aggregate functions)
--Common aggregate function: SUM () avg () max () min () count ()
--Demand: Query the student's servlet's Total (sum (): Sum function)
SELECT SUM (servlet) as ' servlet total ' from student;

--Demand: Query the average score of a student's servlet
The average of SELECT avg (servlet) as ' servlet ' from student;

--Demand: Query current servlet highest score
SELECT Max (servlet) as ' highest score ' from student;

--Demand: Query the lowest score
SELECT min (servlet) as ' lowest score ' from student;

--Demand: Statistics How many students are currently (count field)
SELECT COUNT (*) from student;

SELECT COUNT (ID) from student;

--Note: the count () function counts the number of data that does not contain null
--Use the Count tab to use a field that does not contain a null value
SELECT COUNT (age) from student;

SELECT * from student;
--2.9 paged query (limit start line, query a few lines)
--start line starting from 0
--Pagination: The current page shows how many bars per page
--Paged Query the current page of the data Sql:select * from Student LIMIT (current page-1) * How many bars per page, how many bars per page;

--demand: Inquiry 1th, 2 (Data on page 1th)
SELECT * FROM student LIMIT 0, 2;
--Query 3rd, 4 records (Data on page 2nd)
SELECT * FROM Student LIMIT 2, 2;
--Query 5th, 6 Records (data on page 3rd)
SELECT * FROM Student LIMIT 4, 2;
--Query 7th, 8 Records (no record does not show)
SELECT * FROM student LIMIT 6, 2;

--2.10 Query sort (order by)
--Syntax: ORDER BY Field Asc/desc
--ASC: Sequential, positive sequence. Value: Increment, Letter: Natural order (A-Z)
-desc: Reverse, reverse order. Value: Decrement, Letter: Natural Inverse (z-a)

--By default, sort by insert record order
SELECT * from student;

--Requirements: Sort by ID Order
SELECT * FROM student ORDER by ID ASC;
SELECT * FROM student the ORDER by ID; --Default positive order

SELECT * FROM student ORDER by ID desc;--reverse order

--Note: multiple sort criteria
--demand: According to the servlet positive sequence, according to the reverse of the JSP
SELECT * FROM student ORDER by servlet asc,jsp DESC;

--2.11 Packet Query (group BY)
-Demand: The number of men and women to inquire
-Expected results:
--Male 3
---female 2
-1) Grouping students by gender (group by gender)
--2) count the number of people per group (COUNT (*))
SELECT Gender,count (*) from student GROUP by gender;

--2.12 Filter After grouping query
--demand: Search for the total number of people greater than 2 gender
--1) Number of men and women in search
-2) screening of records with a population greater than 2 (having)
---Note: Before grouping conditions using the WHERE keyword, before grouping conditions using the HAVING keyword
SELECT Gender,count (*) from student WHERE GROUP by gender have COUNT (*) >2;

--add servlet and JSP score columns to the student table
ALTER TABLE student ADD servlet int,add JSP INT;
ALTER TABLE student ADD servlet INT;
ALTER TABLE Student ADD address VARCHAR (10);
DESC student;
UPDATE student SET servlet=70,jsp=85 WHERE id=1;
UPDATE student SET servlet=65,jsp=90 WHERE id=2;
UPDATE student SET gender= ' female ' WHERE id=2;
UPDATE student SET address= ' guangzhou tianhe ' WHERE id=1;
UPDATE student SET address= ' guangzhou tianhe ' WHERE id=2;
UPDATE student SET address= ' guangzhou panyu ' WHERE id=3;

INSERT into Student VALUES (4, ' Chen vi ', ' male ', 28,75,80, ');
INSERT into student VALUES (5, ' Lee Seven ', ' Male ', 30,64,83,null);
INSERT into student VALUES (6, ' Lee 88 ', ' Male ', 35,67,82, ' Guangzhou Tianhe ');

Base statement for SQL