SQL
文章平均质量分 69
zlxadhkust
这个作者很懒,什么都没留下…
展开
-
关于本人将会搬家博客的声明
至对这个博客有点关注的人:本人写博客的初衷很简单,主要是管理个人的知识和信息,软件开发这个行业的知识有其独特的性质,博大精深,结构宏大而细微处又琐碎,往往要经过一番长期的求索才能领悟一些知识,比如原理,算法等等,所以作为从业人员,记录下自己解开的知识点非常重要。这些博客其实只是个人的学习笔记,目标读者也只是我自己而已,没想到还是有很多人来看,也有些被收入知识库。也没有想到有一天会写原创 2017-04-13 17:05:25 · 533 阅读 · 0 评论 -
Get Minutes for Match Event Back!!!
For the new pop-up window functionality, we need this field back.And how does the field look in real world?So this is the time when the backups come to be useful. I have backed up原创 2012-08-01 20:59:48 · 552 阅读 · 0 评论 -
Cosmos的新功能
第一步:data design首先,需要修改jos_bl_arrangement视图。所以备份这个视图的SQL,然后修改,在前面加上查询id:select `m`.`id` AS `id`,`m`.`m_id` AS `mday_id`,month(`m`.`m_date`) AS `mon`,...然后,重新执行这个SQL创建视图。去到文件:/comp原创 2012-08-08 21:22:31 · 692 阅读 · 0 评论 -
Add One Bool Field to Player Extra Field
這次的問題之所以有點麻煩,是因為extra field是一個在預料不到系統會做什麽擴展的情況下預留的擴展能力,而它必然有局限性。局限性的體現就是,jos_bl_extra_values表只能將這個值存放為字符類型數據。實際上,在處理這部份問題時,我採用的做法是將三個表聯合:jos_bl_players,jos_bl_extra_filds與jos_bl_extra_values。在jo原创 2012-07-21 13:30:21 · 559 阅读 · 0 评论 -
如何使用jQuery UI的sortable组件做一个更为友好的界面(上)
http://jqueryui.com/demos/sortable/Basic Understanding of How It WorksWell, after pretty while of playing around with the docs and demos, let's set up our own prototype program firstly. I原创 2012-07-11 12:00:33 · 2415 阅读 · 1 评论 -
修正每个球队在每个赛季的额外信息的缺陷
保留已经建立的team与season关联的数据,和team与matchday关联的数据这些信息目前是额外扣除的纪律分与备注。问题在于(上篇描述过),当赛季被重新保存时,关于球队与该赛季的联合数据会被清除,原因是保存的方法是先删除season_teams表中所以属于该赛季的记录,然后再重新插入新记录,为每个球队。现在将要采取的解决办法是:首先验证每个球原创 2012-07-14 13:05:01 · 461 阅读 · 0 评论 -
Add Linkage between Team and MatchDay, with remarks and bonus
问题描述:在“成绩龙虎榜”里面,下属于联赛盃的第一循环和第二循环的数据都是联赛盃的总成绩的数据,即是错误。创建存储所以,我们首先需要一个新表,来表示matchday与team之间的直接关系,就像season_teams一样:我将称之为matchday_teams。通过输出season_teams表,得到一个SQL的参考,修改部分参数:原创 2012-07-07 22:11:38 · 777 阅读 · 0 评论 -
Parse Date-time From String With PHP & MySQL
STR_TO_DATE:You need to tell MySQL how to parse the string, and you do that byfiguring out the pattern and pass it to STR_TO_DATE() as the second parameter.DATE_FORMAT:This functio原创 2011-10-24 00:17:14 · 991 阅读 · 3 评论 -
Add one field “上半场” to match
Add FieldFirst of all, add this field in phpMyAdmin:ALTER TABLE `jos_bl_match` ADD `first_half` VARCHAR( 10 ) NOT NULL DEFAULT '' AFTER `score2`Then, alert View jos_bl_原创 2012-05-20 13:48:52 · 557 阅读 · 0 评论 -
Tips On PHP & MySQL
Customize Field Sorting when using ORDER BY in MySQLhttp://www.imthi.com/blog/programming/mysql-order-by-field-custom-field-sorting.phphttp://www.webtechquery.com/index.php/2010/04/mysql-ord原创 2013-03-08 09:06:52 · 979 阅读 · 2 评论 -
SQL Interview Preparation (in the context of MS SQL Server)
1. Inner join on multiple columnsSuppose we have two tables in place as below:Joining them on F1 and F4 will give you:And join them on both F1=F4 and F2=F5, you will get:Re原创 2015-06-04 11:42:57 · 540 阅读 · 2 评论 -
Note on <Beginning Microsoft SQL Server 2012 Programming> - 04
Chapter 11: Writing Scripts and BatchesChapter 12: Stored Procedures原创 2015-08-16 15:27:04 · 1666 阅读 · 4 评论 -
Vincent's SQL Server Cookbook: Script the Collation
Collation is a fundamental setting option of a database, it determines how the letters are sorted alphabetically, therefore its value affects sorting, comparison of literal fields and indexes on those原创 2015-09-19 15:43:53 · 356 阅读 · 0 评论 -
Note on <Professional SQL Server 2012 Internals And Troubleshooting> - 01
Chapter 1: SQL Server ArchitectureSELECT COUNT(*)*8/1024 AS 'Cached Size (MB)' ,CASE database_id WHEN 32767 THEN 'ResourceDb' ELSE db_name(database_id) END原创 2015-09-05 23:21:22 · 467 阅读 · 0 评论 -
Note on <Beginning Microsoft SQL Server 2012 Programming> - 03
Chapter 9: SQL Server Storage and Indexs StructuresHow Data Is Accessed in SQL ServerUsing a table scanWhen a table scan is performed, SQL Server starts at the physical beginning of原创 2015-08-28 21:17:17 · 907 阅读 · 0 评论 -
Note on <Beginning Microsoft SQL Server 2012 Programming> - 01
CHAPTER 1 RDBMS BASICS: WHAT MAKES UP A SQL SERVER DATABASE?masterThis database holds a special set of tables (system tables) that keeps track of the system as a whole. All extended an原创 2015-06-14 23:04:46 · 730 阅读 · 1 评论 -
Note on <Beginning Microsoft SQL Server 2012 Programming> - 02
Chapter 5: Creating and Altering TablesObject Names in SQL ServerThere are actually four levels in the naming convention for any SQL Server table (and any other SQL Server object for that ma原创 2015-08-28 21:03:59 · 727 阅读 · 0 评论 -
Note on <Beginning Microsoft SQL Server 2012 Programming> - 05
Chapter 14: Transactions and LocksTransactionsDefinition of ACID:Atomicity: The transaction is all or nothing.Consistency: All constraints and other data integrity rules have been原创 2015-08-28 21:10:42 · 650 阅读 · 0 评论 -
SQL in SQL Server Trouble-shooting
#1: Cannot insert explicit value for identity column in table 'table_name' when IDENTITY_INSERT is set to OFF.SET IDENTITY_INSERT [dbo].[tbl1] ONINSERT INTO [dbo].[tbl1] ([Id], [原创 2015-06-04 11:19:43 · 836 阅读 · 11 评论 -
SQL Progeamming in SQLite of CRC-eDirectory
Narrowcasting Loader:SELECTsys_config.value,sys_config.name,narr_casting.messageFROMsys_config ,narr_castingWHERE sys_config.name LIKE 'narrcast_speed' AND narr_casting.nc_id = 1Pan原创 2011-10-29 18:32:48 · 813 阅读 · 0 评论 -
SQLite Shipped with AIR
REFS:http://souptonuts.sourceforge.net/readme_sqlite_tutorial.htmlhttp://www.sqlite.org/docs.htmlSQL statement performance(这是好东西)http://help.adobe.com/en_US/as3/mobile/WS948100原创 2011-10-21 16:25:51 · 406 阅读 · 0 评论 -
Change Log of Joomsport: [VIEW]晉級圖-淘汰盃 增加comment1, comment2到match表(synchronized with server)
這兩個字段用來存儲淘汰盃第一階段比賽信息中,兩球隊的備註信息:在phpMyAdmin中增加此兩個字段的SQL:class JTableMatch extends JTable{ var $id = null; var $m_id = null; var $team1_id = null; var $t原创 2011-12-10 23:49:32 · 661 阅读 · 0 评论 -
Stored Procedures in SQLite
REFs:http://www.sqliteconcepts.org/pl_proc.htmlhttp://chriswolf.heroku.com/articles/2011/01/26/adding-stored-procedures-to-sqlite/原创 2011-11-28 15:43:25 · 894 阅读 · 0 评论 -
【視圖】罰牌記錄:使用臨時表,UNION與JOIN將Match_Event表中的紅牌黃牌記錄分別統計出來
將要生成的最終結果:該表會根據用戶選擇某個組別,以及某個賽事的當下賽季來顯示不同內容,即是說,該查詢應該有如下輸入參數:組別的id(group id);賽季的id(season id);數據表的E-R圖:該查詢的主要對象為‘match_events’,但是對其記錄將會根據參數進行過濾:首先是球隊需是屬於所查詢的組別原创 2011-10-30 18:21:23 · 1628 阅读 · 0 评论 -
Navicat For SQLite
Just find an excellent GUI Admin tool for SQLite, Navucat for SQLite, which is a good alternative to SQLiteApyQuery Builder (only support SELECT)Select原创 2011-10-29 17:51:45 · 964 阅读 · 0 评论 -
Penalty Records - GROUP BY; HAVING; UNION;
Query for yellow card:with a given Group, so its id is given.with a given Event name, so its e_id is givenwith the current season for each tournament, so the 5 seasons' ids are given, then t原创 2011-10-27 22:58:27 · 495 阅读 · 1 评论 -
View in MySQL
In joomla!, the view should follow the same naming convention as table.CREATE VIEW jos_bl_arrangementAS SELECT MONTH( m.m_date ) AS mon,DATE_FORMAT( m.m_date , '%d/%m/%Y') AS date, DAYOFWEE原创 2011-10-24 23:16:06 · 453 阅读 · 0 评论 -
Accessing Local Database SQLite with AIR API
Probably I went in wrong order, it seems that I picked the most complex one to start this topic. "User Experience Considerations with SQLite Operations", the title implies that the author will emphasi原创 2011-10-06 01:07:10 · 670 阅读 · 0 评论 -
ORDER, SUM and Stored Procedure in MySQL
REFs:http://www.hmes.kh.edu.tw/~jona/redhat/mysqlphp/mysqlsyntax.htmhttp://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.htmlhttp://www.tizag.com/mysqlTutorial/mysqlsum.php原创 2011-10-25 21:01:55 · 453 阅读 · 0 评论 -
Dump data into MySQL from Excel with PHP
There are some factors may affect the result:CSV file encoding format;PHP file encoding format;character set for collation data table in MySQL and one line for specifying encoding format w原创 2011-10-22 10:55:33 · 498 阅读 · 0 评论 -
Change Log of Joomsport: Create view 'arrangement' with parameter 'month'
TargetDatabase ChangeCreate view 'jos_bl_arrangement'Type in the following SQL in phpMyAdmin panel for creating view:SELECT m_id AS mday_id,month(m_date) AS mon,da原创 2011-10-27 16:40:17 · 670 阅读 · 4 评论 -
Panelty View Design: Sorting Arrays in PHP
The following SQL query can get the latest seasons' id for all tournament:SELECT MAX(s_id) AS seasonid FROM jos_bl_seasons GROUP BY t_idThe 'latest' here can be regarded as the原创 2011-10-30 22:12:35 · 1082 阅读 · 0 评论 -
Change Log of Joomsport:[VIEW]晉級圖: 增加ranking_alias字段到team,重新使用match的match_descr字段(synchronized)
這個變量是用來記錄每一個球隊在聯賽盃出線后,進入到以下階段的賽事時將會被提示的成績排名,比如:甲組第一名。數據庫的修改:增加成員變量'$ranking_alias'到'JTableTeams'類:/administrator/components/com_joomsport/admin.joomsport.class.ph原创 2011-12-10 00:17:59 · 488 阅读 · 0 评论 -
Change Log of Joomsport: [VIEW]晉級圖-淘汰盃:增加字段number_label1,number_label2到match表(synchronized with ser)
這兩個字段分別用來紀律一場比賽中主場與客場的編號:class JTableMatch extends JTable{ var $id = null; var $m_id = null; var $team1_id = null; var $team2_id = null; var $score1原创 2011-12-10 11:59:19 · 561 阅读 · 1 评论 -
[VIEW]赛事晋级图-[LAYOUT]主席盃
Firstly, we...And next up is to list the match data for each group within this season:Get the current season:SELECT MAX(s_id) FROM jos_bl_seasons WHERE t_id = 1 AND published原创 2011-12-07 22:13:58 · 845 阅读 · 1 评论 -
Change Log of Joomsport: Adding Order to Teams within one Group
Target: To let user can order the teams of one certain group(like 超組) of a season.Database ChangeTables: jos_bl_grteamsPHP Change/administrator/components/com原创 2011-10-23 21:28:34 · 621 阅读 · 0 评论 -
Change Log of Joomsport: Add two fields to season table
Target:There are two values: for one certain season, the discipline point for team and player.Database Change:run the SQL in phpMyAdmin:ALTER TABLE jos_bl_seasons ADD s_t原创 2011-11-12 12:12:25 · 614 阅读 · 0 评论 -
Change Log for Joomsport: Get Extra Field back for Player & Team
The Reasons:actually it is for the extra unregulated field:playerteamThe relations in Database:we can make use of the two tables: 'extra_filds', and 'extr原创 2011-11-11 21:39:16 · 981 阅读 · 0 评论 -
Change Log for Joomsport: [VIEW] - Shooter Ranking (金靴射手榜)
查詢得出入球記錄:SELECT SUM(me.ecount) AS goal, me.player_id, t.t_name, pl.first_name, pl.last_name FROM jos_bl_match_events AS me, jos_bl_teams AS t, jos_bl_players AS pl, jos_bl_grteams A原创 2011-11-06 23:23:43 · 1837 阅读 · 1 评论 -
Change Log for Joomsport: [VIEW] 成績龍虎榜(Ranking List) SQL Programming
For each Group, perform:{for each team belongs to this group, perform:{QUERY1: get the accumulated 'Played-Matches', 'Goals', 'Misses' and 'Diff'SELECT(SELECT COUNT(id) FROM jos_bl_m原创 2011-11-05 11:03:02 · 936 阅读 · 3 评论