MySQL's most complete and must-have SQL statement

Create a database named MYDB1, if there is a MYDB1 database is used directly, if none create the MYDB1 database is created if not EXISTS mydb1;create db if not exists mydb1; Create a MYDB2 database that uses the UTF8 character set, note that this is not the utf-8create if not exists MYDB2 character set Utf8;create database if not exists myd B2 character set UTF8; Create a MYDB3 database that uses the UTF8 character set and with the collation rules create db if not exists MYDB3 character Set UTF8 collate Utf8_gen Eral_ci; Proofing rules: is the order in which all records in a database table store data, for example: A before, z in the post character set and proofing rules are one by one corresponding, can not be changed if the proofing rules are not written, the default is the [corresponding character set] default proofing rules. Refer to &LT;&LT;MYSQL5 manual--10.10.10>> view all databases in the current database server show databases; View the definition information for the MYDB1 database created earlier show CREATE DATABASE mydb1 , show create Database MYDB1, delete the previously created MYDB1 databases, delete the drop if exists mydb1;drop if MYDB1 exists; use MYDB1 2 database use Mydb2;use mydb2; View the database in the database server and modify the character set of the MYDB3 library to Gbkalter DB mydb3 character Set Gbk;alter Character set GBK; The following code is to create the users table in the MYDB2 database and insert several records, and the participants are temporarily ignoring the CREATE table if not exists users (name varchar); Insert I Nto users values (' XX '); INSERT into users values (' YY '), insert INTO Users values (' ZZ '); Back up the data in the MYDB1 library to a e:/xx.sql file for future recovery with WINDOW7 (exit to Windows environment) Backup: Mysqldump-uroot-p mydb1 > E:\ Mydb1.sql return mysqldump-uroot-p mydb1 > d:/ Myydb1.sqlmysqldump is the command that MySQL provides to back up the database mysqldump command must run in the Windows environment Source command in MySQL environment run MySQL recovery: source e:\ Mydb1.sql Carriage return Note: When recovering, [CREATE database first] and use the database, [then] through the source command, because the SQL file [only] table information, [None] Database information to create a users table, containing id/username/password/ Birthday/salarycreate table if not exists users (ID int (4), username varchar (TEN), Password varchar (6), Birthda                Y datetime, Salary float (6,2)), float (6,2) means: 2 indicates the maximum number of digits after the decimal point, more than 2 digits, rounding; 6 represents the maximum number of integers and decimals, and all the fields in the table with the most (6-2) digits of the entire component are allowed to be null, and the default NULL if the data is garbled, follow these steps: Insert a record into the users table, first in English (unable to insert Chinese) insert into users VALUES (1, ' Jack ', ' 123456 ', ' 2015-8-8 8:8:8 ', 9999.99); INSERT into users values (2, ' haha ', ' 123456 ', ' 2015-9-9 9:9:9 ', 9999.99); INSERT into users values (3, ' hehe ', ' 123456 ', ' 2015-7-7 7:7:7 ', 7777.77); Query the users table for the structure DESC users; Create a Employees table------------------------------use the Employees.sql table in the above employee table to basically add an image column ALTER TABLE Employeesadd IMAGe blob;alter Table Employees add image blob; in enterprise, it is not true that the photo itself is stored, but the photo path, that is, e:/zgl.jpg modify the Name column so that it is 60alter long table employeesmodify name varchar; ALTER TABLE employeesmodify name varchar (60); Delete image column ALTER TABLE Employeesdrop column Image;alter table Employeesdrop column image; If there is content in the table, delete or add a column that does not affect the information table name of the other column employees instead staffrename table employees to Staff;rename table employees to staff; Modify the table's character set to Gbkalter table Staffcharacter set utf8/gbk/gb2312; column name modified to Usernamealter Table Staffchange Column name username varchar ($), ALTER TABLE STAFFCHANGE column name username varchar (60), inserting data into the staff table Nsert into the staff values (3, ' Zhao June ', ' male ', ' 2005-1-1 ', 3333.33, ' 2005-7-1 ', ' This is a memo message '); show insert Nullinsert into employees values (1, ' Haha ', ' male ', ' 2015-1-1 ', 1111.11, ' 2015-5-5 ', ' This is the memo message ', NULL); implicitly inserting Nullinsert into employees (Id,name,sex,birthday,salary, Resume) VALUES (2, ' hehe ', ' male ', ' 2015-1-1 ', 2222.22, ' This is the note information '); drop table if exists staff;//table is not in drop tables if exists Staff;truncate table users;//tables are still there, but there is no content and then recreate the table create tables staff (ID int (5), name varchar (6), Sal Int (5)), insert into staff values (1, ' haha ', 7000); INSERT into staff values (2, ' hehe ', 8000); INSERT INTO Staff values (3, ' hehe ', 9000), insert into staff values (4, ' plainly ', 10000), insert into staff (id,name,sal) VALUES (4, ' stars ', 6000); INSERT into staff (name,id,sal) VALUES (' month ', 5,6000);-------------------------------------------------------------- --------------------------------------------------revise all employee salaries to $10000 update staff set Sal=10000;update sal= 10000, the above is the power of SQL statements, if the command is sent, the MySQL database server will resolve its own commands, and do the corresponding process processing, the process of processing for programmers, is closed, invisible. SQL full NAME "Structured query statement", fourth generation computer language first generation: machine language, that is, 01010010100100101 second generation: assembly language, that is, using a code name to represent 10101010 of these numbers third generation: high-level language, i.e. c/c++/vb/java/c#/ ... Fourth generation: Command language, SQL Fifth generation: Smart language, ... Change the salary of the employee whose name is ' haha ' to $11000 update staff set sal=11000 where name = ' haha '; update staffs set sal=11000 where name = ' haha '; place monthly salary in original Add $1000 Update staff Set sal = sal + + where name = ' Month month '; update staff set sal = sal+1000 where name = ' Month month '; Delete record 3rd in Table de Lete from the staff where id = 3;delete from the staff where id = 3; Delete all records in the table deleteFrom Staff;delete It is a row to delete, slow slower than the "slow" drop table staff; the entire table is deleted using truncate to delete records in the table truncate it is the whole table is deleted and then refactored, slower than "fast." But its table structure is still there, The contents of the knowledge sheet are gone---------------------------------------------------------------------------------------------------------------stu Dents table Use Mydb1;drop table if exists students;create table if not EXISTS students (ID int (5), name varchar (), Chinese int (5 ), 中文版 Int (5), Math Int (5)), insert into students (Id,name,chinese,english,math) VALUES (1, ' Zhang Xiaoming ', 89,78,90); insert into students (Id,name,chinese,english,math) VALUES (2, ' Li Jin ', 67,98,56); INSERT into students (Id,name,chinese,english, Math) VALUES (3, ' Harry ', 87,78,77), insert into students (Id,name,chinese,english,math) VALUES (4, ' Lee ', 88,98,90); insert into students (Id,name,chinese,english,math) VALUES (5, ' disparities Wealth ', 82,84,67); INSERT into students (Id,name,chinese,english, Math) VALUES (6, ' Zhang Jinbao ', 55,85,45), insert into students (Id,name,chinese,english,math) VALUES (7, ' Huang Rong ', 85,75,80); insert into students (Id,name,chinese,english,math) VALUES (8, ' Zhang Li ', 75,65,30); insert into students (Id,name,chinese,english,math) VALUES (9, ' Ho Li ', 75,65,30); INSERT into students (Id,name,chinese, English,math) VALUES (10, ' single ', 75,65,30), insert into students (Id,name,chinese,english,math) VALUES (11, ' Lee ', 75,65,null) INSERT into students (Id,name,chinese,english,math) VALUES ("Jack", 75,65,40), insert into students (Id,name,chinese , English,math) VALUES ("Marry", 75,65,60), querying all the students in the table for information, * representing all fields, The order is the same as the table structure------------------------------with Students.sql table select Id,name,chinese,math,english from Students;select name, Id,chinese,math,english from Students;select name,chinese,math,english,id to Students;select * from students;* Although it is easy to write, but full of uncertainties, be cautious to use the name of all students in the inquiry form and the corresponding English score select Name,english from Students;select name,english from students; Repeating language scores in the filter table distinct (area) in front of the column name, you can repeat your element to filter the name, just keep a select distinct Chinese from students;select distinct Chinese from students; Add 10 points to all student scores select name,chinese+10,math+10,english+10 from Students;select name,chinese+10,math+10 , english+10 from students; Null with any numberValues are calculated as NULL statistics for each student's total score select Name,chinese+math+english from Students;select name,chinese+math+english from students; * * Use (aliases) to indicate student scores, note using aliases with double quotes select name "Name", Chinese+math+english "Total score" from Students;select name "name", chinese+math+ English "Total score" from students; query students whose name is ' Zhang Xiaoming ' note string in single quote ' condition with whereselect id,name,chinese,math,english from students where Name = ' Zhang Xiaoming '; select * from students where name = ' Zhang Xiaoming '; query English score more than 90 points of the students select Id,name,chinese,math,english from students whe Re 中文版 > 90;select * from students where english>90; queries for all students with a total score greater than 200 select Id,name,chinese,math,english from Stu Dents where chinese+math+english > 200; query math score is 89 or 90 or 91 students way one: Select Name,mathfrom studentswhere (math=89) or (math =91) or (math=90); mode two: (recommended) Select Name,mathfrom studentswhere math in (91,89,90,100,10000);//Even with some non-existent values query English scores in Schoolmate between 80-90, contains 80 and 90 ways one: Select Name,englishfrom studentswhere (english>=80) and (english<=90); mode two: select Name, Englishfrom studentswhere 中文版 between and 90;select Name,english from students where 中文版 between and 90; for all surname ' Li ' grades,% indicates 0 or more characters (fuzzy query) Select Name,english,math, Englishfrom studentswhere name like ' li% '; select * from students where name like ' li% '; = for accurate comparison of fuzzy comparisons, like keyword query for the famous ' Li ' student scores Sele CT name,english,math,englishfrom studentswhere name like '% Li '; query all names containing ' Li ' student scores select Name,english,math,englishfrom Studentswhere name like '% Li '; the following three SQL represents the [all] record in the query table select Name,english,math,englishfrom studentswhere name like '% '; Select Name,english,math,englishfrom studentswhere name like '% '; Select Name,english,math,englishfrom studentswhere Name like '%%% '; query all surname ' Li ' student scores, but the name must be three characters, _ represents 1 characters Select *from studentswhere name like ' li __ '; select * from students where NA I like ' li __ '; query math >80 and Language >80 students select *from students where 1=1 and (math>80) and (chinese>80); select * from S Tudents where 1=1 and (math>80) and (chinese>80);---------------------------------------------sort Math scores (descending) Post-Output Select Id,name,mathfrom studentsorder by Math Desc;selectId,name,math from students order by math Desc;desc for descending, sort fields with numeric select Id,name,mathfrom studentsorder by Math asc;asc for Ascending, Do not write ASC and desc default is ascending * * to the total score sort (descending) After output select name "Name", Math+chinese+english "Total Score" from Studentsorder by (Math+chinese+english) Desc;select name "name", Math+chinese+english "total score" from Students Order by (MATH+CHINESE+ENGLISH) desc; extension: usually <order by> The following can be followed: 1) the field order by math desc2) expression order by (Math+chinese+english) desc3) alias (this alias can be unquoted) select name "Name", Math+ch Inese+english "Total Score" from the students order by total desc;4) column number, which is where the column appears in select, sorted from 1 to select name "Name", Math+chinese+english "Total score" from students order by 2 desc; ranking of students with surname ' Li ' (descending) output select name "Name", (chinese+math+english) "Total score" from Studentswhere NA Me like ' Li% ' order by 2 desc;--select name "name", (chinese+math+english) "Total score" from students where name like "Li%" order by 2 D The student select Name,mathfrom studentswhere math is null;select name,math from students where math is null;select Name,mathfrom Studentswhere Mathis not NULL, and not in,not between And,is Not-------------------------------------------------------------------------------------------------------statistic function: The statistic function will null It's worth excluding statistics. The number of students in a class select count (*) "total number" from Students;select count (*) "total number" from students; do not advocate the use of the * number, but the non-null unique column, That is, the ID (master) SELECT COUNT (ID) from Students;select count (id) from Students;select count (math) from students; We recommend that you do not count column values that contain nulls statistics for students with a math score greater than 80 how many select count (id) from Studentswhere math>80;select count (ID) from students where math>80; Statistics total score greater than 250 people how many select count (id) from Studentswhere (math+chinese+english) >250;-----Statistics A class math overall select SUM (math) "Math Total" from Students;select sum (math) "Math Total" from students; statistics of a class Chinese, English, mathematics each section of the total select sum (math) "Math Total", sum (中文版) "Total English", Sum (Chinese) "Language score" from students; statistics of a class of Chinese, English, mathematics, the sum of select sum (chinese+math+english) "Overall" from students;-- ----statistics A class of Chinese scores average select AVG (Chinese) "language average" from Students;select avg (Chinese) "language average" from students; ask for a Class mathematics average division Select AVG (Math) "mathematical average" from students; to find the average score of a class total SeleCT avg (math+chinese+english) "Class total score average" from students; Find class highest score and minimum math score select max (math) "Math Highest Score", Min (math) "Math lowest score" from Students; review: COUNT () Total statistics sum () sum avg () Average max () min ()---------------------------------------------------------- ---------------------------------------------Orders table: Drop table if exists orders;create table if not EXISTS orders (o_id int,o_product varchar (o_price int), insert into orders values (1, ' TV ', "100"), insert into orders values (2, ' washing machine '); INSERT into orders values (3, ' laundry detergent ', "n"), insert into orders values (4, ' oranges ', '), insert into orders values (5, ' detergent ', 80); After classifying the items in the order form, show the total price of each category of goods------------------------------use orders.sql table Select O_product "goods", SUM (o_price) "Total Price" from orders Group by O_product; inquire about the purchase of several kinds of goods, and each kind of total price greater than 100, that is, after classification, also filter select O_product "goods", SUM (o_price) "Total Price" from the orders group by O_ Producthaving sum (o_price) > 100; Summary: Where: Row filters for the original record, that is, records that do not have a group can not appear usually appear in the where after the first execution of wherehaving: Group Filter for the records after grouping can not appear by the after the group by the execution having a query to buy a few kinds of goods, And each category of goods greater than 100, and in descending order of the total price of the Select O_prodUCT "goods", SUM (o_price) "Total price" from orders Group by o_producthaving sum (o_price) > 100order by 2 desc; Summary: SELECT clause FROM clause wher e clause GROUP BY clause HAVING clause ORDER BY clause in MySQL database server, above all clauses, which one must write? A: Select

MySQL's most complete and must-have SQL statement