Mysql usage overview-from basic to stored procedure _ MySQL

Mysql usage overview-from basic to stored procedure bitsCN.com

I don't think I have fully understood the Mysql usage summary in the yard. my logon to advanced storage processes are all involved. This part is what I usually do not know or encounter problems, but now I am going to use the command mode to learn the most basic information about the database. some of my friends who are used to other tools such as phpmyadmin won't execute commands at all, if you have a written test to interview me to see what you do, it is very useful to study it. you can also know what the tool did when you passed 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.

# Log on to the mysql-hlocalhost-uroot-p; # change the password mysqladmin-uroot-pold password new; # display the database show databases; # display the table show tables; # select the database use examples; # create a database and set the encoding UTF-8 multi-language create database 'examples' default character set utf8 collate utf8_general_ci; # delete a 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 null, state int (10) not null default '-1', primary key (id) engine = InnoDB; # display table structure describe # delete table drop table test; # rename table 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; # delete the alter table test drop cn column; # Create index alter table test add index (cn, id); # delete index alter table test drop index cn # insert data into test (id, email, ip, state) values (2, 'qq @ qq. co M', '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 the range of 1 to 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. 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 clause # The left join is also called the outer join. left join returns the record select * from A left join B o for all records in the left table that are equal to the connection fields in the right table. N. 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 Kadir product select * from A cross join B --------------------------------------------------------------- -------------- Create an 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) # four homes five Integer result 3 round (2.5, 2) # retain the two decimal places result 2.50 truncate (2.5234, 3) # The last three decimal places are not rounded to 2.523 sign (-2 ); # The symbolic function returns-1 0 or 0 positive numbers and returns 1pow (2, 3), exp (2); #3 power of 2 or 2 power log of e (2 ), log10 (2); # logarithm radians (180), degrees (0.618); # angle radian conversion sin (0.5), asin (0.5) # cos acos tan atanlength ('hi') similar to Sine and arcsin # Calculate the character length concat ('1', 1, 'hi') # Merge string insert ('123 ', 1, 0, '123'); # start from the first 7890 characters to the end of 0, replace it with the following string, 0 indicates inserting ucase ('A') in the frontend '), lcase ('A') # Convert to uppercase and lowercase left ('ABC', 2), right ('ABC', 2); # return the first two Ltrim ('0'), rtrim ('0'), trim ('0') # delete space replace ('20170', '20160 ', '0'); # Replace the output 12090 substring ('123456', 12345) # Take the output character 12. 1 is the position 2 is the length instr ('123456', '123456 '); #2 reverse ('20140901') to get the 234 position; # output 1234 current () in reverse order # 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 () # The week of the current year dayofyear (now (), dayofmonth (now ()) # today is the day of the current year. today is the day of the current month. year (now (), mo Nth (now (), day (now (), hour (now (), minute (now (), second (now ()) # return time_to_sec (now (), sec_to_time (3600*8); # Convert time to second and restore version () # mysql version 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) # return binary hexadecimal octal conv (, 8); # convert each other into rand () # Generate a random number between 0 and 1 sleep (0.02) # pause seconds database optimization 1. enable caching. try to use the php function instead of the mysql2. explain select statement to know 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); $ 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 to use int (4), and use the ip conversion function ip2long () to connect to each other long2ip () 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 # 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 # defines the storage function create function display (w varchar (20) returns varchar (20) beginreturn concat ('hello', w); endselect display ('world '); drop procedure if exi Sts 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 local variables # if statement if condition then statement elseif condition then statement else statement 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 information? 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 other indexes stored by fixed points are suitable for the where clause or connected clause columns to add the new user grant select, insert, update, delete on * for unique values *. * 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 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 group having limit words select1 union select2 union query have repeated removal keep one row select2 union all select2 all rows merged into the result set

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

(Original Yoby)

BitsCN.com