sql,Code片段,代码实例,代码示例,代码片段库

sql SQLServer的のTransactionLogのサイズを确认するコマンド

SQLServer的のTransactionLogのサイズを确认するコマンド

TransactionLogSize.sql

dbcc sqlperf(logspace)

sql SQLServer的のDBのトランザクションログの拡张履歴を调べるSQLファイル

SQLServer的のDBのトランザクションログの拡张履歴を调べるSQLファイル

TransactionLogHistory.sql

USE <調べたいDB>

DECLARE @filename varchar(500)
-- trcファイルのローカルパス
SELECT @filename = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\Log\log_110.trc'
SELECT @filename
SELECT
    convert(int, EventClass) as EventClass,
    DatabaseName,
    Filename,
    (Duration/1000) as Duration,
    StartTime,
    EndTime,
    (IntegerData*8.0/1024) as ChangeInSize
FROM
    sys.fn_trace_gettable(@filename, default)
WHERE
    EventClass >=  92
    AND
    EventClass <=  95
    AND
    DatabaseName = DB_NAME()
ORDER BY
    StartTime DESC

sql 与SQL的空的回收站

与SQL的空的回收站

empty-recycle-bin.sql

delete from cmsPreviewXml where nodeId in (select id from umbracoNode where path like '%-20%' and id!=-20)
delete from cmsContentVersion where contentId in (select id from umbracoNode where path like '%-20%' and id!=-20)
delete from cmsDocument where nodeId in (select id from umbracoNode where path like '%-20%' and id!=-20)
delete from cmsContentXML where nodeId in (select id from umbracoNode where path like '%-20%' and id!=-20)
delete from cmsContent where nodeId in (select id from umbracoNode where path like '%-20%' and id!=-20)
delete from cmsPropertyData where contentNodeId in (select id from umbracoNode where path like '%-20%' and id!=-20)
delete from dbo.umbracoRelation where childId in (select id from umbracoNode where path like '%-20%' and id!=-20)
delete from dbo.umbracoRelation where parentId in (select id from umbracoNode where path like '%-20%' and id!=-20)
delete from dbo.umbracoDomains where domainRootStructureID in (select id from umbracoNode where path like '%-20%' and id!=-20)
-- delete the XML nodes....
delete from umbracoNode where path like '%-20%' and id!=-20

sql 与SQL的空的回收站

与SQL的空的回收站

empty-recycle-bin.sql

delete from cmsPreviewXml where nodeId in (select id from umbracoNode where path like '%-20%' and id!=-20)
delete from cmsContentVersion where contentId in (select id from umbracoNode where path like '%-20%' and id!=-20)
delete from cmsDocument where nodeId in (select id from umbracoNode where path like '%-20%' and id!=-20)
delete from cmsContentXML where nodeId in (select id from umbracoNode where path like '%-20%' and id!=-20)
delete from cmsContent where nodeId in (select id from umbracoNode where path like '%-20%' and id!=-20)
delete from cmsPropertyData where contentNodeId in (select id from umbracoNode where path like '%-20%' and id!=-20)
delete from dbo.umbracoRelation where childId in (select id from umbracoNode where path like '%-20%' and id!=-20)
delete from dbo.umbracoRelation where parentId in (select id from umbracoNode where path like '%-20%' and id!=-20)
delete from dbo.umbracoDomains where domainRootStructureID in (select id from umbracoNode where path like '%-20%' and id!=-20)
-- delete the XML nodes....
delete from umbracoNode where path like '%-20%' and id!=-20

sql 修复了错误 - 缺少逗号和最后一个drop table命令

修复了错误 - 缺少逗号和最后一个drop table命令

schema.mysql.sql

drop table if exists games;
drop table if exists teams;
drop table if exists events;
drop table if exists events_players;
drop table if exists events_penaltybox;

drop table if exists players;
drop table if exists stats_skaters_summary;
drop table if exists stats_skaters_timeonice;
drop table if exists stats_skaters_faceoff;


/* USED BY events.py */

create table teams (
    team_id integer,
    name varchar(35),
    nickname varchar(35),
    primary key(team_id)
) engine = InnoDB;

create table games (
    season integer,
    game_id integer,
    date date,
    home_team_id integer references nhl.teams(team_id),
    away_team_id integer references nhl.teams(team_id),
    home_team_score integer,
    away_team_score integer,
    rl boolean,
    gcl boolean,
    gcll boolean,
    bs varchar(35),
    bsc varchar(35),
    gs integer,
    primary key(game_id)
) engine = InnoDB;

create table events (
    event_id integer,
    formal_event_id varchar(15),
    game_id integer references games(game_id),
    period integer,
    strength integer,
    type varchar(15),
    shot_type varchar(15),
    description varchar(255),
    player_id integer,
    team_id integer references teams(team_id),
    xcoord integer,
    ycoord integer,
    home_score integer,
    away_score integer,
    home_sog integer,
    away_sog integer,
    time numeric,
    video_url varchar(255),
    altvideo_url varchar(255),
    goalie_id integer,
    primary key (game_id, event_id)
) engine = InnoDB;

create table events_players (
    game_id integer,
    event_id integer,
    which varchar(15),
    player_id integer
) engine = InnoDB;

create table events_penaltybox (
    game_id integer,
    event_id integer,
    which varchar(15),
    player_id integer
) engine = InnoDB;

/* USED BY stats.py */

create table players (
    player_id integer,
    jersey integer,
    name varchar(100),
    team_id integer,
    pos varchar(3),
    dob date,
    birthcity varchar(100),
    state varchar(10),
    country varchar(10),
    height integer,
    weight integer,
    shoots char,
    primary key(player_id)
);

create table stats_skaters_summary (
    player_id integer,
    season integer,
    gp integer,
    g integer,
    a integer,
    p integer,
    plusminus integer,
    pim integer,
    pp integer,
    sh integer,
    gw integer,
    ot integer,
    s integer,
    s_pct numeric,
    toi_g numeric,
    sft_g numeric,
    fo_pct numeric,
    primary key (player_id, season)
);

create table stats_skaters_timeonice (
    player_id integer,
    season integer,
    gp integer,
    evenstrength numeric,
    evenstrength_g numeric,
    shorthanded numeric,
    shorthanded_g numeric,
    powerplay numeric,
    powerplay_g numeric,
    total numeric,
    total_g numeric,
    shifts integer,
    total_s numeric,
    shifts_g numeric,
    primary key (player_id, season)
);

create table stats_skaters_faceoff (
    player_id integer,
    season integer,
    gp integer,
    evenstrength_fow integer,
    evenstrength_fol integer,
    powerplay_fow integer,
    powerplay_fol integer,
    shorthanded_fow integer,
    shorthanded_fol integer,
    home_fow integer,
    home_fol integer,
    road_fow integer,
    road_fol integer,
    fow integer,
    fol integer,
    total integer,
    primary key (player_id, season)
);

sql 通过命令行导出数据库

通过命令行导出数据库

gistfile1.sql

 mysqldump -p -u username database_name > dbname.sql

sql Capitaliza palabras en SQL

Capitaliza palabras en SQL

capitalize.sql

CREATE FUNCTION CAP_FIRST (input VARCHAR(255))

RETURNS VARCHAR(255)

DETERMINISTIC

BEGIN
	DECLARE len INT;
	DECLARE i INT;

	SET len   = CHAR_LENGTH(input);
	SET input = LOWER(input);
	SET i = 0;

	WHILE (i < len) DO
		IF (MID(input,i,1) = ' ' OR i = 0) THEN
			IF (i < len) THEN
				SET input = CONCAT(
					LEFT(input,i),
					UPPER(MID(input,i + 1,1)),
					RIGHT(input,len - i - 1)
				);
			END IF;
		END IF;
		SET i = i + 1;
	END WHILE;

	RETURN input;
END;

sql Trasferire Dominio WP查询数据库

Trasferire Dominio WP查询数据库

move_wordpress.sql

UPDATE wp_options SET option_value = replace(option_value,  'http://www.vecchiosito.com', 'http://www.nuovosito.com') WHERE  option_name = 'home' OR option_name = 'siteurl';
UPDATE wp_posts SET guid = replace(guid,  'http://www.vecchiosito.com','http://www.nuovosito.com');
UPDATE wp_posts SET post_content = replace(post_content,  'http://www.vecchiosito.com', 'http://www.nuovosito.com');

sql 获取mysql表大小

获取mysql表大小

Processlist.sql

SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE User = 'flirchi' Order by Time

table_size.sql

SELECT table_name AS "Table", 
round(((data_length + index_length) / 1024 / 1024), 2) "Size in MB" 
FROM information_schema.TABLES 
WHERE table_schema = "$DB"
 AND table_name = "$tbl";

sql 在DUPLICATE KEY UPDATE

在DUPLICATE KEY UPDATE

gistfile1.sql

INSERT INTO clientes_peticiones (
        id_cliente,fecha,peticiones
    ) VALUES (
        1,'2011-11-02',1
    ) ON DUPLICATE KEY UPDATE peticiones = peticiones+1;