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

- Fucking Around & Business Discussion (https://gfy.com/forumdisplay.php?f=26)

- - Question for those who have database querying knowledge (https://gfy.com/showthread.php?t=1134389)

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

is there a user interface ...?

Quote:

Originally Posted by the Shemp (Post 19994890)

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

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.

Quote:

Originally Posted by Prasawet (Post 19994906)

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

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

http://www.sqlmaestro.com/

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.

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.

Quote:

Originally Posted by Prasawet (Post 19994924)

Yes, it is mysql

what do you mean except in diagrams

Quote:

Originally Posted by Prasawet (Post 19994924)

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

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

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

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....

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.