mysql höpölöpö

 MySQL
 
 echo Start the mysql as the root user using SecretPass password
 
 echo Note that the clear command does not work from the shell so use the Ctrl + l shortcut to clear the messy screen
 [mysql dir]/bin/mysql -h hostname -u root -p SecretPass
 
 echo show the existing databases on this host (show present something like this )
 
 show databases ;
 
 mysql> show databases ;
 +--------------------+
 | Database |
 +--------------------+
 | information_schema |
 | mysql |
 +--------------------+
 5 rows in set (0.00 sec)
 
  
 
 echo running mysql in batch mode
 
 shell> mysql -h host -u user -pSecretPass  < /path/to/batch-file
 
 ou can catch the output in a file for further processing:
 shell> mysql < batch-file > mysql.out
 
  
 
 DROP DATABASE  `db_transpub`  ;
 CREATE DATABASE `db_transpub` DEFAULT CHARACTER SET utf8 ;
 use db_transpub ;
 DROP TABLE IF EXISTS `tb_contactDetails`;
 CREATE TABLE `tb_contactDetails` (firstname VARCHAR(20), middlename VARCHAR(20), lastname VARCHAR(35),officeid VARCHAR(10),username VARCHAR(8),email VARCHAR(35),phone VARCHAR(25), groups
 VARCHAR(15),datestamp DATE,timestamp time, userId VARCHAR(15), PRIMARY KEY  (`userId`));
 
  
 miscelanious
 echo how to get the version of mysql select version()   phpMyAdmin provides php interface to mysql database(s)
 MySQL Cheat Sheet
 
 http://dev.mysql.com/doc/
 
 search the documentation
 
 To login (from unix shell) use -h only if needed.
 
 [mysql dir]/bin/mysql -h hostname -u root -p
 Create a database on the sql server.
 
 create database [databasename];
 List all databases on the sql server.
 
 show databases;
 Switch to a database.
 
 use [db name];
 To see all the tables in the db.
 
 show tables;
 To see database field formats.
 
 describe [table name];
 To delete a db.
 
 drop database [database name];
 To delete a table.
 
 drop table [table name];
 Show all data in a table.
 
 SELECT * FROM [table name];
 Returns the columns and column information pertaining to the designated table.
 
 show columns from [table name];
 Show certain selected rows with the value "whatever".
 
 SELECT * FROM [table name] WHERE [field name] = "whatever";
 Show all records containing the name "Bob" AND the phone number '3444444'.
 
 SELECT * FROM [table name] WHERE name = "Bob" AND phone_number = '3444444';
 Show all records not containing the name "Bob" AND the phone number '3444444' order by the phone_number field.
 
 SELECT * FROM [table name] WHERE name != "Bob" AND phone_number = '3444444' order by phone_number;
 Show all records starting with the letters 'bob' AND the phone number '3444444'.
 
 SELECT * FROM [table name] WHERE name like "Bob%" AND phone_number = '3444444';
 Use a regular expression to find records. Use "REGEXP BINARY" to force case-sensitivity. This finds any record beginning with a.
 
 SELECT * FROM [table name] WHERE rec RLIKE "^a$";
 Show unique records.
 
 SELECT DISTINCT [column name] FROM [table name];
 Show selected records sorted in an ascending (asc) or descending (desc).
 
 SELECT [col1],[col2] FROM [table name] ORDER BY [col2] DESC;
 Return number of rows.
 
 SELECT COUNT(*) FROM [table name];
 Sum column.
 
 SELECT SUM(*) FROM [table name];
 Join tables on common columns.
 
 select lookup.illustrationid, lookup.personid,person.birthday from lookup
 left join person on lookup.personid=person.personid=statement to join birthday in person table with primary illustration id;
 Switch to the mysql db. Create a new user.
 
 INSERT INTO [table name] (Host,User,Password) VALUES('%','user',PASSWORD('password'));
 Change a users password.(from unix shell).
 
 [mysql dir]/bin/mysqladmin -u root -h hostname.blah.org -p password 'new-password'
 Change a users password.(from MySQL prompt).
 
 SET PASSWORD FOR 'user'@'hostname' = PASSWORD('passwordhere');
 Allow the user "bob" to connect to the server from localhost using the password "passwd"
 
 grant usage on *.* to bob@localhost identified by 'passwd';
 Switch to mysql db.Give user privilages for a db.
 
 INSERT INTO [table name] (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES ('%','databasename','username','Y','Y','Y','Y','Y','N');
 
 or
 
 grant all privileges on databasename.* to username@localhost;
 To update info already in a table.
 
 UPDATE [table name] SET Select_priv = 'Y',Insert_priv = 'Y',Update_priv = 'Y' where [field name] = 'user';
 Delete a row(s) from a table.
 
 DELETE from [table name] where [field name] = 'whatever';
 Update database permissions/privilages.
 
 FLUSH PRIVILEGES;
 Delete a column.
 
 alter table [table name] drop column [column name];
 Add a new column to db.
 
 alter table [table name] add column [new column name] varchar (20);
 Change column name.
 
 alter table [table name] change [old column name] [new column name] varchar (50);
 Make a unique column so you get no dupes.
 
 alter table [table name] add unique ([column name]);
 Make a column bigger.
 
 alter table [table name] modify [column name] VARCHAR(3);
 Delete unique from table.
 
 alter table [table name] drop index [colmn name];
 Load a CSV file into a table.
 
 LOAD DATA INFILE '/tmp/filename.csv' replace INTO TABLE [table name] FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (field1,field2,field3);
 Dump all databases for backup. Backup file is sql commands to recreate all db's.
 
 [mysql dir]/bin/mysqldump -u root -ppassword --opt >/tmp/alldatabases.sql
 Dump one database for backup.
 
 [mysql dir]/bin/mysqldump -u username -ppassword --databases databasename >/tmp/databasename.sql
 Dump a table from a database.
 
 [mysql dir]/bin/mysqldump -c -u username -ppassword databasename tablename > /tmp/databasename.tablename.sql
 Restore database (or database table) from backup.
 
 [mysql dir]/bin/mysql -u username -ppassword databasename < /tmp/databasename.sql
 Create Table Example 1.
 
 CREATE TABLE [table name] (firstname VARCHAR(20), middleinitial VARCHAR(3), lastname VARCHAR(35),suffix VARCHAR(3),officeid VARCHAR(10),userid VARCHAR(15),username VARCHAR(8),email VARCHAR(35),phone VARCHAR(25), groups
 VARCHAR(15),datestamp DATE,timestamp time,pgpemail VARCHAR(255));
 Create Table Example 2.
 
 create table [table name] (personid int(50) not null auto_increment primary key,firstname varchar(35),middlename varchar(50),lastnamevarchar(50) default 'bato');
 
 ;who has what priviledges
 
 show grants ;
 
  
 simple setup of php based web interface to a mysql database
 
 This tutorial uses Ubuntu 7.10 the Gutsy Gibbon, Apache 2.0 , php5 and mysql
 
 The follwoing main phpases are used:
 
  
 
 - install Apache
 
 - install mysql
 
 - configure mysql - add users , create databases on mysql
 
 - configure Apache
 
 - install myPhpAdmin
 
 - configure myPhpAdmin
 
  
 
 1.Install Apache
 
 #INSTALL APACHE
 
 sudo apt-get install apache2
 
 #Start or stop Apache
 sudo /etc/init.d/apache2 stop | start | restart
 #if installation of previous packages has failed
 dpkg --configure -a
 
 #install PHP mod_php for Apache
 sudo apt-get install php5 libapache2-mod-php5
 
 echo '<?php echo "Haaudi World"; ?>' > /var/www/test.php
 open http://localhost/test.php - > you should see Haudi World
 
 2. Install mySQL
 
 #if installation of previous packages has failed
 
 dpkg --configure -a
 sudo /etc/init.d/apache2 stop | start | restart
 
 #install mysql server
 sudo apt-get install mysql-server
 #when prompted type in your password
 
 #if not prompted the command to get to the mysql prompt is :
 
 mysql -uroot -pmysql
 
 #set the password with the following:
 mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('SecretPass');
 #run the mysql with:
 mysql -uroot -pSecretPass
 
 #view the databases
 
 show databases ;
 
 #create a database with one table
 
 create database testdb ;
 
 use db_test
 
 CREATE TABLE tb_testtable(
 column0 INT PRIMARY KEY AUTO_INCREMENT,
 column1 VARCHAR(100),
 column2 VARCHAR(10) ) TYPE=INNODB;
 
 3. Intall php
 
 #install the php admin
 sudo apt-get install libapache2-mod-auth-mysql php5-mysql
 #when prompted configure the type of servers
 
 #Other php modules usefull for development
 apt-get install php5-gd
 apt-get install php5-ldap
 apt-get install php5-mysql
 apt-get install php5-openssl
 apt-get install php5-pear
 
 4. Install phpMyAdmin
 
 Download the latest stable build of phpMyAdmin [[http://surfnet.dl.sourceforge.net/sourceforge/phpmyadmin/phpMyAdmin-2.11.3-all-languages.tar.bz2]]
 
 #place the tar under a phpMyAdmin folder (you would have to create it mkdir /var/www/phpMyAdmin ) under your doc root folder
 
 mv /home/user/downloads/phpMyAdmin-2.11.3-all-languages/ /var/www/phpMyAdmin
 
 tar -xvf phpMyAdmin-2.11.3-all-languages.tar.bz2
 
 #configure the most sipliest (not sucure !!!) setup
 
 vim /var/www/phpMyAdmin/config.inc.php
 
 paste the following code in it:
 
 ==============================COPY PASTE START
 
 <?php
 
 $cfg['blowfish_secret']='shazaam' ; // use here a value of your choice
 $i=0;
 $i++;
 $cfg['Servers'][$i]['auth_type'] = 'cookie';
 ?>
 
 ==============================COPY PASTE END
 
 Go to the login page :
 
 http://localhost/phpMyAdmin/
 
 Login in with the same password you set up during the mysql configuration(here user:root pass:SecretPass)
 
  

---

syntax highlighted by Code2HTML, v. 0.9.1