SQL most commonly used base statement

Check all and record of the day
SELECT * from Sys_student_record where date (check_ins) = Curdate ();


Select R.stu_no,i.name, I.classname,activation_type,check_ins,count (*) as acount from Sys_student_record R,sys_studen T_info I where activation_type=3 and r.teacher_no=031234510 and R.stu_no=i.stu_no and date (check_ins) = Curdate () Group B Y r.stu_no ORDER by acount Desc;



Query Day:
SELECT * FROM table where to_days (column_time) = To_days (now ());
SELECT * FROM table where date (column_time) = Curdate ();
Query one week:
SELECT * FROM table where Date_sub (Curdate (), INTERVAL 7 day) <= DATE (column_time);
Query one months:
SELECT * FROM table where Date_sub (Curdate (), INTERVAL 1 MONTH) <= DATE (column_time);
Query a specified number of days
SELECT * FROM table where Date_sub (Curdate (), INTERVAL 2 day) <= DATE (column_time);
Query two weeks
SELECT * from Orders where Date_sub (Curdate (), INTERVAL 2 WEEK) <= DATE (column_time)



Check the consumption record of the day:
Select sum (spent) form from Sys_student_record where date (check_ins) = Curdate ();

sql:structured Query Language (Structured Query language)

User name and password: root


Create a database called MYDB1.
Create Database mydb1;

View all databases
show databases;

Create a MYDB2 database that uses the Utf-8 character set.
Create database mydb2 character set UTF8;

Create a MYDB3 database that uses the Utf-8 character set and with the proofing rules.
Create database mydb3 character set UTF8 collate utf8_general_ci;

To display library creation information
Show CREATE Database mydb3;

Delete the previously created MYDB1 database
Drop database mydb1;

View the database in the server and modify the character set of one of the libraries to gb2312;
ALTER DATABASE MYDB2 character set gb2312;
Show CREATE Database mydb2;

Backup Library
1. Prepare the data for the library
Create Database mydb1;
Use MYDB1;
CREATE TABLE Test
(
ID int
);
INSERT into test (ID) values (1);
SELECT * from Test;

2. Backup Library
2.1 Exiting the MySQL client: quit
2.2 In the Windows command-line window: Mysqldump-uroot-p mydb1>c:\test.sql

3, delete the library: drop database mydb1;

4. Recovery Library (1):
4.1 Creating the Library: Create Database mydb1;
4.2 Source C:\test.sql (implemented by executing a script file)
5. Recovery Library (2): mysql-uroot-p mydb1<c:\test.sql (Window command)


Create an Employee table
Use MYDB1; Enter Library
CREATE TABLE Employee
(
ID int,
Name varchar (20),
Gender varchar (4),
Birthday date,
Entry_date date,
Job varchar (40),
Salary Double,
Resume text
) Character set UTF8 collate utf8_general_ci;

View all tables in the library
Show tables;

View the creation details of a table
Show create table employee;

View the structure of a table
DESC employee;

An image column is basically added to the employee table above.
ALTER TABLE employee add image blob;

Modify the job column so that it has a length of 60.
ALTER TABLE employee Modify job varchar (60);

Delete the sex column.
ALTER TABLE employee drop gender;

The table name is changed to user.
Rename table employee to user;

Modify the table's character set to Utf-8
ALTER TABLE user character set gb2312;
Show create table user;

Column Name name modified to username
ALTER TABLE user change column name username varchar (20);

Use the INSERT statement to insert information about an employee into a table.
INSERT into employee (Id,username,birthday,entry_date,job,salary,resume) VALUES (1, ' AAA ', ' 1980-09-09 ', ' 1980-09-09 ', ' BBB ', +, ' bbbbbbbb ');

To view the inserted data
SELECT * from employee;


Use the INSERT statement to insert information about an employee into a table.
INSERT into employee (Id,username,birthday,entry_date,job,salary,resume) VALUES (2, ' Little plums ', ' 1980-09-09 ', ' 1980-09-09 ', ' BBB ', +, ' bbbbbbbb ');

Insert a solution after failure
Show variables like ' chara% ';
Set character_set_client=gb2312;


Display the solution after the failure
Set character_set_results=gb2312;

Revise the salary of all employees to 5000 yuan.
Update employee set salary=5000;

Change the salary of the employee named ' AAA ' to 3000 yuan.
Update employee set salary=3000 where username= ' AAA ';

Change the salary of the employee named ' AAA ' to 4000 yuan, the job changed to CCC
Update employee set salary=4000,job= ' CCC ' where username= ' AAA ';

Increase the AAA's salary by 1000 yuan on the original basis.
Update employee set salary=salary+1000 where username= ' AAA ';

Delete records with name ' ZS ' in the table.
Delete from employee where username= ' little plum ';

Deletes all records in the table.
Delete from employee;

Use truncate to delete records in a table.
TRUNCATE TABLE employee;

Query the information for all students in the table.
Select Id,name,chinese,english,math from student;
SELECT * from student;

Check the names of all the students in the table and the corresponding English scores.
Select Name,english from student;

Filter duplicate data in a table.
Select distinct 中文版 from student;

Add 10 points to all students ' English scores.
Select name,english+10 from student;

Count each student's total score.
Select Name, (English+chinese+math) from student;

Use aliases to represent student scores.
Select name as name, (English+chinese+math) as total score from student;
Select name Name, (English+chinese+math) total score from student;

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 (English+chinese+math) >200;

Check the English score between 80-90 students.
SELECT * FROM student where english>80 and english<90;
SELECT * FROM student where 中文版 between and 90;

Query math scores for 89,90,91 's classmates.
SELECT * FROM student where math=80 or math=90 or math=91;
SELECT * FROM student where math in (80,90,91);

All students surnamed Li are queried for their grades.
SELECT * FROM student where name like ' Li% ';

Sort the math scores after the output.
Select Name,math from student order by math;

Sort the output after the total score, and then output in order from high to low
Select name from Student order by (Math+english+chinese) desc;

Sort out the grades of students surnamed Li
Select name Name, (Math+english+chinese) total score from student where name like ' Li% ' order by (Math+english+chinese) desc;

How many students are there in a class?
Select COUNT (*) from student;
Select count (name) from student;

How many students with a statistical math score greater than 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+english+chinese) >250;

Statistic a class math total?
Select sum (math) from student;

Statistics of a class of Chinese, English, mathematics, the total scores of various subjects
Select sum (Math), SUM (Chinese), sum (中文版) from student;

Statistics a class of Chinese, English, mathematics, the sum of the results
Select SUM (chinese+math+english) from student;

Statistic the average score of a class's Chinese score
Select SUM (Chinese)/count (Chinese) from student;

Ask for a class math average score?
Select AVG (math) from student;

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

To find the highest and lowest grades of class
Select Max (Chinese+english+math), Min (Chinese+english+math) from student;

Displays the total price of each type of item after sorting the items in the order form
Select product from the orders group by product;
Select Product,sum (Price) from the orders 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 from Orders GROUP by product have sum (price) >100;


To define a table with a PRIMARY KEY constraint
CREATE TABLE Test1
(
ID int PRIMARY KEY,
Name varchar (20),
Password varchar (20)
);

Define a table with a primary key auto-grow
CREATE TABLE Test2
(
ID int primary KEY auto_increment,
Name varchar (20),
Password varchar (20)
);

CREATE TABLE Test3
(
ID int primary KEY auto_increment,
Name varchar (unique)
);

CREATE TABLE Test4
(
ID int primary KEY auto_increment,
Name varchar () unique NOT NULL
);

What is a FOREIGN key constraint

CREATE TABLE Husband
(
ID int PRIMARY KEY,
Name varchar (20)
);

Create TABLE wife
(
ID int PRIMARY KEY,
Name varchar (20),
husband_id int,
Constraint HUSBAND_ID_FK foreign KEY (husband_id) references husband (ID)
);


One-to-many or many-to-

CREATE TABLE Department
(

)

CREATE TABLE Employee
(

)


Many-to-many

CREATE TABLE Teacher
(
ID int PRIMARY KEY,
Name varchar (20),
Salary Double
);

CREATE TABLE Student
(
ID int PRIMARY KEY,
Name varchar (20)
);


CREATE TABLE Teacher_student
(
teacher_id int,
student_id int,
Primary KEY (TEACHER_ID,STUDENT_ID),
Constraint TEACHER_ID_FK foreign KEY (teacher_id) references teacher (ID),
Constraint STUDENT_ID_FK foreign KEY (student_id) references student (ID)
);

One

CREATE TABLE Person
(
ID int PRIMARY KEY,
Name varchar (20)
);

CREATE TABLE Idcard
(
ID int PRIMARY KEY,
Address varchar (40),
Constraint ID_FK foreign key (ID) references person (ID)
);

This article comes from the "effort ... "Blog, be sure to keep this provenance http://y645194203.blog.51cto.com/8599045/1558851

SQL most commonly used base statement