ubuntu 配置 mysql

GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'%' IDENTIFIED BY 'mypassword' WITH GRANT OPTION

sudo ufw allow 3306/tcp
sudo service ufw restart
bind-address 127.0.0.1    
sudo /etc/init.d/mysql restart

mysql两种计算坐标距离的方法

SET @lat = 33;
SET @lon = -98;
SELECT count(*) FROM pecker_db.d_zip_lookup
where MBRContains(
              LineString
                        (
                        Point
                                 (
                                 @lon + 100 / ( 111.1 / COS(RADIANS(@lat))),
                                    @lat + 100 / 111.1
                                 ),
                        Point    (
                                 @lon - 100 / ( 111.1 / COS(RADIANS(@lat))),
                                    @lat - 100 / 111.1
                                 )
                        ),
                   my_point)

使用mbrcontains方法，需要自己my_point blob位置属性
实际上是一个矩形，较慢


SET @lat = 33;
SET @lon = -98;  
SELECT (6371 * acos( cos( radians(lat_loc) ) * cos( radians( @lat ) ) * cos( radians( @lon ) - radians(lng_loc) ) + sin( radians(lat_loc) ) * sin( radians( @lat ) ) ) ) as distance
FROM pecker_db.d_zip_lookup
 where lat_loc between @lat-2 and @lat+2
and lng_loc between @lon-2 and @lon+2
having distance<100;
速度快，关键的地方在于划定区域来计算

参考http://geography.about.com/library/faq/blqzdistancedegree.htm

php 连接 mysql以及嵌入html代码

";
  }

mysql_close($conn);


//以下是php内嵌html代码
$ar = array('demo' => '111', 'test' => '222');
$html = <<
        

        

    
HTML;
 
echo $html;

mysql 常见操作 --经常更新

 CREATE USER 'hr'@'localhost' IDENTIFIED BY '1234';

 GRANT ALL PRIVILEGES ON company.* TO 'hr'@'localhost';

CREATE TABLE user_table(
id INT NOT NULL AUTO_INCREMENT,
user VARCHAR(20) NOT NULL,
password VARCHAR(30) NOT NULL,
PRIMARY KEY (id )
);

insert into user_table (user, password) values("guest","123456");

alter table location add my_point point;// location with 经纬坐标

update location SET my_point = PointFromText(CONCAT('POINT(',long,' ',latt,')'));

将会自动计算，以blob形式存储

mysql的一些有用操作

source d:/create-company-data-for-MySQL.sql       // 导入sql源文件

下载安装
http://dev.mysql.com/downloads/connector/

打开Eclipse，创建一个项目，

操作：右键点击--->build Path--->add external Archiver...选择jdbc驱动，点击确定。

新建java文件测试

import java.sql.*;

public class DBTest {
    static Connection conn = null;
      
        public static void main(String[] args) {

            // Initialize variables for fields by data type
            String ssn;
            String firstName;
            String lastName;
            String address;
            int dno;

            int linect = 0;

        try {
            // Create a connection to the local MySQL server, with the "company" database selected.
            //        conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/company", "root", "mypassword");
            // Create a connection to the local MySQL server, with the NO database selected.
                    conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/", "root", "");
    
            // Create a SQL statement object and execute the query.
            Statement stmt = conn.createStatement();
        
            // Set the current database, if not already set in the getConnection
            // Execute a SQL statement
//   String chooseDatabase = "use company;"
            stmt.execute("use company;");

            // Execute a SQL query using SQL as a String object
            ResultSet rs = stmt.executeQuery("SELECT ssn, fname, lname, dno FROM employee;");

            // Iterate through the result set using ResultSet class's next() method
            while (rs.next()) {
                // Keep track of the line/tuple count
                linect++;
                // Populate field variables
                
                firstName = rs.getString("fname");
                lastName = rs.getString("lname");
//                dno = rs.getInt("dno");
    ssn = rs.getString("ssn");
    dno = Integer.parseInt(rs.getString("dno"));
            
                // Do something with the data
                System.out.print(linect + ".\t");
                System.out.print(ssn + "\t");
                System.out.print(firstName + "\t");
                System.out.print(lastName + "\t");
                System.out.print(dno + "\t");
                System.out.println();
            } // End while(rs.next())

            // Always close the recordset and connection.
            rs.close();
            conn.close();
            System.out.println("Success!!");
        } 
        catch(SQLException ex) {
            System.out.println("Error in connection: " + ex.getMessage());
        }
    }

   
    }
}

简单jsp教程--使用jdbc连接数据库

备注：jdbc是用来连接tomcat和mysql的，需要下载connector

1、首先在MySQL数据库中建立books数据库，再建book表。
然后插入数据。SQL语言如下：

create database books;

use books;
create table book(bookId varchar(50),bookName varchar(50),publisher varchar(100),price float,constraint pk_book primary key(bookId))
         
insert into book values('1001','An inconvenient truth','电子工业出版社',45.00);
insert into book values('1002','The satanic gases','电子工业出版社',49.00);
insert into book values('1003','Global climate change and wildlife','电子工业出版社',59.00);
insert into book values('1004','Environmental resource handbook','电子工业出版社',59.00);
insert into book values('1005','American heritage science dictionary','电子工业出版社',56.00);


2.新建sql.jsp
<%@ page contentType="text/html; charset=gb2312" language="java" import="java.sql.*"%>
<html>
<body>
以下是从MySQL数据库读取的数据：<hr>
<table border=0>
<tr><td>ID</td><td>书名</td><td>出版社</td><td>价格</td></tr>

<%
   Class.forName("com.mysql.jdbc.Driver").newInstance();
   Connection con=java.sql.DriverManager.getConnection("jdbc:mysql://192.168.1.8/books?

useUnicode=true&characterEncoding=GBK","root","1234");
   Statement stmt=con.createStatement();
    ResultSet rst=stmt.executeQuery("select * from book");
    while(rst.next())
    {
        out.println("<tr>");
        out.println("<td>"+rst.getString("bookId")+"</td>");
        out.println("<td>"+rst.getString("bookName")+"</td>");
        out.println("<td>"+rst.getString("publisher")+"</td>");
        out.println("<td>"+rst.getFloat("price")+"</td>");
        out.println("</tr>");
    }
    //关闭连接、释放资源
    rst.close();
    stmt.close();
    con.close();
    %>
</table>
</body>
</html>

we can get

以下是从MySQL数据库读取的数据：

---

ID	书名	出版社	价格
1001	An inconvenient truth	Bogdonoff & Rubin	45.0
1002	The satanic gases	Rodale	49.0
1003	Global climate change and wildlife	Cato Institute	59.0
1004	Environmental resource handbook	Wildlife Society	59.0
1005	American heritage science dictionary	Houghton Mifflin Book	56.0