Usually accustomed to phpmyadmin and other tools of the friend some simply will not command, if you want to have a written exam to interview I see how you do, so, learn to be very useful, you can also know what you have done through the GUI tool tool in the end. MySQL is very useful, PHP is the best partner, the use of Java is also very convenient.
I am operating through Windows 7, so open the run-enter CMD bar, and then enter the Mysql-hlocalhost-uroot-p, enter the password can be entered here, the * number can be shown, of course, and-P ligatures, which is login MySQL. Modify password mysqladmin-uroot-pold password new; the root here is username. Quit is what command, someone asked me, I said you directly point x good, but the command is quit; exit to CMD environment, exit CMD Environment command is exit, then is to operate MySQL additions and deletions to check, often known as curd operation.
Copy Code code as follows:
#登录数据库
Mysql-hlocalhost-uroot-p;
#修改密码
mysqladmin-uroot-pold password new;
#显示数据库
show databases;
#显示数据表
Show tables;
#选择数据库
Use examples;
#创建数据库并设置编码utf-More than 8 languages
Create database ' examples ' default character set UTF8 collate utf8_general_ci;
#删除数据库
drop database examples;
#创建表
CREATE TABLE Test (
ID Int (a) unsigned zerofill not NULL auto_increment,
Email varchar NOT NULL,
IP varchar NOT NULL,
State Int (a) NOT null default '-1 ',
Primary KEY (ID)
) Engine=innodb;
#显示表结构
Describe
#删除表
drop table test;
#重命名表
ALTER TABLE test_old rename test_new;
#添加列
ALTER TABLE Test add CN int (4) not null;
#修改列
ALTER TABLE test change ID id1 varchar (a) not null;
#删除列
ALTER TABLE Test drop CN;
#创建索引
ALTER TABLE test Add index (CN,ID);
#删除索引
ALTER TABLE test DROP INDEX CN
#插入数据
INSERT into Test (id,email,ip,state) VALUES (2, ' qq@qq.com ', ' 127.0.0.1 ', ' 0 ');
#删除数据
Delete from Test where id = 1;
#修改数据
Update test set id= ' 1 ', email= ' q@qq.com ' where id=1;
#查数据
SELECT * from Test; #取所有数据
SELECT * FROM Test limit 0, 2; #取前两条数据
SELECT * FROM test email like '%qq% ' #查含有qq字符 _ to represent a% of multiple
SELECT * FROM test-ID ASC; #降序desc
SELECT * FROM Test ID not in (' 2 ', ' 3 '); #id不含2, 3 or remove the not expression containing
SELECT * FROM test timer between 1 and #数据在1, 10
#---------------------------Table Connection Knowledge------------------------------
#等值连接又叫内链接 INNER JOIN returns only rows with the same connection fields in two tables
SELECT * FROM A inner join B on a.id = b.id; #写法1
SELECT * from a,b where a.id = b.id; #写法2
Select A.id,a.title the temporary name of a INNER join B on a.id=b.id and a.id=1; #写法3 table
Select a.ID as id,a.title as title from a inner join B on a.id=b.id; #添加as字句
#左连接又叫外连接 left JOIN returns records that are equal to the connection fields in all records in the left-hand table and 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=A.I1 and c.i2=a.i2 and d.i3 = A.i3); #复杂连接
#右连接又叫外连接 right Join returns records that are equal to all the records in the right-hand table and the join fields in the left table
SELECT * from A right join B on a.id = b.id;
#完整外部链接 full join returns all data in the left and right table
SELECT * from A full join B on a.id = b.id;
#交叉连接 no where words return Kader product
SELECT * from A cross join B;
-------------------------Table Connection End------------------------------------------------------------
-----------------Index Creation------------------------------------------------
Show index from A #查看索引
ALTER TABLE A add primary key (ID) #主键索引
ALTER TABLE A add unique (name) #唯一索引
ALTER TABLE A Add index name (name) #普通索引
ALTER TABLE A add fulltext (name) #全文索引
ALTER TABLE A Add index name (id,name) #多列索引
#常用函数
ABS ( -1) #绝对值
Pi () #pi值
sqrt (2) #平方根
MoD ( -5,3) #取余-2
Ceil (10.6) #进位 +1 results ceil (10.0) Result 10
Floor (10.6) #取整 10
Round (2.5) #四舍五入到整数 Results 3
Round (2.5,2) #保留两位小数 results 2.50
Truncate (2.5234,3) #取小数后3位不四舍五入 2.523
sign ( -2); #符号函数 return-1 0 or 0 positive number return 1
Pow (2,3), exp (2); 2 power of #2的3次幂 or E
Log (2), log10 (2); #求对数
radians (180), degrees (0.618); #角度弧度转换
Sin (0.5), ASIN (0.5) #正弦和反正弦 similar to cos ACOs Tan atan
Length (' Hi ') #计算字符长度
Concat (' 1 ', 1, ' Hi ') #合并字符串
Insert (' 12345 ', 1,0, ' 7890 '); #从开头第1个字符开始到0个结束, replace with a string behind, 0 to insert at the front
UCase (' A '), LCase (' a ') #转成大写和小写
Left (' ABCD ', 2), right (' ABCD ', 2); #返回前两个字符和后两个字符
LTrim (' 0 '), RTrim (' 0 '), trim (' 0 ') #删除空格
Replace (' 1234567890 ', ' 345678 ', ' 0 '); #替换输出12090
SUBSTRING (' 12345 ', 1,2) #取字符 output 12 1 is position 2 is length
InStr (' 1234 ', ' 234 '); #取得234位置是2
Reverse (' 1234 '); #反序输出4321
Current () #返回日期
Curtime () #返回时间
Now () #返回日期时间
Month (now ()) #当前月份 monthname English Month
Dayname (now ()) #星期英文 DayOfWeek () 1 is Sunday Weekday () 1 is Tuesday
Week (now ()) #本年第多少周
DayOfYear (now ()), DayOfMonth (Today ()) #今天是本年第多少天 How many days of the month
Year (now), Month (today ()), Day (now ()), Hour (now ()), Minute (now), second (now)) #返回年月日 minutes
Time_to_sec (now ()), Sec_to_time (3600*8); #转换时间为秒和还原
Version () #mysql版本
The database () #当前连接的数据库 is not null
User () #获取用户名
MD5 (' a ') #加密字符串
ASCII (' a ') #ascii值97
Bin (m), Hex (MB), Oct #返回二进制 hexadecimal octal
Conv (10001,2,8); #各种进制相互转换
Rand () #生成0到1之间随机数
Sleep (0.02) #暂停秒数
Database optimization
. Turn on caching and try to use PHP functions instead of MySQL
. Explain SELECT statement to know performance
. One row of data uses limit 1;
. Rebuilding indexes for search fields such as keyword labels
. Table join join guarantees that the field type is the same and has its index
. Random queries 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");
. Avoid using SELECT * You should use specific fields
. Each table is with the ID primary key and is unsigned int
. Use enum types for limited values, such as gender, state of the National family sector
. Use NOT NULL IP storage whenever possible using INT (4), Ip2long () mutual long2ip () with IP conversion functions ()
The. Delete and INSERT statements lock the table, so you can use the split statement operation
while (1) {action statement; Usleep (2000);}
Select the correct storage engine; MyISAM for a large number of query write operations InnoDB support transactions
#存储过程
#存储程序
Delimiter #定义存储程序
CREATE PROCEDURE GetVersion (out params varchar) #params是传出参数 in and out outgoing inout returns
Begin
Select version () into params; #版本信息赋值params
End
Call GetVersion (@a); #调用存储过程
Select @a;
Delimiter #定义存储函数
Create function display (w varchar ()) returns varchar (20)
Begin
return concat (' Hello ', w);
End
Select Display (' World ');
drop procedure if exists spname; #删除一个存储过程
Alter function spname []; #修改一个存储过程
Show CREATE procedure spname; #显示存储过程信息
Declare varName type default value; #声明局部变量
#if语句
If condition then statement
ElseIf Conditional Then statement
Else statement
End If
#case语句
Case conditions
When condition then statement
When condition then statement
Else statement
End case
#loop语句
Fn:loop
Statement
End loop FN;
LEAVE FN #退出循环
#while语句
Fn:while condition do
Statement
End while FN
#mysql使用帮助资料
? Contents #列出帮助类型
? data types; #列出数据类型
？ int; #列出具体类型
? Show; #show语句
? Create table;#
#常见表的比较
Myisam BDB Memory InnoDB Archive
Storage limit No No yes 64T no
Things Security Support Support
Lock mechanism table lock page lock table lock row lock line Lock
Full-text Indexing Support
FOREIGN key support support
MyISAM frm storage table definition myd storage Data myi Storage Index
InnoDB for transaction processing
Char and varchar are both saved and indexed differently
Floating point Float (10,2) fixed-point number decimal (10,2)
The length of a certain, floating point number represents a larger data range, the disadvantage is caused by loss of precision, currency, such as the use of fixed-point storage
The index is suitable for the WHERE clause or the connection clause column
Use a unique index for unique values
Add new user Grant Select,insert,update,delete on *.* to yoby@localhost identified by ' MySQL ';
# *.* database name. Table name, limit login to a certain database test.* localhost is a local host network can use '% ' instead of all host ' MySQL ' is password Yoby is username All permissions can be replaced with all
View User rights show grants for ' root ' @ ' localhost ';
Remove permission revoke all on *.* from Root@localhost;
Group BY ID Grouping
Having restrictive words
SELECT1 Union select2 Union query has duplicate remove keep row
Select2 UNION ALL Select2 Merge all rows to the result set
This is one of the most complete MySQL notes, need to be copied and saved!
(Original Yoby)
