Shulou(Shulou.com)06/01 Report--

The following mainly brings you a simple introduction to MySQL, I hope these words can bring you practical use, which is also the main purpose of my simple introduction to editing MySQL to understand this article. All right, don't talk too much nonsense, let's just read the following.

MySQL basic statement MySQL Advanced part 1.1 Overview of SQL

Structured query language (Structured Query Language), referred to as SQL, is a special purpose programming language, a database query and programming language for accessing data and querying, updating and managing relational database systems, as well as an extension of database script files.

From the above, we can see that there are about two kinds of jobs related to our database: DBD and DBA.

Dba is the database administrator database administrator

Dbd is the database developer database developer

SQL is an American standard for database language adopted by the American National Standards Institute (ANSI) in October 1986, and then the International Organization for Standardization (ISO) issued a formal international standard for SQL. In April 1989, ISO put forward the SQL89 standard with integrity characteristics, and in November 1992, the SQL92 standard was published. In this standard, the database is divided into three levels: basic set, standard set and complete set.

As for the basic set, we don't have to worry about the standard set, just take a look at it, and it's involved in database principles and mathematical algorithms.

1.2 SQL statement structure

The structured query language consists of six parts:

1.2.1 data query language (DQL:Data Query Language):

Its statement, also known as a "data retrieval statement", is used to obtain data from a table and determine how the data is given in the application. The reserved word SELECT is the most frequently used verb in DQL (and all SQL). The other commonly used reserved words in DQL are WHERE,ORDER BY,GROUP BY and HAVING. These DQL reserved words are often used with other types of SQL statements.

1.2.2 data manipulation language (DML:Data Manipulation Language):

Its sentences include verbs INSERT,UPDATE and DELETE. They are used to add, modify, and delete rows in the table, respectively. Also known as Action query language.

1.2.3 transaction processing language (TPL): somewhat similar to shell as a whole consisting of multiple SQL statements

Its statement ensures that all rows of the table affected by the DML statement are updated in a timely manner. TPL statements include BEGIN TRANSACTION,COMMIT and ROLLBACK.

1.2.4 data Control language (DCL):

Its statements are licensed through GRANT or REVOKE to determine the access of individual users and user groups to database objects. Some RDBMS can use GRANT or REVOKE to control access to individual columns of the form.

1.2.5 data definition language (DDL):

Its sentences include verbs CREATE and DROP. Create a new table or delete a table (CREAT TABLE or DROP TABLE) in the database; add indexes to the table, and so on. DDL includes a number of reserved words related to obtaining data in the human database catalog. It is also part of the action query.

1.2.6 pointer Control language (CCL):

Its statements, such as DECLARE CURSOR,FETCH INTO and UPDATE WHERE CURRENT, are used for separate operations on one or more forms.

1.3 MySQL statement 1.3.1 about database operations

(1) View the database:

Mysql > show databases; +-+ | Database | +-+ | information_schema | | mysql | | performance_schema | | sys |

Note:

1:information_schema this database stores the information of all databases on MySQL CVM. Such as the database name, the table of the database, the data type of the table column does not have access rights and so on. ["skim"]

2:performance_schema this is a new performance-optimized engine added by MySQL5.5: named PERFORMANCE_SCHEMA [p-optimized f: M-optimized ns]

It is mainly used to collect database CVM performance parameters. MySQL users cannot create tables whose storage engine is PERFORMANCE_SCHEMA

3:mysql library is a system library, which contains account information, permission information and so on.

4:mysql5.7 adds sys system database, through which you can quickly understand the metadata information of the system.

Metadata is data about data information, such as database or table names, data types of columns, or access rights.

Mysql > show databases\ G # displays * * 1. Row * * Database: information_schema * * 2. Row * in rows * Database: mysql * * 3. Row * * Database: performance_schema [root@server01] # mysql-e'show databases'-usystem-p123456 +- -+ | Database | +-+ | information_schema | | mysql | | performance_schema | +-+

Mysql-e is followed by a SQL statement, which is run directly at the terminal, and can be used to write sql-related shell.

[root@xuegod63 ~] # mysqlshow-uroot-p123456 +-+ | Databases | +-+ | information_schema | | mysql | | performance_schema | +-+

(2) create a database:

Syntax: create database database name

Considerations for creating a database:

1) in the file system, the datastore of MySQL will represent the MySQL database as a directory. Therefore, the database name in the above command must match the directory name of the operating system's constraints. For example,\, /,:, *,?, ", | these symbols are not allowed in file and directory names, and these letters are automatically deleted in the MySQL database name.

2) the name of the database cannot exceed 64 characters. Names containing special characters or names composed entirely of numbers or reserved words must be enclosed in single quotation marks ``.

3) the database cannot have the same name.

Mysql > create database HA; # create a database named HA mysql > create database `HA- test`; [root@server01 ~] # ls / var/lib/mysql/ # View the database storage directory

Select the database to operate on:

Using the user statement will select a database to become the current database. The latter operations are performed in the selected database by default.

Mysql > use HA-test; Database changed

Check your location and default location

Mysql > select database (); +-+ | database () | +-+ | HA-test | +-+ 1 row in set (0.00 sec)

Default

Mysql > select database (); +-+ | database () | +-+ | NULL | +-+ 1 row in set (0.00 sec)

Null means that no database is selected

Null represents unknown data in the database, which has three main meanings:

1) know that the data exists, but do not know the specific value

2) do not know whether the data exists

3) the data does not exist.

Select the default database on the command line

Mysql-uroot-p123456 HA mysql > select now (), user (), database () +-+ | now () | user () | database () | +-+- -+ | 2015-10-06 10:52:48 | system@localhost | HA | +-+

Delete the database:

Mysql > drop database `HA- test`

There is no hint to delete it, so you should operate it carefully.

Method 2: just go directly to the database and move out of the directory.

Cd / usr/local/mysql/data/ mv HA@002dtest / tmp mysql > show databases

Use the IF EXISTS clause to avoid MySQL error messages when deleting a database that does not exist

Mysql > drop database if exists `HA- test`

IF EXISTS: if present

In the same way, we can also use the

Mysql > create database if not exists HA; 1.3.2 about table operations:

(1) create a table:

Syntax: create table table name (field name type, field name type, field name type)

Mysql > create table student (id int 20), name char 40, age int)

(2) View table-related information:

View the table:

To access the database and then check

Mysql > use mysql; Database changed mysql > show tables; +-+ | Tables_in_HA | +-+ | student |

(3) View the structure of the table:

Describe

Mysql > desc student +-+ | Field | Type | Null | Key | Default | Extra | +-+-+ | id | | int (20) | YES | | NULL | name | char (40) | YES | | NULL | | age | int (11) | YES | | NULL | | +-+-+ 3 rows in set (0.00 sec) mysql > explain mysql.user | Mysql > show columns from mysql.user; mysql > show fields from mysql.user; mysql > show columns from mysql.user like'% user'

Just know a commonly used one.

(4) check which commands are executed to create the table:

Mysql > show create table student\ G * * 1. Row * * Table: student Create Table: CREATE TABLE `student` (`id` int (20) DEFAULT NULL, `name` char (40) DEFAULT NULL, `age` int (11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8 mysql > create table student2 (id int (20), name char (40) Age int) ENGINE=MyISAM DEFAULT CHARSET=utf8 You can specify the default storage engine and character set for the table

These are the default storage engine and the default character set

(5) Delete the table:

Mysql > drop table student2

(6) pre-reading table information is prohibited:

There is no hint before prohibition.

Mysql > use performance_schema; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with-A

Add the-A parameter when logging in

Mysql-uroot-p123456-A

(7) modify the table name ALTER:

Syntax: alter table table name rename new table name

Mysql > use HA; mysql > alter table student rename students; # studen table name is changed to students mysql > show tables; +-+ | Tables_in_HA | +-+ | students | +-+

(8) modify the field type in the table:

Syntax: alter table table name modify the type of field name to be modified

Mysql > desc students +-+ | Field | Type | Null | Key | Default | Extra | +-+-+ | id | | int (20) | YES | | NULL | | name | char (40) | YES | | NULL | | age | int (11) | YES | | NULL | | +-+-+ |

Mysql > alter table students modify id int (10)

(9) modify the field type and field name in the table:

Syntax: alter table table name change original field name new field name new field type

I checked the official documents and found that mysql really does not support modifying multiple fields at the same time.

MODIFY [COLUMN] col_name column_definition

[FIRST | AFTER col_name]

Source: http://dev.mysql.com/doc/refman/5.5/en/alter-table.html

Mysql > desc students +-+ | Field | Type | Null | Key | Default | Extra | +-+-+ | id | | int (10) | YES | | NULL | | name | char (40) | YES | | NULL | | age | int (11) | YES | | NULL | | +-+-+ |

Mysql > alter table students change name stname char (20)

Note: the difference between CHANGE and MODIFY:

CHANGE renames the column and changes the column type by giving the old column name, the new column name, and the current type. MODIFY can change the type of column without renaming (no new column name is required)

(10) add fields to the table:

Syntax: alter table table name add field name field type

Mysql > alter table students add sex enum ('Maureen')

(11) specify the location to add fields:

Add a field to the first column:

Mysql > alter table students add uid int (10) first

Add an address field after age:

Mysql > alter table students add address char (40) after age

(12) Delete the fields in the table:

Syntax: alter table table name drop field name

Mysql > alter table students drop address; 1.3.3 about recording operations:

(1) insert field INSERT:

Syntax: insert into table name values (field value 1, field value 2, field value 3)

Mysql > create table student (id int (20), name char (40), age int); mysql > insert into student values (1)

Insert a record to correspond to the relative type

Mysql > insert into student values (2) (2), (3) 3)

Insert multiple strips at the same time, use, separate

Mysql > insert into student (id,name) values

Specify field insertion

(2) record in the query table:

Syntax: select * from table name

Mysql > select * from student; * means all +-+ | id | name | age | +-+ | 1 | zhangs | 21 | 2 | lis | 24 | 3 | wange | 26 | 4 | hangl | NULL | +-+

We can also use\ G when there are more fields.

Mysql > select * from student\ G

Query only the contents of a field in the table:

Mysql > select name from student; +-+ | name | +-+ | zhangs | | lis | | wange | | hangl | mysql > select id,name from student +-+ | id | name | +-+-+ | 1 | zhangs | | 2 | lis | | 3 | wange | | 4 | hangl | +-+-+

(3) check the tables of other databases or not on this database:

Syntax: SELECT field FROM database name. Table name

Mysql > select * from HA.student; to view the table content and database name specified under a database. Table name +-+ | id | name | age | +-+ | 1 | zhangs | 21 | | 2 | lis | 24 | 3 | wange | 26 | | 4 | hangl | NULL | +-+

This is equivalent to use the database first and then view the

(4) Delete the record:

Delete the row with id 3

Mysql > delete from students where id=3

Delete a line whose age is empty

Mysql > delete from students where age is null

(5) updating records

Mysql > update students set sex='M' where id=2; mysql > update students set id=2; all are changed to 2 update students set stname='zhangsan',age=21 where uid=1; when updating multiple fields at the same time, separated by 1.3.4 SQL basic condition query statement

Syntax: select field name 1, field name 2 from table name [where condition]

(1) query the NAME,AGE in the STUDENTS table

Mysql > select name,age from student; +-+-+ | name | age | +-+-+ | zhangs | 21 | lis | 24 | jk | 24 | lo | 26 | io | 25 | jk | 24 | +-+

(2) to repeatedly query distinct

Mysql > select distinct name,age from student; +-+-+ | name | age | +-+-+ | zhangs | 21 | lis | 24 | jk | 24 | lo | 26 | io | 25 | mysql > select distinct id,name,age from student where id=3 +-+ | id | name | age | +-+ | 3 | jk | 24 |

The distinct of select distinct from students; mysql can be used for

(3) use AND and OR for multi-conditional query

When or and and exist at the same time, the two boundary values of and are calculated first, logic and execution first.

Mysql > select id,name,age from student where id > 3 and age > 25; +-+ | id | name | age | +-+ | 5 | lo | 26 | +-+ mysql > select id,name,age from student where id > 3 or age > 25 +-+ | id | name | age | +-+ | 5 | lo | 26 | | 6 | io | 25 | +-+

Select * from students where stname='zhangsan' and (age=21 or age=24)

Notice the logical relationship when both and and or are used

(4) MYSQL case-sensitive query:

MySQL queries are case-insensitive by default

Mysql > select name from student where name='jk'; +-+ | name | +-+ | jk | | jk | | JK | +-+

Solve

Mysql > select * from student where binary name='jk' ["ba" n "ri] +-+ | id | name | age | sex | +-+ | 3 | jk | 24 | W | | 3 | jk | 24 | W | +-+

BINARY is a type conversion operator that is used to force the string after it to be a binary string, which can be understood to be case-sensitive when comparing strings.

(5) sorting of MYSQL queries:

Syntax: select distinct field 1, field 2 from table name order by field name

Default is ascending asc

Mysql > select distinct id from student order by id asc; +-+ | id | +-+ | 2 | 3 | 5 | 6 | 8 | +-+ mysql > select distinct id from student order by id desc; +-+ | id | +-+ | 8 | 6 | 5 | 3 | 2 | +-+ 1.3.5 about MySQL command help

Help

Mysql > help show

It will tell us a lot of usage and information.

Mysql > help select

For the above simple introduction to MySQL understanding, we do not think it is very helpful. If you need to know more, please continue to follow our industry information. I'm sure you'll like it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.