Spanish Spanish

MySQL Tutorial

This Tutorial goal is to give you a basic knowledge about MySQL and to serve as a reference manual for the command's syntax that we don't remember when we need it.

Installation:

If you are using RedHat 7.1, mysql installation is easy. Get the second distribution's CD and mount it and look for:
mysql-3.23.36-1.i386.rpm
mysqlclient9-3.23.22-4.i386.rpm
mysql-devel-3.23.36-1.i386.rpm
mysql-server-3.23.36-1.i386.rpm
php-mysql-4.0.4pl1-9.i386.rpm

mysql-devel is for developers that wants to make his own mysql software .
php-mysql is the PHP suport for MySQL.

If you're using other Linux distributions read the documentation or go to MySQL download area and download and compile it following the MySQLweb site instructions.

Being root install the .rpm packages: rpm -ivh mysql*.rpm
Install also rpm -ivh php-mysql*.rpm and follow this steps:
Now we must activate the daemon (server)
On RedHat type setup from the console, select System Services and activate mysqld, then reboot and the daemon will be active.

Once Installed:

Warning: Security alert.
The first time you start it: Change root password using this commands:

mysql
use mysql;
select * from user;
mysqladmin -uroot -hlocalhost password newpassword
mysqladmin -uroot -hyourhostname password newpassword
Because there are 2 entries on our user table. That means that root user could access your database from host localhost and from host yourhostname.

To create a standard user with access to a dabatase:

create database databasename;
grant select,insert,update,delete,create,drop,alter,index on databasename.* to username@localhost identified by 'thepassword';

We are giving username access to databasename for select,insert,update,delete,create, drop, alter, index when conecting from localhost and using a password

Usually databasename and username are both the same. (only for comodity)

Basic MySQL commands:
Type this if you are on the server's console or if you're using a remote server, just telnet hostname and then type:
mysql -hlocalhost -uroot -p
(we are on localhost and user=root and we have to type a password to log in MySQL)

Some sample commands to practice:

create database databasename;
show databases;
use databasename;
create table tablename (field1 integer,field2 char(50));
create table tablename (field1 integer not null auto_increment,field2 integer, primary key (field1));
insert into nombretabla values (666,"anytext"),(777,"anothertext");
select * from tablename;
show tables;
To create a user with all privileges in all the databases that connects from a specific host:
grant all privileges on *.* to username@192.168.0.this identified by 'thepassword';
To do the same but allow to connect from any host:
grant all privileges on *.* to username@"%" identified by 'thepassword';

To look at the fields structure:
show columns from tablename;
or
describe tablename;  

Delete the table and it's structure
drop table tablename;
Delete only desired rows:
delete from tablename where field=value;
Update a field's value:
update tablename set field=newvalue where condition;

Specific querys:
select sum(fieldtosum) as total from tablename where conditions;
      (sum,max,min,count,avg)
      (...where surname in ('velez','smith','lee'))
      (...where price between 1000 and 5000))
      (...where price not between 1000 and 5000))
select * from tablename order by fieldname limit howmany;
select fieldname from tablename where fieldname like 'wordbegin%';
      (correspondences are: * is %,  ? is  _)

Importing from other databases:

To convert a Microsoft Database to use with MySQL, you must: In Access, select the table and then Export, file type .txt, delimited,tabulation, text cualifier=nothing, send the file.txt to your FTP account an then this command will do the rest:
load data local infile "file.txt" into table tablename;

Backup and Restore your Dabatase:

I suggest you to read the Tao of Backup
Backup your database on a file:
mysqldump -uusername -ppassword -hhostname databasename > file.sql
This (file.sql) will contain all the information (database structure and data) necessary to create it again. Warnig: This doesn't include the users and the  permissions (the administrator task is this)
To restore the data:
mysql -uusername -ppassword -hhostname databasename < file.sql

Accessing other databases:

Using MyODBC you could use a Microsoft Access Database
Control Panel , ODBC, USER DNS

Windows DNS name: Test
MySQL host (name or IP): yourhostnameorIP
MySQL databasename: thedatabasename
password:  yourpassword
port: 3306


Compiling your C code that uses MySQL functions:

You must install MySQL developer to be able to do this:
gcc -I/usr/include/mysql -L/usr/lib/mysql -o executablename myfile.c -lmysqlclient



by: drdoom@sorgonet.com
www.sorgonet.com