MySQL

1
MySQL

• Presented by Jordan Nedelchev, FN
• Course Modern Software Technologies

2
MySQL

• The principal author
• Monty Widenius

3
WHAT IS MYSQL

• Free SQL (Structured Query Language) database
  server.
• licensed with the GNU General public license
  http//www.gnu.org/
• MySQL is a database management system.
• MySQL is a relational database management
  system.
• MySQL is Open Source Software.

4
THE IDEA

• Client PC mysql client mysql
• Server PC MySQL Server safe_mysqld
• QUERY
• RESPONSE
• Separate the logical part of the queries from the
  implementation logicalgly, and phisically.

5
HISTORY

• The authors started to use mSQL, but they came to
  the conclusion that mSQL as not fast enough and
  not flexible enough to face their needs.
• The reslt is a new SQL server - MySQL
• The name MySQL
  The directories "my
  Monty's daughter My

6
MAIN SUPPORTED PLATFORMS

• First developed for Solaris and RedHat Linux.
• FreeBSD.
• OpenBSD.
• Mac OS X Server.
• Win95, Win98, NT, and Win2000.
• All modern systems with working Posix
  threads and a C compiler.

7
DOWBNLOAD

• http//www.mysql.com/ Download instructions
• http//download.sourceforge.net/mirrors/mysql/
• RedHat and SuSe Linux distributions.
• http//www.analysisandsolutions.com/code/mybasic.h
  tm Beginners MySQL Tutorial on how to install and
  set up MySQL on a Windows machine.

8
INSTALLATION

• Under RedHat Linux from an RPM package (install
  as root) rpm -i MySQL-VERSION.i386.rpm
  MySQL-client-VERSION.i386.rpm
• Under any (other) Linux (install as root)
  groupadd mysql
  
  useradd -g
  mysql mysql
  cd
  /usr/local
  
  gunzip lt /path/to/mysql-VERSION-OS.tar.gz tar
  xvf - ln -s
  mysql-VERSION-OS mysql
  cd
  mysql
  scripts/mysql_insta
  ll_db
  chown -R mysql
  /usr/local/mysql
  chgrp -R mysql
  /usr/local/mysql
  bin/safe_mysqld
  --usermysql

9
START MYSQL Server

• As root under Linux with the command
• safe_mysqld

10
START A MYSQL CLIENT

• Without using passwords
  (when the password for
  the specified user is empty)
  mysql -u ltusergt -h
  ltHostgt
• Using passwords
  mysql -u ltusergt -h ltHostgt -p
  Example mysql -u root -h localhost
• Exitting with the command quit or exit.

11
DATA MANAGEMENT

• SHOW DATABASES
• USE databaseName
• SHOW TABLES
• DESCRIBE table
• SELECT FROM table
• SELECT FROM table \G
• CREATE DATABASE databaseName
• DROP DATABASE databaseName
• CREATE TABLE tableName(name1 type1, name2 type2,
  ...)
• DROP TABLE tableName
• INSERT INTO TABLE VALUES( value1, value2, ...)
• SELECT field1, field2, ... FROM tableName
• SELECT INTO OUTFILE 'C/tmp/skr.txt' FROM skr
  
• LOAD DATA INFILE /path/file.txt INTO TABLE skr

12
(No Transcript)
13
(No Transcript)
14
GRANT PRIVILEGES

• Change the password of the MySQL's administrator
  (root) mysql -u root
  mysql
  UPDATE USER SET
  Passwordpassword('new_password) WHERE
  user'root' FLUSH
  PRIVILEGES
  OR
  
  
  SET PASSWORD FOR
  rootPASSWORD('new_password')

15
Main Features

• Fully multi-threaded using kernel threads.
• Works on many different platforms.
• Many column types
• Very fast joins using an optimized one-sweep
  multi-join
• Full operator and function support in the SELECT
  and WHERE parts of queries.
• You can mix tables from different databases in
  the same query.
• A privilege and password system that is very
  flexible and secure.
• Handles large databases.
• Tested with a broad range of different
  compilers. (C/C)
• No memory leaks.
• Full support for several different character
  sets.

16
Documentation

• http//www.mysql.com/documentation/
• http//www.mysql.com/documentation/manual.php
• As text manual.txt
• As HTML manual_toc.html
• As GNU Info mysql.info
• As PostScript manual.ps http//www.turbolift.co
  m/mysql

17
ADVANTAGES

• very fast
• reliable and easy to use
• multi-threaded multi-user and robust SQL
  database server.

18
DISADVANTAGES

• Missing Sub-selects.
• MySQL doesn't yet support the Oracle SQL
  extension i SELECT ... INTO TABLE , but supports
  INSERT INTO ... SELECT ..
• Does not support Stored Procedures and
  Triggers.
• MySQL doesn't support views, but this is on the
  TODO.