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

The commonly used structured query language is mainly divided into 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 and so on, common SQL statements are used to manage data such as additions, deletions, changes and queries. This article will introduce 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

I. brief introduction of create and drop

First of all, create and drop can directly operate on the database, such as creating and deleting the database. A simple example is as follows

CREATE DATABASE IF NOT EXISTS DBMS default character set utf8; # create a database called DBMS default character set UTF8 SHOW databases; # display all the databases in the current database management system, you can see that our currently created DBMS library Drop database DBMS; # deletes the database named DBMS

Second, create and drop can operate on the table, changing database to table in accordance with the operation on the library to create and delete operations. Later, we will contact the addition, deletion, modification and search, 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 (30) not null, password varchar (30) not null), or use the SQL below. In fact, the principle is the same as the table structure, except that the rendering method is different: CREATE TABLE if not exists `user` (`id` int (10) unsigned NOT NULL AUTO_INCREMENT, `username` varchar (30) NOT NULL, `password` varchar (30) NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8

2. Insert data insert

Insert is one of the most common ways to write data to the database. The common syntax is as follows:

Insert into tablename (COLUMN) VALUEs (); # tablename is the target table name where we want to insert data, column is the column name, and values corresponds to the value to be inserted, which needs to be strongly stressed. When we insert all columns, that is, the corresponding column in values () corresponds to the column order in which the table structure is created, we can omit (COLUMN) insert into tablename VALUEs (',','); for example: insert into user VALUEs (1) But set the primary key to id self-increment, so you don't have to do it. Just use the column name above. After optimization: insert into user (username,password) VALUE ('python','java') # We can also insert data in bulk with insert into tablename (COLUMN) VALUEs (value 1), (value 2). The way of bulk insertion is much more efficient. Isert into user (username,password) VALUEs ('python999',334989), (' LInuxmysql','777') or insert into user (username,password) VALUE ('python','java'), (' LInuxmysql','777') Note: when testing the insertion value of values and value, it is important to note that the defined password is varchar () character type, while 3334989 is × ×, which will also be inserted successfully. Here, the × × is converted into a string type.

In addition to insert, there are ways to insert data into a database, such as load data infile (load files), and methods to query inserts from other tables, which can be referenced in previous articles or even imported using client tools such as navicat.

3. Delete data

Delete data can be divided into delete part and delete all, delete all can be divided into only delete data and tables and data together delete, delete tables and data, you can use the above-mentioned drop table tablename, delete all data can use delete from tablename or tuncate table.

The difference between delete from table and tuncate table:

The value of the primary key will not be refreshed after the delete is deleted. For example, if you delete the primary key ID (3-5), the next time you insert data will start at 5.

Delete from user # Delete all data insert into user (username,password) VALUE ('pythonlinux','javadocker'), (' LInuxmysql','redis') select * from user # insert data and query all the data. Check the data in our id:select * from user 11 python java12 LInuxmysql 77713 pythonlinux javadocker14 LInuxmysql redis observation table. We can find that the id in the first row starts at 11. This means that when delete deletes data, it will not refresh the self-added truncate user and then insert data to observe that truncate user# clears all data insert into user (username,password) VALUE ('pythonlinux','javadocker'), (' LInuxmysql','redis') select * from user. # insert the data again, query 1 pythonlinux javadocker2 LInuxmysql redis truncate to empty the table, and then insert the data, starting from 0. Delete part of the data and insert multiple pieces of data to 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 python javadocker12 LInux redis13 linux 99914 redis name delete delete from user where id > 10 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 discontiguous compared to the above results.

IV. Condition judgment and restriction

Conditional judgment is mainly based on mathematical operators, logical operators and comparison operators.

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

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.