MySQL Database Study Notes & lt; 1 & gt;, mysql Database Study Notes

MySQL Database Study Notes <1>: mysql Database Study Notes

 

Basic concepts and simple operations of MySQL  I. MySQLMySQL is a relational database management system developed by MySQL AB in Sweden. It is currently a product of Oracle. MySQL is one of the most popular Relational Database Management systems. In terms of WEB applications, MySQL is the best RDBMS (Relational Database Management System) application software. MySQL stores data in different tables rather than in a large warehouse, which increases the speed and flexibility. The SQL language used by MySQL is the most common standard language for accessing databases. MySQL adopts the dual Authorization Policy, which is divided into community edition and commercial edition. because of its small size, fast speed, and low total cost of ownership, especially open source code, generally, MySQL is used as the website database for the development of small and medium websites. Due to its superior performance, the Community edition works with PHP and Apache to form a good development environment. 2. Comparison between MySQL and other large databases
For example, compared with Oracle, DB2, and SQL Server, MySQL has its own shortcomings, but this does not reduce its popularity. For individual users and small and medium-sized enterprises, MySQL provides more than enough functions, and because MySQL is open source software, it can greatly reduce the total cost of ownership. Linux as the operating system, Apache or Nginx as the Web server, MySQL as the database, PHP/Perl/Python as the server-side script interpreter. Since these four Software are both Free or Open Source Software (FLOSS: Free/Libre and Open Source Software ), therefore, in addition to manual costs, a stable and free website system can be established, also known as the "LAMP" or "LNMP" combination. Iii. Importance of databases in WebTo make static HTML pages active and Display Dynamic pages, you need to use the Apache server-side PHP script programming language to process data using the database to achieve dynamic, so while learning PHP, it is necessary to master the basic operations of the MySQL database.

Iv. Common Database Systems

MySQL, DB2, Oracle, SQL Server, MariaDB, and so on. Because MySQL is an Oracle product, it may be shut down from the source and no longer free of charge, but the MariaDB database management system is a branch of MySQL, it is mainly maintained by the open-source community. MariaDB is fully compatible with MySQL, including APIs and command lines, so that it can easily become a substitute for MySQL. In terms of storage engine, MariaDB replaces MyISAM of MySQL based on the transaction-based Maria storage engine, and uses XtraDB to replace InnoDB of MySQL.

V. Database types

1. Relational databases

2. Non-relational databases

Vi. Structured SQL Query Language

You must connect to our database through the server to perform operations.

Use various SQL statements, add, delete, modify, and query

　1. Data Definition Language (DDL ):

Defines and manages data objects, including databases and data tables. For example, CREATE, DROP, and ALTER.

Database> data table: creates, deletes, and modifies a database or data table.

　2. Data Operation Language (DML ):

Used to manipulate data contained in database objects. For example, INSERT, UPDATE, and DELETE statements.

Adding, deleting, and modifying data are data operation languages.

　　3. Data Query Language (DQL ):

This interface is used to query the data contained in database objects. It can be used to query a single table,

Connection query, nested query, set query, and other database queries with different levels of complexity, and

Return to the client. SELECT

　　4. Data Control Language (DCL ):

Is the language used to manage databases, including management permissions and data changes.

7. Operate MySQL in the DOC System

As I am studying for myself, the installation environment is completed in the windows operating system, that is, the integration package of windows + Apache + PHP + MySQL. The following is simply called "wamp ". To operate MySQL in the DOC System

Some configurations

　　1> Environment Variables

My computer-> right-click Properties-> advanced system settings-> environment variables-> system variables-> path

Add the; variable value after the path variable

The variable value is the bin directory path under MySQL.

Note: You must enable the wamp Server before entering.

2>Enter the MySQL database

Command for Screen Cleaning in the DOS system: cls

Mysql-h localhost-u root-p-B

-H server (local, specified server IP address) [local localhost can be omitted]
-U User Name (we use the root user: Super administrator)
-P User Password (password is not required after it is entered, and password is written after press Enter)
-B buzzer (the sound prompted when an error is reported when the command is incorrect)

3>Commands after entering the MySQL database

Note:
1> after each line of commands is completed, use; or \ g. Otherwise, the command can be written continuously.
2> In the MySQL command, if a line of command is not completed, press enter and it will continue to be executed, provided that it must be a complete command.
3> Use \ c or Ctrl + c to exit the wrong command or command that you do not want to continue running.
4> if we want to make the data stand up and display, we can use \ G after the command. Sometimes, if a row fails to run, it will be messy. \ G can solve this problem.
5> \ s to display the current server configuration
6> help to view all the shortcut commands available
7> if you encounter single quotes in the command line, MySQL considers that you want to enter a string. Therefore, it must use ending single quotes to exit the string input state;

　4>Log out of the Database Server
\ Q, exit, and quit are all supported.

Note:

To store all the commands entered under the doscommand line to a document, you need to use the tee command.

Tee path;

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

8. database operation commands

　　　　1. Create a database

Create database name;

Note:
1. If the database already exists, you cannot create a database with the same name.
2. Create commands and database names are case insensitive.

If not exists if the database already exists, adding the previous keyword can avoid an error.

　　　2. delete a database

Drop database name;

　　　3. view the created database

Show databases;

　　　　4. Use a database

Use Database Name;

　　　　5. view the database currently in use

Select database ();

　　　6. view the database creation statement

Name of the show create database;

Note: Through this command, we can see the database creation statements and encoding types created for the database;

Note:
1. commands in the MySQL database are case-insensitive.

2. Each time a database is created, a folder named after this database is created under the data Directory.

3. In Windows, database names are case-insensitive, but in Linux, database names are case-insensitive.

　　　7. Operations on Data Tables

The fields in the table can be understood as the table header.

　　　　　　　　　　1> Create a data table (create a simple data table without any primary key information, auto-increment, and data restrictions)> available for the first exercise
Create table Name (
Field name 1 field type,
Field name 2 field type,
Field name 3 Field Type
);

Note:
1. When creating a data table, each of its fields is separated by commas;
2. The last field does not contain commas ",";
3. After the table is created, end the brackets with a semicolon.
4. When creating a table, write the field name (field type) of the table in brackets after the table name)

　　　　　　　　　　2> View the table structure

Desc table name;

　　　　　　　　　3> View table creation statements

Show create table name;

　　　　　　　　　　4> View existing data tables of the current database

Show tables;

　　　　　　　　　　5> delete a data table

Drop table Name

　　　8. Data Operations

　　　　　　　　　　1> add 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 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 ...;

Note:
1. The values and field names must correspond one to one, otherwise an error is reported.
2. the value you write must match the data type.

　　　　　　　　　2> delete data

Delete from table name where condition;

Note: When deleting data, you must add the where condition. Otherwise, all data will be deleted.

　　　　　　　　　　3> modify data

Update table name set field to be modified = modified value where Condition

Note: When modifying data, you must add the where condition. Otherwise, all data will be modified.

　　　　　　　　　4> Search for Data

Select * (all fields) from table;

Select Field 1, Field 2... from table;

 

The following is a simple database operation example:

<? Php // simple database operation method // display all databases mysql> show databases; + -------------------- + | Database | + -------------------- + | information_schema | lamp183 | mysql | performance_schema | sys | + -------------------- + 5 rows in set (0.00 sec) // Delete the database lamp183mysql> drop database lamp183; Query OK, 1 row affected (0.01 sec) // Delete the database lamp183mysql> drop database lamp183; Query OK, 0 rows affected (0.00 sec) // create a database lmap175mysql> Create database lamp175; Query OK, 1 row affected( 0.00 sec) // view the current database [the database is not used, even if it is created, it will not be queried] mysql> select database (); + ------------ + | database () | + ------------ + | NULL | + ------------ + 1 row in set (0.00 sec) // use the lamp175 Database mysql> use lamp175; Database changed // The current Database displays the lamp175, this database is the current database mysql> select database (); + ------------ + | database () | + ------------ + | lamp175 | + ------------ + 1 row in set (0.00 sec) // View the lamp175 database creation statement/G to 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 the lamp175 database creation statement mysql> show create database lamp175; + ---------- + databases + | Database | Create Database | + ---------- + ---------------------------------------------------------------- + | lamp175 | create database 'lamp175 '/*! 40100 default character set latin1 */| + ---------- + rows + 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 an stu data table in the lamp183 Database mysql> create table stu (-> id int,-> name varchar (32 ), -> sex varchar (2),-> age int,-> job varchar (32)->); Query OK, 0 rows affected (0.05 sec) mysql> desc stu; // view the table structure of the 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 the stu table creation statement [You can view the encoding type and storage type of the table] mysql> show create table stu; + ------- + hour + | Table | Create Table | + ------- + hour + | stu | create table 'stu' ('id' int (11) default null, 'name' varchar (32) default null, 'sex' varchar (2) default null, 'age' int (11) default null, 'job' varchar (32) default null) ENGINE = MyISAM default charset = latin1 | + ------- + rows + 1 row in set (0.00 sec) // view the stu table creation statement [vertical view] mysql> show create table stu \ G; * *************************** 1. row *************************** Table: stuCreate Table: create table 'std' ('id' int (11) default null, 'name' varchar (32) default null, 'sex' varchar (2) default null, 'age' int (11) default null, 'job' varchar (32) default null) ENGINE = MyISAM default charset = latin11 row in set (0.00 sec) ERROR: no query specified // view all tables in the current database mysql> show tables; + --------------------- + | tables | + --------------------- + | stu | + ----------------- + 1 row in set (0.00 sec) // Delete the stu data table mysql> drop table stu; Query OK, 0 rows affected (0.00 sec) // create the stu data table mysql> create table stu (-> id int, -> name varchar (32),-> sex varchar (2),-> age int,-> job varchar (32)->); Query OK, 0 rows affected (0.01 sec) // view the table structure of the stu data table 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 in the stu data table mysql> select * from stu; Empty set (0.00 sec) // insert data into the stu data table 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 the stu data table mysql> select * from stu; + ------ + ---------- + ------ + --------- + | id | name | sex | age | job | + ------ + ---------- + ------ + --------- + | 1 | zhangsan | w | 18 | laobao | 2 | lisi | m | NULL | 3 | wangwu | w | NULL | 4 | maliu | m | NULL | 5 | zhaoqi | w | 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