时间：2021-07-01 10:21:17 帮助过：26人阅读
# SELECT field1, field2,...fieldN FROM table_name1, table_name2... # ORDER BY field1 [ASC [DESC][默认 ASC]], [field2...] [ASC [DESC][默认 ASC]]
案例
# 查询年龄在18到34岁之间的男性，按照年龄从小到大到排序 # select * from students where (age between 18 and 34) and gender = 1 order by age asc; # 查询年龄在18到34岁之间的女性，身高从高到矮排序 # select * from students where (age between 18 and 34) and gender = 2 order by height desc;
# 查询年龄在18到34岁之间的女性，身高从高到矮排序, 如果身高相同的情况下按照年龄从小到大排序 # select * from students where (age between 18 and 34) and gender = 2 order by height desc,age asc; # 查询年龄在18到34岁之间的女性，身高从高到矮排序, 如果身高相同的情况下按照年龄从小到大排序,如果年龄也相同那么按照id从大到小排序 # select * from students where (age between 18 and 34) and gender = 2 order by height desc,age asc,id desc;
# mysql> select bName,price from books where price in (50,60,70) order by price asc; +-------------------------------------+-------+ | bName | price | +-------------------------------------+-------+ | Illustrator 10完全手册 | 50 | | FreeHand 10基础教程 | 50 | | 网站设计全程教程 | 50 | | ASP数据库系统开发实例导航 | 60 | | Delphi 5程序设计与控件参考 | 60 | | ASP数据库系统开发实例导航 | 60 |升序：order by “排序的字段” asc 默认
# mysql> select bName,price from books where price in (50,60,70) order by price desc; +--------------------------------+--------+ | bName | price | +--------------------------------+--------+ | ASP数据库系统开发实例导航 | 60 | | Delphi 5程序设计与控件参考 | 60 | | ASP数据库系统开发实例导航 | 60 | | Illustrator 10完全手册 | 50 | | FreeHand 10基础教程 | 50 | | 网站设计全程教程 |50 |降序：oredr by “排序的字段” desc
# select bName,price from books where price in (50,60,70) order by price desc,bName desc;多个字段排序
MySQL GROUP BY 语句
GROUP BY 语法
# SELECT column_name, function(column_name) # FROM table_name # WHERE column_name operator value # GROUP BY column_name;
实例演示
SET NAMES utf8; SET FOREIGN_KEY_CHECKS =0;
-- ----------------------------
-- Table structure for`employee_tbl`
-- ---------------------------- DROP TABLE IF EXISTS `employee_tbl`; CREATE TABLE `employee_tbl`(
`id`int(11) NOT NULL,
`name`char(10) NOT NULL DEFAULT ‘‘,
`date` datetime NOT NULL,
`singin` tinyint(4) NOT NULL DEFAULT ‘0‘ COMMENT ‘登录次数‘, PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of `employee_tbl`
-- ----------------------------BEGIN; INSERT INTO `employee_tbl` VALUES (‘1‘,‘小明‘,‘2016-04-22 15:25:33‘,‘1‘),
(‘2‘,‘小王‘,‘2016-04-20 15:25:47‘,‘3‘),(‘3‘,‘小丽‘,‘2016-04-19 15:26:02‘,‘2‘),
(‘4‘,‘小王‘,‘2016-04-07 15:26:14‘,‘4‘),(‘5‘,‘小明‘,‘2016-04-11 15:26:40‘,‘4‘),
(‘6‘,‘小明‘,‘2016-04-04 15:26:54‘,‘2‘); COMMIT; SET FOREIGN_KEY_CHECKS =1;
# mysql>set names utf8; # mysql> SELECT * FROM employee_tbl; +----+--------+------------------+--------+ | id | name | date | singin | +----+--------+------------------+--------+ |1 |小明 |2016-04-2215:25:33 |1| |2 |小王 |2016-04-2015:25:47 |3| |3 |小丽 |2016-04-1915:26:02 |2| |4 |小王 |2016-04-0715:26:14 |4| |5 |小明 |2016-04-1115:26:40 |4| |6 |小明 |2016-04-0415:26:54 |2| +----+--------+------------------+--------+ 6 rows inset(0.00 sec)
# mysql> SELECT name, COUNT(*) FROM employee_tbl GROUP BY name; +--------+----------+ | name | COUNT(*)| +--------+----------+ |小丽 | 1| |小明 | 3| |小王 | 2| +--------+----------+ 3 rows inset(0.01 sec)
使用 WITH ROLLUP
# mysql> SELECT name, SUM(singin)as singin_count FROM employee_tbl GROUP BY name WITH ROLLUP; +--------+--------------+ | name | singin_count | +--------+--------------+ |小丽 | 2| |小明 | 7| |小王 | 7| | NULL | 16| +--------+--------------+ 4 rows inset(0.00 sec)
# select coalesce(a,b,c);
# mysql> SELECT coalesce(name,‘总数‘), SUM(singin)as singin_count FROM employee_tbl GROUP BY name WITH ROLLUP; +--------------------------+--------------+
| coalesce(name,‘总数‘)| singin_count |
+--------------------------+--------------+
|小丽 | 2|
|小明 | 7|
|小王 | 7|
|总数 | 16|
+--------------------------+--------------+
4 rows inset(0.01 sec)
JOIN 按照功能大致分为如下三类：
示例：
常用的连接：
内连接：
# select 字段 from 表1 inner join 表2 on 表1.字段=表2.字段
# 测试 select a.bname,a.price,b.btypename from books a inner join category b on a.btypeid=b.btypeid; # 实际使用中inner可省略掉 # 跟WHERE 子句结果一样 select a.bname,a.price,b.btypename from books a, category b where a.btypeid=b.btypeid;
# 左连接： select 字段 from a表 left join b表 on 连接条件 # a表是主表，都显示。 # b表从表 # 主表内容全都有，从表内没有的显示null。 Select a.bname,a.price,b.btypename from books a left join category b on a.btypeid=b.btypeid;
# 右连接：select 字段 from a表 right join b表 on 条件 # a表是从表， # b表主表，都显示。 Select a.bname,b.* from books a right join category b on a.btypeid=b.btypeid; # 右连接，可以多表连接
inner join ... on select ... from 表A inner join 表B; select * from students inner join classes; # 查询 有能够对应班级的学生以及班级信息 select * from students inner join classes on students.cls_id = classes.id; # 按照要求显示姓名、班级 select students.name,classes.name from students inner join classes on students.cls_id = classes.id; # 给数据表起名字 select s.name,c.name from students as s inner join classes as c on s.cls_id = c.id; # 查询 有能够对应班级的学生以及班级信息，显示学生的所有信息 students.*，只显示班级名称 classes.name. select s.*,c.name from students as s inner join classes as c on s.cls_id = c.id; # 在以上的查询中，将班级姓名显示在第1列 select c.name,s.* from students as s inner join classes as c on s.cls_id = c.id; # 查询 有能够对应班级的学生以及班级信息, 按照班级进行排序 select c.xxx s.xxx from students as s inner join clssses as c on .... order by ....; select c.name,s.* from students as s inner join classes as c on s.cls_id = c.id order by c.name; # 当时同一个班级的时候，按照学生的id进行从小到大排序 select c.name,s.* from students as s inner join classes as c on s.cls_id = c.id order by c.name,id asc; # left join # 查询每位学生对应的班级信息 select * from students left join classes on students.cls_id = classes.id; # right join select * from students right join classes on students.cls_id = classes.id; # 查询没有对应班级信息的学生 # 语句 select ... from xxx as s left join xxx as c on..... where ..... select ... from xxx as s left join xxx as c on..... having ..... # 列 select * from students left join classes on students.cls_id = classes.id where classes.name is null; #(注意)不建议使用 select * from students left join classes on students.cls_id=classes.id having classes.id is null; # right join on # 将数据表名字互换位置，用left join完成应用示例
为了处理这种情况，MySQL提供了三大运算符:
# 注意： select * , columnName1+ifnull(columnName2,0) from tableName; # columnName1，columnName2 为 int 型，当 columnName2 中，有值为 null 时，columnName1+columnName2=null，
ifnull(columnName2,0) 把 columnName2 中 null 值转为 0。
案例
# 查询身高为空的信息 select * from students where height is null; # 判非空is not null select * from students where height is not null;
| 模式 | 描述 |
|---|---|
| ^ | 匹配输入字符串的开始位置。如果设置了 RegExp 对象的 Multiline 属性，^ 也匹配 ‘\n‘ 或 ‘\r‘ 之后的位置。 |
| $ | 匹配输入字符串的结束位置。如果设置了RegExp 对象的 Multiline 属性，$ 也匹配 ‘\n‘ 或 ‘\r‘ 之前的位置。 |
| . | 匹配除 "\n" 之外的任何单个字符。要匹配包括 ‘\n‘ 在内的任何字符，请使用象 ‘[.\n]‘ 的模式。 |
| [...] | 字符集合。匹配所包含的任意一个字符。例如， ‘[abc]‘ 可以匹配 "plain" 中的 ‘a‘。 |
| [^...] | 负值字符集合。匹配未包含的任意字符。例如， ‘[^abc]‘ 可以匹配 "plain" 中的‘p‘。 |
| p1|p2|p3 | 匹配 p1 或 p2 或 p3。例如，‘z|food‘ 能匹配 "z" 或 "food"。‘(z|f)ood‘ 则匹配 "zood" 或 "food"。 |
| * | 匹配前面的子表达式零次或多次。例如，zo* 能匹配 "z" 以及 "zoo"。* 等价于{0,}。 |
| + | 匹配前面的子表达式一次或多次。例如，‘zo+‘ 能匹配 "zo" 以及 "zoo"，但不能匹配 "z"。+ 等价于 {1,}。 |
| {n} | n 是一个非负整数。匹配确定的 n 次。例如，‘o{2}‘ 不能匹配 "Bob" 中的 ‘o‘，但是能匹配 "food" 中的两个 o。 |
| {n,m} | m 和 n 均为非负整数，其中n <= m。最少匹配 n 次且最多匹配 m 次。 |
示例
# 查找name字段中以‘st‘为开头的所有数据： mysql> SELECT name FROM person_tbl WHERE name REGEXP ‘^st‘; # 查找name字段中以‘ok‘为结尾的所有数据： mysql> SELECT name FROM person_tbl WHERE name REGEXP ‘ok$‘; # 查找name字段中包含‘mar‘字符串的所有数据： mysql> SELECT name FROM person_tbl WHERE name REGEXP ‘mar‘; # 查找name字段中以元音字符开头或以‘ok‘字符串结尾的所有数据： mysql> SELECT name FROM person_tbl WHERE name REGEXP ‘^[aeiou]|ok$‘;
MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你既需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务，详细了解可以看一下这篇【常识与进阶】！
一般来说，事务是必须满足4个条件（ACID）：：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。
# 在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。
因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SET AUTOCOMMIT=0，
用来禁止使用当前会话的自动提交。
事务控制语句：
MYSQL 事务处理主要有两种方法：
# mysql> use RUNOOB; Database changed # mysql> CREATE TABLE runoob_transaction_test( id int(5)) engine=innodb; # 创建数据表 Query OK, 0 rows affected (0.04 sec) # mysql> select * from runoob_transaction_test; Empty set (0.01 sec) # mysql> begin; # 开始事务 Query OK, 0 rows affected (0.00 sec) # mysql> insert into runoob_transaction_test value(5); Query OK, 1 rows affected (0.01 sec) # mysql> insert into runoob_transaction_test value(6); Query OK, 1 rows affected (0.00 sec) # mysql> commit; # 提交事务 Query OK, 0 rows affected (0.01 sec) # mysql> select * from runoob_transaction_test; +------+ | id | +------+ | 5 | | 6 | +------+ 2 rows in set (0.01 sec) # mysql> begin; # 开始事务 Query OK, 0 rows affected (0.00 sec) # mysql> insert into runoob_transaction_test values(7); Query OK, 1 rows affected (0.00 sec) # mysql> rollback; # 回滚 Query OK, 0 rows affected (0.00 sec) # mysql> select * from runoob_transaction_test; # 因为回滚所以数据没有插入 +------+ | id | +------+ | 5 | | 6 | +------+ 2 rows in set (0.01 sec) # mysql>事物测试
<?php $dbhost = ‘localhost:3306‘; // mysql服务器主机地址 $dbuser = ‘root‘; // mysql用户名 $dbpass = ‘123456‘; // mysql用户名密码 $conn = mysqli_connect($dbhost, $dbuser, $dbpass); if(! $conn ) { die(‘连接失败: ‘ . mysqli_error($conn)); } // 设置编码，防止中文乱码 mysqli_query($conn, "set names utf8"); mysqli_select_db( $conn, ‘RUNOOB‘ ); mysqli_query($conn, "SET AUTOCOMMIT=0"); // 设置为不自动提交，因为MYSQL默认立即执行 mysqli_begin_transaction($conn); // 开始事务定义 if(!mysqli_query($conn, "insert into runoob_transaction_test (id) values(8)")) { mysqli_query($conn, "ROLLBACK"); // 判断当执行失败时回滚 } if(!mysqli_query($conn, "insert into runoob_transaction_test (id) values(9)")) { mysqli_query($conn, "ROLLBACK"); // 判断执行失败时回滚 } mysqli_commit($conn); //执行事务 mysqli_close($conn); ?>PHP中使用事物示例
# root@host# mysql -u root -p password; Enter password:******* # mysql> use RUNOOB; Database changed # mysql> create table testalter_tbl -> ( -> i INT, -> c CHAR(1) -> ); Query OK, 0 rows affected (0.05 sec) # mysql> SHOW COLUMNS FROM testalter_tbl; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | i | int(11) | YES | | NULL | | | c | char(1) | YES | | NULL | | +-------+---------+------+-----+---------+-------+ 2 rows in set (0.00 sec)
删除，添加或修改表字段
# 如下命令使用了 ALTER 命令及 DROP 子句来删除以上创建表的 i 字段： mysql> ALTER TABLE testalter_tbl DROP i; # 如果数据表中只剩余一个字段则无法使用DROP来删除字段。 # MySQL 中使用 ADD 子句来向数据表中添加列，如下实例在表 testalter_tbl # 中添加 i 字段，并定义数据类型: mysql> ALTER TABLE testalter_tbl ADD i INT;
# mysql> SHOW COLUMNS FROM testalter_tbl; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | c | char(1) | YES | | NULL | | | i | int(11) | YES | | NULL | | +-------+---------+------+-----+---------+-------+ 2 rows in set (0.00 sec) # 如果你需要指定新增字段的位置，可以使用MySQL提供的关键字 FIRST (设定位第一列)， AFTER 字段名（设定位于某个字段之后）。
ALTER TABLE testalter_tbl DROP i; ALTER TABLE testalter_tbl ADD i INT FIRST; ALTER TABLE testalter_tbl DROP i; ALTER TABLE testalter_tbl ADD i INT AFTER c; # FIRST 和 AFTER 关键字可用于 ADD 与 MODIFY 子句，所以如果你想重置数据表字段的位置就需要先使用 DROP 删除字段然后使用
ADD 来添加字段并设置位置。
修改字段类型及名称
# 例如，把字段 c 的类型从 CHAR(1) 改为 CHAR(10)，可以执行以下命令: mysql> ALTER TABLE testalter_tbl MODIFY c CHAR(10); # 使用 CHANGE 子句, 语法有很大的不同。 在 CHANGE 关键字之后，紧跟着的是你要修改的字段名，然后指定新字段名及类型。尝试如下实例： mysql> ALTER TABLE testalter_tbl CHANGE i j BIGINT; mysql> ALTER TABLE testalter_tbl CHANGE j j INT;
ALTER TABLE 对 Null 值和默认值的影响
# 以下实例，指定字段 j 为 NOT NULL 且默认值为100 。 mysql> ALTER TABLE testalter_tbl -> MODIFY j BIGINT NOT NULL DEFAULT 100; # 如果你不设置默认值，MySQL会自动设置该字段默认为 NULL。
修改字段默认值
# mysql> ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000; # mysql> SHOW COLUMNS FROM testalter_tbl; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | c | char(1) | YES | | NULL | | | i | int(11) | YES | | 1000 | | +-------+---------+------+-----+---------+-------+ 2 rows in set (0.00 sec)你可以使用 ALTER 来修改字段的默认值，尝试以下实例：
