SQL Tutorial

Some basic SQLite commands:

.schema table_name
select * from table_name;
select * from table_name where id='640';
.table
delete from devices;
delete from devices where id=25;
alter table devices ADD batteryStatus VARCHAR(255);
create table if not exists table_name (number, name);
create table table_name (Column1 int,
        Column2 varchar(255),
        Column3 varchar(255)
        );
drop table table_name;
.quit

Basic MySQL:

You can access MySQL in command line just by typing:
C:\Program Files\mysql\bin> mysql -u root -p

After which you can type sql commands normally such as:
mysql> SHOW DATABASES;

(Here, I am assuming you mySQL installation directory is "C:\Program Files\mysql")

The sample commands:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.17 sec)

mysql> select version();
+------------+
| version()  |
+------------+
| 5.6.12-log |
+------------+
1 row in set (0.03 sec)

mysql> select version(), current_date;
+------------+--------------+
| version()  | current_date |
+------------+--------------+
| 5.6.12-log | 2013-07-16   |
+------------+--------------+
1 row in set (0.07 sec)

mysql> select sin(pi()/4), (4+1)*5;
+--------------------+---------+
| sin(pi()/4)        | (4+1)*5 |
+--------------------+---------+
| 0.7071067811865475 |      25 |
+--------------------+---------+
1 row in set (0.06 sec)

mysql> select version(); select now();
+------------+
| version()  |
+------------+
| 5.6.12-log |
+------------+
1 row in set (0.00 sec)

+---------------------+
| now()               |
+---------------------+
| 2013-07-16 11:14:01 |
+---------------------+
1 row in set (0.00 sec)

mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

mysql> create database medical;
Query OK, 1 row affected (0.06 sec)

mysql> use medical
Database changed
mysql> show tables;
Empty set (0.01 sec)

mysql> create table doctor (name varchar(25), birth date);
Query OK, 0 rows affected (0.19 sec)

mysql> show tables;
+-------------------+
| Tables_in_medical |
+-------------------+
| doctor            |
+-------------------+
1 row in set (0.00 sec)

mysql> insert into doctor
    -> values ('ken','1999-3-3');
Query OK, 1 row affected (0.04 sec)

mysql> describe doctor;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(25) | YES  |     | NULL    |       |
| birth | date        | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> select * from doctor;
+------+------------+
| name | birth      |
+------+------------+
| ken  | 1999-03-03 |
+------+------------+
1 row in set (0.02 sec)

mysql> insert into doctor
    -> values ('boa','1977-3-3');
Query OK, 1 row affected (0.05 sec)

mysql> update doctor set birth = '1989-3-3' where name = 'ken';
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from doctor;
+------+------------+
| name | birth      |
+------+------------+
| ken  | 1989-03-03 |
| boa  | 1977-03-03 |
+------+------------+
2 rows in set (0.00 sec)

mysql> select * from doctor where name = 'ken';
+------+------------+
| name | birth      |
+------+------------+
| ken  | 1989-03-03 |
+------+------------+
1 row in set (0.00 sec)

mysql> select * from doctor where birth >= '1970-1-1';
+------+------------+
| name | birth      |
+------+------------+
| ken  | 1989-03-03 |
| boa  | 1977-03-03 |
+------+------------+
2 rows in set (0.00 sec)

mysql> alter table doctor ADD skill VARCHAR(255);
Query OK, 0 rows affected (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> describe doctor;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| name  | varchar(25)  | YES  |     | NULL    |       |
| birth | date         | YES  |     | NULL    |       |
| skill | varchar(255) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> delete from doctor where birth='1111-01-01';
Query OK, 1 row affected (0.04 sec)

mysql> rename table cus_item to patient_item;
Query OK, 0 rows affected (0.14 sec)

mysql> alter table patient_item change bp_time item_time datetime;
Query OK, 0 rows affected (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 0

**SQL script**
use medical;
SET SQL_SAFE_UPDATES=0;
update doctor set skill = 'gastronomy' where name = 'ken';
select * from doctor;

Advanced MySQL commands

** change a column to be a primary key **
mysql> alter table patient add column patient_id int(10) unsigned primary key auto_increment;
Query OK, 2 rows affected (0.01 sec)

Records: 2  Duplicates: 0  Warnings: 0

** delete a column from table **
mysql> alter table patient drop column patient_id;
Query OK, 2 rows affected (0.02 sec)

Records: 2  Duplicates: 0  Warnings: 0

** Output to Excel format **
mysql> select * from doctor into outfile 'doctor.csv' fields terminated by ',';

** move SQL column order sequence **
mysql> ALTER TABLE Employees MODIFY COLUMN empName VARCHAR(50) AFTER department;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

** change SQL column data type **
mysql> alter table patient modify column Gluco int;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

** select and count **
mysql> select a.*, count(name) as total  from doctor a;
+------+------------+----------+-------+
| name | birth      | skill    | total |
+------+------------+----------+-------+
| ken  | 1989-03-03 | surgeon1 |     3 |
+------+------------+----------+-------+
1 row in set (0.00 sec)

** foreign key, primary key **
mysql> create table patient
    -> (cus_id int NOT NULL, cus_name varchar(50), primary key(cus_id));
Query OK, 0 rows affected (1.14 sec)

mysql> describe patient;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| cus_id   | int(11)     | NO   | PRI | NULL    |       |
| cus_name | varchar(50) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.26 sec)

mysql> create table cus_item
    -> (cus_item_id int NOT NULL, cus_id int, bp int, bp_time datetime, primary
key(cus_item_id), foreign key(cus_id) references `patient`(cus_id));
Query OK, 0 rows affected (0.15 sec)

mysql> describe cus_item;
+-------------+----------+------+-----+---------+-------+
| Field       | Type     | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+-------+
| cus_item_id | int(11)  | NO   | PRI | NULL    |       |
| cus_id      | int(11)  | YES  | MUL | NULL    |       |
| bp          | int(11)  | YES  |     | NULL    |       |
| bp_time     | datetime | YES  |     | NULL    |       |
+-------------+----------+------+-----+---------+-------+
4 rows in set (0.09 sec)

mysql> select * from `patient` o join cus_item oi
    -> on o.cus_id = oi.cus_id
    -> where o.cus_id = 5;
Empty set (0.11 sec)

mysql> insert into patient values
    -> (5,'poi');
Query OK, 1 row affected (0.08 sec)

mysql> insert into cus_item values
    -> (1, 5, 89, '1999-01-01 00:00:00');
Query OK, 1 row affected (0.01 sec)

mysql> insert into cus_item values
    -> (2, 5, 69, '1999-02-01 00:00:00');
Query OK, 1 row affected (0.04 sec)

mysql> select * from `patient` o join cus_item oi
    -> on o.cus_id = oi.cus_id
    -> where o.cus_id = 5;
+--------+----------+-------------+--------+------+---------------------+
| cus_id | cus_name | cus_item_id | cus_id | bp   | bp_time             |
+--------+----------+-------------+--------+------+---------------------+
|      5 | poi      |           1 |      5 |   89 | 1999-01-01 00:00:00 |
|      5 | poi      |           2 |      5 |   69 | 1999-02-01 00:00:00 |
+--------+----------+-------------+--------+------+---------------------+
2 rows in set (0.00 sec)

** save a file directly into MySQL **
mysql> CREATE TABLE IF NOT EXISTS `Files` (
    ->   `ID` int(25) NOT NULL auto_increment,
    ->   `Title` varchar(250) NOT NULL default '',
    ->   `File_Name` varchar(250) NOT NULL default '',
    ->   `File_Type` varchar(15) NOT NULL default '',
    ->   `File_Size` varchar(45) NOT NULL default '',
    ->   `File_Content` longblob NOT NULL,
    ->   `File_Extension` varchar(10) NOT NULL default '',
    ->   PRIMARY KEY  (`ID`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;
Query OK, 0 rows affected (0.76 sec)

** insert a row into the Files table **

mysql> insert into Files set title = 'me', file_name = 'me.txt', file_type = 'text', file_size = '4k', file_content = '444555', file_extension = 'txt';
Query OK, 1 row affected (0.08 sec)

** update a column with new datetime data Format: YYYY-MM-DD HH:MM:SS **
mysql> update patient_item set item_time = '2012-8-1 15:21:11' where patient_item_id = 107;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

** select data based on time and id **
mysql> select * from cus_item where bp_time <='2012-08-02 08:00:00' and cus_id=5;
+-------------+--------+------+---------------------+-------------+
| cus_item_id | cus_id | bp   | bp_time             | doc_message |
+-------------+--------+------+---------------------+-------------+
|           1 |      5 |   89 | 1999-01-01 00:00:00 | NULL        |
|           2 |      5 |   69 | 1999-02-01 00:00:00 | NULL        |
+-------------+--------+------+---------------------+-------------+
2 rows in set (0.05 sec)

Change Primary Key Size

***change primary key size****
mysql> create table ref (ref_id int unsigned not null, something varchar(30), primary key(ref_id));
Query OK, 0 rows affected (0.21 sec)

mysql> describe ref;
+-----------+------------------+------+-----+---------+-------+
| Field     | Type             | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+-------+
| ref_id    | int(10) unsigned | NO   | PRI | NULL    |       |
| something | varchar(30)      | YES  |     | NULL    |       |
+-----------+------------------+------+-----+---------+-------+
2 rows in set (0.03 sec)

mysql> alter table ref drop primary key , add column newcol int PRIMARY KEY  ;
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> describe ref;
+-----------+------------------+------+-----+---------+-------+
| Field     | Type             | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+-------+
| ref_id    | int(10) unsigned | NO   |     | NULL    |       |
| something | varchar(30)      | YES  |     | NULL    |       |
| newcol    | int(11)          | NO   | PRI | NULL    |       |
+-----------+------------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> alter table ref modify column ref_id int(20) unsigned;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table ref drop primary key;
Query OK, 0 rows affected (0.20 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> describe ref;
+-----------+------------------+------+-----+---------+-------+
| Field     | Type             | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+-------+
| ref_id    | int(20) unsigned | YES  |     | NULL    |       |
| something | varchar(30)      | YES  |     | NULL    |       |
| newcol    | int(11)          | NO   |     | NULL    |       |
+-----------+------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> alter table ref add column ref_id int(20) unsigned primary key auto_incre
ment;
ERROR 1060 (42S21): Duplicate column name 'ref_id'
mysql> alter table ref modify column ref_id int(20) unsigned primary key auto_in
crement;
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> describe ref;
+-----------+------------------+------+-----+---------+----------------+
| Field     | Type             | Null | Key | Default | Extra          |
+-----------+------------------+------+-----+---------+----------------+
| ref_id    | int(20) unsigned | NO   | PRI | NULL    | auto_increment |
| something | varchar(30)      | YES  |     | NULL    |                |
| newcol    | int(11)          | NO   |     | NULL    |                |
+-----------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

Change MySQL root password

You can change the MySQL root password using mysqladmin command as shown below. Please note that there is no space between -p and currentpassword.

# mysqladmin -u root -pcurrentpassword password 'newpassword'


Copy MySQL Database from one server to another server

You create a database backup file at the local server
$ mysqldump -u user -p db-name > db-name.out

Using whatever method, copy the database backup file to the remote server

You restore database at remote server
$ mysql -u user -p db-name < db-name.out

SQL Like Opeartor

The Like Operatoe is able to do partial matching with the sql table.

Select * from profile where last_name like "%Mary%" or first_name LIKE "%Mary%"
select * from profile where last_name like '%M%';