MySQL – Execute SQL script
Many times we need to run a SQL script and we do not have graphical tools such as: phpMyAdmin or Adminer so we need to use the command line and use the mysql shell and follow one of the following methods:
Many times we need to run a SQL script and we do not have graphical tools such as: phpMyAdmin or Adminer so we need to use the command line and use the mysql shell and follow one of the following methods:
MySQL offers several types of charset (CHARACTER_SET), thereby satisfying the most varied projects.
> SHOW CHARACTER SET; +----------+-----------------------------+--------------------- | Charset | Description | Default collation +----------+-----------------------------+-------------------- | big5 | Big5 Traditional Chinese | big5_chinese_ci | dec8 | DEC West European | dec8_swedish_ci | cp850 | DOS West European | cp850_general_ci | hp8 | HP West European | hp8_english_ci | koi8r | KOI8-R Relcom Russian | koi8r_general_ci | latin1 | cp1252 West European | latin1_swedish_ci | latin2 | ISO 8859-2 Central European | latin2_general_ci | swe7 | 7bit Swedish | swe7_swedish_ci | ascii | US ASCII | ascii_general_ci ....
> SELECT COLLATION_NAME FROM INFORMATION_SCHEMA.COLLATIONS WHERE CHARACTER_SET_NAME = 'utf8'; +--------------------+ | COLLATION_NAME | +--------------------+ | utf8_general_ci | | utf8_bin | | utf8_unicode_ci | | utf8_icelandic_ci | | utf8_latvian_ci | | utf8_romanian_ci | | utf8_slovenian_ci | ...
Here we take as an example the BD: Sakila
> SELECT COLUMN_NAME,COLLATION_NAME, CHARACTER_SET_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'actor' AND TABLE_SCHEMA = 'sakila'; +-------------+-----------------+--------------------+ | COLUMN_NAME | COLLATION_NAME | CHARACTER_SET_NAME | +-------------+-----------------+--------------------+ | actor_id | NULL | NULL | | first_name | utf8_general_ci | utf8 | | last_name | utf8_general_ci | utf8 | | last_update | NULL | NULL | +-------------+-----------------+--------------------+
– F. van der lan, Rick. SQL for MySQL Developers, Part III, Chapter 22.
MySQL has a refined security scheme, see MySQL: administration of users, but some time is necessary to allow a user to connect to our MySQL server from any computer on the Local Area Network, let's have a development server: devsrv and can make ssh:
MySQL is a relational database server with a great reputation for being fast, consume few resources and adapt very well to the demands of the web. In this article we will show how to connect, from the command line, to a MySQL server, as well as create, list, select and delete a database as well as show information about tables and storage engines
mysql [-h mysql-server] -u user-name -p[password] [BD]
We encourage using the option -p instead of -ppassword, because the latter is less secure (password is saved in the history)
The default mysql server is localhost
create schema DB;
create database DB default character set = UTF8 default collate =utf8_general_ci;
schema is an alias for database command and can be used both interchangeably
show collation;
Or by specifying a specific charset
show collation like 'utf8%';
drop schema BD;
show schemas;
use DB;
show tables;
desc table-name;
show columns from table-name where Field='column-name'
show index from table-name;
show engines;
-man mysql