Mysql Common Basic SQL statement summary _mysql

1. Common Orders

Connecting local and Remote databases (172.16.xx.xx:3306):

Mysql-h Localhost-u root-p123 

2. DDL

Data definition Lanuage, DDL, defines the database schema, including Create, ALTER, DROP, TRUNCATE, comment, and rename statements.

Creating (Create)

The CREATE statement creates a table:

CREATE TABLE ' Device_label ' (
' id ' int (one) not NULL auto_increment COMMENT ' primary key-self-growth id ',
' origin_model ' varchar (64 COLLATE utf8_bin NOT NULL COMMENT ' collection model ',
' origin_vendor ' varchar () COLLATE ' NOT null Utf8_bin ' collection vendor ',
' Vendor ' varchar COLLATE utf8_bin NOT null COMMENT ' label manufacturer ',
' model ' varchar (+) COLLATE utf8_bin not null COMMENT ' Label Brand ',
PRIMARY key (' id '),
UNIQUE key ' Device_key ' (' Origin_model ', ' Origin_vendor ')
Engine=innodb Increment=1 DEFAULT Charset=utf8 collate=utf8_bin comment= ' equipment annotation table '; 
Change (ALTER)

ALTER TABLE changes the structure of the tables and supports the following operations,

To modify the column type and column name:

ALTER TABLE Device_label modify Origin_model varchar (32);

Append columns:

To modify the order between columns:

Modify PRIMARY key:

Empty (TRUNCATE)

Truncate is an empty table, which is equivalent to delete from without specifying a where condition.

Truncate Device_label;

3. DCL

Data Control Language (Language, DCL) is used for user Rights management, including Grant and REVOKE commands.

Authorization (Grant)

MySQL has very granular permissions control:

• Detailed classification of permissions
db-> Table-> column, the weight of the permissions of the granularity
• Control of host (wildcard matching)

Create a hive user and give permission to access db1 all tables in localhost:

CREATE USER ' hive ' @ ' localhost ' identified by ' mypass ';
GRANT all on db1.* to ' hive ' @ ' localhost ';
--Can be abbreviated as

You can also assign only select permissions to a table:

4. DML

Data definition language (manipulation language, DML) is mainly used to express the query and update of database, mainly including the deletion and modification (insert,update,delete,select).

Add (INSERT)

Increase row data:

Insert into Device_label (Origin_model, Origin_vendor, Vendor, model)
values (
' H9 ', ' bbk ', ' Step high ', ' H9 ')

Copy a table to a different table:

Insert into device_label_copy (' Origin_model ', ' Origin_vendor ', ' Vendor ', ' model ')
select ' Origin_model ', ' Origin_ Vendor ', ' Vendor ', ' model '

In addition, MySQL supports the storage of structured plain text in the Load data format:

Load data local infile ' dvc-label.csv '
to table Device_label
fields terminated by ', '
ignore 1 lines

If error 1148 (42000) errors occur, use MySQL--local-infile-u user-ppasswd command to enter MySQL.

Change (UPDATE)

To update the value of a column:

Update Device_label 
Set Origin_model = ' T2 ', Origin_vendor = ' Xiami '

Delete (delete)

To delete a row according to the Where condition:

Check (SELECT)

Query label manufacturers of more than 10 models:

Select Vendor, COUNT (distinct model) as models from
Device_label
GROUP by vendor have
models > 10

The above is a small set to introduce the MySQL Common basic SQL statement Summary, I hope to help you, if you have any questions please give me a message, small series will promptly reply to everyone. Here also thank you very much for the cloud Habitat Community website support!