Detailed MySQL first-mysql brief introduction and DDL statements

background: In recent years, open source databases have become popular. Due to its advantages of free use, simple configuration, good stability and excellent performance, open source database occupies a large market share in low-and middle-end applications, and MySQL is an outstanding representative in open source database. MySQL database is currently divided into the Community version (Community Server) and Enterprise Edition, the most important difference is: The community version is free download and completely free, but the official does not provide any technical support for most ordinary users, while the Enterprise version is charged , can not be downloaded online, accordingly, it provides more features and more complete technical support, more suitable for the database function and reliability of high-demand enterprise customers. This blog will be a rich example of the foundation of the SQL language in detail, MySQL, so that readers can not only learn the standard SQL "Structure query Language (structured querying language)" use, but also to learn some of the MySQL extension SQL Method of use.

PS: The content of this blog is to learn from the text of this book, MySQL is really a good book of SQL Primer, recommended.

SQL classification:

SQL statements can be classified into the following 3 categories.

DDL (data definition Languages) statements , which define the definitions of database objects, such as different data segments, databases, tables, columns, indexes, and so on. The commonly used statement keywords include create, drop, alter and so on.

DML (Data manipulation Language) statement: Data manipulation statements to add, delete, update, and query database records, and to check data integrity, commonly used statement keywords include insert, delete, udpate and select and so on. (Add a change to the search)

DCL (Data Control Language) statement: A statement that controls the level of permission and access to different data segments directly. These statements define the database, table, field, user's access rights, and security level. The main statement keywords include GRANT, revoke, and so on.

DDL statements:

DDL is the abbreviation of the data definition language, simply speaking, is to create, delete, modify the object inside the database operation language. the biggest difference between it and the DML language is that DML is just an operation on the internal data of the table, not the definition of the table, the modification of the structure, and no other objects involved. DDL statements are more commonly used by database administrators (DBAs), and are seldom used by developers.

Here are some examples of how common DDL statements are used in MySQL.

1. Create a database

After starting the MySQL service, enter the following command to connect to the MySQL server:

[Email protected] ~]$ mysql-uroot-p

Enter Password:

Welcome to the MySQL Monitor. Commands End With; or \g.

Your MySQL Connection ID is 7344941 to server Version:5.1.9-beta-log

Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the buffer.

Mysql>

In the above command line, MySQL represents the client command, and-U follows the connected database user,-p indicates that a password is required. If the database is set up correctly and you enter the correct password, you will see the above Welcome screen and a mysql> prompt. The following sections are described in the Welcome screen.

The Terminator of an order: with or \g the end.

Client Connection ID: This number records the number of connections the MySQL service has so far, with each new connection automatically adding 1, in this case 7344941.

MySQL Server version : In this case, "5.1.9-beta-log", the test version of 5.1.9, if it is the standard version, will be used instead of beta.

through "help;" or the "\h" command to display Help content: clear the command line buffer with the "\c" command.

At the mysql> prompt, enter the SQL statement that you want to execute, and each SQL statement ends with a semicolon or \g, followed by the ENTER key.

Because all of the data is stored in the database, the first command to learn is to create a database with the syntax as follows:

CREATE DATABASE dbname

For example, to create a database test1, the command executes as follows:

mysql> CREATE DATABASE test1;

Query OK, 1 row Affected (0.00 sec)

It can be found that after the creation of the command, there is a line of "query OK, 1 row affected (0.00 sec)", this hint can be divided into 3 parts, "Query OK" indicates that the above command execution succeeds, the reader may be strange, not to perform the query operation, Why is the query displayed successfully? In fact, this is a feature of MySQL, all of the DDL and DML (excluding SELECT) operation after the successful execution of the display "Query OK", which is understood to be successful execution ; "1 row

Affected "indicates that the operation affects only one row of records in the database, and" 0.00 sec "records the time the operation was executed. At this point, if you need to know what databases exist in the system, you can use the following command to view:

1234567891011

Mysql> show databases; +--------------------+| Database | +--------------------+|information_schema | |Cluster | |MySQL | |Test | |test1 | +--------------------+5 rows in set (0.00 sec)

As you can see, in addition to the test1 you just created in the list above, there are 4 additional databases that were created automatically when you installed MySQL, with the following features.

Information_schema: It mainly stores some database object information in the system. such as user table information, column information, permission information, character set information, partition information and so on.

cluster: The cluster information of the system is stored.

MySQL: stores user rights information for the system.

Test : The system automatically creates a testing database that can be used by any user.

Once you have reviewed the databases that are already in your system, you can select the database you want to manipulate with the following command: Usedbname

Then use the following command to view all data tables created in the Test1 database:mysql> show tables;

2. Deleting a database

The syntax for deleting a database is simple, as follows:drop DB dbname;

For example, to delete a test1 database, you can use the following statement:mysql> the drop DB test1;

Note: After the database is deleted, all of the table data below will be deleted, so be sure to check and make a proper backup before deleting it.

3. Create a table

The basic syntax for creating a table in a database is as follows:

12345

CREATE TABLE tablename (column_name_1 column_type_1   Constraints,column_name_2 column_type_2 constraints ,... column_name_n Column_type_n constraints)

Because the table name of MySQL exists on disk in the form of a directory, the character of the table name can be used with any character allowed by the directory name. column_name is the name of the column, Column_type is the data type of the column, Contraints is the column's approximate beam conditions .

For example, create a table called EMP. The table includes 3 fields, ename (name), HireDate (hire date), Sal (Salary), and the field types are varchar (10), date, int (2):

12345

mysql> create table emp( ename varchar(ten), hiredate Date, sal decimal(2),deptno int(2)); Query OK, 0 rows affected (0.02 sec)

Once the table has been created, if you need to look at the table definition, you can use the following command:DESC tablename

Although the DESC command can view the table definition, its output information is not comprehensive enough, in order to view more comprehensive table definition information, sometimes you need to view the SQL statement to create the table to get, you can use the following command to achieve:mysql> show create table emp \ G;

12345678910111213

***************************1. row *************************** Table: emp Create table: create table ' emp ' ( ' ename ' varchar DEFAULT NULL, ' hiredate ' date DEFAULT NULL, ' sal ' decimal (10,2) DEFAULT NULL, ' deptno ' int (2) DEFAULT NULL, KEY idx_emp_ename' (' ename ') ) ENGINE=InnoDB DEFAULT CHARSET=gbk 1 row in set (0.02 sec) ERROR:No Query specified Mysql>

From the Create SQL statement in the table above, you can see information such as the engine (storage engine) and CharSet (character set) of the table, in addition to the table definition. The meaning of the "\g" option is that records can be arranged vertically by field, making it easier to display records with longer content.

4. Delete a table

The delete command for the table is as follows:drop TABLE TableName

For example, to remove a database emp You can use the following command to:mysql> the drop table emp;

5. Modify Table (Important)

For tables that have already been created, especially those that already have a large amount of data, if you need to make some structural changes to the table, we can first delete the table (drop) and then rebuild the table according to the new table definition. This is no problem, but there is a certain need to do some extra work, such as reloading the data. And, if a service is accessing the table, it will also have an impact on the service. Therefore, in most cases, changes to the table structure generally use the ALTER TABLE statement, and the following are some common commands.

(1) To modify the table type, the syntax is as follows:

ALTER TABLE tablename MODIFY [COLUMN] column_definition [First | After Col_name]

For example, modify the Ename field definition of the table emp to change varchar (10) to varchar:mysql> ALTER TABLE EMP modify ENAME varchar;

(2) Add a table field with the following syntax:

ALTER TABLE tablename ADD [COLUMN] column_definition [First | After Col_name]

For example, the table EMP adds a new field of age, type int (3):mysql> ALTER TABLE EMP add column age int (3);

(3) Delete the table field with the following syntax:

ALTER TABLE tablename DROP [COLUMN] Col_name

For example, the field age is deleted:mysql> ALTER TABLE EMP drop column age;

(4) The field is renamed with the following syntax:

ALTER TABLE tablename Change [COLUMN] Old_col_name column_definition [first| After Col_name]

For example, rename age to Age1 and modify the field type to int (4):mysql> ALTER TABLE EMP change age Age1 int (4);

Note: The change and modify can modify the definition of the table, the difference is that the changes after the need to write two times the column name, inconvenient. But the advantage of change is that you can modify the column name, and modify cannot.

(5) Modify the order of the fields.

In the field additions and modifications syntax described earlier (add/cnahge/modify), there is an option first|after column_name, which can be used to modify the position of the field in the table, the default ADD The added new field is added to the last position of the table, and change/modify does not change the position of the field by default.

For example, add the new field birth date to the ename:mysql> ALTER TABLE EMP add birth date after ename;

Modify the field age, put it in front:mysql> ALTER TABLE EMP modify age int (3) first;

Note: change/first| After COLUMN these keywords are part of the MySQL extension on standard SQL and do not necessarily apply on other databases.

(6) The table is renamed with the following syntax: ALTER TABLE tablename RENAME [to] new_tablename

For example, rename the table emp to EMP1 with the following command:mysql> ALTER TABLE emp rename EMP1;

Detailed MySQL first-mysql brief introduction and DDL statements