MySQL (a)

The main is some SQL statements, the implementation of the good

will produce an attachment in the back, which is some database knowledge and some SPL statements

Below to implement some of the statements.

--Write SQL statements, send to server execution

--You can write notes in the back

--Create a library, DAY16

CREATE DATABASE day16 DEFAULT CHARACTER SET UTF8;

--Using the DAY16 database

Use DAY16;

--Query DAY16 all the tables in this library

SHOW TABLES;

--demand: Create student table (Id,name,age)

CREATE TABLE Student (

--Field Name field type

ID INT,

NAME VARCHAR (20),

Age INT

);

--Querying the data in a table

SELECT * from student;

--Querying a table structure

DESC student;

--Delete Student table

DROP TABLE student;

--Add an address field (gender varchar (2))

ALTER TABLE student ADD COLUMN gender VARCHAR (2);

--Modify the Gender field type to varchar (3)

ALTER TABLE student MODIFY COLUMN Gender VARCHAR (3);

--Modify the gender field to the Sex field varchar (2)

ALTER TABLE student Change COLUMN gender sex VARCHAR (2);

--Add an address field

ALTER TABLE student ADD COLUMN address VARCHAR (20);

--Delete the sex and address fields

ALTER TABLE student DROP column sex,drop column address;

--Change the table name of the student table to Stu

ALTER TABLE Stu RENAME to student;

--View the table's data

--The Select field name (if all fields are queried *) from student;

--View Table data

SELECT * from student;

--Insert 3 data into the table (insert all fields)

INSERT into student VALUES (1, ' Andy Lau ', 50, ' Hong Kong, China ');

INSERT into Student VALUES (2, ' Eason Chan ', 40, ' Hong Kong, China ');

INSERT into Student VALUES (3, ' Han Hong ', 50, ' Hebei ');

--Can we insert some fields straight when we need to insert all the fields? Can't insert

INSERT into student VALUES (1, ' Dragon ', 60, ' Hong Kong ');

--point to the table where you want to insert only the ID field and the Name field, what should I do?

INSERT into student (Id,name) VALUES (4, ' Guo Degang ');

--Change the age of all students to 50 years, and modify the age of all students with very little

UPDATE student SET age=50;

--Change the name of the student with ID 3 to Jaycee name

UPDATE student SET name= ' Jaycee name ' WHERE id=3;

--Modify multiple fields, change the name of the student with ID 2 to Jacky Cheung, change age to 60

UPDATE student SET name= ' Jacky ', age=60 WHERE id=2;

--Delete the whole table data, essentially one by one to delete, the efficiency is relatively slow

DELETE from student;

--delete data with ID 3

DELETE from student WHERE id=3;

--Use TRUNCATE table name, delete the whole table data, not a strip of delete, but directly delete the whole table data, the efficiency is relatively fast

TRUNCATE TABLE student;

--What is the difference between delete from and TRUNCATE table in the two ways of deleting the whole table?

--1.delete from the Strip delete, truncate TABLE directly kill all the data

--2.delete from can delete a piece of data conditionally, TRUNCATE table can only delete whole table data, cannot be deleted by condition

--3.delete from cannot reset self-growing primary key, TRUNCATE TABLE can reset self-growing primary key

--Querying data

--Query all columns

SELECT * from student;

--Query the specified field, query Id,name

SELECT id,name from student;

--Specify alias, name--name, address--address when querying

SELECT name as ' name ', address as ' address ' from student;

--the alias specified above can be omitted.

SELECT name ' name ' from student;

--Add servlet,jsp field

ALTER TABLE Student ADD column servlet int,add column JSP INT;

--add servlet and JSP scores to each student's data

UPDATE student SET servlet=50,jsp=60 WHERE id=1;

UPDATE student SET servlet=60,jsp=70 WHERE id=2;

UPDATE student SET servlet=70,jsp=80 WHERE id=3;

--Merge the servlet and JSP two columns to query, query each student's servlet and JSP's score sum

--Merge column queries have one feature: only fields of numeric types can be combined

SELECT name ' name ', (servlet+jsp) ' total ' from student;

--Add a constant column when querying, add a constant column to the Student table class--java001

SELECT name ' name ', address ' addresses ', ' java001 ' as ' class ' from student;

--Find out where the students in your class come from

SELECT name ' name ', address ' addresses ' from student;

--Remove duplicate values to find out where each student comes from

SELECT DISTINCT address from student;

--Another way to remove duplicate values

SELECT DISTINCT (address) from student;

--The query ID is 1 and the servlet score equals 50 students (intersection and)

SELECT * FROM student WHERE id=1 and servlet=50;

--Query ID 1 or students from Hong Kong, China (and set or)

SELECT * FROM student WHERE id=1 OR address= ' Hong Kong, China ';

--Query for students with a servlet with a score greater than 60

SELECT * FROM student WHERE servlet>60;

--Query JSP score is less than equal to 70 students

SELECT * FROM student WHERE jsp<=70;

--Another way of writing

SELECT * FROM student WHERE jsp<70 OR jsp=70;

--query JSP with scores greater than or equal to 70 and less than or equal to 80 students

SELECT * FROM student WHERE jsp<=80 and jsp>=70;

--the other syntax of the above notation, Between...and ... Pack before the package also

SELECT * FROM student WHERE JSP between and 80;

--Check with students ages 30 years old

SELECT * FROM student WHERE age<>30;

--Add a data to the student table

INSERT into Student VALUES (4, ' Guo Degang ', NULL, ' ', 80,90);

--Query for a student with an age field of NULL (IS null)

SELECT * FROM Student WHERE-is NULL;

--Query for students with an empty string in the address field (= ')

SELECT * FROM student WHERE address= ';

--query for students who are not NULL in the Age field (is not null)

SELECT * FROM student WHERE-is not NULL;

--Query the Address field is not "students (<>")

SELECT * FROM student WHERE address<> ';

--fuzzy query (like), followed by the symbol

--% any number of characters

-_ One character

--Query the student surnamed Liu (like ' Liu ')

SELECT * FROM student WHERE NAME like ' Liu% ';

--search for students whose names contain the word Liu

SELECT * FROM student WHERE NAME like '% Liu ';

--inquire about a student surnamed Liu with 3 characters in name

SELECT * FROM student WHERE NAME is like ' Liu __ ';

--Aggregate function

--Query the total of the servlet (Sum-sum function)

SELECT SUM (servlet) from student;

--Query the average servlet score for each student (AVG, average function)

SELECT AVG (servlet) from student;

--Query the highest score of the student's servlet (max, Max function)

SELECT MAX (servlet) from student;

--Query the minimum score for all the students ' servlets (min, find minimum function)

SELECT MIN (servlet) from student;

--Find out how many data (count (*)) is in this student table

-Low efficiency

SELECT COUNT (*) from student;

--Calculates the number of data bars in a student table according to a field, which is not calculated when the value of a field is null

--but it's more efficient to get the stats in this way, but sometimes the data isn't accurate.

SELECT COUNT (age) from student;

--How many students in Hong Kong and Hebei are in this class.

--1. Use Address to Group (group by) 2. Find out the number of students in each group

--Address count

--Hong Kong 2

--Hebei 1

SELECT Address,count (*) from student GROUP by address;

--Search for regions with more than 1 people (Group by Group Field has filter)

SELECT Address,count (*) from student GROUP by address have COUNT (*) >1;

--Paged query limit start number of rows, number of queries Note: Starting line number starting from 0

--Suppose I have 20 data in my table, and 4 is also shown

--first page: Limit 0,5

--page Two: Limit 5,5

--third page: Limit 10,5

--page Fourth: Limit 15,5

--Conclusion: The following formula can be used to query the actual data of a page (current page-1) * Number of bars per page, actual number of pages per page

--Student table currently has 4 data, divided into 2 pages, showing 2 per page

--Query the display data on the first page:

SELECT * FROM student LIMIT 0, 2;

--Query The second page of realistic data

SELECT * FROM Student LIMIT 2, 2;

--Sort by the ascending order of the ID field

--ASC, ascending, number from small to large, letter A-Z

--desc, descending, numbers from big to small, letters Z-A

SELECT * FROM student ORDER by ID DESC;

--Sort in descending order of the servlet's grades

SELECT * FROM student ORDER by servlet DESC;

--when there are multiple sorting criteria, sort by the first condition, and then sort by the second condition if the first condition is the same

--First sorted according to the age of the students in ascending order, the same age according to the students ' Servelt grades

SELECT * FROM student ORDER by age Asc,servlet ASC;

DESC student;

SELECT * from student;

-- ------------------------------------------------------------------

--Data constraint: Add some data constraints to the table to achieve the effect of constraining user operation data

--1: Default value constraint

-: When a value is not added to this field, a default value is given, and if the value of the field to which the default value is constrained is null, then his field value is null

--Create a Stu table

CREATE TABLE Stu (

ID INT,

NAME VARCHAR (20),

--to the gender gender this field adds a default value constraint

Gender VARCHAR (2) DEFAULT ' Male '

);

--Add a few data to the Stu table

INSERT into Stu VALUES (1, ' Zhang San ', ' Male ');

INSERT into Stu (id,name) VALUES (2, ' John Doe ');

--Insert data into Stu table, gender is null

INSERT into Stu VALUES (3, ' Liu poem ', NULL);

--2. A non-null constraint (NOT NULL), the inserted field is not NULL, and the data must be inserted

CREATE TABLE Stu (

--Add a non-null constraint to the ID field in the Stu table

ID INT not NULL,

NAME VARCHAR (20),

Gender VARCHAR (2)

);

--Add an element to this table without inserting the value of the ID field

INSERT into Stu (name,gender) VALUES (' Guo Degang ', ' Male ');

--Add a piece of data to this table, the value of the ID field is given directly to NULL, so the insertion is not in.

-Unique constraint (unique)

--Add a unique constraint to the Stu table

CREATE TABLE Stu (

--Add a unique constraint to the ID field in the Stu table

ID INT UNIQUE,

NAME VARCHAR (20),

Gender VARCHAR (2)

);

--Insert two data with the same ID in the table

INSERT into Stu VALUES (1, ' Andy Lau ', ' Male ');

Insert into Stu values (1, ' Jacky ', ' Male ');--Duplicate entry ' 1 ' for key ' id ', unable to insert duplicate ID value

--Inserts a null-ID data into the table, and when a unique constraint is added to the ID, it is still possible to insert multiple null values without repeating

INSERT into Stu VALUES (NULL, ' Wu ', ' Male ');

INSERT into Stu VALUES (NULL, ' Liu poem ', ' Female ');

--After our analysis, we identified our ID field (unique + non-null)--primary key (primary key)

--Note:

--1. In general, we need to set a primary key field (non-null + unique) for each table to indicate the uniqueness of a single message.

--2. We generally do not set the business field as the primary key field, such as the Name field, generally we will add an ID field to each table as the key field

--3. It is recommended to add a primary key field to each table to indicate the uniqueness of each piece of data

CREATE TABLE Stu (

--Set the primary key for the ID field in the Stu table (unique + non-empty)

ID INT PRIMARY KEY,

NAME VARCHAR (20),

Gender VARCHAR (2)

);

--Insert two student information with ID 1 for table

INSERT into Stu VALUES (1, ' Andy ', ' Male ');

INSERT into Stu VALUES (1, ' Huajian ', ' Male ');--Duplicate entry ' 1 ' for key ' PRIMARY '

--Insert an element with an ID of NULL in the table

INSERT into Stu VALUES (NULL, ' Jay ', ' Male ');--Column ' ID ' cannot be null

The above experiment we can conclude that when we set the PRIMARY KEY constraint for the ID field, the ID field is not empty + unique

--Self-growth constraint (auto_increment)

--Add a primary key self-growth constraint to the ID field in the Stu table

CREATE TABLE Stu (

--to set the primary key self-growth constraint for the ID field in the Stu table, we actually gave the ID this field to the database to maintain itself, we do not need to move him

ID INT PRIMARY KEY auto_increment,

NAME VARCHAR (20),

Gender VARCHAR (2)

);

--Add two data to the Stu table

INSERT into Stu (name,gender) VALUES (' Wah zi ', ' male ');

INSERT into Stu (name,gender) VALUES (' Jay Chou ', ' Male ');

INSERT into Stu (name,gender) VALUES (' Jay Chou ', ' Male ');

INSERT into Stu (name,gender) VALUES (' Jay Chou ', ' Male ');

--delete data with ID 4

DELETE from Stu WHERE id=4;

--Add a piece of data to the table

INSERT into Stu (name,gender) VALUES (' Jacky ', ' Male ');

--Delete from this method of deleting data, cannot reset self-growing primary key

--Delete Stu's full table data

DELETE from Stu;

--Add a piece of data

INSERT into Stu (name,gender) VALUES (' Jacky ', ' Male ');

--Delete the TRUNCATE TABLE table name of the full table data, delete the whole table data, the way to delete the whole table data can reset the primary key

TRUNCATE TABLE Stu;

--Add a piece of data to the table

INSERT into Stu (name,gender) VALUES (' Wah zi ', ' male ');

SELECT * from Stu;

DROP TABLE Stu;

MySQL (a)