MySQL Tutorials - Herong's Tutorial Examples - v4.46, by Herong Yang
Using "mysql" Command to Run SQL Statements
This section provides some tutorial examples on how to run SQL statements on a MySQL server with the client tool mysql. SQL statements can be executed one by one interactively, or in batch mode.
Once mysql is started, it will present an interactive prompt for you to run any SQL statements on the MySQL server or any mysql commands:
In the following example, I executed 1 mysql command and 4 SQL statements with mysql:
herong> %mysql%\bin\mysql --host=localhost \ --user=root --password=TopSecret mysql> create database test; mysql> use test mysql> create table hello (message varchar(80)); Query OK, 0 rows affected (0.41 sec) mysql> insert into hello (message) values ('Hello world!'); Query OK, 1 row affected (0.03 sec) mysql> select * from hello; +--------------+ | message | +--------------+ | Hello world! | +--------------+ 1 row in set (0.00 sec) mysql> drop table hello; Query OK, 0 rows affected (0.00 sec) mysql> quit Bye
Note that:
In the previous example, SQL statements were executed interactively one by one. Another way to execute SQL statements is to put them into a file, and execute them in one command. First let's store all the statements we used in the previous into a file, hello.sql:
-- hello.sql -- Copyright (c) 1999 HerongYang.com. All Rights Reserved. -- create table hello (message varchar(80)); insert into hello (message) values ('Hello world!'); select * from hello; drop table hello;
To execute the statements in hello.sql, you can use the "source" command inside mysql:
herong> %mysql%\bin\mysql --host=localhost \ --user=root --password=TopSecret mysql> use test mysql> source hello.sql Query OK, 0 rows affected (0.05 sec) Query OK, 1 row affected (0.00 sec) +--------------+ | message | +--------------+ | Hello world! | +--------------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec)
Or you can run mysqsl in batch mode:
herong> %mysql%\bin\mysql --host=localhost \ --user=root --password=TopSecret test < hello.sql message Hello world!
As you can see, this is a much better way to execute SQL statements.
Table of Contents
MySQL Introduction and Installation
►Introduction of MySQL Programs
mysqld - The MySQL Server Program
mysqladmin - The Client Tool for Administrators
mysql - The Client Tool for End Users
►Using "mysql" Command to Run SQL Statements
mysqldump - Dumping Data to Files
--secure-file-priv="" - MySQL Server Option
mysqlimport - Loading Data from Files
Perl Programs and MySQL Servers
Java Programs and MySQL Servers
Character Strings and Bit Strings
Table Column Types for Different Types of Values
Using DDL to Create Tables and Indexes
Using DML to Insert, Update and Delete Records
Using SELECT to Query Database
Window Functions for Statistical Analysis
Use Index for Better Performance
Transaction Management and Isolation Levels
Defining and Calling Stored Procedures
Variables, Loops and Cursors Used in Stored Procedures
System, User-Defined and Stored Procedure Variables
Storage Engines in MySQL Server
InnoDB Storage Engine - Primary and Secondary Indexes
Performance Tuning and Optimization
Installing MySQL Server on Linux