Quick Start to SQL _ MySQL

The createtable statement in Table SQL is used to create a new database table. The format of the Createtable statement is as follows: createtabletablename (column1datatype, column2datatype, column3datatype); if you want to define the column restrictions when creating a new table, you can use the optional condition option: crea to create a table

The create table statement in SQL is used to create a new database table. The format of the Create table statement is as follows:

Create table tablename

(Column1 data type,

Column2 data type,

Column3 data type );

If you want to specify the column restrictions when creating a new table, you can use the optional condition options:

Create table tablename

(Column1 data type [constraint],

Column2 data type [constraint],

Column3 data type [constraint]);

Example:

Create table employee

(Firstname varchar (15 ),

Lastname varchar (20 ),

Age number (3 ),

Address varchar (30 ),

City varchar (20 ));

Simply put, when creating a new table, add the name of the table to be created after the keyword "create table", and set the name and data type of each column in brackets, and optional restrictions. Note that all SQL statements must end with the ";" symbol.

The names of database tables and columns created using SQL statements must start with a letter, followed by letters, numbers, or underscores. The name length cannot exceed 30 characters. Note: When you select a table name, do not use the reserved keywords in the SQL language, such as select, create, and insert, as the table or column name.

The data type is used to set the data type in a specific column. For example, you can only use the varchar or char data type in the name column, but not the number data type.

Commonly used data types in SQL are:

Char (size): a fixed-length string. The size in parentheses is used to set the maximum length of the string. The maximum length of a Char type is 255 bytes.

Varchar (size): a variable-length string. The maximum length is set by size.

Number (size): number type. The maximum number of digits is set by size.

Date: Date type.

Number (size, d): number type. size determines the maximum number of digits. d is used to set the number of digits after the decimal point.

Finally, when creating a new table, you must note the restrictions on the columns in the table. A condition is a rule that must be followed when data is input to a specific column. For example, the unique condition requires that there cannot be two records with the same value in a column, and the values of all records must be unique. In addition to unique, restrictions on commonly used columns include not null and primary key. Not null indicates that the value of a column in the table cannot be null. The Primary key specifies a unique identifier for all records in the table.


Insert data to a table

The SQL language uses the insert statement to insert or add new data rows to the database table. The format of the Insert statement is as follows:

Insert into tablename

(First_column,... last_column)

Values (first_value,... last_value );

For example:

Insert into employee

(Firstname, lastname, age, address, city)

Values ('lil', 'ming', 45, 'No. 77 Changan road', 'Beijing ");

Simply put, when adding a new record to a database table, enter the name of the table to be added after the keyword insert into, and then list the names of the columns to add new values in brackets. Finally, after the keyword values, enter all the record values to be added according to the sequence of the columns entered earlier.


Update record

The SQL language uses the update statement to update or modify existing records that meet the specified conditions. The Update statement format is:

Update tablename

Set columnname = newvalue [, nextcolumn = newvalue2...]

Where columnname OPERATOR value [and | or column OPERATOR value];

For example:

Update employee

Set age = age 1

Where first_name = 'Mary 'and last_name = 'williams ';

When using the update statement, the key point is to set the where Condition Clause for judgment.


Delete record

SQL uses the delete statement to delete rows or records in database tables. The format of the Delete statement is:

Delete from tablename

Where columnname OPERATOR value [and | or column OPERATOR value];

For example:

Delete from employee

Where lastname = May;

To put it simply, when you need to delete a row or a record, enter the table name after the delete from keyword, and then set the judgment condition for deleting the record in the where clause. Note: If you do not set the where clause when using the delete statement, all records in the table will be deleted.


Delete database tables

Use the drop table command in SQL to delete a table and all records in the table. The format of the Drop table command is as follows:

Drop table tablename;

For example:

Drop table employee;

If you want to delete a database table completely, you only need to enter the name of the table you want to delete after the drop table command. The Drop table command is different from deleting all records in a table. After deleting all records in the table, the table still exists, and the column information in the table does not change. Using the drop table command, all information about the entire database table is deleted.


The preceding sections describe the main Commands and statements of the SQL language in detail. It should be said that the syntax structure and style of SQL statements are quite simple and intuitive. As long as you practice more in combination, you will be able to quickly grasp it in the short term.