This is the 5th day of my participation in the August More Text Challenge

1. Data types

The types of data fields defined in mysql are very important for database optimization. The types supported in mysql are roughly divided into: numeric, date/time, and string (character) types

1) Numerical types are shown as follows:

2) Date and time types

3) String type

4) integer:

5) Floating point:

6) Character type:

2. Common SQL commands:

1) select command
-- Print the current date and time select now(); Select curdate() from curdate(); Select curtime() from curtime(); Select database(); Select version(); -- Print the current user select user(); -- Show variables;# display variable
show global variables;  Display global variables
show global variables like '%version%';
show variables like '%storage_engine%'; 		The default storage engineLike and "_" fuzzy search is also available to userswhereWords. MySQL provides two wildcards for use with the LIKE operator: the percentage symbol % and the underscore _. The percentage (%) indicates that the wildcard is allowed to match zero or more characters of any string. The underscore (_) indicates that the wildcard is allowed to match any single character.Copy the code
2) Underscore indicates that any single character is wildcard
mysql> mysql> create database zmedu; Query OK, 1 row affected (0.00 SEC) mysql> use zmedu Database changed mysql> create table students(ID int(11),stname char(20)); Query OK, 0 rows affected (0.01sec) mysql> insert into students values(1,'liuyong'), (1,'zhangru'), (3,'laowang'), (4,'tongtong');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from students where stname like '% _'; +------+----------+ | id | stname | +------+----------+ | 1 | liuyong | | 1 | zhangru | | 3 | laowang | | 4 | tongtong |  +------+----------+ 4 rowsin set(0.00 SEC) mysql >Copy the code
3) Like, like, not like

Not like the instance

mysql> select * from students where stname not like '%g';
+------+---------+
| id   | stname  |
+------+---------+
|    1 | zhangru |
+------+---------+
1 row in set (0.00 sec)

Copy the code
4) Check the system running status
Check the system running status: mysql> show status;Check the running status
mysql> show global status like 'Thread%'; 		Display global status

Copy the code

Logical operation and or not and and or or not not

Example:

The import book. SQL
mysql> select bName,publishing,price from books whereprice=30 or price=40 or price=50 or price=60; +--------------------------------------+--------------------------+-------+ | bName | publishing | price | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- + | | Illustrator 10 completely manual science press 50 | | | FreeHand 10 basic tutorial | Beijing hope electronic publishing 50 | | | website design the entire tutorial 50 | | science press | | ASP database instance navigation system development | people's posts and telecommunications publishing house 60 | | | | Delphi 5 program design and the control reference Electronic industry press 60 | | | the ASP database instance navigation system development of posts and telecommunications publishing house 60 | | | people +--------------------------------------+--------------------------+-------+ 6 rowsin set (0.00 sec)

Copy the code
5). Arithmetic operators

= equal to <> not equal to! =

Is greater than

< <

= greater than or equal to

<= is less than or equal to

The IN operator is used in WHERE expressions to support multiple selections in the form of list items. The syntax is as follows: WHERE column in (value1,value2…) WHERE column NOT IN (value1,value2,…)

When preceded by the not operator, in indicates the opposite of in, that is, not to be selected from these list items

mysql> select bName,price from books where price >60;
mysql> select bName,price from books whereprice =60; +--------------------------------------+-------+ | bName | price | +--------------------------------------+-------+ | ASP database instance navigation system development 60 | | | Delphi 5 program design and control reference 60 | | | the ASP database instance navigation system development | | 60 +--------------------------------------+-------+ 3 rowsin setMysql > select bName,price from bookswhere price in(50,60,70); +--------------------------------------+-------+ | bName | price | +--------------------------------------+-------+ | Illustrator 10 completely manual 50 | | | FreeHand 10 cs 50 | | | website design the entire tutorial 50 | | | the ASP database instance navigation system development 60 | | | Delphi 5 | | 60 program design and the control reference | the ASP database instance navigation system development 60 | | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + 6 rowsin set (0.00 sec)

Copy the code
6.)

Ascending: order by ASC default descending: oredr by desc

mysql> select bName,price from books where price in(50,60,70) order by price asc ->; +--------------------------------------+-------+ | bName | price | +--------------------------------------+-------+ | Illustrator 10 completely manual 50 | | | FreeHand 10 cs 50 | | | website design the entire tutorial 50 | | | the ASP database instance navigation system development 60 | | | Delphi 5 | | 60 program design and the control reference | the ASP database instance navigation system development 60 | | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + 6 rowsin set (0.00 sec)

Copy the code
7). Range operation

[not]between …. and…. Between and can be replaced with greater than or less, and a more explicit example of greater than or less: find titles and prices that are not Between 30 and 60

mysql> select bName,price from books whereprice not between 30 and 60 order by price desc; +---------------------------------------------------------+-------+ | bName | price | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- + | Javascript and Jscript from entry to master | 7500 | | XML fully explore | 104 | | 3 primary ASP tutorial | 104 | | 7.0 SQL Server database system management and application development | 95 | | SQL Server 2000 from entry to master | 93 | | 3 d Studio Max comprehensive use | 91 | 3 | lllustrator nine treasure dian | 83 | | 3 d MAX R3 animation production and training tutorial | 73 | | 72 | | HTML design practice | 2000 & Frontpage ASP web design skills and website maintenance | 71 | | Auto CAD R14 Chinese practical tutorial | 64 | | into Flash 5 tutorial | 64 | | master Javascript | 63 | | 3 ds MAX 4 was born | 63 | | Auto CAD | 63 | 2002 Chinese practical tutorial Live learning Delphi5 | | 62 | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- + 16 rowsin set (0.00 sec)


Copy the code
8) fuzzy matching

Field name [not]like ‘wildcard’ —- % Any multiple characters

mysql> select bName from books where bName like '% program %'; + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | bName | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | network program and the design of asp | | Delphi 5 programming and control reference | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 2 rowsin set (0.00 sec)

Copy the code
9). Mysql subquery

Concept: Select appears in the SELECT where condition, query nested query

mysql> select bName,bTypeId from books where bTypeId=(select bTypeId from category where bTypeName='Network Technology'); + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- + | bName | bTypeId | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- + 7 | | | Internet operation technology +----------------------+---------+ 1 rowin set (0.00 sec)

Copy the code
10).limit specifies the items to be displayed

The SELECT * FROM table LIMIT [offset,] rows offset clause can be used to force a SELECT statement to return a specified number of records. LIMIT accepts one or two numeric parameters. The argument must be an integer constant. If you are given two arguments, the first parameter specifies the offset of the first row to return, and the second parameter specifies the maximum number of rows to return. The offset of the initial record row is 0 (not 1).

For example, select * from table limit m,n statement indicates that m is the index at the beginning of the record, starting from 0, indicating that the first record n is the index at the beginning of m+1.

Look up rows 2 through 6 in the category table. First of all, rows 2 through 6 have 2, 3, 4, 5, and 6 have 5 numbers, starting at 2, offset by 1

mysql> select * from category limit1, 5; + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | bTypeId | bTypeName | + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | | site 2 | 3 | 3 d animation | | | 4 Linux learning | | 5 | Delphi study | | | | hacker 6 + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + 5 rowsin set (0.00 sec)
Copy the code

conclusion

MySQL statement learning, need a lot of practice