1.总起来就四个字CURD C=create U=update R=read D=delete
对应语句就是insert into | update | select | delete
2.注意防止sql injection
txtNam = getRequestString("CustomerName");
txtAdd = getRequestString("Address");
txtCit = getRequestString("City");
txtSQL = "INSERT INTO Customers (CustomerName,Address,City) Values(@0,@1,@2)";
db.Execute(txtSQL,txtNam,txtAdd,txtCit);
这方法叫做参数化sql语句，先建立一个sql语句的样式pattern，可变部分作为参数传入。
3.第一次了解了foreign key 的用法
select customers.customerid,orders.orderdate,customers.city,customer.country
from customers inner join orders on customers.customerid=orders.customerid where customers.city='london' and orders.orderid <77
inner 所得的结果是join
4.alias用于组合一些列，并且暂时的重命名
5.获取最后插入项的id
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDBPDO";
try {
$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
// set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('John', 'Doe', 'john@example.com')";
// use exec() because no results are returned
$conn->exec($sql);
$last_id = $conn->lastInsertId();
echo "New record created successfully. Last inserted ID is: " . $last_id;
}
catch(PDOException $e)
{
echo $sql . "<br>" . $e->getMessage();
}
$conn = null;
?>
将结果按日期分组
1、每年
select year(ordertime) 年，
sum(Total) 销售合计
from 订单表
group by year(ordertime)
2、每月
select year(ordertime) 年，
month(ordertime) 月,
sum(Total) 销售合计
from 订单表
group by year(ordertime)，
month(ordertime
3、每日
select year(ordertime) 年，
month(ordertime) 月,
day(ordertime) 日，
sum(Total) 销售合计
from 订单表
group by year(ordertime)，
month(ordertime),
day(ordertime)
另外每日也可以这样:
select convert(char(8),ordertime,112) dt，
sum(Total) 销售合计
from 订单表
group by convert(char(8),ordertime,112)
mysql设置定时任务 设置trigger
还有操作系统的定时任务
view
procedure都是可以用到的手段
想想数据库优化怎么来做
1.求两个表的差集
select idabc from abc where idabc not in (select idabc from abc where abccol1 is null);
一些可能用到的函数或语言特性when distinct case if not exist ifnull ord conv cast day month unix_timestamp
select * from ddd inner join(select * from abc) as ddd;子查询必须有自己的别名
2.
DELIMITER |
CREATE EVENT e_daily
ON SCHEDULE
EVERY 1 DAY
COMMENT 'Saves total number of sessions then clears the table each day.'
DO
BEGIN
INSERT INTO site_activity.totals (when, total)
SELECT CURRENT_TIMESTAMP, COUNT(*)
FROM site_activity.sessions;
DELETE FROM site_activity.sessions;
END |
DELIMITER
创建事件,值得注意的是current_timestamp 可以加入到select的结果中
CREATE EVENT srvchange
ON SCHEDULE EVERY 1 MONTH STARTS DATE_ADD(DATE_ADD(DATE_SUB(CURDATE(),INTERVAL DAY(CURDATE())-1 DAY), INTERVAL 1 MONTH),INTERVAL 1 HOUR)
ON COMPLETION PRESERVE ENABLE
DO
update rm_users set srvid=nextsrv,expiration=nextsrv_date , nextsrv=null ,expiration=null where srvid is not null;
curdate()这一函数的用法 以及date_add 和date_sub
3.一些系统表的设置和更改
SHOW VARIABLES LIKE '%sche%';
set global event_scheduler=1;
SHOW PROCESSLIST;
select * from information_schema.events;显示系统中的事件列表
4.group by floor 通过对相应列 的运算的结果来对行进行分组，计算时间差可以用datediff也可以用to_days(curdate)-todays(datecol);
5.union+group by的时候可以用select * from (select from A union all select * from B) as T group by abc
6.to_days返回天数,datediff返回两个时间差，参数1减去参数2以天计算，period_diff计算月份差，dateformat用于对取出的时间进行格式化,extract用于提取日期中指定的部分.
7.date_add(),date_diff(),date_sub()
8.coalesce(p1,p2,p3...)返回参数列表中的第一个非空表达式
9.mysql found_rows()函数得到总行数，我们在使用limit子句的时候，有时候需要得到总的行数，我们指定选择语句除了进行limit筛选还统计总数，即select SQL_CALC_FOUND_ROWS * from rm_users limit 0,15;
然后调用select found_rows()得到总行数。
10.create table xxx{}engine=memory可以建立内存表
11.create procedure abc()
begin end建立存储过程
12.create view name as select statement
13.
show procedure status;
show create procedure searcharea;
show create event onlinestats;
显示相关创建语句
14. 要想从procedure返回值的话，应该用select into 而不是什么return 或者 set @res =select语句
15.用date_format处理后，就变成字符串类型了，不能再用日期函数进行运算
16.extract(year from date)这是extract 的一般样式
17.str_to_date和date_format相反，他把字符串转换成日期类型
18.关于trigger
old表示删除的数据，或者更新之前的数据，new表示新insert的数据，或者update的数据
19.create table设置字段检查check(id>0 and city='weifang')
20.通配符%匹配任意多个字符 ，'_' 匹配一个字符
FROM ［user］ WHERE u_name REGEXP ‘三$’; FROM ［user］ WHERE u_name NOT REGEXP ‘三$’;支持正则匹配
21.http://www.cnblogs.com/rootq/archive/2009/05/27/1490523.html关于delimiter
默认的delimiter是分号；当写符合语句的时候，比如
create procedure abc()
begin
insert into area(area_id,area,parent_id) values(888,'sdds',579);
end;
默认遇到第一个分号，579);mysql 就要执行了，这时候算上 end;一共是两句显然会造成语法错误
22.from_unixtime()和unix_timestamp这是一对
STR_TO_DATE()和DATE_FORMAT()这是一对
23.mysql导入导出sql文件
mysqldump -u root -psecret mydatabase >backupfile.sql
mysql -h localhost -u username -ppassword -d dbname <initdb.sql
24.一些包含简单逻辑的mysql 语句 select * from atable where col1 in (select * from tb2)|("asds","asdfasd","asdfads","wefew","asdfasdf")
25.函数ifnull() substr(), coalesce
26.mysql -u root -psecretmysql -u root -psecret radius -e 'set global event_scheduler=1在登录mysql的同时执行一条语句
27.
SELECT t1.framedipaddress FROM radippool AS t1
JOIN
(
SELECT ROUND
(
RAND() *
(
( SELECT MAX(id) FROM radippool WHERE pool_name = '到期后' AND pool_key IS NULL)
- ( SELECT MIN(id) FROM radippool WHERE pool_name = '到期后' AND pool_key IS NULL)
) +
( SELECT MIN(id) FROM radippool WHERE pool_name = '到期后' AND pool_key IS NULL)
) AS id
) AS t2 WHERE t1.id >= t2.id AND pool_name = '到期后' AND pool_key IS NULL ORDER BY t1.id LIMIT 1;
join的运用方式
28.可以为无限级分类表增加一个path字段来记录父分类的ID，这样可以增加一些查询效率.
29.mysql 的left join 和之前理解的left join 不太一样
30.mysql 中 对is null 和 =null的处理是不一样的，如果不设置set ANSI_NULLS off 那么 =null 或者!null总是返回零行
31.外键约束还是挺有用的,应用时可以先清空表
32.一种实现字段+1的方法 update table set order=order+1 where id=2
33.update tb1,tb2 set tb1.sd=tb2.ee;
34.非常隐蔽的错误，有时候导出工具会给字段默认指定latin字符集，造成插入中文的时候失败.
35.默认是latin 这可以在my.cnf里面更改
36.使用find_in_set(str,strlist)来从逗号分割的字符串中查找值,可以和replace 函数配合使用
37.建立数据库表的时候，如果是一对一的关系可以考虑双向索引，也就是说关联的两个表都可以存放另一个表的ID
如果是用户名和用户ID这种，在引用的表里可以考虑加上用户名这样在用的时候甚至不用链接另一个表就可以直接取到用户名
38.上一篇下一篇功能
(select id from table where id < 10 order by id asc limit 1)
union all
(select id from table where id = 10)
union all
(select id from table where id > 10 order by id desc limit 1);
39.navcat下没有datetime 类型，如果需要使用default current_timestamp,需要指定为timestamp类型
40.Insert into Table2(field1,field2,...) select value1,value2,... from Table1 可以进行表复制http://www.cnblogs.com/freshman0216/archive/2008/08/15/1268316.html，也可以使用select a,c into tb1 from tb2
41. 获取下一个自增ID
SELECT auto_increment FROM information_schema.`TABLES` WHERE `TABLE_SCHEMA`='tree' AND TABLE_NAME='play'
42. mysql 使用using 关键字和join语句搭配，表示在连接的两个表中均存在的field，比用on可以少打几个字 e.g. a left join b using(blogid)
43. 最近使用mysql 遇到一个怪异的问题， select * 和 count(*) 得到的记录数不一样， 经过搜索发现，这是因为mysql 发生了表错误， 检查可以通过check table table_name 修复是通过 repair table table_name
44. 注意replace into的使用，这种可以替代insert into 如果原表中存在记录，则先删掉这条记录，然后再插入，这样的语法带来一些便利，但在master slave的系统中会由于auto increment等特性造成错误
45.关于数据库水平拆分时的排序问题，既然要用到水平拆分了，那么说明数据量肯定是很大了，按此推理，那么这个整体的排序，肯定也是比较慢的，但是如果在往各个分表中插入数据的时候就考虑到排序问题，也就是说，一个分表总是存某一个次序范围内的数据，那么排序问题就迎刃而解了，也就是说需要取哪张表中的数据，可以通过程序计算出来.
