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

Next, let's learn about the common commands and examples of MySQL query statements. I believe you will benefit a lot after reading it. The text is not much in essence. I hope that the common commands and examples of MySQL query sentences are what you want.

Syntax format of related commands

1.1 add Field

Alter table tb_name add column type [not null | null] [primary key] [uniqe] [auto_increment] [default value] alter table tb_name add after

1.2 deleting a field

Alter table tb_name drop field name

1.3 modify field type

New type of alter table tb_name modify field

1.4 modify field name and type

Alter table tb_name change

1.5 modify the table name

Alter table OLD_tb_name rename NEW_tb_name

1.6 Delete the table (and avoid reporting errors)

Drop table [fi (not) exists] tb_name

1.7 actions on rows in the table-insert

Syntax: insert [into] tb_name [(field list)] values | value (expression | null | default,...), (expression | null | default...) insert [into] tb_name set field name = value,... insert and insert. The difference between set is that the latter can have subqueries.

1.8 actions on rows in the table-update

Update tb_name set field name = value,... [where condition]; default omission is to show all records should be used with caution

1.9 Operation of rows in the table-delete

Delete from tb_ name [where condition]; as above, delete all records without the restriction of where condition

1.10 actions on rows in the table-select

Select field list from tb_name [as tb_alias] [where condition]; when using select, the * sign indicates all fields

Note: the alias of the table can effectively reduce the length indicated.

Mysql > select xx.name,fsb.id from xiaoxiong as xx,fengshenbang as fsb where xx.id=fsb.id +-+-+ | name | id | +-+-+ | Zhangfei | 2 | zhaoyun | 2 | liubei | 2 | xiaoqiao | 2 | Zhangfei | 2 | zhaoyun | 2 | liubei | 2 | xiaoqiao | 2 | +-+-+ 8 rows in set (0.00 sec)

Summary: select returns the read operation to the database, while insert, update and delete only return the number of records affected by this operation; it belongs to write operation.

Read operation command

2.1 select view system information of MySQL database

Mysql > select now (); View current date and time mysql > select curdate (); View current date mysql > select curtime (); View current time mysql > select database (); View current default database mysql > select version (); View current mysql database version mysql > select user (); View current login users

2.2 show view system information

Mysql > show processlist; view current links mysql > show variables\ G; view current system information mysql > show global variables\ G; view global configuration information mysql > show global variables like'% version%' view system version number county magistrate booing% indicates 0 or more unknown characters mysql > show global variables like'% storage_engine%'; view current default storage engine mysql > show engines; view currently supported storage engine mysql > show status View current system status mysql > show global status like 'Thread%'; to view the current number of threads

III. Backup and restore of database

3.1 Database backup:

Syntax: mysqldump-u-p sql_name > sql_name.sql

Import database: source / database backup file

[root@node3 ~] # mysqldump-usys_neme-psys_password xiaoxiong > xiaoxiong.sql backup database mysql > create database xiaoxiong; needs to create a database before importing the database; mysql > use xiaoxiong; set the default database mysql > source / root/xiaoxiong.sql; import the backed up database mysql > show tables; to view the imported database

3.2 Export to a text file through the results of select

The mysql > select * into outfile'/ tmp/xiaoxiong.txt' from xiaoxiong; / tmp directory is the mysql > select ID,uuid,name,sex from xiaoxiong where sex like 'M'into outfile'/ tmp/bak_xiaoxiong.txt';Query OK where sql users are allowed to create files, 3 rows affected (0.00 sec)

IV. The use of logical operators in MySQL

The following actions will be done in the database book

4.1View the table and getable fields contained in the book database

Mysql > desc category +-+ | Field | Type | Null | Key | Default | Extra | +- +-+ | bTypeId | int (4) | NO | PRI | NULL | auto_increment | | bTypeName | varchar (40) | YES | | NULL | | +- -+-+ 2 rows in set (0.00 sec) mysql > mysql > desc books +-+-- + | Field | Type | | Null | Key | Default | Extra | +-+ -+ | bId | int (4) | NO | PRI | NULL | auto_increment | | bName | varchar | YES | | NULL | | bTypeId | enum ('1') "2", "3", "4", "5", "6", "7", "8", "9' '10') | YES | | NULL | publishing | varchar | YES | | NULL | | price | int (4) | YES | | NULL | | pubDate | date | | YES | | NULL | | author | varchar (30) | YES | | NULL | | ISBN | varchar (255) | YES | | NULL | | +-| -+

8 rows in set (0.00 sec)

4.2 query through conditional constraint statements

Find out the names of books with prices between 40 and 70, and list prices and publishers

Mysql > select bName,price,publishing from books where price > 40 and price, less than = and less than or equal to select bName,price,publishing from books where price in (40, 50, 60, 70)

Not in and in have the opposite effect.

Mysql > select bName,price,publishing from books where price not in (40, 50, 60, 70)

5. Sort operation-order by

Ascending order: the default sort in MySQL is to output asc in ascending order

Descending order: order by "sort field" desc

Mysql > select bName,price from books where price > 40 and price select bName,price from books where price in (40, 50, 50, 60, 70) order by price desc +-- +-+ | bName | price | +-- +-+ | ASP database system Development instance navigation | 60 | | Delphi 5 programming and control reference | 60 | | ASP database system development instance navigation | 60 | | Illustrator 10 complete manual | 50 | | FreeHand 10 basic tutorial | 50 | | website design tutorial | 50 | + -- +-+ 6 rows in set (0.00 sec)

VI. Range operation

[not] between... And...; between... And... Use an operation equivalent to an expression that is greater than and equal to, but the meaning is unclear; for example: mysql > select bName,price from books where price between 40 and 60 order by price desc

7. The use of fuzzy field query-like

Syntax: [not] like 'wildcard' where% means multiple characters

For example, look up the title of the book with the word "web page" in the table books and print it out.

Mysql > select bName from books where bName like 'web page' +-- + | bName | +-- -+ | Page style design-CSS | | Dreamweaver 4 web page creation | | Fireworks 4 web page graphic design | | Web interface design art tutorial | Frontpage 2000 & ASP web page design skills and website maintenance | +-- + 5 rows in set (0.00 sec) |

8. Application of subquery

Syntax: the nested statement of the select query appears in the where condition of select in the query statement.

Mysql > select bName,bTypeId,price from books where bTypeId= (select bTypeId from category where bTypeName='***') +-- + | bName | bTypeId | price | +-- + | * * and network security | 6 | 41 | | * Prevention Secrets | 6 | 44 | +-+ 2 rows in set (0.00 sec)

9. Limit the entries displayed by the query results

Syntax: select * from tb_name LIMIT [n,] m

The limit clause, which can be used by the select statement to return a specified number of records, LIMIT receives one or two parameters, which must be an integer constant. Given a contiguous parameter, the first parameter specifies the offset of the first returned record row, and the second specifies the maximum number of record rows returned. The initial row record offset is 0

Find the records in rows 5 to 10 of the books table, with a total of 6 numbers

Mysql > mysql > select * from books limit 4pm 6 +-- -+ | bId | bName | bTypeId | publishing | price | pubDate | author | ISBN | +-+-- +- -+ | 5 | * Prevention tips | 6 | Beijing Tengtu Electronic Publishing House | 44 | 2003-06-29 | Zhao Lei Rain | 7120000233 | 6 | Dreamweaver 4 introduction and improvement | 2 | Tsinghua University Press | 44 | 2004-06-01 | Yue Yubo | 7505397699 | 7 | Web page style design-CSS | 2 | people's posts and Telecommunications Publishing House | 45 | 2002-03-01 | Zhang Xiaoyang | 7505383663 | | 8 | | Internet Operation Technology | 7 | Tsinghua University Press | 45 | 2002-02-01 | Xiao Ming | 7121003023 | 9 | Dreamweaver 4 web creation | 2 | Tsinghua University Press | 45 | 2004-04-01 | Huang Yu | 7505380796 | 10 | 3D MAX 3.0 creation effect | Example | 3 | Beijing Wanshui Electronic Information Publishing House | 45 | 2002-09-01 | Geng Ying | 7505380796 | +- -+

9.2 check the title and price of the cheapest of all books

Mysql > select bName,price from books order by price limit 1 +-+-+ | bName | price | +-+-+ | website creation through train | 34 | +-+ -+ 1 row in set (0.00 sec)

X. comprehensive application

10.1 shows the bId,bName,bTypeId content on condition that it is cheaper than the cheapest book in the Electronic Industry Press

Mysql > select bId,bName,bTypeId from books where price

< (select price from books where publishing="电子工业出版社"order by price asc limit 1);+-----+--------------------------------------------------------+---------+| bId | bName | bTypeId |+-----+--------------------------------------------------------+---------+| 1 | 网站制作直通车 | 2 || 2 | ***与网络安全 | 6 || 3 | 网络程序与设计－asp | 2 || 4 | pagemaker 7.0短期培训教程 | 9 || 5 | ******防范秘笈 | 6 || 6 | Dreamweaver 4入门与提高 | 2 || 7 | 网页样式设计－CSS | 2 || 8 | Internet操作技术 | 7 || 9 | Dreamweaver 4网页制作 | 2 || 10 | 3D MAX 3.0 创作效果百例 | 3 || 11 | Auto CAD职业技能培训教程 | 10 || 12 | Fireworks 4网页图形制作 | 2 || 13 | 自己动手建立企业局域网 | 8 || 14 | 页面特效精彩实例制作 | 2 || 15 | 平面设计制作整合案例详解－页面设计卷 | 2 || 16 | Illustrator 10完全手册 | 9 || 17 | FreeHand 10基础教程 | 9 || 18 | 网站设计全程教程 | 2 || 19 | 动态页面技术－HTML 4.0使用详解 | 2 || 20 | Auto CAD 3D模型大师 | 10 || 21 | Linux傻瓜书 | 4 || 22 | 网页界面设计艺术教程 | 2 || 23 | Flash MX 标准教程 | 2 || 24 | Auto CAD 2000 应用及实例基集锦 | 10 || 25 | Access 2000应用及实例基集锦 | 1 |mysql>

Select bName,price from books where price select * from books where price

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.