 PODCAST via Itune
|
 RSS feed
|
To Backup:
## php_mysql: ####
<?
$username="username";
$password="password";
$database="your_database";
$link = mysql_connect(localhost,$username,$password);
if (!$link) {
die('Could not connect: ' . mysql_error());
}
echo 'Connected successfully';
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM contacts";
$result=mysql_query($query);
$num=mysql_numrows($result);
mysql_close();
echo "<b><center>Database Output</center></b><br><br>";
$i=0;
while ($i < $num) {
$first=mysql_result($result,$i,"first");
$last=mysql_result($result,$i,"last");
$phone=mysql_result($result,$i,"phone");
$mobile=mysql_result($result,$i,"mobile");
$fax=mysql_result($result,$i,"fax");
$email=mysql_result($result,$i,"email");
$web=mysql_result($result,$i,"web");
echo "<b>$first $last</b><br>Phone: $phone<br>Mobile: $mobile<br>Fax: $fax<br>E-mail: $email<br>Web: $web<br><hr><br>";
$i++;
}
?>
## Joomla_mysql: ####
## Normally in a helper class ####
<?php
/**
* Helper class for Hello World! module
*
* @package Joomla.Tutorials
* @subpackage Modules
*/
class modHelloWorldHelper
{
function getHello( $userCount ){
//$db = &JFactory::getDBO();
$username="gigibri1_cross";
$password="zhengxin";
$database="gigibri1_dev";
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
// get a list of all users
$query = 'SELECT * FROM jos_users';
//$db->setQuery($query);
$result = mysql_query($query);
$items = ($items = $db->loadObjectList())?$items:array();
// create a new array and fill it up with random users
$actualCount = count($items);
if ($actualCount < $userCount) {
$userCount = $actualCount;
}
$items2 = array();
$rands = array_rand($items, $userCount);
foreach ($rands as $rand) {
$items2[] = $items[$rand];
}
return $items2;
mysql_close();
return $result;
}
}
## Drupal_mysql: ####
## Normally in a module file ####
$result_sell_price = db_fetch_object(db_query('SELECT i.sell_price FROM {image} i WHERE i.vid = %d', $node->vid));
$node->sell_price=$result_sell_price->sell_price;
$result = db_query("SELECT i.image_size, f.filepath FROM {image} i INNER JOIN {files} f ON i.fid = f.fid WHERE i.nid = %d", $node->nid);
$node->images = array();
while ($file = db_fetch_object($result)) {
$node->images[$file->image_size] = file_create_path($file->filepath);
}
准备：更改root的密码 （mysql安装后，root是没有密码的）
@>mysqladmin -uroot password YOURNEWPASSWORD
1.新建用户。
//登录MYSQL
@>mysql -u root -p
@>密码
//创建用户
mysql> insert into mysql.user(Host,User,Password) values("localhost","cross",password("1234"));
//刷新系统权限表
mysql>flush privileges;
这样就创建了一个名为：cross 密码为：1234 的用户。
然后登录一下。
mysql>exit;
@>mysql -u cross -p
@>输入密码
mysql>登录成功
2.为用户授权。
//登录MYSQL（有ROOT权限）。我里我以ROOT身份登录.
@>mysql -u root -p
@>密码
//首先为用户创建一个数据库(joomla1)
mysql>create database joomla1;
//授权phplamp用户拥有phplamp数据库的所有权限。
>grant all privileges on joomla1.* to 'cross'@localhost identified by '1234';
//刷新系统权限表
mysql>flush privileges;
mysql>其它操作
/*
如果想指定部分权限给一用户，可以这样来写:
mysql>grant select,update on phplampDB.* to 'phplamp'@localhost identified by '1234';
//刷新系统权限表。
mysql>flush privileges;
*/
3.删除用户。
@>mysql -u root -p
@>密码
mysql>DELETE FROM user WHERE User="phplamp" and Host="localhost";
mysql>flush privileges;
//删除用户的数据库
mysql>drop database phplampDB;
4.修改指定用户密码。
@>mysql -u root -p
@>密码
mysql>update mysql.user set password=password('新密码') where User="phplamp" and Host="localhost";
mysql>flush privileges;
How is a LEFT JOIN different from a normal join? First of all, the syntax is quite different and somewhat more complex. Besides looking different, the LEFT JOIN gives extra consideration to the table that is on the left.
Being "on the left" simply refers to the table that appears before the LEFT JOIN in our SQL statement. Nothing tricky about that.
This extra consideration to the left table can be thought of as special kind of preservation. Each item in the left table will show up in a MySQL result, even if there isn't a match with the other table that it is being joined to.
Here are the tables we used in the previous Mysql Joins lesson.
| Position | Age |
|---|---|
| Dad | 41 |
| Mom | 45 |
| Daughter | 17 |
| Dog |
| Meal | Position |
|---|---|
| Steak | Dad |
| Salad | Mom |
| Spinach Soup | |
| Tacos | Dad |
We executed a simple query that selected all meals that were liked by a family member with this simple join query:
SELECT food.Meal, family.Position
FROM family, food
WHERE food.Position = family.Position
When we decide to use a LEFT JOIN in the query instead, all the family members be listed, even if they do not have a favorite dish in our food table.
This is because a left join will preserve the records of the "left" table.
The code below is the exact same as the code in the previous lesson, except the LEFT JOIN has now been added to the query. Let's see if the results are what we expected.
// Make a MySQL Connection
// Construct our join query
$query = "SELECT family.Position, food.Meal "."FROM family LEFT JOIN food ".
"ON family.Position = food.Position";
$result = mysql_query($query) or die(mysql_error());
// Print out the contents of each row into a table
while($row = mysql_fetch_array($result)){
echo $row['Position']. " - ". $row['Meal'];
echo "
";
}
?>
Success! The LEFT JOIN preserved every family member, including those who don't yet have a favorite meal in the food table! Please feel free to play around with LEFT JOIN until you feel like you have a solid grasp of it. This stuff isn't easy!
Another example for 'JOIN', 'LEFT JOIN' and 'RIGHT JOIN' is here:
http://www.wellho.net/mouth/158_MySQL-LEFT-JOIN-and-RIGHT-JOIN-INNER-JOIN-and-OUTER-JOIN.html
Selecting a database:
mysql> USE database;
Listing databases:
mysql> SHOW DATABASES;
Listing tables in a db:
mysql> SHOW TABLES;
Describing the format of a table:
mysql> DESCRIBE table;
Creating a database:
mysql> CREATE DATABASE db_name;
Creating a table:
mysql> CREATE TABLE table_name (field1_name TYPE(SIZE), field2_name TYPE(SIZE));
Ex: mysql> CREATE TABLE pet (name VARCHAR(20), sex CHAR(1), birth DATE);
Load tab-delimited data into a table:
mysql> LOAD DATA LOCAL INFILE "infile.txt" INTO TABLE table_name;
(Use \n for NULL)
Inserting one row at a time:
mysql> INSERT INTO table_name VALUES ('MyName', 'MyOwner', '2002-08-31');
(Use NULL for NULL)
Retrieving information (general):
mysql> SELECT from_columns FROM table WHERE conditions;
All values: SELECT * FROM table;
Some values: SELECT * FROM table WHERE rec_name = "value";
Multiple critera: SELECT * FROM TABLE WHERE rec1 = "value1" AND rec2 = "value2";
Reloading a new data set into existing table:
mysql> SET AUTOCOMMIT=1; # used for quick recreation of table
mysql> DELETE FROM pet;
mysql> LOAD DATA LOCAL INFILE "infile.txt" INTO TABLE table;
Fixing all records with a certain value:
mysql> UPDATE table SET column_name = "new_value" WHERE record_name = "value";
Selecting specific columns:
mysql> SELECT column_name FROM table;
Retrieving unique output records:
mysql> SELECT DISTINCT column_name FROM table;
Sorting:
mysql> SELECT col1, col2 FROM table ORDER BY col2;
Backwards: SELECT col1, col2 FROM table ORDER BY col2 DESC;
Date calculations:
mysql> SELECT CURRENT_DATE, (YEAR(CURRENT_DATE)-YEAR(date_col)) AS time_diff [FROM table];
MONTH(some_date) extracts the month value and DAYOFMONTH() extracts day.
Pattern Matching:
mysql> SELECT * FROM table WHERE rec LIKE "blah%";
(% is wildcard - arbitrary # of chars)
Find 5-char values: SELECT * FROM table WHERE rec like "_____";
(_ is any single character)
Extended Regular Expression Matching:
mysql> SELECT * FROM table WHERE rec RLIKE "^b$";
(. for char, [...] for char class, * for 0 or more instances
^ for beginning, {n} for repeat n times, and $ for end)
(RLIKE or REGEXP)
To force case-sensitivity, use "REGEXP BINARY"
Counting Rows:
mysql> SELECT COUNT(*) FROM table;
Grouping with Counting:
mysql> SELECT owner, COUNT(*) FROM table GROUP BY owner;
(GROUP BY groups together all records for each 'owner')
Selecting from multiple tables:
(Example)
mysql> SELECT pet.name, comment FROM pet, event WHERE pet.name = event.name;
(You can join a table to itself to compare by using 'AS')
Currently selected database:
mysql> SELECT DATABASE();
Maximum value:
mysql> SELECT MAX(col_name) AS label FROM table;
Auto-incrementing rows:
mysql> CREATE TABLE table (number INT NOT NULL AUTO_INCREMENT, name CHAR(10) NOT NULL);
mysql> INSERT INTO table (name) VALUES ("tom"),("dick"),("harry");
Adding a column to an already-created table:
mysql> ALTER TABLE tbl ADD COLUMN [column_create syntax] AFTER col_name;
Removing a column:
mysql> ALTER TABLE tbl DROP COLUMN col;
(Full ALTER TABLE syntax available at mysql.com.)
Batch mode (feeding in a script):
# mysql -u user -p <> source batch_file;
Backing up a database with mysqldump:
# mysqldump --opt -u username -p database > database_backup.sql
(Use 'mysqldump --opt --all-databases > all_backup.sql' to backup everything.)
(More info at MySQL's docs.)
Join tables:
# SELECT * FROM cars JOIN colors ON colors.car_ID=cars.id
# SELECT * FROM cars AS cr JOIN colors AS cl ON cl.car_ID=cr.id
After the ON, we state on wich columns the tables should join. Every color has a reference to a car by the "car_ID" column. Every car has an
ID, and these two columns are the link between the two tables.
1.java-sdk
2.tomcat
3.mysql
4.mysql driver for jdbc: just copy the mysql*.bin.jar to /tomcat/lib 即可。
以防万一，有些程序特殊要求，在1，2，3所有lib文件夹里都放一个，jre/lib/ext/和jsp所在folder里的WEB-INF/lib/也放一个。
