|
adamarama posted:I apologise if this isn't the right place to ask, I was wondering if anyone knows of any good resources (books or online) for learning SQL? There's so many books and sites available, I'm not sure which would be best for a beginner. I have never done programming before, I'm just learning as I hear it's quite useful in business analysis. Should I focus on mastering SELECT, or is it useful knowing how to construct databases? Well i would start with select that's the most basic function your going to always use. You can use something like a visual gui tool to create ones and certain ones even show you the sql they are going to run to do what you did with the gui so you can learn those kinds of things.
|
# ? Nov 23, 2009 20:10 |
|
|
# ? May 30, 2024 08:41 |
|
adamarama posted:I have never done programming before, I'm just learning as I hear it's quite useful in business analysis. Should I focus on mastering SELECT, or is it useful knowing how to construct databases? Obviously it would be great to just know everything, but if you're a beginner with no technical experience, constructing/managing/designing a database should be pretty low on your list. There's a wealth of knowledge to acquire as an analyst, based solely on the SELECT statement (basic selects, aggregations, joins, and analytics). You can get pretty far on SELECT material and not know anything about how databases should be designed/managed. Technical note: If anything, normalized tables make data access more cumbersome. All it does is help maintain integrity free of errors.
|
# ? Nov 23, 2009 20:17 |
|
Eh, I dunno, without some relational-data basics, SQL doesn't always make sense. I do think that starting out requires some basic big-picture stuff before you dive into joining tables.adamarama posted:I have never done programming before, I'm just learning as I hear it's quite useful in business analysis. Yes, quite useful. I'm a business analyst, and being able to write my own SQL has greatly expanded my toolset, and made me much more valuable to my company.
|
# ? Nov 23, 2009 21:14 |
|
There's a world of difference between "this is a two dimensional structure with optional values" and "this table isn't indexed properly and doesn't obey fourth normal form". I'm saying, to be an analyst, you probably don't need the latter. What consumer would care about relational integrity? Even if you had two badly designed tables with duplicate data, it's not really your place to do anything about it (unless you are the data architect).
|
# ? Nov 23, 2009 21:44 |
|
Jethro posted:If you're using SQL Server 2005 or newer http://msdn.microsoft.com/en-us/library/ms190936%28SQL.90%29.aspx No such luck, 2000. But I've found XMLSpy does basically exactly what I want.
|
# ? Nov 24, 2009 00:56 |
Hey, I have a really stupid question. I'm updating a database for someone, and I have very limited experience. I really can't gently caress this up, or bad things will happen. I have a table called "property" and an enum field called "parking". I want to change all the values of this field to "Yes, fee". so, UPDATE property SET parking=Yes, fee; or something like that??
|
|
# ? Nov 30, 2009 19:01 |
|
growing posted:Hey, I have a really stupid question. I'm updating a database for someone, and I have very limited experience. I really can't gently caress this up, or bad things will happen. well it would be UPDATE property SET parking='Yes, fee'; but since its an enum field it does have the option of being a 'Yes, fee' in the descriptor right otherwise it will error saying you can't.
|
# ? Nov 30, 2009 19:03 |
Perfect, thanks. Whoops, I forgot the ' '. Yes, it had the option of "Yes, fee." Someone set this database up a long time ago, and it has tons and tons of data...being an SQL newbie, I didn't want to mess anything up. Thanks for the double-check. Apologies for the lvl1 question. every fucked around with this message at 19:28 on Nov 30, 2009 |
|
# ? Nov 30, 2009 19:09 |
|
growing posted:Hey, I have a really stupid question. I'm updating a database for someone, and I have very limited experience. I really can't gently caress this up, or bad things will happen. But surely backups of the data exist, right?
|
# ? Nov 30, 2009 21:06 |
|
Hammerite posted:But surely backups of the data exist, right? Actually, that depends on how much I've pissed off the DBA...
|
# ? Nov 30, 2009 21:08 |
|
Is it possible to gain programmatic access to the actual cost of a query, estimated or actual?
|
# ? Dec 2, 2009 20:28 |
|
Triple Tech posted:Is it possible to gain programmatic access to the actual cost of a query, estimated or actual? Not sure what database you're asking about, but Oracle exposes estimated costs of cached cursors in the V$SQL_PLAN object.
|
# ? Dec 2, 2009 20:57 |
Using MySQL, I'm creating a temp table:code:
Unfortunately, I don't have enough memory on this machine to have temp tables in memory that are that big for X number of users. Are there other ways I can speed this up? 2 minutes to load 50,000 rows seems like way too long. edit: specifying ENGINE = MyISAM, overriding the InnoDB default, yields a 10 second load time. Hurrah! fletcher fucked around with this message at 00:04 on Dec 3, 2009 |
|
# ? Dec 2, 2009 23:04 |
|
Take off the index. If you want to speed up writes, drop all indices. Then if you need the index for reporting purposes, recreate it.
|
# ? Dec 2, 2009 23:26 |
|
i can't even imagine why you would put a 1300 character field into the primary key that doesn't make any sense.
|
# ? Dec 3, 2009 00:00 |
Triple Tech posted:Take off the index. If you want to speed up writes, drop all indices. Then if you need the index for reporting purposes, recreate it. Interesting...so it would be faster to not have the indicies in the CREATE, and to just create them one I'm done inserting? Sprawl posted:i can't even imagine why you would put a 1300 character field into the primary key that doesn't make any sense. Whoops, that should really be SomeOtherField, not the long one.
|
|
# ? Dec 3, 2009 00:02 |
|
fletcher posted:Interesting...so it would be faster to not have the indicies in the CREATE, and to just create them one I'm done inserting? Yes inserts go much faster without keys and you can add them back much faster after you've imported all the rows.
|
# ? Dec 3, 2009 00:06 |
|
fletcher posted:Interesting...so it would be faster to not have the indicies in the CREATE, and to just create them one I'm done inserting? One of two things is happening, but they're both based on the same principle, just one worse than the other. If you're inserting a stream of rows into a table (as singular inserts), each insert has to go through the table's indices, make a spot for itself, and then plop it in place. Multiply this by how many times you do it, seek put seek put, and accounting for the fact that the table gets longer and longer the more rows you have. The other way is that you're doing a bulk insert. The database will sort your temporary data set, set down multiple markers in the real table, make room for your new rows, and then slide them in. Slightly faster, but incurs the cost of sorting and more temporary/logging space. If you relieve the table of its indices, the database doesn't have to do any of this work. It can just very quickly append your rows, in any order, probably its nature convenient order, to the end of the table. Then after the rows are committed, you can go back and optimize for speed, asking the table to reshuffle all the data, but just sorting that entire set once. A rule of thumb is... Anything you do to improve write/read performance will cause read/write performance to suffer. Adding indices to improve read performance will cause writes to be slower, because they will continually try to maintain these indices. This isn't to say that you should always drop and recreate your indices (this also has a time cost, obviously, during re-creation). It's just that in some use cases, people need to optimize for very large, bulk inserts and dropping the indices and recreating them is part of this workflow. For single time inserts, it is negligable.
|
# ? Dec 3, 2009 06:15 |
|
I need to be able to order the results of a mysql query in an arbitrary way the query is code:
PS Triple Tech your av/text is AWESOME.
|
# ? Dec 3, 2009 17:21 |
|
Does that list of IDs have any sort significance on some dimension inherent to them? Or is it truly, truly arbitrary? Edit: The point is if there is, do a join and sort on that field. PS, more great Gleevatag combos found in the Glee thread... Triple Tech fucked around with this message at 18:26 on Dec 3, 2009 |
# ? Dec 3, 2009 17:27 |
|
Lamb-Blaster 4000 posted:I need to be able to order the results of a mysql query in an arbitrary way and I need the results ordered the same way the list of ids is. Probably easiest way is to order by a decode statement code:
In that case maybe case would work code:
Sneaking Mission fucked around with this message at 17:51 on Dec 3, 2009 |
# ? Dec 3, 2009 17:41 |
|
Lamb-Blaster 4000 posted:I need to be able to order the results of a mysql query in an arbitrary way Well there are some issues when doing this. select firstName,lastName,image from authors where id in (95,194,97,52,62,20,17,1) order by id=1,id=17,id=20,id=62,id=52,id=97,id=194,id=95 no idea why its work in reverse like this but i can show you this. select id,description from area where id in (1,22,5,10,20,27,32) order by id=32,id=27,id=20,id=10,id=5,id=22,id=1 code:
|
# ? Dec 3, 2009 18:24 |
|
Sprawl posted:no idea why its work in reverse like this but i can show you this. You can see why it's in reverse by going through one by one. First comparison that happens is: is id=32? For MARITIMES only it is, so id=32 evaluates as 1 for MARITIMES and 0 for all the others. So since default order is ascending, MARITIMES goes to the end of the list. etc. You have a couple of ways to get them in the other order; use <> instead of =, or add DESC after each comparison.
|
# ? Dec 3, 2009 18:58 |
|
the idea is there's a table of authors and at any point in time I should be able to change which authors are pulled together and in what order just by swapping the ids. Sprawl, your solution worked, thanks! Edit: also thanks hammerite for the reverse solution. Lamb-Blaster 4000 fucked around with this message at 19:02 on Dec 3, 2009 |
# ? Dec 3, 2009 18:59 |
|
Hammerite posted:You can see why it's in reverse by going through one by one. First comparison that happens is: is id=32? For MARITIMES only it is, so id=32 evaluates as 1 for MARITIMES and 0 for all the others. So since default order is ascending, MARITIMES goes to the end of the list. etc. That didn't really occur to me that does make sense.
|
# ? Dec 3, 2009 19:29 |
|
var1ety posted:Not sure what database you're asking about, but Oracle exposes estimated costs of cached cursors in the V$SQL_PLAN object. I would recommend against actually using this cost value for anything. If I had a nickel for every cost=1 query that took 12 hours to run I could retire right now.
|
# ? Dec 4, 2009 16:58 |
|
Well it's just sort of an abstract/uncommon question... Like if a business were to charge users for the queries they write, what metric would you use to charge them? So I figured query cost.
|
# ? Dec 4, 2009 17:11 |
|
Triple Tech posted:Well it's just sort of an abstract/uncommon question... Like if a business were to charge users for the queries they write, what metric would you use to charge them? So I figured query cost. Well I don't know about other database systems, but when you ask MySQL to EXPLAIN SELECT ... it gives you a bunch of information that you can use to infer the approximate "cost" of the query. Like it will tell you how many rows it thinks it needs to examine in each joined table, whether it could use any indices, whether it has to use a filesort to get things in the correct order, etc. Details here. Maybe you can get your DBMS's query optimiser to record its conclusions in this way in a log file, for queries that it runs. Doesn't any such endeavour come with a certain amount of overhead anyway, though, if you're doing that for every query? Also, don't most DBMSs have an option to log "slow" queries? You could just examine this log and have the user responsible accrue a cost. When you say "the queries they write", also, surely the cost of a given query scales with the size of the table. So it's more the queries the users execute than the ones they write.
|
# ? Dec 4, 2009 17:32 |
|
I would be inclined to use runtime, simply because it's objective and it automatically scales with system load giving you easy Peak Load Pricing. If you didn't want price to scale that way, you could, assuming again Oracle, run all queries with tracing enabled and charge based on "db block gets", "consistent gets", or "physical reads", possibly with a surcharge for additional sort operations.
|
# ? Dec 4, 2009 23:28 |
|
Having trouble with what is probably a pretty simple problem related to requesting and using a URL from mysql. Basically I have some RSS urls in a mysql database I want to use in some code (SimplePie). I am querying the database to get the urls, and that works fine, but when I try to use the urls it seems to be omitting the http: portion and tries to look locally for the rss feed. The error I'm getting is: Warning: file_get_contents(//www.cbssports.com/nfl/teams/syndicate/ARI',) [function.file-get-contents]: failed to open stream: No such file or directory In the database the url is formatted as 'http://www.cbssports.com/nfl/teams/syndicate/ARI' The code I'm using to use the url is: $query = "SELECT rssfeeds.URL FROM `rssfeeds` WHERE rssfeeds.TeamID='arizonacardinals'"; $result = mysql_query($query); for ($j=0; $row=mysql_fetch_array($result);$j++) $feeds = new SimplePie (array ("{$row['URL']}")); I'm sure there's a simple solution to get it to use the full url, but new to php and mysql. Also the code works fine when I put that url in the $feeds array as 'http://www.cbssports.com/nfl/teams/syndicate/ARI' rather than calling it from the database.
|
# ? Dec 5, 2009 08:12 |
|
What character set is the field that holds it?
|
# ? Dec 5, 2009 09:03 |
|
Sprawl posted:What character set is the field that holds it? Currently have it as VARCHAR
|
# ? Dec 5, 2009 18:19 |
|
atb posted:Currently have it as VARCHAR That's the column type. Columns that have a string data type (CHAR, VARCHAR or TEXT) also have a character set and collation. If you issue SHOW CREATE TABLE `rssfeeds` then you will get a CREATE TABLE statement for the table; at the end of the statement there should be a clause that says something like CHARACTER SET utf8 or CHARACTER SET latin1. Tell us what this clause says, unless there is a similar clause after the column listing for `URL`, in which case tell us what that clause says instead.
|
# ? Dec 5, 2009 19:43 |
|
CREATE TABLE `rssfeeds` ( `TeamID` varchar(150) NOT NULL, `URL` varchar(255) character set dec8 collate dec8_bin NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 edit: Tried changing the URL collate to ut8_general_ci and latin1_swedish_ci, neither which helped Also, if I try echo $row["URL"]; I get the url printed as 'http://www.cbssports.com/nfl/teams/syndicate/ARI' which is what matches the sql data. atb fucked around with this message at 22:29 on Dec 5, 2009 |
# ? Dec 5, 2009 20:39 |
|
Figured out the problem, changed the collation to latin1_swedish_ci and the field type to Text. Have the URL in sql as http://www.cbssports.com/nfl/teams/syndicate/ARI without any quotations. Now the problem is in order to process more than one feed I need to have a comma after each feed. If I put a comma behind the url in sql, it doesn't process the feed. Is there a way I can add a comma into the code pulling the feeds to that it adds it onto the end of the url being processed? The code I have is: $query = "SELECT rssfeeds.URL FROM `rssfeeds` WHERE rssfeeds.TeamID='arizonacardinals'"; $result = mysql_query($query); for ($j=0; $row=mysql_fetch_array($result);$j++) $feeds = new SimplePie (array ($row['URL']));
|
# ? Dec 5, 2009 23:06 |
|
atb posted:Figured out the problem, changed the collation to latin1_swedish_ci and the field type to Text. Have the URL in sql as http://www.cbssports.com/nfl/teams/syndicate/ARI without any quotations. I think what your saying is you want to add a "," to the end of the url after the sql extraction during the php? I think right?
|
# ? Dec 6, 2009 02:03 |
|
Yeah, right now if I extract the output is http://www.url.com http://www.url2.com http://www.url3.com So I need to somehow get a "," after each URL.
|
# ? Dec 6, 2009 02:46 |
|
atb posted:
Can you just do it in your select statement like: "SELECT rssfeeds.URL + ',' FROM `rssfeeds` WHERE rssfeeds.TeamID='arizonacardinals'"; (I see you said that you can't add the , in sql but wasn't sure if you just meant adding it directly to the data) If that is too early in the routine, it looks like you would add it here: $feeds = new SimplePie (array ($row['URL']+',')); But I don't know php so I'm just kind of guessing what that code is doing. Kekekela fucked around with this message at 06:21 on Dec 6, 2009 |
# ? Dec 6, 2009 06:13 |
|
atb posted:Yeah, right now if I extract the output is http://www.url.com http://www.url2.com http://www.url3.com To me this says that you ought at some point to have an array of URLs that you want to convert into a comma-separated string. So the solution should just be code:
Kekekela posted:If that is too early in the routine, it looks like you would add it here:
|
# ? Dec 6, 2009 16:12 |
|
|
# ? May 30, 2024 08:41 |
|
could try GROUP_CONCAT but that wouldn't put a comma after the last one
|
# ? Dec 6, 2009 22:18 |