mysql-additions and deletions to check operation

--Query statements

SELECT * from student; -Low efficiency, generally not recommended

SELECT tid,tname from student;

SELECT Tname as name from student;

SELECT * from student WHERE 1=1;

SELECT tname from student; --Specify the query for individual columns

SELECT user,password,host from Mysql.user; --cross-Library query user name, password, host in MySQL database

--Create Student information table

CREATE TABLE Stu (

Sno INT UNSIGNED not NULL PRIMARY KEY auto_increment,

Sname VARCHAR (Ten) is not NULL,

Sgender ENUM (' Male ', ' female ') DEFAULT ' male ',

Saddress VARCHAR (50),

Sscore TINYINT UNSIGNED,

Sdept VARCHAR () not NULL

) Engine=myisam auto_increment=2014270001 DEFAULT Charset=utf8;

INSERT into Stu VALUES (NULL, ' Donghua ', ' Male ', ' Henan province Zhengzhou ', 80, ' computer network ');

INSERT into Stu VALUES (NULL, ' yboard ', ' Male ', ' Henan Pingdingshan ', 85, ' computer security ');

INSERT into Stu VALUES (NULL, ' Xiaojiang ', ' Male ', ' Shangqiu, Henan ', 70, ' computer network ');

INSERT into Stu VALUES (NULL, ' Xiao Bing ', ' Male ', ' Henan province Shangqiu ', 80, ' computer security ');

INSERT into Stu VALUES (NULL, ' Xiao Li ', ' female ', ' Henan province Anyang ', 90, ' chemical food ');

INSERT into Stu VALUES (NULL, ' Xiao Hua ', ' Male ', ' Beijing ', 48, ' chemical food ');

INSERT into Stu VALUES (NULL, ' Kelly ', ' Female ', ' Shanghai ', 74, ' bioengineering ');

INSERT into Stu VALUES (NULL, ' Faye ', ' female ', ' Shanghai ', 81, ' bioengineering ');

INSERT into Stu VALUES (NULL, ' Casa ', ' female ', ' Tianjin, Hebei ', 59, ' bioengineering ');

INSERT into Stu VALUES (NULL, ' Lily ', ' female ', ' Tianjin, Hebei province ', 0, ' chemical food ');

INSERT into Stu VALUES (null, ' Zhang Chuang ', ' Male ', ' Tianjin, Hebei province ', NULL, ' computer network ');

INSERT into Stu VALUES (null, ' Swing ', ' male ', ' Anhui province Hefei ', NULL, ' computer security ');

INSERT into Stu VALUES (NULL, ' Zhang Liqiu ', ' Male ', ' Anhui ', 67, ' building works ');

INSERT into Stu VALUES (null, ' Chou Heung ', null, ' Anhui Province ', 95, ' building works ');

--Conditional query

--NULL condition to determine is null or is not NULL

SELECT * from Stu WHERE Sscore is NULL;

SELECT Sno,sname,sscore from Stu WHERE Sscore are not NULL;

-In () ... Within range or not in () No ... Within range

SELECT * from Stu WHERE sdept in (' Computer network ', ' PC security ');

SELECT * from Stu WHERE sdept (' Computer network ', ' PC security ');

--Between and in ... Between or not between and is not in ... Between

SELECT * from Stu WHERE Sscore not between and 90;

SELECT * from Stu WHERE sscore between and 90;

--not take inverse operator ADN with operator or OR operator

SELECT * from stu WHERE sdept = ' Building Engineering ' OR sdept = ' bioengineering ';

SELECT * from stu WHERE sgender = ' Male ' and sdept = ' computer network ';

--like ' Zhang% ' Fuzzy Lookup operator (%: 0-n multiple characters, _ represents one character)

SELECT * from Stu WHERE sname like ' li% ';

SELECT * from Stu WHERE sname like ' li _ ';

SELECT * from Stu WHERE sname like '% li% ';

SELECT * from Stu WHERE sname '% li% ';

--Sort Operations desc represents descending sort, ASC represents ascending sort, default ascending sort

SELECT * from Stu ORDER by Sscore DESC;

SELECT * from Stu ORDER by Sscore ASC;

--Limit limits results display rows (usage: Limit 5 shows 5 rows or limit 0,5 from 0 positions to display 5 rows, 0 for the number of start rows in the database), limit at the end of the SQL statement

SELECT * from Stu LIMIT 5;

SELECT * from Stu LIMIT 0, 5;

--ifnull () and if () two functions using and difference: ifnull (EXP1,EXP2) = if (Exp1 is NULL,EXP2,EXP3)

SELECT Sno As study number, sname as name, Sgender as Gender, ifnull (Sscore, ' Kuang Kao ') as score from Stu;

SELECT Sno as school number, sname as name, Sgender as Gender, IF (Sscore is NULL, ' Kuang Kao ', Sscore) as score from Stu;

--Aggregate function (aggregate function)

AVG ()--averages (calculates average, does not contain null values)

SUM ()--sum

Count ()--Total number of records

Max ()--max value

Min ()--Minimum value

SELECT avg (sscore) average, SUM (Sscore) score, Max (Sscore), min (sscore) Minimum, COUNT (*) Total, COUNT (sscore) number of exams from Stu;

SELECT COUNT (DISTINCT sdept) professional from Stu; --DISTINCT Remove Duplicates

SELECT Sdept,avg (Sscore) from Stu GROUP by Sdept; ---group query, usually preceded by a group name, followed by the aggregate function (aggregation function)

--the query in the condition is a subquery that shows the highest score of student information

SELECT * from stu WHERE Sscore = (

SELECT MAX (Sscore) from Stu-sub-query, when the subquery found only one of the data, you can use ' = ', more than a bar can only use ' in '

);

SELECT * from Stu WHERE Sscore in (

SELECT MAX (Sscore) from Stu--subquery

);

--statistics of male and female students are several people

SELECT ifnull (Sgender, ' confidential '), COUNT (*) from Stu GROUP by Sgender;

--statistics on the average performance of each major, showing the top two data of the professional with the average score of not less than 60 points

SELECT Sdept,avg (Sscore) from Stu WHERE 1=1 GROUP by sdept have avg (sscore) >=60 ORDER by AVG (sscore) DESC LIMIT 2;

WHERE Query criteria

GROUP BY group

Having is a grouping condition (using a group if used)

Order by sort

LIMIT takes several records

--Multi-table Join query Query results are information in multiple tables

CREATE TABLE Student (

Sid INT UNSIGNED not NULL PRIMARY KEY auto_increment,

Sname VARCHAR (Ten) is not NULL,

Sage TINYINT UNSIGNED,

Stid SMALLINT UNSIGNED

) Engine=myisam DEFAULT Charset=utf8;

CREATE TABLE Teacher (

Tid SMALLINT UNSIGNED not NULL PRIMARY KEY auto_increment,

Tname VARCHAR (Ten) not NULL

) Engine=myisam DEFAULT Charset=utf8;

INSERT into teacher VALUES (NULL, ' teacher Zhang ');

INSERT into teacher VALUES (NULL, ' Miss Wang ');

INSERT into teacher VALUES (NULL, ' Miss Zhao ');

INSERT into teacher VALUES (NULL, ' Miss Li ');

SELECT * from teacher;

INSERT into student VALUES (NULL, ' Caocao ', 20, 1);

INSERT into student VALUES (NULL, ' Zhang Fei ', 27,4);

INSERT into student VALUES (NULL, ' Guan Yu ', 29,2);

INSERT into student VALUES (NULL, ' Liu Bei ', 50,2);

INSERT into student VALUES (NULL, ' Sun Quan ', 40,4);

INSERT into student VALUES (NULL, ' Zhuge Liang ', 24, 3);

INSERT into student VALUES (NULL, ' Liao Gai ', 25, 1);

INSERT into student VALUES (NULL, ' Big Joe ', 25,5);

SELECT * from student;

TRUNCATE student;

--Check the student number, name, age and the name of the substitute teacher:

--Equivalent link query

SELECT sid,sname,sage,tname from Student,teacher WHERE Student.stid=teacher.tid;

--Left link query

SELECT sid,sname,sage,ifnull (Tname, ' none ') from student s left JOIN teacher T on S.stid=t.tid;

--Right link query

SELECT sid,sname,sage,ifnull (Tname, ' none ') from student s right joins teacher T on S.stid=t.tid;

--The table's own link query

SELECT A.sid,a.sname,b.sname from student a,student b WHERE a.sid<b.sid;

--Insert INSERT statement

CREATE TABLE T1 (

Tid INT UNSIGNED not NULL PRIMARY KEY auto_increment,

Tname VARCHAR (Ten) is not NULL,

Tage TINYINT UNSIGNED

) Engine=myisam DEFAULT Charset=utf8;

INSERT into T1 VALUES (NULL, ' Jack ', 18); --Inserting a piece of data

INSERT into T1 VALUES (null, ' Jane ', '), (null, ' Lili ', 10); --insert more than one data

INSERT into T1 (tname,tage) VALUES (' Zhang San ', 20); --Inserting a piece of data, corresponding to the field

INSERT into T1 SELECT * from T1; --inserting the queried data into the database (provided there is no primary KEY constraint)

REPLACE into T1 VALUES (1, ' Cook ', 35); --If the primary key is the same, replace the INSERT, and different insert

– Delete DELETE statement

DELETE from T1 WHERE tid=1; --delete data for Tid 1 in the T1 table

DELETE from T1; --Delete all data in the T1 table (insert data again, the primary key is incremented before)

TRUNCATE T1; --Truncate all data in the table, empty the data, retain the structure (again inserting the data, the primary key restarts), do not trigger the trigger

--Drop DELETE statement

Drop Database db;--Delete databases

drop table t1;--Delete tables

--Update Modification statement

UPDATE T1 SET tname = ' few ', tage=18 where tid = 2;

This article is from the "Night Wind" blog, please be sure to keep this source http://jiangkun08.blog.51cto.com/6266992/1548533

mysql-additions and deletions to check operation