Shulou(Shulou.com)06/01 Report--

SQL is the abbreviation of Structure Query Language (structured query language). It is the application language of relational database, which was sent by IBM in the 1970s to realize the information retrieval in relational database.

In the early 1980s, the American National Standards Institute (ANSI) began to develop the SQL standard. The earliest ANSI standard was completed in 1986 and was called SQL-86. It is precisely because of the standardization of the SQL language that most relational databases support SQL, which has developed into an underlying conversation language for multiple platforms to interact with each other.

Classification of SQL:

DDL: data definition language, that is, the language for creating, deleting, modifying and other operations on internal objects in the database. The biggest difference between DML and DML is that DML only operates on the data in the table, but does not involve the definition and structure modification of the table, let alone other objects. DBA is widely used. Common keywords include create, drop, alter and so on.

DML: data manipulation language for adding, deleting, updating, and querying records in tables, and checking the integrity of data, which is widely used by developers. Commonly used statements include insert, delete, update, and select.

DCL: data control language, used to manage the permissions of objects in the system, commonly used statements such as grant, revoke, etc.

1 example of DDL statement:

1) create a database

Mysql > show engines; # to view supported engines, including default engines

+-- +

| | Engine | Support | Comment | Transactions | XA | Savepoints | |

+-- +

| | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | |

| | CSV | YES | CSV storage engine | NO | NO | NO | |

| | MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | NO | NO | NO | |

| | InnoDB | YES | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |

| | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |

+-- +

Mysql > show character set; # View the character sets supported by the system

Mysql > show variables like "character%"; # View current character set settings

Mysql > show variables like "collation%"; # View character set verification settings

Mysql > create database test1; # create test1 database

Query OK, 1 row affected (0.05sec)

Mysql > show databases; # View the database

+-+

| | Database |

+-+

| | information_schema |

| | mysql |

| | performance_schema |

| | test |

| | test1 |

| | testdb |

Mysql > create database T121 default character set utf8 collate utf8_general_ci

Query OK, 1 row affected (0.00 sec) # when creating, you can also specify a character set

2) Delete the database

Mysql > drop database test1

Query OK, 0 rows affected (0.01 sec)

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | mysql |

| | performance_schema |

| | test |

| | testdb |

Note: once the database is deleted, all tables in the database will also be deleted, so backup is very important.

3) create tables

Mysql > use zwj; # Select a database

Mysql > create table emp # varchar (n) where n represents the number of characters

-> (ename varchar (10)

-> hiredate date

-> sal decimal (10jue 2)

-> deptno int (2))

Query OK, 0 rows affected (0.11 sec)

Mysql > desc zwj.emp; # View the structure of the emp table in the zwj library

+-+ +

| | Field | Type | Null | Key | Default | Extra | |

+-+ +

| | ename | varchar (10) | YES | | NULL |

| | hiredate | date | YES | | NULL |

| | sal | decimal (10jue 2) | YES | | NULL | |

| | deptno | int (2) | YES | | NULL |

+-+ +

Mysql > show create table zwj.emp\ g # View the SQL statement that creates the table, including the character set used

+- -+

| | Table | Create Table |

+- -+

| | emp | CREATE TABLE `emp` (

`ename` varchar (10) DEFAULT NULL

`hiredate` date DEFAULT NULL

`sal` decimal (10jue 2) DEFAULT NULL

`deptno` int (2) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

Copy a table

Mysql > create table t119 like zwj.t118

Query OK, 0 rows affected (0.04 sec)

Mysql > insert into t119 select * from zwj.t118

Query OK, 12 rows affected (0.00 sec)

Records: 12 Duplicates: 0 Warnings: 0

Check the status of the table to see if the two tables are consistent

Mysql > use zwj

Database changed

Mysql > show table status\ G

* * 1. Row *

Name: t118

Engine: MyISAM

Version: 10

Row_format: Fixed

Rows: 12

Avg_row_length: 30

Data_length: 360

Max_data_length: 8444249301319679

Index_length: 1024

Data_free: 0

Auto_increment: NULL

Create_time: 2017-04-29 08:20:18

Update_time: 2017-04-29 08:29:50

Check_time: NULL

Collation: latin1_swedish_ci

Checksum: NULL

Create_options:

Comment:

* 2. Row * *

Name: t119

Engine: MyISAM

Version: 10

Row_format: Fixed

Rows: 12

Avg_row_length: 30

Data_length: 360

Max_data_length: 8444249301319679

Index_length: 1024

Data_free: 0

Auto_increment: NULL

Create_time: 2017-04-29 09:09:12

Update_time: 2017-04-29 09:09:38

Check_time: NULL

Collation: latin1_swedish_ci

Checksum: NULL

Create_options:

Comment:

2 rows in set (0.00 sec)

View the status information of the specified table

Mysql > show table status like't 118'\ G

* * 1. Row *

Name: t118

Engine: MyISAM

Version: 10

Row_format: Fixed

Rows: 12

Avg_row_length: 30

Data_length: 360

Max_data_length: 8444249301319679

Index_length: 1024

Data_free: 0

Auto_increment: NULL

Create_time: 2017-04-29 08:20:18

Update_time: 2017-04-29 08:29:50

Check_time: NULL

Collation: latin1_swedish_ci

Checksum: NULL

Create_options:

Comment:

1 row in set (0.00 sec)

4) delete the table

Mysql > drop table zwj.emp

Query OK, 0 rows affected (0.05 sec)

5) alter table statement is needed to modify the table

Modify the definition of table ename field to change varchar (10) to varchar (20)

Mysql > alter table emp modify ename varchar (20); # keyword modify is used to modify the definition of fields in the table

Query OK, 0 rows affected (0.17 sec)

Records: 0 Duplicates: 0 Warnings: 0

Mysql > desc emp

+-+ +

| | Field | Type | Null | Key | Default | Extra | |

+-+ +

| | ename | varchar (20) | YES | | NULL |

| | hiredate | date | YES | | NULL |

| | sal | decimal (10jue 2) | YES | | NULL | |

| | deptno | int (2) | YES | | NULL |

+-+ +

Add a new field age to the table emp with the type int (3):

Mysql > alter table emp add age int (3); # last by default

Query OK, 0 rows affected (0.08 sec)

Records: 0 Duplicates: 0 Warnings: 0

Mysql > desc emp

+-+ +

| | Field | Type | Null | Key | Default | Extra | |

+-+ +

| | ename | varchar (20) | YES | | NULL |

| | hiredate | date | YES | | NULL |

| | sal | decimal (10jue 2) | YES | | NULL | |

| | deptno | int (2) | YES | | NULL |

| | age | int (3) | YES | | NULL |

+-+ +

5 rows in set (0.00 sec)

Delete a field

Mysql > alter table emp drop age

Query OK, 0 rows affected (0.08 sec)

Records: 0 Duplicates: 0 Warnings: 0

Modify field name

Mysql > alter table emp change age age1 int (4); # keyword change can modify the definition of the table, such as the field name

Query OK, 0 rows affected (0.03 sec)

Records: 0 Duplicates: 0 Warnings: 0

Mysql > desc emp

+-+ +

| | Field | Type | Null | Key | Default | Extra | |

+-+ +

| | ename | varchar (20) | YES | | NULL |

| | hiredate | date | YES | | NULL |

| | sal | decimal (10jue 2) | YES | | NULL | |

| | deptno | int (2) | YES | | NULL |

| | age1 | int (4) | YES | | NULL |

+-+ +

Modify the order of field arrangement

Mysql > alter table emp add birth date after ename; # added field birth, after ename

Query OK, 0 rows affected (0.08 sec)

Records: 0 Duplicates: 0 Warnings: 0

Mysql > desc emp

+-+ +

| | Field | Type | Null | Key | Default | Extra | |

+-+ +

| | ename | varchar (20) | YES | | NULL |

| | birth | date | YES | | NULL |

| | hiredate | date | YES | | NULL |

| | sal | decimal (10jue 2) | YES | | NULL | |

| | deptno | int (2) | YES | | NULL |

| | age1 | int (4) | YES | | NULL |

+-+ +

6 rows in set (0.00 sec)

Put the field deptno after age1

Mysql > alter table emp1 modify deptno int (2) after age1

Query OK, 0 rows affected (0.14 sec)

Records: 0 Duplicates: 0 Warnings: 0

Mysql > desc emp1

+-+ +

| | Field | Type | Null | Key | Default | Extra | |

+-+ +

| | age1 | int (4) | YES | | NULL |

| | deptno | int (2) | YES | | NULL |

| | ename | varchar (20) | YES | | NULL |

| | birth | date | YES | | NULL |

+-+ +

4 rows in set (0.00 sec)

Put the field age1 first

Mysql > alter table emp modify age1 int (4) first

Query OK, 0 rows affected (0.15 sec)

Records: 0 Duplicates: 0 Warnings: 0

Mysql > desc emp

+-+ +

| | Field | Type | Null | Key | Default | Extra | |

+-+ +

| | age1 | int (4) | YES | | NULL |

| | ename | varchar (20) | YES | | NULL |

| | birth | date | YES | | NULL |

| | hiredate | date | YES | | NULL |

| | sal | decimal (10jue 2) | YES | | NULL | |

| | deptno | int (2) | YES | | NULL |

+-+ +

Modify the table name:

Mysql > alter table emp rename emp1

Query OK, 0 rows affected (0.02 sec)

Mysql > show tables

+-+

| | Tables_in_zwj |

+-+

| | emp1 |

+-+

1 row in set (0.00 sec)

2 example of DML statement

1) insert record

Mysql > insert into emp1 (age1,ename,birth,deptno) values (555)

Query OK, 1 row affected (0.03 sec)

Field names may not be specified, but the order followed by the values should be the same as the order of the fields

Mysql > insert into emp1 values ('666pm / BBM / BBM / 201612 / 30mp8')

Mysql > select * from emp1

+-+

| | age1 | ename | birth | deptno | |

+-+

| | 555 | aaa | 2016-10-30 | 5 |

| | 666 | bbb | 2016-12-30 | 8 |

+-+

Insert multiple records at a time

Mysql > insert into emp1 (age1,ename,birth,deptno)

-> values (11 / 11 / 30 / 11 / 2011 / 11 / 30 / 2011 / 11 / 30 / 2011 / 11 / 30 / 11 / 11 / 11 / 2011 / 11 / 30 / 2011 / 11 / 30 / 11 / 11 / 2011 / 11 / 30 / 2011 / 11 / 30 / 2011 / 11 / 30 / 11 / 11 / 2011 / 11 / 30 / 2011 / 11 / 30 / 11 / 11 / 30 / 11 / 11 / 11 / 30 / 11 / 11 / 30 / 11 / 11 / 30 / 11 / 11 / 30 / 11 / 11 / 11 / 30 / 11 / 11 / 30 / 11 / 11 / 30 / 11 / 11 / 11 / 30 / 11 / 11 / 11 / 11 / 2011 / 11 / 30 / 2011 / 11 / 30 / 2011 / 11 / 30 / 2011 / 11 / 30 / 2011 / 11 / 30 / 2011 / 11 / 30 / 2011 / 11 / 30 / 2011 / 11 /

-> ('666pm / DDD / 2014 / 12 / 22 / 2014 / 12 / 22 / 14 / 11)

-> ('888, 2015-11-30, 2015-11-30, 22')

-> ('333', '333', '2011-04-30','8')

Query OK, 4 rows affected (0.02 sec)

Records: 4 Duplicates: 0 Warnings: 0

Mysql > select * from emp1

+-+

| | age1 | ename | birth | deptno | |

+-+

| | 555 | aaa | 2016-10-30 | 5 |

| | 666 | bbb | 2016-12-30 | 8 |

| | 111 | ccc | 2011-11-30 | 4 |

| | 666 | ddd | 2014-12-22 | 11 | |

| | 888 | eee | 2015-11-30 | 22 | |

| | 333 | fff | 2011-04-30 | 8 |

+-+

6 rows in set (0.00 sec)

2) update the record and make changes through the update command

Mysql > update emp1 set age1=1000 where ename='aaa'

Query OK, 1 row affected (0.03 sec)

Rows matched: 1 Changed: 1 Warnings: 0

Mysql > select * from emp1

+-+

| | age1 | ename | birth | deptno | |

+-+

| | 1000 | aaa | 2016-10-30 | 5 |

| | 666 | bbb | 2016-12-30 | 8 |

| | 111 | ccc | 2011-11-30 | 4 |

| | 666 | ddd | 2014-12-22 | 11 | |

| | 888 | eee | 2015-11-30 | 22 | |

| | 333 | fff | 2011-04-30 | 8 |

+-+

6 rows in set (0.00 sec)

3) Delete the record:

Mysql > delete from emp1 where ename='bbb'

Query OK, 1 row affected (0.02 sec)

Mysql > select * from emp1

+-+

| | age1 | deptno | ename | birth | |

+-+

| | 2011 | 4 | ccc | 2011-11-30 |

| | 666 | 11 | ddd | 2014-12-22 |

| | 888 | 22 | eee | 2015-11-30 |

| | 333 | 8 | fff | 2011-04-30 |

+-+

4 rows in set (0.00 sec)

4) query records

Mysql > select age1,ename from zwj.emp1

+-+ +

| | age1 | ename |

+-+ +

| | 666 | bbb |

| | 111 | ccc |

| | 666 | ddd |

| | 888 | eee |

| | 333 | fff |

+-+ +

Remove and repeat the records in the table and display them.

Mysql > select distinct age1 from emp1; # distinct is the keyword, age1 is the field name

Conditional query

Mysql > select * from emp1 where age1='666'

+-+

| | age1 | ename | birth | deptno | |

+-+

| | 666 | bbb | 2016-12-30 | 8 |

| | 666 | ddd | 2014-12-22 | 11 | |

+-+

Combination condition query:

Mysql > select * from emp1 where ename='bbb' and birth select * from emp1 where ename='bbb' or birth select * from zwj.emp1 where ename regexp'^ caches; the # keyword regexp supports regular expressions

+-+

| | age1 | deptno | ename | birth | |

+-+

| | 2011 | 4 | ccc | 2011-11-30 |

+-+

1 row in set (0.00 sec)

Mysql > select * from zwj.emp1 where ename like'clockwise; # short horizontal means to match any single character

+-+

| | age1 | deptno | ename | birth | |

+-+

| | 2011 | 4 | ccc | 2011-11-30 |

+-+

1 row in set (0.01 sec)

Mysql > select * from zwj.emp1 where ename like'c% characters; #% means any character

+-+

| | age1 | deptno | ename | birth | |

+-+

| | 2011 | 4 | ccc | 2011-11-30 |

+-+

1 row in set (0.00 sec)

Sort and limit: keyword order by (default ascending sort)

Mysql > select * from emp1 order by age1

+-+

| | age1 | ename | birth | deptno | |

+-+

| | 111 | ccc | 2011-11-30 | 4 |

| | 333 | fff | 2011-04-30 | 8 |

| | 666 | bbb | 2016-12-30 | 8 |

| | 666 | ddd | 2014-12-22 | 11 | |

| | 888 | eee | 2015-11-30 | 22 | |

+-+

5 rows in set (0.00 sec)

For records with the same age1, if you arrange the fields deptno from high to low, you can use the following command, where desc represents descending order.

Mysql > select * from emp1 order by age1,deptno desc

+-+

| | age1 | ename | birth | deptno | |

+-+

| | 111 | ccc | 2011-11-30 | 4 |

| | 333 | fff | 2011-04-30 | 8 |

| | 666 | ddd | 2014-12-22 | 11 | |

| | 666 | bbb | 2016-12-30 | 8 |

| | 888 | eee | 2015-11-30 | 22 | |

+-+

5 rows in set (0.01 sec)

For records with the same age1, if you arrange the fields deptno from lowest to highest, you can use the following command, where asc represents ascending order.

Mysql > select * from emp1 order by age1,deptno asc

+-+

| | age1 | ename | birth | deptno | |

+-+

| | 111 | ccc | 2011-11-30 | 4 |

| | 333 | fff | 2011-04-30 | 8 |

| | 666 | bbb | 2016-12-30 | 8 |

| | 666 | ddd | 2014-12-22 | 11 | |

| | 888 | eee | 2015-11-30 | 22 | |

+-+

5 rows in set (0.01 sec)

Select the first three records after sorting

Mysql > select * from emp1 order by age1 limit 3

+-+

| | age1 | ename | birth | deptno | |

+-+

| | 111 | ccc | 2011-11-30 | 4 |

| | 333 | fff | 2011-04-30 | 8 |

| | 666 | bbb | 2016-12-30 | 8 |

+-+

3 rows in set (0.00 sec)

The first three records in descending order

Mysql > select * from emp1 order by age1 desc limit 3

+-+

| | age1 | ename | birth | deptno | |

+-+

| | 888 | eee | 2015-11-30 | 22 | |

| | 666 | bbb | 2016-12-30 | 8 |

| | 666 | ddd | 2014-12-22 | 11 | |

+-+

3 rows in set (0.00 sec)

Displays the first four records starting with the second record, sorted by the age1 field

Mysql > select * from emp1 order by age1 limit 1 and 4

+-+

| | age1 | ename | birth | deptno | |

+-+

| | 333 | fff | 2011-04-30 | 8 |

| | 666 | bbb | 2016-12-30 | 8 |

| | 666 | ddd | 2014-12-22 | 11 | |

| | 888 | eee | 2015-11-30 | 22 | |

+-+

4 rows in set (0.00 sec)

3 example of DCL statement:

Create a new user and authorize

Mysql > grant select,insert on mysql.* to 'abc'@'localhost' identified by' abc'

Query OK, 0 rows affected (0.08 sec)

Revoke permissions

Mysql > revoke insert on mysql.* from 'abc'@'localhost'

Query OK, 0 rows affected (0.00 sec)

View current user permissions

Mysql > show grants

+-- +

| | Grants for root@localhost |

+-- +

| | GRANT ALL PRIVILEGES ON *. * TO 'root'@'localhost' WITH GRANT OPTION |

| | GRANT PROXY ON'@''TO 'root'@'localhost' WITH GRANT OPTION |

+-- +

View a user right

Mysql > show grants for 'abc'@'localhost'

+-+

| | Grants for abc@localhost |

+-+

| | GRANT USAGE ON *. * TO 'abc'@'localhost' IDENTIFIED BY PASSWORD' * 0D3CED9BEC10A777AEC23CCC353A8C08A633045E' | |

| | GRANT SELECT ON `mysql`. * TO 'abc'@'localhost' |

+-+

2 rows in set (0.00 sec)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.