mysql 常用操作

  |   0 评论   |   1,066 浏览
CREATE TABLE student
(
   id INT AUTO_INCREMENT PRIMARY KEY ,
   NAME VARCHAR(20),
   sex CHAR(2) NOT NULL ,
   age INT NOT NULL,
   tel CHAR(11) NULL ,
   address CHAR(50)
) 

DROP TABLE student 

INSERT INTO student VALUES(NULL,'zhang','男',14,'132654898','北京')

INSERT INTO student VALUES(NULL,'wang','女',16,'13215489878','上海')

SELECT * FROM student


1.23种常用查询数据.


一查询数值型数据:

SELECT * FROM tb_name WHERE sum > 100;

二查询字符串

 SELECT * FROM tb_stu  WHERE sname  =  '小刘'
 SELECT * FROM tb_stu  WHERE sname like '刘%'

三查询日期型数据

SELECT * FROM tb_stu WHERE date = '2011-04-08'

四查询逻辑型数据

SELECT * FROM tb_name WHERE type = 'T'
 SELECT * FROM tb_name WHERE type = 'F'

 逻辑运算符:and or not

五查询非空数据

SELECT * FROM tb_name WHERE address <>'' order by addtime desc

六利用变量查询数值型数据

SELECT * FROM tb_name WHERE id = '$_POST[text]'

 注:利用变量查询数据时,传入SQL的变量不必用引号括起来,

七利用变量查询字符串数据 

SELECT * FROM tb_name WHERE name LIKE '%$_POST[name]%'

 完全匹配的方法"%%"表示可以出现在任何位置

八查询前n条记录

SELECT * FROM tb_name LIMIT 0,5;

 limit语句与其他语句,如order by等语句联合使用,会使用SQL语句千变万化,使程序非常灵活

九查询后n条记录

SELECT * FROM tb_stu ORDER BY id ASC LIMIT 5

十查询从指定位置开始的n条记录

SELECT * FROM tb_stu ORDER BY id ASC LIMIT 5,8

十一查询统计结果中的前n条记录

 SELECT * ,(yw+sx+wy) AS total FROM tb_score ORDER BY (yw+sx+wy) DESC LIMIT 0,5

十二查询指定时间段的数据

 SELECT  要查找的字段 FROM 表名 WHERE 字段名 BETWEEN 初始值 AND 终止值

 SELECT * FROM tb_stu WHERE age BETWEEN 0 AND 18

十三按月查询统计数据

 SELECT * FROM tb_stu WHERE month(date) = '$_POST[date]' ORDER BY date ;

 注:SQL语言中提供了如下函数,利用这些函数可以很方便地实现按年、月、日进行查询

 year(data):返回data表达式中的公元年分所对应的数值

 month(data):返回data表达式中的月分所对应的数值

 day(data):返回data表达式中的日期所对应的数值

 

十四查询大于指定条件的记录

SELECT * FROM tb_stu WHERE age>$_POST[age] ORDER BY age;

十五查询结果不显示重复记录

SELECT DISTINCT 字段名 FROM 表名 WHERE 查询条件

注:SQL语句中的DISTINCT必须与WHERE子句联合使用,否则输出的信息不会有变化 ,且字段不能用*代替

 

十六NOT与谓词进行组合条件的查询

 (1)NOT BERWEEN … AND … 对介于起始值和终止值间的数据时行查询 可改成 <起始值 AND >终止值

 (2)IS NOT NULL 对非空值进行查询 

 (3)IS NULL 对空值进行查询

 (4)NOT IN 该式根据使用的关键字是包含在列表内还是排除在列表外,指定表达式的搜索,搜索表达式可以是常量或列名,而列名可以是一组常量,但更多情况下是子查询

 

十七显示数据表中重复的记录和记录条数

SELECT name,age,count(*) ,age FROM tb_stu WHERE age = '19' group by date

 

十八对数据进行降序/升序查询

 SELECT 字段名 FROM tb_stu WHERE 条件 ORDER BY 字段 DESC 降序

 SELECT 字段名 FROM tb_stu WHERE 条件 ORDER BY 字段 ASC  升序

 注:对字段进行排序时若不指定排序方式,则默认为ASC升序

 

十九对数据进行多条件查询

 SELECT 字段名 FROM tb_stu WHERE 条件 ORDER BY 字段1 ASC 字段2 DESC  …

 注意:对查询信息进行多条件排序是为了共同限制记录的输出,一般情况下,由于不是单一条件限制,所以在输出效果上有一些差别。

 

二十对统计结果进行排序

 函数SUM([ALL]字段名) 或 SUM([DISTINCT]字段名),可实现对字段的求和,函数中为ALL时为所有该字段所有记录求和,若为DISTINCT则为该字段所有不重复记录的字段求和

 如:

SELECT name,SUM(price) AS sumprice  FROM tb_price GROUP BY name
SELECT * FROM tb_name ORDER BY mount DESC,price ASC

 

二十一单列数据分组统计

 SELECT id,name,SUM(price) AS title,date FROM tb_price GROUP BY pid ORDER BY title DESC

 注:当分组语句group by排序语句order by同时出现在SQL语句中时,要将分组语句书写在排序语句的前面,否则会出现错误

 

二十二多列数据分组统计

 多列数据分组统计与单列数据分组统计类似 

  SELECT *,SUM(字段1*字段2) AS (新字段1) FROM 表名 GROUP BY 字段 ORDER BY 新字段1 DESC
 SELECT id,name,SUM(price*num) AS sumprice  FROM tb_price GROUP BY pid ORDER BY sumprice DESC

 注:group by语句后面一般为不是聚合函数的数列,即不是要分组的列

 

二十三多表分组统计

SELECT a.name,AVG(a.price),b.name,AVG(b.price) FROM tb_demo058 AS a,tb_demo058_1 AS b WHERE a.id=b.id GROUP BY b.type;




2 插入、更新与删除数据


添加数据

        INSERT INTO db1_name(field1,field2) values (value1,value2)
    INSERT INTO db1_name (field1,field2) SELECT field1,field2 FROM db2_name
    Select * Into new_table_name from old_table_name;  MYSQL 不支持此语法可以用一下语句替代
    Create table new_table_name (Select * from old_table_name);

更新数据

   单表更新

UPDATE persondata SET age=age*2, age=age+1;

   多表更新

UPDATE items,month SET items.price=month.price WHERE items.id=month.id;

删除单表数据

DELETE FROM somelog  WHERE user = 'jcole' ORDER BY timestamp_column

删除多表数据

DELETE t1, t2 FROM t1, t2, t3 WHERE t1.id=t2.id AND t2.id=t3.id; 或:
DELETE FROM t1, t2 USING t1, t2, t3 WHERE t1.id=t2.id AND t2.id=t3.id;


3 mysql运算符


MySQL运算符包括四类:算术运算符、比较运算符、逻辑运算符和位运算符。

一、算数运算符(加+、减-、乘*、除/、求余%,用在数值运算上)

除法除数为零时,执行结果为空。

二、比较运算符(大于小于等于不等于为空等比较运算,用于数值比较、字符串匹配等方面)

"="相等返回1,不等返回0,空值不能使用等号和不等号判断

"<=>"与=作用相等,唯一区别是它可以用来判断空值。

“>”用来判断左边的操作数是否大于右边的操作数。如果大于,返回1.如果不大于,返回0。空值不能用“>”来判断。

“>=”用来判断左边的操作数是否大于等于右边的操作数。如果大于等于,返回1.如果小于,返回0。空值不能用“>=”来判断。

“<”用来判断左边的操作数是否小于右边的操作数。如果小于,返回1.如果不小于,返回0。空值不能用“<”来判断。

<=”用来判断左边的操作数是否小于等于右边的操作数。如果小于等于,返回1.如果大于,返回0。空值不能用“<=”来判断。

“is null”用来判断操作数是否为空值。为空时返回1,不为空返回0。

“between and”可以判断操作数是否落在某个取值范围内。

“in”可以判断操作数是否落在某个集合中。表达式“x1 in(值1,值2,…,值n)

”,如果x1等于其中任何一个值,返回1,不是返回0。

“like”用来匹配字符串。表达式“x1 like s1”,如果x1与字符串s1匹配,结果返回1。

“regexp”也用来匹配字符串,但其中使用正则表达式匹配。


三、逻辑运算符布尔运算符(与、或、非、异或等)

1、与运算

“&&”或者and表示与运算。所有操作数不为o且不为空时,返回1,存在一个为0,返回0,存在一个为空且没有操作数为0时返回空。

2、或运算

“||”或者or表示或运算。

3、非运算

“!”或者not表示非运算。如果操作数是非零,结果返回0;如果操作数是0,结果返回1,如果操作数是null,结果返回null。

4、异或运算

xor表示异或运算,基本形式是“x1 xor x2”,只要其中任何一个操作数为null时,结果返回null,如果两个都是非零数字或者都是零,结果返回0,如果一个是非零,一个是0,结果返回1。



四、位运算符

1、按位与“&”

2、按位或“|”

3、按位取反“~”

4、安位异或“^”

5、按位左移“<<”

“m<<n”m的二进制左移n位,右边补n个0

6、按位右移“>>”

“m>>n”m的二进制右移n位,左边补n个0



4.mysql函数


字符串函数

ASCII(str)   

 返回字符串str的第一个字符的ASCII值(str是空串时返回0)  mysql> select ASCII('2'); 返回  50  

ORD(str)   

 如果字符串str句首是单字节返回与ASCII()函数返回的相同值。  select ORD('2'); 返回 50

 如果是一个多字节字符,以格式返回((first byte ASCII code)

CONV(N,from_base,to_base)   

 对数字N进制转换,并转换为字串返回(任何参数为NULL时返回NULL)   select CONV("a",16,2);  返回 '1010' 

BIN(N)   

 把N转为二进制值并以字串返回 select BIN(12);  返回 '1100' 

OCT(N)   

 把N转为八进制值并以字串返回 select OCT(12);  返回 '14'  

HEX(N)   

 把N转为十六进制并以字串返回  select HEX(255);   返回 'FF' 

CONCAT(str1,str2,...)   

 把参数连成一个长字符串并返回 select CONCAT('My', 'S', 'QL');  返回'MySQL' 

LENGTH(str)  

 返回字符串str的长度 select LENGTH('text');  返回 4  

LOCATE(substr,str)   或者 POSITION(substr IN str)   

 返回字符串substr在字符串str第一次出现的位置 select LOCATE('bar', 'foobarbar');  返回 4  

LOCATE(substr,str,pos)  

  返回字符串substr在字符串str的第pos个位置起第一次出现的位置 select LOCATE('bar', 'foobarbar',5);返回 7  

INSTR(str,substr)   

 返回字符串substr在字符串str第一次出现的位置select INSTR('foobarbar', 'bar');  返回 4  

LPAD(str,len,padstr)   

 用字符串padstr填补str左端直到字串长度为len并返回  select LPAD('hi',4,'??');  返回 '??hi' 

RPAD(str,len,padstr)   

 用字符串padstr填补str右端直到字串长度为len并返回   select RPAD('hi',5,'?'); 返回 'hi???' 

LEFT(str,len)   

 返回字符串str的左端len个字符   select LEFT('foobarbar', 5);  返回 'fooba' 

RIGHT(str,len)   

 返回字符串str的右端len个字符    select RIGHT('foobarbar', 4);  返回 'rbar' 

SUBSTRING(str,pos,len)   或SUBSTRING(str FROM pos FOR len)   或MID(str,pos,len)   

 返回字符串str的位置pos起len个字符  select SUBSTRING('Quadratically',5,6);  返回 'ratica' 

 

SUBSTRING_INDEX(str,delim,count)   

 返回从字符串str的第count个出现的分隔符delim之后的子串

  select SUBSTRING_INDEX('www.mysql.com', '.', 2);返回   'www.mysql' 

  select SUBSTRING_INDEX('www.mysql.com', '.', -2);   返回 'mysql.com' 

LTRIM(str) 

  返回删除了左空格的字符串str  select LTRIM('  barbar');  返回 'barbar' 

RTRIM(str)   

 返回删除了右空格的字符串str   select RTRIM('barbar   ');  返回 'barbar' 

TRIM([[BOTH | LEADING | TRAILING] [remstr] FROM] str)   

 返回前缀或后缀remstr被删除了的字符串str(位置参数默认BOTH,remstr默认值为空格)     

SPACE(N)   

 返回由N个空格字符组成的一个字符串   select SPACE(6);  返回 '      ' 

REPLACE(str,from_str,to_str)   

 用字符串to_str替换字符串str中的子串from_str并返回  

  select REPLACE('www.mysql.com', 'w', 'Ww');     返回 'WwWwWw.mysql.com' 

REPEAT(str,count)   

 返回由count个字符串str连成的一个字符串 select REPEAT('MySQL', 3);  返回 'MySQLMySQLMySQL' 

REVERSE(str)   

 颠倒字符串str的字符顺序并返回   select REVERSE('abc');   'cba'  

INSERT(str,pos,len,newstr)   

 把字符串str由位置pos起len个字符长的子串替换为字符串newstr并返回  

  select INSERT('Quadratic', 3, 4, 'What');  返回 'QuWhattic'  

LCASE(str)  或 LOWER(str)   

 返回小写的字符串str  select LCASE('QUADRATICALLY');   返回'quadratically' 

UCASE(str)   或 UPPER(str)   

 返回大写的字符串str  select UCASE('quadratically');  返回 'QUADRATICALLY'  


数学函数


ABS(N)  

 返回N的绝对值   select ABS(2);    返回 2    

MOD(N,M)    

 取模运算,返回N被M除的余数(同%操作符) select MOD(234, 10); 返回 4    

FLOOR(N)  

 返回不大于N的最大整数值   select FLOOR(1.23);    返回 1    

CEILING(N)  

 返回不小于N的最小整数值   select CEILING(1.23);    返回 2    

ROUND(N,D)  

 返回N的四舍五入值,保留D位小数(D的默认值为0)  select ROUND(-1.23);    返回-1    

POW(X,Y)    POWER(X,Y)    

 返回值X的Y次幂  select POW(2,2);    返回 4.000000    

SQRT(N)  

 返回非负数N的平方根  select SQRT(4);    返回 2.000000    

RAND() 或 RAND(N)    

 返回在范围0到1.0内的随机浮点值(可以使用数字N作为初始值)  select RAND();    返回 0.5925    

TRUNCATE(N,D)    

 保留数字N的D位小数并返回   select TRUNCATE(1.223,1);    返回 1.2    

LEAST(X,Y,...)    

 返回最小值 select LEAST(2,0);    返回 0    

GREATEST(X,Y,...)    返回最大值 select GREATEST(2,0);   返回2    

 

时期时间函数 


DAYOFWEEK(date)    

 返回日期date是星期几(1=星期天,2=星期一,……7=星期六,ODBC标准)  select DAYOFWEEK('1998-02-03');    返回 3    

WEEKDAY(date)    

 返回日期date是星期几(0=星期一,1=星期二,……6= 星期天)。 select WEEKDAY('1997-10-04 22:23:00'); 返回 5    

DAYOFMONTH(date)    

 返回date是一月中的第几日(在1到31范围内)     select DAYOFMONTH('1998-02-03');    返回 3    

DAYOFYEAR(date)    

 返回date是一年中的第几日(在1到366范围内)   select DAYOFYEAR('1998-02-03');    返回 34    

MONTH(date)    

 返回date中的月份数值    select MONTH('1998-02-03');    返回 2    

DAYNAME(date)    

 返回date是星期几(按英文名返回)  select DAYNAME("1998-02-05");    返回 'Thursday'    

MONTHNAME(date)    

 返回date是几月(按英文名返回)  select MONTHNAME("1998-02-05");    返回 'February'    

QUARTER(date)    

 返回date是一年的第几个季度      select QUARTER('98-04-01');    返回 2    

WEEK(date,first)   

  返回date是一年的第几周(first默认值0,first取值1表示周一是周的开始,0从周日开始) select WEEK('1998-02-20');返回7    

YEAR(date)    

 返回date的年份(范围在1000到9999)    select YEAR('98-02-03'); 返回1998     

HOUR(time)    

 返回time的小时数(范围是0到23)   select HOUR('10:05:03'); 返回 10    

MINUTE(time)    

 返回time的分钟数(范围是0到59)  select MINUTE('98-02-03 10:05:03');返回 5    

SECOND(time)    

 返回time的秒数(范围是0到59)  select SECOND('10:05:03'); 返回 3     

PERIOD_ADD(P,N)    

 增加N个月到时期P并返回(P的格式YYMM或YYYYMM) select PERIOD_ADD(9801,2);返回199803     

PERIOD_DIFF(P1,P2)    

 返回在时期P1和P2之间月数(P1和P2的格式YYMM或YYYYMM)   select PERIOD_DIFF(9802,199703); 返回11    

CURDATE()  或 CURRENT_DATE()  

 以'YYYY-MM-DD'或YYYYMMDD格式返回当前日期值(根据返回值所处上下文是字符串或数字)    

  select CURDATE(); '1997-12-15'    

  select CURDATE() + 0;    -> 19971215    

CURTIME() 或  CURRENT_TIME()  

 以'HH:MM:SS'或HHMMSS格式返回当前时间值(根据返回值所处上下文是字符串或数字)      

  select CURTIME();    -> '23:50:26'    

  select CURTIME() + 0;    -> 235026     

NOW()   或 SYSDATE()  或  CURRENT_TIMESTAMP()  

 以'YYYY-MM-DD HH:MM:SS'或YYYYMMDDHHMMSS格式返回当前日期


转换函数


cast

用法:cast(字段 as 数据类型) [当然是否可以成功转换,还要看数据类型强制转化时注意的问题]

实例:select cast(a as unsigned) as b from cardserver where order by b desc;

convert:

用法:convert(字段,数据类型)

实例:select convert(a ,unsigned) as b from cardserver where order by b desc;



5 存储过程和函数


create procedure 存储过程名字() 

[in|out|inout] 参数 datatype 

begin 

MySQL 语句; 

end; 

--调用

call pr_no_param(); 

删除

drop procedure pr_name;


--以下才是成功创建存储过程,百思不解。

DELIMITER$$ 

CREATE

    PROCEDURE abcdee11()

    BEGIN

SELECT * FROM students;

    END$$

DELIMITER ;




一、函数


创建格式:


CREATE FUNCTION sp_name ([func_parameter[,...]])  
    RETURNS type 
    [characteristic ...] routine_body  
func_parameter:
    param_name type

  

 例子:DELIMITER //CREATE FUNCTION Myf() RETURNS VARCHAR(50) BEGINRETURN '11';END //注意:"DELIMITER //" 意思是定义 "//"为批处理结束分隔符,没有这个语句会报错 

调用: select Myf();


总结:函数的参数前不能有修饰符如,IN,OUT(不同于存储过程),调用时可直接调用,像系统函数一般使用。



二、存储过程


创建格式:


CREATE PROCEDURE sp_name ([proc_parameter[,...]]) 
    [characteristic ...] routine_body 
proc_parameter: 
    [ IN | OUT | INOUT ] param_name type

IN用于说明是传入参数,OUT为返还结果的参数(即开始时没有值,调用后有值),INOUT则充当两者的角色 

例子: 

DELIMITER //CREATE PROCEDURE mySP(a INT(2),OUT b INT(2))BEGINSET b=110;SELECT a+b;END //调用: 

CALL mySP(1,@b);SELECT @b; 

总结:IN,OUT,INOUT用于指定参数的类型,OUT参数的赋值使用set操作符(与局部变量赋值类似),调用时使用call,out参数要有@修饰。 


附录:教程TXT  教程word1 教程word2 





>