The database I use at work is Microsoft SQL Server. It uses a program called “Enterprise Manager” to manage your databases – building tables and fields and indexes; backing up and compressing the database, etc.
It’s built to be almost as “friendly” as Microsoft Access, since apparently a lot of people who use SQL Server have migrated from Access. To edit a table, you right-click on it, and select Design Table. To see data, Open Table/Return All Rows. When you double-click a table, the default is the nearly useless View Properties, where in Access the default is the equivalent of Return All Rows. So if you go back and forth from Access to Enterprise Manager, just a little annoyance.
Return All Rows is pretty cool; you can even edit data there by hand, and you can go to SQL view or Diagram View to customise the query. You can perform action queries, such as DELETE FROM merchants WHERE merchant_status = ’99’. But there’s something wrong with the engine that EM uses to perform these queries – it times out on complex actions such as multiple table joins.
So there’s another tool you can use, from one of the inscrutable “Tools” menu of EM: SQL Query Analyzer. QA lets you do very complex queries, save them, and has not only a long timeout but a timer in the window showing how long the query has taken. And I suppose you could even analyze your queries, but I’ve never managed to need that.
You can’t hand-edit data in QA, though you can of course mass-edit. “TRUNCATE TABLE users”. So you still go back to Return All Rows for that.
And neither QA or Return All Rows has the nifty feature Access has, where you can right-click to filter data – “Filter By Selection” or “Filter Excluding Selection”. This is useful enough that it’s often a good idea to ODBC link an Access database to your SQL server tables, to do ad-hoc queries and instant reports. So there’s yet another query tool for your SQL server data.
Now, technically, you really shouldn’t be allowed to hand-edit that data at all. Going in and changing, say, merchant_status, with no log of who did it (“audit trail”) and no control to prevent people from doing it, is what got Diebold in trouble for their voting system. There are ways to limit this, but in most cases the DBA (Database Administrator) can override that and edit any data he pleases, including deleting automatic audit trails, if he knows the database well enough.
From my vague reccollection, Oracle uses similar tools for database administration and management. Lance can probably correct me on this. But when I used them they weren’t exactly user friendly.
And yet I find this is one of my annoyances with MySQL. There’s a nifty open-source tool called phpMyAdmin (I can never remember the name) allows many of the same capabilities as EM, but is entirely php web-based. It doesn’t suck, but still has some challenges of its own.
While I’m at it, one of my annoyances with SQL, is that the syntax of the INSERT statement is significantly different from that of the UPDATE statement.
INSERT INTO users (username, user_status) VALUES ('fred', 1)
versus
UPDATE users SET username = 'fred', user_status='1' WHERE user_id = '123'
Couldn’t they have just allowed the syntax
INSERT INTO users username = 'fred', user_status='1'
and/or
UPDATE users (username, user_status) VALUES ('fred', 1)
so that I could copy and paste my code for update and insert? For that matter, how about
INSERT_OR_UPDATE users SET username = 'fred', user_status='1' WHERE user_id = '123'
and/or
INSERT_OR_UPDATE users (user_id, username, user_status) VALUES ('123', 'fred', 1)