A good default database create statement is as follows:CREATE DATABASE my_new_db CHARACTER SET = 'utf8' COLLATE = 'utf8_general_ci';
You may need something different but this is my "starter for 10".
In general this is a simple thing to do but often you need to know the syntax for these things, so see the following for help:
To see how databases were created, so what collation they are using, then execute this:select * from information_schema.schemata;
This reports the following:
If you want to see how to recreate all of your databases then use this:SELECT CONCAT('CREATE DATABASE ', schema_name, ' CHARACTER SET = ''', default_character_set_name, ''' COLLATE = ''', default_collation_name, ''';') FROM information_schema.schemata WHERE schema_name NOT IN ('mysql', 'sys', 'performance_schema', 'information_schema');
The "system" databases are excluded as you will never be creating those yourself. They have different uses as follows:
There are a few useful commands I seem to use a lot from within the mysql command line tool:
show databases;
- show a list of databases the current user has access touse my_db;
- change the default database to my_dbshow tables;
- output a list of tables in the current databaseshow full tables;
- as above but identify table or viewdesc my_table;
- display the definition of my_tableshow create table my_table
- display the syntax of the "create table" command to create my_tableshow index from my_table
- output index information on my_tableshow procedure status;
- list all procedures over all databasesshow function status;
- list all the functionsshow procedure status where db = database();
- show procedures for current databaseshow create procedure my_procedure;
- show the definition of the procedureshow create procedure my_procedure\G
- show the definition of the procedure but in an easier to read format!show create function my_function;
- show the definition of the procedureselect * from information_schema.routines;
- handy queryGetting all of the constraints is a little more tricky but the following will do it:select * from information_schema.table_constraints where constraint_schema = 'my_db';
select * from information_schema.key_column_usage where constraint_schema = 'my_db';
select * from information_schema.referential_constraints where constraint_schema = 'my_db';
Both MySQL and MariaDB support the common data types and they have a lot of overlap but also some minor differences. So check which supports what see the following documentation:
MySQL allows you to specify where the new column appears in the table structure, so for example:ALTER TABLE test_table ADD COLUMN price DECIMAL(16,2) AFTER status;
However, you can replace "AFTER status" with "FIRST" if you want the new column to be the first one.
You can rename a table as follows:RENAME TABLE my_table TO new_table;
You can use this to move tables between databases by specifying different database names before the tables names, as long as the databases use the same filesystem:RENAME TABLE db1.my_table TO db2.new_table;
After creating your table you can also very easily change it, so let's say you want to alter a column name. First off have a look at the current schema with the describe command, which can be abbreviated this:DESC my_table;
Make a note of the current column type and then you can do thisALTER TABLE my_table CHANGE current_name new_name INTEGER;
This of course assumes it is an integer column but you do have to specify the column type. This of course means that you can change the colun type:ALTER TABLE my_table CHANGE current_name current_name VARCHAR(20);
However this is more clearly done as:ALTER TABLE my_table MODIFY current_name VARCHAR(20);
Note that with both CHANGE and MODIFY you can specify "FIRST" or "AFTER Col1" to move the column in the order.
There are three main index types as follows:
If you wish to add a column to the primary key, then I think you need to drop it first and then add it as follows:ALTER TABLE my_table DROP PRIMARY KEY;
ALTER TABLE my_table ADD PRIMARY KEY (Col1, Col2);
Hopefully I can prove myself wrong at some point.
You can use the mysqldump
command with "--no-data" to export the database schema. I believe the issue here is you need permissions on the Information Schema or something like that. If you don't have this but you can query the database then you can connect with the mysql
command, then use the following:show create table databasename.tablename;
this will show the SQL that would be needed to create the table.
Alternatively you can use the following and even redirect the output to a filemysql --execute="show create table ddddd.ttttt"
You may need to specify host, username and password, which is expained in the "Tools and Utilities" section of MySQL.