MySQL Preliminary research Database

I use the environment Win7. began to learn PHP and MySQL, and bought this "head first PHP & MySQL", from the head of the website to get the headfirst series of books related information and source code.

1. Download XAMPP Development Package

from XAMPP Chinese website Download the now popular PHP development package, XAMPP is a completely free and easy to install Apache release, including MySQL, PHP and Perl. XAMPP settings for Windows, Mac OS x, and linux,xampp open source packages make installation and use surprisingly easy. The version number I downloaded is: Xampp-win32-1.8.3-4-vc11-installer.exe

2. Start learning MySQL

After having a certain SQL Foundation. For example, I used to study in the school is Microsoft Sqlsever, most of the SQL Foundation statements have been learned to practice, so learning MySQL is very smooth, after all, except for the special differences in the main concept is consistent. But compared to other relational databases such as Oracle, Sqlserver,mysql is a relatively lightweight database engine.

The best way to learn mysql is to download MySQL 5.7 Reference Manual from the official website, which is the latest version of MySQL 5.7 reference manual in English, and I haven't found the Chinese version at the moment. In addition to encounter the MySQL problem does not have Google is a good way to learn.

2 ways to learn MySQL:

(1) MySQL command line terminal

After installing the XAMPP installation package (you can also download and install the MySQL installation package separately), there is a XAMPP Control panel panel. Open and click Apace, MySQL startbutton start Apache, MySQL, and then click the Shellbutton on the right side, for example, as seen in:

watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqvy2nmmtk4odewmza=/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/ Dissolve/70/gravity/southeast ">

In the popup MySQL shell form input such as the following MySQL command to connect to the root account

Mysql-uroot-p

For example, with:

The MySQL database can then be used normally.

The following is the process of MySQL database I use:

Setting environment for using XAMPP for Windows.  [email protected] d:\programs\xampp# mysql-uroot-penter Password: ********welcome to the MySQL monitor. Commands End With; or \g.your MySQL connection ID is 17Server version:5.6.16 mysql Community Server (GPL) Copyright (c) +, Oracle an d/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names trademarks of their respectiveowners. Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.mysql> SHOW databases;+--------------------+| Database |+--------------------+| Information_schema | | China | | aliendb | | Cdcol | | Elvis_store | | Finanace_project2 | | Malan_lecture | | MySQL | | Performance_schema | | Php_test | | phpMyAdmin | | Testdemo | | Webauth |+--------------------+13 rows in Set (0.02 sec) mysql> use Elvis_storEdatabase changedmysql> SHOW tables;+-----------------------+| Tables_in_elvis_store |+-----------------------+| Email_list |+-----------------------+1 row in Set (0.00 sec) mysql> DESCRIBE email_list;+------------+------- ------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+------------+-------------+------+-----+---------+-------+| first_name | varchar (20) | NO |         PRI |       | || last_name | varchar (20) | NO |         PRI |       | || email | varchar (60) |     NO | |       NULL | |+------------+-------------+------+-----+---------+-------+3 rows in Set (0.02 sec) mysql> ALTER TABLE email_list DROP PRIMARY KEY; Query OK, rows affected (3.51 sec) records:13 duplicates:0 warnings:0mysql> DESCRIBE email_list;+------------+-- -----------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+------------+-------------+------+-----+---------+-------+| First_Name| varchar (20) |     NO |         |       | || last_name | varchar (20) |     NO |         |       | || email | varchar (60) |     NO | |       NULL | |+------------+-------------+------+-----+---------+-------+3 rows in Set (0.03 sec) mysql> ALTER TABLE email_list Add ID INT not NULL auto_increment First, add PRIMARY KEY (ID); Query OK, 0 rows affected (1.64 sec) records:0 duplicates:0 warnings:0mysql> DESCRIBE email_list;+------------+---- ---------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+------------+-------------+------+-----+---------+----------------+| ID | Int (11) | NO | PRI | NULL | auto_increment | | first_name | varchar (20) |     NO |         |                | || last_name | varchar (20) |     NO |         |                | || email | varchar (60) |     NO | |                NULL | |+------------+-------------+------+-----+---------+----------------+4 rows inSet (0.01 sec) mysql> 

(2) Using command line interface such as phpMyAdmin

phpMyAdmin is a web-based MySQL management tool written in PHP that can control and manipulate MySQL over the internet.

After installing XAMPP and after starting Apacheserver and MySQL in the XAMPP Control panel panel, log in to the browser to type http://localhost, change security settings such as Mysqlpassword, Open the phpMyAdmin link under tools. Enter the account and password into the phpMyAdmin management interface for example as seen in:

watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqvy2nmmtk4odewmza=/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/ Dissolve/70/gravity/southeast ">

Then in the Database and table operations, there are two ways, one is to use a SQL bar similar to the command line to write SQL script, the second is to use very easy manual operation to create a database and tables, change database tables and so on.

3. mysql Command summary

Make a summary of the MySQL commands you learned

(1) MySQL command to connect to server

Mysql-uroot-p

(2) Create, delete, and display databases

CREATE DATABASE Elvis_store;

DROP DATABASE Elvis_store;

SHOW DATABASES;

(3) Select a database

For example, I now have a database named Elvis_store in the database, now in the operation of the table inside the need to select it, can be used such as the following command:

Use Elvis_store;

(4) Using the Create TABLE_NAME command, a table named Email_list is created with SQL scripts such as the following:

CREATE TABLE IF not EXISTS ' email_list ' (  ' first_name ' varchar (a) Not NULL DEFAULT ' ',  ' last_name ' varchar ') NO T null DEFAULT ',  ' email ' varchar (not null),  PRIMARY KEY (' first_name ', ' last_name ')) Engine=innodb  DEFAULT charset=latin1 auto_increment=14;

Note: The above symbol is the ~ corresponding accent sign on the keyboard ', not the single-quote '

(5) Display all tables in the database Elvis_store

Use show TABLES; command

You can see from the Elvis_store database that there is a table named Email_lsit.

(6) Display table Email_list structure

Use the describe table_name command, for example, by:

watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqvy2nmmtk4odewmza=/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/ Dissolve/70/gravity/southeast ">

(7) Delete, new primary key

If I have such a requirement now, I need to delete the Union primary key (First_name,last_name) in the Email_list table, I need to add an ID field and set it as the primary key, which can be operated according to the following SQL script:

ALTER TABLE email_list DROP PRIMARY KEY; ALTER TABLE email_list Add ID INT not NULL auto_increment First, add PRIMARY KEY (ID);

The entire procedure is as seen in the following:

watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqvy2nmmtk4odewmza=/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/ Dissolve/70/gravity/southeast ">

In the process of altering the Email_list table structure, you can use the describe command to view the structure of the Email_list table at any time. See if the changes follow their intentions, so the next step.



MySQL Preliminary research Database