Hello everyone, I am Nezha, not to say more, today is a review of the MySQL database (relational database management system) essential article, welcome to enjoy.Copy the code

preface

MySQL is a relational database management system, in the web application, MySQL is one of the best relational database management system applications.

MySQL is a relational database management system. Relational databases keep data in different tables instead of putting all data in a large warehouse, which increases speed and flexibility.

The SQL language used by MySQL is the most commonly used standardized language for accessing databases. Because of its small size, fast speed, low total cost of ownership and open source, MySQL is generally selected as the website database for the development of small and medium-sized websites.

Application environment

Compared with other large databases such as Oracle, DB2, SQL Server, etc., MySQL has its own shortcomings, but this does not reduce its popularity. For ordinary individual users and small and medium-sized enterprises, MySQL provides more than enough functions.

Linux as the operating system, Apache or Nginx as the Web server, MySQL as the database, and PHP/Perl/Python as the server-side script interpreter.

Application architecture

Single, suitable for small-scale application Replication, suitable for small – and medium-sized application Cluster, suitable for large-scale application

The index function

In theory, it is possible to create an index for each field in a table, but MySQL limits the total number of indexes in a table to 16.

  1. InnoDBThe index of a data table
  2. limit

The index categories

  1. Plain index, plain index (by keywordKEY 或 INDEXThe task of index definition is to speed up access to data.
  2. Index. Normal indexes allow indexed columns to contain duplicate values.
  3. Primary index, you must create an index for the primary key field, which is called a “primary index.”
  4. If a foreign key constraint is defined for a foreign key field,MySQLYou define an internal index to help you manage and use foreign key constraints in the most efficient way.
  5. Composite indexes, which can cover multiple data columns, such asINDEX (columnA, columnB) index. The characteristics of this kind of index areMySQLYou can optionally use one of these indexes.

Beginners basis

MySQL > connect to MySQL

Mysql -h host address -u user name -p user passwordCopy the code

Connect to mysqlbin, open DOS window, go to mysqlbin, type mysql-uroot -p, press Enter to prompt you to enter password.

Connect to MySQL on remote host, IP address of remote host, user name root, password

Mysql -h remote host IP address -uroot -p password

Mysql name: exit Press Enter, but note that to successfully connect to the remote host, you need to enable mysql remote access on the remote host.

The difference between mysql and SQLite

Mysql is the most widely used database server in the Web world. Mysql is a database designed for the server side and can withstand high concurrent access while occupying much more memory than SQLite.

SQLite is lightweight and embeddable, but cannot withstand high concurrent access, making it suitable for desktop and mobile applications.

Change the database default encoding to UTF-8

CNF /etc/ mysql.cnf /etc/ mysql.cnf /etc/ mysql.cnf

The code is as follows:

[client]
default-character-set = utf8

[mysqld]
default-storage-engine = INNODB
character-set-server = utf8
collation-server = utf8_general_ci
Copy the code

After mysql restarts, you can check the encoding on the mysql client command line.

$ mysql -u root -p

Review the necessary

MySQL database management methods, master database initialization, create, view, and delete methods. Learn the data table management method, master the data table creation, view, modify and delete methods.

The management of user account, the creation and deletion of user, the granting and recovery of user authority, the setting and change of user password.

Catalog to review

In order to facilitate my study, I made a mind map, as shown below:

Mysql Database Management

The database is initialized

Initialize the mysql database. After installing the mysql database, do not start it directly.

Initialization process

  1. Create metadata tables
  2. The data directory
  3. Creating user root

Mysql provides database commands:

Mysql –initialize performs initialization

  • According to thewindows+rKey combination, you can open the “Run” window, in the window inputcmdAccording to theenterKey to open the command line.
  • usecdCommand to entermysqlDirectory.
  • performbin\mysqld --initializeCommand to initialize the system.

After initialization, you can start the database in either of the following ways.

  1. usingwindowsStart server management interface;
  2. usingmysqlCommand start.

Start database with mysql command:

  1. According to thewindows+rKey combination, open the window, enter the command line;
  2. usecdCommand to entermysqlDirectory;
  3. The inputbin\mysqld, startmysqlService.

Start the database

Run the bin\mysql -u root -p command to enter the mysql operating environment.

Service picture, open database:

Creating a database

Let’s create a database, the CREATE DATABASE statement.

The syntax is as follows:

create database [db_name];

Create a database named Web:

create database web;

After the database is created, a directory named Web is automatically generated in the data directory of the mysql database and the data of the database is stored in this directory.

Mysql supports running multiple databases, so we can create multiple databases.

Viewing a Database

After the database is created, use the show statement to check which databases are currently in mysql.

show databases;

Deleting a Database

To drop a database, use the drop statement in the following syntax:

drop database db_name;

To delete the created Web database, run the following command:

drop database web

Deleting a database is an irreversible operation.

Mysql database management

To create a data table, use the create TABLE statement with the following syntax.

User Database name; Create table table name (field name type (length), field name type (length));Copy the code

Create a table sentence:

  1. Define the structure of the data table;
  2. The name of the field;
  3. Type;
  4. Length, etc.

View tables

View table points:

  1. See which tables are contained in the database
  2. View the concrete structure of a table

The statements used are the show statement and describe statement.

  • useshowStatement to see which tables are in the database.

show tables

  • usedescribeStatement to view the concrete structure of a table and the information about the fields that make up the table.

Go in the library. The name of the table.

You can use the describe statement to view information such as the name, type, length, non-empty, primary key, default value, and remarks of each field in the table.

Modify table

You can modify table structures such as table names, field names, and field types by using the ALTER statement.

Modify the name of the table

Alter table name alter table name

Alter table old table rename new table name;

Alter table student to student1;

alter table student rename student1

Changing the field name

The syntax format for modifying field names is as follows:

Alter table alter table name change old attribute name new attribute name new data type;

alter table student1 change name s_anme varchar(50);

Changing the field Type

Modify the syntax format of the field type:

Alter table table name modify attribute name Data type;

alter table student1 modify name varchar(32);

Increase the field

The syntax format for adding a field is as follows:

Alter table name add attribute name data type;

alter table student1 add sex char(1);

Delete the field

Delete field statement:

Alter table drop drop name;

Example:

alter table student1 drop sex;

Delete table

Drop a table from a database using the DROP statement.

Use database name; Drop the name of the table;Copy the code

Or is it

Drop Table Specifies the database name. The name of the table.Copy the code

Delete a table from a table:

use test;
drop table student;
Copy the code

Or is it

drop table test.student;
Copy the code

Mysql User Management

Mysql provides a complete database user and permission management system.

Create and delete users

  1. Create a user

Create a user using the create statement:

create user 'username'@'host' idendified by 'password';

Username indicates the name of the created user, and host specifies the host on which the user can log in.

create user 'test1'@'localhost' idendified by '1234';

create user 'test3'@'122.xxx' idendified by '1234';

The user to delete

To drop a user, use the drop statement.

drop user 'username'@'host';

The following is an example:

drop user 'test1'@'localhost';

Grant and revoke user rights

Grant user permissions

The grant statement authorizes a user:

grant privileges on dbname.tablename TO 'username'@'host';

  1. privilegesIndicates the operation rights to be granted to the user.

grant select, insert on mysql.test TO 'test1'@'%';

Indicates that authorized user test1 has select and insert permissions on the test table of the mysql database on all hosts.

grant all on *.* TO 'test2'@'localhost';

Indicates that authorized user test2 has all permissions on all tables in all libraries of the local host database.

Revoking User Rights

Revoking permissions can be done with the REVOKE statement.

revoke privileges on databasename.tablename from 'username'@'host';

Example:

revoke select on *.* from 'test2'@'localhost';

Set and change user passwords

Mysql > change password (s);

set password for 'username'@'host' = password('newpassword');

  1. usernameRepresents the user name for which the password is to be set or changed;
  2. hostSpecify the host to which the user logs in.
  3. newpasswordRepresents a password to set or change.

Example:

set password for 'test1'@'localhost' = password('12345');

What is a database

A database is a warehouse that organizes, stores and manages data according to data structures. Each database has one or more different apis for creating, accessing, managing, searching, and copying stored data.

Data is stored in files, but reading and writing data in files is relatively slow.

The term

  1. A database is a collection of associated tables.
  2. A data table is a matrix of data.
  3. Columns, a column containing the same type of data.
  4. Row, row is a set of related data.
  5. Redundancy: Stores twice as much data. Redundancy reduces performance but improves data security.
  6. The primary key, which is unique, can only contain one primary key in a table.
  7. A foreign key used to associate two tables.
  8. Compound key, which uses multiple columns as a single index key, commonly used for compound indexes.
  9. Index, which allows quick access to specific information in a database.
  10. Referential integrity, the integrity of a reference requires that a relationship does not allow references to nonexistent entities.

A relational database consists of one or more tables: table headers, rows, columns, keys, and values.

The table header is the name of each column, the column is a collection of data of the same data type, the behavior of each row is used to describe the specific information of a record, the value is the specific information of the row, each value must be the same as the data type of the column, and the key value is unique in the current column.

MySQL download address is: MySQL download https://dev.mysql.com/downloads/mysql/

Install MySQL https://dev.mysql.com/downloads/repo/yum/

After Mysql is installed, the default password of user root is empty. You can run the following command to create a password for user root:

[root@host]# mysqladmin -u root password "new_password";

Log on to the Mysql

Mysql -h host name -u user name -p

Run the mysql service:

mysql -h localhost -u root -p

MySQL, PHP syntax

The PHP Mysqli function has the following format:

mysqli_function(value,value,...) ;

mysqli_connect($connect); Mysqli_query ($connect, "SQL statements"); mysqli_fetch_array() mysqli_close()Copy the code

Connect to MySQL using PHP script

The mysqli_connect() function connects to the database

Grammar:

mysqli_connect(host,username,password,dbname,port,socket);

Parameter Description:

  1. hostFor the host oripAddress;
  2. usernameformysqlThe user name;
  3. passwordformysqlPassword;
  4. dbnameIs the default database;
  5. portTry to connect tomysqlPort number of the server;

Grammar:

bool mysqli_close ( mysqli $link )

Connect to mysql server:

<? php $dbhost = 'localhost'; $dbuser = 'root'; $dbpass = '123456'; $conn = mysqli_connect($dbuser, $dbpass); if(! $conn ) { die('Could not connect: ' . mysqli_error()); } echo 'connect to database successfully! '; mysqli_close($conn); ? >Copy the code

Mysql > create database

Create a database using the create command.

Create database Specifies the database name.

Use PHP scripts to create databases

Grammar: mysqli_query (connection, query, resultmode);

  1. connectionFor usemysqlThe connection;
  2. queryIs a query string;
  3. resultmodeA constant, a valueMYSQLI_USE_RESULTandMYSQLI_STORE_RESULT.

Use PHP to create a database

Code:

<? php $dbhost = 'localhost'; $dbuser = 'root'; $dbpass = '123456'; $conn = mysqli_connect($dbuser, $dbpass); if(! $conn) {die(' connection error: '.mysqli_error ($conn)); } echo 'connect successfully <br />' $sql = 'create database web'; $retval = mysqli_query($conn,$sql ); if(! $retval) {die(' failed to create database: '.mysqli_error ($conn)); } echo "create database successfully \n"; mysqli_close($conn); ? >Copy the code

The drop command deletes a database

Format of the drop command:

Drop database < database name >;

Delete the database using the PHP script

grammar

mysqli_query(connection,query,resultmode);

Mysql > delete database from mysqli_query;

Delete database:

<? php $dbhost = 'localhost:3306'; $dbuser = 'root'; $dbpass = '123456'; $conn = mysqli_connect($dbuser, $dbpass); if(! $conn) {die(' connection failed: '. Mysqli_error ($conn)); } echo 'connect successfully <br />' $sql = 'DROP DATABASE web'; $retval = mysqli_query( $conn, $sql ); if(! $retval) {die(' delete database failed: '.mysqli_error ($conn)); } echo "delete database successfully \n"; mysqli_close($conn); ? >Copy the code

Select MySQL database using PHP script

Use the function mysqli_select_DB to get a database

Grammar:

mysqli_select_db(connection,dbname);

Example:

Mysqli_select_db function to select a database:

Select database

<? php $dbhost = 'localhost:3306'; $dbuser = 'root'; $dbpass = '123456'; $conn = mysqli_connect($dbuser, $dbpass); if(! $conn) {die(' connection failed: '. Mysqli_error ($conn)); } echo 'connect successfully '; mysqli_select_db($conn, 'web' ); mysqli_close($conn); ? >Copy the code

MySQL data type

Mysql supports multiple types, divided into three categories:

  1. Numerical value;
  2. Date/time;
  3. The value is a string.

MySQL 5.0 or later

1. The length of a Chinese character is related to the code:

Utf-8: One Kanji = 3 bytes

GBK: One Chinese character = 2 bytes

2, varchar(n) indicates n characters, whether Chinese or English, Mysql can store N characters, only the actual byte length is different

3, MySQL check the length, available SQL language to check

MySQL creates tables

  1. The name of the table
  2. Watch the field name
  3. Define each table field

Grammar:

SQL syntax for creating mysql tables

CREATE TABLE table_name (column_name column_type);

Create table in database:

create table if not exists `table_tb` (
    `table_id` int unsigned auto_increment,
    `table_title` varchar(100) not null,
    `table_author` varchar(40) not null,
    `table_date` date,
    primary key (`table_id`)
)engine=InnoDB default charset = utf8;
Copy the code

Note: Auto_increment defines a column as an incremented attribute, typically for primary keys, and automatically increments by 1. Engine sets the storage engine, and charset sets the encoding.

Create table

Code:

<? php $dbhost = 'localhost:3306'; $dbuser = 'root'; $dbpass = '123456'; $conn = mysqli_connect($dbuser, $dbpass); if(! $conn) {die(' connection failed: '. Mysqli_error ($conn)); } echo 'connect successfully <br />' $sql = "create table table_tbl( ". "table_id int not null auto_increment, ". "table_title varchar(100) not null, ". "table_author varchar(40) not null, ". "submission_date DATE, ". "primary key ( runoob_id ))ENGINE=InnoDB DEFAULT CHARSET=utf8; "; mysqli_select_db( $conn, 'RUNOOB' ); $retval = mysqli_query( $conn, $sql ); if(! $retval) {die(' dataflow failed to create: '. Mysqli_error ($conn)); } echo "create table \n"; mysqli_close($conn); ? >Copy the code

MySQL field attributes should be set to NOT NULL as far as possible

First, consider the concept of null values “” and null:

  1. Null values take up no space
  2. mysqlIn thenullIt actually takes up space

MySQL > delete from database

Mysql > alter table dataflow;

drop table table_name;

Delete the data table using the PHP script

Grammar:

mysqli_query(connection,query,resultmode);

Delete table with PHP script:

<? php $dbhost = 'localhost:3306'; $dbuser = 'root'; $dbpass = '123456'; $conn = mysqli_connect($dbuser, $dbpass); if(! $conn) {die(' connection failed: '. Mysqli_error ($conn)); } echo 'connect successfully <br />' $sql = "drop table table_tb1"; mysqli_select_db( $conn, 'web' ); $retval = mysqli_query( $conn, $sql ); if(! $retval) {die(' mysqli_error($conn)); } echo "select * from db; mysqli_close($conn); ? >Copy the code

MySQL > insert data into database

Insert into; insert into;

insert into table_name(field1,field2,... fieldN) values (value1,value2,... valueN);Copy the code

Add data

Code:

<? php $dbhost = 'localhost:3306'; $dbuser = 'root'; $dbpass = '123456'; $conn = mysqli_connect($dbuser, $dbpass); if(! $conn) {die(' connection failed: '. Mysqli_error ($conn)); } echo 'connect successfully <br />' Mysqli_query ($conn, "set names utf8"); $table_title = 'learn '; $table_author = 'web'; $submission_date = '2018-03-06'; $sql = "insert into table_tbl ". "(table_title,table_author, submission_date) ". "values ". "('$table_title','$table_author','$submission_date')"; mysqli_select_db( $conn, 'web' ); $retval = mysqli_query( $conn, $sql ); if(! $retval) {die(' cannot insert data: '.mysqli_error ($conn)); } echo "insert data successfully \n"; mysqli_close($conn); ? >Copy the code

INSERT Inserts multiple pieces of data

INSERT INTO table_name (field1, field2,... fieldN) VALUES (valueA1,valueA2,... valueAN),(valueB1,valueB2,... valueBN),(valueC1,valueC2,... valueCN)...... ;

SQL Basic Syntax

Learn SQL statements and mysql basic data types, learn to master data insert, modify, delete SQL statements, query statements, and all kinds of specific query statements.

Learn the concepts, features and usage of database transactions.

The SQL directory also collates a mind map:

Introduction to SQL Statements

Structured Query Language (SQL) is a database query and programming language used to access data and query, update and manage relational database systems.

SQL statements are a language for operating on a database.

There are three types of SQL:

  1. DDLStatement, data definition statement, defines different database, table, column, index and other database objects.
  2. DMLStatement, data manipulation statement, used to add, delete, update, and query database records, and check data integrity.
  3. DCLStatement, data control statement, define database, tables, fields, user access permissions and security levels.

Mysql basic data type

Mysql provides multiple data types, including numeric types, string types, and date and time types.

  1. Numeric types
  2. String type
  3. Date and time types

Numeric types

Mysql > select * from ‘mysql’;

  1. Integer types
  2. Floating point type
  3. Fixed-point number type

Integer type:

  1. tinyint
  2. small int
  3. medium int
  4. int
  5. big int

Floating point types:

  1. float
  2. double

Fixed-point number types:

  1. decimal

Integer types are divided according to the size of the storage space and the size of the representation range

Floating-point types are a way of representing real numbers. There are single-precision floating-point types (8-bit precision) and double-precision floating-point types (16-bit precision), based on the number of digits and precision.

String type

Multiple string types are provided:

  1. char
  2. varchar
  3. binary
  4. varbinary
  5. blob
  6. text
  7. enum
  8. set

Char and varchar

  1. charDefines a fixed length string
  2. varcharDefines a variable length string

The length of char is fixed to the length declared when the table is created. The value ranges from 0 to 255. When saving the char value, space is filled to the right to reach the specified length.

When a char value is retrieved, the trailing whitespace is removed, so when stored, there is normally no whitespace to the right of the string. If there is a space to the right of the string, it will be deleted after the query.

The length of vARCHar can be specified from 0 to 6535. The vARCHar value saves only the desired string, with an additional byte added to record the length.

binarywithvarbinary

Binary and varbinary are used to store binary strings. There is no character set, and numeric values are sorted and compared based on column value bytes.

The text with a blob

Text and blob are text and binary stored as object types.

Text is treated as a sufficiently large VARCHar and blob as a sufficiently large varbinary, but text and BLOb differ from varchar and varbinary in some ways:

  1. When saved or retrievedblobandtextDoes not remove trailing Spaces.
  2. In comparison, pairs of Spaces are usedtextExpand to fit the object of comparison.
  3. forblobandtextThe length of the index prefix must be specified.
  4. blobandtextThere cannot be default values.

The maximum string length of a chess text and blob object is determined by its type.

Text is divided into tinytext,text,mediumtext and longtext.

Blogs are divided into tinyblob, blob, mediumblob and longblob.

enum

Enum Indicates the enumeration type. Its value range must be explicitly specified during table creation. Enum is case insensitive. Enum allows you to select a single value from a set of values, not multiple values at a time.

set

A set is a collection object that can contain anywhere from 0 to 64 members. The size of its storage space varies depending on the number of members in the collection.

Date and event type

Multiple date and time types are provided:

  1. year
  2. time
  3. date
  4. datetime
  5. timestamp

Date format, year, YYYY; Time, HH: MM: SS. The date – DD YYYY – MM; Datetime, YYYY-MM-DD HH:MM:SS, timestamp, YYYY-MM-DD HH:MM:SS.

Insert data into

Insert into values (1, 2,...) ;

Insert into table name (1, 2,...) Values (1, 2);

In mysql, the syntax of the insert statement is as follows:

Insert into table name (1, 2,...) Values (1, 2...) , (value 1, value 2...) . ;

Data modification

Using the update command:

Update the table name set column name = new value whert column name = a certain value;

Update table 1, table 2,... Set table 1. 表2. 表2. Where conditions;

To delete data

This can be done using the delete command:

Delete from table_name where table_name = table_name;

The delete command can delete more than one table at a time:

Delete table 1, Table 2... From table 1, table 2... Where conditions;

Data query

The basic syntax for a SELECT statement is:

Select * from table_name where table_name = 1;

Conditions of the query

Conditional query statement:

Select * from table_name where table_name = 1;

The joint query

The union operator

The union operator is used to combine the result sets of two or more SELECT statements.

Code:

Select * from table 1 union select * from table 2;Copy the code

unionandunion allThe main difference between

  1. union allUse to merge result sets directly together.
  2. unionIs used tounion allThe post deconstruction is done oncedistinctTo delete duplicate result records.

Non-repeat query

The syntax is as follows:

Select distinct from table name;

Fuzzy query

Syntax format:

Select * from table_name where table_name = 1;

%, used to match zero or more characters, can match any type and length of characters, there is no length limit.

The use of “_”, used to match any single character, is often used to limit the length of an expression.

Sorting query

Use the order by keyword to sort:

Order by field1 desc/asc, field2 desc/ asC,... ;

Sorting mode: DESC indicates descending order, and ASC indicates ascending order. The default value is ASC.

Order by can be followed by a number of different sort fields.

Limit the query

Limit query using the limit keyword.

Select * from (select * from (select * from (select * from)));

The aggregation

Aggregate statement format:

Select op_name from tablename where tablename group by filed1, filed2,... With rollup having condition;

Op_name indicates the aggregation operation to be performed, which is the aggregation function.

Aggregate function:

  1. sumFunction sum
  2. countNumber of function records
  3. maxMaximum of function
  4. minFunction minimum
  5. groupbyRepresents the field to be aggregated by category
  6. with rollupIndicates whether the results of classification aggregation are summarized
  7. havingIndicates conditional filtering of the result after classification

The connection

In the connection

An inline join query means that the results of all queries can have corresponding records in the joined table. By default, it is an inner join. You can either omit the JOIN keyword or write an inner Join.

There are three types of join:

  1. inner join: Gets the record of the field matching relationship between two tables.
  2. left join: Retrieves all records in the left table, even if there is no matching record in the right table.
  3. right join: Used to obtain all records in the right table, even if there is no matching record in the left table.

Left connect and right connect

The left join refers to matching the data of the right table against the data of the left table.

  1. If the corresponding data is matched, the matching result is displayed
  2. If the corresponding data cannot be matched, null is displayed

Left join keyword: left JOIN. Right join keyword: right join.

Connect right and vice versa.

The transaction

Overview of a transaction: a transaction, usually something to be done or done. In computer terms, a unit of program execution that accesses and possibly updates various data items in a database.

A transaction consists of all the operations that are performed between the start and end of a transaction.

A transaction is a set of business logic composed of SQL statements. A transaction succeeds only when all SQL statements in the transaction are successfully executed. Otherwise, the transaction fails.

Four characteristics of transactions

  1. atomic
  2. consistency
  3. Isolation,
  4. persistence

Transaction commit

By default, SQL statements are automatically committed. After each SQL statement is executed, transactions are automatically committed. To commit transactions in a unified manner, you need to disable the automatic commit function of mysql first.

Check whether automatic commit is enabled for the database:

show variables like 'autocommit';

Use the following command to turn off autocommit:

set autocommit=0;

Command to commit a transaction manually:

commit

Transaction rollback

The result of a successfully executed statement in a transaction should be rolled back to an unexecuted state, called a transaction rollback.

Transaction rollback:

rollback

Transaction isolation level

Concurrent reads of transactions

  1. Dirty read: reads uncommitted data from another transaction;
  2. Non-repeatable read: two reads are inconsistent;
  3. Phantom read (virtual read) : Read the committed data of another transaction.

Start the transaction

A transaction begins with a begin transaction:

Format:

The begin transaction < business name > | @ variable name > < affairs

The syntax is as follows:

@< transaction variable name > A user-defined variable that must be declared using the char, vARCHAR, NCHAR or nvARCHAR data types.

Begin Transaction statement execution.

Commit the transaction

Commit commits the transaction, that is, all operations of the transaction.

Cancel the transaction

Rollback Rollback Rollback rollback Rollback Rollback Rollback Rollback Rollback Rollback Rollback Rollback Rollback Rollback rollback rollback rollback rollback rollback rollback

Syntax format:

rollback[transaction]

MySQL regular expression

  1. ^Matches the start of the input string.
  2. $Matches the end of the input string.
  3. [...].Collection of characters. Matches any of the contained characters.
  4. [^...].A collection of negative characters. Matches any character that is not contained.
  5. *Matches the preceding subexpression zero or more times.
  6. +Matches the previous subexpression one or more times.
  7. {n}N is a non-negative integer. Match certain n times.
  8. {n,m}Both m and n are non-negative integers, where n <= m. At least n times and at most m times are matched.

The SQL statement

The SQL statement:

select lastname from persons

  1. selectGet data from a database table
  2. updateUpdate data in a database table
  3. deleteDeletes data from a database table
  4. insert intoInsert data into that database table

DDL statements

  • create databaseCreating a new database
  • alter databaseModifying a Database
  • create tableCreate a new table
  • drop tableDelete table
  • alter tableAlter database table
  • create indexCreate indexes
  • drop indexRemove the index

statements

The SELECT statement is used to SELECT data from a table.

SELECT column name FROM table name

SELECT * FROM table name

SELECT LastName,FirstName FROM Persons

SQL SELECT DISTINCT statements

In a table, duplicate values may be contained. The keyword DISTINCT is used to return a unique DISTINCT value.

Grammar:

SELECT DISTINCT column name FROM table name

The WHERE clause is used to specify the criteria for selection.

SELECT column name FROM table name WHERE column operator value

The operator describe
= Is equal to the
<> Is not equal to
> Is greater than
< Less than
> = Greater than or equal to
< = Less than or equal to
BETWEEN Within a certain range
LIKE Search for a pattern

If both the first condition AND the second condition are true, the AND operator displays a record.

The OR operator displays a record if only one of the first and second conditions is true.

The ORDER BY statement is used to sort the result set.

The INSERT INTO statement is

INSERT INTO VALUES (1, 2,....)

INSERT INTO table_name (表 1, 表 2,...) VALUES (Value 1, value 2,....)

The Update statement is used to modify data in a table.

UPDATE table name SET column name = new value WHERE column name = some value

The DELETE statement is used to DELETE rows from a table.

DELETE FROM table name WHERE column name = value

conclusion

The development history of database:

  1. Network database
  2. Hierarchical databases
  3. Relational database
  4. Object-oriented database

Relational database:

  1. Desktop database
  2. Client/server database

A data table is a logical unit that stores data.

In a data table, a row is called a record and a column is called a field.

Primary key: uniquely identifies this record.

Afterword.

Scan the public account to subscribe for more exciting content.