MySQL knowledge first (i)

　　　　

MySQL Introduction
(1) MySQL database is Swedish AB developed.
(2) mysql--> Sun---Oracle.
(3) features of MySQL database.
1. Open source.
2. Free.
3. Cross-platform (windows/linux/unix/Apple).
4. Handling concurrency (13,000)/security/stability.
5. The database is light (low on resource requirements) installation file 37.7M. And
No high memory requirements for cpu/

Basic syntax:
Under DOS input:
Mysql-u User name-p password
Special note that the password after-p does not have a space/when using the command, you need to
To configure environment variables
2. MySQL database also provides a graphical interface to log in and operate.
3. How to create a database in MySQL
Basic syntax:
Create Database mydb1;
--Create a MYDB1 database
Create database mydb2 character set UTF8; --Create a
MYDB2 database using the Utf-8 character set
Create database mydb3 character Set UTF8 collate
Utf8_general_ci

How to view, create a database directive
Show create database name;
Display Database
directive: Show database;
View instructions for creating a database
Drop database UUU; ----Delete a database
How to use a database:
Use MYDB1----the use database name
-------How to create a data table
CREATE TABLE Users (
ID int,---study number
Name Varcahr (---)
)

How to back up a database and reply to a database:
------the directive, it needs to be executed directly under the DOS console.
Mysqldump-u root-p Password Data name > store path + file name. sql.
Example:
Mysqldump-u root-p123 myshop > D:\myshop.sql Database recovery;
Mysql> Source D:/myshop.sql

To recover a database:
1. Create a database MYDB2, but this database is currently empty
2.mysql console, using the source backup file path,
MySQL Paging technology:
SELECT * from table name limit start (removed from the first few), remove a few
. Create a table
Basic syntax:
CREATE TABLE Table name (
The data type for column names,
....
){
You can set character sets later
Character character set name collate proofing rules
}
Case LIST:
Create a user table
CREATE TABLE Users (
ID int,
Name varchar (64),
PWD varchar (64),
Birthday Date
)

MySQL data type:

Numeric:
1. Bit (m) m defaults to 1 MAX---maximum capacity to store eight bytes of
-----This section is integer
2. tinyint signed/unsigned The value range is: This is for the signed
-128-127 , for unsigned, the value range is: 0-255, actually said to understand is to occupy a byte. The
3.smallint accounts for two bytes and is also divided into signed and unsigned two types. The
4.mediumint accounts for 3 bytes and is also divided into signed and unsigned two types.
5. int is 4-byte signed and unsigned
6. bigint accounts for 8-byte, signed, and unsigned.

7. Float is 4 bytes to represent the
format: float (m,d) [unsigned]
M: represents the number of digits, and D indicates several
8. Double is 8 bytes
Its usage is similar to float, only the range represented Larger, but also fixed length.
9. Numeric (M, d)
is used to denote decimals, or integers.
10.decimal (M, d) is variable length.
for text and binary types:
commonly used are:
(1) char (m) m range is 0-255, fixed length
Eg:char (20) If you store, ' abc ', the actual form of existence in the table is: ' abc ' 17 Spaces "'
Tip:
MySQL comes with the client default support UTF8 code, all of us when adding Chinese, we need to set the client to support GBK.
*show variables like '%char% ' *//display setting parameters about characters
(2) varchar (m)
m for size range 0-65535 variable length
varchar (20) If you store the ' ABC ' string, the real The form ' abc '
suggests: if a table has a column length fixed, such as a product number, a number, and within 255, we should use char, if the length is not available, or if the length is greater than 255 and less than 65535, then varchar is used.
(3) text
that type, which can represent a larger string,
(4) Date type:
1. Date (date: Year-month-day)
2. DateTime
3. Timestamp

Postmark: The type can be saved year-month-day: hours and minutes
The biggest difference between it and datetime is that when you upda a record, the column
Value is automatically updated.
--------Advice: If you don't know if you should use timestamp, don't use
* Modify the new column
ALTER TABLE name add column name data type
----Modify columns (type and size of columns)
ALTER TABLE name modify column name new data type
----Delete a column
ALTER TABLE name drop column name
0. An image column is basically added to the employee table above
1. Modify the job column so that it has a length of 60
2. Delete the sex column
3. Change the table name to user
4. Modify the table's character set to UTF8
5. Name of column is modified to username
ALTER TABLE user change column name username varchar (20);
Use the INSERT statement to insert data into the table.
1. insert into table name [column name ...] values (value ...);
2. Update table name set column name = expression ... where condition
Description: If there are no conditions behind the where, it is equivalent to manipulating the entire table.
3. Delete from table name where condition,
Note: If you do not use the WHERE clause, all data in the table is deleted.
All must be careful to use
Precautions:
1. Delete statement cannot delete a column (can use Update)
2. Use the DELETE statement to delete only the records, not the table itself. If you delete a table
, using (drop statement)
3. As with insert and update, deleting records from one table will cause other
The referential integrity of the table, when modifying database data, the mind should always not
Forget about this potential problem.
4. Deleting data from a table can also use the TRUNCATE TABLE statement, which and delete
Different
---the TRUNCATE table name, you can delete the table's records faster, but
Cannot be rolled back.
4. A special description of the transaction in MySQL:

(1). The MySQL console is the default auto-Commit transaction (DML)
(2). If we are going to use transactions in the console, this should
First this setting: Set Autocommit=false;
Set a save point: Savapoint AA;
Rollback: rollback to AA;
5. SELECT statement
Select Column name .... from table name where condition
Precautions:
* Select specifies that the query is an evil column of data
* column names are specified.
* Indicates query all columns
SELECT * FROM table name.
* from the specified query that table
* Distinct optional, indicates whether important data is excluded when the result is displayed.
SELECT DISTINCT * from book;
---Use the WHERE clause to filter the query.
---a like statement,% represents 0 or more arbitrary characters, and _ represents one character.
The query results are sorted by using the ORDER BY clause, which should be at the end of the SELECT statement.
Select Column1,column2,column3. From table ORDER BY
Column Asc|desc;
ASC: Ascending desc descending;
---count statement, statistics
Count the number of students in a class
Select COUNT (*) from student where grade>90;
Sum-----aggregate function
The SUM function returns the rows that satisfy the Where condition and
1. Statistics A class math total?
Select sum (math) from student;
2. Statistics of a class of Chinese, English, mathematics, the total scores of various subjects
Select sum (Math), sum (中文版), sum (Chinese) from student;
3. Statistics a class of Chinese, English, mathematics, the sum of the results.
Select SUM (math+english+chinese) from student;
4. Statistics on the average grade of a class in Chinese
Select SUM (Chinese)/count (*) from student;
Attention:
1. Sum only works on numeric values. Otherwise you will get an error.
Usage of avg:
1. Ask for a class math average score:
Select AVG (math) from student;
2. Ask for an average score of a class total
Select AVG (Math+english+chinese) from student;
Aggregate function---max/min
I. To find the lowest score of class (the numerical range is particularly useful in statistics)
Select Max (English+math+chinese) from student;

MySQL knowledge first (i)