SQL Quick Start

relational database
Container for saving relational schema data
Relational pattern is the abstraction of business object entity, attribute and relation, and the noun of refining requirement is the common method of establishing entity Relation model. To understand the drawing of the E-r entity diagram.
Common relational database
Microsoft SQL Server, Microsoft products, medium-sized databases, running on the Windows platform, generally combined with the. NET Development platform
Oracle products, large business databases, can run on any operating system and are often used with Java.
MySQL; Originally an open source free database product, small and medium sized database, lightweight, Internet industry mainstream database
SQLITE, embedded database, Android internal main database.

Connection and composition of database
Connect to a database on a network
Mysql-h ip-p port-u root-p Enter password
show databases; view all databases
After installing MySQL, it will bring two databases Mysql,test
MySQL store some database core data, user rights password, etc.
Test database used for testing
On a single server, multiple databases can be used, and a database is created internally to a table of data tables
There are multiple users in the database, each with the appropriate operation permissions, the default is a root user, is the Super administrator has all permissions
A row of table tables is called a record and typically corresponds to an object in a Java program

SQL language
Structured Query Language
Characteristics
1, non-procedural language, each SQL statement executes without a succession of dependencies
2, officially provided database standard language, any database must support SQL language syntax
3,sql is a language used to access relational databases, with four aspects of querying, manipulating, defining, and controlling relational data.
In order to strengthen the language ability of SQL, each vendor enhances the characteristics of the process language and provides the extension of the SQL language.

SQL language Classification
DDL; a data definition language used to define a database object, such as a data table, a view, an index
DML, data manipulation language, updating in database tables, adding and deleting records, such as Update,insert,delete
DCL; Data Control language, user set user rights and control transaction statements, such as Grant,revoke,if else,while,begin transaction
DQL; Data query Language, data query language, such as Select

Use of SQL statements
Create a database
creat database[if not EXISTS] db_name;
Character Set encoding table collate comparison mode;//Specify the character set comparison method for the database, if you do not specify a default encoding set and comparison method
View Database
Show creat database db_name; View current DB encoding set
Select Database (); View the databases currently in use
Deleting a database
Drop database db_name;
Modify Database Encoding
ALTER DATABASE db_name character set encoding table
Switch database
Use db_name; If you want to operate on a database, you need to switch to the specified database

Create a data table
CREATE TABLE table_name (column name type (length), column name type (length)) Character Set encoding table
If you do not specify an encoding table, you will use the database default
such as CREATE TABLE user (
ID int,
Name varchar (40),
Password varchar (40),
Birthday Date
);
The corresponding relationship between the Java data type and the database data type, except that the string type must be specified in length other than the default length can be used
The string Varchar,char corresponds to a string, the former is variable in length and often used, and the latter is fixed in length for better performance.
Large data type Blob,text; The former mainly saves some byte files, the latter stores the character file
Numerical tinyint,smallint,int,bigint,float,double correspond to byte,short,int,long,float,double respectively
Logic-Type BIT
Date Type Date,time,datetime,timestamp
Create indicates that the table structure can be viewed with the DESC table name

Constraints on a single table
A PRIMARY key constraint is defined, a PRIMARY KEY constraint uniquely identifies a record, the primary key cannot be null and cannot be duplicated, and the primary key automatically grows if the primary key is set for a numeric type auto_increment
Primary key
Unique constraint; The contents of this field are not allowed to repeat
Unique
Defines a non-null constraint; value cannot be null
NOT NULL
such as CREATE TABLE employee (
ID int PRIMARY KEY NOT NULL auto_increment,
Name varchar (max) unique NOT NULL
Gender varchar (TEN) is not NULL,
Entry_date date NOT NULL,
Birthday date NOT NULL,
Job varchar (NOT NULL),
Resume Longtext NOT NULL
Salary double NOT NULL,
);

Data Table Modification
ALTER TABLE uses this statement to increment, modify, or delete the syntax of a column
ALTER TABLE name add column name type (length) constraint;//Add
ALTER TABLE name modify column name type (length) constraint;//Modify
ALTER TABLE name drop Column name//delete
Modify the name of a column of an existing data table
ALTER TABLE indicates change old column name new column name type (length) constraint
Modify table name rename table name to new table name
Modify the character set of a table ALTER TABLE name Charater set UTF8; Note that the table's encoding set is not the same as the database edit
Show tables View all tables in the current database
Alter primary to modify the primary key

Database crud Statements
Insert statement
Insert into table name (column name, column name ...) Values (value, value, ...) )
Be aware that the columns correspond to the values.
Insert into employee (id,name,gender,entry_date,birthday,job,resume,salary) VALUES (null, ' ZS ', ' Mall ', ' 2012-10-10 ', ' 1990-01-10 ', ' hr ', +, ' He's a man ');
All values are enclosed in single quotation marks, except for numbers without single quotes
When inserting data, if some columns have default values or can be null, inserts can omit these columns, just focus on columns that cannot be empty
You can also omit all column names when inserting data, but you must insert values for all columns in the order of the tables
Be aware of the coding problems of SQL.

UPDATE statement
Update table name set column name = value, column name = value WHERE Condition statement
Note If the WHERE statement constraint is not written, all data for that column will be changed
Update employee Set salary=5000;//This sentence changes all employees ' new water to 5000
Update Emploee set salary=5000 where name= ' ZS '; To change the salary of ZS to 5000, be aware that this is a SQL statement where a bug condition comparison may be case-insensitive, and if Zs's salary is present, You need to add the keyword binary before name to make the comparison more accurate
Update employee set salary=5000 where binary name= ' ZS ';
Update employee set salary=salary+1000 where binary name= ' ZS '; Added on original extrusion

Delete statement
Delete from table name where condition statement
Delete cannot delete a column's value, only one row of records can be deleted, and if the WHERE condition statement is not incremented, all records in the table will be deleted, but the table will not be deleted.
Table deletion using the drop statement
Another TRUNCATE TABLE name statement can also delete all table records, but not recoverable,
The TRUNCATE statement principle is to delete the table, then re-build the table, unrecoverable data, and DELETE statement can delete a row of records, you can delete all the records of the entire table, his principle is to delete all rows of records, and will not delete the table, in a transaction can be rolled back rollback recovery. When you delete an entire table, the TRUNCATE statement performs better and does not consume resources.

SELECT statement
Select *| Specify column name from table name
SELECT * FROM employee View all data in the table
Select Gendet from Employee View Gender column data in a table
Select distinct gender from Employee view the gender column data in the table, do not show duplicates, DISTINCT keyword is to go heavy.

What statements should be used should be based on the specific requirements, INSERT statement Inserts DELETE statement Delete is the operation of the whole row update,select is the operation of the column, update can also join the restriction on the column of the whole column operation, select can only operate on the whole column.

Operation on Query
Select Column Name op-expression from table name
Select column name as column alias from table name
Select name,english+10 from scores; English scores are all the same.
Select Name,english +math+chinese as total score from scores; note as can be omitted, the function of the statement is to query and find the total score, the total score display list English +math+chinese change to the alias score

Filter queries when querying
filtering queries using the WHERE statement
Select English+math+chinese as total score of where name= ' Zs ' query for ZS
SELECT * from scores where 中文版 >80; query English score greater than 80 records

Operators commonly used in SELECT statements
> < <= >= = <> Not equal to
Between...and ...
SELECT * from scores where Chinese between and 80; note write small and write big
In () takes a value in several fixed values.
SELECT * from Scorse where Chinese in (70,80,90); Query language scores are recorded in 70 and 80,90
Like fuzzy query combined with% wildcard and _ wildcard characters,% for any length string, _ for single character
SELECT * from scores the where name like ' old% ' query all records with the old beginning of the name
SELECT * from scores where name is like ' old _ ' query name with the old beginning of the two-word name the amount of all records
Is null, was not NULL, note that =null cannot be written, because null itself is a value, which means that 1/2;0 is false 1 is true;
When and and or are used together, and has a priority greater than or

Sort the order BY statement when querying
SELECT * from table name where conditional statement order by column name ASC|DSC, column name ASC|DSC
ASC is ascending, DSC is descending

Grouping functions---Data statistics in combination with query grouping
COUNT (*) returns the number of records for the query result record
Select COUNT (*) from scores where math>90; returns the number of records with a data score greater than 90
SUM (column name) sums the data for a column
Select SUM (match) from scores;
Select sum (Match), sum (Chinese) from scores the sum of the scores of the subjects of Chinese mathematics
Select SUM (Match+chinese) from scores to find the total of Chinese mathematics
AVG averages the column data
Select AVG (math) from scores to calculate the average of the math, for null values is not involved in the operation
Select AVG (Math+chinese) from scores for the average score of the total, there is also a record with a null value his other scores also fill in the calculation
Max,min the maximum value for a column of data.

Group
The purpose of grouping is to make statistics, the premise of grouping is to have duplicate data, grouped by repeating data
If the different departments according to the Department group, different classes according to the class group.
GROUP BY statement, note where is not available with GROUP by
Having keyword, adding criteria for a grouped query
Select Product,sum (Price) from Oders GROUP by product have sum (price) >100

Backup and recovery of database data
Backing up the database mysqldump-u user name-p database name > backup file name. sql
Database recovery
First create an empty database and then mysql-u the user name p database name < file name. SQL used in cmd

SQL Quick Start