The code change format: CHARSET=utf8;
Create a database: create database database name
Check the database: show the databases;
Create a table: create table name (id ind (50), the name varchar (50), sex char (1), birthay datetime);
The selected database command: use
Int - said plastic char - varchar said characters - said the date string - said date (date)
The date, time (minutes) when (date) (month) (year)
Delete - drop table
Modify the name of the table, alter table (1) rename (2);
Increase table fields -- the alter table (table name) add (table fields) int (50);
Delete table fields - alter table (table name) to drop (table field);
Modify the table fields - alter by Tate (table name) change the name (modified) varcahr (50);
Insert the data (input data) insert into (table name) (data values) value (data);
Table constraints:
Primary key (primary key) : the order of the physical store
Is not empty (not null) : this field is not allowed to fill in the empty value
The only (unique) : this field are not allowed to repeat the word
Default value (the default) : when not completing this value using the default values, if fill, fill in the specified
A foreign key (foreign key) : to maintain the relationship between two tables
Table creation with constraints and annotation: create table (table name) (id int (50), not null primary key comment sequence {} "annotation";
Query -
Select id, name, from student; - a full table query;
With a * is a full table query (low execution efficiency), single field query (high execution efficiency)
Query conditions:
- a demand: query student id information for 111 students
Select * from (table name) where (student id)='111'
- demand 2: query a classmate called Lao wang
Select * from (table name) where (name)='Lao wang'
- demand three: query the name is wang classmate (fuzzy query)
Select * from (table name) where (name) like "% king";
- delete table data
The delete from (table name) where id=(delete);
- to modify data table
Update (table name) set name='modified data where id=777;
Select id AS student number, the name AS "name", birthday AS 'birth date from (table name) AS s;
The default value is
(table name) (field) default ()
Query conditions:
Comparison operators:
Is equal to:=
More than: & gt;
Greater than or equal to: & gt;=
Less than: & lt;
Less than or equal to: & lt;=
Is not equal to that!=or & lt;>
Logical operators:
And - and
The or - or
The not - not
Fuzzy query:
Like
% said any number of characters_ is an arbitrary character
Query range:
In said in a discontinuous range
between... and... Said within a continuous
Empty:
Note: the null and "is different
Sentenced to empty: is null
Not found empty: is not null
- demand the students list the student achievement positive sequence alignment
- order by said sorting, default is positive sequence row, column names followed by desc is arranged in reverse chronological order after
Select * from student order by sccore;
- demand will students table surface snow arranged in reverse chronological order
Select * from student order by sccore desc;
- aggregation function
1, statistics - count
2, the average - avg
3, the maximum Max
4, the minimum min
5, sum, sum
Students on the surface of the table -
Select count (id) from student;
- the number of students in statistics to pass the
Select count (id) from student WHERE score>=60
- calculate the students watch students scored an average of
Select avg (socore) from student;
- aggregation function is not calculating null values
- to find the oldest student
Select id, name, {min year} {} Max Numbers (birthday) from student;
- calculating table all the students' score
Select sum (score) from student;
- find out is greater than the average student
[select * from student where score> avg (score) - write wrong]
- principle: the where condition is unable to write inside the aggregation function
- the subquery
Select * from student where score> (selest avg (score) from student);
A movement - the subquery
[select min (score) from student; -- write wrong]
- find out result of the worst student
Select * from student where score=(select min (score) from student);
- ZhaXunChu check result is lower than li si's student name, student achievement, student age
The alter table student add sage int (50);
Select the name 'student's name, score student achievement, sage' students' ages from student where score<(select score from student where name='bill')
Table to increase data:
The update table set the field name=field name +,,,
Paging query:
Formula (n - 1) * m, m
Select * from stydent limit (value, value);
- round (accurate to one decimal places)
Select id, name, round, (height, 0) from (table name) where id=(location) t
- the worst of all the boys in the query student information (note here where sex='male' to write in the subquery)
Select * from student where score=(select min (score) from student where sex="male");
- grouping query
- how many people were boys, how many girls?
Select the sex, the count (*) from student group by sex.
- the statistics the number of each class
Select the class, the count (sid) from group by class.
- a statistical groups scored an average of
Select the class, avg (id) from student group by calss.
- find out the average more than 70 points of class
Select the class, avg (scrore) from student group by class having avg (scrore) & gt; 70;
The where and having the difference between a, after having used only in the group by the results of group after screening (i.e., the use of having only if the group),
Second, must be in the group by the where, before the
3, where are not allowed to use in the conditional expression after aggregation function, and having can
Four, when a query statement appeared the where, at the same time, having, order by, group by executive order and write sequence is:
1. Perform the where xx to do a full table data screening, return a result set, 1
2. According to the first result set using group by group, to return a result set, 2
3. On the second result set per 1 set of data to execute the select xx, several groups is executed several times, return a result set, 3
4. The third executive having collected xx for screening, return a result set, 4
5. According to the fourth sort the result set,
- create a commodity classification
The CREATE TABLE category (
Cid INT PRIMARY KEY (20) [KEY] the COMMENT 'category id',
- category id
Cname VARCHAR (20) the COMMENT 'classification name' name, classification,) the COMMENT='classification;
- create goods table
The CREATE TABLE product (
Pid INT PRIMARY KEY (20) the COMMENT 'commodity id,
- the goods id
Pname VARCHAR (20) the COMMENT 'name of commodity,
- the commodity name
Price INT the COMMENT 'commodity prices,
- commodity prices
Pdesc VARCHAR (40) the COMMENT 'commodity description,
- description
Cno INT the COMMENT 'commodity categories - product category
);
- give goods list (table) to add a foreign key
The ALTER TABLE product ADD CONSTRAINT FK_cno FOREIGN KEY (cno) REFERENCES category (cid);
Delete table is to have cultured, can't delete, can't delete the main table first
Only first delete from the table, then delete the main table, or two tables together delete (delete statements from table before, and the main table delete statements behind)
DROP TABLE product;
DROP TABLE category;
- 1, query the commodity name and name of commodity classification data
- problem solving ideas, two tables together, forming a large list
SELECT * FROM the category, the product WHERE the category. ` cid ` (field)=product. ` cno ` (field);
SELECT a category. ` cname ` category name, product. ` pname ` commodity name FROM the category,
The product WHERE the category. ` cid `=product. ` cno `;
- join queries within
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull
