MySQL Database learning Preliminary

the environment I used was Win7, I started to learn PHP and MySQL, and I bought this head first PHP & MySQL, which can be The official website obtains the related information and the source code of the Headfirst series books.

1. Download XAMPP Development Package

from XAMPP Chinese website Download the currently popular PHP SDK, XAMPP is a completely free and easy-to-install Apache release that includes 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 I downloaded is: Xampp-win32-1.8.3-4-vc11-installer.exe

2. Start learning MySQL

There is a certain foundation of SQL, for example, I studied in the previous school is Microsoft Sqlsever, most of the SQL basic statements have been learned to practice, so learning MySQL is very smooth, after all, except for the special differences in the basic concept is consistent. However, 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 yet. 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 the MySQL installation package separately), there is a XAMPP Control panel panel, open and click Apace, the MySQL Start button launches Apache, MySQL, Then click the right-most shell button, as shown in:

In the MySQL shell window that pops up, enter the following MySQL command to connect to the root account

Mysql-uroot-p

Such as:

The MySQL database can then be used normally.

Here is the procedure for the MySQL database I am using:

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 the XAMPP and after launching the Apache server and MySQL in the XAMPP Control panel panel, the login browser type http://localhost, after modifying the security settings such as the MySQL password, Open the phpMyAdmin link under Tools, enter your account and password to enter the phpMyAdmin management interface as shown:

Then there are two ways to do database and table operations, one is to write SQL scripts in a similar command line through the SQL bar, and a simple manual operation to create databases and tables, modify database tables, and so on.

3. mysql Command summary

Make a summary of the MySQL commands you learned

(1) MySQL command to connect to the 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 in the database named Elvis_store, now in the operation of the table in the need to select it, you can use the following command:

Use Elvis_store;

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

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

As you can see, there is a table named Email_lsit in the Elvis_store database.

(6) Display table Email_list structure

Use the describe table_name command, such as:

(7) Delete, new primary key

If I had to cover a requirement now, to remove the Federated primary Key (First_name,last_name) from the Email_list table, add an ID field and set it as the primary key, you can follow the SQL script below:

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 operation process is as follows:

In the process of modifying the structure of the Email_list table, you can use the describe command to view the structure of the Email_list table at any time to see if you have modified it according to your own intentions for the next steps.