MySQL database study preliminary _ MySQL

MySQL database learning initially I used Win7, started to learn PHP and MySQL, and bought this book Head First PHP & MySQL, you can obtain the relevant information and source code of HeadFirst series books from Head First Labs.

1. download the XAMPP Development Kit

Download the popular PHP development kit from the XAMPP Chinese official website. XAMPP is a free and easy-to-install Apache release, including MySQL, PHP, and Perl. XAMPP is applicable to Windows, Mac OS X, and Linux. the setting of XAMPP open source package makes installation and use surprisingly easy. The version I downloaded is: xampp-win32-1.8.3-4-VC11-installer.exe

2. start learning MySQL

After a certain degree of SQL Foundation, for example, I used to learn Microsoft SQLSever in my previous school. most of the basic SQL statements have been learned and practiced, so I learned MySQL smoothly, after all, except for the special differences, the basic concepts of exceptions are the same. However, compared with other relational databases such as Oracle and SQLServer, MySQL is a lightweight database engine.

The best way to learn about MySQL is to download MySQL 5.7 Reference Manual from the official website, which is the latest MySQL 5.7 Reference Manual in English. I have not found the Chinese version yet. In addition, Google is also a good way to learn MySQL.

Two 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 an XAMPP Control Panel. open it and click the Start button of Apace and MySQL to Start Apache and MySQL, click the shell button on the rightmost side, as shown in:

In the pop-up MySQL shell window, enter the following MySQL command to connect to the root account

mysql -uroot -p

For example:

Then you can use the MySQL database normally.

The following is the process of using the MySQL database:

Setting environment for using XAMPP for Windows. administrator @ CCF-PC d:/programs/xampp # mysql-uroot-pEnter password: ******* Welcome to the MySQL monitor. commands end with; or/g. your MySQL connection id is 17 Server version: 5.6.16 MySQL Community Server (GPL) Copyright (c) 2000,201 4, Oracle and/or its affiliates. all rights reserved. oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. other names may be trademarks of their respectiveowners. type 'help; 'or'/h' for help. type '/C' to clear the current input statement. mysql> show databases; + databases + | Database | + databases + | 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 + | Tables_in_elvis_store | + TABLES + | 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, 13 rows affected (3.51 sec) Records: 13 Duplicates: 0 Warnings: 0 mysql> 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: 0 mysql> 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 in set (0.01 sec) mysql>

(2) use command line interfaces such as phpMyAdmin

PhpMyAdmin is a Web-based MySQL management tool written in PHP. it can control and operate MySQL over the Internet.

After XAMPP is installed and the Apache server and MySQL are started on the XAMPP Control Panel, log on to the browser and type http: // localhost. after changing the security settings, such as the MySQL password, open the phpMyAdmin link under Tools, enter the account and password to go to the phpMyAdmin management interface, as shown in:

There are two ways to perform database and table operations. one is to write an SQL script in the SQL column in a way similar to a command line, another method is to manually create databases and tables and modify database tables.

3. MySQL command summary

Summarize the learned MySQL commands.

(1) connect the MySQL command 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, if I have a database named elvis_store in my database, I need to select it when operating the table in it. the following command can be used:

USE elvis_store;

(4) Use the CREATE TABLE_NAME command to CREATE a table named email_list. the SQL script is as follows:

CREATE TABLE IF NOT EXISTS `email_list` (`first_name` varchar(20) NOT NULL DEFAULT '',`last_name` varchar(20) NOT NULL DEFAULT '',`email` varchar(60) NOT NULL,PRIMARY KEY (`first_name`, `last_name`)) ENGINE=InnoDBDEFAULT CHARSET=latin1 AUTO_INCREMENT=14 ;

Note: The above symbol is ~ on the keyboard ~ Corresponding accent symbol 'instead of single quotation marks'

(5) display all tables in the elvis_store database

Use the show tables command

The elvis_store database contains a table named email_lsit.

(6) display table email_list structure

Use the DESCRIBE TABLE_NAME command, for example:

(7) delete and create a primary key

If I want to hide a request, 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, you can follow 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 operation process is shown in:

When modifying the structure of an email_list table, you can use the DESCRIBE command to view the structure of the email_list table at any time to see if the table has been modified according to your own intent, so that you can proceed to the next step.