One, SQL statements
1. Operational database
(1) Create a database
CREATE DATABASE [IF not EXISTS] db_name [create_specification [, Create_specification] ...]
Create_specification:
[DEFAULT] CHARACTER SET Charset_name | [DEFAULT] COLLATE collation_name
~ Create a database called MYDB1.
Create Database mydb1;
~ Create a MYDB2 database that uses the GBK character set.
Create DATABASE mydb2 character Set GBK;
~ Create a MYDB3 database that uses the UTF8 character set and with the proofing rules.
Create DATABASE mydb3 Character Set utf-8 collate utf8_bin;
(2) View database
To display the database statement:
SHOW DATABASES
To display the database creation statement:
SHOW CREATE DATABASE db_name
~ View all databases in the current database server
show databases;
~ View the definition information for the MYDB2 database created earlier
Show CREATE Database mydb3;
(3) Modify the database
ALTER DATABASE [IF not EXISTS] db_name [alter_specification [, Alter_specification] ...]
Alter_specification:
[DEFAULT] CHARACTER SET Charset_name | [DEFAULT] COLLATE collation_name
~ View the database in the server and modify the MYDB2 character set to UTF8
ALTER DATABASE MYDB2 character set UTF8;
(4) Deleting a database
DROP DATABASE [IF EXISTS] Db_name
~ Delete the MYDB1 database that was created earlier, drop DB mydb1;
Drop database mydb1;
(5) Select database
Access database: Use db_name;
View the currently selected databases: Select database ();
2. Operation table
(1) Create a table
CREATE TABLE table_name
(
Field1 datatype,
Field2 datatype,
FIELD3 datatype,
) [Character set character set] [collate proofing rules]
Field: Specify column name datatype: Specify column type
~ Create an Employee table employee
CREATE TABLE Employee (
ID int primary KEY auto_increment,
Name varchar (unique),
Gender bit NOT NULL,
Birthday date,
Entry_date date,
Job varchar (40),
Salary Double,
Resume text
);
(2) View table
View table structure: desc tabname
View all tables in the current database: show tables
View current database table Build table statements show create tables tabname;
(3) Modify the table
ALTER Table Table Add/modify/drop/character set/change (column datatype [DEFAULT expr][, column datatype] ...);
* Name of the modified table: Rename table name to the new name;
~ An image column is basically added to the employee table above.
ALTER TABLE employee add image blob;
~ Modify the job column so that it is 60 in length.
ALTER TABLE employee Modify job varchar (60);
~ Delete the Gender column.
ALTER TABLE employee drop gender;
~ Table name changed to user.
Rename table employee to user;
~ The character set of the modified table is GBK
ALTER TABLE user character set GBK;
~ Column Name name modified to username
ALTER TABLE user change name username varchar (20);
(4) Delete a table
DROP TABLE Tab_name;
~ Delete User Table
drop table user;
3. The action sheet Records crud
(1) INSERT
INSERT into table [(column [, Column ...])] VALUES (value [, value ...]);
The data you insert should be the same as the data type of the field.
The size of the data should be within the specified range of columns, for example: You cannot add a string of length 80 to a column of length 40.
The data locations listed in values must correspond to the arranged positions of the columns being joined.
Character and date data should be enclosed in single quotes.
Insert null: Do not specify or INSERT into table value (NULL)
If you want to insert all the fields, you can save the list of columns by writing the list of values directly in the table's field order
~ Inserting three employees ' information into a table using INSERT statements
INSERT into employee (Id,name,gender,birthday,entry_date,job,salary,resume) VALUES (null, ' Zhang Fei ', 1, ' 1999-09-09 ', ' 1999-10-01 ', ' thugs ', 998.0, ' the eldest brother's three brothers, really can play ');
INSERT into employee values (null, ' Guan Yu ', 1, ' 1998-08-08 ', ' 1998-10-01 ', ' wealth ', 9999999.00, ' boss's second brother, the company earns all points to him ');
INSERT into employee values (null, ' Liu Bei ', 0, ' 1990-01-01 ', ' 1991-01-01 ', ' CEO ', 100000.0, ' boss of the company '), (null, ' Zhao Yun ', 1, ' 2000-01-01 ', ' 2001-01-01 ', ' Bodyguard ', 1000.0, ' the boss ' personal person ');
(2) UPDATE
UPDATETbl_name SET col_name1=expr1 [, col_name2=expr2 ...] [WHERE Where_definition]
The update syntax can update the columns in the original table row with the new values.
The SET clause indicates which columns to modify and which values to give.
The WHERE clause specifies which rows should be updated. If there is no WHERE clause, all rows are updated
~ Change the salary of all employees to 5000 yuan.
Update employee Set salary = 5000;
~ The salary of the employee whose name is ' Zhang Fei ' is revised to 3000 yuan.
Update employee Set salary = where name= ' Zhang Fei ';
~ The salary of the employee whose name is ' Guan Yu ' has been changed to 4000 yuan and the job changed to CCC.
Update employee set salary=4000,job= ' CCC ' where name= ' Guan Yu ';
~ Liu Bei's salary will be increased by 1000 yuan on the original basis.
Update employee set salary=salary+1000 where Name= ' Liu Bei ';
(3) DELETE
If you do not use the WHERE clause, all data in the table is deleted.
Delete statement cannot delete a column's value (can use update)
Use the DELETE statement to delete records only and not delete the table itself. To delete a table, use the DROP TABLE statement.
As with insert and update, deleting records from one table will cause referential integrity problems for other tables, and you should always keep this potential problem in mind when modifying database data.
FOREIGN KEY constraints
You can also use the TRUNCATE TABLE statement to delete data from a table, which is different from delete, see the MySQL documentation.
Delete from Tbl_name [WHERE where_definition]
~ Delete the record in the table named ' Zhang Fei '.
Delete from employee where Name= ' Zhang Fei ';
~ Delete all records in the table.
Delete from employee;
~ Use truncate to delete records in the table.
TRUNCATE TABLE employee;
(4) SELECT
To. Basic Query
SELECT [DISTINCT] *| {column1, Column2. Column3 ...} FromTable
~ Query The information of all students in the table.
SELECT * from exam;
~ The names of all students in the enquiry form and the corresponding English scores.
Select Name,english from exam;
~ Filter duplicate data in a table
SELECT distinct 中文版 from exam;
-Add 10 points to all student scores.
Select name, math+10,english+10,chinese+10 from exam;
~ Count Each student's total score.
Select name, English+math+chinese from exam;
Use aliases to represent student totals.
Select name as name, English+math+chinese as total from exam;
Select name Name, English+math+chinese total from exam;
Select name 中文版 from exam;
To filter a query with a WHERE clause
~ Query the student's grade named Zhang Fei
SELECT * FROM exam where name= ' Zhang Fei ';
~ Query students with English scores greater than 90 points
SELECT * from exam where 中文版 > 90;
~ Search all students with a total score greater than 230
Select name Name, Math+english+chinese total from exam where math+english+chinese>230;
~ Check the English score between 80-100 students.
SELECT * from exam where 中文版 between and 100;
~ Query Math scores for 75,76,77 's classmates.
SELECT * from exam where math in (75,76,77);
~ Check all the student's grades with Zhang's surname.
SELECT * from exam where name is like ' Zhang% ';
SELECT * from exam where name is like ' Zhang __ ';
~ Query Math >70, Chinese >80 students.
SELECT * from exam where math>70 and chinese>80;
To. Use the order BY keyword to sort the results of a query
SELECT Column1, Column2. Column3. FromTable where ... order by column Asc|desc;
ASC Ascending--The default is ascending
Desc Descending
~ The output after sorting the Chinese scores.
Select Name,chinese from Exam order by Chinese desc;
~ Sorting by total score output from highest to lowest order
Select name Name, chinese+math+english total from exam order by total desc;
~ Sort the output of the student's grade of Zhang surname
Select name, chinese+math+english total from exam where name like ' Zhang% ' order by total desc;
~ ~. Aggregate functions
(1) Count--to count the number of rows that meet the criteria
~ How many students are there in a class?
Select COUNT (*) from exam;
~ How many students with a statistical math score greater than 90?
Select COUNT (*) from exam where math>70;
~ What is the number of people with total statistics greater than 230?
Select COUNT (*) from exam where Math+english+chinese > 230;
(2) Sum--Used to sum the specified column of a record that matches a condition
~ Statistic A class math total?
Select sum (math) from exam;
~ Statistics of a class of Chinese, English, mathematics, the total scores of each department
Select sum (Math), sum (中文版), sum (Chinese) from exam;
~ Statistics of a class of Chinese, English, math scores sum
Select SUM (ifnull (chinese,0) +ifnull (english,0) +ifnull (math,0)) from exam;
When a calculation is performed, the entire structure of the calculation is null, as long as there is a null participation in the calculation
This can be handled with the Ifnull function
~ Statistic The average score of a class's Chinese score
Select SUM (Chinese)/count (*) language average score from exam;
(3) AVG--The average value of the specified column used to calculate the qualifying record
~ Ask for a class math average score?
Select AVG (math) from exam;
~ to ask for a class score average?
Select AVG (ifnull (chinese,0) +ifnull (english,0) +ifnull (math,0)) from exam;
(4) Max/min--Used to get the maximum and minimum values for all records that match the criteria specified by the column
~ Ask for class highest score and lowest score
Select Max (Ifnull (chinese,0) +ifnull (english,0) +ifnull (math,0)) from exam;
Select min (ifnull (chinese,0) +ifnull (english,0) +ifnull (math,0)) from exam;
--Group queries
~ Display the total price of each category of goods after sorting the items in the order form
Select Product,sum (Price) from the orders group by product;
~ A number of items have been purchased and each category has a total price greater than 100
Select Product Product name, SUM (price) Total merchandise amount from the orders group by product have sum (prices) >100;
The difference between the WHERE clause and the HAVING clause:
The WHERE clause is filtered before grouping and having clauses are filtered after grouping
An aggregate function can be used in a HAVING clause, which cannot be used in a WHERE clause
There are many cases where the WHERE clause can be substituted using the HAVING clause
-Check the name of the item with a unit price less than 100 and a total price greater than 150
Select product from the orders where PRICE<100 group by product has sum (price) >150;
~~sql Statement Writing Order:
Select from where GroupBy have an
~~sql Statement Execution Order:
From where select group by have order by
~ ~ Backup Recovery Database
Backup: Under the cmd window mysqldump-u root-p dbname>c:/1.sql
Recovery: Mode 1: Under the cmd window mysql-u root-p dbname<c:/1.sql
Mode 2: Under the MySQL command, source C:/1.sql
Be aware that recovering data can only restore the data itself, the database cannot be recovered, and you need to create the data yourself before you can recover it.
Second, multi-table design multi-table query
1. FOREIGN KEY constraints
The table is used to save the data displayed in the life, and the real life of the data and data often have a certain relationship, we use the table to store data, we can explicitly declare the table and table before the dependency, command database to help us maintain this relationship, to this constraint is called a FOREIGN KEY constraint
CREATE TABLE Dept (
ID int primary KEY auto_increment,
Name varchar (20)
);
INSERT INTO dept values (NULL, ' Finance Department '), (null, ' HR '), (null, ' Sales department '), (null, ' administration Department ');
CREATE TABLE EMP (
ID int primary KEY auto_increment,
Name varchar (20),
dept_id int,
Foreign KEY (dept_id) references dept (ID)
);
INSERT into EMP values (NULL, ' Obama ', 1), (null, ' Harry Potter ', 2), (null, ' Bin Laden ', 3), (null, ' Park ', 3);
2. Multi-table Design
One-to-many: the primary key of a party that is saved on many sides as a foreign key
One-to-one: Save the other party's primary key as a foreign key on either side
Many-to-many: Create a third-party relationship table holds the primary keys of the two tables as foreign keys, saving their correspondence
3. Multi-Table Query
Cartesian product query:
The result of a multiplication of two table records is a Cartesian product query, if the left table has n records, the right table has m records, Cartesian product query has N*M records, which often contain a lot of wrong data, so this query method is not commonly used
SELECT * from Dept,emp;
Internal connection query: The query is the left table and the right table can find records of the corresponding records
SELECT * from dept,emp where dept.id = emp.dept_id;
SELECT * FROM dept INNER join EMP on dept.id=emp.dept_id;
Outer JOIN query:
Left OUTER JOIN query: Add a record on the left side of the table that has not the right table on the inner join
SELECT * FROM dept LEFT join EMP on dept.id=emp.dept_id;
Right outer join query: Add a record on the right side of the table without the left table, based on the inner join
SELECT * FROM dept right join EMP on dept.id=emp.dept_id;
Full outer JOIN query: Add the left table on the basis of the inner join and the right table does not have a record and the right table has a record that does not have the left table
SELECT * FROM dept full join EMP on dept.id=emp.dept_id; --MySQL does not support full external connections
You can use the Union keyword to simulate an all-out connection:
SELECT * FROM dept LEFT join EMP on dept.id = emp.dept_id
Union
SELECT * FROM dept right join EMP on dept.id = emp.dept_id;
Black Horse day09 MySQL basic grammar
