MYSQL Notes

Basic Actions

CREATE DATABASE database_name ;

USE database_name;

mysql> create database telephone;
Query OK, 1 row affected (0.02 sec)
mysql> show databases;
+--------------------+
Database
+--------------------+
information_schema
mysql
telephone
test
+--------------------+
4 rows in set (0.00 sec)
mysql> use telephone;
Database changed
mysql>

Create a table

Statements must be ended with a semicolon in order to be executed.

CREATE TABLE	npanxx
(
npa	int	NOT NULL ,
nxx	int	NOT NULL ,
series	int	NULL ,
State	char(2)	NULL ,
CompanyName	char(100)	NULL ,
OCN	int	NULL ,
RateCenter	char(15)	NULL ,
CLLI	char(15)	NULL ,
AssignDate	date	NULL ,
RecordNum	int	NOT NULL AUTO_INCREMENT ,
PRIMARY KEY (	RecordNum	)
) ENGINE = InnoDB;

An example of inserting one record into the table npanxx String literals and dates are in single quotes.

INSERT INTO npanxx( npa,
nxx,
series,
State,
CompanyName,
OCN,
RateCenter,
CLLI,
AssignDate)
VALUES( 703, 999,
NULL ,
'VA',
'CELLCO PARTNERSHIP DBA VERIZON WIRELESS - VA (Verizon Wireless) ',
6827,
'LEESBURG',
'FRBGVARDCM1',
'2001-11-21');

INSERT INTO npanxx( npa,
nxx,
series,
State,
CompanyName,
OCN,
RateCenter,
CLLI,
AssignDate)
VALUES (703,300,0,'VA','CELLCO PARTNERSHIP DBA VERIZON WIRELESS - VA (Verizon Wireless)',6827,'WSNGTNZN17','ANJTMDAHCM2','2004-09-09'),
(703,300,1,'VA','CELLCO PARTNERSHIP DBA VERIZON WIRELESS - VA (Verizon Wireless)',6827,'WSNGTNZN17','ANJTMDAHCM2','2005-01-11'),
(703,300,2,'VA','CELLCO PARTNERSHIP DBA VERIZON WIRELESS - VA (Verizon Wireless)',6827,'WSNGTNZN17','ANJTMDAHCM2','2005-01-11'),
(703,300,7,'VA','NEW CINGULAR WIRELESS PCS, LLC - DC',4036,'WSNGTNZN17','BTVLMD18GT0','2005-06-09'),
(703,300,8,'VA','NEW CINGULAR WIRELESS PCS, LLC - DC',4036,'WSNGTNZN17','BTVLMD18GT0','2005-06-09');

Here, I have executed a simple select query to verify that the values have been inserted into the table.

mysql> select npa,nxx,CompanyName from npanxx where nxx between 200 and 209 order by nxx;
+-----	+-----	+-----------------------------------------------------------------+
npa	nxx	CompanyName
+-----	+-----	+-----------------------------------------------------------------+
703	200	SPRINT SPECTRUM L.P. (Sprint)
703	201	CELLCO PARTNERSHIP DBA VERIZON WIRELESS - VA (Verizon Wireless)
703	202	N/A
703	203	NEW CINGULAR WIRELESS PCS, LLC - DC
703	204	VERIZON VIRGINIA, INC.
703	205	VERIZON VIRGINIA, INC.
703	206	VERIZON VIRGINIA, INC.
703	207	VERIZON VIRGINIA, INC.
703	208	VERIZON VIRGINIA, INC.
703	209	NEW CINGULAR WIRELESS PCS, LLC - DC
+-----	+-----	+-----------------------------------------------------------------+
10 rows in set (0.13 sec)

Copy table from one database to another.

CREATE TABLE target database.new table name SELECT * FROM database name .old table name;

mysql> CREATE TABLE telephone.vanpanxx SELECT * FROM test.npanxx;
Query OK, 1773 rows affected (0.69 sec)
Records: 1773 Duplicates: 0 Warnings: 0
mysql>

To delete a table

mysql> DROP TABLE npanxx;
Query OK, 0 rows affected (0.05 sec)
mysql>

To send query results to a comma delimited file.

mysql>
mysql> SELECT npa,nxx,series,CompanyName FROM vanpanxx INTO OUTFILE 'C:/Databases/npanxx703b.csv' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';
Query OK, 1773 rows affected (0.34 sec)
mysql>

Matching with a Regular Expression

mysql> SELECT npa,nxx, CompanyName,RateCenter FROM vanpanxx WHERE CompanyName REGEXP 'worldcom' ORDER BY npa,nxx;

-----	-----	--------------------------------------------------	------------
npa	nxx	CompanyName	RateCenter
-----	-----	--------------------------------------------------	------------
703	463	MCI WORLDCOM COMMUNICATIONS, INC. - VA (Verizon)	HERNDON
703	483	MCI WORLDCOM COMMUNICATIONS, INC. - VA (Verizon)	HERNDON
703	483	MCI WORLDCOM COMMUNICATIONS, INC. - VA (Verizon)	HERNDON
703	485	MCI WORLDCOM COMMUNICATIONS, INC. - VA (Verizon)	WSNGTNZN17
703	663	MCI WORLDCOM COMMUNICATIONS, INC. - VA (Verizon)	WSNGTNZN17
703	663	MCI WORLDCOM COMMUNICATIONS, INC. - VA (Verizon)	WSNGTNZN17
703	663	MCI WORLDCOM COMMUNICATIONS, INC. - VA (Verizon)	WSNGTNZN17
703	682	MCI WORLDCOM COMMUNICATIONS, INC. - VA (Verizon)	WSNGTNZN08
703	852	MCI WORLDCOM COMMUNICATIONS, INC. - VA (Verizon)	WSNGTNZN17
703	891	MCI WORLDCOM COMMUNICATIONS, INC. - VA (Verizon)	WSNGTNZN17
703	923	MCI WORLDCOM COMMUNICATIONS, INC. - VA (Verizon)	WSNGTNZN17
703	995	MCI WORLDCOM COMMUNICATIONS, INC. - VA (Verizon)	WSNGTNZN19
-----	-----	--------------------------------------------------	------------

12 rows in set (0.03 sec)