MySQL基本操作

原创

北京动点软件 2009-05-18 14:13:00 博主文章分类：Silverlight项目开发 ©著作权

文章标签 职场 MySQL 数据库休闲 文章分类 服务器

©著作权归作者所有：来自51CTO博客作者北京动点软件的原创作品，如需转载，请与作者联系，否则将追究法律责任

01 MySQL基本操作

一、使用MySQL命令行

1、连接与断开MySQL服务器：mysql –h host –u user –p，回车后输入密码。
断开连接：quit或者exit

2、执行SQL语句：语句以“；”结束。不区分大小写。
例：select curdate(), curtime(); 查询服务器当前日期和时间。

在一行中可以输入多条SQL语句，各语句以“；”号隔开。各条语句得到各自的结果集。

在任何时候输入“\c”并回车，则取消当前输入，重新回到mysql>提示符。

某些情况下，按回车后，SQL语句并不执行，出现的也不是“->”提示符，如：[’>]、[”>]、[`>]、[/*]，则表示引号或注释没有结束。即使通过\c取消，也要先结束引号。

3、创建数据库：CREATE DATABASE
create database db_test default character set gb2312 collate gb2312_chinese_ci;
CHARACTER SET：字符集。COLLATE：整理或校对。

如不指定则采用默认。create database db_test;

4、关于字符校对collate
指比较字符串时采用的一套规则。还可以设置服务器校对和表校对。一般情况下不需要设置字符校对，因为对应于每一种字符集MySQL有默认的校对规则，例如gb2312字符集默认校对规则为gb2312_chinese_ci。当不设置校对时采用默认。

5、删除数据库：DROP DATABASE
例：drop database db_test;

6、创建数据库表：CREATE TABLE
在创建数据表之前，首先要选择数据库。其它查询、修改、删除等操作也一样。
use db_name;
例：create table use_info (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
name VARCHAR(10) NOT NULL ,
gender SET(‘男’， ‘女’) NOT NULL DEFAULT ‘男’ ，
age INT NULL ,
email VARCHAR(30) NOT NULL ,
INDEX (name) )
ENGINE = innodb CHARACTER SET gb2312
COLLATE gb2312_chinese_ci COMMENT = ‘Information’ ;

查看当前数据库下所有表：show tables;

7、查看表结构：SHOW COLUMNS 或 DESCRIBE
show columns from user_info from db_test;
show columns from db_test.user_info;
如果当前使用数据库为表所在的数据库，则可以省略db_test。

describe user_info;
用法同上，还可以只得到某一个列的信息：
describe user_info name;

8、删除表：DROP TABLE
drop table user_info;
可以一次删除多个表：drop table user_info1，user_info2;

9、重命名表：RENAME TABLE
可以同时对多个表重命名，之间以逗号“，”隔开。
rename table user_info to users_infomation,
sale_info to sales_infomation;

10、修改表结构：ALTER TABLE
修改表名称：ALTER TABLE user_info RENAME users_information;
修改字段类型、长度：ALTER TABLE user_information MODIFY age TINYINT NOT NULL,
CHANGE email e_mail VARCHAR(40);
添加两个新字段：ALTER TABLE user_information ADD reg_date DATETIME NOT NULL,
ADD address VARCHAR(50) NULL;
在列reg_date中添加索引：ALTER TABLE user_information ADD INDEX (reg_date);
删除age字段：ALTER TABLE users_information DROP COLUMN age;

11、浏览数据库记录：SELECT
返回所有记录所有列：select * from user_info;
返回所有记录部分列：select name, gender,email from user_info;
返回所有男用户：select * from user_info where gender = ‘男’;
返回所有小于30岁的男用户：select * from user_info where gender=‘男’AND age<30;
返回所有小于30大于22的男用户，或者小于28大于20的女用户：
select * from user_info where (gender=‘男’AND age<30 AND age>22)
OR (gender=‘女’AND age<28 AND age>20);

12、插入记录：INSERT
insert into user_info (name, gender, age, email) values(‘XiaoHei’, ‘男’，28，‘xhei@sohu.com’);
insert into user_info (name, gender, email) values(‘Xiaobai’,DEFAULT，28，‘xbei@sohu.com’);
insert into user_info (name,email) values(‘wanghai’,CONCAT(name, ‘@sohu.com’))
CONCAT为MySQL系统函数，将多个字符串连接起来。
可以一次同时插入多行记录，用“，”隔开。
insert into user_info(name,gender,age,email)
values(‘ChenYi’, ‘女’，25，‘chenyi@shou.com’),
(‘XiaoHei’, ‘男’，28，‘xhei@sohu.com’);

13、修改记录：UPDATE
update user_info set age=age+1;
update user_info set name=“小黑”,gender=‘男’ where name=‘XiaoHei’;

14、删除记录：DELETE
各关键字含义用UPDATE，不指定where子句时删除所有行。
delete from user_info;
delete from user_info where gender=‘男’;

二、使用MySQL Query Browser

三、使用phpMyAdmin
如果字段类型是set型，则值以‘a’ ‘b’ ‘c’的形式给出，如要在值中输入反斜线“\”或者单引号“‘”，需要有前加上“\”转意。
例：gender SET 长度/值：‘男’， ‘女’

四、PHP中访问MySQL

两个扩展：mysql基础库，mysqli增加了对mysql新特性的支持。

五、mysql函数库

常用数据库操作函数：
mysql_connect() — 打开一个到 MySQL 服务器的连接
mysql_pconnect() — 打开一个到 MySQL 服务器的持久连接
mysql_select_db() — 选择 MySQL 数据库
mysql_query() — 发送一条 MySQL 查询
mysql_db_query() — 发送一条 MySQL 查询
mysql_result() — 取得结果数据
mysql_affected_rows() — 取得前一次 MySQL 操作所影响的记录行数
mysql_num_rows() — 取得结果集中行的数目
mysql_num_fields() — 取得结果集中字段的数目
mysql_data_seek() — 移动内部结果的指针
mysql_fetch_array() — 从结果集中取得一行作为关联数组，或数字数组，或二者兼有
mysql_fetch_assoc() — 从结果集中取得一行作为关联数组
mysql_fetch_row() — 从结果集中取得一行作为枚举数组
mysql_free_result() — 释放结果内存
mysql_close() — 关闭 MySQL 连接
mysql_get_host_info() — 取得 MySQL 主机信息
mysql_get_client_info() — 取得 MySQL 客户端信息
mysql_get_server_info() — 取得 MySQL 服务器信息
mysql_client_encoding() — 返回字符集的名称

1、连接数据库
$link = mysql_connect(‘localhost’, ‘root’, ‘password’);
mysql_pconnect()建立的连接持久保留，脚本执行结束后，仍然不会关闭，并且无法使用mysql_close()关闭。

2、选择数据库
$link = mysql_connect(‘localhost’, ‘root’, ‘password’);
mysql_select_db(‘teaching_sys’,$link);
选择了数据库后之后所进行的操作默认都是对该数据库进行。要对其它数据库操作，可以使用mysql_select_db()选择其它数据库。
或者使用SQL语句访问其它数据库的对象：select * from db_user.user_info，使用这种方法，SQL语句执行结束后，当前默认数据库仍是原来选择的数据库。

3、执行SQL语句
$link = mysql_connect(‘localhost’, ‘root’, ‘password’);
mysql_select_db(‘teaching_sys’,$link);
$sql = “select * from department”;
$result = mysql_query($sql,$link);
查询字符串中不包含分号“；”
mysql_db_query()不同的是可以指定要使用的数据库。
$result = mysql_db_query(‘text’,$sql,$link);
使用mysql_db_query()后，新数据补激活。要使用原来需生新选择。因此，一般不用。

4、读取结果集
int mysql_result(resource result, int row [, mixed field]) — 取得结果数据
int mysql_result($result,2,1);返回第3行记录中的第2个字段的值。
mysql_affected_rows([resource link_identifier]) — 取得前一次 MySQL 操作所影响的记录行数。link_identifier为已建立的连接，如不指定则使用上一个。
mysql_num_rows(resource result) — 取得结果集中行的数目
mysql_num_fields(resource result) — 取得结果集中字段的数目
mysql_data_seek(resource result, int row_number) — 移动内部结果的指针到指定行。
mysql_fetch_array(resource result [, int result_type]) — 从结果集中取得一行作为关联数组，或数字数组，或二者兼有
mysql_fetch_assoc(resource result) — 从结果集中取得一行作为关联数组
mysql_fetch_row(resource result) — 从结果集中取得一行作为枚举数组

5、关闭连接
bool mysql_free_result(resource result)
bool mysql_close([resource link_identifier])

6、服务器信息
string mysql_get_host_info ( [resource $link_identifier] )
string mysql_get_client_info ( void )
string mysql_get_server_info ( [resource $link_identifier] )
string mysql_client_encoding ( [resource $link_identifier] )

7、应用实例
//建立连接
$link = @mysql_connect('localhost', 'root', 'czhnet');
//选择数据库
@mysql_select_db('teaching_sys',$link);
$sql = "select * from student";
//设置字符集
mysql_query("set names gb2312");
//发送SQL语句
$result = mysql_query($sql,$link);
?>

//读取结果
$bgcolor="#ffffff"; //定义表格中行的颜色
for($i=0;$i{ //交替变换表格数据行的颜色
if($bgcolor=="#ffffff")
$bgcolor = "#dddddd";
else
$bgcolor = "#ffffff";
//取一行记录
$row = mysql_fetch_array($result);
echo "";
echo "".$row['name']."";
echo "".$row['school_no']."";
echo "".$row['gender']."";
echo "".$row['province']."";
echo "".$row['city']."";
echo "".$row['address']."";
echo "".$row['email']."";
echo "".$row['phone']."";
echo "";
}
?>
8、关于MySQL的中文问题
解决乱码的方法是，在执行SQL语句之前，将MySQL以下三个系统参数设置为与服务器字符集character-set-server相同的字符集。
character_set_client：客户端的字符集。
character_set_results：结果字符集。
character_set_connection：连接字符集。
设置这三个系统参数通过向MySQL发送语句：set names gb2312
即在PHP中执行：mysql_query(“set names gb2312”);

六、mysqli扩展

1、连接数据库
$mysqli = new mysqli(‘localhost’, ‘root’, ‘password’, ‘teaching_sys’);
或$link=mysqli_connect(‘localhost’, ‘root’, ‘password’, ‘teaching_sys’);

2、选择数据库
$mysqli->select_db(‘sales’);
或：mysqli_xelect_db($link, ‘sales’);

3、执行SQL语句
$sql = ‘select * from student’;
$result = $mysqli->query($sql);
或：$result = mysqli_query($link,$sql);

4、读取结果集
取得mysql操作所影响的记录行数
mysqli->affected_rows或mysqli_affected_rows()
取得结果集的行数
mysqli_result->num_rows或mysqli_num_rows($result)
取得结果集中字段的数目
mysqli_result->field_count或mysqli_num_fields()
移动结果集指针
mysqli_result->data_seek()或mysqli_data_seek()
实现fetch_array
mysqli_result->fetch_array()或mysqli_fetch_array()
实现fetch_assoc
mysqli_result->fetch_assoc()或mysqli_fetch_assoc()
实现fetch_row
mysqli+result->fetch_row()或mysqli_fetch_row()

5、关闭连接
释放内存
mysqli_result->free()、mysqli_result->close()、mysqli_result->free_result()。
或mysqli_free_result()
关闭连接
mysqli->close()或mysqli_close()

6、服务器信息
主机信息：mysqli->host_info或mysqli_get_host_ifo()
客户端信息：mysqli->client_info或mysqli_get_client_info()
服务器信息：mysqli->get_server_info或mysqli_get_server_info()

7、应用实例
//建立连接
$mysqli = new mysqli('localhost', 'root', 'czhnet','teaching_sys');
$sql = "select * from student";
//设置字符集,mysqli同样需要
$mysqli->query("set names gb2312");
//发送SQL语句
$result = $mysqli->query($sql);
?>
//读取结果
$bgcolor="#ffffff"; //定义表格中行的颜色
for($i=0;$i<$result->num_rows;$i++)
{
//交替变换表格数据行的颜色
if($bgcolor=="#ffffff")
$bgcolor = "#dddddd";
else
$bgcolor = "#ffffff";
//取一行记录
$row = $result->fetch_array();
echo "";

02 使用SQL语句

一、SELECT语句

1、选择所有列：SELECT * FROM student
可以指定多个表，将记录组合在一起，返回结果集是各表记录的交叉组合。有N*M行记录
SELECT department.* , course.* FROM department, course

2、选择部分列：SELECT name, school_no,school_year FROM student
可由多个表中选择部分字段
SELECT student.name, teach.name FROM student, teacher

3、使用别名
SELECT name AS 姓名， school_no AS 学号， phone AS 电话 FROM student
关键字AS是可选的，别名也可以使用单引号或双引号括起来。并不需要同时为所有字段指定别名。
SQL语句中可以加入注释文字，注释放在/*与*/之间，可以出现在SQL语句任意位置。
例：SELECT * FROM /*学生表*/ student
当为字段定义别名后，可以在ORDER BY子句、GROUP BY子句中引用字段的别名，但是在WHERE子句中不允许引用字段的别名。

4、使用表达式
SELECT title 课程，units 学分，units*16 课时 FROM course
在查询时对字段进行的运算只影响返回的结果，不影响表中原数据。
SELECT title 课程，course_no 课程编号，units>5 ‘学分是否大于5’ FROM course
通过()改变运算顺序：SELECT title 课程，(units+0.5)*16 扩展课时 FROM course
在使用表达式时，各种字段类型将自动进行转换：SELECT title AS 课程，units AS 学分，depa_no AS 学院编号，units+depa_no AS 课时加学院编号 FROM course
表达式中并不要求必须出现字段，可以由指定值组成表达式：SELECT “课程信息” AS 说明，title AS 课程，course_no AS 课程编号，units 学分 FROM course
返回：说明课程 ……
课程信息高等数学 ……
课程信息英语 ……

5、使用ORDER BY对结果排序
按学号升序：SELECT * FROM student ORDER BY school_no
先按学号再按课程：SELECT * FROM grade ORDER BY student_schoo_no, course_no
升序：ASC，降序：DESC，默认为升序。
SELECT * FROM grade ORDER BY student_school_no, course_no DESC
SELECT * FROM grade ORDER BY student_school_no ASC, course_no DESC
SELECT student_school_no AS 学号，course_no AS 课程编号，grade AS 成绩 FROM grade ORDER BY 学号 ASC，课和编号 DESC
排序字段也可以是字符型：SELECT * FROM student ORDER BY name

7、限制返回结果行
SELECT * FROM student ORDER BY school_no LIMIT 2,5
LIMT的第一个参数表示开始行的偏移量，0为第一行；第二个整数表示要返回的行数。
省略表示从第一行开始：SELECT * FROM student ORDER BY school_no LIMIT 5
也可使用row_count OFFSET offset格式指定
SELECT * FROM student ORDER BY school_no LIMIT 5 OFFSET 2
从2开始，返回5行

8、使用DISTINCT消除重复行
SELECT DISTINCT student_school_no FROM grade WHERE term = ‘2005-2006-1’
跟多处字段时只有当多个字段均相同是才认为是重复的
SELECT DISTINCT student_schoo_no, course_no, term FROM grade

二、WHERE条件子句

1、基本子句：SELECT * FROM student WHERE student_no = ‘200503001008’

2、使用AND、OR和NOT 优先级NOT>AND>OR
SELECT * FROM grade WHERE student_school_no=‘200503001008’AND course_no=‘1001’
SELECT * FROM grade WHERE student_school_no=‘200503001008’ AND (course_no =‘1001’ OR course_no=‘1005’)
SELECT * FROM grade WHERE NOT student_school_no=‘200503001008’

3、使用比较运算符
SELECT * FROM grade WHERE course_no=‘110101’AND grade>80
SELECT * FROM grade WHERE grade<90 AND grade>=80
比较运算符用于比较字符串时，默认情况下不区分大小写：
SELECT * FROM department WHERE no>‘07’

4、使用BETWEEN AND运算符
BETWEEN AND运算符的功能类似于同时满足<=和>=。
例：SELECT * FROM grade WHERE grade BETWEEN 80 AND 90
可将NOT与BETWEEN AND联合使用，功能为 < 或者 > 。
SELECT * FROM grade WHERE grade NOT BETWEEN 80 AND 90

5、查找空值IS NULL
判断值是否为空，是则返回TRUE，否则返回FALSE。IS NOT NULL相反。
SELECT * FROM student WHERE phone IS NULL
SELECT * FROM student WHERE email IS NOT NULL

6、使用用LIKE关键字
通配符：“%”表示零个或多个任意字符，“_”表示任意单个字符。
SELECT * FROM grade WHERE course_no LIKE ‘110101’
SELECT * FROM student WHERE name LIKE ‘李%’
SELECT * FROM student WHERE name LIKE ‘李_’?
SELECT * FROM student WHERE school_no LIKE ‘2005%12’
SELECT * FROM student WHERE name LIKE ‘李_’AND school_no LIKE ‘2005%12’AND email LIKE ‘x%sohu.com’
可以将NOT关键字与LIKE同时使用
SELECT * FROM student WHERE name NOT LIKE ‘李%’

7、使用IN列表
IN关键字提供一个可选的列表，在列表中出现的值均补视为满足条件的值。
SELECT * FROM ‘course’WHERE depa_no IN(‘11’, ‘13’, ‘15’)
也可以将IN与NOT运算符联合使用
SELECT * FROM ‘course’WHERE depa_no NOT IN(‘11’,‘13’)

三、使用内部函数

CONCAT()将多个字符串连接组成一个字符串。
SELECT name AS 姓名，school_no AS 学号，CONCAT(province,city,address) AS 家庭住址，address FROM student
SELECT CONCAT(name, ‘/’,school_no, ‘/’,CONCAT(province,city,address)) AS 家庭住址 FROM student

使用内部函数时要注意：
 函数的处理只影响客户端返回的结果，而不会影响数据库中的原数据。
 所在内闻函数在使用时，函数名与符号、符号与参数之间不能有空格。
 对于字符串函数，字符串中字符的位置序号由1开始，第一个字符的位置是1。

1、字符串处理函数
查询所有以c、x、y开头，具为sohu.com邮箱的学生：select * from student where substring(email,1,1) in (‘c’, ‘x’, ‘y’) and email like ‘%@sohu.com’
将邮箱名与后缀分开显示：select name as 姓名，substring_index(email, ‘@’,1) as 邮箱名称，substring_index(email, ‘@’,-1) as 邮箱后缀 from student
查询所有名字为两个字的学生
select * from student where char_length(name)=2

2、日期与时间处理函数
select school_no as 学号，grade as 成绩，data(submit_time) as 登记日期 from grade
获取服务器当前时间，并以YYYYMMDD形式显示
select date_format(now(),‘%Y%m%d’)

3、数值处理函数

4、比较函数
GREATEST(exp1,exp2,…)返回最大值
LEAST(exp1,exp2,…) 返回最小值

四、聚合函数与记录分组

1、聚合函数
查询所有学生110101课程的平均成绩
SELECT AVG(grade) AS 平均成绩 FROM grade WHERE course_no=‘110101’
查询学生中使用sohu.com邮箱的人数
SELECT COUNT(*) FROM student WHERE substring_index(email,‘@’，-1)=‘sohu.com’
查询110101课程最高成绩
SELECT MAX(grade) AS 最高成绩 FROM grade
查询110101课程最低成绩
SELECT MIN(grade) AS 最低成绩 FROM grade
求所有学生110101课程的总成绩
SELECT SUM(grade) AS 总成绩 FROM grade WHERE course_no=‘110101’

2、使用GROUP BY对记录分组
查询2005-2006-1学期各课程的平均成绩
/*课程编号必须包含在GROUP BY子句中，可以使用别名*/
SELECT course_no AS 课程编号，AVG(grade) AS 平均成绩 ROM grade
HWERE term=‘2005-2006-1’ GROP BY 课程编号
查询各学期各课程的平均成绩
SELECT course_no AS 课程编号，term AS 学期，AVG(grade) AS 平均成绩
FROM grade GROUP BY course_no,term
/*course_no和term必须包含在group by 子句中*/

3、HAVING子句
对GROUP BY子句分组得到的每一组进行限制。只能和SELECT一起使用。当不使用GROUP BY时，和WHERE子句效果相同。
查询2005-2006-1学期平均成绩大于80的所有课程
SELECT course_no AS 课程编号，AVG(grade) AS 平均成绩 FROM grade
WHERE term=‘2005-2006-1’GROUP BY course_no HAVING AVG(grade)>80

五、多表联合查询

1、FROM子句中的多表联合查询
SELECT grade.*, student.*, course.*
FROM grade, student, course
WHERE grade.student_school_no = student.school_no
AND grade.course_no = course.course_no
ORDER BY grade.course_no, grade.student_school_no
可以为表定义别名，定义之后在所有引用表的地方都必须使用别名，否则将报错
SELECT s.name AS 姓名，s.school_no AS 学号，c.title AS 课程，
c.course_no AS 课和编号，g.grade AS 成绩 /*使用表别名*/
FROM student AS s, course AS c, grade AS g
WHERE s.school_no = g.student_school_no AND c.course_no = g.course_no
ORDER BY c.course_no ASC, g.grade DESC

2、使用JOIN…ON关键字进行多表查询
内连接(INNER JOIN)：最常用的一种方式，关键字INNER可省略，效果同WHERE。
SELECT student.name AS 姓名，student.school_no AS 学号，course.title AS 课程，
course.course_no AS 课和编号，grade.grade AS 成绩
FROM student
INNER JOIN grade ON student.school_no = grade.student_school_no
INNER JOIN course.course_no = grade.course_no
ORDER BY course.course_no ASC, grade.grade DESC
左连接(LEFT JOIN)从左边表中返回所有行，无论在其他表中有无对应行。无匹配则返回空
SELECT student.id, student.name AS 姓名， student.school_no AS 学号，
course.title AS 课程， course.course_no AS 课程编号，grade.grade AS 成绩
FROM student
LEFT JOIN grade ON student.school_no = grade.student_school_no
LEFT JOIN course ON course.course_no = grade.course_no
ORDER BY course.course_no ASC, grade.grade DESC
右连接(RIGHT JOIN)与LEFT JOIN类似，从右边表中选择所有记录
交叉连接(CROSS JOIN)在mysql中与INNER JOIN结果相同。而在标准SQL语言中两者不同

六、子查询

1、FROM子句中的子查询

2、WHERE子句中的子查询

3、INSERT、UPDATE和DELETE语句中的子查询

4、IN列表中的子查询

5、EXISTS关键子中的子查询
EXISTS用于判断子查询返回的结果集中是否存在记录。

七、使用UNION关键字

将多个独立的查询结果集组合为一个完整的结果集。被组合的多个查询必须具有相同的列数，并且对应列需要有匹配的数据类型。
select * from grade where course_no = ‘0100101’
UNION
select * from grade_1992_2000 where course_no = ‘0100101’

另一种用法是将清单与统计组合在一起
select …… from …… where ……
UNION
select …… from …… where ……

八、跨数据库连合查询
通过使用“.”连接表名来引用不同数据库的表
SELECT tc.*, tu.* FROM teaching_sys.course AS tc, user.user_info AS tu

03 视图、存储过程、触发器

一、视图

1、创建视图
CREATE VIEW view_exist_grade_student
AS
select * from student
where exists(select * from grade where student_school_no = student.school_no)

2、修改视图
ALTER VIEW view_name [column1,……columnN]
AS
select_statement

3、删除视图
DROP VIEW [IF EXISTS]
view_name [, view_name]

4、使用视图
一般情况下视图只作查询用，使用方法与使用表的方法相同

二、存储过程简介

使用SQL语句和过程控制语句编写的程序，存储在数据库服务器上，可以由用户直接或间接进得调用。具有以下优点：
提高效率
可重用性
可维护性

PHP中mysql扩展库不支持对存储过程返回结果集的处理，所以采用mysqli扩展调用。

如果采用普通的操作方式而不采用存储过程，则插入多条记录时，需要循环向数据库发送SQL语句，每次插入一条记录。而存储过程只需要与数据库进行一次通信就可插入多条记录。有利于提高效率，降低资源消耗。