Question for those who have database querying knowledge

[Badmaash]

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]

is there a user interface ...?

[Badmaash]

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]

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]

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]

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

http://www.sqlmaestro.com/

[myleene]

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]

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]

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]

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]

You mean this?

[FONT="Courier New"]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)[/FONT]

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.

[FONT="Courier New"][B]mysql> ALTER TABLE table_name ENGINE = INNODB;[/B][/FONT]

Just make it work :P

Nice references website

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

[woj]

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]

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]

what ^^ said.