mysql查询

查询数据：
字符串查询一定加””否则不走索引

mysql> SELECT name,id FROM test LIMIT 2;
mysql> SELECT name,id FROM test ORDER BY id ASC LIMIT 2;
mysql> SELECT name,id FROM test WHERE id=1;
mysql> SELECT name,id FROM test WHERE id=1 and name=”zhang zhang”;
mysql> SELECT name,id FROM test WHERE id=5 or name=”zhang zhang”;
mysql> SELECT name,id FROM test WHERE id<2 or id>4;

多表查询：
mysql> create database zabbix character set = utf8 collate = utf8_general_ci;
Query OK, 1 row affected (0.00 sec)

mysql> use zabbix;
Database changed
mysql> set names utf8;
Query OK, 0 rows affected (0.00 sec)

DROP TABLE student;
CREATE TABLE student(
Sno int(10) NOT NULL COMMENT ‘学号’,
Sname char(10) NOT NULL COMMENT ‘姓名’,
Ssex char(2) NOT NULL COMMENT ‘性别’,
Sage tinyint(2) NOT NULL COMMENT ‘年龄’,
Sdept varchar(16) DEFAULT NULL COMMENT ‘学生所在系别’,
PRIMARY KEY (Sno),
KEY index_name (Sname)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET = utf8;
DROP TABLE course;
CREATE TABLE `course`(
`Cno` int(10) NOT NULL COMMENT ‘课程号’,
`Cname` varchar(64) NOT NULL COMMENT ‘课程名’,
`Ccredit` tinyint(2) NOT NULL COMMENT ‘学分’,
PRIMARY KEY (`Cno`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

DROP TABLE `SC`;
CREATE TABLE `SC`(
`SCid` int(12) NOT NULL AUTO_INCREMENT COMMENT ‘主键’,
`Cno` int(10) NOT NULL COMMENT’课程号’,
`Sno` int(10) NOT NULL COMMENT ‘学号’,
`Grade` tinyint(2) NOT NULL COMMENT ‘学生成绩’,
PRIMARY KEY (`SCid`)
) ENGINE=InnoDB DEFAULT CHARSET = utf8;

INSERT INTO student (Sno,Sname,Ssex,Sage,Sdept) VALUES
(0001,’李宏志’,’男’,30,’计算机网络’),
(0002,’张无忌’,’男’,30,’计算机网络’),
(0003,’badboy’,’男’,28,’计算机网络’),
(0004,’郭靖’,’男’,29,’计算机网络’),
(0005,’黄蓉’,’女’,26,’计算机网络’)
;

INSERT INTO `course` (`Cno`,`Cname`,`Ccredit`) VALUES
(1001,’LINUX 中高级运维’,3),
(1002,’LINUX 构架师’,5),
(1003,’MySQL 高级Dba’,4),
(1004,’Python 运维开发’,4),
(1005,’Jave web 开发’,3)
;

INSERT INTO `SC` (`Sno`,`Cno`,`Grade`) VALUES

(0001,’1001′,4),
(0001,’1002′,3),
(0001,’1003′,1),
(0001,’1004′,6),

(0002,’1001′,4),
(0002,’1002′,4),
(0002,’1003′,2),
(0002,’1004′,8),

(0003,’1001′,4),
(0003,’1002′,4),
(0003,’1003′,2),
(0003,’1004′,8),

(0004,’1001′,1),
(0004,’1002′,2),
(0004,’1003′,2),
(0004,’1004′,3),

(0005,’1001′,5),
(0005,’1002′,3),
(0005,’1003′,2),
(0005,’1004′,9);

mysql> select S.Sname,S.Sno,SC.Grade,C.CNAME from student AS S,SC,COURSE AS C where S.Sno = SC.Sno andd C.Cno = SC.Cno;

mysql> explain select * from student where Sname=’badboy’ \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: student
type: ref
possible_keys: index_name
key: index_name ###使用了索引
key_len: 30
ref: const
rows: 1
Extra: Using index condition
1 row in set (0.00 sec)