以前的SQL学习笔记
时间：2006-05-15 来源：pplboy
学无止境 2003-5-1 11:24
************************************************数据管理*****************************************
{
insert into table_name(id1,id2,id3) values(_,_,_);
insert into table_name(id1,id2,id3) select id1,id2,id3 from othertable_name where ..; update table_name set id1=value1,id2=value2,id3=value3;
update table_name set id1=value1,id2=value2,id3=value3 where serch_condition;
update table_name set id1=value1,id2=value2,id3=value3 from table_list where serch_condition; delete from table_name where condition;
-------------------------------------------------
create database database_name; create table table_name(field1 datatype[not null],field2 datatype[not null],field3 datatype[not null]);
create table table_name(field1,field2,field3) as (select field1,field2,field3 from old_table<where...>); alter table table_name add column_name data_type;|modify column_name data_type; drop table table_name;
drop database database_name;
?如果需要删除字段怎么办？？ alter table table_name drop column_name ;
}
*************************************************************select****************************************
{
select * from table_name;
select id1+id2*id3 idall from table_name;
select distinct|all id from table_name;
select id from table_name1,table_name2 where id2+|-|*|/|d2=|<|>|<>|!=id3;
true false unknown
select id from table_name where id2 is null;
select id from table_name where id2 = null;<false>
= >= <= != <>
is like and or not
_ || * <----[模糊搜索]
select id from table_name where id2 like "%__%";
select id from table_name where id2 like "%__%" and id3 ="dd";
select id from table_name where id2 like "%__%" not id5 ="pp";
select * from tablename where id1 starting with('aa');查找包含aa的数据列同like select id1 from table_name union(联合)|union all select id1 from table_name ;//集合操作符
select id1 from table_name intersect(交) select id1 from table_name ;//集合操作符
select id1 from table_name minus(差)(difference) select id1 from table_name ;//集合操作符 select * from tablename where id1='aa' or id1='bb' or id1='cc';
=select * from tablename where id in('aa','bb','cc'); select * from tablename where id1='aa' and id1='bb';
=select * from tablename where id between 'aa' and 'bb'; <-----[排序搜索]
select * from tablename order by id1 desc(递减)|asc（递增,默认）,id1 desc(递减)|asc（递增,默认）; select * from table_name group by id; }
**************************************************************子句***************************************************
排序order by
group by
having
starting with
**************************************************************47个函数*********************************************8
///////////////////////////////////////////////
聚集函数(分组函数)
///////////////////////////////////////////////
count();符合列个数
select count(id) from table_name;
sum();求列和 仅用于数值型
select sum(id) from table_name;
avg();求列平均值
select avg(id) from table_name;
max();求最大值 数字都可
select max(id) from table_name;
min();求最小值
select min(id) from table_name;
variance();产生标准差方 仅用于数值型
stddev();得到一列的数据差方 仅用于数值型
//////////////////////////////////////////
日期和时间函数
/////////////////////////////////////////
add_months(日期型列,增月值); 用于改变月份
last_day(日期型列)； 返回列中日期所在月的最后一天
months_between
new_time
next_day
sysdate
/////////////////////////////////////////
算术函数
/////////////////////////////////////////
abs() 返回绝对值
select abs(id) from table_name;
ceil() 返回最小整数值
floor () 返回最大整数值
cos()
sin()
tan()
sinh()
cosh()
tanh()
exp() 以参数为底的指数函数
ln() 参数的自然对数
log() 以10为底的列id的对数值
select log(id,10) from table_name;
mod()
power()
sign()
sqrt() 平方根
//////////////////////////////////////////////
字符函数
//////////////////////////////////////////////
chr() 返回字符集 如下例中的code列值若为67则返回C
select chr(code) from table_name;
concat() 连接多个字符列
select concat(id1,id2) from table_name;
initcap() 首字母大写 mysql不可用
lower() 所有字母小写
upper() 所有字母大写
lpad(id,填充个数,"填充字符") 左填充字符
rpad(id,填充个数,"填充字符") 右填充字符 填充子父母默认唯空格
select lpad(id1,20,'*') from table_name
ltrim(id,'要抠去的字符')
rtrim(id,'要抠去的字符')
replace(id,'要查找的字符','要替换的字符') 替换字符串 可用
substr(id,'要输出的字符串第一个字符的位置','要输出的位数') mysql为substring
translate(id,'from串','to串'); 替换----好像可以用于密码解码
select translate(id1,'1234567890','nnnndddddd'); mysql不可用
instr(id,'要查找的字符','开始查找的位置','')
length() 返回字符串的长度
////////////////////////////////////////
转换函数
////////////////////////////////////////
to_char()
to_number()
////////////////////////////////////////
其它函数
/////////////////////////////////////////
greatest(id1,id2,id3); 找寻列中的最大和最小成员。。
least(id1,id2,id3);
user(); **********************************************例程
select payee,sum(amount) total, count(payyee) number_written from ghecks group by payyee having sum(amount)>50; drop database download;
create database download;
use download;
create table download(
id int primary key auto_increment,mtime date,
filename varchar(160),size int,
typename char(20),intro text,hits int); #--use databasename; #--connect databasename; #--select database(); #--show tabales; #--describe tablename; #--select * from;
#############----------------sql基本命令---------------------------
###select 字段1 字段2 ... from 数据表名;
### select 字段1 字段2 ... from 数据表名 where 字段='条件' or/and/no 字段='条件'...; ###create database databasename;
###drop database databasename; ###create table tablename(字段1 数据类型 属性 默认值 其它,
字段2 数据类型 属性 默认值 其它,
字段3 数据类型 属性 默认值 其它, ...);
###drop table table_name;
###alter table table_name modify 字段 new_属性,add column birthday date; ###insert into tablename values(字段1的值,字段2的值,字段3的值,...);
###insert into tablename(指定字段1,指定字段2,指定字段3,...)
values(字段1的值,字段2的值,字段3的值,...);
###insert into tablename set col_name=expression,col_name=expression......
###insert into tablename(col_name) select ...... ###delete from tablename where 字段1 = '值1' 逻辑运算符 字段2 = '值2'; ###update tablename set 字段1 = '值1',字段2 = '值2';
###update tablename set 字段1 = '值1',字段2 = '值2'
where 特定字段1='值1'逻辑运算符 特定字段2='值2'; ###describe table_name; ###optimize table table_name;优化表
###逻辑运算符---and(&&),or(||),not(!)
############----------------管理命令
###use mysql;
update user set password=password('youpassword')
where host='localhost' and use='root';
###flush flush_option 清除内部缓存
###show processlist; 显示线程
###kill thread_id; 杀死线程
###########-----------------外部命令
###mysqladmin###更改密码，建立删除数据库，查询mysql状态,查询mysql是否存活,关闭服务
###mysqladmin -u uername -ppassword command1 command2
###1#mysqladmin -u root -p123456 create test1//databasename
###2#mysqladmin -u root -p123456 drop test1//databasename
###3#mysqladmin -u root -p password newpassword
###4#mysqladmin -u root -p123456 shoutdown ###mysqlaccess###查询存取权限
###mysqlaccess 主机 用户 数据库 -u usename -p password ###mysqlshow###显示mysql数据库的结构，包括：数据表，数据库，字段。
###mysqlshow -u usename -p password [数据库[数据表[字段]]]
###mysqlshow -u root -p123456 mysql
###mysqlshow -u root -p123456 mysql user
###mysqlshow -u root -p123456 mysql user host ###mysqldump###输出sql文件 > sql.sql
### 参数-l .lock.
###mysqldump -u usename -p password 数据库[数据表1 数据表2 ...]
###mysqldump -u root -p123456 mysql
###mysqldump -u root -p123456 mysql host
###mysqldump -u root -p123456 mysql host user ###mysql < sql.sql ########################################-------------------------------用户管理
###grant 权限，权限 on 表 to 用户
1.grant all on *.* to caolei;
2.grant select,insert,update,delete,index,alter,create,drop,grant,file,reload,shoutdown,process on db_name.table_name to user;
3.(完全的样子)GRANT privileges (columns) ON what TO user IDENTIFIED BY "password" WITH GRANT OPTION
4.insert into user set host='localhost',user='blue',reload_priv='y',process_priv='y';
###revoke 权限 on 表 from 用户
revoke select,insert,update,delete,index,alter,create,drop,grant,file,reload,shoutdown,process on db_name.table_name from user;
###update 表 set 字段='value' where 字段='value'
1.update user set password=password('new_password') where user='user_name';
2.set password for user_name=password('new_password'); 注意：新设置的密码需要使用
flush privileges;
命令生效。
###########有用的实例---初始化新的数据库，以增加其安全性。
//1，删除匿名用户
use mysql;
delete form user where user= ";
//2，修改root密码
use mysql;
update user set password=password('root') where user='root';
flush privileges; ##########----------------------------------select command
select all|distinct|distinctrow select_list,select_list
into outfile 'file_name' export_options
from tbl_list
where where_expr
order by tbl_listname asc|desc
limit[offset,]rows;
2.3 避免授权表风险
本届介绍一些在你授权时的一些预防措施，以及不明值的选择带来的风险。一般地，你要很“吝啬”地授予超级用户权限，即不要启用user表中条目中的权限，而使用其它授权表，以将用户权限限制于数据库、表、或列。在user表中的权限允许于影响到你的服务器操作或能访问任何数据库中的任何表。 不要授予对mysql数据库的权限。一个拥有包含授权表数据库权限的用户可能会修改表以获取对其他任何数据库的权限。授予允许一个用户修改mysql数据库表的权限也实际上给了用户以一个全局GRANT权限。如果用户能直接修改表，这也等价于能够发出任何你能想象的任何GRANT语句。 FILE权限尤其危险，不要轻易授权它。以下是一个拥有FILE权限的人能干除的事情： CREATE TABLE etc_passwd (pwd_entry TEXT);
LOAD DATA INFILE "/etc/passwd" into TABLE etc_passwd;
SELECT * FROM etc_passwd; 在发出这些语句后，用户已经拥有了你的口令文件的内容了。实际上，服务器上任何公开可读文件的内容都可被拥有FILE权限的用户通过网络访问。 FILE权限也能被利用来危害没有设置足够权限制的文件权限的系统上的数据库。这就是你为什么应该设置数据目录只能由服务器读取的原因。如果对应于数据库表的文件可被任何人读取，不只是用户服务器账号的用户可读，任何有FILE权限的用户也可通过网络连接并读取它们。下面演示这个过程：
·创建一个有一个LONGBLOB列的表：
USER test;
CREATE TABLE tmp (b LONGBLOB);
·使用该表读取每个对应于你想偷取的数据库表文件的内容，然后将表内容写入你自己数据库的一个文件中： LOAD DATA INFILE "./other_db/x.frm" INTO TABLE tmp
FIELDS ESCAPED BY "" LINES TERMINATED BY "";
SELECT * FROM tmp INTO OUTFILE "y.frm"
FIELDS ESCAPED BY "" LINES TERMINATED BY "";
DELETE FROM tmp;
LOAD DATA INFILE "./other_db/x.ISD" INTO TABLE tmp
FIELDS ESCAPED BY "" LINES TERMINATED BY "";
SELECT * FROM tmp INTO OUTFILE "y.ISD"
FIELDS ESCAPED BY "" LINES TERMINATED BY "";
DELETE FROM tmp;
LOAD DATA INFILE "./other_db/x.ISM" INTO TABLE tmp
FIELDS ESCAPED BY "" LINES TERMINATED BY "";
SELECT * FROM tmp INTO OUTFILE "y.ISM" ·现在你拥有了一个新表y，它包含other_db.x的内容并且你有全权访问它。
为避免让人以同样的方式攻击，根据“第一部分 内部安全性-保护你的数据目录”中的指令设置你的数据目录上的权限。你也可以在你启动服务器时使用--skip-show-database选项限制用户对于他们没用访问权限的数据库使用SHOW DATABASES和SHOW TABLES。这有助于防止用户找到关于它们不能访问的数据库和表的信息。 这对管理员新手（有时对有经验的老手）是很困惑的事情，当时解决方法很简单：在你改变了它们之后告诉服务器重载授权表内容，你可以发一条FLUSH PRIVILEGES或执行mysqladmin flush-privileges（或如果你有一个不支持flush-privileges的老版本，用mysqladmin reload。）。
php连接
require('./libraries/grab_globals.lib.php');
incloud('') 跳转语句
<meta http-equiv="refresh" content="0;URL=11">
{
insert into table_name(id1,id2,id3) values(_,_,_);
insert into table_name(id1,id2,id3) select id1,id2,id3 from othertable_name where ..; update table_name set id1=value1,id2=value2,id3=value3;
update table_name set id1=value1,id2=value2,id3=value3 where serch_condition;
update table_name set id1=value1,id2=value2,id3=value3 from table_list where serch_condition; delete from table_name where condition;
-------------------------------------------------
create database database_name; create table table_name(field1 datatype[not null],field2 datatype[not null],field3 datatype[not null]);
create table table_name(field1,field2,field3) as (select field1,field2,field3 from old_table<where...>); alter table table_name add column_name data_type;|modify column_name data_type; drop table table_name;
drop database database_name;
?如果需要删除字段怎么办？？ alter table table_name drop column_name ;
}
*************************************************************select****************************************
{
select * from table_name;
select id1+id2*id3 idall from table_name;
select distinct|all id from table_name;
select id from table_name1,table_name2 where id2+|-|*|/|d2=|<|>|<>|!=id3;
true false unknown
select id from table_name where id2 is null;
select id from table_name where id2 = null;<false>
= >= <= != <>
is like and or not
_ || * <----[模糊搜索]
select id from table_name where id2 like "%__%";
select id from table_name where id2 like "%__%" and id3 ="dd";
select id from table_name where id2 like "%__%" not id5 ="pp";
select * from tablename where id1 starting with('aa');查找包含aa的数据列同like select id1 from table_name union(联合)|union all select id1 from table_name ;//集合操作符
select id1 from table_name intersect(交) select id1 from table_name ;//集合操作符
select id1 from table_name minus(差)(difference) select id1 from table_name ;//集合操作符 select * from tablename where id1='aa' or id1='bb' or id1='cc';
=select * from tablename where id in('aa','bb','cc'); select * from tablename where id1='aa' and id1='bb';
=select * from tablename where id between 'aa' and 'bb'; <-----[排序搜索]
select * from tablename order by id1 desc(递减)|asc（递增,默认）,id1 desc(递减)|asc（递增,默认）; select * from table_name group by id; }
**************************************************************子句***************************************************
排序order by
group by
having
starting with
**************************************************************47个函数*********************************************8
///////////////////////////////////////////////
聚集函数(分组函数)
///////////////////////////////////////////////
count();符合列个数
select count(id) from table_name;
sum();求列和 仅用于数值型
select sum(id) from table_name;
avg();求列平均值
select avg(id) from table_name;
max();求最大值 数字都可
select max(id) from table_name;
min();求最小值
select min(id) from table_name;
variance();产生标准差方 仅用于数值型
stddev();得到一列的数据差方 仅用于数值型
//////////////////////////////////////////
日期和时间函数
/////////////////////////////////////////
add_months(日期型列,增月值); 用于改变月份
last_day(日期型列)； 返回列中日期所在月的最后一天
months_between
new_time
next_day
sysdate
/////////////////////////////////////////
算术函数
/////////////////////////////////////////
abs() 返回绝对值
select abs(id) from table_name;
ceil() 返回最小整数值
floor () 返回最大整数值
cos()
sin()
tan()
sinh()
cosh()
tanh()
exp() 以参数为底的指数函数
ln() 参数的自然对数
log() 以10为底的列id的对数值
select log(id,10) from table_name;
mod()
power()
sign()
sqrt() 平方根
//////////////////////////////////////////////
字符函数
//////////////////////////////////////////////
chr() 返回字符集 如下例中的code列值若为67则返回C
select chr(code) from table_name;
concat() 连接多个字符列
select concat(id1,id2) from table_name;
initcap() 首字母大写 mysql不可用
lower() 所有字母小写
upper() 所有字母大写
lpad(id,填充个数,"填充字符") 左填充字符
rpad(id,填充个数,"填充字符") 右填充字符 填充子父母默认唯空格
select lpad(id1,20,'*') from table_name
ltrim(id,'要抠去的字符')
rtrim(id,'要抠去的字符')
replace(id,'要查找的字符','要替换的字符') 替换字符串 可用
substr(id,'要输出的字符串第一个字符的位置','要输出的位数') mysql为substring
translate(id,'from串','to串'); 替换----好像可以用于密码解码
select translate(id1,'1234567890','nnnndddddd'); mysql不可用
instr(id,'要查找的字符','开始查找的位置','')
length() 返回字符串的长度
////////////////////////////////////////
转换函数
////////////////////////////////////////
to_char()
to_number()
////////////////////////////////////////
其它函数
/////////////////////////////////////////
greatest(id1,id2,id3); 找寻列中的最大和最小成员。。
least(id1,id2,id3);
user(); **********************************************例程
select payee,sum(amount) total, count(payyee) number_written from ghecks group by payyee having sum(amount)>50; drop database download;
create database download;
use download;
create table download(
id int primary key auto_increment,mtime date,
filename varchar(160),size int,
typename char(20),intro text,hits int); #--use databasename; #--connect databasename; #--select database(); #--show tabales; #--describe tablename; #--select * from;
#############----------------sql基本命令---------------------------
###select 字段1 字段2 ... from 数据表名;
### select 字段1 字段2 ... from 数据表名 where 字段='条件' or/and/no 字段='条件'...; ###create database databasename;
###drop database databasename; ###create table tablename(字段1 数据类型 属性 默认值 其它,
字段2 数据类型 属性 默认值 其它,
字段3 数据类型 属性 默认值 其它, ...);
###drop table table_name;
###alter table table_name modify 字段 new_属性,add column birthday date; ###insert into tablename values(字段1的值,字段2的值,字段3的值,...);
###insert into tablename(指定字段1,指定字段2,指定字段3,...)
values(字段1的值,字段2的值,字段3的值,...);
###insert into tablename set col_name=expression,col_name=expression......
###insert into tablename(col_name) select ...... ###delete from tablename where 字段1 = '值1' 逻辑运算符 字段2 = '值2'; ###update tablename set 字段1 = '值1',字段2 = '值2';
###update tablename set 字段1 = '值1',字段2 = '值2'
where 特定字段1='值1'逻辑运算符 特定字段2='值2'; ###describe table_name; ###optimize table table_name;优化表
###逻辑运算符---and(&&),or(||),not(!)
############----------------管理命令
###use mysql;
update user set password=password('youpassword')
where host='localhost' and use='root';
###flush flush_option 清除内部缓存
###show processlist; 显示线程
###kill thread_id; 杀死线程
###########-----------------外部命令
###mysqladmin###更改密码，建立删除数据库，查询mysql状态,查询mysql是否存活,关闭服务
###mysqladmin -u uername -ppassword command1 command2
###1#mysqladmin -u root -p123456 create test1//databasename
###2#mysqladmin -u root -p123456 drop test1//databasename
###3#mysqladmin -u root -p password newpassword
###4#mysqladmin -u root -p123456 shoutdown ###mysqlaccess###查询存取权限
###mysqlaccess 主机 用户 数据库 -u usename -p password ###mysqlshow###显示mysql数据库的结构，包括：数据表，数据库，字段。
###mysqlshow -u usename -p password [数据库[数据表[字段]]]
###mysqlshow -u root -p123456 mysql
###mysqlshow -u root -p123456 mysql user
###mysqlshow -u root -p123456 mysql user host ###mysqldump###输出sql文件 > sql.sql
### 参数-l .lock.
###mysqldump -u usename -p password 数据库[数据表1 数据表2 ...]
###mysqldump -u root -p123456 mysql
###mysqldump -u root -p123456 mysql host
###mysqldump -u root -p123456 mysql host user ###mysql < sql.sql ########################################-------------------------------用户管理
###grant 权限，权限 on 表 to 用户
1.grant all on *.* to caolei;
2.grant select,insert,update,delete,index,alter,create,drop,grant,file,reload,shoutdown,process on db_name.table_name to user;
3.(完全的样子)GRANT privileges (columns) ON what TO user IDENTIFIED BY "password" WITH GRANT OPTION
4.insert into user set host='localhost',user='blue',reload_priv='y',process_priv='y';
###revoke 权限 on 表 from 用户
revoke select,insert,update,delete,index,alter,create,drop,grant,file,reload,shoutdown,process on db_name.table_name from user;
###update 表 set 字段='value' where 字段='value'
1.update user set password=password('new_password') where user='user_name';
2.set password for user_name=password('new_password'); 注意：新设置的密码需要使用
flush privileges;
命令生效。
###########有用的实例---初始化新的数据库，以增加其安全性。
//1，删除匿名用户
use mysql;
delete form user where user= ";
//2，修改root密码
use mysql;
update user set password=password('root') where user='root';
flush privileges; ##########----------------------------------select command
select all|distinct|distinctrow select_list,select_list
into outfile 'file_name' export_options
from tbl_list
where where_expr
order by tbl_listname asc|desc
limit[offset,]rows;
2.3 避免授权表风险
本届介绍一些在你授权时的一些预防措施，以及不明值的选择带来的风险。一般地，你要很“吝啬”地授予超级用户权限，即不要启用user表中条目中的权限，而使用其它授权表，以将用户权限限制于数据库、表、或列。在user表中的权限允许于影响到你的服务器操作或能访问任何数据库中的任何表。 不要授予对mysql数据库的权限。一个拥有包含授权表数据库权限的用户可能会修改表以获取对其他任何数据库的权限。授予允许一个用户修改mysql数据库表的权限也实际上给了用户以一个全局GRANT权限。如果用户能直接修改表，这也等价于能够发出任何你能想象的任何GRANT语句。 FILE权限尤其危险，不要轻易授权它。以下是一个拥有FILE权限的人能干除的事情： CREATE TABLE etc_passwd (pwd_entry TEXT);
LOAD DATA INFILE "/etc/passwd" into TABLE etc_passwd;
SELECT * FROM etc_passwd; 在发出这些语句后，用户已经拥有了你的口令文件的内容了。实际上，服务器上任何公开可读文件的内容都可被拥有FILE权限的用户通过网络访问。 FILE权限也能被利用来危害没有设置足够权限制的文件权限的系统上的数据库。这就是你为什么应该设置数据目录只能由服务器读取的原因。如果对应于数据库表的文件可被任何人读取，不只是用户服务器账号的用户可读，任何有FILE权限的用户也可通过网络连接并读取它们。下面演示这个过程：
·创建一个有一个LONGBLOB列的表：
USER test;
CREATE TABLE tmp (b LONGBLOB);
·使用该表读取每个对应于你想偷取的数据库表文件的内容，然后将表内容写入你自己数据库的一个文件中： LOAD DATA INFILE "./other_db/x.frm" INTO TABLE tmp
FIELDS ESCAPED BY "" LINES TERMINATED BY "";
SELECT * FROM tmp INTO OUTFILE "y.frm"
FIELDS ESCAPED BY "" LINES TERMINATED BY "";
DELETE FROM tmp;
LOAD DATA INFILE "./other_db/x.ISD" INTO TABLE tmp
FIELDS ESCAPED BY "" LINES TERMINATED BY "";
SELECT * FROM tmp INTO OUTFILE "y.ISD"
FIELDS ESCAPED BY "" LINES TERMINATED BY "";
DELETE FROM tmp;
LOAD DATA INFILE "./other_db/x.ISM" INTO TABLE tmp
FIELDS ESCAPED BY "" LINES TERMINATED BY "";
SELECT * FROM tmp INTO OUTFILE "y.ISM" ·现在你拥有了一个新表y，它包含other_db.x的内容并且你有全权访问它。
为避免让人以同样的方式攻击，根据“第一部分 内部安全性-保护你的数据目录”中的指令设置你的数据目录上的权限。你也可以在你启动服务器时使用--skip-show-database选项限制用户对于他们没用访问权限的数据库使用SHOW DATABASES和SHOW TABLES。这有助于防止用户找到关于它们不能访问的数据库和表的信息。 这对管理员新手（有时对有经验的老手）是很困惑的事情，当时解决方法很简单：在你改变了它们之后告诉服务器重载授权表内容，你可以发一条FLUSH PRIVILEGES或执行mysqladmin flush-privileges（或如果你有一个不支持flush-privileges的老版本，用mysqladmin reload。）。
php连接
require('./libraries/grab_globals.lib.php');
incloud('') 跳转语句
<meta http-equiv="refresh" content="0;URL=11">
相关阅读 更多 +
