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

After building the mysql database, you can practice SQL statements to achieve the purpose of skilled operation of the database. SQL statements are mainly divided into additions, deletions, modifications and queries. Practice makes perfect.

First set up a data table to facilitate the exercise of adding, deleting, changing and checking. Here, write out the statement to create the table and execute it first. Later, I will learn how to create a data table.

SET NAMES utf8;SET FOREIGN_KEY_CHECKS = 0Mutual-Table structure for `websites`-DROP TABLE IF EXISTS `websites` CREATE TABLE `websites` (`id`websites` (11) NOT NULL AUTO_INCREMENT, `name` char (20) NOT NULL DEFAULT''COMMENT' site name', `url`varchar (255) NOT NULL DEFAULT'', `alexa`int (11) NOT NULL DEFAULT'0' COMMENT 'Alexa ranking', `roomy`char (10) NOT NULL DEFAULT''COMMENT' country', PRIMARY KEY (`id`) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 -Records of `websites`-BEGIN INSERT INTO `websites` VALUES ('1Qing,' Google', 'https://www.google.cm/',' 1percent, 'USA'), (' 2percent, 'Taobao', 'https://www.taobao.com/',' 13percent, 'CN'), (' 3percent, 'rookie tutorials', 'http://www.runoob.com/',' 4689, 'CN'), (' 4percent, 'Weibo' 'http://weibo.com/',' 20, 'CN'), (' 5', 'Facebook',' https://www.facebook.com/','3), 'USA') COMMIT;SET FOREIGN_KEY_CHECKS = 1

1.select is used to query data

Example:

Select * from websites; query the websites table data created. Notice the semicolon that follows. The semicolon indicates the end of a SQL command.

Select * from websites where name=' Taobao'; query the data of 'Taobao' in the websites table, and the number means to query all the data in the websites table. The number here can be written as a column of the table.

Select name from websites; or select name from websites where country='CN'

2.select disinct is used to return a unique different value.

Example:

Select distinct country from websites; queries the unique different values of country in the websites table.

The 3.where clause is used to extract data of specified criteria.

Operator

, =, 50 select * FROM Websites WHERE country='CN' or alexa > 50

Combined application

Select * from websites where alexa > 15 and (country='CN' or country='US')

5.order by sorts the values of the query, order by ascending, and desc is descending

Example:

Select * from websites order by alexa; sorts ascending by the values of the alexa column select * from websites order by country,alexa desc; sorts in descending order by the values of country and Alexa (where country takes precedence over Alexa because country comes first)

6.insert into inserts new data into the table.

Example:

Insert into websites (name,url,alexa,country) values ('Baidu', 'https://www.baidu.com/','4','CN')'

7.update is used to update records that already exist in the table

Example:

Update websites set alexa = '5000', country = 'USA' where name =' rookie tutorial'

Note that if you do not add where, the data of the entire websites table will be changed. Therefore, in the actual production environment, administrators will restrict the use of update. If you use update without adding where, an error will be reported.

Delete data from delete, drop and truncate (the deleted data from drop and truncate cannot be recovered, so be careful before using it and prepare for data backup)

Delete is used to delete data in a table (you can delete data in an entire table or a piece of data in a table) delete from websites where name=' rookie tutorial; delete table websites; or delete * from websites;drop tables websites; delete websites data table drop database mysql; delete mysql database truncate tables websites delete table data, the framework of the table is still retained, but the data cannot be recovered, which is the difference from delete

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.