Getting Started with SQL Basics (i): SQL Brief and DDL statements

SQL Brief

SQL is an abbreviation for Structure query Language (Structured Query language), which is an application language for relational model databases. After long-term development, SQL has become a very popular multi-platform interactive low-level conversational language, which is becoming more and more perfect.

SQL statements can be classified into the following three categories:

• DDL (data definition Language) Data definition language: Used to define different data segments, databases, tables, views, indexes, and other database objects. Common statement keywords mainly include create, drop, alter and so on.
• The DML (data manipulation Language) Data manipulation language: Used to add, delete, update, and query database records, while also providing data integrity checks. Common statement keywords include insert, delete, update, select, and so on.
• DCL Data Control Language: Controls the level of permissions and access to different data segments directly, which defines the database, table, field, user access, and security level. Common statement keywords mainly include GRANT, REVOKE, etc.

Next, the SQL three category standards and syntax will be introduced, and based on MySQL to demonstrate the relevant instances, and the MySQL part of the extended syntax is a little explanation, so as to better familiar with the SQL statements and to lay the groundwork for future learning and work.

Pre-preparation

After you start the MySQL service in a system service or command-line window, enter the following command at the command line to connect to the MySQL server

C:\users\username>mysql-uroot–p

Where the command ' MySQL ' represents the client command, '-U ' followed by ' root ' to represent the database root user, and '-P ' indicates the need to enter a password

As shown in the following examples:

After the connection is successful, you will be prompted to enter the Welcome screen, which mainly displays the following content:

• Command statement use '; ' or ' \g ' to end
• MySQL connection ID number, which records the total number of MySQL server connections, has been connected 13 times
• Server version number, in MySQL 8.0.11 version
• Copyright information of affiliated Companies
• Use ' \h ' for help instructions, use ' \c ' to clear the command line cache

SQL Statement First category--DDL statement

DDL is the abbreviation of the data definition language, mainly used to create, delete and modify the internal objects of the database, it is mostly used by DBAs (database Administrator, DBA), and is seldom used by developers. Depending on the action object, it can be divided into database operations and data table operations. A database is a container for storing data, whereas a data table is a description of the structure of the data.

(i) Database operations 1. Create a database

Basic syntax:CREATE DATABASE dbname;

Example (create a database named example)

After execution, a "Query OK, 1 row affected (0.18 sec)" message is executed, which indicates that this command executed successfully, affecting only one row of records (because only 1 databases were created), which took 0.18 seconds.

If you try to create a database that already exists with the same name, you receive the following error alert:

Example (attempting to create a database with the same name that already exists)

2. View the database

Basic syntax:SHOW DATABASES;

Example (view a database that already exists in the system)

As you can see, there are currently 5 databases in the system, except for the example database we just created, and the others are created from the system. Where Information_schema is used to store system database object information, MySQL is used to store user rights information, Performance_schema is used to collect database server performance parameters, SYS contains a series of stored procedures, Custom functions and views to help get system object information, in addition, the system will automatically create test testing database (the figure is not shown because it was inadvertently deleted)

3. Operation database 1) Select database

Basic syntax: usedbname;

Example (select Example database)

2) View all data tables in the selected database

Basic syntax:SHOW TABLES;

Example (view data table in Test1 database)

3) Delete Database

Basic syntax:DROP DATABASE dbname;

Example (delete test1 database)

(ii) Data sheet Operation 1. Create a table

Basic syntax:

CREATE TABLE TableName (

Column_name_1 column_type_1 Constraints,

column_name_2 column_type_2 Constraints,

...

Column_name_n column_type_n constraints

);

Where column_name is the name of the property column in the table, column_type the data type for this property, constraints represents the constraint on the attribute (so that the property is nullable, the property is scoped, and so on)

Example (creating a student student table in the example database)

2. View the table

Basic syntax:DESC tablename;

Example (View student table)

One drawback of this viewing table is that the display is not comprehensive, and if you want to display complete information, you can view the table by 'show CREATE table tablename \g' (note that the ' \g ' here is not the same as ' \g ', both of which show relevant information, But the former will be displayed in vertical form and the interface is more friendly.)

Example (view all information about the student table)

3. Delete a table

Basic syntax:DROP TABLE tablename;

Example (delete temp table)

4. Modify table 1) Modify the data type of the properties in the table

Basic syntax:ALTER TABLE tablename MODIFY [COLUMN] column_definition [first| After column_name];

where 'MODIFY' is the modified keyword, 'column_definition' is a new definition for the Property column object to be modified, '[first| After column_name]' indicates the location of the modified column, 'first ' is stored in line one,' after column_name' is stored behind a column, If no location is selected, the default remains unchanged, followed by an action syntax similar to

Example (Modify the Sno property data type in the student table to char (12))

2) New Table attribute field

Basic syntax:ALTER TABLE tablename ADD [COLUMN] column_definition [first| After column_name];

Example (add a Sgrade attribute field for the student table)

3) Delete Table attribute fields

Basic syntax:ALTER TABLE tablename DROP [COLUMN] column_name;

Example (delete sgrade attribute field in student table)

4) Modify Table property field name

Basic syntax:ALTER TABLE tablename Change [COLUMN] Old_column_name column_definition [first| After column_name];

Example (renaming Sno to SID in the student table)

It is important to note that both change and MODIFY can modify the table attribute field, But the change can also modify the name, and MODIFY cannot

5) Change the data table name

Basic syntax:ALTER TABLE tablename RENAME [to] new_tablename;

Example (renaming the student table to Stu)

Summary

Getting Started with SQL Basics (i) mainly introduces the SQL Basic overview and SQL statement three categories, and details the first category of SQL statements--DDL data definition language and its use of the basic syntax, together with a demonstration of the database and data table The basic operation of the two objects.

Getting Started with SQL Basics (i): SQL Brief and DDL statements