From basic to stored procedures

Friends who are used to other tools such as phpmyadmin do not have the command at all. If you have a written test to interview me and see what you do, it is very useful to study it, you can also know what the tool did when you used the GUI tool. Mysql is widely used and the best partner in php. It is also very convenient to use in Java.

I operate on Windows 7, so open "run", enter "cmd", and then "mysql-hlocalhost-uroot-p". Press enter to enter the password, which can be displayed, of course, it can also be connected to-p, which is to log on to mysql. Change the password mysqladmin-uroot-pold password new. Here, root is the username new, which is your new password. Some people once asked me what command to quit. I said you just clicked X, but the command was quit. exit the cmd environment and exit the cmd Environment command; the next step is to add, delete, modify, and query mysql operations, which are often called CURD operations.Copy codeThe Code is as follows: # log on to the database
Mysql-hlocalhost-uroot-p;
# Change Password
Mysqladmin-uroot-pold password new;

# Displaying Databases
Show databases;
# Display data tables
Show tables;
# Selecting a database
Use examples;
# Create a database and set UTF-8 encoding for multiple languages
Create database 'examples' default character set utf8 collate utf8_general_ci;
# Deleting a database
Drop database examples;
# Creating 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
# Deleting a table
Drop table test;
# Rename a table
Alter table test_old rename test_new;
# Adding columns
Alter table test add cn int (4) not null;
# Modifying Columns
Alter table test change id id1 varchar (10) not null;
# Deleting Columns
Alter table test drop cn;
# Creating an index
Alter table test add index (cn, id );
# Deleting an index
Alter table test drop index cn
# Insert data
Insert into test (id, email, ip, state) values (2, 'qq @ qq.com ', '2017. 0.0.1', '0 ');
# Deleting data
Delete from test where id = 1;
# Modifying data
Update test set id = '1', email = 'q @ qq.com 'where id = 1;
# Querying data
Select * from test; # retrieve all data
Select * from test limit; # obtain the first two pieces of data
Select * from test email like '% qq %' # query containing qq characters _ indicates one % indicates multiple
Select * from test order by id asc; # desc in descending order
Select * from test id not in ('2', '3'); # The id does not contain 2, 3, or not.
Select * from test timer between 1 and 10; # The data is between 1 and 10.

# --------------------------- Table connection knowledge ------------------------------
# Equivalent join, also called inner join, returns only rows with the same connection fields in two tables.
Select * from A inner join B on A. id = B. id; # Statement 1
Select * from A, B where A. id = B. id; # Write 2
Select a. id, a. title from A a inner join B B on a. id = B. id and a. id = 1; # Write the temporary name of table 3
Select a. id as ID, a. title as title from A inner join B on A. id = B. id; # Add the as Clause

# The left join is also called the Outer join. left join returns all records in the left table that are equal to the connection fields in the right table.
Select * from A left join B on A. id = B. id;

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

# The right join is also called the Outer join. right join returns all records in the right table that are equal to the connection fields in the left table.
Select * from A right join B on A. id = B. id;

# Full join with the complete external link returns all data in the left and right tables
Select * from A full join B on A. id = B. id;

# Return Kadir product without the where clause in the cross join
Select * from A cross join B;
------------------------- Table connection end ------------------------------------------------------------
----------------- Create an index ------------------------------------------------
Show index from A # view Indexes
Alter table A add primary key (id) # primary key index
Alter table A add unique (name) # unique index
Alter table A add index name (name) # normal index
Alter table A add fulltext (name) # Full-text index
Alter table A add index name (id, name) # multi-column index

# Common functions
Abs (-1) # absolute value
Pi () # pi value
Sqrt (2) # square root
Mod (-5, 3) # obtain the remainder-2
Ceil (10.6) # Carry + 1 Result 11 ceil (10.0) result 10
Floor (10.6) # rounded up 10
Round (2.5) # rounding to integer 3
Round (2.5, 2) # retain the two decimal places result 2.50
Truncate (2.5234, 3) # The last three digits of the decimal number are not rounded to 2.523.
Sign (-2); # the symbolic function returns-1, 0, or 0, and returns 1.
Pow (2, 3), exp (2); #3 power of 2 or 2 power of e
Log (2), log10 (2); # Number of logarithm
Radians (180), degrees (0.618); # angle radian Conversion
Sin (0.5), asin (0.5) # sine and arcsin are similar to cos acos tan atan
Length ('Hi') # Calculate the character length
Concat ('1', 1, 'Hi') # merge strings
Insert ('123456', 12345, '123456'); # Start from 7890 characters and end from 0. Replace the string with the following string. 0 indicates that the string is inserted at the frontend.
Ucase ('A'), lcase ('A') # convert to uppercase and lowercase
Left ('abc', 2), right ('abc', 2); # returns the first two and last two characters
Ltrim ('0'), rtrim ('0'), trim ('0') # Delete Spaces
Replace ('123456', '123456', '0'); # replace output 1234567890
Substring ('100', 1, 2) # Take the output character 12 1 is the position 2 is the length
Instr ('123', '123'); # obtain the 1234 position as 2
Reverse ('20140901'); # output 1234 in reverse order
Current () # Return date
Curtime () # return time
Now () # Return date and time
Month (now () # current month monthname English month
Dayname (now () # weekly English dayofweek () 1 is Sunday weekday () 1 is Tuesday
Week (now () # week of the current year
Dayofyear (now (), dayofmonth (now () # Today is the day of the year. Today is the day of the month.
Year (now (), month (now (), day (now (), hour (now (), minute (now ()), second (now () # returns year, month, day, hour, minute, second
Time_to_sec (now (), sec_to_time (3600*8); # The conversion time is second and restoration.
Version () # mysql version
Database () # The currently connected database is not null
User () # Get user Name
Md5 ('A') # encrypted string
Ascii ('A') # ascii value 97
Bin (100), hex (100), oct (100) # returns binary hexadecimal octal
Conv (10001,2, 8); # convert each other in hexadecimal notation
Rand () # generate a random number between 0 and 1
Sleep (0.02) # Pause seconds

Database Optimization
. Enable cache and try to use php functions instead of mysql
The. explain select statement can know the performance.
. Use limit 1 for a row of data;
. Re-indexing a search field, such as a keyword tag
Table join ensures that fields of the same type and have their indexes
. Use php $ r = mysql_query ("SELECT count (*) FROM user") for random queries ");
$ D = mysql_fetch_row ($ r );
$ Rand = mt_rand (0, $ d [0]-1 );
$ R = mysql_query ("SELECT username FROM user LIMIT $ rand, 1 ");
. Avoid using select *. Use specific fields.
. Each table uses the id Primary Key and is an unsigned int.
. If the value is limited, the enum type is used, for example, the status of the Gender country name family.
. Use not null ip Storage as much as possible to use int (4) and ip Conversion Function ip2long () for mutual long2ip ()
The. delete and insert statements lock the table, so you can use the split statement.
While (1) {operation statement; usleep (2000 );}
. Select the Correct storage engine. MyISAM is suitable for a large number of query and write operations. Use InnoDB to support transactions.

# Stored Procedure
# Storage Program
Delimiter # define the storage Program
Create procedure getversion (out params varchar (20) # params is the output parameter in passed in out and inout
Begin
Select version () into params; # assign the version value to params
End
Call getversion (@ a); # call a stored procedure
Select @;
Delimiter # define 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; # display stored procedure information
Declare varName type default value; # declare a local variable
# If statement
If condition then statement
Elseif condition then statement
Else statement
End if
# Case statement
Case Condition
When condition then statement
When condition then statement
Else statement
End case
# Loop statement
Fn: loop
Statement
End loop fn;
Leave fn # exit the loop
# While statement
Fn: while condition do
Statement
End while fn

# Mysql help Materials
? Contents; # list help types
? Data types; # List data types
? Int; # list specific types
? Show; # show statement
? Create table ;#
# Comparison of common tables
Myisam BDB Memory InnoDB Archive
Storage Limit no yes 64 T no
Transaction Security Support
Lock Mechanism table lock page lock table lock row lock
Full-text index support
Foreign key support
Myisam frm storage table definition MYD storage data MYI storage Index
InnoDB for Transaction Processing
Char and varchar save and index are different
Floating point float () decimal)
Given a certain length, floating point numbers indicate a larger data range. The disadvantage is that precision is lost, and currencies are stored in fixed points.
The index is applicable to the where clause or connected clause columns.
Use a unique index for a unique value

Add a new user grant select, insert, update, delete on *. * to Yoby @ localhost identified by 'mysql ';
#*. * Database name. table Name, restrict the login to a database test. * localhost is a local host network. You can use '%' to replace all hosts. 'mysql' is the password. Yoby is the username. all permissions can be replaced by "all ".
View User Permissions: show grants for 'root' @ 'localhost ';
Remove the permission revoke all on *. * from root @ localhost;
Group by id group
Having limit words
Select1 union select2 union query has repeated removal to keep one row
Select2 union all select2 all rows are merged into the result set

This is the most complete mysql note. You can copy and save it as needed!

(Original Yoby)