Common SQL statements

11:33 2013/6/26 SQL statement: DML (data manipulation Language) statement: Insert Update Delete Select

DDL (data difinition Language-definition Language) statement: Create table/create database/drop database/alter table DCL (the data Control Lan guage Data Control statement) statement:

The primary key can be defined as autogrow, note that the primary key type must be int CREATE TABLE d (ID int primary key auto_increment, name varchar (20));

Insert into D (name) VALUES (' CCC ');

Insert into D (name) VALUES (' aaaa ');

Delete from d where id=4;

CREATE table e (id int, name varchar (20)); Add PRIMARY KEY constraint ALTER TABLE e modify ID int primary key auto_increment;

Creating a federated primary key create TABLE F (FirstName varchar, LastName varchar), primary KEY (FirstName, LastName));

INSERT into F values (' Zhao ', ' nan '); INSERT into F values (' Li ', ' nan ');

The most important constraint foreign KEY constraint is CREATE TABLE husband (ID int primary key, name varchar (20));

CREATE TABLE wife (id int primary key auto_increment, name varchar), Husbandid int, constraint HUSBANDID_FK Foreig N Key (Husbandid) references husband (id));

Insert into husband (Id,name) VALUES (3, ' Liuxiaoshuai ');

Insert into wife (name, Husbandid) VALUES (' Fengjie ', 3);

Delete from husband where id=3;

CREATE TABLE AAA (ID int

);

For AAA plus FOREIGN KEY constraint ALTER TABLE AAA ADD constraint HUSID_FK foreign key (HUSID) references husband (ID);

Delete FOREIGN KEY constraint ALTER TABLE AAA drop FOREIGN key HUSID_FK;

2. Modify the table on the basis of the employee table above add an image column ALTER TABLE employee ADD image varchar (30);

Modify the job column to the length of the ALTER TABLE employee modify job varchar (60);

Delete the sex column. ALTER TABLE employee drop sex;

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

Modify the table's character set to utf-8 ALTER TABLE user character set UTF8;

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

View all tables show tables;

View creation statement for table show create table user;

View table structure desc user;

Delete User table drop table user;

Third, the data deletion and modification 1.  Insert statement Employee.sql CREATE TABLE employee (ID int, name varchar), sex varchar (ten), birthday date, salary float, Entry_date date, resume text); CREATE TABLE employee (ID int, name varchar), sex varchar (ten), birthday date, salary float, entry_date date, re Sume text); Insert three records into employee table insert into employee (Id,name,sex,birthday,salary,entry_date,resume) VALUES (1, ' zjx ', ' Male ', ' 1991-10-08 ', 9500, ' 2010-2-18 ', ' Good Boy '); INSERT into employee (Id,name,sex,birthday,salary,entry_date,resume) VALUES (4, ' Zhaonan ', ' Male ', ' 1986-11-23 ', 3800, ' 2011-2-18 ', ' extinct Good Men ');

View all records for a table select * from employee;

View data using all the codes show variables like ' character% ';

Modify the client's encoding to GBK set CHARACTER_SET_CLIENT=GBK;

The encoding of the modified result set is GBK set CHARACTER_SET_RESULTS=GBK;

Additional wording of the INSERT statement CREATE TABLE A (username varchar, password varchar (20));

Insert into a values (' Zs ', ' 111 ');

Insert into a values (' Wangwu ', ' 2222 '), (' Lisi ', ' 3333 ');

Insert a VALUES (' AAA ', ' BBB ');

Insert a (username) values (' AAA ');

2. UPDATE statement

Revise all employee salaries to $5000 update employee set salary=5000;

Change the salary of the employee named ' Zhangsan ' to 3000 yuan. Updata Employee set salary=2000 where name= ' Zhangsan ';

The employee's salary, named ' Lisi ', was revised to $4000 and sex changed to female. Update employee set salary=4000,sex= ' female ' where name= ' Wangwu ';

Add Xiaohong salary to the original base of 1000 yuan update employee set salary=salary+1000 where name= ' Xiaohong ';

3. Delete statement

Delete the record for name ' Zhaonan ' in the table. Delete from employee where name= ' Zhaonan ';

Deletes all records in the table. Delete from employee;

Use truncate to delete records in a table. Destroy table and CREATE table truncate employee;

4. SELECT statement Student.sql CREATE TABLE student (ID int, name varchar (), Chinese float, English float, math float);

INSERT into student (Id,name,chinese,english,math) VALUES (1, ' a ', 89,78,90); INSERT into student (Id,name,chinese,english,math) VALUES (2, ' B ', 67,53,95); INSERT into student (Id,name,chinese,english,math) VALUES (2, ' C ', 67,53,95); INSERT into student (Id,name,chinese,english,math) VALUES (3, ' e ', 87,78,77); INSERT into student (Id,name,chinese,english,math) VALUES (4, ' F ', 88,98,92); INSERT into student (Id,name,chinese,english,math) VALUES (5, ' d ', 82,84,67); INSERT into student (Id,name,chinese,english,math) VALUES (6, ' G ', 55,85,45); INSERT into student (Id,name,chinese,english,math) VALUES (7, ' H ', 75,65,30);

Query the information for all students in the table. 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 extra-long points to all student scores. Select name,english+10,chinese+10,math+10 from student;

Count each student's total score. Select Name,english+chinese+math as sum from student;

Use aliases to represent student scores.

The WHERE clause queries the student whose name is Lee select * from student where name= ' Lee ';

Search for students with a score greater than 90 in English select * from student where english>90;

Query all students with a total score greater than 200 select Name,english+chinese+math from student where english+chinese+math>200;

The operator queries English scores between 80-90 students. SELECT * FROM student where 中文版 between and 85; Query math scores for 89,90,91 's classmates. Select Name,math from student where math in (89,90,91); All students surnamed Li are queried for their grades. SELECT * FROM student where name like ' Li% '; Query the student surnamed Li's two-character select * from student where name like ' Li _ '; Query Math >80, the language of >80 students. SELECT * FROM student where math>80 and chinese>80; Query English >80 or total >200 of students select *,chinese+math+english from student where english>80 or chinese+english+math>200 ;

The ORDER BY clause sorts the math scores after the output. SELECT * FROM student order by math;

After sorting out the total score, and then outputting the select *,chinese+math+english from student ORDER by chinese+math+english DESC in sequence from high to low; Sort out the student scores for surname Li output select * from student where name like ' Li% ' order by Chinese+math+english;

Aggregate function count Statistics How many students are there in a class? Select COUNT (*) 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 230? Select COUNT (*) from student where chinese+math+english>230;

Sum statistics A class math total? Select sum (math) from student; Select SUM (math,chinese,english) from student; Select sum (math) from student; Statistics a class of Chinese, English, mathematics each section of the total of select sum (Math), SUM (Chinese), sum (中文版) from student; Statistics a class of Chinese, English, mathematics, the sum of the results of select SUM (math+chinese+english) from student; Statistics of a class language scores average select sum (Chinese)/count (*) from student; The missing test does not participate in the calculation of the select SUM (Chinese)/count (*) from student; Select SUM (Chinese)/count (Chinese) from student;

Avg language divided by select AVG (Chinese) from student; Select AVG (Chinese) from student; Max/min language Highest score Select MAX (Chinese) from student; Select Max (Chinese) from student; Group BY CREATE TABLE orders (ID int, product varchar (a), price float);

INSERT into orders (Id,product,price) VALUES (1, ' TV ', 900); INSERT into orders (Id,product,price) VALUES (2, ' washing machine ', 100); INSERT into orders (Id,product,price) VALUES (3, ' detergent ', 90); INSERT into orders (Id,product,price) VALUES (4, ' oranges ', 9); INSERT into orders (Id,product,price) VALUES (5, ' detergent ', 90);

Classify a product as a select * from Orders group by product; SELECT * FROM orders group by product; SELECT * FROM orders group by product; SELECT * from the orders group by OId; Display of a single-class commodity summary Select *,sum (price) from the orders group by product; I commodity category shows the total price of a single class of goods greater than 100 of the Select *,sum (prices) from the orders group by product have sum (value) >100; SELECT *,//items with a unit price greater than 20 are categorized to show the price sort select * from the orders where PRICE>20 group by product order by value;

Iv. constraints of the table we can add constraints to the field while creating the table, and limit the data that will be inserted in the future

Unique constraint uniquely CREATE TABLE A (name varchar () unique);

Insert into a values (' AAA '); Insert into a values (' AAA '); Wrong name has a unique constraint

A non-null constraint NOT NULL CREATE TABLE B (id int, name varchar (a) not null);

Insert into B values (1, ' AAA ');

INSERT into B (ID) values (2); Wrong, name has a non-null constraint

Primary key Each table should have a primary key so we can find the record. The primary key must have a unique constraint, a non-null constraint

PRIMARY KEY constraint primary key CREATE TABLE C (ID int primary key, name varchar () not NULL);

Insert into C (id,name) VALUES (1, ' aaaa ');

Insert into C (id,name) VALUES (1, ' bbbb '); Error, primary key repeat INSERT into C (name) VALUES (' CCC '); Wrong, primary key cannot be null

The primary key can be defined as autogrow, note that the primary key type must be int CREATE TABLE d (ID int primary key auto_increment, name varchar (20));

Insert into D (name) VALUES (' CCC ');

Insert into D (name) VALUES (' aaaa ');

Delete from d where id=4;

CREATE table e (id int, name varchar (20));