MySQL basic operation of the single table and delete and change

First, add data.

--1. Create a table and insert certain data.

1 CREATE TABLESTUDENT (2IdINT,3USERNAMEVARCHAR( -),4ServletINT,5JspINT,6ADDRESSVARCHAR( -)7 );8 INSERT  intoSTUDENTVALUES(1,'Zhang San', at, -,'Jingkou District');9 INSERT  intoSTUDENTVALUES(2,'John Doe', $, About,'Ruizhou District');Ten INSERT  intoSTUDENTVALUES(3,'Harry', About, -,'Jingkou District'); One INSERT  intoSTUDENTVALUES(4,'Zhao Liu', the, -,'Jingkou District'); A INSERT  intoSTUDENTVALUES(5,'Li Qi', the,98,'Grand Port District'); - INSERT  intoSTUDENTVALUES(6,'Zheng', About, the,NULL); - INSERT  intoSTUDENTVALUES(7,'Mr Michael Suen', -, -,"');

--2. Inserting part of the data

INSERT  into VALUES (8,' li 20 ');

Ii. Modification of data

--1. Modifying data based on conditions

UPDATE SET =  the WHERE = 1;

--2. Modifying the contents of multiple fields

UPDATE SET =  $ =  the WHERE = 2;

Third, delete data (no demo)

--7. Delete all data

--can be conditionally deleted, can only delete the table's data, can not delete the table constraints, that is, since the growth of the value of the last deleted value growth, delete data can be rolled back.
DELETE from STUDENT;

--8. Conditional delete
DELETE from STUDENT WHERE SID = 2;
--9. Another way to delete all the data in the table

--You can delete the table's data or delete the constraint of the table and delete it permanently without conditional deletion.

TRUNCATE TABLE STUDENT;

Four, single-table query

--1. Querying all Columns

SELECT *  from STUDENT;

--2. Querying a specified column

SELECT  from STUDENT;

--3. Specify aliases when querying
--Frequent use of table aliases when querying multiple tables

SELECT  as ' numbering '  as ' User name '  from  as S

--Add a constant column at 4.A query

SELECT ID, USERNAME,' This is a class 'as' remark ' from STUDENT;

--5. Merging columns when querying
--Query the overall scores of each student's servlet and JSP
--Merge columns can only merge fields of numeric types.

SELECT ID, USERNAME, (SERVLET+as'   total ' from STUDENT;

--6. Query to remove duplicate records
--Query all JSP results can appear

SELECT DISTINCT  from STUDENT;

--Another syntax

SELECT DISTINCT  from STUDENT;

--7. Conditional query (WHERE)
--7.1 Logical conditions and OR
--The query SID is 2, and the student named John Doe

SELECT *  from WHERE = 2  and = ' John Doe ';

--7.1.1 Query SID is 2, or student named Zhang San

SELECT *  from WHERE = 2 OR = ' Zhang San ';

--7.2 Comparison conditions > < >= <= = = <> between and
--7.2.1 Query SERVLET students with a score greater than 70

SELECT *  from WHERE > ;

--7.2.2 Query SERVLET scores greater than 60 points less than 80 students

SELECT *  from WHERE >  -  and < ;

--7.2.3 Query SERVLET scores greater than or equal to 70 students less than or equal to 80

SELECT *  from WHERE >=  -  and <= ;

--7.2.4 Another kind of grammar

SELECT *  from WHERE between  -  and ;

--7.2.5 query name is not equal to Zhang San record

SELECT *  from WHERE <> ' Zhang San ';

--7.3 Empty (Null empty string) is null = "<>"
--7.3.1 judgment NULL

SELECT *  from WHERE  is NULL;

--7.3.2 Judge empty string

SELECT *  from WHERE = ";

--7.3.3 Query the record with empty address

SELECT *  from WHERE  is NULL OR = ";

--7.3.4 The query address is not empty records

SELECT *  from WHERE  is  not NULL  and <> ";

--7.4 fuzzy conditions like
--typically use the following replacement tag:%: Denotes any character; _: Represents a character
--7.4.1 records of students surnamed Li

SELECT *  from WHERE  like ' Li% ';

--8. Aggregate query
--Common aggregate function: SUM () AVG () MAX () MIN () COUNT ()
--8.1 Query the student servlet total

SELECT SUM  as ' the total of the servlet '  from STUDENT;

--8.2 Query the average student SERVLET score

SELECT AVG  as ' the average servlet score '  from STUDENT;

--8.3 Query the current SERVLET's highest score

SELECT MAX  as ' Highest score '  from STUDENT;

--8.4 Minimum points for query servlet

SELECT MIN  as ' Minimum score '  from STUDENT;

--Query How many students are currently in COUNT (field)

SELECT COUNT (* from STUDENT;

--9. Query the LIMIT start line and query several rows. Mainly used for paging.
--Query 1th, 2 records

SELECT *  from 0,2;

--10. Sort by default in the order in which records are inserted. ORDER by
--10.1ASC Positive order DESC Reverse

SELECT *  from ORDER  by DESC;

--10.2 Follow the servlet positive sequence, then the JSP reverse

SELECT *  from ORDER  by ASC DESC;

--11. GROUP BY
--Check the number of people in each region

SELECT Address,COUNT(*fromGROUP by ADDRESS;

--12. Filter After grouping query
--after querying the area after the group has more than 1 people area group by can no longer be followed by a condition where

SELECT ADDRESS,Count(*fromGROUPby hasCOUNT(* )>1;

MySQL basic operation of the single table and delete and change