代码（2）

索引

索引的创建

1.使用CREATE INDEX语句创建索引

CREATE INDEX index_customers

ON mysql_test.customers(cust_name(3) ASC);

 

CREATE INDEX index_cust

ON mysql_test.customers(cust_name,cust_id)

USING BTREE;

如不指定，默认BTREE

2.使用CREATE TABLE语句创建索引

CREATE TABLE seller(

seller_id int NOT NULL AUTO_INCREAMENT,

seller_name char(50) NOT NULL,

seller_address char(50) NULL,

seller_contact char(50) NULL,

product_type int(5) NULL,

sales int NULL,

PRIMARY KEY(seller_id,product_type),

INDEX index_seller(sales)

);

3.使用ALTER TABLE语句创建索引

ALTER TABLE mysql_test.seller

ADD INDEX index_seller_name(seller_name);

索引的删除

1.使用DROP INDEX语句删除索引

DROP INDEX index_cust ON mysql_test.customers;

2.使用ALTER TABLE删除索引

ALTER TABLE mysql_test.customers

DROP PRIMARY KEY,

DROP INDEX index_customers;

 

 

视图

创建视图

CREATE OR REPLACE VIEW mysql_test.customers.view

AS

SELECT * FROM mysql_test.customers

WHERE cust_sex='M'

WITH CHECK OPTION;

注意WITH CHECK OPTION用于指定在可更新视图上所进行的修改都要符合SELECT

中所指定的限制条件。

删除视图与数据库、表差不多

修改视图

查看视图定义

更新视图数据

查询视图数据

 

 

 

 

数据完整性约束与表维护语句

CREATE TABLE customers(

cust_id int NOT NULL AUTO_INCREMENT PRIMARY KEY,

cust_name char(50) NOT NULL,

cust_sex int(1) NOT NULL DEFAULT 0,

cust_address char(50) NULL,

cust_contact char(50) NULL

)ENGINE=InnoDB;

 

 

CREATE TABLE orders(

order_id INT NOT NULL AUTO_INCREMENT,

order_product CHAR(50) NOT NULL,

order_product_type CHAR(50) NOT NULL,

cust_id INT NOT NULL,

order_date DATETIME NOT NULL,

order_price DOUBLE NOT NULL,

order_amount INT NOT NULL,

PRIMARY KEY(order_id),

FOREIGN KEY(cust_id)

REFERENCES customers(cust_id)

ON DELETE RESTRICT

ON UPDATE RESTRICT

);

 

 

CREATE TABLE orders(

order_id INT NOT NULL AUTO_INCREMENT,

order_product CHAR(50) NOT NULL,

order_product_type CHAR(50) NOT NULL,

cust_id INT NOT NULL

CHECK(cust_id IN(SELECT cust_id FROM customers)),

order_date DATETIME NOT NULL,

order_price DOUBLE NOT NULL,

order_amount INT NOT NULL,

PRIMARY KEY(order_id)

);

 

 

CREATE TABLE orders(

order_id INT NOT NULL AUTO_INCREMENT,

order_product CHAR(50) NOT NULL,

order_product_type CHAR(50) NOT NULL,

cust_id INT NOT NULL,

order_date DATETIME NOT NULL,

order_price DOUBLE NOT NULL,

order_amount INT NOT NULL,

PRIMARY KEY(order_id),

CHECK(order_price>=2000 AND order_amount<=5)

);

 

 

 

 

CREATE TABLE orders(

order_id INT NOT NULL AUTO_INCREMENT,

order_product CHAR(50) NOT NULL,

order_product_type CHAR(50) NOT NULL,

cust_id INT NOT NULL,

order_date DATETIME NOT NULL,

order_price DOUBLE NOT NULL,

order_amount INT NOT NULL,

CONSTRAINT PRIMARY_KEY_ORDERS PRIMARY KEY(order_id),

CONSTRAINT FOREIGH_KEY_ORDERS FOREIGH KEY(cust_id)

REFERENCES customers(cust_id)

ON DELETE RESTRICT

ON UPDATE RESTRICT

);

 

 

ANALYZE TABLE mysql_test.customers;

更新数据库表的索引散列程度

 

 

CHECK TABLE mysql_test.customers;

检测表是否出现错误，该语句只对InnoDB、MyISAM、ARCHIVE、CSV表起作用

 

 

CHECKSUM TABLE mysql_test.customers;

获取一个校验和

 

 

OPTIMIZE TABLE mysql_test.customers;

优化数据库表

 

 

REPAIR TABLE

 

 

 

触发器

创建触发器

CREATE TRIGGER mysql_test.customers_insert.trigger AFTER INSERT

ON mysql_test.customers FOR EACH ROW SET @str='one customer added!';

 

SELECT @str;

 

 

DROP TRIGGER IF EXISTS mysql_test.customers_insert.trigger;

 

CREATE TRIGGER mysql_test.customers_insert.trigger AFTER INSERT

ON mysql_test.customers FOR EACH ROW SET @str=NEW.cust_id;

 

 

INSERT INTO mysql_test.customers

VALUES(NULL,'曾伟','F','长沙市','芙蓉区');

SELECT @str;

 

CREATE TRIGGER mysql_test.customers_update.trigger BEFORE UPDATE

ON mysql_test.customers FOR EACH ROW

SET NEW.cust_address=OLD.cust_contact;

 

UPDATE mysql_test.customers SET cust_address='武汉市'

WHERE cust_name='曾伟';

 

 

事件

SHOW VARIABLES LIKE 'EVENT_SCHEDULER';

 

或

 

SELECT @@ EVENT_SCHEDULER;

 

SELECT GLOBAL EVENT_SCHEDULER=1;

 

或

 

SELECT GLOBAL EVENT_SCHEDULER=TRUE;

 

创建事件

USE mysql_test;

DELIMITER $$

CREATE EVENT IF NOT EXISTS event_insert

ON SCHEDULER EVERY 1 MONTH

STARTS CURDATE+INTERVAL 1 MONTH

ENDS '2013-12-31'

DO

BEGIN

IF YEAR(CURDATE())<2013 THEN

INSERT INTO customers

VALUES(NULL,'戴军','M','北京市','海淀区');

       END IF;

END $$

 

修改事件

ALTER EVENT event_insert DISABLE;

 

ALTER EVENT event_insert ENABLE;

 

ALTER EVENT event_insert 

RENAME TO e_insert

 

DROP EVENT IF EXISTS e_insert;

 

存储过程与存储函数

DELIMITER !!

DELIMITER ;

 

DELIMITER $$

CREATE PROCEDURE sp_update_sex(IN cid INT,IN csex CHAR(1))

BEGIN

UPDATE customers SET cust_sex=csex WHERE cust_id=cid;

END $$

 

DECLARE cid INT(10);

 

SET cid=910;

 

DELIMITER $$

CREATE PROCEDURE sp_sumofrow(OUT ROWS INT)

BEGIN

DECLARE cid INT;

DECLARE FOUND BOOLEAN DEFAULT TRUE;

DECLARE cur_cid CURSOR FOR 

SELECT cust_id FROM customers;

DECLARE CONTINUE HANDLER FOR NOT FOUND

SET FOUND=FALSE;

SET ROES=0;

OPEN cur_cid;

FETCH cur_cid INTO cid;

WHILE FOUND DO

SET ROWS=ROWS+1;

FETCH cur_cid INTO cid;

END WHILE

CLOSE cur_cid;

END $$

 

本例中定义了一个CONTINUE HANDLER句柄，它是在条件出现时执行的代码

用于控制循环语句，实现游标的下移

 

调用存储过程

CALL sp_update_sex(909,'M');

修改存储过程

删除存储过程

DROP PROCEDURE sp_update_sex;

 

 

存储函数

DELIMITER $$

CREATE FUNCTION fn_search(cid INT)

RETURNS CHAR(2)

DETERMINISTIC

BEGIN

DECLARE SEX CHAR(2)

SELECT cust_sex INTO SEX FROM customers

WHERE cust_id=cid;

IF SEX IS NULL THEN

RETURN(SELECT'没有客户');

ELSE IF SEX='F' THEN

RETURN(SELECT'女');

ELSE RETURN(SELECT'男');

END IF;

END IF;

END $$

 

 

SELECT fn_search(904);

 

DROP FUNCTION IF EXISTS fn_search;

访问控制与安全管理

1.用户账号管理

select user from mysql.user;

1.1创建用户账号

SELECT PASSWORD(456);

CREATE USER 'zhangsan'@'localhost'IDENTIFIED BY '123'

'lisi'@'localhost'IDENTIFIED BY PASSWPRD

'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'

1.2删除用户

DROP USER lisi@ localhost

1.3修改用户账号

RENAME USER 'zhangsan'@'localhost'TO'wangwu'@'localhost';

1.4修改用户口令

SELECT PASSWORD('hello');

SET PASSWORD FOR 'wangwu'@'localhost'

='XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX';

2.账户权限管理

SHOW GRANTS FOR 'zhangsan'@'locaalhost';

2.1权限的授予

GRANT SELECT(cust_id,cust_name)

ON mysql_test.customers

TO 'zhangsan'@'locaalhost';

GRANT SELECT,UPDATE

ON mysql_test.customers

TO 'liming'@'localhost' IDENTIFIED BY '123',

'huang'@'localhost' IDENTIFIED BY '789';

GRANT ALL

ON mysql_test.*

TO 'wangwu'@'localhost';

GRANT CREATE USER

ON *.*

TO 'wangwu'@'localhost';

2.2权限的转移与限制

GRANT SELECT,UPDATE

ON mysql_test.customers

TO 'zhou'@'localhost' IDENTIFIED BY '123'

WITH GRANT OPTION;

 

GRANT DELETE

ON mysql_test.customers

TO 'zhou'@'localhost'

WITH MAX_QUERIES_PER_HOUR 1;

 

2.3权限的撤销

REVOKE SELECT

ON mysql_test.customers

TO 'zhou'@'localhost';

 

 

 

备份与恢复

1.使用sql语句备份恢复表数据

SELECT * FROM mysql_test.customers

INTO OUTFILE 'C:/BACKUP/backupfile.txt'

FIELDS TERMINATED BY ','

OPTIONALLY ENCLOSED BY '"'

LINES TERMINATED BY '?';

 

 

LOAD DATA INFILE 'C:/BACKUP/backupfile.txt'

INTO TABLE mysql_test.customers_copy

FIELDS TERMINATED BY ','

OPTIONALLY ENCLOSED BY '"'

LINES TERMINATED BY '?';

2.使用客户端恢复备份表数据

mysqldump -h localhost -u root -p123456 mysql_test customers>c:\backup\file.sql

注意：如果是本地服务器-h项可以省略

mysqldump -u root -p123456 --databases mysql_test mysql>c:\backup\data.sql

 

mysqldump -u root -p123456 --all-databases>c:\backup\alldata.sql

 

mysqldump -u root -p123456 --tab=c:\backup\mysql_test

 

mysql -u root -p123456 mysql_test<mysql_test.sql

 

mysql -u root -p123456 customers<c:\backup\customers.sql

 

mysqlimport -u root -p123456 --low-priority --replace mysql_test c:\backup\customers.txt

 

mysqlbinlog binlog.000001

 

mysqlbinlog binlog.000001>c:\backup\bin_log000001.txt

 

mysqldump -u root -p123456 mysql_test<alldata.sql

 

mysqlbinlog bin_log.000001|mysql -u root -p123456

 

mysqlbinlog bin_log.000002|mysql -u root -p123456

 

PHP的mysql数据库编程

<? php

$string="hello";

echo $string;

?>

 

 

<? php

$con=mysql_connect("localhost:3306","root","123456");

if(!$con){

echo "连接失败！<br>";

echo "错误编号：".mysql_errorno()."<br>";

echo "错误信息：".mysql_error()."<br>";

die();

}

echo "连接成功！<br>";

?>

 

<? php

$server="localhost:3306";

$user="root";

$pwd="123456";

$con=mysql_pconnect($server,$user,$pwd);

if(!$con){

die("连接失败！".mysql_error());

}

echo "MYSQL服务器：$server<br>用户名：$user<br>";

echo "使用函数mysql_pconnect()永久链接数据库。<br>";

?>

 

 

 

<? php

$con=mysql_connect("localhost:3306","root","123456");

if(mysql_errno()){

echo "数据库服务器连接失败！<br>";

die();

}

mysql_select_db("mysql_test",$con);

if(mysql_errno()){

echo "数据库服务器连接失败！<br>";

die();

}

echo "数据库选择成功！<br>";

?>

 

 

<? php

$con=mysql_connect("localhost:3306","root","123456")

or die("数据库服务器连接失败！<br>");

mysql_select_db("mysql_test",con) or die("数据库选择失败！<br>");

mysql_query("set names 'gbk'");

$sql="INSERT INTO customers(cust_id,cust_name,cust_sex,cust_address,cust_contact)";

$sql=$sql."VALUES(NULL,'李中华','M','深圳市','南山区')";

if($sql,$con){

echo "客户添加成功！<br>";

}

else

echo "客户添加失败！<br>";

?>

 

 

<? php

$con=mysql_connect("localhost:3306","root","123456")

or die("数据库服务器连接失败！<br>");

mysql_select_db("mysql_test",con) or die("数据库选择失败！<br>");

mysql_query("set names 'gbk'");

$sql="UPDATE customers SET cust_address='广州市'";

$sql=$sql."WHERE cust_name='李中华'";

if(mysql_query($sql,$con))

echo "客户地址修改成功！<br>";

else

echo "客户地址修改失败！<br>";

?>

 

<? php

$con=mysql_connect("localhost:3306","root","123456")

or die("数据库服务器连接失败！<br>");

mysql_select_db("mysql_test",con) or die("数据库选择失败！<br>");

mysql_query("set names 'gbk'");

$sql="DELETE FROM customers";

$sql=$sql."WHERE cust_name='李中华'";

if(mysql_query($sql,$con))

echo "客户删除成功！<br>";

else

echo "客户删除失败！<br>";

?>

 

<? php

$con=mysql_connect("localhost:3306","root","123456")

or die("数据库服务器连接失败！<br>");

mysql_select_db("mysql_test",con) or die("数据库选择失败！<br>");

mysql_query("set names 'gbk'");

$sql="SELECT cust_name FROM customers";

$sql=$sql."WHERE cust_id=916";

$result=mysql_query($sql,$con);

if($result){

echo "客户查询成功！<br>";

$arry=mysql_fetch_array($result,MYSQL_NUM);

if($array){

echo "读取到客户信息！<br>";

echo "所要查询客户的姓名是：".$array[0];

}

else

echo "没有读取到客户信息！<br>"

 

}

else

echo "客户查询失败！<br>";

?>

 

 

<? php

$con=mysql_connect("localhost:3306","root","123456")

or die("数据库服务器连接失败！<br>");

mysql_select_db("mysql_test",con) or die("数据库选择失败！<br>");

mysql_query("set names 'gbk'");

$sql="SELECT * FROM customers";

$sql=$sql."WHERE cust_sex='F'";

$result=mysql_query($sql,$con);

if($result){

echo "查询成功！<br>";

$num=mysql_num_rows($result);

echo "数据库mysql_test中女性顾客数为：".$num."位";

}

else

echo "查询失败！<br>";

?>

 

 

<? php

$con=mysql_connect("localhost:3306","root","123456")

or die("数据库服务器连接失败！<br>");

mysql_select_db("mysql_test",con) or die("数据库选择失败！<br>");

mysql_query("set names 'gbk'");

$sql="SELECT * FROM customers";

$sql=$sql."WHERE cust_sex='F'";

$result=mysql_query($sql,$con);

if($result){

echo "查询成功！<br>";

if(mysql_data_seek($result,2)){

$array=mysql_fetch_array($result,MYSQL_NUM);

echo "数据库mysql_test中第三位女性顾客是：".$array[1];

}

else

echo "记录定位失败！<br>";

}

else

echo "查询失败！<br>";

?>

 

 

                <? php

$con=mysql_connect("localhost:3306","root","123456")

or die("数据库服务器连接失败！<br>");

echo "已成功建立与MYSQL服务器的连接！<br>";

mysql_select_db("mysql_test",con) or die("数据库选择失败！<br>");

echo "已成功选择数据库mysql_test！<br>";

mysql_close($con) or die("关闭与MYSQL数据库服务器的连接失败！<br>");

echo "已成功关闭与MYSQL数据库服务器的连接！<br>";

?>