MySQL is a fully networked cross-platform relational database system, a real multi-user, multi-threaded SQL database server, is also a distributed database management system with client / server architecture. It has the advantages of strong function, simple, easy to manage, fast running, and strong security and reliability, users can use many languages to access the Mysql database, for medium, small application systems is ideal. In addition to supporting standard ANSI SQL statements, it is more important to support a variety of platforms, while the software supports multi-threaded mode on the UNIX system to achieve considerable performance. For users who don't use UNIX, it can run in a system service in the Windows NT system, or run in a normal process on the Windows 95/98 system. On the UNIX / Linux system, MySQL supports multi-threaded operation, so that it can get a considerable performance, and it belongs to the open source code soft.
MySQL is an implementation of a client / server structure, which consists of a server daemnqld and a lot of different client programs and libraries. MySQL performs very high performance, very fast running, and is very good. Database. The official pronunciation of MySQL is "My Ess Que Ell" (not my-sequel).
One. Get mysql
First, you must download MySQL. MySQL's official website is: http://www.mysql.com, in China's mirror is: http://www.freecode.net.cn/mirror/mysql/ or http://www2.linuxforum.net/mirror / mysql /, you can download mysql's stable version 3.22.32 (as of the writer to the writer). Its version name is mysql-shareware-3.22.32-win, not only free and there is no so-called "30-day vitality period".
two. MYSQL installation
(1) Installation under Win98 / WinNT
To a directory, run the setup program, will prompt the entire installation process. It is installed by default to C: / MySQL. If you want to install some things, as an initiator, you can install it in the default directory. Ok, after installing, enter the c: / mysql / bin directory, run the mysqld-shareware.exe file directly under Win98, normally, there is no prompt information.
1. Unzip the downloaded file to a temporary directory.
2. Click Setup.exe to install to C: / MySQL (default).
3. Copy mysql_example.cnf to c: /my.cnf, turn # basedir = d: / mysql / change in the file to BaseDir = C: / MySQL.
4. Enter MSDOS to the directory C: / mysql / bin.
5. Enter mysqld-shareware -install, this is to start the MySQL database service.
6. MySQLADMIN -U root -p password new password, this is the password to modify the root user (the default password is empty.)
7. Enter mysql -u root -p.
8. Then, enter a new password according to the prompt, and a few prompt information will appear and prompt symbols appear.
Welcome to the mysql monitor. Commands end with; or / g. Your mysql connection id is 6268 to Server version: 3.22.32
Type 'help' for help. MySQL> At this, you have successfully installed MySQL.
(2) Installation 1 under Windows2000. Select the default directory when installing: C: / MySQL 2. Put the c: / mysql/my-example.cnf Copy as C: /my.cnf, and put the c: / mysql/lib/cygwinb19.dll copy to WinNT / System32. 3. The method of starting MySQL is: c: / mysql / bin / mysqld-shareware --install net start mysql is so simple, you can start. 4. Change the password of the super user (root): c: / mysql / bin / mysql mysql mysql> Update user set password = password ('Your password') where user = 'root'; mysql> quit c: / mysql / bin / mysqladmin Run Using the command c: / mysql / bin / mysqlshow to see the test. Here you should display: --------- | Databases | ----------- | MySQL | | TEST | --------- - See these information This step will prove that there is no problem. C: c: / mysql / bin / mysqlshow --user = root --parsqord = Your password mysql here should be displayed: Database: mysql ----- --------- | Tables | -------------- | Column_Priv | | DB | | Host | | TABLES_PRIV | | User | ----- --------- Everything is done! 5.
C: / mysql / bin / mysqladmin Version Status ProC should be able to see these version information: mysqladmin Ver 8.0 Distrib 3.22.32, for Win95 / Win98 on i586 TCX Datakonsult AB, by monty server version 3.22.32-shareware-debug protocol VERSION 10 Connection Localhost Via TCP / IP TCP Port 3306 Uptime: 1 Hour 29 min 30 Sec Threads: 1 Questions: 72 Slow Queries: 0 Opens: 16 Flush Tables: 1 Open Tables: 0 MEMORY IN USED: 16490K Uptime: 5370 Threads: 1 Questions: 72 Slow Queries: 0 Opens: 16 Flush Tables: 1 Open Tables: 0 MAX MEMORY USED: 16490K ---- ---- --- -------- ---- -------- ---- ------- --------- ------- | ID | User | Host | DB | Command | Time | State | INFO | ---- ------ ---------- ---- --------- ---- ------- ---------------- | 35 | ODBC | Localhost | | query | 0 | | Show processList | ---- ---- ---------- ---- --- ---- ---- ------ ------------------ Next, use the show database command to install the database Listed: mysql> show data; You can see: ---------- | Database | ---------- | MySQL | | TEST | ---------- 2 ROWS IN Set (0.00 sec) If everything is normal, then Mysql can work full! If you want to exit the program, enter: exit mysql> exit; Bye
three. MySQL common sense (1) field type 1. INT [(m)] Normal size integer type 2. Double [(M, D)] [Zerofill] Normal Size (Double Precision) Floating Point Digital Type 3. Date date. Supported range is '1000-01-01' to '9999-12-31'. MySQL displays the DATE value in 'YYYY-MM-DD' format, but allows you to use strings or numbers to assure the value to the DATE column 4. CHAR (M) Set the length of the string, when stored, always fill the right to the specified length 5 with spaces. BLOB text blob or text type, the maximum length is 65535 (2 ^ 16-1) characters. 6. VARCHAR becomes long string type, the most common type. (2) Basic operation 1: Display database mysql> show data; 2: currently selected database, mysql> select database (); ------------ | Database () | - ---------- | TEST | ------------ 3. Current database contains table information: mysql> show tables; ------- -------------- | Tables in test | -------------------- | MyTable1 | | MyTable2 | -------------------- 4. Get the table structure mysql> desc mytable1; -------- ------- ------ ------ ---- --------- ------- | Field | Type | NULL | Key | Default | Extra | --------- ----------- ---- ----- ------- - ------ | S1 | VARCHAR (20) | YES | | NULL | | -------- ------------ ---- - ----- -------- ------- 5. Creating a table is one of the most basic elements of the database, and the tables can be independent of each other and can be associated with each other. The basic syntax of the creation table is as follows: create Table Table_name (Column_name Datattype {Identity | Null | NOTATYPE}, ...) Where the parameter table_name and column_name must meet the Identifier requirements in the user database, the parameter datatype is a standard SQL Type or type provided by the user database. Users should use the Non-Null clause to enter data. Create Table also has some other options, such as creating a temporary table and using the SELECT clause from another table to form a new table. Also, the creation table is the primary key or index of the identifier that can be set with Primary Key, Key, Index. Pay attention to writing: List of full field list in a pair of parentheses. The field name is spaced apart with a comma. Add a space after the comma between fields. There is no comma after the last field name.
All SQL statements are ended in semicolons; Example: MySQL> Create Table Guest (Name Varchar (10), SEX VARCHAR (2), AGE INT (3), Career Varchar (10)); 6. Create an index index for queries for the database. The general database has a variety of indexing schemes, each of which is fine at a particular query class. Index can accelerate the query process of the database. The basic syntax for creating an index is as follows: CREATE INDEX INDEX_NAME ON TABLE_NAME (Col_name [(Length)], ...) Example: MySQL> CREATE INDEX NUMBER ON GUEST (Number (10)); 7. Executing a query query is the most use of the SQL command. Querying the database requires factors such as structures, indexes, and field types. Most databases contain an optimizer, convert the user's query statement into an optional form to improve query efficiency. It is worth noting that mysql does not support the nested WHERE clauses of SQL92 standards, ie it only supports a WHERE clause. The basic syntax is as follows: SELECT [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [HIGH_PRIORITY] [DISTINCT | DISTINCTROW | ALL] select_expression, ... [INTO {OUTFILE | DUMPFILE} 'file_name' export_options] [FROM table_references [WHERE where_definition] [ Group by col_name, ...] [haVing where_definition] [Order by {unsigned_integer | col_name | formula} [ASC | DESC], ...] [Limit [offset,] rows] [procedure procedure_name]]] Where WHERE clause is defined Select a standard place, where_definition can have different formats, but all follow the form: Field Name Operation Expression field name action field name In the first form, the standard compares the value of the field with the expression; in the second Under the form, compare the values of the two fields. According to the compared data type, the operations in Search_Condition may choose the following: = Check if you are equal! = Check if inequality> (or> =) Check if the left value is greater than (or greater than or equal) right value <(or <=) check if the left value is less than (or less than or equal) right value [NOT] BETWEEN Check whether the left side value is In a certain range [not] in checking the left side of a particular set of members [NOT] Like Checks the left side of the substring is [not] null check if the left is null value here, you can use wildcard _ representature any one Characters,% represents any string. Use keywords,
with
Complicated words can be generated, and they use the multi-standard set of Boolean expressions when they are running.
example:
mysql> select t1.name, t2.salary from employee AS t1, info AS t2 where t1.name = t2.name; mysql> select college, region, seed from tournament ORDER BY region, seed; mysql> select col_name from tbl_name WHERE COL_NAME> 0; 8. Change the table structure
During the use of the database, sometimes you need to change its table structure, including changing the field name, and even changing relationships between different database fields. You can achieve the above changed commands is ALTER, and its basic syntax is as follows:
ALTER TABLE TABLE_NAME ALTER_SPEC [, alter_spec ...]
example:
Mysql> ALTER TABLE DBNAME Add Column Userid Int (11) Not Null Primary Key Auto_INCREMENT
In this way, a field userid is added to the table DBNAME, and the type INT (11).
9. Modify the data in the table
During using the database process, you often modify the data in its table, such as adding new data to the table, deleting the original data in the table, or changes in the table in the table. Their basic grammar is as follows:
Data Add:
INSERT [INTO] TABLE_NAME [(Column (s))]
VALUES (Expression (s))
example:
Mysql> Insert Into MyDatabase Values ('PHP', 'MySQL', 'ASP', 'SQLServer', 'JSP', 'Oracle');
10. Data update table
(1) Modify a field at a time, pay attention to the syntax again. Text needs an additional number but numbers don't.
MySQL> Update Table01 Set Field03 = 'New Info' Where Field01 = 1;
Query Ok, 1 Row Affected (0.00 sec)
(2) Change multiple fields at a time, remember to use a comma in each updated field.
MySQL> Update Table01 Set Field04 = 19991022, Field05 = 062218 WHERE FIELD01 = 1;
Query Ok, 1 Row Affected (0.00 sec)
(3) Update multiple data at a time
MySQL> Update Table01 Set Field05 = 152901 WHERE FIELD04> 19990101;
Query OK, 3 ROWS Affected (0.00 sec)
11. Delete data
Mysql> delete from table01 where field01 = 3;
Query Ok, 1 Row Affected (0.00 sec)
12. Import database table
(1) Create a .SQL file
(2) First generate a library such as Auction.c: / mysql / bin> mysqladmin -u root -p creat auction, will prompt the password and successfully created.
(3) Import an Auction.sql file
C: / mysql / bin> mysql -u root -p Auction With the above operation, you can create a database AUCTION and one of the table Auctions. 13. Authorization of mysql database MySQL> Grant SELECT, INSERT, DELETE, CREATE, DROP on *. * (or test. * / user. * / ..) To user name @localhost Identified by 'password'; Such as: Create a new user account to access the database, you need to do the following: MySQL> GRANT USAGE -> on test. * -> to testuser @ localhost; Query OK, 0 ROWS Affected (0.15 sec) Thereafter, a new user called: Testuser, this user can only connect to the database from localhost and connect to the TEST database. Next, we must specify which operations can be performed by this user: Mysql> Grant Select, Insert, Delete, Update -> on test. * -> to testuser @ localhost; Query OK, 0 ROWS Affected (0.00 SEC) This action enables Testuser to perform SELECT, INSERT, and DELETE and UPDATE query operations in every Test database. Now we end the operation and exit the MySQL client: Mysql> EXIT BYE 14. Authorize MYSQL user password The default user name of the mysql database is "root" (MS SQL Server SA Similar), the password is default. Enter it at the DOS prompt (note, not a MySQL prompt) C: / mysql / bin> "mysqladmin -u root -p password newpassword After entering the bus, you will prompt you to enter the original password. Since the original password is empty, go directly to the bus, the root user's password is changed to "newpassword". four. Installing the phpMyadmin MySQL graphical interface manager phpMyAdmin is a set of php3 writes, a web management interface for the MySQL database system. It can easily use the field, data in the mysql database, and delete the action, and can manage the contents of the database itself in the graphical interface. Manage a single database. In addition, you can learn the correct syntax of SQL by using this graphical interface until you are proficient. Then wait until you have seen the part in the mysql manual. You can go to http://www.phpwizard.net/phpmyadmin/ to download the latest version. First, packet phpMyAdmin software in a directory. 1. Modify document config.inc.php3. Set the original setting: $ cfgservers [1] ['Host'] = ''; // mysql hostname $ cfgservers [1] ['port'] = ''; // mysql port - Leave Blank for default port $ cfgservers [1] ['adv_auth'] = false; // use advanced automation? $ cfgservers [1] ['stduser'] = ''; // mysql standard user (Only Needed with Advanced Auth $ cfgservers [1] ['stdpass'] ='; // mysql standard password (ONLY NEEDED with Advanced) AUTH) $ cfgServers [1] ['user'] = ''; // mysql user (only neededed withbasic auth) $ cfgservers [1] ['password'] = ''; // mysql password (Only Needed with Basic Auth) $ cfgservers [1] ['Only_DB'] = ''; //iff set to a db-name, ONLY THIS DB IS Accessible $ cfgservers [1] ['verbose'] = '; // verbose name for this host -leave blank to show the hostname : : Require ("English.inc.php3"); changed to: $ cfgservers [1] ['Host'] = 'mysql server hostname'; // Fill in your MySQL Server host name $ cfgservers [1] ['Port'] = ''; // Fill in the port linking MySQL, do not fill in the preset Port $ cfgservers [1] ['adv_auth'] = true; // Removed to TRUE to enter PHPMYADMIN must be authenticated first $ cfgservers [1] ['stduser'] = 'root'; // mysql user account $ cfgservers [1] ['stdpass'] = 'password'; // mysql user password $ cfgservers [1] ['user'] = 'root'; // mysql management account $ cfgservers [1] ['password'] = 'password'; // mysql management password $ cfgservers [1] ['Only_DB'] = ''; // Specify the management library name, not fill the entire Server $ cfgservers [1] ['verbose'] = ''; // Specify the name of MySQL, do not fill the Hostname of the system itself : : Require ("chinese_gb.inc.php3"); // change the language to Chinese Description: (1) Due to this management interface, you can manage multiple MySQL Server with an interface, so you can find it in config.inc.php3. $ cfgservers [1] ... $ cfgservers [1] ... $ cfgservers [1] ... : $ cfgservers [2] ... $ cfgservers [2] ... $ cfgservers [2] ... Where [1] represents the first mysql server, [2] represents the second MySQL Server, you can manage the mysql server more than three, you can refer to the same, increase [4] ..... ! (2) If your MySQL Server is the same, $ cfgservers [1] ['Host'] = can be filled in LocalHost. Finally, open the traffickeser, enter http: // After your URL / phpMyAdmin, you will see a password verification small window, enter your MySQL management account and password, you can successfully see the management screen of phpMyAdmin. With phpmyadmin, you can graphically, manage all your databases. Fives. summary Through the above learning and operation, it is finally free to operate the MySQL database, or the use of PHP or ASP is called Mysql using PHP or ASP. Finally, I would like to thank you for developing mysql and contributing to geniuses, as well as the prawn to provide mysql instructions on the Internet.
