MySQL Php-mysql shell-mysql

net stop MySQL

net start MySQL

Mysql-uroot-p--tee=c:\log.log

show databases;

Create Database dbname;

Drop Database dbname;

show databases;

Use dbname

Show tables

FRM table structure, myd table data, myi table index

CREATE TABLE User (id int,name varchar (), password varchar (30));

DESC user;

SELECT * FROM user

Insert into User (Id,name,password) VALUES (1, "Jack", "pwd");

Delete from user where id=2

Update user set Name= ' Jay ' where id=1

Rename Table tbname1 to Tbname2

ALTER TABLE Tbname Add pass varchar (+) NOT null default "password"//Add Field

ALTER TABLE tbname modify age int not null default 20;

ALTER TABLE TBNAME change name newname varchar (30);

ALTER TABLE tbname drop age;

drop table Tbname if exists tbname

----------------------------------------------------------------------------Design

1. Numeric int

CREATE TABLE tname (id int);

Desc Tname

type int (11) signed

CREATE TABLE tname (id int unsigned zerofill);

Desc Tname

type int (10) unsigned

Int (3) is independent of length, 3 bits is not enough, front 0 is not displayed by default

Auto_increment

NOT NULL

Default

Primary key

2. String

char (n)//MAX 255

VARCHAR (n)

Text//65535 bytes

Longtext//4.2 billion bytes

3. Time

Try to save with timestamp integer

Strange null value

\s//view MySQL basic information

View Database character encoding

Show CREATE Database dbname

View Table Character Set

Show CREATE TABLE Tbname

//

Set Names UTF8

DESC SELECT * from user where name= "Jack" \g//View Run parameters

Primary key Index

Primary key

Normal index

Index in_named

? Show//? Linux-like Man

Show index from T2; View all indexes in a table

Add a normal index

ALTER TABLE Tbname Add index in_name (name)

Delete Normal index

ALTER TABLE T2 DROP INDEX In_named

DDL//Data definition language Create drop alter

DML//Data Manipulation Language Insert Update delete

DQL//Data Query Language Select

DCL//Data Control Language Grant commit rollback

-------------------------------------------------------------

Insert into Tbname (name) VALUES ("Jack");

Update tbname set name= "Wood" where id=1

Delete from tbname where id=2;

Delete from Tbname where ID in (1,3.5);

Delete from Tbname where id=1 or id=3 or id=5

Delete from Tbname where id>=3 and id<=5;

Delete from Tbname where ID between 3 and 5;

weiphp, Cakewx, Lanewechat,we7

-----------------------------------------------------------------

Insert into table inserts more than one data

Method 1:

Insert INTO ' TTT '

Select ' 001 ', ' Language ' union ALL

Select ' 002 ', ' Math ' union ALL

Select ' 003 ', ' English ';

Method 2:

INSERT into Tab_comp VALUES (item1, Price1, Qty1),

(Item2, Price2, Qty2),

(Item3, Price3, qty3);

Method 3:

INSERT into Tab_comp (item1, Price1, qty1) SELECT item1, Price1, qty1 from TAB_CC;

------------------------------------------------------------------

Inquire

Alias query

Select Username name from user

Select Username as name from user

Distinct

Select distinct age from user

//

Null value NULL note is null/is NOT NULL cannot be used =

SELECT * from user where addr is null

Like note%

Regexp

SELECT * from the user where name like '%s% ';

SELECT * from user where name RegExp '. *4.* ';

SELECT * from the user where name like '%4% ' or the name like '%5% ';

SELECT * from user where name RegExp ' (. *4.*) | (. *5.*) ';

ORDER by ASC (ascending default)/desc (Descending)

SELECT * from the user order by ID

SELECT * from the user order by ID ASC

SELECT * from the user order by id DESC

Limit

SELECT * from the user order by id DESC limit 1;//take 1

SELECT * from the user order by id desc-Limit 0,3;//from No. 0 of 3

Concat () connector

Select Concat ("A", "B");//Output AB

Select Id,name,pass,concat (ID, '-', name) from user;

RAND ()

SELECT * from the user order by rand () limit 3;//randomly out of 3

Count ()

SUM ()

AVG ()

Max ()

Min ()

Select COUNT (ID) num from user;//statistics ID number, num alias

Select COUNT (*) num from user;//count, note: official Recommended use

//

Select SUM (ID) num from user;//sum

Select AVG (ID) num from user;//

GROUP by has order by grouping cannot be used where, only with having

Select Name, count (ID) from mess group by name; //

Select Shopid, COUNT (ID) num from the ' p_purchase_order_his ' GROUP by shopid the ORDER by num DESC;

Select Name, count (ID) tot from mess group by name has tot>=80 order by tot desc; GROUP by must precede order by

After

ALTER TABLE Tbname add store int after ID;

Multi-Table Query

General query-multi-table

SELECT * from Tb1 TB2;//tb1 's entire record plus TB2 records.

SELECT * from Tb1 TB2 where tb1.id=tb2.oid

Left join (Put the full display to the left)

SELECT * from tb1 LEFT join TB2 on tb1.id=tb2.oid

Right join (put to the right to be fully displayed)

subquery (nested query): Normally not used

Select name from the user where ID in (select UID from mess);

-------------------------------------------------

PHP MySQL

Header ("Content-type:text/html;charset=utf-8");

[Email Protected]_connect ("localhost", "root", "root");

if (!conn) {

Echo Mysql_errno (). <br/>.mysql_error ();

}

mysql_select_db ("test");

mysql_query ("Set names UTF8");

Mysql>create table t1 (id int unsigned auto_increment primary key,username varchar (+), password varchar (32));

Insert

$username = "Allen";

$password = "PWD";

$sql = "INSERT into T1 (Username,password) VALUES (' {$username} ', ' {$password} ');//Note single quotes

$sql = "INSERT into T1 (Username,password) VALUES (' $username ', ' $password ')";//Note single quotes

Echo $sql;

mysql_query ($sql);

if (mysql_query ($sql)) {

Echo mysql_insert_id ();

}

Update

$sql = "Update t1 set username= ' user1 ' where id=2";

mysql_query ($sql);

Delete

$sql = "Delete from T1 where id=2";

mysql_query ($sql);

Select result set

$sql = "Select username,addr from T1 order by id";

$res =mysql_query ($res);//MySQL Resource

MYSQL_FETCH_ASSOC Associative arrays

Mysql_fetch_row indexed arrays

Mysql_fetch_array Mixed arrays

Mysql_fetch_object Object

$row =mysql_fetch_assoc ($res);

$row =mysql_fetch_row ($res);

$row =mysql_fetch_array ($res);

$row =mysql_fetch_object ($res);

//

while ($row =mysql_fetch_assoc ($res)) {

echo "<pre>";

Print_r ($row);

echo "</pre>";

}

------------------------------------------

Fetching column Information

while ($f =mysql_fetch_field ($res)) {

echo "<pre>";

Print_r ($f->name);

echo "</pre>";

}

Fetching column Information

$sql = "desc T1";

$res =mysql_query ($sql);

while ($row =mysql_fetch_assoc ($res)) {

echo "<pre>";

Print_r ($row [' Field ']);

echo "</pre>";

}

-----------------------------------------------------

MYSQL_INSERT_ID ()//Get the ID generated by the insert operation in the previous step

Mysql_affected_rows ();//The number of rows affected by increasing, deleting, and changing operations

Mysql_num_rows ()//Gets the number of rows in the query result set

Select COUNT (*) from tbname;//fastest fetched table row count

Mysql_close ($conn);

---------------------------------------------------------------------------------------------

Shell MySQL

chmod a+x xxx.sh #给xxx. SH Execute permissions

Shell Script

----

#! /bin/bash

#mysql. Sh

Mysql= "/usr/local/mysql/bin/mysql-uroot-p123"

#sql = "Show Databases"

#sql = "CREATE TABLE test.user (ID int unsigned,name varchar (+), password varchar (32))"

#sql = "desc test.user"

#sql = "INSERT into Test.user (Username,password) VALUES (' Jack ', ' pwd ')"

$mysql-E "$sql"

-----

-----

#! /bin/bash

#mysql. Sh

Conn= "/usr/local/mysql/bin/mysql-uroot-p123"

#sql = "CREATE Database Test"

#sql = "CREATE TABLE test.user (id int unsigned auto_increment primary,name varchar (+), password Varcher (32))"

#sql = "INSERT into Test.user (Username,password) VALUES (' Jack ', ' pwd ')"

#传参

Case $ in

Delete

Sql= "Delete from Test.user where id=$2"

;;

Insert

Sql= "INSERT into Test.user (Username,password) VALUES (' $ ', ' $ $ ')"

;;

Update

sql= "Update test.user set Username= ' $ $ ', password= ' $4 ' where id=$2 '

;;

Select | *)

Sql= "SELECT * from Test.user ORDER by id"

;;

Esac

$conn-E "$sql"

---------------------------------------

Shell Apache

Apache Log Split

Rotatelogs,cronolog

#! /bin/bash

#logcut. Sh

Yesterday= ' date-d yesterday +%y%m%d '

Srclog= "/usr/local/apache2/logs/access_log"

Dstlog= "/usr/local/apache2/logsbak/access_${yesterday}"

MV $srclog $dstlog

PKILL-1 httpd

------------------------

Cat Access_xxxxxxxx.log|awk ' {print '} ' |sort|uniq-c

--------------------------

#! /bin/bash

#mysql. Sh

Conn= "/usr/local/mysql/bin/mysql-uroot-p123"

#sql = "CREATE TABLE test.countab (id int unsigned auto_increment primary key,data varchar (), IP varchar (+), num int)"

#sql = "desc test.countab"

$conn-E "$sql"

-------

#! /bin.bash

Yesterday= ' date-d yesterday +%y%m%d '

Srclog= "/usr/local/apache2/logs/access_log"

Dstlog= "/usr/local/apache2/logsbak/access_${yesterday}"

MV $srclog $dstlog

PKILL-1 httpd

Tmpfile=$$.txt # $$ What the hell?

Cat Access_xxxxxxxx.log|awk ' {print $} ' |sort|uniq-c|awk ' {print $ ': ' $ $ ' > $tmpfile

Mysql= "/usr/local/mysql/bin/mysql-uroot-p123"

For i in ' Cat $tmpfile '

Do

Ip= ' echo $i |awk-f: ' {print '} '

Num= ' echo $i |awk-f: ' {print $} '

Sql= "INSERT into Test.countab (data,ip,num) VALUES (' $yesterday ', ' $ip ', ' $num ')"

Done

RM-RF $tmpfile

Sql= "SELECT * from Test.countab ORDER by id"

$mysql-E "$sql"

-------------------

Crontab-e

#每晚0点执行

XX * * * */mnt/xxx.sh

---------------------

MySQL Php-mysql shell-mysql