MySQl 总结知识
--设置mysql 远程连接访问
grant select,update,insert,delete on *.* to root@192.168.11.30 identified by
"root";
GRANT ALL PRIVILEGES ON *.* TO 'root'@'websv';
--语句查询
Insert into ori_qk_issue(qcode, year,issue) select
'chenjt','2012','12' from dual
where not exists(select 1 from ori_qk_issue
where qcode='chenjt' and year='2012' and issue='12');
--创建存储过程
DELIMITER $$
CREATE DEFINER=`root`@`localhost`
PROCEDURE `InsertOri_issue_journal_resource`(
name1
varchar(1000) Charset 'utf8',
issn1 varchar(45) Charset
'utf8',
year1 varchar(50) Charset 'utf8',
issue1
varchar(50) Charset 'utf8',
title1 varchar(2000) Charset
'utf8',
keyword1 varchar(300) Charset 'utf8',
abstract1
varchar(8000) Charset 'utf8',
author1 varchar(2000) Charset
'utf8',
page1 varchar(255) Charset 'utf8',
code1
varchar(50) Charset 'utf8',
category1 varchar(1000) Charset
'utf8',
qcode1 varchar(50) Charset 'utf8',
fqcode1
varchar(40) Charset 'utf8',
pdf_url1 varchar(255) Charset
'utf8',
url1 varchar(1000) Charset 'utf8',
site_name1
varchar(200) Charset 'utf8',
date1 datetime
)
begin
declare journalId1 bigint;
declare issueId1 bigint;
if
exists(select 1 from ori_qk_issue where qcode=qcode1 and year=year1 and
issue=issue1) then
set issueId1=(select id from ori_qk_issue
where qcode=qcode1 and year=year1 and issue=issue1);
else
insert ori_qk_issue(qcode,year,issue)
values(qcode1,year1,issue1);
set issueId1=(select
LAST_INSERT_ID());
end if;
insert
ori_journal_resource(url,site_name,date,code)
values(url1,site_name1,date1,code1);
Insert
ori_journal(name,issn,year,issue,title,keyword,abstract,author,page,code,category,qcode,fqcode,pdf_dir)
values(name1,issn1,year1,issue1,title1,keyword1,abstract1,author1,page1,code1,category1,qcode1,fqcode1,pdf_url1);
set journalId1=(select LAST_INSERT_ID());
Insert
ori_issue_journal(issueId, journalId) values(issueId1,journalId1);
end
#清空数据
truncate table ori_journal;
truncate table
ori_journal_resource;
truncate table ori_issue_journal;
#查看进程,杀死进程
show processlist;
kill 192;
#创建索引
create index qcode_index on ori_qk_issue(qcode); #创建索引
drop
index qcode_index on ori_qk_issue; #删除索引
SHOW VARIABLES LIKE '%character%';
SHOW VARIABLES LIKE
'collation%';
character_set_database latin1
character_set_filesystem binary
character_set_results utf8
character_set_server latin1
collation_database latin1_swedish_ci
collation_server latin1_swedish_ci
SET GLOBAL character_set_database=utf8;
SET GLOBAL
character_set_server=utf8;
SET GLOBAL
collation_database=utf8_general_ci;
SET GLOBAL
collation_server=utf8_general_ci;
SET GLOBAL utf8;
DELIMITER $$
CREATE DEFINER=`root`@`localhost`
PROCEDURE `AddOri_qk`(
name1 varchar(300) Charset 'utf8',
qcode1 varchar(50) Charset 'utf8',
issn1 varchar(50) Charset
'utf8',
category1 varchar(50) Charset 'utf8',
grab_url1
varchar(200) Charset 'utf8'
)
Insert
ori_qk(name,qcode,issn,category,grab_url)
values(name1,qcode1,issn1,category1,grab_url1)
自动编号:
declare @x int ; set @x=0;
select temp.* from(
select
@x:=@x+1 as Rownum,ori_companysic.* from ori_companysic where
SIC_Code='01000000')temp where temp.Rownum between 1 and 10;
查看列:desc 表名;
修改表名:alter table t_book rename to bbb;
添加列:alter
table 表名 add column 列名 varchar(30);
删除列:alter table 表名 drop column 列名;
修改列名MySQL: alter table bbb change nnnnn hh int;
修改列名SQLServer:exec
sp_rename't_student.name','nn','column';
修改列名Oracle:lter table bbb rename
column nnnnn to hh int;
修改列属性:alter table t_book modify name varchar(22);