语法
# 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 |
# 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 |
# 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);
?>
# 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)
# mysql> ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;
# 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)
Changing a Table Type:
# mysql> ALTER TABLE testalter_tbl ENGINE = MYISAM;
# mysql> SHOW TABLE STATUS LIKE 'testalter_tbl'\G
# *************************** 1. row ****************
Name: testalter_tbl
Type: MyISAM
Row_format: Fixed
Rows: 0
Avg_row_length: 0
Data_length: 0
Max_data_length: 25769803775
Index_length: 1024
Data_free: 0
Auto_increment: NULL
Create_time: 2007-06-03 08:04:36
Update_time: 2007-06-03 08:04:36
Check_time: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
修改表名
# mysql> ALTER TABLE testalter_tbl RENAME TO alter_tbl;
创建普通索引
# 这是最基本的索引,它没有任何限制。它有以下几种创建方式:
CREATE INDEX indexName ON mytable(username(length));
# 如果是CHAR,VARCHAR类型,length可以小于字段实际长度;如果是BLOB和TEXT类型,必须指定 length。
修改表结构(添加索引)
# ALTER table tableName ADD INDEX indexName(columnName)
# 创建表的时候直接指定
CREATE TABLE mytable(
ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
INDEX [indexName] (username(length))
);
# 删除索引的语法
DROP INDEX [indexName] ON mytable;
创建唯一索引
# CREATE UNIQUE INDEX indexName ON mytable(username(length))
# 修改表结构
ALTER table mytable ADD UNIQUE [indexName] (username(length))
# 创建表的时候直接指定
CREATE TABLE mytable(
ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
UNIQUE [indexName] (username(length))
);
使用ALTER 命令添加和删除索引
#以下实例为在表中添加索引。
mysql> ALTER TABLE testalter_tbl ADD INDEX (c);
# 你还可以在 ALTER 命令中使用 DROP 子句来删除索引。尝试以下实例删除索引:
mysql> ALTER TABLE testalter_tbl DROP INDEX c;
使用 ALTER 命令添加和删除主键
# mysql> ALTER TABLE testalter_tbl MODIFY i INT NOT NULL;
# mysql> ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);
# 你也可以使用 ALTER 命令删除主键:
mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;
# 删除主键时只需指定PRIMARY KEY,但在删除索引时,你必须知道索引名。
显示索引信息
# 尝试以下实例:
mysql> SHOW INDEX FROM table_name; \G
........
示例
mysql> CREATE TEMPORARY TABLE SalesSummary (
-> product_name VARCHAR(50) NOT NULL
-> , total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00
-> , avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00
-> , total_units_sold INT UNSIGNED NOT NULL DEFAULT 0
);
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO SalesSummary
-> (product_name, total_sales, avg_unit_price, total_units_sold)
-> VALUES
-> ('cucumber', 100.25, 90, 2);
mysql> SELECT * FROM SalesSummary;
+--------------+-------------+----------------+------------------+
| product_name | total_sales | avg_unit_price | total_units_sold |
+--------------+-------------+----------------+------------------+
| cucumber | 100.25 | 90.00 | 2 |
+--------------+-------------+----------------+------------------+
1 row in set (0.00 sec)
删除MySQL 临时表
mysql> CREATE TEMPORARY TABLE SalesSummary (
-> product_name VARCHAR(50) NOT NULL
-> , total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00
-> , avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00
-> , total_units_sold INT UNSIGNED NOT NULL DEFAULT 0
);
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO SalesSummary
-> (product_name, total_sales, avg_unit_price, total_units_sold)
-> VALUES
-> ('cucumber', 100.25, 90, 2);
mysql> SELECT * FROM SalesSummary;
+--------------+-------------+----------------+------------------+
| product_name | total_sales | avg_unit_price | total_units_sold |
+--------------+-------------+----------------+------------------+
| cucumber | 100.25 | 90.00 | 2 |
+--------------+-------------+----------------+------------------+
1 row in set (0.00 sec)
mysql> DROP TABLE SalesSummary;
mysql> SELECT * FROM SalesSummary;
ERROR 1146: Table 'RUNOOB.SalesSummary' doesn't exist
如果我们需要完全的复制MySQL的数据表,包括表的结构,索引,默认值等。 如果仅仅使用CREATE TABLE ... SELECT 命令,是无法实现的。
实例
# mysql> SHOW CREATE TABLE runoob_tbl \G;
*************************** 1. row ***************************
Table: runoob_tbl
Create Table: CREATE TABLE `runoob_tbl` (
`runoob_id` int(11) NOT NULL auto_increment,
`runoob_title` varchar(100) NOT NULL default '',
`runoob_author` varchar(40) NOT NULL default '',
`submission_date` date default NULL,
PRIMARY KEY (`runoob_id`),
UNIQUE KEY `AUTHOR_INDEX` (`runoob_author`)
) ENGINE=InnoDB
1 row in set (0.00 sec)
ERROR:
No query specified
# mysql> CREATE TABLE `clone_tbl` (
-> `runoob_id` int(11) NOT NULL auto_increment,
-> `runoob_title` varchar(100) NOT NULL default '',
-> `runoob_author` varchar(40) NOT NULL default '',
-> `submission_date` date default NULL,
-> PRIMARY KEY (`runoob_id`),
-> UNIQUE KEY `AUTHOR_INDEX` (`runoob_author`)
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (1.80 sec)
# 执行完第二步骤后,你将在数据库中创建新的克隆表 clone_tbl。 如果你想拷贝数据表的数据你可以使用 INSERT INTO... SELECT 语句来实现。
mysql> INSERT INTO clone_tbl (runoob_id,
-> runoob_title,
-> runoob_author,
-> submission_date)
-> SELECT runoob_id,runoob_title,
-> runoob_author,submission_date
-> FROM runoob_tbl;
Query OK, 3 rows affected (0.07 sec)
Records: 3 Duplicates: 0 Warnings: 0
# 执行以上步骤后,你将完整的复制表,包括表结构及表数据。
使用 AUTO_INCREMENT
实例
mysql> CREATE TABLE insect
-> (
-> id INT UNSIGNED NOT NULL AUTO_INCREMENT,
-> PRIMARY KEY (id),
-> name VARCHAR(30) NOT NULL, # type of insect
-> date DATE NOT NULL, # date collected
-> origin VARCHAR(30) NOT NULL # where collected
);
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO insect (id,name,date,origin) VALUES
-> (NULL,'housefly','2001-09-10','kitchen'),
-> (NULL,'millipede','2001-09-10','driveway'),
-> (NULL,'grasshopper','2001-09-10','front yard');
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM insect ORDER BY id;
+----+-------------+------------+------------+
| id | name | date | origin |
+----+-------------+------------+------------+
| 1 | housefly | 2001-09-10 | kitchen |
| 2 | millipede | 2001-09-10 | driveway |
| 3 | grasshopper | 2001-09-10 | front yard |
+----+-------------+------------+------------+
3 rows in set (0.00 sec)
获取AUTO_INCREMENT值
# 使用 mysql_insertid 属性来获取 AUTO_INCREMENT 的值。 实例如下:
$dbh->do ("INSERT INTO insect (name,date,origin)
VALUES('moth','2001-09-14','windowsill')");
my $seq = $dbh->{mysql_insertid};
# PHP 通过 mysql_insert_id ()函数来获取执行的插入SQL语句中 AUTO_INCREMENT列的值。
mysql_query ("INSERT INTO insect (name,date,origin)
VALUES('moth','2001-09-14','windowsill')", $conn_id);
$seq = mysql_insert_id ($conn_id);
重置序列
# mysql> ALTER TABLE insect DROP id;
# mysql> ALTER TABLE insect
-> ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,
-> ADD PRIMARY KEY (id);
# mysql> CREATE TABLE insect
-> (
-> id INT UNSIGNED NOT NULL AUTO_INCREMENT,
-> PRIMARY KEY (id),
-> name VARCHAR(30) NOT NULL,
-> date DATE NOT NULL,
-> origin VARCHAR(30) NOT NULL
)engine=innodb auto_increment=100 charset=utf8;
# 或者你也可以在表创建成功后,通过以下语句来实现:
# mysql> ALTER TABLE t AUTO_INCREMENT = 100;
防止表中出现重复数据
CREATE TABLE person_tbl
(
first_name CHAR(20),
last_name CHAR(20),
sex CHAR(10)
);
# 如果你想设置表中字段 first_name,last_name 数据不能重复,你可以设置双主键模式来设置数据的唯一性, 如果你设置了双主键,
那么那个键的默认值不能为 NULL,可设置为 NOT NULL。如下所示:
CREATE TABLE person_tbl
(
first_name CHAR(20) NOT NULL,
last_name CHAR(20) NOT NULL,
sex CHAR(10),
PRIMARY KEY (last_name, first_name)
);
# mysql> INSERT IGNORE INTO person_tbl (last_name, first_name)
-> VALUES( 'Jay', 'Thomas');
Query OK, 1 row affected (0.00 sec)
# mysql> INSERT IGNORE INTO person_tbl (last_name, first_name)
-> VALUES( 'Jay', 'Thomas');
Query OK, 0 rows affected (0.00 sec)
CREATE TABLE person_tbl
(
first_name CHAR(20) NOT NULL,
last_name CHAR(20) NOT NULL,
sex CHAR(10),
UNIQUE (last_name, first_name)
);
统计重复数据
mysql> SELECT COUNT(*) as repetitions, last_name, first_name
-> FROM person_tbl
-> GROUP BY last_name, first_name
-> HAVING repetitions > 1;
# 如果你需要读取不重复的数据可以在 SELECT 语句中使用 DISTINCT 关键字来过滤重复数据。
# mysql> SELECT DISTINCT last_name, first_name
-> FROM person_tbl;
# 你也可以使用 GROUP BY 来读取数据表中不重复的数据:
# mysql> SELECT last_name, first_name
-> FROM person_tbl
-> GROUP BY (last_name, first_name);
# 如果你想删除数据表中的重复数据,你可以使用以下的SQL语句:
# mysql> CREATE TABLE tmp SELECTlast_name, first_name, sex FROM person_tblGROUP BY (last_name, first_name, sex);
# mysql> DROP TABLE person_tbl;
# mysql> ALTER TABLE tmp RENAME TO person_tbl;
# 当然你也可以在数据表中添加 INDEX(索引) 和 PRIMAY KEY(主键)这种简单的方法来删除表中的重复记录。方法如下:
# mysql> ALTER IGNORE TABLE person_tbl
-> ADD PRIMARY KEY (last_name, first_name);