Shulou(Shulou.com)05/31 Report--

This article mainly shows you "what is the use of Mysql", the content is easy to understand, clear, hope to help you solve your doubts, the following let the editor lead you to study and learn "what is the use of Mysql" this article.

# Login

-hlocalhost-uroot-p

# change password

Mysqladmin-uroot-pold password new

Www.2cto.com

# display the database

Show databases

# display data sheet

Show tables

# Select a database

Use examples

# create a database and set up encoding utf-8 in multiple languages

Create database `examples` default character set utf8 collate utf8_general_ci

# Delete database

Drop database examples

# create a table

Create table test (

Id int (10) unsigned zerofill not null auto_increment

Email varchar (40) not null

Ip varchar (15) not null

State int (10) not null default'- 1'

Primary key (id)

) engine=InnoDB

# display table structure

Describe

# Delete table

Drop table test

# rename the table

Alter table test_old rename test_new

# add column

Alter table test add cn int (4) not null

# modify columns

Alter table test change id id1 varchar (10) not null

# Delete columns

Alter table test drop cn

# create an index

Alter table test add index (cn,id)

# Delete index

Alter table test drop index cn

# insert data

Insert into test (id,email,ip,state) values (127.0.0.1) values (127.0.0.1)

# Delete data

Delete from test where id = 1

# modify data

Update test set id='1',email='q@qq.com' where id=1

# check data

Select * from test; # fetch all data

Select * from test limit 0Pol 2; # fetch the first two pieces of data

Select * from test email like'% qq%' # look for the qq character _ for one% for more than one

Select * from test order by id asc;# descending desc

Select * from test id not in ('2century recordings 3'); # id does not contain 2meme 3 or remove not to indicate that it contains

Select * from test timer between 1 and 10 witch # data is between 1 and 10

Www.2cto.com

#-knowledge of table joins

# equivalent join also known as inner link inner join only returns rows with equal join fields in two tables

Select * from An inner join B on A.id = B.ID; # write 1

Select * from A Magi B where A.id = B.ID; # Writing 2

Select a.idpene a.title from An an inner join B b on a.id=b.id and a.idquarter1witz # temporary name of table 3

Select a.id as ID,a.title as title from An inner join B on A. idler B.idscape # add as sentence

# left join is also called external join left join returns all records in the left table that are equal to the join fields in the right table

Select * from A left join B on A.id = B.id

Select * from A left join (Bjens Cpene D) on (B.i1=A.i1 and C.i2=A.i2 and D.i3 = A.i3); # complex connections

# right join also called external connection right join returns all records in the right table that are equal to the join fields in the left table

Select * from A right join B on A.id = B.id

# full external link full join returns all the data in the left and right tables

Select * from A full join B on A.id = B.id

# Cross-connect returns Qadir product without where words

Select * from A cross join B

-end of table connection-

-Index creation--

Show index from A # View Index

Alter table An add primary key (id) # Primary key Index

Alter table An add unique (name) # unique index

Alter table An add index name (name) # General Index

Alter table An add fulltext (name) # full text Index

Alter table An add index name (id,name) # Multi-column index

# Common functions

Abs (- 1) # absolute value

Pi () # pi value

Sqrt (2) # square root

Mod (- 5pm 3) # take residue-2

Ceil # carry + 1 result 11 ceil (10.0) result 10

Floor (10.6) # rounded 10

Round (2.5) # rounded to integer result 3

Round (2.5) # preserves two decimal places and results 2.50

Truncate (2.5234) # the last 3 decimal places are not rounded up to 2.523

Sign (- 2); # sign function returns-1 0 or 0 positive number returns 1

Pow (2), exp (2); # 2 to the third power or e to the second power

Log (2), log10 (2); # logarithm

Radians (180), degrees (0.618); # Angle Radian conversion

Sin (0.5), asin (0.5) # sinusoidal and arcsine are similar to cos acos tan atan

Length ('hi') # calculates the character length

Concat ('1merge, 1merge, merge, etc.

Insert ("12345"); # from the beginning of the first character to the end of 0, replace it with the trailing string, and 0 means to insert at the beginning

Ucase ('a'), lcase ('A') # converted to uppercase and lowercase

Left ('abcd',2), right (' abcd',2); # returns the first two characters and the last two characters

Ltrim ('0'), rtrim ('0'), trim ('0') # remove spaces

Replace ('1234567890' replacement output 12090)

Substring ('12345 characters 1, 2) # take character output 12 1 is position 2 is length

Instr ('1234'); # the position is 2.

Reverse ('1234'); # reverse output 4321

Current () # return date www.2cto.com

Curtime () # return time

Now () # returns the date and time

Month (now ()) # current month monthname English month

Dayname (now ()) # week English dayofweek () 1 is Sunday weekday () 1 is Tuesday

Week (now ()) # more or less weeks this year

Dayofyear (now ()), dayofmonth (now ()) # Today is the most or less day of this year. Today is the day of this month.

Year (now ()), month (now ()), day (now ()), hour (now ()), minute (now ()), second (now ()) # returns the year, month, day, minute and second

Time_to_sec (now ()), sec_to_time (3600 to 8); # conversion time is seconds and restore

Version () # mysql version

Database () # the currently connected database is not null

User () # get the user name

Md5 ('a') # encrypted string

Ascii ('a') # ascii value 97

Bin, hex, oct # returns binary hexadecimal

Conv (1000-1-2-8); # conversion of various binary systems to each other

Rand () # generates random numbers between 0 and 1

Sleep (0.02) # pause seconds

Database optimization

1. Turn on caching and try to use the php function instead of mysql

2. The explain select statement can know the performance

3. One row of data uses limit 1

4. Re-index search fields such as keyword tags

5. The table join join ensures that the field type is the same and has its index

6. Random queries use php $r = mysql_query ("SELECT count (*) FROM user")

$d = mysql_fetch_row ($r); www.2cto.com

$rand = mt_rand (0sperm [0]-1)

$r = mysql_query ("SELECT username FROM user LIMIT $rand, 1")

7. Avoid using select * you should use specific fields

8. Each table uses the id primary key and is unsigned int

9. Fixed use of enum types for limited values, such as gender, national and ethnic sector status

10. Use not null ip storage as much as possible using int (4), and use the ip conversion function ip2long () to long2ip each other ()

11.delete and insert statements lock tables, so you can use split statements to operate

While (1) {Action statement; usleep (2000);}

twelve。 Choose the right storage engine; MyISAM is suitable for a large number of query write operations, multi-purpose InnoDB to support transactions

# stored procedure

# stored program

Delimiter # define stored procedures

Create procedure getversion (out params varchar (20)) # params is the outgoing parameter in in out out inout back

Begin

Select version () into params; # version information assignment params

End

Call getversion (@ a); # call stored procedure

Select @ a

Delimiter # defines storage functions

Create function display (w varchar (20)) returns varchar (20)

Begin

Return concat ('hello',w)

End

Select display ('world')

Drop procedure if exists spName; # Delete a stored procedure

Alter function spName []; # modify a stored procedure

Show create procedure spName;# displays stored procedure information

Declare varName type default value;# declares local variables

# if statement

If conditional then statement

Elseif conditional then statement

Else statement www.2cto.com

End if

# case statement

Case condition

When conditional then statement

When conditional then statement

Else statement

End case

# loop statement

Fn:loop

Statement

End loop fn

Leave fn # exit the loop

# while statement

Fn:while conditional do

Statement

End while fn

# mysql usage help material

? Contents; # lists help types

? Data types;# lists data types

? Int;# lists specific types

? Show;#show statement

? Create table;#

# comparison of common tables

Myisam BDB Memory InnoDB Archive

Storage limit no no yes 64T no

Thing security support

Lock mechanism table lock page lock row lock row lock

Full-text indexing support

Foreign key support

Myisam frm storage table definition MYD storage data MYI storage index

InnoDB for transaction processing

Char and varchar have different saving and indexing

Floating-point number float (10) fixed-point number decimal (10)

When the length is fixed, the floating point number represents a more big data range, but the disadvantage is that the precision is lost and the currency is stored with a fixed point number.

The index is suitable for where sentences or join sentence columns

Use the unique index www.2cto.com for unique values

Add a new user grant select,insert,update,delete on *. * to Yoby@localhost identified by 'mysql'

# *. * Database name. Table name, restrict login to a database test.* localhost is local host network can use'% 'instead of all hosts' mysql' is password Yoby is user name all permissions can be replaced by all

View user permissions show grants for 'root'@'localhost'

Remove permission revoke all on *. * from root@localhost

Group by id grouping

Having restricted sentence

Select1 union select2 federated query has repeatedly removed and reserved a row

Merge all rows of select2 union all select2 into the result set

The above is all the contents of the article "what are the ways to use Mysql?" Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.