We use the current my-sql to compare the new version of my SQL 5.0 version. First, how to get my SQL 5.0 we can download from the Internet ([Url]www.mysql.cn[/url]).
Let's look at how to install my SQL to support multiple platforms, that is, it can work on the Windwos platform, or it can work on a Linux platform, the installation under Linux may be a little more complicated, but the installation under Windows is very simple, And we usually install the software is no different, let's start the installation: We got an executable file, double-click the Setup.exe file, start mysql5.0 installation. Wait until the following welcome window appears
*********
Installation process slightly!
********
How to detect your MySQL properly installed. So how do you detect if SQL Server is installed correctly?
Netstat-an
See if there are 3306 mouths to go! Or you can go and see if your service has started!
Second, log on to the MySQL server: two methods:
1. Mysql–hlocalhost–uroot–p Password
2, directly use the Start menu! Start----Program---mysql5.0, simple use of commands:
The following operations typically use standard SQL commands: That is, how you use it in SQL Server, where you use it, but some commands are not exactly the same. There's a little bit of nuance! Let's simply pass this command over again:
Use of my SQL command: ends with a semicolon or \g, \c cancels one line of command, \h Help
1, display database: show databases;
2. Build database: Create DATABASE [if not exists] name;
3. Build data table: CREATE TABLE [if not EXISTS] Table name (field name 1 type ...) )
CREATE table student (number int auto_increment primary key, name varchar (10));
Note: Set the automatic growth, it is necessary to set the primary key, if the bit type is selected, 0 does not display, not 0 display as a special symbol!
4. Display Data sheet: show tables;
5. Delete Library: Drop database [if exists] library name;
6. Delete Table: The table name of drop table [if exists];
7. Display table structure: DESC table name
8, how to modify the table structure: the growth of a field; ALTER TABLE name add field name type
9. Delete a field: ALTER Table name drop field name
10. Modify the properties of a field: ALTER TABLE name modify field new property
11. Modify the primary key: Add a PRIMARY key ALTER TABLE name add primary key (field name)
12. Delete a primary key ALTER TABLE name drop PRIMARY key (field name) Four, table of three basic operations: Insert record, delete record, update record, this and SQL Server is the same, how do you do in SQL Server, How to operate it in MySQL! There is no big difference, of course, there are small differences. For example, insert command, SQL SERVER2000 only one record at a time, MySQL can insert multiple records.
Insert into table (Field table list) VALUES (field values), (field values) ...
Delete, update and SQL Server no difference, no longer a statement! 13. Delete the database: Drop databases database name
14. Delete tables: drop table name
15, the name of the table: RENAME table in the old tables to the new table name database can not be renamed, but also not absolutely can not change, but not good will result in the inside of the data can not read normally, the consequences of self-esteem!
16, library Renaming: The common method is to create a new library, and then the old library data into the.
V. Query: This should be a chunk of SQL.
Select query: There is no big difference with SQL Server:
The key is to be proficient in the use of various operators, mathematical operators are relatively simple, focusing on the character operator like, relational operators and logical operators
For example: Find the King's record: Select * from Yuangong Where name like ' King% ';
Find five of records in a name: Select * from Yuangong Where name like '% King ';
Find records ending with Wang: Select * from Yuangong Where name like '% King ';
Where linit is not in SQL Server.
For example: I want to show the third to seventh record
SELECT * FROM table name limit 2, 5;
Wildcard Description Example
% pass with 0 or more arbitrary characters
_ (underline) wildcard any character
No longer contains [] different from SQL SERVER
Note: If you find the result is incorrect with like, there may be a problem with the encoding six, record sorting
Sort records with ORDER by
Format: Select field list from table name [where condition] order by sort field 1 [ASC] [desc] [Sort field 2 ...]
such as: in ascending order of Yuangong table by age!
SELECT * from Yuangong ORDER BY age ASC or SELECT * FROM Yuangong ORDER by age
such as: in descending order of Yuangong table by age!
Select * from Yuangong ORDER BY age DESC
The employee table is sorted by sex in ascending order, gender is the same as the age from big to small
Select * FROM employee table order by gender ASC, age DESC VII, aggregation function:
Max: Max () min min () average AVG ()
Sum: Sum () Rollup: count () such as: average of the base salary for each department
Select Department, AVG (base salary) as department base salary from employee table Group by Department show department with average base wage greater than 3000
Select Department, AVG (base salary) from Employee table Group Department where AVG (base salary) >3000
This sentence is wrong. SQL specifies that conditions in a grouping cannot be used where but have
Select Department, AVG (base salary) from Employee form GROUP by department having AVG (base salary) >3000
Eight, multi-table query:
A database of more than one table, there is a certain connection, how to display the normal information such as the table?
There are now three tables:
Yg
Name Sex Age
Samnang Male 20
Ma Dongxu Female 40
Gs
Name Title Date Unit
Samnang AD detailed 2006-11-10 Tsinghua University
Ma Dongxu Linux 2005-01-01 Renmin University
Dz
Unit Address
Five crossing of Tsinghua University
Renmin University of Huang Zhuang
The first method is called cross-join, which is also called the Cartesian product in SQL Server
But note that the total number of records generated by default is the product of two table records
SELECT * from Yg,gs;
SELECT * from Yg,gs where yg.name=gs.name;
This is the record we want.
The second method is to connect using join:
Internal connection
SELECT * FROM YG join GS on Yg.name=gs.name
Left outer connection
Right outer connection
But there is no full outer connection
Ix. Union:
In addition to the connection, Mysql4. More than 0 of the versions also support the Union operator, which is used to concatenate the output of multiple select query numbers into a single result set. In most cases, this operator is used to add the result set produced by the query to a different table, while creating a separate table that includes all the results. For example, the interview asked you, there are two tables, the same field information, let you use a statement to the two table information into a separate table!
To illustrate how the Union operator is used, let's take an example: now there are two tables, each of which is a male classmate information and a female classmate information, if you use a statement to show all the students information!
Mysql> select * from Nan;
+--------+-------+
| name | Score |
+--------+-------+
| Peng Cong Stay | 80 |
| Fee Advantage | 81 |
| Chiquan | 82 |
+--------+-------+
3 Rows in Set (0.00 sec) mysql> SELECT * from NV;
+------+-------+
| name | Score |
+------+-------+
| Peng Hong | 80 |
| Fehon | 81 |
| monascusred | 82 |
+------+-------+
3 Rows in Set (0.00 sec) mysql> select * FROM nan Union SELECT * from NV;
+--------+-------+
| name | Score |
+--------+-------+
| Peng Cong Stay | 80 |
| Fee Advantage | 81 |
| Chiquan | 82 |
| Peng Hong | 80 |
| Fehon | 81 |
| monascusred | 82 |
+--------+-------+
6 rows in Set (0.00 sec)
What if there are three tables? Also the same operation! Note, however, that if a record information of two tables is exactly the same, only one is displayed, and if you want to display all records, add all after union
Mysql> select * FROM nan UNION ALL SELECT * from NV;
What if the interviewer asks you if you want to save the displayed information to a table?
Mysql> CREATE TABLE name SELECT statement;
X. Database backup and Recovery
Backup:
The first method: Mysqldump back up a table Mysqldump-h host name –u username –p password database table name > text file
Recovery: mysql-h hostname –u user name –p Password Database < text file
What do I do to back up two or more tables in a database?
Mysqldump–h Host name –u user name –p password database table name 1 Table Name 2 > text file
When recovering: mysql–h hostname –u user name –p Password Database < text file back up the entire database:
Format: Mysqldump–u user name-p password Database name > text file name
Example: c:\test>mysqldump-uroot-p111111 net14 >net14.txt Delete database Net14:drop db net14
To recover:
C:\test>mysql-u root-p111111 Net14<net14.txt
ERROR 1049 (42000): Unknown database ' net14 '
The error is not found. Database net14;
You must manually create an empty net14 database before you can bring the data in!
C:\test>mysql-u root-p111111 Net14<net14.txt That's one more question, what if you want to back up more than two databases at the same time?
Format: Mysqldump–u user name-p password-B database 1 database 2 > text file name
such as: C:\test>mysqldump-uroot-p111111-b net14 net28 >net1428.txt
Then remove net14 and net28 and then restore
However, be aware that you must restore one or two of the same:
such as: C:\test>mysql-uroot-p111111-b net14 <net1428.txt
C:\test>mysql-uroot-p111111-b Net28 <net1428.txt The second method: SELECT INTO for backup: This is much simpler than the first method!
Format: SELECT statement into outfile "path and filename";
such as: mysql> select * from student to outfile ' c:\\abc1.txt ';
Query OK, Rows Affected (0.00 sec)
Attention:
1. The disk specifier in the path is two \ \, where the first one represents the escape function and the second is the root directory. Sometimes write a \ time will not error, so pay attention to check the correctness of the backup;
2. Do not allow rewriting of text files;
Recovery method:
So how to recover::
Use load data to recover:
Format: Load data infile ' path and filename ' into table table name
such as: Load Data infile ' c:\\student.txt ' into table student;
Note: The table must exist. Delete can be used to empty all of the records or use: TRUNCATE table name, delete only records, do not delete the structure!
What if there is a recovery error?
1. Permissions issues.
2. The delimiter does not match!
3. Path and filename is wrong!
XI. Import/Export of data:
How to import and export data with other data sources!
Example: How to lead SQL server data to MySQL
1. Export the data to the *.txt file format in the MS SQL 2000 Import and Export tool first
Note Open Backup.txt
Observe the separator character in MS SQL 2000 as if it were separated by commas
2. In MySQL, use the Load Data infile command to import
mysql> load Data infile ' c:\\sql.txt ' into table ABC fields terminated by ', ';
Query OK, 5 rows Affected (0.00 sec)
Records:5 deleted:0 skipped:0 warnings:0
Note that the target table must already exist, and the structure should be the same as the structure of the source table!
ACCESS leads to MYSQL:
1. First create an Access file, save it as a text file
2. Open the text file, and then convert the code into ANSI
3. Build the database and import it into MySQL!
mysql> load Data infile ' c:\\abc1.txt ' into table ABC fields terminated by ', ';
Query OK, 3 Rows Affected (0.00 sec)
EXCEL leads to MySQL
Step above. Just note: Excel defaults to TAB-delimited, so apply the following statement:
mysql> load Data infile ' c:\\book1.txt ' into table ABC fields terminated by ' \ t ';
MySQL Basic operation
