Basic MySQL concepts and simple operations

First, MySQL

MySQL is a relational database management system developed by the Swedish MySQL AB company and is currently part of Oracle's product portfolio.　　MySQL is one of the most popular relational database management systems, and MySQL is the best RDBMS (relational database Management system) application software for WEB applications.　　MySQL saves data in different tables rather than putting all of the data in a large warehouse, which increases speed and increases flexibility. The SQL language used by MySQL is the most commonly used standardized language for accessing databases. MySQL software has adopted a dual licensing policy, divided into community and commercial version, due to its small size, fast, low total cost of ownership, especially the open source, the development of the general small and medium-sized web site to choose MySQL as the site database. Thanks to its Community edition performance, PHP and Apache make a good development environment second, MySQL and other large database comparison
such as Oracle, DB2, SQL Server, and so on, MySQL has its shortcomings, but this has not reduced its popularity.　　MySQL provides more than enough functionality for general personal users and small and midsize businesses, and because MySQL is open source software, it can significantly reduce total cost of ownership. Linux as the operating system, Apache or Nginx as a Web server, MySQL as a database, Php/perl/python as a server-side scripting interpreter. Since these four software are free or open source software software (floss:free/libre and open source software), using this method can establish a stable, free website system, also known as "LAMP" or "LNMP" combination, except labor cost. third, the importance of the database in the WebIn order to make static HTML page work, become dynamic page display, need to through the Apache server-side PHP scripting language, relying on database processing data, to achieve dynamic, so while learning PHP, master MySQL database basic operation is necessary.

Iv. Some common database systems

MySQL, DB2, Oracle, SQL Server, mariadb, and so on, because MySQL is already a product of Oracle, may be closed source, no longer free, but the MARIADB database management system is a branch of MySQL, mainly by the open source community in the maintenance, MARIADB is fully compatible with MySQL, including APIs and command lines, making it easy to be a replacement for MySQL. In terms of the storage engine, mariadb the transaction-based Maria Storage engine, replacing the MySQL MyISAM, and using xtradb instead of the MySQL InnoDB.

V. Types of databases

1. Relational database

2. Non-relational database

VI. SQL Structured Query Language

Must be connected to our database through a server to operate

Use a variety of SQL statements, add, delete, change, check

　1. Data definition Language (DDL):

Used to define and manage data objects, including databases, data tables, and so on. For example: Create,drop,alter and so on.

databases, data tables: creating, deleting, modifying, and manipulating databases or data tables

　2. Data manipulation Language (DML):

Used to manipulate the data contained in a database object. For example: Insert,update,delete statement.

The manipulation of data by adding, deleting and changing these operations is the language of data manipulation.

　　3. Data Query Language (DQL):

It is used to query the data contained in the database object and can make a single table query.

Connection queries, nested queries, and collection queries, and other database queries of varying degrees of complexity, and data

Returns the display in the client. SELECT

　　4. Data Control Language (DCL):

is the language used to manage the database, including administrative permissions and data changes.

Vii. operating MySQL in the doc system

Because I am a personal learning, the installation environment is done in the Windows operating system, that is, the Windows+apache+php+mysql integration package, the following is known as "Wamp". To operate MySQL in the doc system

Some of the configuration that needs to be done before

　　1> Environment variables

My Computer, right-click Properties, advanced system settings, environment variables--System variables->path

After the path variable, add the value of the variable

The value of the variable is the bin directory path under MySQL

Note: You must turn on the WAMP server before you can go in

2> access mysql Database

Command for clear screen under DOS system: CLS

Mysql-h Localhost-u Root-p-B

-H Server (local, specified server IP) [local localhost can be omitted]
-u username (we use root User: Super Admin)
-P User Password (no need to write password after it, password is written after carriage return)
-B Buzzer (when the command is wrong, the sound that is prompted when the error is made)

3> commands after entering MySQL database

Attention:
1> after each line of command is finished, please use; or \g to the end, otherwise the command can be written down
2> in MySQL command, if a line of command does not finish, then enter, it will continue to execute, if it must be a complete command
3> exit the wrong command, or do not want to continue the execution of the command, with \c or CTRL + C
4> we want to let the data stand up to show, after the command with \g, sometimes a row can not be filled with chaos, \g to solve the problem
5> \s can display the configuration of the current server
6> Help View all the shortcut commands you can use
7> If you encounter a single quote at the command line, MySQL thinks you want to enter a string, so it is necessary to use the end of the single quotation mark, you can jump out of the string input state;

　 4> Exit database Server
\q, exit, quit three can be

Attention:

To store all of the commands that we enter below the DOS command line into a single document, you need to use the tee command

Tee path;

Example: Tee E:\wamp\www\Test\Test_MySQL\1.php

Viii. Some commands for manipulating databases

　　　　 1. Create a database

Create database name;

Attention:
1. You cannot create a database with the same name if the database already exists
2. Create commands and database names are case-insensitive

If not exists if the database already exists, add the preceding keyword to avoid the error

　　　 2. Deleting a database

drop database name;

　　　 3. View the database you have created

show databases;

　　　　 4. Use a database

Use database name;

　　　　 5. View the current database we are using

Select Database ();

　　　 6. View the Build database statement

Show create database name;

Note: Through this command, we can see the database building statements, encoding type;

Attention:
The commands in the 1.MySQL database are case insensitive.

2. For each database created, a folder named after the database name is created in the data directory.

3. Under Windows, database names are also case insensitive, but under Linux, database names are strictly case-sensitive.

　　　 7. Operation of the data table

A field in a table can be understood as a header in a table table

　　　　　　　　　　 1> Create a data table (without any primary key information, self-increment, data-constrained simple data sheet creation) > Initial practice available
CREATE TABLE Table name (
Field name 1 field type,
Field Name 2 field type,
Field Name 3 field type
);

Attention:
1. When creating a data table, it is separated by a comma "," between each of its fields;
2. The last field does not have a comma ",";
3. After the table is created, end with a semicolon after the last parenthesis
4. When a table is built, the field name (field type) of the table that is written in parentheses after the table name

　　　　　　　　　　 2> View Table Structure

DESC table name;

　　　　　　　　　 3> View the Build Table statement

Show create table table name;

　　　　　　　　　　 4> Viewing the data tables that exist in the current database

Show tables;

　　　　　　　　　　 5> Deleting a data table

DROP table Name

　　　 8. Operation of the data

　　　　　　　　　　 1> Adding Data
①insert into table name (Field 1, field 2 ...) VALUES (value 1, value 2 ...);

②insert into table name values (value 1, value 2 ...), (value 1, value 2 ...);

③insert into the table name (Field 1, field 2 ...) VALUES (value 1, value 2 ...), (value 1, value 2 ...);

④insert into table name values (value 1, value 2 ...);

⑤insert into table name set field 1= value 1, field 2= value 2 ...;

Attention:
1. The value and the field name should correspond, otherwise the error will be
2. The value you write must match the data type

　　　　　　　　　 2> Deleting Data

Delete from table name where condition;

Note: When deleting data, be sure to add a where condition, otherwise all data will be deleted

　　　　　　　　　　 3> Modifying Data

Update table name set the field to modify = modified value Where condition

Note: When modifying data, be sure to add a where condition, otherwise all data will be modified

　　　　　　　　　 4> Finding Data

SELECT * (all fields) from table;

Select field 1, Field 2: from table;

Here is an example of a simple operation on a database:

<?php//simple operation method to the database//display all databases mysql> show databases; +--------------------+| Database |+--------------------+| Information_schema | | lamp183 | | MySQL | | Performance_schema | | SYS |+--------------------+5 rows in Set (0.00 sec)//delete database lamp183mysql> drop db lamp183; Query OK, 1 row affected (0.01 sec)//delete database lamp183mysql> drop db lamp183; Query OK, 0 rows Affected (0.00 sec)//Create a database lmap175mysql>. lamp175; Query OK, 1 row Affected (0.00 sec)//view the current database [the database that is not being used even if it is created will not be queried to]mysql> Select Database (); +------------+| Database () |+------------+| NULL |+------------+1 row in Set (0.00 sec)//using LAMP175 database mysql> use lamp175;database changed//current database shows lamp175, indicating this The database is the current operation of the library mysql> Select Database (); +------------+| Database () |+------------+| lamp175 |+------------+1 row in Set (0.00 sec)//view lamp175 's Build statement/g can display mysql> show create database lamp175\g;******* vertically 1. Row ***********Database:lamp175create database:create Database ' lamp175 '/*!40100 DEFAULT CHARACTER SET latin1 * /1 row in Set (0.00 sec) error:no Query specified//View lamp175 Build Library statement mysql> show CREATE database lamp175;+----------+------- -------------------------------------------------------------+| Database | Create Database |+----------+------------------------------------------ --------------------------+| lamp175 | CREATE DATABASE ' lamp175 '/*!40100 DEFAULT CHARACTER SET latin1 */|+----------+--------------------------------------- -----------------------------+1 Row in Set (0.00 sec) mysql> CREATE database lamp183;         Query OK, 1 row Affected (0.00 sec) mysql> use lamp183;database changed//create lamp183 data table in Stu Database mysql> creation table Stu ( ID int, name varchar (+), sex varchar (2), age int, job varchar (32)-& Gt );    Query OK, 0 rows affected (0.05 sec) mysql> desc Stu; ViewTable structure of STU data table +-------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| ID | Int (11) |     YES | |       NULL | || name | varchar (32) |     YES | |       NULL | || sex | varchar (2) |     YES | |       NULL | || Age | Int (11) |     YES | |       NULL | || Job | varchar (32) |     YES | |       NULL | |+-------+-------------+------+-----+---------+-------+5 rows in Set (0.00 sec)//view STU statement [you can see the encoding type of the table, storage type]mysql > Show CREATE TABLE stu;+-------+------------------------------------------------------------------------------ --------------------------------------------------------------------------------------------------------------- --------------------------------+| Table |                                                                                                                                                                    Create Table                                            |+-------+-------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- ------------------------------------------+| Stu | CREATE TABLE ' stu ' (' id ' int (one) default null, ' name ' varchar (+) default null, ' Sex ' varchar (2) default NULL, ' age ' int (one) default null, ' job ' varchar (+) default null) Engine=myisam default charset=latin1 |+-------+------------------- --------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------+1 Row in Set (0.00 sec)    View the STU Table statement [stand Up to view]mysql> show create TABLE stu\g; 1. Row *************************** table:stucreate table:create Table ' stu ' (' id ' int (one) DEFAULT NULL, ' name ' var char (+) DEFAULT NULL, ' sex ' varcHar (2) default NULL, ' age ' int (one) default null, ' job ' varchar (+) default null) Engine=myisam default CHARSET=LATIN11 R ow in Set (0.00 sec) Error:no Query specified//View all tables in the current database mysql> show tables;+-------------------+| tables_in_lamp183 |+-------------------+| Stu |+-------------------+1 row in Set (0.00 sec)//delete Stu data table mysql> drop table Stu; Query OK, 0 rows Affected (0.00 sec)//Create a STU Data sheet mysql> CREATE TABLE stu (ID int, name varchar (32),- > Sex varchar (2), age int, job varchar (+)); Query OK, 0 rows affected (0.01 sec)//View STU Datasheet table structure mysql> desc stu;+-------+-------------+------+-----+---------+--- ----+| Field | Type | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| ID | Int (11) |     YES | |       NULL | || name | varchar (32) |     YES | |       NULL | || sex | varchar (2) |     YES | |       NULL | || Age | Int (11) |     YES | |   Null | || Job | varchar (32) |     YES | |       NULL | |+-------+-------------+------+-----+---------+-------+5 rows in Set (0.00 sec)//View all data Stu Data Sheet mysql> select * FROM Stu Empty Set (0.00 sec)//Insert data in STU Data sheet mysql> insert into Stu set id=9,name= ' Haiyang ', sex= ' W ', age=38,job= ' Toupai '; Query OK, 1 row Affected (0.00 sec)//View all data in Stu data sheet mysql> SELECT * from stu;+------+----------+------+------+--------- +| ID | name | sex | Age |    Job |+------+----------+------+------+---------+| 1 | Zhangsan |   W | 18 |    Laobao | | 2 | Lisi | m | NULL |    NULL | | 3 | Wangwu | W | NULL |    NULL | | 4 | Maliu | m | NULL |    NULL | | 5 | Zhaoqi | W | NULL |    NULL | | 6 | Wangba |   m | 20 |    Baoan | | 7 | Haibo |   W | 28 |    Guigong | | 8 | Hongze |   m | 17 |    Student | | 9 | Haiyang |   W | 38 | Toupai |+------+----------+------+------+---------+9 rows in Set (0.00 sec)//exit MySQL database mysql> \q

Basic MySQL concepts and simple operations