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%';
