01 MySQL基本操作
一、使用MySQL命令行
1、连接与断开MySQL服务器:mysql –h host –u user –p,回车后输入密码。
断开连接:quit或者exit
断开连接:quit或者exit
2、执行SQL语句:语句以“;”结束。不区分大小写。
例:select curdate(), curtime(); 查询服务器当前日期和时间。
例: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 default character set gb2312 collate gb2312_chinese_ci;
CHARACTER SET:字符集。COLLATE:整理或校对。
如不指定则采用默认。create database db_test;
4、关于字符校对collate
指比较字符串时采用的一套规则。还可以设置服务器校对和表校对。一般情况下不需要设置字符校对,因为对应于每一种字符集MySQL有默认的校对规则,例如gb2312字符集默认校对规则为gb2312_chinese_ci。当不设置校对时采用默认。
指比较字符串时采用的一套规则。还可以设置服务器校对和表校对。一般情况下不需要设置字符校对,因为对应于每一种字符集MySQL有默认的校对规则,例如gb2312字符集默认校对规则为gb2312_chinese_ci。当不设置校对时采用默认。
5、删除数据库:DROP DATABASE
例:drop database db_test;
例: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’ ;
在创建数据表之前,首先要选择数据库。其它查询、修改、删除等操作也一样。
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。
show columns from user_info from db_test;
show columns from db_test.user_info;
如果当前使用数据库为表所在的数据库,则可以省略db_test。
describe user_info;
用法同上,还可以只得到某一个列的信息:
describe user_info name;
用法同上,还可以只得到某一个列的信息:
describe user_info name;
8、删除表:DROP TABLE
drop table user_info;
可以一次删除多个表:drop table user_info1,user_info2;
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;
可以同时对多个表重命名,之间以逗号“,”隔开。
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;
修改表名称: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);
返回所有记录所有列: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’);
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’;
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=‘男’;
各关键字含义用UPDATE,不指定where子句时删除所有行。
delete from user_info;
delete from user_info where gender=‘男’;
二、使用MySQL Query Browser
三、使用phpMyAdmin
如果字段类型是set型,则值以‘a’ ‘b’ ‘c’的形式给出,如要在值中输入反斜线“\”或者单引号“‘”,需要有前加上“\”转意。
例:gender SET 长度/值:‘男’, ‘女’
如果字段类型是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() — 返回字符集的名称
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()关闭。
$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语句执行结束后,当前默认数据库仍是原来选择的数据库。
$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()后,新数据补激活。要使用原来需生新选择。因此,一般不用。
$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) — 从结果集中取得一行作为枚举数组
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])
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] )
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);
?>
//建立连接
$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”);
$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’);
$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’);
$mysqli->select_db(‘sales’);
或:mysqli_xelect_db($link, ‘sales’);
3、执行SQL语句
$sql = ‘select * from student’;
$result = $mysqli->query($sql);
或:$result = mysqli_query($link,$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()
取得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()
释放内存
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()
主机信息: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 "";
//建立连接
$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
可以指定多个表,将记录组合在一起,返回结果集是各表记录的交叉组合。有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
可由多个表中选择部分字段
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子句中不允许引用字段的别名。
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
返回:说明 课程 ……
课程信息 高等数学 ……
课程信息 英语 ……
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
按学号升序: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行
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
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’
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’
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
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
判断值是否为空,是则返回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 ‘李%’
通配符:“%”表示零个或多个任意字符,“_”表示任意单个字符。
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’)
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
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开始,第一个字符的位置是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
查询所有以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’)
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,…) 返回最小值
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’
查询所有学生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 子句中*/
查询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
对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
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语言中两者不同
内连接(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用于判断子查询返回的结果集中是否存在记录。
EXISTS用于判断子查询返回的结果集中是否存在记录。
七、使用UNION关键字
将多个独立的查询结果集组合为一个完整的结果集。被组合的多个查询必须具有相同的列数,并且对应列需要有匹配的数据类型。
select * from grade where course_no = ‘0100101’
UNION
select * from grade_1992_2000 where course_no = ‘0100101’
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 …… from …… where ……
UNION
select …… from …… where ……
八、跨数据库连合查询
通过使用“.”连接表名来引用不同数据库的表
SELECT tc.*, tu.* FROM teaching_sys.course AS tc, user.user_info AS tu
通过使用“.”连接表名来引用不同数据库的表
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)
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
ALTER VIEW view_name [column1,……columnN]
AS
select_statement
3、删除视图
DROP VIEW [IF EXISTS]
view_name [, view_name]
DROP VIEW [IF EXISTS]
view_name [, view_name]
4、使用视图
一般情况下视图只作查询用,使用方法与使用表的方法相同
一般情况下视图只作查询用,使用方法与使用表的方法相同
二、存储过程简介
使用SQL语句和过程控制语句编写的程序,存储在数据库服务器上,可以由用户直接或间接进得调用。具有以下优点:
提高效率
可重用性
可维护性
提高效率
可重用性
可维护性
PHP中mysql扩展库不支持对存储过程返回结果集的处理,所以采用mysqli扩展调用。
如果采用普通的操作方式而不采用存储过程,则插入多条记录时,需要循环向数据库发送SQL语句,每次插入一条记录。而存储过程只需要与数据库进行一次通信就可插入多条记录。有利于提高效率,降低资源消耗。
三、MySQL5 触发器
是数据库服务器中与表相关的特定存储过程,当表上发生相关事件时自动执行。例如,当向表中插入数据时,自动进行有效性检查、对插入值自动计算等等。
支持三种类型的触发事件:
INSERT事件:当向表中插入记录时触发执行触发程序。
UPDATE事件:当更新表中记录时触发执行触发程序。
DELETED事件:当删除表中记录时触发执行触发程序。
INSERT事件:当向表中插入记录时触发执行触发程序。
UPDATE事件:当更新表中记录时触发执行触发程序。
DELETED事件:当删除表中记录时触发执行触发程序。
对于触发程序执行时间,支持以下两种类型:
BEFORE:在触发该触发程序的语句之前执行。
AFTER: 在触发该触发程序的语句之后执行。
BEFORE:在触发该触发程序的语句之前执行。
AFTER: 在触发该触发程序的语句之后执行。
本文转自dotfun 51CTO博客,原文链接:http://blog.51cto.com/dotfun/286046