![]() String Literals: A string literal (or string value) is enclosed by a pair of single quotes (e.g., 'a string') (recommended) or a pair of double quotes (e.g., "a string"). (Need to further check on Unixes and Macs.) Literals By default, string comparison in MySQL (Windows) are not case sensitive. String comparison and sorting depend on the character collation sequence used. Hence, it is a good practice to treat the identifiers as case-sensitive in th script. The identifiers (such as database names, table names and column names) are case sensitive in some platforms but case insensitive in others (e.g., In general, identifiers are case sensitive in Unixes but case-insensitive in Windows). For clarity, I often show the keywords in uppercase (e.g., CREATE TABLE, SELECT). It is a good practice to back-quote all the identifiers in a script to distinguish the names from the reserved words (possibly in future MySQL versions). Identifiers (such as database names, table names and column names) must be back-quoted if they contain blanks and special characters or are reserved word, e.g., `date`, `order`, `desc` (reserved words), `Customer Name` (containing space). *!40101 SET SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */ ĬREATE DATABASE /*!32312 IF NOT EXISTS*/ `eastwind` /*!40100 DEFAULT CHARACTER SET latin1 */ MySQL specific codes (with version number) are often generated when you export a database via mysqldump utility. You can find the MySQL server version via show version() command. The statements will be processed by MySQL if the server's version is at least at the specified version, e.g., version 4.00.14. You could include an optional MySQL version number, e.g., /*!40014. In other words, they will be processed by MySQL but treated as comments by other databases. They are recognized by the MySQL engine, but ignored by other database engines. ![]() I strongly encourage you to use comments liberally. An end-of-line comment begins with '- ' (two dashes and a space) or #, which lasts till the end of the current line.Ĭomments are ignored by the processing engine but are important to provide explanation and documentation for the script. Hence, there is no need for the terminating ' ' (to send the command to the server for processing).Ī multi-line comment begins with /* and ends with */ (just like the C/C /Java language). No single or double quotes needed (nor allowed) in filename.On Windows systems, you could also replace backslash '\' with double backslash e.g., " d:\\myproject\\sqlscripts\\mytestscript.sql". You need to use Unix-style forward-slash '/' as the directory separator.You could provide either absolute or relative path.Mysql> SOURCE d:/myproject/sqlscripts/mytestscript.sql You can use 'source' command to run a script For example, - Start and login to a mysql interactive client In an interactive mysql client session, you can use the source command (or \. > mysql -u myuser -p -t mysql -u myuser -p -t -vvv mysql -u myuser -p -vvv -e "SELECT user, host FROM user SHOW databases" mysql To get the "table-like" output, use -t (table) option, for example, > mysql -u username -p mysql -u myuser -p '), for example, To run a script in batch (non-interactive) mode, start a mysql client and redirect the script as the input, as follows: You can run the script using mysql client in two ways: batch mode or using source command. SELECT user, host FROM user - List all users by querying table 'user' USE mysql - Set system database 'mysql' as the current database SHOW DATABASES - List the name of all the databases in this server * You need to run this script with an authorized user. * My First MySQL Script - testscript.sql. sql" file as a SQL script with syntax highlighting. I recommend NetBeans which provides direct support to MySQL database (read NetBeans and MySQL), or NotePad ), which recognizes ". Use a programming text editor to create the following script and saved as " testscript.sql" in a chosen directory (e.g., " d:\myproject\sqlscripts"). You could then run the entire script, or copy and paste selected statements to run. Instead of issuing each of the SQL statements from a mysql client interactively, it is often more convenience to keep the statements in a script. I shall begin by describing the syntax of a MySQL script, as scripts will be used for all the examples in this tutorial. Read " MySQL for Beginners" for the basics. This article explains some concepts in depth, such as scripting and data types.
0 Comments
Leave a Reply. |