MySQL Tutorials

[nfm]

Hi,

I need some help feeding mysql with commands, I got some good info and even video tutorials but they're out of date and syntax changed.

Basically this is what I need:

- Basic Unix/Linux shell commands such as viewing, creating database/tables for v5.0+

- Same thing goes for Windows

I've looked into official manual, it's pretty complicated. I also have something called phpmyadmin, does that handles pretty much everything that command does through gui?

Well this is what phpmyadmin team says:

phpMyAdmin is a tool written in PHP intended to handle the administration of MySQL over the Web. Currently it can create and drop databases, create/drop/alter tables, delete/edit/add fields, execute any SQL statement, manage keys on fields, manage privileges,export data into various formats and is available in 50 languages. GPL License information.

Which one would be better to maintain database? Note that most of the things I will run on Linux and on Linux it is not easy/efficient to work with files since I don't have my favorite text editor and everything is accessible only by root user, unless I keep inputing password which is time consuming.

Having said that, once I learn to use and remember basic commands, I will work myself up reading the manual .

it's not like I didn't google, I just can't find what I'm looking for. I'm still googling anyway...

[ripken204]

well you can use php do grap info out of the database and to put info back into it. i have very little practice with mysql and i'de like to find some good tutorial too. the first thing you need to do is to make php and your mysql database talk to eachother, just google for php mysql and you should be able to find a good tutorial on it. for windows how do you have all this set up for testing? are you using apache?

[nfm]

Yeah, I have everything setup on both machines, I'm using apache on both of course. I ended up using xampp on linux because I couldn't get apache, php, mysql to talk to each other. On Windows everything is good. I will post basic commands when I'll find them and test them.

well this is how you connect to th db using shell cmd on both unix and windows:

cd C:\Program Files (x86)\xampp\mysql\bin

mysql -u root -p

"You may asked for password" and that's it, you can type --help or look into manual.

linux may differ:

cd /opt/lampp/bin

mysql -u root -p

edit: i had a talk with mysql

C:\Program Files (x86)\xampp\mysql\bin>mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with; or \g.
Your MySQL connection id is 3 to server version: 5.0.21-community

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show databases;
+--------------------+
| Database		   |
+--------------------+
| information_schema |
| cdcol			  |
| mysql			  |
| phpmyadmin		 |
| test			   |
| webauth			|
+--------------------+
6 rows in set (0.00 sec)

mysql> create database sexy;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database		   |
+--------------------+
| information_schema |
| cdcol			  |
| mysql			  |
| phpmyadmin		 |
| sexy			   |
| test			   |
| webauth			|
+--------------------+
7 rows in set (0.00 sec)

mysql> use sexy;
Database changed
mysql> show tables;
Empty set (0.00 sec)

mysql> create table sexy_test (Name varchar(15), Age int);
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
+----------------+
| Tables_in_sexy |
+----------------+
| sexy_test	  |
+----------------+
1 row in set (0.00 sec)

mysql> describe sexy_test;
+-------+-------------+------+-----+---------+-------+
| Field | Type		| Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| Name  | varchar(15) | YES  |	 | NULL	|	   |
| Age   | int(11)	 | YES  |	 | NULL	|	   |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> create table guests (autoID int unsigned auto_increment primary key, firs
t_name varchar(15), last_name varchar(15), age int, comments text, data_entered
date);
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
+----------------+
| Tables_in_sexy |
+----------------+
| guests		 |
| sexy_test	  |
+----------------+
2 rows in set (0.00 sec)

mysql> describe guests;
+--------------+------------------+------+-----+---------+----------------+
| Field		| Type			 | Null | Key | Default | Extra		  |
+--------------+------------------+------+-----+---------+----------------+
| autoID	   | int(10) unsigned | NO   | PRI | NULL	| auto_increment |
| first_name   | varchar(15)	  | YES  |	 | NULL	|				|
| last_name	| varchar(15)	  | YES  |	 | NULL	|				|
| age		  | int(11)		  | YES  |	 | NULL	|				|
| comments	 | text			 | YES  |	 | NULL	|				|
| data_entered | date			 | YES  |	 | NULL	|				|
+--------------+------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

mysql>

the database>field>table i created can be used as a hit counter, it auto increments automatically. i should post info on connecting to db shortly

more crap:

C:\Program Files (x86)\xampp\mysql\bin>mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with; or \g.
Your MySQL connection id is 4 to server version: 5.0.21-community

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use sexy:
ERROR 1049 (42000): Unknown database 'sexy:'
mysql> use sexy;
Database changed
mysql> describe guests
	-> \c
mysql> describe guests;
+--------------+------------------+------+-----+---------+----------------+
| Field		| Type			 | Null | Key | Default | Extra		  |
+--------------+------------------+------+-----+---------+----------------+
| autoID	   | int(10) unsigned | NO   | PRI | NULL	| auto_increment |
| first_name   | varchar(15)	  | YES  |	 | NULL	|				|
| last_name	| varchar(15)	  | YES  |	 | NULL	|				|
| age		  | int(11)		  | YES  |	 | NULL	|				|
| comments	 | text			 | YES  |	 | NULL	|				|
| data_entered | date			 | YES  |	 | NULL	|				|
+--------------+------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

mysql> insert into guests values (NULL, "Bill", "Gates", 1337, "Is rich", "2006-
08-12");
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM guests;
+--------+------------+-----------+------+----------+--------------+
| autoID | first_name | last_name | age  | comments | data_entered |
+--------+------------+-----------+------+----------+--------------+
|	  1 | Bill	   | Gates	 | 1337 | Is rich  | 2006-08-12   |
+--------+------------+-----------+------+----------+--------------+
1 row in set (0.00 sec)

mysql> insert into guests values (NULL, "Kamil", "Anonim;)", 17, "Is poor", "20
06-08-12);
	">
	"> c\
	"> \c
	"> );
	"> \c
	"> '\c'
	"> insert into guests values (NULL, "Kamil", "Anonim", 17, "Is poor", "06");

	"> /c
	"> \c
	"> clear the ****in buffer
	"> \c
	">

I couldn't clear the buffer

____________________________________

even more:

C:\Program Files (x86)\xampp\mysql\bin>mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with; or \g.
Your MySQL connection id is 6 to server version: 5.0.21-community

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use sexy;
Database changed
mysql> show tables;
+----------------+
| Tables_in_sexy |
+----------------+
| guests		 |
| sexy_test	  |
+----------------+
2 rows in set (0.00 sec)

mysql> describe guests;
+--------------+------------------+------+-----+---------+----------------+
| Field		| Type			 | Null | Key | Default | Extra		  |
+--------------+------------------+------+-----+---------+----------------+
| autoID	   | int(10) unsigned | NO   | PRI | NULL	| auto_increment |
| first_name   | varchar(15)	  | YES  |	 | NULL	|				|
| last_name	| varchar(15)	  | YES  |	 | NULL	|				|
| age		  | int(11)		  | YES  |	 | NULL	|				|
| comments	 | text			 | YES  |	 | NULL	|				|
| data_entered | date			 | YES  |	 | NULL	|				|
+--------------+------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

mysql> insert into guests values (NULL, "Kamil", "Not your business", 17, "Is le
arning sql, mysql not ms's bs", "08-12-06");
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> describe guests;
+--------------+------------------+------+-----+---------+----------------+
| Field		| Type			 | Null | Key | Default | Extra		  |
+--------------+------------------+------+-----+---------+----------------+
| autoID	   | int(10) unsigned | NO   | PRI | NULL	| auto_increment |
| first_name   | varchar(15)	  | YES  |	 | NULL	|				|
| last_name	| varchar(15)	  | YES  |	 | NULL	|				|
| age		  | int(11)		  | YES  |	 | NULL	|				|
| comments	 | text			 | YES  |	 | NULL	|				|
| data_entered | date			 | YES  |	 | NULL	|				|
+--------------+------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

mysql> select * from guests;
+--------+------------+-----------------+------+--------------------------------
----+--------------+
| autoID | first_name | last_name	   | age  | comments
	| data_entered |
+--------+------------+-----------------+------+--------------------------------
----+--------------+
|	  1 | Bill	   | Gates		   | 1337 | Is rich
	| 2006-08-12   |
|	  2 | Kamil	  | Not your busine |   17 | Is learning sql, mysql not ms's
 bs | 2008-12-06   |
+--------+------------+-----------------+------+--------------------------------
----+--------------+
2 rows in set (0.00 sec)

mysql>

after all, it's not that really hard!

Edited August 11, 2006 by Kamil

[ripken204]

but is this here thru the cmd prompt or thru php? i can help you getting it to talk with php, that was a pita to get going my first time. with php can you fill out a form and have the info from it imported into a mysql database.

[nfm]

That was added, removed, updated and deleted through cmd. I haven't really started out with php yet, however, I was able to setup a hit counter and ip tracker using php. php inserted data into mysql's db, so I guess I know a little:

basically this is what I'm doing, this is only for practice, i created db_conf.php with following code:

<?php

$db_host = "localhost";

$db_user = "php";

$db_pass = "foobar";

$db_name = "sexy";

?>

yes I named my db sexy, just for fun

This is what I use to connect to db (first 4 lines, the rest is simple hit counter, note that I created a table name "counter" beforehand in sexy database):

<?php
  require($_SERVER["DOCUMENT_ROOT"] ."/test/config/db_conf.php");
  $connection = @mysql_connect($db_host, $db_user, $db_pass) or die("error connecting");
  mysql_select_db($db_name, $connection);

  $query = "select * from counter";
  $result = mysql_query($query, $connection) or die(mysql_error());
  $views = mysql_result($result, 0, "num_views");
  $views++;

  $query = "update counter set num_views = $views";
  mysql_query($query, $connection) or die(mysql_error());

  echo "This page has been viewed ".$views." times";

?>

ripken can you post any example your html <form>, I'm interested in it

Edited August 12, 2006 by Kamil

[billybob bo buck]

it looks like you might be well on your way, but if you have any questions feel free to contact (email) tehkubix or I, but preferabbly tehkubix, as he knows much more about mysql and php then i could dream of