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);