Database MySQL Statement

Basic operations of the database:
--Increase, delete, change, check

--How the database is stored: table

--The basic operation statement of the database:
--Start the database service
net start MySQL
--Shut down the database service
net stop MySQL
--Connect to the database via Windows command Window
mysql-uroot-p123456
--View Database
Show databases
--Create a database
Create DATABASE value
--Delete Database
Drop Database Value
--View CREATE Database
Show CREATE DATABASE value
--Enter the database
Use value
--Using resource files
SOURCE C:/mysql.sql
--View a table already in the database
Show tables
--Specify the Database view table
Show tables from Databasevaule
--View table structure
Desc Tablesvalue
--View the Build table statement
Show CREATE TABLE Tablesvalue
--Create a table
CREATE TABLE Tablesvalue (
value int (numb) primary key auto_increment,
Value double NOT NULL,
Value varchar (numb),
Value Date,
Value Char Unique
)
--Add data
Insert into Tablesvalue (Value,value,value) values (XXX,XXX,XXX);
INSERT into Tablesvalue values (XXX,XXX,XXX,XXX,XXX);

/* INSERT into students (Id,name,sno,birthday) VALUES (1, "HNN", "144215", 0820)
INSERT into students values (2, "Zhangsan", 144216,0624); */
--View table information
SELECT * FROM Tablesvalue
Select Value,value from Tablesvalue
--Conditional query
SELECT * FROM Tablesvalue where value=values
/*
Select Id,name,sno from students where Sno = 144215
*/
--Restricted query
Select Value,value from Tablesvalue
--Limited conditional query
Select Value,value from Tablesvalue where value=values
--Multi-conditional use of characters
And OR XOR not

/* Select Id,name,sno from students where Sno = 144215
Select Id,name,sno from students where Sno = 144215 and id = 1
Select Id,name,sno from students where Sno = 144215 XOR id = 1
Select Id,name,sno from students where Sno = 144215 or sno = 144216
Select Id,name,sno from students where isn't sno = 144215 and not id = 2
*/
--Clear Duplicate records
Select distinct Value,value from Tablesvalue

/* SELECT DISTINCT sno from students
Select Sno from Students */
--Sort by regulation
SELECT * from Tablesvalue ORDER by value Asc/desc

/* SELECT * from students ORDER BY id DESC
*/
--Query by specified value
SELECT * from Tablesvalue where value in (XXXX,XXXX,XXXX)

/* SELECT * from students where ID in
*/
--Specify how many pieces of information to start the query with
SELECT * from Tablesvalue limit x, y

/* SELECT * from students limit 0,3
*/
--Aggregate function
Min () max () sum () Avg () count ()
The maximum value for a field in a numeric type in a database can be used directly:
SELECT MAX (field-name) from table-name WHERE conditions
For other types of fields, use the following statement:
SELECT MAX (CAST (field-name as UNSIGNED)) from Table-name WHERE conditions

/* Select COUNT (*) from students
Select COUNT (ID) from students
Select Max (SNO) from students
Select SUM (ID) from students
Select AVG (SNO) from Students */

--Filter the record rows for aggregated values
* SELECT * from Tablesvalue ORDER by value Asc/desc have sum (value) >100

/* SELECT * from students the group by ID has a sum (ID) >1
Select *,avg (SNO), AVG (ID) from students GROUP by SNO have avg (SNO) >2
http://www.cnblogs.com/yank/p/3672478.html * *

--Connect multiple query results
Select .... Union SELECT ...
/* Select SUM (ID) as Sumset from Students Union select AVG (SNO) from students
*/
--Add a column to the current table
ALTER TABLE tablesvalue Add column value varchar (+) NOT NULL
/* ALTER TABLE Students add Column sname varchar (+) NOT NULL
*/
--Delete a column from the current table
ALTER TABLE tablesvalue Drop column value
/* ALTER TABLE Students drop column sname */

--revision of the definition of the forefront
ALTER TABLE Tablesvalue Modify value varchar (one-by-one) not NULL
/* ALTER TABLE students modify sname int (one) NOT NULL
List cannot be empty */
--Modify the definition and name of the column
ALTER TABLE Tablesvalue Change oldvalue newvalue char (TEN) NOT NULL

/* ALTER TABLE students change ID IDD char (TEN) NOT NULL
*/
--Add primary key
ALTER TABLE Tablesvalue add primary key (ID)
--Delete primary key
ALTER TABLE Tablesvalue drop PRIMARY key
--Change the table name
ALTER TABLE Tablesvalue Rename Tablesvalue
Rename Table Tablesvalue to Tablesvalue
/* ALTER TABLE students rename Studenta
Rename table Studenta to students */
--Delete Table
drop table Tablesvalue

--Update table data
Update Tablesvalue Set Value=xxxx
Update Tablesvalue set value=xxxx,value=xxxx where value=xxxx
--Delete table data
Delete from Tablesvalue
Delete from Tablesvalue where value=xxxx

--association between two tables
SELECT * FROM Tablesvalue,tablesvalue
SELECT * from Tablesvalue t1,tablesvalue T2
Select T1.value,t2.value from Tablesvalue t1,tablesvalue T2
Select T1.value,t2.value from Tablesvalue as t1,tablesvalue as T2

--Inner connection
SELECT * from Tablesvalue T1 join Tablesvalue t2 on t2.value = t1.value where T1.value = 10000
SELECT * from Tablesvalue t1 inner joins tablesvalue t2 on t2.value = t1.value where T1.value = 10000

--no difference between outside connections
SELECT * FROM Tablesvalue T1 left outer joins Tablesvalue t2 on t2.value = t1.value where T1.value = 10000
SELECT * FROM Tablesvalue T1 right outer joins Tablesvalue t2 on t2.value = t1.value where T1.value = 10000

--Create a table foreign key
CREATE TABLE Tablesvalue (
value int (numb) primary key auto_increment,
Value double NOT NULL,
Value varchar (numb),
Value Date,
Value Char Unique,
Foreign key (value) references Tablesvalue (value)
)

ALTER TABLE Tablesvalue add foreign key (value) references Tablesvalue (value)
ALTER TABLE Student Add foreign key (Tno) references teacher (Tno)//After the properties of the table are primary keys, the properties of the preceding table are not primary keys. The type of the property must be the same length

--Transaction control statements
Start transaction
SELECT * FROM Tablesvalue where value=1
Delete from Tablesvalue where value=1
SELECT * FROM Tablesvalue where value=1
Rollback//Deny the above four sentences to the database changes, the database back to it did not execute these four statements before
SELECT * FROM Tablesvalue where value=1

Start transaction
SELECT * FROM Tablesvalue where value=1
Delete from Tablesvalue where value=1
SELECT * FROM Tablesvalue where value=1
Commit//Agree to the above four sentences changes to the database
SELECT * FROM Tablesvalue where value=1

--Control automatic submission
Set autocommit = Off
Set autocommit = On

Set Session autocommit = Off
Set Session autocommit = On

--Create a view
CREATE View Tablesvalue as select Value,value from Tablesvalue
SELECT * FROM Tablesvalue

Create View Tablesvalue (Value,value) as select Value,value from Tablesvalue
--Modify View
Alter View Tablesvalue (Value,value) as select Value,value from Tablesvalue

--Delete View
Drop View Tablesvalue

--Show CREATE View
Show CREATE View Tablesvalue

Database MySQL Statement