Group by
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-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 |
+----+--------+---------------------+--------+
6 rows in set (0.00 sec)
mysql> SELECT name, COUNT(*) FROM employee_tbl GROUP BY name;
+--------+----------+
| name | COUNT(*) |
+--------+----------+
| 小丽 | 1 |
| 小明 | 3 |
| 小王 | 2 |
+--------+----------+
Inner join
mysql> SELECT * FROM tcount_tbl;
+-----------------+----------------+
| runoob_author | runoob_count |
+-----------------+----------------+
| mahran | 20 |
| mahnaz | NULL |
| Jen | NULL |
| Gill | 20 |
| John Poul | 1 |
| Sanjay | 1 |
+-----------------+----------------+
6 rows in set (0.01 sec)
mysql> SELECT * from runoob_tbl;
+-------------+----------------+-----------------+-----------------+
| runoob_id | runoob_title | runoob_author | submission_date |
+-------------+----------------+-----------------+-----------------+
| 1 | Learn PHP | John Poul | 2007-05-24 |
| 2 | Learn MySQL | Abdul S | 2007-05-24 |
| 3 | JAVA Tutorial | Sanjay | 2007-05-06 |
+-------------+----------------+-----------------+-----------------+
3 rows in set (0.00 sec)
mysql>
mysql> SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a INNER JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;
+-----------+---------------+--------------+
| runoob_id | runoob_author | runoob_count |
+-----------+---------------+--------------+
| 1 | John Poul | 1 |
| 3 | Sanjay | 1 |
+-----------+---------------+--------------+
2 rows in set (0.00 sec)
< Inner join可以省略用where>
left join
MySQL left join 与 join 有所不同。 MySQL LEFT JOIN 会读取左边数据表的全部数据，即便右边表无对应数据。
mysql> SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a LEFT JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;
+-------------+-----------------+----------------+
| runoob_id | runoob_author | runoob_count |
+-------------+-----------------+----------------+
| 1 | John Poul | 1 |
| 2 | Abdul S | NULL |
| 3 | Sanjay | 1 |
+-------------+-----------------+----------------+
3 rows in set (0.02 sec)
RIGHT JOIN
MySQL RIGHT JOIN 会读取右边数据表的全部数据，即便左边边表无对应数据
mysql> SELECT b.runoob_id, b.runoob_author, a.runoob_count FROM tcount_tbl a RIGHT JOIN runoob_tbl b ON a.runoob_author = b.runoob_author;
+-------------+-----------------+----------------+
| runoob_id | runoob_author | runoob_count |
+-------------+-----------------+----------------+
| 1 | John Poul | 1 |
| 2 | Abdul S | NULL |
| 3 | Sanjay | 1 |
+-------------+-----------------+----------------+
3 rows in set (0.02 sec)
我们已经知道MySQL使用 SQL SELECT 命令及 WHERE 子句来读取数据表中的数据,但是当提供的查询条件字段为 NULL 时，该命令可能就无法正常工作。
为了处理这种情况，MySQL提供了三大运算符:
IS NULL: 当列的值是NULL,此运算符返回true。IS NOT NULL: 当列的值不为NULL, 运算符返回true。<=>: 比较操作符（不同于=运算符），当比较的的两个值为NULL时返回true。
关于 NULL 的条件比较运算是比较特殊的。你不能使用 = NULL 或 != NULL 在列中查找 NULL 值 。
在MySQL中，NULL值与任何其它值的比较（即使是NULL）永远返回false，即 NULL = NULL 返回false 。
MySQL中处理NULL使用IS NULL和IS NOT NULL运算符。
ID lastname firstname address year city
1 Adams John Oxford street 1970 London
2 Bush George Fifth avenue 1975 New York
3 Carter Thomas Changan Street 1980 Beijing
4 Gates Bill Xuanwumen 10 1985 Beijing
Order by [desc|asc]
LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式。
提示："%" 可用于定义通配符（模式中缺少的字母）。
通过使用 NOT 关键字，我们可以从 "Persons" 表中选取居住在不包含 "lon" 的城市里的人：
我们可以使用下面的 SELECT 语句：
SELECT * FROM person
WHERE City NOT LIKE '%lon%'
在搜索数据库中的数据时，SQL 通配符可以替代一个或多个字符。
SQL 通配符必须与 LIKE 运算符一起使用。
在 SQL 中，可使用以下通配符：
通配符 描述
% 替代一个或多个字符
_ 仅替代一个字符
[charlist] 字符列中的任何单一字符
[^charlist]或者[! charlist] 不在字符列中的任何单一字符
现在，我们希望从上面的 "Persons" 表中选取居住的城市以 "A" 或 "L" 或 "N" 开头的人：
SELECT * FROM Persons
WHERE City LIKE '[ALN]%'
IN 操作符允许我们在 WHERE 子句中规定多个值。
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...)
操作符 BETWEEN ... AND 会选取介于两个值之间的数据范围。这些值可以是数值、文本或者日期。
SQL BETWEEN 语法
SELECT column_name(s)
FROM table_name
WHERE column_name
BETWEEN value1 AND value2
SELECT column_name(s)
FROM table_name
AS alias_name
列的 SQL Alias 语法(表也可以)
SELECT column_name AS alias_name
FROM table_name
SELECT column_name(s)
FROM table_name
AS alias_name
函数
函数 描述 AVG() CONUT() Max() Min() Sum() sqrt 这是用来生成给定数的平方根 RAND() 使用MySQL命令产生一个随机数 CONCAT() - 这是用来连接MySQL命令中的任何字符串 DATE和TIME
MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你即需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！
在MySQL中只有使用了Innodb数据库引擎的数据库或表才支持事务事务处理可以用来维护数据库的完整性，保证成批的SQL语句要么全部执行，要么全部不执行事务用来管理insert,update,delete语句
mysql的索引分为单列索引(主键索引,唯索引,普通索引)和组合索引.
单列索引:一个索引只包含一个列,一个表可以有多个单列索引.
组合索引:一个组合索引包含两个或两个以上的列,
1. 单列索引
1-1) 其sql格式是 CREATE INDEX IndexName ON `TableName`(`字段名`(length)) 或者 ALTER TABLE TableName ADD INDEX IndexName(`字段名`(length))
CREATE TABLE `award` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户id',
`aty_id` varchar(100) NOT NULL DEFAULT '' COMMENT '活动场景id',
`nickname` varchar(12) NOT NULL DEFAULT '' COMMENT '用户昵称',
`is_awarded` tinyint(1) NOT NULL DEFAULT 0 COMMENT '用户是否领奖',
`award_time` int(11) NOT NULL DEFAULT 0 COMMENT '领奖时间',
`account` varchar(12) NOT NULL DEFAULT '' COMMENT '帐号',
`password` char(32) NOT NULL DEFAULT '' COMMENT '密码',
`message` varchar(255) NOT NULL DEFAULT '' COMMENT '获奖信息',
`created_time` int(11) NOT NULL DEFAULT 0 COMMENT '创建时间',
`updated_time` int(11) NOT NULL DEFAULT 0 COMMENT '更新时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='获奖信息表';
CREATE INDEX account_Index ON `award`(`account`);
ALTER TABLE award ADD INDEX account_Index(`account`)
注释：award是表名，account是列名（字段名）
如果是CHAR,VARCHAR,类型,length可以小于字段的实际长度,如果是BLOB和TEXT类型就必须指定长度
1-2)唯一索引,与普通索引类似,但是不同的是唯一索引要求所有的类的值是唯一的,这一点和主键索引一样.但是他允许有空值,
其sql格式是 CREATE UNIQUE INDEX IndexName ON `TableName`(`字段名`(length)); 或者 ALTER TABLE TableName ADD UNIQUE (column_list)
1-3) 主键索引,不允许有空值,(在B+TREE中的InnoDB引擎中,主键索引起到了至关重要的地位)
主键索引建立的规则是 int优于varchar,一般在建表的时候创建,最好是与表的其他字段不相关的列或者是业务不相关的列.一般会设为 int 而且是 AUTO_INCREMENT自增类型的
2.组合索引
一个表中含有多个单列索引不代表是组合索引,通俗一点讲 组合索引是:包含多个字段但是只有索引名称
其sql格式是 CREATE INDEX IndexName On `TableName`(`字段名`(length),`字段名`(length),...);
CREATE INDEX nickname_account_createdTime_Index ON `award`(`nickname`, `account`, `created_time`);
1、不按索引最左列开始查询（多列索引） 例如index(‘c1’, ‘c2’, ‘c3’) where ‘c2’ = ‘aaa’ 不使用索引,where `c2` = `aaa` and `c3`=`sss` 不能使用索引
2、查询中某个列有范围查询，则其右边的所有列都无法使用查询（多列查询）
Where c1= ‘xxx’ and c2 like = ‘aa%’ and c3=’sss’ 改查询只会使用索引中的前两列,因为like是范围查询
3、不能跳过某个字段来进行查询,这样利用不到索引,
select * from `award` where nickname > 'rSUQFzpkDz3R' and account = 'DYxJoqZq2rd7' and created_time = 1449567822; 那么这时候他使用不到其组合索引.
因为我的索引是 (nickname, account, created_time),如果第一个字段出现 范围符号的查找,那么将不会用到索引,如果我是第二个或者第三个字段使用范围符号的查找,那么他会利用索引,利用的索引是(nickname),
因为上面说了建立组合索引(nickname, account, created_time), 会出现三个索引
(3)全文索引
文本字段上(text)如果建立的是普通索引,那么只有对文本的字段内容前面的字符进行索引,其字符大小根据索引建立索引时申明的大小来规定.
如果文本中出现多个一样的字符,而且需要查找的话,那么其条件只能是 where column lick '%xxxx%' 这样做会让索引失效
.这个时候全文索引就祈祷了作用了
ALTER TABLE tablename ADD FULLTEXT(column1, column2)
有了全文索引，就可以用SELECT查询命令去检索那些包含着一个或多个给定单词的数据记录了。
ELECT * FROM tablename
WHERE MATCH(column1, column2) AGAINST(‘xxx′, ‘sss′, ‘ddd′)
这条命令将把column1和column2字段里有xxx、sss和ddd的数据记录全部查询出来
(二)索引的删除
删除索引的mysql格式 :DORP INDEX IndexName ON `TableName`
id name sex birth department adress
901 张老大 1 1985 计算机系 北京市海淀区902 张老二 1 1986 中文系 北京市昌平区903 张三 0 1990 中文系 湖南省永州市904 李四 1 1990 英语系 辽宁省阜新市905 王五 1 1991 英语系 福建省厦门市906 王六 1 1988 计算机系 湖南省衡阳市
id stu_id c_name grade
1 901 计算机 982 901 英语 803 902 计算机 654 902 中文 885 903 中文 956 904 计算机 707 904 英语 928 905 英语 949 906 计算机 9010 906 英语 85
查询同时参加计算机和英语考试的学生的信息
方式一:
SELECT a.* FROM student a ,score b ,score c WHERE a.id=b.stu_id AND b.c_name='计算机' AND a.id=c.stu_id AND c.c_name='英语';
方式二:
SELECT * FROM student WHERE id =ANY ( SELECT stu_id FROM score WHERE stu_id IN ( SELECT stu_id FROM score WHERE c_name= '计算机') AND c_name= '英语' );
方式三:
select * from student where id in( select s.stu_id from (select stu_id from score where c_name = '计算机') s
(select stu_id from score where c_name='英语') as t where s.stu_id=t.stu_id)
方式四:
select * from student where id in (
select stu_id from score where c_name ='计算机' and stu_id in(
select stu_id from score where c_name ='英语'));
将计算机考试成绩按从高到低进行排序
SELECT stu_id , grade from score WHERE c_name='计算机' ORDER BY grade DESC;
从student表和score表中查询出学生的学号，然后合并查询结果
SELECT id from student UNION SELECT stu_id FROM score;
查询姓张或者姓王的同学的姓名、院系和考试科目及成绩
SELECT s.id,s.`name`,s.birth,s.department,s.adress,sc.c_name,sc.grade from student s LEFT JOIN score sc on s.id=sc.stu_id WHERE name LIKE "张%" OR name LIKE "王%";
查询都是湖南的学生的姓名、年龄、院系和考试科目及成绩
SELECT s.id,s.`name`,s.birth,s.department,s.adress,sc.c_name,sc.grade from student s LEFT JOIN score sc on s.id=sc.stu_id WHERE s.adress LIKE "湖南%";
转载于:https://www.cnblogs.com/JohnsonZilch/p/6548293.html
