Common structured SQL statements for database Skill combat Advanced (top)

The commonly used Structured query language consists of data definition language (DDL), Data Manipulation language (DML), Data Control Language (DCL) and data Query Language (DQL). Especially in relational databases such as (MySQL, mariadb, Percona, DB2, Oracle, SQL Server) are all using common SQL statements to implement additions and deletions and other data management. This article describes the following four types of structured SQL.

DDL data Definition Language Create drop ALTERDML data manipulation language Insert Delete UPDATEDCL Data Control Language Grant commit ROLLBACKDQL data Query Language Select

First, Create and drop introduction

First create and drop are directly capable of manipulating the database, such as creating a database and deleting a database. A simple example is below,

CREATE DATABASE IF not EXISTS DBMS default character set UTF8; #创建一个名为DBMS默认字符集为UTF8的数据库SHOW databases; #显示当前数据库管理系统里面所有的数据库, you can see the database DBMS that we are currently creating in the DBMS Library; #删除名为DBMS的数据库

Second, create and drop can manipulate the table, and the operation of the library will change database to table, creating and deleting operations. After we want to contact the additions and deletions to change, so create the following table SQL as follows:

CREATE table if not exists user (ID int. unsigned NOT NULL Auto_increment primary key, username varchar (+) not NULL, password varchar (+) not null), or use SQL below. Actually the principle and the table structure is the same, just renders the way not the same as the CREATE table if not exists ' user ' (' id ' int (unsigned) ', ' auto_increment ', ' username ' V Archar (+) not NULL, ' password ' varchar (+) NOT NULL, PRIMARY KEY (' id ')) engine=innodb DEFAULT Charset=utf8

Inserting data Insert

    Insert is one of the most common ways we write data into a database, with the following syntax:

 insert  into  tablename   (column )  values ( );   # tablename  is the name of the target table in which we want to insert the data, column is the name of the columns, the values inside the value that you want to insert, need to be strong   when we have all columns inserted in values () corresponding to the column order to create the table structure, you can omit ( column )  insert  into  tablename   values (",", " ");  For example:  insert  into  user    values (1, ' Linux ', ' Linux '  ); But the ID is set to the primary key self-increment so there is no need to do,  use the above column name corresponding to the   optimization after: insert  into  user  (username, password )    value (' Python ', ' Java '  )   #批量插入数据   We can also use  insert   into  tablename   (column )  values (  value 1), (value 2) ... In the way of bulk insertion   efficiency is much higher.  isert into  user  (username,password )    values (' python999 '), 334989 ), (' Linuxmysql ', ' 777 ')   or   insert  into  user  (username, password )    value(' Python ', ' Java '  ), (' Linuxmysql ', ' 777 ')    Note: As tested values and value are inserted as values, it is important to note that the defined password is a varchar () character type,   3334989 is the shape, and it will be inserted successfully, this is to convert the shaping into a string type.

Methods for inserting data into a database in addition to insert, load data infile (loading), as well as the method of querying from other tables, can refer to previous articles, and even can be imported with client tools such as Navicat.

Third, delete data

Delete data can be divided into delete parts and delete all, delete all inside can be divided into only delete data and table and data together delete, delete table and data, can use the above mentioned drop table tablename, delete all data can be deleted from TableName or T Uncate table.

The difference between the delete from table and the Tuncate table:

Delete Does not refresh the value of the primary key after deletion for example, if you delete the primary key ID (3-5), the next time you insert the data is starting from 5.

delete     from user   #删除所有数据   insert  into   user  (username,password )    value (' Pythonlinux ', ' Javadocker '  ), (' Linuxmysql ', ' Redis ') select  *  from  user; #插入数据再查询查询所有数据, check our id:select    * from user 11    python    java12     LInuxmysql    77713    pythonlinux     javadocker14    LInuxmysql    redis  Watch the data inside the table, You can see that the ID of the first line starts at 11, which means that delete deletes the data without refreshing the self-increment  truncate  user  reinsert data watch  truncate   user# Clear All data  insert  into  user  (username,password )     VALUE (' Pythonlinux ', ' Javadocker '  ), (' Linuxmysql ', ' Redis ') select  *  from   User, #再插入数据, then query 1    pythonlinux    javadocker2    linuxmysql    redis  truncate  after emptying the table   inserting the data, the self-increment column starts at 0. Delete part of the data first insert more than one, check the inserted data, insert  into  user  (username,password )     value (' python ', ' Javadocker '  ), (' Linux ', ' Redis '), (' Linux ', 999), (' Redis ', ' name  ') 1     pythonlinux    javadocker2    LInuxmysql     redis3    python    javadocker4    linux     redis5    linux    9996     redis    name 7    python     javadocker8    linux    redis9    linux     99910    redis    name 11   & nbsp;p ython    javadocker12    linux    redis13     linux    99914    redis    name   Delete part of   id  >10 Delete   from user  where id  >10;  then insert the data and the query results are as follows: 1    pythonlinux    javadocker2     LInuxmysql    redis3    python     javadocker4    LInux    redis5     linux    9996    redis    name 7     python    javadocker8    LInux     redis9    linux    99910    redis     name 15    python    javadocker16    linux     redis17    linux    99918    redis     name   id is discontinuous compared to the above results.

Iv. Condition judgment and limitation

conditional judgments are mainly done based on mathematical operators, logical operators, and comparison operators.

1) Mathematical operator: +,-,*,% 2) logical operator &&,| |,and,or, in, between And3) comparison operator: =,!=,>=,<=,>,<,<>

V. Change the data or structure of the table

Data changes are done using update, and the table structure changes depend on alter completion, the next step is to introduce update change data, update and delete, the condition is not tight enough to cause the data to overwrite, is a more dangerous operation, so must be careful.

Basic syntax: Update table set column= "" Where to judge the condition;

1    pythonlinux    javadocker2    linuxmysql     redis3    python    javadocker4     LInux    redis5    linux    9996     redis    name 7    python     javadocker8    linux    redis9    linux     99910    redis    name 15     python    javadocker16    LInux     redis17    linux    99918    redis     name update user set  username= ' Kailinux '  where id in   (1,7,9) after the change: (SQL statement is generally performed first the conditional section first lock row and then lock the column) 1    kailinux    javadocker2    LInuxmysql     redis3    python    javadocker4     LInux    redis5    linux    9996     redis    name 7    kailinux     javadocker8    linux    redis9    kailinux     99910    redis    name 15     python    javadocker16    linux    redis17     linux    99918    redis     name

Vi. Querying data

The SQL that queries the data is our most frequently used SQL, and these SQL optimizations represent a person's familiarity with the database, so we will involve a lot of knowledge points in the query section.

1) query all and query individual fields

Select Username,password form User;

2) alias the partial column

Select Username as loginname form user; Select username loginame, password from user; # Note the new column names and old column names are separated by spaces

3) Remove duplicate values Distinct

Select distinct username from user; Select DISTINCT username loginame, password from user;

4) querying with a Where condition

SELECT * FROM User where * * *;

5) query for null values
Although we generally set the NOT NULL when creating the table structure, but also some tables will appear null, for example, we change the user table, the table contains the following data:

1    kailinux    javadocker2    linuxmysql     null    null is a character null3    python     NULL4    LInux    redis5    linux     06    redis    name 7     kailinux    javadocker8    linux    redis9     kailinux          #值为 '   ' 10     redis    name 15    python     javadocker16    linux         #值为 '   ' 17     linux    99918    redis    name  19    ubuntu        #值为默认的NULL20     centos        #值为默认的NULL

After analysis, there are not only null values in the table and ' values, we will now look at the null and for the query, for NULL is generally not used, the default is 0, the string pattern is ""

SELECT * from user where password= '; result ID username password9 kailinux LInux #查找password列为NULL的方法select * from user where password=null result is empty select * from user where password= ' NULL ' result is: 2 linuxmysql NULL3 python NU Llselect * from user where password is null; The result is: Ubuntu CentOS So when we want to query the default NULL, we need to use the IS NULL query

6) Condition judgment in and between...and ...

Where condition to judge

7) Like fuzzy query

Like in%p matches any number of characters _ underscore matches one character

SELECT * from user where username like '%linux% ' #查询用户中包含linux的用户, case insensitive 1 kailinux javadocker2 linuxmysql NUL L4 linux redis5 linux kailinux javadocker8 linux redis9 kailinux Linux-lin UX 999

8) Use regular expressions

MySQL is a very cow database, not only the function of many, but also support regular expression, next we look at the regular expression matching. RegExp efficiency would be almost as close as like

RegExp inside the regular

select * from user where username regexp  ' ^linux '   # Results starting with Linux: Case insensitive 2    linuxmysql    null4     linux    redis5    linux    08     LInux    redis16    LInux    17     linux    999select * from  user  where    username regexp  ' Python|redis '  ;  #用户名中包含redis和python的3      python    null6    redis    name 10     redis    name 15    python     javadocker18    redis    name 21     pythonmysql    22    rediswinner    23    pythonmysql     24    winnerredis    select * from  user   where   username regexp  '  python$|redis$|234$ '  ;  query with Python , redis 234 end of user name 6    redis    name 10     redis    name 18    redis    name  24    winnerredis    25    pythoredis     27    python234    28    winner1234

9 Sorting Query Results   Order by   desc  ASC

select  *   from user order by id;  #默认升序1      kailinux    javadocker2    LInuxmysql     null3    python    null4    linux     redis5    linux    0 .......   # Partial results Omit 23    pythonmysql    24    winnerredis     25    PYTHoREDIS    26     redispyhon    27    python234    28     winner1234    select  *   from user order  by id desc;   #降序排列  desc Descending 28    winner123427     python23426    redispyhon25    pythoredis.......     #部分结果省略8      linux7    kailinux6    redis5    linux4     LInux3    python2    LInuxmysql1     kailinuxselect  *   from user order by id asc;    Results:1    kailinux    javadocker2     linuxmysql    null3    python    null4     LInux    redis5    linux    0  .......    #部分结果省略23     PYthonmysql    24     winnerredis    25    pythoredis    26     redispyhon    27    python234    28     winner1234    The default sort method is ASC mode

Commonly used structured query the content of the basic end, in the next section from the continuation of the query part of the content, we often use the structure of the query statement, due to my limited level, the content of the lack of wonderful fragments, but willing to share with you friends to learn, please friends more guidance!

This article is from the "Keep Dreaming" blog, please be sure to keep this source http://dreamlinux.blog.51cto.com/9079323/1908264

Common structured SQL statements for database Skill combat Advanced (top)