"Database" MySQL database (i)

First, MySQL database system

MySQL database system is used to the database, some of the data management

Second, the database system

1. Database
is used to store a variety of data

2. Database management System
is a system used to manage data from various databases.

Third, some common database system

MySQL, DB2, Oracle, SQL Server, maradb ...

Iv. Database

1. Relational database

2. Non-relational database

V. the importance of databases in the Web

We now see the page, basically no pure HTML page, are from the database inside

Vi. Why MySQL is chosen as a golden partner for PHP

1. Open Source Free

2. Easy and quick operation

3. More suitable for small and medium sized enterprises

Vii. Structured Query Language of SQL

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.

What do you do with the increase, deletion, and modification of data, the data manipulation language

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.

5.MySQL actually belongs to a C/s software
MySQL belongs to a client application
MySQL belongs to the database management system

It must have a client

We want to operate MySQL under the DOS system

Some of the configuration that needs to be done before entering MySQL under a DOS system

1> Environment variables

My Computer, right-click Properties, advanced system settings, environment variables, new
|
Variable value: The bin directory path under MySQL <-variable name: path

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

6. Go to MySQL Database

Command for clear screen under DOS system: CLS

Mysql-h Localhost-u Root-p-B

MySQL Database
-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)

7. Commands to enter 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
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 can solve this 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
You must use the closing single quotation mark to jump out of the string input state;

8. Exit the database server
\q
Exit
Quit

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\lamp175\lesson25_MySQL\1.php

9. Operation of some of the database commands

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 statement, encoding type;

Attention:
The commands in the 1.MySQL database are case insensitive.
2. The name of the database is also 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.

Tee command: You can export the practice statements in MySQL to an external file
How to use: Tee E:\wamp\www\lamp171\lesson23_MySQL01\test.txt

10. Operation of the data table

Note: The field in the table is actually a table header

1> Creating a data table
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

11. 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;

"Database" MySQL database (i)