2016-08-09 Essay summary MySQL related

mysql-h127.0.0.1-uroot-p Password//command line connection MySQL
show databases; View all databases
Use database name; Select Database
Show tables; View all data Sheets
Describe data table name; View table Structure

Mysqldump-uroot-p Password Study (database name) > C:\MYSTUDY.SQL//Export Database
Note: This command executes at the DOS command line and cannot be executed at the MySQL command line
Create databases Study1; Import Database
Use study1;
Mysql-uroot-p Password <c:\mystudy.sql

SELECT * from data table name; Query shows all records
Select COUNT (*) from data table name; Number of statistical record strips
Select ID, name, age from data table name where sex= ' male '//All boys ' ID, name, ages
Select Avg. from data table name where sex= ' male '//average age of all boys
Select SUM (age) from data table name where sex= ' male '//All boys ' total ages
Select ID, name, date from data table name where sex= ' man ' ORDER BY age desc limit 0,3//query for information about the oldest 3 people in a boy
Order by sort
Desc Descending
Select ID, name, date from data table name where sex= ' man ' ORDER by age ASC limit 0,3//query for information about the youngest 3 people in a boy
ASC Ascending
Select COUNT (distinct ages) from data table name//age how many stages
Distinct Remove duplicate values

Select AVG (age) from data table name GROUP by sex//grouped by sex, statistics on average ages for each group of users
GROUP BY group
Select  avg (age)  from   data table name  where  age<25 Group  by  sex  having  avg ( Age) >20  //  of users younger than 25, grouped by gender and per                                                ,         &NB Sp                          ,         &NB Sp                          ,         &NB Sp                          ,         &NB sp;  group average age must be greater than 20, identify each group of users                           &NB Sp                          ,         &NB Sp                            ,         &N Bsp                          ,         &NB Sp                           average age
Update data table name set age=22, sex= ' woman ' where id=5//change the user age of ID equals 5 to 22, gender to female
Delete from data table name where age>=25//delete all users older than or equal to 25
Delete from data table name
Insert into data table name (name, sex, age) VALUES (' Zhang San ', ' Male ', 25)

To create a table:
Command: CREATE table < table name > (< Field name 1> < type 1> [,.. < Field name N> < type n>]);
Example:
Mysql> CREATE TABLE MyClass (
> ID int (4) NOT null primary key auto_increment,
> Name char () NOT NULL,
> Sex int (4) NOT null default ' 0 ',
> Degree double (16,2));

Mysql Database Supplemental Content:
Four commonly used indexes: "1." Index main role: Indexing some key fields, also equivalent to sorting, can improve the query speed
Note: Indexes also come at a price, not as many indexed fields as possible. Indexing can increase query speed by up to 10 times times. Especially useful for big data tables. It is generally indexed for the fields commonly used in the Where, order by
】
1. Primary index primary key A table can only create one primary index, primary key default primary index, unique
2. Unique index The value in the Unique key field is unique, and a table can have multiple
3. Normal index index key, no unique requirements, a table can have multiple
4. Full-text index fulltext key is created for type fields such as text, such as news content fields

Database Engine:
Myisam: Default engine, suitable for select query operation, fast query speed. Transactions are not supported. Table lock mechanism.
InnoDB: Support transactions, row lock mechanism, suitable for update, insert and other operations.

2016-08-09 Essay summary MySQL related