What is My SQL Free SQL Structured Query

What is My. SQL ? • Free SQL (Structured Query Language) database server. • licensed with the GNU General public license http: //www. gnu. org/ • My. SQL is a database management system. • My. SQL is a relational database management system. • My. SQL is Open Source Software. 9/16/2020 Technology Park, BDU 1

Start My. SQL • Without using passwords $ mysql -u <user> -h <host> • Using passwords $ mysql -u <user> -h <Host> -p • After login it goes to mysql prompt mysql> quit (or) bye 9/16/2020 Technology Park, BDU 2

DATA MANAGEMENT • • • • SHOW DATABASES; USE database. Name; SHOW TABLES; DESCRIBE table; SELECT * FROM table; SELECT field 1, field 2, . . . FROM table. Name; CREATE DATABASE database. Name; DROP DATABASE database. Name; CREATE TABLE table. Name(name 1 type 1, name 2 type 2, . . . ); DROP TABLE table. Name; INSERT INTO TABLE VALUES( value 1, value 2, . . . ); UPDATE table. Name SET name 1=‘xyz’ WHERE name 2=100; DELETE FROM table. Name WHERE name 2=100; 9/16/2020 Technology Park, BDU 3

9/16/2020 Technology Park, BDU 4

9/16/2020 Technology Park, BDU 5

Database Administration • Create new My. SQL account mysql> CREATE USER user [INDENTIFIED BY 'password']; • Drop user mysql> DROP USER user [, user]. . . • Grant Privileges mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON db. * TO jack@localhost INDENTIFIED BY 'password'; 9/16/2020 Technology Park, BDU 6

SET PASSWORD • SET PASSWORD statement assign password to an existing My. SQL user account. mysql> SET PASSWORD FOR jack = PASSWORD('newpasswd'); (or) mysql> update mysql. user SET Password=PASSWORD('newpass') WHERE User='jack' AND Host='localhost'; 9/16/2020 Technology Park, BDU 7

EXPORTING DATA To make a simple backup of the server or export certain columns from a table into another program such as Microsoft Access • mysqldump • the SELECT INTO OUTFILE statement mysql> mysqldump database tablename (or) mysql> SELECT * FROM pet INTO OUTFILE 'pet. txt'; 9/16/2020 Technology Park, BDU 8

IMPORTING DATA To import the data from some other program use • mysqlimport • LOAD DATA INFILE mysql> mysqlimport database textfile; (or) mysql>LOAD DATA INFILE 'filename. txt' INTO TABLE <table name> [filedname]; 9/16/2020 Technology Park, BDU 9

Advantage • • Powerful Sophisticated Easy-to-use/manage Portable Integrates very well with PHP Runs on small PC's to large multi-processor's handle large data sets 9/16/2020 Technology Park, BDU 10

Disadvantage • Does not support relational integrity constrains 9/16/2020 Technology Park, BDU 11