1.修改字符串字符集 编辑mysql配置文件 my.ini
[client]
default-character-set = utf8 [wampmysqld]
character-set-server = utf8 //设置默认新建数据库编码为utf8
collation-server = utf8_general_ci //更改现有数据库编码2.表字段类型数值: intfloat不用加圆括号字符串:char (255) 最大长度255varchar(255): 最大长度65535text 65535字节 不能加圆括号
3.数据表操作1.sql条件 a.数学运算符+-*/ / %
b.逻辑运算符&& || and or
c.比较运算符
=, ！= ,>=, <=, > , <, <>
d.语句
DELETE from user where id in (1,2,3)
id 在1 或者2 或者3
DELETE from user where id>=3 and id<=5;DELETE from user where id between 3 and 5;
select distinct username from user 查询唯一的username
select * from user where username like '%linux% 用户名含有linux
排序order by select * from user order by id asc 默认升序select * from user order by id desc 默认降序 select * from user where age is null 年龄非空
SELECT * from patient_13 WHERE age > 20 order by age asc
mysql 关键字
使用limit 限定输出个数limit 7,3 【从7开始 截取三条】
随机数rand（）SELECT * FROM `user` order by rand() limit 3 随机打乱 提前前三条
随机数concat（）SELECT concat(name,'-',sex) from user 把数据库name和sex 连接起来
统计个数 count（）SELECT count(*) FROM `user` WHERE id>3 查看数据库条数
求和sum（）select sum（id）from user
平均数avg（） select avg（id） from user
最大值 最小值select max（id） from userselect min（age）from user
输出结果 print_r($arrs[0]['avg(password)']);
嵌套查询
查找本月的数量
$r=mysql_query("SELECT author, count(author) from patient_18 WHERE jiedai !='' && addtime >1551370929 group by author order by count(author) desc ");
$max=mysql_query("SELECT * from patient_13 WHERE age in (SELECT max(age) from patient_13)"); $_arrmax=mysql_fetch_assoc($max);echo "<pre>"; print_r($_arrmax);
分组聚合
select * from user group by class_id （按照班级分组）select class_id from user group by class_id select class_id,count(id) from user grou by class_id 统计班级人数
按年龄分组 并且统计出 相同年龄的人数$r=mysql_query("SELECT age, count(id) from patient_13 group by age"); $num=@mysql_num_rows($r);
$_arr=mysql_fetch_assoc($r);
打印出人数 echo "<li>{$_arr['count(age)']}</li>";
echo "<li>{$_arr['age']}</li>";
查找相同年龄的人数
mysql_query("SELECT age , count(age) from patient_18 WHERE age > 20 && age < 40 group by age order by count(age)desc");
跨表查询$r=mysql_query("SELECT patient_13 .name, patient_15 .name FROM patient_13 , patient_15 WHERE patient_13 .name = patient_15 .name");
多表查询（查两个表 后面是两个表的条件）select * from user，class where user.class_id=class.id只展示用户名字和班级select user.username，class.classname from user，class where user.class_id=class.id
一对多 在多的那里留ID
select（select count(*) from user where score>=60）yes,（select count(*) from user where score<=60）no;
select a,b;
select sum(if(score>=60,1,0) ) 及格, sum (if(score<60,1,0)) 不及格 from user;
SELECT DISTINCT `name` FROM patient_18
查询语句
完全限定语句select name from houtaiablyycnbak66.patient_18 where id =8000
计算次序 （）用包含SELECT * FROM patient_18 where (id<5 OR author = 'XX') AND `status` = 1
不在某个区间SELECT * FROM patient_18 where id NOT BETWEEN 5 and 10000
执行算数计算SELECT id,age,id+age AS ia FROM patient_18 where id <100 ORDER BY ia desc
时间函数SELECT id,age ,Now(),month(Now()) FROM patient_18 where id <1000
表表查询 where 函数SELECT author , aa from (SELECT author , count(author) AS aa from patient_18 where status=1 GROUP BY author) as tt1 where aa>50 ORDER BY aa desc
联合查询select patient_12.age,patient_12.sex from patient_12,patient_18 where patient_12.sex = patient_18.sex order by patient_12.age desc
查找年龄出现次数大于10的人 HAVING
select id ,age,count(age) FROM patient_18 WHERE `status`=1 GROUP BY age HAVING( COUNT(age)>10 )
常用的文本处理函数Left() 返回串左边的字符Length() 返回串的长度Locate() 找出串的一个子串Lower() 将串转换为小写LTrim() 去掉串左边的空格Right() 返回串右边的字符RTrim() 去掉串右边的空格Soundex() 返回串的SOUNDEX值SubString() 返回子串的字符Upper() 将串转换为大写
常用数值处理函数
Abs() 返回一个数的绝对值Cos() 返回一个角度的余弦Exp() 返回一个数的指数值Mod() 返回除操作的余数Pi() 返回圆周率Rand() 返回一个随机数Sin() 返回一个角度的正弦Sqrt() 返回一个数的平方根Tan() 返回一个角度的正切
SQL聚集函数AVG() 返回某列的平均值COUNT() 返回某列的行数MAX() 返回某列的最大值MIN() 返回某列的最小值SUM() 返回某列值之和
from_unixtime(day) 实现时间戳转换日期
转载于:https://www.cnblogs.com/nice2018/p/10441983.html
