These are part of the design of the database, I wouldn't imagine any api-level tool to have "support" for them except as a direct SQL call. I would absolutely do what you're doing and use a GUI to design the database, and then access it through the API.
Mysql workbench is pretty powerful, but can be confusing. I've always used a tool called HeidiSQL for working with mysql/mariadb databases. Its nicer in my opinion for learning with.
Some other thoughts:
Mysql has a TCP server https://decibel.ni.com/content/docs/DOC-10453 which is faster for small interactions
the mysql command line is a pain to use but could be better for bulk imports (eg from a giant mass of old CSV files). As shown in the link, Heidi can help you.
Postgresdb is becoming more and more popular (among other things, mysql is now owned by oracle and spent a while sort of languishing -- for my personal load of several TB of indexed data, postgres performed significantly better out of box than a somewhat optimized mysql). If you decided to go this route there are two libraries to consider (in addition to the db connectivity toolkit).
What you described, having a bunch of old measurement data in csv files and wanting to catalog them in a database-esque way for performance and ease of use is literally the sales pitch of Diadem. Like, almost verbatim.
It may be 1 to N files depending on the configuration. Indices can sometimes be stored in separate files, and if you have a ton of data you would use partitioning to split the data up into different files based on parameters (eg where year(timestamp)=2018 you use partition 1, where year=2017 use partition 2, etc.). You don't reference the file directly. You usually use a connection string formatted like this: https://www.connectionstrings.com/mysql/
You cannot, you must have a server machine which runs the mysql service. To the best of my knowledge, the only database which you can put on a share drive and forget about is sqlite, but they recommend against it. I had never used the MDB format before but it looks like that is similarly accessible as a file.
As with 2, you generally don't edit the files manually. You access the database through the server which exposes everything through SQL.
They do, but I think its in the TB range. If you reach a single database file that is over a TB, you should learn about and use partitioning which breaks it down into smaller files.
Not sure, but I believe the truly giant websites like google have their own database system they use. More generally, they divide the workload across a large number of machines. As an example: https://en.wikipedia.org/wiki/Shard_(database_architecture)
You will need to install the database server somewhere. Assuming you've set up some server thats going to host your data, then you just need the client software. If you use the TCP-based connector mentioned above, that client software is just labview. However, that connector has no security implementation and gets bogged down with large data sets. If you want to use the database toolkit, you'll need the ODBC connector and perhaps to configure a data source as shown here, although you may be able to create a connection string at runtime.