Question for those who have database querying knowledge - GoFuckYourself.com

Badmaash · 02-25-2014, 01:31 AM

Hi

Let say you know how to apply queries to a database and you land yourself a job.

Now your job is to query the DB, but without knowing what it looks like and all the relationships between the tables how are you suppose to query it?

Is the company suppose to give you documentation showing in graphical form what the tables look like and how they are linked?

Or can you run a query that gives you all the information?

Thanks

B

the Shemp · 02-25-2014, 01:42 AM

is there a user interface ...?

Badmaash · 02-25-2014, 01:46 AM

Quote:

Originally Posted by the Shemp

is there a user interface ...?

No idea.... have not taken up the job yet just doing research into the potential job

1. Lets say you have one

2. Lets say you dont have one

Thanks

Prasawet · 02-25-2014, 02:05 AM

Quote:

Or can you run a query that gives you all the information?

Such a (mysql) query just don't exist, realtionships beetween tables could be anything but usually is beetwen id's.
Look for the views, that could be kind of graph about realtionships.
If there is no documentation they are in a deep trouble.
In that case you will have to dive into website code and find what is calling what.

Badmaash · 02-25-2014, 02:07 AM

Quote:

Originally Posted by Prasawet

Such a (mysql) query just don't exist, realtionships could be anything but usually is beetwen id's. If there is no documentation they are in a deep trouble.
In that case you will have to dive into website code and find what is calling what.

So are you saying that if a DBA took up a job at a new company, they should provide him/her without the schema on document form?

Thanks

B

EddyTheDog · 02-25-2014, 02:11 AM

Software like the stuff here would help a lot...

http://www.sqlmaestro.com/

myleene · 02-25-2014, 02:14 AM

This?

http://onewebsql.com/blog/list-all-tables

SQL Server or SqLite aren't included in these examples, but there's usually a way to do it whatever the database is. There's usually also a way to get database relationships.

As mentioned, a GUI is usually preferred as it may also be able to show them well visually.

(*) These examples haven't been tested and are only to show it's possible.

Prasawet · 02-25-2014, 02:37 AM

Again, if it is MySql, that doesn't support foreign keys, you don't link the tables anywhere except your diagrams and queries. If there is saved queries (known as views) that can help.

Without that (diagrams or at least saved queries) that could be nightmare, in that case only the code (php or what is used) can show you possible relationships.

Badmaash · 02-25-2014, 03:32 AM

Quote:

Originally Posted by Prasawet

Again, if it is MySql, that doesn't support foreign keys, you don't link the tables anywhere except your diagrams and queries. If there is saved queries (known as views) that can help.

Without that (diagrams or at least saved queries) that could be nightmare, in that case only the code (php or what is used) can show you possible relationships.

Yes, it is mysql

what do you mean except in diagrams

Badmaash · 02-25-2014, 03:41 AM

Quote:

Originally Posted by Prasawet

Again, if it is MySql, that doesn't support foreign keys, you don't link the tables anywhere except your diagrams and queries. If there is saved queries (known as views) that can help.

Without that (diagrams or at least saved queries) that could be nightmare, in that case only the code (php or what is used) can show you possible relationships.

Apparently if you are using Innob tables then it does support foreign keys:

http://dev.mysql.com/doc/refman/5.1/...eign-keys.html

Barry-xlovecam · 02-25-2014, 07:36 AM

Code:

barry:~$ mysql -u****** -p*******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1016
Server version: 5.***************************

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| models               |
| mysql                |
| test                    |
+--------------------+
11 rows in set (0.00 sec)

mysql> use models;

mysql> show tables;
+------------------+
| Tables_in_models |
+------------------+
| modelData      |
+------------------+
1 row in set (0.00 sec)



mysql> SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES where TABLE_SCHEMA = 'models';
+-------------+--------+
| TABLE_NAME  | ENGINE |
+-------------+--------+
| modelData | INNODB |
+-------------+--------+
1 row in set (0.06 sec)

Quote:

http://dev.mysql.com/doc/refman/5.1/en/alter-table.html

ALTER TABLE changes the structure of a table. For example, you can add or delete columns, create or destroy indexes, change the type of existing columns, or rename columns or the table itself. You can also change characteristics such as the storage engine used for the table or the table comment.

Code:

mysql> ALTER TABLE table_name ENGINE = INNODB;

Just make it work :P

Nice references website

http://mysqlresources.com/documentation/db-table-schema

woj · 02-25-2014, 07:46 AM

for anyone who has been around a block a few times, 99% of the time it's pretty obvious what the relationships are ... and if that fails, you can always look at the queries....

Why · 02-25-2014, 09:34 AM

describe database;
describe table;

will do most of what you need, however there are a lot of places to "hide" things in mysql(stored procedures, as an example), which is why documentation should be kept.

freecartoonporn · 02-25-2014, 10:20 AM

what ^^ said.

02-25-2014, 01:31 AM	#1
Badmaash Confirmed User Join Date: Jan 2007 Posts: 2,695	Question for those who have database querying knowledge Hi Let say you know how to apply queries to a database and you land yourself a job. Now your job is to query the DB, but without knowing what it looks like and all the relationships between the tables how are you suppose to query it? Is the company suppose to give you documentation showing in graphical form what the tables look like and how they are linked? Or can you run a query that gives you all the information? Thanks B __________________ best cbd oil uk - Hit me up on ICQ 400607632

02-25-2014, 01:42 AM	#2
the Shemp congrats to the winners Industry Role: Join Date: Nov 2001 Location: Echo Beach Posts: 10,891	is there a user interface ...? __________________ i use Vacares...so should you Submit your picture galleries to my site...Outlaw TGP

02-25-2014, 02:14 AM	#7
myleene Confirmed User Industry Role: Join Date: Oct 2013 Location: Canada Posts: 870	This? http://onewebsql.com/blog/list-all-tables SQL Server or SqLite aren't included in these examples, but there's usually a way to do it whatever the database is. There's usually also a way to get database relationships. As mentioned, a GUI is usually preferred as it may also be able to show them well visually. () These examples haven't been tested and are only to show it's possible. Last edited by myleene; 02-25-2014 at 02:15 AM..*

02-25-2014, 02:37 AM	#8
Prasawet Registered User Join Date: Nov 2009 Posts: 62	Again, if it is MySql, that doesn't support foreign keys, you don't link the tables anywhere except your diagrams and queries. If there is saved queries (known as views) that can help. Without that (diagrams or at least saved queries) that could be nightmare, in that case only the code (php or what is used) can show you possible relationships. __________________ Bitter Strawberry-Mobile Traffic Monetization MOBIDEA

02-25-2014, 07:46 AM	#12
woj <&(©¿©)&> Industry Role: Join Date: Jul 2002 Location: Chicago Posts: 47,882	for anyone who has been around a block a few times, 99% of the time it's pretty obvious what the relationships are ... and if that fails, you can always look at the queries.... __________________ Custom Software Development, email: woj#at#wojfun#.#com to discuss details or skype: wojl2000 or gchat: wojfun or telegram: wojl2000 Affiliate program tools: Hosted Galleries Manager Banner Manager Video Manager Wordpress Affiliate Plugin Pic/Movie of the Day Fansign Generator Zip Manager

02-25-2014, 02:11 AM	#6
EddyTheDog Just Doing My Own Thing Industry Role: Join Date: Jan 2011 Location: London, Spain, New Zealand, GFY - Not Croydon... Posts: 25,040	Software like the stuff here would help a lot... http://www.sqlmaestro.com/

02-25-2014, 09:34 AM	#13
Why MFBA Industry Role: Join Date: Mar 2003 Location: PNW Posts: 7,230	describe database; describe table; will do most of what you need, however there are a lot of places to "hide" things in mysql(stored procedures, as an example), which is why documentation should be kept.

02-25-2014, 10:20 AM	#14
freecartoonporn Confirmed User Industry Role: Join Date: Jan 2012 Location: NC Posts: 7,683	what ^^ said. __________________ SSD Cloud Server, VPS Server, Simple Cloud Hosting \| DigitalOcean