Mysql usage overview-from basic to Stored Procedure

Mysql usage overview-from the basic to the stored procedure, I can see the Mysql usage summarized in the yard. I don't think it's all about me. All my stored procedures from login to advanced are involved,
This part is common to me, but now I will study the database with the command mode.
The most basic thing is that some friends who are used to other tools such as phpmyadmin do not have the command at all. If you have a written test to go to the interview
I think what you do, so it is very useful to learn, and 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. Www.2cto.com I operate on Windows 7, so open run-Enter cmd, and then enter mysql-hlocalhost-uroot-p;
After you press enter, you can enter the password. The password can be displayed at the * sign, and can also be connected to-p, which means you can log on to mysql.
Change the password mysqladmin-uroot-pold password new. Here, root is the username new, which is your new password.
When someone asked me what command to quit, I said you just clicked X, but the command was quit. quit to the cmd environment,
To exit the cmd environment, run the "exit" command, followed by the "add, delete, modify, and query" operation on mysql, which is often called the "CURD" operation. # Log on to the mysql-hlocalhost-uroot-p; # change the password mysqladmin-uroot-pold password new; www.2cto.com # display the database show databases; # display the data table show tables; # select database use examples; # create a database and set the UTF-8-encoded multi-language create database 'exampes' default character set utf8 collate utf8_general_ci; # Delete the database drop database examples; # create table test (id int (10) unsigned zerofill not null auto_increment, email varchar (40) not null, ip varchar (15) not n Ull, state int (10) not null default '-1', primary key (id) engine = InnoDB; # display table structure describe # Delete table drop table test; # rename the alter table test_old rename test_new; # add the alter table test add cn int (4) not null; # modify the alter table test change id id1 varchar (10) not null column; # Delete the 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 into test (id, email, ip, state) val Ues (2, 'qq @ qq.com ', '2017. 0.0.1 ', '0'); # delete data from test where id = 1; # modify data update test set id = '1 ', email = 'q @ qq.com 'where id = 1; # query data select * from test; # retrieve all data select * from test limit 0, 2; # select * from test email like '% qq %' # query containing qq character _ indicates one % indicates multiple select * from test order by id asc; # descselect * from test id not in ('2', '3') in descending order; # The id does not contain 2, 3, or remove not, which indicates that select * from test timer between 1 and 10 are contained; # data in Between 0: www.2cto.com # ----------------------------- 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. id = B. id; # Statement 1 select * from A, B where. id = B. id; # Statement 2 select. id,. title from A a inner join B B on. id = B. id and. id = 1; # write 3 temporary table name select. id as ID,. title as title from A inner join B on. id = B. id; # Add the as statement # The left join is also called the Outer join. left join returns the records of all records in the left table that are equal to the connection fields in the right table. Select * from A left join B on. id = B. id; select * from A left join (B, C, D) on (B. i1 =. i1 and C. i2 =. i2 and D. i3 =. i3); # complex join # The right join is also called the outer join right join. Return the select * from A right join B on A record with the same connection fields as the left table. id = B. id; # full external link full join return all data in the left and right tables select * from A full join B on. id = B. id; # No where clause in the cross join operation returns the select * from A cross join B; ----------------------- the table connection ends ----------------------------- Create A primary index -------------------------------------------- show index from A # view the index 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) # general 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) # retrieve remainder-2 ceil (10.6) # Carry + 1 Result 11 ceil (10.0) Result 10 floor (10.6) # round 10 round (2.5) # round to integer result 3 round (2.5, 2) # retain 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 return value of the symbolic function is-1 0 or 0. the return value is 1 POW (2, 3) and exp (2 ); #3 power of 2 or 2 power log of e (2), log10 (2); # logarithm number radians (180), degrees (0.618 ); # angle radian conversion sin (0.5), asin (0.5) # sine and arcsin are similar to cos acos tan atanlength ('Hi') # Calculate the character length concat ('1', 1, 'Hi') # merge the string insert ('123456', 12345, '123456'); # Replace the string with the string after it starts with 7890 characters and ends with 0 characters, 0 indicates inserting ucase ('A'), lcase ('A') # into upper and lower case le Ft ('abcd', 2), right ('abcd', 2); # returns the first two characters and the last two characters ltrim ('0 '), rtrim ('0'), trim ('0') # Delete the space replace ('123', '123', '0 '); # Replace the output 12090 substring ('123', 1, 2) # Take the character output 12 1 as the position 2 as the length instr ('123', '123 '); #2 reverse ('20140901') to get the 234 position; # output 1234 current () in reverse order # Return date www.2cto.com 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 () # The week of the current year dayofyear (now (), da Yofmonth (now () # Today is the day of the current year. Today is the day of the current month. year (now (), month (now (), day (now ()), hour (now (), minute (now (), second (now () # returns the year, month, day, hour, minute, second time_to_sec (now (), sec_to_time (3600*8 ); # converting time to second and restoring version () # mysql database () # The currently connected database is not nulluser () # obtain the username md5 ('A ') # encrypted string ascii ('A') # ascii value 97bin (100), hex (100), oct (100) # returns binary hexadecimal octal conv (, 8 ); # convert each other in hexadecimal notation rand () # generate a random number between 0 and 1 sleep (0.02) # Pause seconds database optimization 1. enable caching and try to use php functions instead of mysql2. explain se The lect statement shows the performance. use limit 1 for a row of data; 4. re-indexing a search field, such as a keyword tag 5. table join ensures that fields of the same type have their indexes. 6. use php $ r = mysql_query ("SELECT count (*) FROM user"); $ d = mysql_fetch_row ($ r); www.2cto.com $ rand = mt_rand (0, $ d [0]-1); $ r = mysql_query ("SELECT username FROM user LIMIT $ rand, 1"); 7. avoid using select *. Use the specific field 8. each table uses an id Primary Key and an unsigned int9. The enum type is used for a limited value, for example, gender country name family status 10. use not null ip Storage as much as possible using int (4) and ip Conversion Function ip2long () to lo each other Ng2ip () 11. the delete and insert statements lock the table, so you can use the split statement to operate the while (1) {operation statement; usleep (2000);} 12. select the Correct storage engine. MyISAM is suitable for a large number of query and write operations. InnoDB supports transactions # stored procedures delimiter # defines the storage program create procedure getversion (out params varchar (20 )) # params is the outgoing parameter in passed in out inout passed back beginselect version () into params; # The version information is assigned with paramsendcall getversion (@ a); # The stored procedure is called select @; delimiter # define the storage function create function display (w varchar (20) returns varchar (20) beginreturn co Ncat ('hello', w); endselect display ('World'); drop procedure if exists spName; # delete a stored procedure alter function spName []; # modify a stored procedure show create procedure spName; # display the stored procedure information declare varName type default value; # declare local variables # if statement if condition then statement elseif condition then statement else statement www.2cto.com end if # case statement case condition when condition then statement else statement end case # loop statement fn: loop statement end loop fn; leave fn # exit 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 restrictions no yes 64 T no transaction security support lock mechanism Table lock page lock table lock row lock Full-text index support foreign keys support myisam frm storage table definition MYD storage data MYI storage index InnoDB for transaction processing char and varchar storage and index are not the same floating point float) decimal () with a fixed point length, floating point number indicates a larger data range. The disadvantage is that precision is lost, currency and so on using the fixed point storage index is suitable for adding new users grant select, insert, update, delete on * to the where clause or join clause column for unique values using the unique index www.2cto.com *. * to Yoby @ localhost identified by 'mysql ';#*. * database name. table Name, restrict logon to a database test. * localhost is a local host network. You can use '%' to replace all hosts. 'mysql' is the password. Yoby is the user name. all permissions can be used to replace show grants for 'root' @ 'localhost. '; remove permission revoke all on *. * from root @ localhost; group by id grouping having restriction clause select1 union select2 union query is removed repeatedly retain a row select2 union all select2 all rows merged into the result set this is the most complete mysql note, the required information can be copied and saved!
From Qingzhu blog