MySQL Basic operation

SQL Overview

Sql:structure Query Language. (Structured Query language)

Classification of SQL

ddl** (data definition Language): Definition Language for database objects: libraries, tables, columns, etc.;

CREATE, ALTER, DROP

dml*** (Data manipulation Language): Operation language for manipulating database records (data);

INSERT, UPDATE, DELETE

DCL (Data Control Language): The language used to define access rights and security levels;

DQL* * * * *(data query Language): Query Language for querying records (data).

SELECT

* Note: SQL language ends with;

DDL: Defining databases, tables, columns, and so on

Keywords used: Create, alter, drop

manipulating databases

Create

　　　　CREATE database mydb1; --Create a database directly, using the default encoding

　　　　CREATE database mydb2 character Set GBK; --Specify the encoding

　　　　CREATE database mydb3 character Set GBK collate gbk_chinese_ci      ; --Specifying the encoding and the collation used

Inquire

View all databases in the current database server

　　show databases;

View the definition information for the MYDB2 database that you created earlier

　　　　Show CREATE Database mydb2;

Delete the previously created MYDB3 database

　　　　Drop database mydb3;

Modify

View the database in the server and modify the MYDB2 character set to UTF8;

　　　　ALTER DATABASE MYDB2 character set UTF8;

Delete

　　　Drop database mydb3;

　　　

Operating Data Sheet

* Syntax for creating tables:

　　　CREATE TABLE Table name (

Field 1 field type,

Field 2 field type,

...

field N field type

);

　　　　　

* Common data types:

int: integral type

Double: a floating-point type, such as double (5,2), that represents up to 5 bits, which must have 2 decimal places, that is, a maximum value of 999.99;

Char: Fixed length string type, char (TEN) ' ABC '---length up to 65535

VARCHAR: variable-length string type; varchar (65535) ' ABC '---length max

Text: String type,---maximum allowable length is 4M

BLOB: Byte type;

Date: The format of the type: yyyy-mm-dd; ' 2016-12-13 '

Time: The format of the type: HH:MM:SS

Timestamp: Timestamp type YYYY-MM-DD HH:MM:SS will be automatically assigned, as of 2023 years;

DateTime: DateTime type YYYY-MM-DD HH:MM:SS

　　　

* Syntax for modifying table structure

Modify table structure using ALTER TABLE, modify the table structure to include actions such as adding column definitions, modifying column definitions, deleting columns, renaming columns, and so on.

① the syntax for adding a column definition is as follows:

　　　　　　　　　　ALTER TABLE table name

Add

(

#可以有多个列定义

COLUMN_NAME1 datatype [Default expr],

...

);

② the syntax for modifying a column definition is as follows:

　　　　　　　　　　　ALTER TABLE table name

Modify COLUMN_NAME datatype;

③ the syntax for removing columns from a data table:

　　　　　　　　　　　ALTER TABLE table name

Drop column_name;

④ The syntax format for renaming a data table is as follows:

　　　　　　　　　　ALTER TABLE table name

Rename to table name;

⑤mysql provides the change option for ALTER TABLE, which changes the column name. The syntax for the Change option is as follows:

　　　　　　　　　　ALTER TABLE table name

Change Old_column_name new_column_name type;

⑥ syntax to delete a table:

　　　　　　　　　　drop table name;

DML Operations (important)

Querying all data in a table

Select*from table name;

DML is the operation of adding, deleting, and modifying data in a table . Do not confuse the DDL.

　　　　INSERT, UPDATE, DELETE

　　　　

　　　　Small knowledge:

In MySQL, both the string type and the date type are enclosed in single quotation marks. Like ' Tom ' 2015-09-04 '

Null value: null

Insert operation: Insert

Syntax:INSERT into table name (column name 1, column Name 2, ...) VALUES (column value 1, column value 2, ...);

Note: column names correspond to the type, number, and order of column values.

You can think of a column name as a parameter in Java and a bar column value as an argument.

The value does not exceed the length of the column definition.

If you insert a null value, consult NULL

Insert the same date and character that you want to enclose in single quotation marks.

If each field has a value, it can be abbreviated as:

　　　　　　　INSERT into table name values (column value 1, column value 2, ...);

　　　　　　　

BULK INSERT:

　　　　INSERT into EMP VALUES

(1, ' Zs ', ' m ', ' 2015-09-01 ', 10000, ' 2015-09-01 ', NULL),

(2, ' Li ', ' m ', ' 2015-09-01 ', 10000, ' 2015-09-01 ', NULL),

(3, ' ww ', ' m ', ' 2015-09-01 ', 10000, ' 2015-09-01 ', NULL);

Modify Operation UPDATE

Syntax:UPDATE table name set column Name 1 = column value 1, column Name 2 = column value 2, ... where column name = value-The Where is followed by a condition that satisfies the condition is modified, otherwise it is not modified.

such as: UPDATE emp SET salary=salary+1000 WHERE name= ' John Doe ';

Delete operation Delete

　　　Syntax:DELETE from table name; --Delete all records in the table

--Delete the record in the table, delete the slow speed

--deleted data can be recovered

　　　　　　　TRUNCATE table name -Deletes fast because it deletes the entire table before creating an empty table that is exactly the same

--deleted data cannot be replied to

DELETE from table name WHERE column name = value; --Deletes the specified record, where it is followed by a condition that satisfies the condition is deleted, otherwise it is not deleted.

MySQL Basic operation