cmd-Mysql学习
一、DML语句
create table student(
s_id int PRIMARY key auto_increment,
s_name VAECHAR(20)not null,
s_birthday VARCHAR（20）not null,
s_birthday VARCHAR（20）not null);
mysql> CREATE TABLE student(s_id int PRIMARY key auto_increment,s_name VARCHAR(20) not null,s_birthday VARCHAR(20) not null,s_sex VARCHAR(20) not null);
Query OK, 0 rows affected (0.04 sec)
create table course(
c_id int auto_increment,
c_name VARCHAR(20)not null,
t_id int not null,
primary key(c_id)
);
create table course( c_id int auto_increment, c_name VARCHAR(20)not null, t_id int not null, primary key(c_id) );
Query OK, 0 rows affected (0.03 sec)
CREATE TABLE teacher(
t_id int primary key auto_increment ,
t_name VARCHAR(20) not null
);
mysql> CREATE TABLE teacher(t_id int primary key auto_increment ,t_name VARCHAR(20) not null);
Query OK, 0 rows affected (0.03 sec)
mysql> CREATE TABLE score(
-> s_id int ,
-> c_id int ,
-> s_score FLOAT(4,2),
-> PRIMARY KEY(s_id,c_id)
-> );
Query OK, 0 rows affected (0.04 sec)
insert into student values('01' , '赵雷' , '1990-01-01', '男');
insert into student values('02' , '钱电' , '1990-12-21' , '男');
insert into student values('03' , '孙风' , '1990-05-20' , '男');
insert into student values('04' , '李云' , '1990-08-06' , '男');
insert into student values('05' , '周梅' , '1991-12-01' , '女');
insert into student values('06' , '吴兰' , '1992-03-01' , '女');
insert into student values('07' , '郑竹' , '1989-07-01' , '女');
insert into student values('08' , '王菊' , '1990-01-20' , '女');
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
insert into Score values('01' , '01' , 80);
insert into Score values('01' , '02' , 90);
insert into Score values('01' , '03' , 99);
insert into Score values('02' , '01' , 70);
insert into Score values('02' , '02' , 60);
insert into Score values('02' , '03' , 80);
insert into Score values('03' , '01' , 80);
insert into Score values('03' , '02' , 80);
insert into Score values('03' , '03' , 80);
insert into Score values('04' , '01' , 50);
insert into Score values('04' , '02' , 30);
insert into Score values('04' , '03' , 20);
insert into Score values('05' , '01' , 76);
insert into Score values('05' , '02' , 87);
insert into Score values('06' , '01' , 31);
insert into Score values('06' , '03' , 34);
insert into Score values('07' , '02' , 89);
insert into Score values('07' , '03' , 98);
mysql> select * from student;
+------+--------+------------+-------+
| s_id | s_name | s_birthday | s_sex |
+------+--------+------------+-------+
| 1 | 赵雷 | 1990-01-01 | 男 |
| 2 | 钱电 | 1990-12-21 | 男 |
| 3 | 孙风 | 1990-05-20 | 男 |
| 4 | 李云 | 1990-08-06 | 男 |
| 5 | 周梅 | 1991-12-01 | 女 |
| 6 | 吴兰 | 1992-03-01 | 女 |
| 7 | 郑竹 | 1989-07-01 | 女 |
| 8 | 王菊 | 1990-01-20 | 女 |
+------+--------+------------+-------+
8 rows in set (0.00 sec)
mysql> select * from score;
+------+------+---------+
| s_id | c_id | s_score |
+------+------+---------+
| 1 | 1 | 80.00 |
| 1 | 2 | 90.00 |
| 1 | 3 | 99.00 |
| 2 | 1 | 70.00 |
| 2 | 2 | 60.00 |
| 2 | 3 | 80.00 |
| 3 | 1 | 80.00 |
| 3 | 2 | 80.00 |
| 3 | 3 | 80.00 |
| 4 | 1 | 50.00 |
| 4 | 2 | 30.00 |
| 4 | 3 | 20.00 |
| 5 | 1 | 76.00 |
| 5 | 2 | 87.00 |
| 6 | 1 | 31.00 |
| 6 | 3 | 34.00 |
| 7 | 2 | 89.00 |
| 7 | 3 | 98.00 |
+------+------+---------+
18 rows in set (0.01 sec)
select * from student a left join score b on (a.s_id=b.s_id);
+------+--------+------------+-------+------+------+---------+
| s_id | s_name | s_birthday | s_sex | s_id | c_id | s_score |
+------+--------+------------+-------+------+------+---------+
| 1 | 赵雷 | 1990-01-01 | 男 | 1 | 1 | 80.00 |
| 1 | 赵雷 | 1990-01-01 | 男 | 1 | 2 | 90.00 |
| 1 | 赵雷 | 1990-01-01 | 男 | 1 | 3 | 99.00 |
| 2 | 钱电 | 1990-12-21 | 男 | 2 | 1 | 70.00 |
| 2 | 钱电 | 1990-12-21 | 男 | 2 | 2 | 60.00 |
| 2 | 钱电 | 1990-12-21 | 男 | 2 | 3 | 80.00 |
| 3 | 孙风 | 1990-05-20 | 男 | 3 | 1 | 80.00 |
| 3 | 孙风 | 1990-05-20 | 男 | 3 | 2 | 80.00 |
| 3 | 孙风 | 1990-05-20 | 男 | 3 | 3 | 80.00 |
| 4 | 李云 | 1990-08-06 | 男 | 4 | 1 | 50.00 |
| 4 | 李云 | 1990-08-06 | 男 | 4 | 2 | 30.00 |
| 4 | 李云 | 1990-08-06 | 男 | 4 | 3 | 20.00 |
| 5 | 周梅 | 1991-12-01 | 女 | 5 | 1 | 76.00 |
| 5 | 周梅 | 1991-12-01 | 女 | 5 | 2 | 87.00 |
| 6 | 吴兰 | 1992-03-01 | 女 | 6 | 1 | 31.00 |
| 6 | 吴兰 | 1992-03-01 | 女 | 6 | 3 | 34.00 |
| 7 | 郑竹 | 1989-07-01 | 女 | 7 | 2 | 89.00 |
| 7 | 郑竹 | 1989-07-01 | 女 | 7 | 3 | 98.00 |
| 8 | 王菊 | 1990-01-20 | 女 | NULL | NULL | NULL |
+------+--------+------------+-------+------+------+---------+
19 rows in set (0.00 sec)
student和score做左连接，当条件匹配不成功时，右表对应项为null
mysql> select * from student a right join score b on (a.s_id=b.s_id)
-> ;
+------+--------+------------+-------+------+------+---------+
| s_id | s_name | s_birthday | s_sex | s_id | c_id | s_score |
+------+--------+------------+-------+------+------+---------+
| 1 | 赵雷 | 1990-01-01 | 男 | 1 | 1 | 80.00 |
| 1 | 赵雷 | 1990-01-01 | 男 | 1 | 2 | 90.00 |
| 1 | 赵雷 | 1990-01-01 | 男 | 1 | 3 | 99.00 |
| 2 | 钱电 | 1990-12-21 | 男 | 2 | 1 | 70.00 |
| 2 | 钱电 | 1990-12-21 | 男 | 2 | 2 | 60.00 |
| 2 | 钱电 | 1990-12-21 | 男 | 2 | 3 | 80.00 |
| 3 | 孙风 | 1990-05-20 | 男 | 3 | 1 | 80.00 |
| 3 | 孙风 | 1990-05-20 | 男 | 3 | 2 | 80.00 |
| 3 | 孙风 | 1990-05-20 | 男 | 3 | 3 | 80.00 |
| 4 | 李云 | 1990-08-06 | 男 | 4 | 1 | 50.00 |
| 4 | 李云 | 1990-08-06 | 男 | 4 | 2 | 30.00 |
| 4 | 李云 | 1990-08-06 | 男 | 4 | 3 | 20.00 |
| 5 | 周梅 | 1991-12-01 | 女 | 5 | 1 | 76.00 |
| 5 | 周梅 | 1991-12-01 | 女 | 5 | 2 | 87.00 |
| 6 | 吴兰 | 1992-03-01 | 女 | 6 | 1 | 31.00 |
| 6 | 吴兰 | 1992-03-01 | 女 | 6 | 3 | 34.00 |
| 7 | 郑竹 | 1989-07-01 | 女 | 7 | 2 | 89.00 |
| 7 | 郑竹 | 1989-07-01 | 女 | 7 | 3 | 98.00 |
+------+--------+------------+-------+------+------+---------+
18 rows in set (0.00 sec)
右连接
mysql> select * from score a right join student b on (a.s_id=b.s_id);
+------+------+---------+------+--------+------------+-------+
| s_id | c_id | s_score | s_id | s_name | s_birthday | s_sex |
+------+------+---------+------+--------+------------+-------+
| 1 | 1 | 80.00 | 1 | 赵雷 | 1990-01-01 | 男 |
| 1 | 2 | 90.00 | 1 | 赵雷 | 1990-01-01 | 男 |
| 1 | 3 | 99.00 | 1 | 赵雷 | 1990-01-01 | 男 |
| 2 | 1 | 70.00 | 2 | 钱电 | 1990-12-21 | 男 |
| 2 | 2 | 60.00 | 2 | 钱电 | 1990-12-21 | 男 |
| 2 | 3 | 80.00 | 2 | 钱电 | 1990-12-21 | 男 |
| 3 | 1 | 80.00 | 3 | 孙风 | 1990-05-20 | 男 |
| 3 | 2 | 80.00 | 3 | 孙风 | 1990-05-20 | 男 |
| 3 | 3 | 80.00 | 3 | 孙风 | 1990-05-20 | 男 |
| 4 | 1 | 50.00 | 4 | 李云 | 1990-08-06 | 男 |
| 4 | 2 | 30.00 | 4 | 李云 | 1990-08-06 | 男 |
| 4 | 3 | 20.00 | 4 | 李云 | 1990-08-06 | 男 |
| 5 | 1 | 76.00 | 5 | 周梅 | 1991-12-01 | 女 |
| 5 | 2 | 87.00 | 5 | 周梅 | 1991-12-01 | 女 |
| 6 | 1 | 31.00 | 6 | 吴兰 | 1992-03-01 | 女 |
| 6 | 3 | 34.00 | 6 | 吴兰 | 1992-03-01 | 女 |
| 7 | 2 | 89.00 | 7 | 郑竹 | 1989-07-01 | 女 |
| 7 | 3 | 98.00 | 7 | 郑竹 | 1989-07-01 | 女 |
| NULL | NULL | NULL | 8 | 王菊 | 1990-01-20 | 女 |
+------+------+---------+------+--------+------------+-------+
19 rows in set (0.00 sec)
score右连接student 相当于student左连接score
mysql> select * from score a inner join student b on (a.s_id=b.s_id);
+------+------+---------+------+--------+------------+-------+
| s_id | c_id | s_score | s_id | s_name | s_birthday | s_sex |
+------+------+---------+------+--------+------------+-------+
| 1 | 1 | 80.00 | 1 | 赵雷 | 1990-01-01 | 男 |
| 1 | 2 | 90.00 | 1 | 赵雷 | 1990-01-01 | 男 |
| 1 | 3 | 99.00 | 1 | 赵雷 | 1990-01-01 | 男 |
| 2 | 1 | 70.00 | 2 | 钱电 | 1990-12-21 | 男 |
| 2 | 2 | 60.00 | 2 | 钱电 | 1990-12-21 | 男 |
| 2 | 3 | 80.00 | 2 | 钱电 | 1990-12-21 | 男 |
| 3 | 1 | 80.00 | 3 | 孙风 | 1990-05-20 | 男 |
| 3 | 2 | 80.00 | 3 | 孙风 | 1990-05-20 | 男 |
| 3 | 3 | 80.00 | 3 | 孙风 | 1990-05-20 | 男 |
| 4 | 1 | 50.00 | 4 | 李云 | 1990-08-06 | 男 |
| 4 | 2 | 30.00 | 4 | 李云 | 1990-08-06 | 男 |
| 4 | 3 | 20.00 | 4 | 李云 | 1990-08-06 | 男 |
| 5 | 1 | 76.00 | 5 | 周梅 | 1991-12-01 | 女 |
| 5 | 2 | 87.00 | 5 | 周梅 | 1991-12-01 | 女 |
| 6 | 1 | 31.00 | 6 | 吴兰 | 1992-03-01 | 女 |
| 6 | 3 | 34.00 | 6 | 吴兰 | 1992-03-01 | 女 |
| 7 | 2 | 89.00 | 7 | 郑竹 | 1989-07-01 | 女 |
| 7 | 3 | 98.00 | 7 | 郑竹 | 1989-07-01 | 女 |
+------+------+---------+------+--------+------------+-------+
18 rows in set (0.00 sec)
内连接，也就是全连接吗
mysql> select * from student a left join score b on (a.s_id=b.s_id) where b.s_id is null;
+------+--------+------------+-------+------+------+---------+
| s_id | s_name | s_birthday | s_sex | s_id | c_id | s_score |
+------+--------+------------+-------+------+------+---------+
| 8 | 王菊 | 1990-01-20 | 女 | NULL | NULL | NULL |
+------+--------+------------+-------+------+------+---------+
1 row in set (0.00 sec)
求student右连接score表得到整个结果集中中s_id列为null的值
mysql> select * from student a left join score b on (a.s_id = b.s_id) where a.s_id is null
-> union select * from student a left join score b on (a.s_id = b.s_id) where b.s_id is null;
+------+--------+------------+-------+------+------+---------+
| s_id | s_name | s_birthday | s_sex | s_id | c_id | s_score |
+------+--------+------------+-------+------+------+---------+
| 8 | 王菊 | 1990-01-20 | 女 | NULL | NULL | NULL |
+------+--------+------------+-------+------+------+---------+
1 row in set (0.00 sec)
mysql> select * from student a left join score b on (a.s_id = b.s_id) where b.s_id is null
-> union select * from student a left join score b on (a.s_id = b.s_id) where b.s_id is null;
+------+--------+------------+-------+------+------+---------+
| s_id | s_name | s_birthday | s_sex | s_id | c_id | s_score |
+------+--------+------------+-------+------+------+---------+
| 8 | 王菊 | 1990-01-20 | 女 | NULL | NULL | NULL |
+------+--------+------------+-------+------+------+---------+
1 row in set (0.00 sec)
union用法
select s_id from student
-> union select s_id from score;
+------+
| s_id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
+------+
8 rows in set (0.00 sec)
union使用前提是两个表有相同的列数据，union能够自动去重
列出在学生表和成绩表中所有学生的学号
mysql> select s_id from student union all select s_id from score ;
+------+
| s_id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 1 |
| 1 |
| 1 |
| 2 |
| 2 |
| 2 |
| 3 |
| 3 |
| 3 |
| 4 |
| 4 |
| 4 |
| 5 |
| 5 |
| 6 |
| 6 |
| 7 |
| 7 |
+------+
26 rows in set (0.00 sec)
union all不能自动去重
mysql> select * from student corss join score;
+------+--------+------------+-------+------+------+---------+
| s_id | s_name | s_birthday | s_sex | s_id | c_id | s_score |
+------+--------+------------+-------+------+------+---------+
| 1 | 赵雷 | 1990-01-01 | 男 | 1 | 1 | 80.00 |
| 2 | 钱电 | 1990-12-21 | 男 | 1 | 1 | 80.00 |
| 3 | 孙风 | 1990-05-20 | 男 | 1 | 1 | 80.00 |
| 4 | 李云 | 1990-08-06 | 男 | 1 | 1 | 80.00 |
| 5 | 周梅 | 1991-12-01 | 女 | 1 | 1 | 80.00 |
| 6 | 吴兰 | 1992-03-01 | 女 | 1 | 1 | 80.00 |
| 7 | 郑竹 | 1989-07-01 | 女 | 1 | 1 | 80.00 |
| 8 | 王菊 | 1990-01-20 | 女 | 1 | 1 | 80.00 |
| 1 | 赵雷 | 1990-01-01 | 男 | 1 | 2 | 90.00 |
| 2 | 钱电 | 1990-12-21 | 男 | 1 | 2 | 90.00 |
| 3 | 孙风 | 1990-05-20 | 男 | 1 | 2 | 90.00 |
| 4 | 李云 | 1990-08-06 | 男 | 1 | 2 | 90.00 |
| 5 | 周梅 | 1991-12-01 | 女 | 1 | 2 | 90.00 |
| 6 | 吴兰 | 1992-03-01 | 女 | 1 | 2 | 90.00 |
| 7 | 郑竹 | 1989-07-01 | 女 | 1 | 2 | 90.00 |
| 8 | 王菊 | 1990-01-20 | 女 | 1 | 2 | 90.00 |
| 1 | 赵雷 | 1990-01-01 | 男 | 1 | 3 | 99.00 |
| 2 | 钱电 | 1990-12-21 | 男 | 1 | 3 | 99.00 |
| 3 | 孙风 | 1990-05-20 | 男 | 1 | 3 | 99.00 |
| 4 | 李云 | 1990-08-06 | 男 | 1 | 3 | 99.00 |
| 5 | 周梅 | 1991-12-01 | 女 | 1 | 3 | 99.00 |
| 6 | 吴兰 | 1992-03-01 | 女 | 1 | 3 | 99.00 |
| 7 | 郑竹 | 1989-07-01 | 女 | 1 | 3 | 99.00 |
| 8 | 王菊 | 1990-01-20 | 女 | 1 | 3 | 99.00 |
| 1 | 赵雷 | 1990-01-01 | 男 | 2 | 1 | 70.00 |
| 2 | 钱电 | 1990-12-21 | 男 | 2 | 1 | 70.00 |
| 3 | 孙风 | 1990-05-20 | 男 | 2 | 1 | 70.00 |
| 4 | 李云 | 1990-08-06 | 男 | 2 | 1 | 70.00 |
| 5 | 周梅 | 1991-12-01 | 女 | 2 | 1 | 70.00 |
| 6 | 吴兰 | 1992-03-01 | 女 | 2 | 1 | 70.00 |
| 7 | 郑竹 | 1989-07-01 | 女 | 2 | 1 | 70.00 |
| 8 | 王菊 | 1990-01-20 | 女 | 2 | 1 | 70.00 |
| 1 | 赵雷 | 1990-01-01 | 男 | 2 | 2 | 60.00 |
| 2 | 钱电 | 1990-12-21 | 男 | 2 | 2 | 60.00 |
| 3 | 孙风 | 1990-05-20 | 男 | 2 | 2 | 60.00 |
| 4 | 李云 | 1990-08-06 | 男 | 2 | 2 | 60.00 |
| 5 | 周梅 | 1991-12-01 | 女 | 2 | 2 | 60.00 |
| 6 | 吴兰 | 1992-03-01 | 女 | 2 | 2 | 60.00 |
| 7 | 郑竹 | 1989-07-01 | 女 | 2 | 2 | 60.00 |
| 8 | 王菊 | 1990-01-20 | 女 | 2 | 2 | 60.00 |
| 1 | 赵雷 | 1990-01-01 | 男 | 2 | 3 | 80.00 |
| 2 | 钱电 | 1990-12-21 | 男 | 2 | 3 | 80.00 |
| 3 | 孙风 | 1990-05-20 | 男 | 2 | 3 | 80.00 |
| 4 | 李云 | 1990-08-06 | 男 | 2 | 3 | 80.00 |
| 5 | 周梅 | 1991-12-01 | 女 | 2 | 3 | 80.00 |
| 6 | 吴兰 | 1992-03-01 | 女 | 2 | 3 | 80.00 |
| 7 | 郑竹 | 1989-07-01 | 女 | 2 | 3 | 80.00 |
| 8 | 王菊 | 1990-01-20 | 女 | 2 | 3 | 80.00 |
| 1 | 赵雷 | 1990-01-01 | 男 | 3 | 1 | 80.00 |
| 2 | 钱电 | 1990-12-21 | 男 | 3 | 1 | 80.00 |
| 3 | 孙风 | 1990-05-20 | 男 | 3 | 1 | 80.00 |
| 4 | 李云 | 1990-08-06 | 男 | 3 | 1 | 80.00 |
| 5 | 周梅 | 1991-12-01 | 女 | 3 | 1 | 80.00 |
| 6 | 吴兰 | 1992-03-01 | 女 | 3 | 1 | 80.00 |
| 7 | 郑竹 | 1989-07-01 | 女 | 3 | 1 | 80.00 |
| 8 | 王菊 | 1990-01-20 | 女 | 3 | 1 | 80.00 |
| 1 | 赵雷 | 1990-01-01 | 男 | 3 | 2 | 80.00 |
| 2 | 钱电 | 1990-12-21 | 男 | 3 | 2 | 80.00 |
| 3 | 孙风 | 1990-05-20 | 男 | 3 | 2 | 80.00 |
| 4 | 李云 | 1990-08-06 | 男 | 3 | 2 | 80.00 |
| 5 | 周梅 | 1991-12-01 | 女 | 3 | 2 | 80.00 |
| 6 | 吴兰 | 1992-03-01 | 女 | 3 | 2 | 80.00 |
| 7 | 郑竹 | 1989-07-01 | 女 | 3 | 2 | 80.00 |
| 8 | 王菊 | 1990-01-20 | 女 | 3 | 2 | 80.00 |
| 1 | 赵雷 | 1990-01-01 | 男 | 3 | 3 | 80.00 |
| 2 | 钱电 | 1990-12-21 | 男 | 3 | 3 | 80.00 |
| 3 | 孙风 | 1990-05-20 | 男 | 3 | 3 | 80.00 |
| 4 | 李云 | 1990-08-06 | 男 | 3 | 3 | 80.00 |
| 5 | 周梅 | 1991-12-01 | 女 | 3 | 3 | 80.00 |
| 6 | 吴兰 | 1992-03-01 | 女 | 3 | 3 | 80.00 |
| 7 | 郑竹 | 1989-07-01 | 女 | 3 | 3 | 80.00 |
| 8 | 王菊 | 1990-01-20 | 女 | 3 | 3 | 80.00 |
| 1 | 赵雷 | 1990-01-01 | 男 | 4 | 1 | 50.00 |
| 2 | 钱电 | 1990-12-21 | 男 | 4 | 1 | 50.00 |
| 3 | 孙风 | 1990-05-20 | 男 | 4 | 1 | 50.00 |
| 4 | 李云 | 1990-08-06 | 男 | 4 | 1 | 50.00 |
| 5 | 周梅 | 1991-12-01 | 女 | 4 | 1 | 50.00 |
| 6 | 吴兰 | 1992-03-01 | 女 | 4 | 1 | 50.00 |
| 7 | 郑竹 | 1989-07-01 | 女 | 4 | 1 | 50.00 |
| 8 | 王菊 | 1990-01-20 | 女 | 4 | 1 | 50.00 |
| 1 | 赵雷 | 1990-01-01 | 男 | 4 | 2 | 30.00 |
| 2 | 钱电 | 1990-12-21 | 男 | 4 | 2 | 30.00 |
| 3 | 孙风 | 1990-05-20 | 男 | 4 | 2 | 30.00 |
| 4 | 李云 | 1990-08-06 | 男 | 4 | 2 | 30.00 |
| 5 | 周梅 | 1991-12-01 | 女 | 4 | 2 | 30.00 |
| 6 | 吴兰 | 1992-03-01 | 女 | 4 | 2 | 30.00 |
| 7 | 郑竹 | 1989-07-01 | 女 | 4 | 2 | 30.00 |
| 8 | 王菊 | 1990-01-20 | 女 | 4 | 2 | 30.00 |
| 1 | 赵雷 | 1990-01-01 | 男 | 4 | 3 | 20.00 |
| 2 | 钱电 | 1990-12-21 | 男 | 4 | 3 | 20.00 |
| 3 | 孙风 | 1990-05-20 | 男 | 4 | 3 | 20.00 |
| 4 | 李云 | 1990-08-06 | 男 | 4 | 3 | 20.00 |
| 5 | 周梅 | 1991-12-01 | 女 | 4 | 3 | 20.00 |
| 6 | 吴兰 | 1992-03-01 | 女 | 4 | 3 | 20.00 |
| 7 | 郑竹 | 1989-07-01 | 女 | 4 | 3 | 20.00 |
| 8 | 王菊 | 1990-01-20 | 女 | 4 | 3 | 20.00 |
| 1 | 赵雷 | 1990-01-01 | 男 | 5 | 1 | 76.00 |
| 2 | 钱电 | 1990-12-21 | 男 | 5 | 1 | 76.00 |
| 3 | 孙风 | 1990-05-20 | 男 | 5 | 1 | 76.00 |
| 4 | 李云 | 1990-08-06 | 男 | 5 | 1 | 76.00 |
| 5 | 周梅 | 1991-12-01 | 女 | 5 | 1 | 76.00 |
| 6 | 吴兰 | 1992-03-01 | 女 | 5 | 1 | 76.00 |
| 7 | 郑竹 | 1989-07-01 | 女 | 5 | 1 | 76.00 |
| 8 | 王菊 | 1990-01-20 | 女 | 5 | 1 | 76.00 |
| 1 | 赵雷 | 1990-01-01 | 男 | 5 | 2 | 87.00 |
| 2 | 钱电 | 1990-12-21 | 男 | 5 | 2 | 87.00 |
| 3 | 孙风 | 1990-05-20 | 男 | 5 | 2 | 87.00 |
| 4 | 李云 | 1990-08-06 | 男 | 5 | 2 | 87.00 |
| 5 | 周梅 | 1991-12-01 | 女 | 5 | 2 | 87.00 |
| 6 | 吴兰 | 1992-03-01 | 女 | 5 | 2 | 87.00 |
| 7 | 郑竹 | 1989-07-01 | 女 | 5 | 2 | 87.00 |
| 8 | 王菊 | 1990-01-20 | 女 | 5 | 2 | 87.00 |
| 1 | 赵雷 | 1990-01-01 | 男 | 6 | 1 | 31.00 |
| 2 | 钱电 | 1990-12-21 | 男 | 6 | 1 | 31.00 |
| 3 | 孙风 | 1990-05-20 | 男 | 6 | 1 | 31.00 |
| 4 | 李云 | 1990-08-06 | 男 | 6 | 1 | 31.00 |
| 5 | 周梅 | 1991-12-01 | 女 | 6 | 1 | 31.00 |
| 6 | 吴兰 | 1992-03-01 | 女 | 6 | 1 | 31.00 |
| 7 | 郑竹 | 1989-07-01 | 女 | 6 | 1 | 31.00 |
| 8 | 王菊 | 1990-01-20 | 女 | 6 | 1 | 31.00 |
| 1 | 赵雷 | 1990-01-01 | 男 | 6 | 3 | 34.00 |
| 2 | 钱电 | 1990-12-21 | 男 | 6 | 3 | 34.00 |
| 3 | 孙风 | 1990-05-20 | 男 | 6 | 3 | 34.00 |
| 4 | 李云 | 1990-08-06 | 男 | 6 | 3 | 34.00 |
| 5 | 周梅 | 1991-12-01 | 女 | 6 | 3 | 34.00 |
| 6 | 吴兰 | 1992-03-01 | 女 | 6 | 3 | 34.00 |
| 7 | 郑竹 | 1989-07-01 | 女 | 6 | 3 | 34.00 |
| 8 | 王菊 | 1990-01-20 | 女 | 6 | 3 | 34.00 |
| 1 | 赵雷 | 1990-01-01 | 男 | 7 | 2 | 89.00 |
| 2 | 钱电 | 1990-12-21 | 男 | 7 | 2 | 89.00 |
| 3 | 孙风 | 1990-05-20 | 男 | 7 | 2 | 89.00 |
| 4 | 李云 | 1990-08-06 | 男 | 7 | 2 | 89.00 |
| 5 | 周梅 | 1991-12-01 | 女 | 7 | 2 | 89.00 |
| 6 | 吴兰 | 1992-03-01 | 女 | 7 | 2 | 89.00 |
| 7 | 郑竹 | 1989-07-01 | 女 | 7 | 2 | 89.00 |
| 8 | 王菊 | 1990-01-20 | 女 | 7 | 2 | 89.00 |
| 1 | 赵雷 | 1990-01-01 | 男 | 7 | 3 | 98.00 |
| 2 | 钱电 | 1990-12-21 | 男 | 7 | 3 | 98.00 |
| 3 | 孙风 | 1990-05-20 | 男 | 7 | 3 | 98.00 |
| 4 | 李云 | 1990-08-06 | 男 | 7 | 3 | 98.00 |
| 5 | 周梅 | 1991-12-01 | 女 | 7 | 3 | 98.00 |
| 6 | 吴兰 | 1992-03-01 | 女 | 7 | 3 | 98.00 |
| 7 | 郑竹 | 1989-07-01 | 女 | 7 | 3 | 98.00 |
| 8 | 王菊 | 1990-01-20 | 女 | 7 | 3 | 98.00 |
+------+--------+------------+-------+------+------+---------+
144 rows in set (0.00 sec)
cross join SQL CROSS JOIN 连接用于从两个或者多个连接表中返回记录集的笛卡尔积，即将左表的每一行与右表的每一行合并。18*8=144
mysql> select * from student a left join score b on(a.s_id=b.s_id)where b.s_score>=80;
+------+--------+------------+-------+------+------+---------+
| s_id | s_name | s_birthday | s_sex | s_id | c_id | s_score |
+------+--------+------------+-------+------+------+---------+
| 1 | 赵雷 | 1990-01-01 | 男 | 1 | 1 | 80.00 |
| 1 | 赵雷 | 1990-01-01 | 男 | 1 | 2 | 90.00 |
| 1 | 赵雷 | 1990-01-01 | 男 | 1 | 3 | 99.00 |
| 2 | 钱电 | 1990-12-21 | 男 | 2 | 3 | 80.00 |
| 3 | 孙风 | 1990-05-20 | 男 | 3 | 1 | 80.00 |
| 3 | 孙风 | 1990-05-20 | 男 | 3 | 2 | 80.00 |
| 3 | 孙风 | 1990-05-20 | 男 | 3 | 3 | 80.00 |
| 5 | 周梅 | 1991-12-01 | 女 | 5 | 2 | 87.00 |
| 7 | 郑竹 | 1989-07-01 | 女 | 7 | 2 | 89.00 |
| 7 | 郑竹 | 1989-07-01 | 女 | 7 | 3 | 98.00 |
+------+--------+------------+-------+------+------+---------+
10 rows in set (0.00 sec)
where限定条件
mysql> select student.s_id ,score.s_id from student left join score on(student.s_id=score.s_id);
+------+------+
| s_id | s_id |
+------+------+
| 1 | 1 |
| 1 | 1 |
| 1 | 1 |
| 2 | 2 |
| 2 | 2 |
| 2 | 2 |
| 3 | 3 |
| 3 | 3 |
| 3 | 3 |
| 4 | 4 |
| 4 | 4 |
| 4 | 4 |
| 5 | 5 |
| 5 | 5 |
| 6 | 6 |
| 6 | 6 |
| 7 | 7 |
| 7 | 7 |
| 8 | NULL |
+------+------+
19 rows in set (0.00 sec)
单独选列
mysql> select * from student union select * from teacher;
ERROR 1222 (21000): The used SELECT statements have a different number of columns
mysql> select s_id,s_name from student union select t_id,t_name from teacher;
+------+--------+
| s_id | s_name |
+------+--------+
| 1 | 赵雷 |
| 2 | 钱电 |
| 3 | 孙风 |
| 4 | 李云 |
| 5 | 周梅 |
| 6 | 吴兰 |
| 7 | 郑竹 |
| 8 | 王菊 |
| 1 | 张三 |
| 2 | 李四 |
| 3 | 王五 |
+------+--------+
11 rows in set (0.00 sec)
注意union的写法 select*会报错，这里的union实现了拼接效果
mysql> select * from student a,score b,course c where a.s_id=b.s_id and b.c_id= c.c_id group by c_name;
+------+--------+------------+-------+------+------+---------+------+--------+------+
| s_id | s_name | s_birthday | s_sex | s_id | c_id | s_score | c_id | c_name | t_id |
+------+--------+------------+-------+------+------+---------+------+--------+------+
| 1 | 赵雷 | 1990-01-01 | 男 | 1 | 2 | 90.00 | 2 | 数学 | 1 |
| 1 | 赵雷 | 1990-01-01 | 男 | 1 | 3 | 99.00 | 3 | 英语 | 3 |
| 1 | 赵雷 | 1990-01-01 | 男 | 1 | 1 | 80.00 | 1 | 语文 | 2 |
+------+--------+------------+-------+------+------+---------+------+--------+------+
group by 还是不太理解
mysql> select * from student a,score b,course c where a.s_id=b.s_id and b.c_id= c.c_id group by s_name;
+------+--------+------------+-------+------+------+---------+------+--------+------+
| s_id | s_name | s_birthday | s_sex | s_id | c_id | s_score | c_id | c_name | t_id |
+------+--------+------------+-------+------+------+---------+------+--------+------+
| 6 | 吴兰 | 1992-03-01 | 女 | 6 | 1 | 31.00 | 1 | 语文 | 2 |
| 5 | 周梅 | 1991-12-01 | 女 | 5 | 1 | 76.00 | 1 | 语文 | 2 |
| 3 | 孙风 | 1990-05-20 | 男 | 3 | 1 | 80.00 | 1 | 语文 | 2 |
| 4 | 李云 | 1990-08-06 | 男 | 4 | 1 | 50.00 | 1 | 语文 | 2 |
| 1 | 赵雷 | 1990-01-01 | 男 | 1 | 1 | 80.00 | 1 | 语文 | 2 |
| 7 | 郑竹 | 1989-07-01 | 女 | 7 | 2 | 89.00 | 2 | 数学 | 1 |
| 2 | 钱电 | 1990-12-21 | 男 | 2 | 1 | 70.00 | 1 | 语文 | 2 |
+------+--------+------------+-------+------+------+---------+------+--------+------+
insert into student_copy(s_id,s_name,s_birthday,s_sex)values('09','汪洋','1999-01-15','男');
Query OK, 1 row affected (0.01 sec)
数据插入
SQL DELETE 语句
DELETE 语句用于删除表中的行。
SQL DELETE 语法
DELETE FROM table_name
WHERE some_column=some_value;
mysql> select * from student_copy;
+------+--------+------------+-------+
| s_id | s_name | s_birthday | s_sex |
+------+--------+------------+-------+
| 1 | 赵雷 | 1990-01-01 | 男 |
| 2 | 钱电 | 1990-12-21 | 男 |
| 3 | 孙风 | 1990-05-20 | 男 |
| 4 | 李云 | 1990-08-06 | 男 |
| 5 | 周梅 | 1991-12-01 | 女 |
| 6 | 吴兰 | 1992-03-01 | 女 |
| 7 | 郑竹 | 1989-07-01 | 女 |
| 8 | 王菊 | 1990-01-20 | 女 |
| 9 | 汪洋 | 1999-01-15 | 男 |
+------+--------+------------+-------+
9 rows in set (0.00 sec)
mysql> delete from student_copy where s_id=9;
Query OK, 1 row affected (0.01 sec)
mysql> select * from student_copy;
+------+--------+------------+-------+
| s_id | s_name | s_birthday | s_sex |
+------+--------+------------+-------+
| 1 | 赵雷 | 1990-01-01 | 男 |
| 2 | 钱电 | 1990-12-21 | 男 |
| 3 | 孙风 | 1990-05-20 | 男 |
| 4 | 李云 | 1990-08-06 | 男 |
| 5 | 周梅 | 1991-12-01 | 女 |
| 6 | 吴兰 | 1992-03-01 | 女 |
| 7 | 郑竹 | 1989-07-01 | 女 |
| 8 | 王菊 | 1990-01-20 | 女 |
+------+--------+------------+-------+
8 rows in set (0.00 sec)
SQL UPDATE 语句
UPDATE 语句用于更新表中已存在的记录。
SQL UPDATE 语法
UPDATE table_name
SET column1=value1,column2=value2,…
WHERE some_column=some_value;
mysql> update student_copy
-> set s_name='王明'
-> where s_id=9;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from student_copy;
+------+--------+------------+-------+
| s_id | s_name | s_birthday | s_sex |
+------+--------+------------+-------+
| 1 | 赵雷 | 1990-01-01 | 男 |
| 2 | 钱电 | 1990-12-21 | 男 |
| 3 | 孙风 | 1990-05-20 | 男 |
| 4 | 李云 | 1990-08-06 | 男 |
| 5 | 周梅 | 1991-12-01 | 女 |
| 6 | 吴兰 | 1992-03-01 | 女 |
| 7 | 郑竹 | 1989-07-01 | 女 |
| 8 | 王菊 | 1990-01-20 | 女 |
| 9 | 王明 | 1999-01-15 | 男 |
+------+--------+------------+-------+
9 rows in set (0.00 sec)
Update 警告！
在更新记录时要格外小心！在上面的实例中，如果我们省略了 WHERE 子句，如下所示：
UPDATE Websites
SET alexa='5000', country='USA'
执行以上代码会将 Websites 表中所有数据的 alexa 改为 5000，country 改为 USA。
执行没有 WHERE 子句的 UPDATE 要慎重，再慎重。
二、DDL语句
-
ALTER TABLE 语句用于在已有的表中添加、删除或修改列。alter主要是对列进行一些增加、删除、修改操作
-
1.增加s_age列ADD
-
ALTER TABLE table_name ADD column_name datatype
-
-
-
mysql> alter table student_copy add s_age int(3) not Null; Query OK, 0 rows affected (0.31 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select * from student_copy -> ; +------+--------+------------+-------+-------+ | s_id | s_name | s_birthday | s_sex | s_age | +------+--------+------------+-------+-------+ | 1 | 赵雷 | 1990-01-01 | 男 | 0 | | 2 | 钱电 | 1990-12-21 | 男 | 0 | | 3 | 孙风 | 1990-05-20 | 男 | 0 | | 4 | 李云 | 1990-08-06 | 男 | 0 | | 5 | 周梅 | 1991-12-01 | 女 | 0 | | 6 | 吴兰 | 1992-03-01 | 女 | 0 | | 7 | 郑竹 | 1989-07-01 | 女 | 0 | | 8 | 王菊 | 1990-01-20 | 女 | 0 | | 9 | 王明 | 1999-01-15 | 男 | 0 | +------+--------+------------+-------+-------+ 9 rows in set (0.00 sec)
-
-
2.删除s_age列drop
-
ALTER TABLE table_name DROP COLUMN column_name
mysql> alter table student_copy -> drop column s_age; Query OK, 0 rows affected (0.20 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select * from student_copy; +------+--------+------------+-------+ | s_id | s_name | s_birthday | s_sex | +------+--------+------------+-------+ | 1 | 赵雷 | 1990-01-01 | 男 | | 2 | 钱电 | 1990-12-21 | 男 | | 3 | 孙风 | 1990-05-20 | 男 | | 4 | 李云 | 1990-08-06 | 男 | | 5 | 周梅 | 1991-12-01 | 女 | | 6 | 吴兰 | 1992-03-01 | 女 | | 7 | 郑竹 | 1989-07-01 | 女 | | 8 | 王菊 | 1990-01-20 | 女 | | 9 | 王明 | 1999-01-15 | 男 | +------+--------+------------+-------+ 9 rows in set (0.00 sec) -
-
3.修改modify
-
ALTER TABLE table_name MODIFY COLUMN column_name datatype修改主要改字段类型，但是字段类型仍存在一些问题未解决，如何选取最合适的字段并且赋予字段长度
-
三、查询
1）子查询
子查询或称为内部查询、嵌套查询，指的是在 SQLite 查询中的 WHERE 子句中嵌入查询语句。
一个 SELECT 语句的查询结果能够作为另一个语句的输入值。
子查询可以与 SELECT、INSERT、UPDATE 和 DELETE 语句一起使用，可伴随着使用运算符如 =、<、>、>=、<=、IN、BETWEEN 等。
以下是子查询必须遵循的几个规则：
- 子查询必须用括号括起来。
- 子查询在 SELECT 子句中只能有一个列，除非在主查询中有多列，与子查询的所选列进行比较。
- ORDER BY 不能用在子查询中，虽然主查询可以使用 ORDER BY。可以在子查询中使用 GROUP BY，功能与 ORDER BY 相同。
- 子查询返回多于一行，只能与多值运算符一起使用，如 IN 运算符。
- BETWEEN 运算符不能与子查询一起使用，但是，BETWEEN 可在子查询内使用。
mysql> select * from student a left join score b on(a.s_id=b.s_id)
-> where a.s_name in(
-> select s_name from student
-> where s_sex='男');
+------+--------+------------+-------+------+------+---------+
| s_id | s_name | s_birthday | s_sex | s_id | c_id | s_score |
+------+--------+------------+-------+------+------+---------+
| 1 | 赵雷 | 1990-01-01 | 男 | 1 | 1 | 80.00 |
| 1 | 赵雷 | 1990-01-01 | 男 | 1 | 2 | 90.00 |
| 1 | 赵雷 | 1990-01-01 | 男 | 1 | 3 | 99.00 |
| 2 | 钱电 | 1990-12-21 | 男 | 2 | 1 | 70.00 |
| 2 | 钱电 | 1990-12-21 | 男 | 2 | 2 | 60.00 |
| 2 | 钱电 | 1990-12-21 | 男 | 2 | 3 | 80.00 |
| 3 | 孙风 | 1990-05-20 | 男 | 3 | 1 | 80.00 |
| 3 | 孙风 | 1990-05-20 | 男 | 3 | 2 | 80.00 |
| 3 | 孙风 | 1990-05-20 | 男 | 3 | 3 | 80.00 |
| 4 | 李云 | 1990-08-06 | 男 | 4 | 1 | 50.00 |
| 4 | 李云 | 1990-08-06 | 男 | 4 | 2 | 30.00 |
| 4 | 李云 | 1990-08-06 | 男 | 4 | 3 | 20.00 |
+------+--------+------------+-------+------+------+---------+
12 rows in set (0.55 sec)
mysql> select * from student a left join score b on(a.s_id=b.s_id) where a.s_sex='男';
+------+--------+------------+-------+------+------+---------+
| s_id | s_name | s_birthday | s_sex | s_id | c_id | s_score |
+------+--------+------------+-------+------+------+---------+
| 1 | 赵雷 | 1990-01-01 | 男 | 1 | 1 | 80.00 |
| 1 | 赵雷 | 1990-01-01 | 男 | 1 | 2 | 90.00 |
| 1 | 赵雷 | 1990-01-01 | 男 | 1 | 3 | 99.00 |
| 2 | 钱电 | 1990-12-21 | 男 | 2 | 1 | 70.00 |
| 2 | 钱电 | 1990-12-21 | 男 | 2 | 2 | 60.00 |
| 2 | 钱电 | 1990-12-21 | 男 | 2 | 3 | 80.00 |
| 3 | 孙风 | 1990-05-20 | 男 | 3 | 1 | 80.00 |
| 3 | 孙风 | 1990-05-20 | 男 | 3 | 2 | 80.00 |
| 3 | 孙风 | 1990-05-20 | 男 | 3 | 3 | 80.00 |
| 4 | 李云 | 1990-08-06 | 男 | 4 | 1 | 50.00 |
| 4 | 李云 | 1990-08-06 | 男 | 4 | 2 | 30.00 |
| 4 | 李云 | 1990-08-06 | 男 | 4 | 3 | 20.00 |
+------+--------+------------+-------+------+------+---------+
12 rows in set (0.00 sec)
不用子查询反而更快
-
子查询一些例子
-
1）查询成绩最低的学生信息
-
mysql> select a.s_name,b.s_score from student_copy a left join score b on (a.s_id=b.s_id) -> where b.s_score=(select min(s_score) from score); +--------+---------+ | s_name | s_score | +--------+---------+ | 李云 | 20.00 | +--------+---------+ 1 row in set (0.03 sec) mysql> select a.s_name,b.s_score from student_copy a left join score b on (a.s_id=b.s_id) -> where b.s_score=min(b.s_score); ERROR 1111 (HY000): Invalid use of group function
-
-
2）查看平均成绩最低的学生信息
-
3）查看平均成绩最的学生信
-
3-1）单表
-
mysql> select avg(s_score) from score group by c_id order by avg(s_score)limit 1; +--------------+ | avg(s_score) | +--------------+ | 64.500000 | +--------------+ 1 row in set (0.00 sec) -
3-2)多表
-
-
2）多表关联
3）分组group by
-
关联stuednt表和score表，得到每个学生的平均成绩，并且按照降序排序。
-
这里降序排序写错了，应该是order by avg(s_score)
-
mysql> select s_name,avg(s_score) from student_copy a left join score b on(a.s_id=b.s_id) -> group by a.s_id -> order by b.s_score desc; +--------+--------------+ | s_name | avg(s_score) | +--------+--------------+ | 郑竹 | 93.500000 | | 孙风 | 80.000000 | | 赵雷 | 89.666667 | | 周梅 | 81.500000 | | 钱电 | 70.000000 | | 李云 | 33.333333 | | 吴兰 | 32.500000 | | 王菊 | NULL | | 王明 | NULL | +--------+--------------+ 9 rows in set (0.00 sec) -
得到学生的平均学科成绩最高的 用limit 但注意判断分数是否为空
mysql> select a.s_name,avg(b.s_score) avg -> from student_copy a,score b -> where a.s_id=b.s_id -> group by b.s_id -> order by avg -> desc limit 1; +--------+-----------+ | s_name | avg | +--------+-----------+ | 郑竹 | 93.500000 | +--------+-----------+ 1 row in set (0.00 sec)-
mysql> select * from score where s_score is null; Empty set (0.00 sec) mysql> select * from score where s_score is not null; +------+------+---------+ | s_id | c_id | s_score | +------+------+---------+ | 1 | 1 | 80.00 | | 1 | 2 | 90.00 | | 1 | 3 | 99.00 | | 2 | 1 | 70.00 | | 2 | 2 | 60.00 | | 2 | 3 | 80.00 | | 3 | 1 | 80.00 | | 3 | 2 | 80.00 | | 3 | 3 | 80.00 | | 4 | 1 | 50.00 | | 4 | 2 | 30.00 | | 4 | 3 | 20.00 | | 5 | 1 | 76.00 | | 5 | 2 | 87.00 | | 6 | 1 | 31.00 | | 6 | 3 | 34.00 | | 7 | 2 | 89.00 | | 7 | 3 | 98.00 | +------+------+---------+ 18 rows in set (0.00 sec)is nulll && is not null 在涉及到数字计算的时候，请分外注意
-
-
4）条件查询 where
5）排序 order by
6) 分页limit
索引学习
-
建立索引
建立索引出现mysql> ALTER TABLE 425dongaonews ADD INDEX CONTENT_INDEX(content);
ERROR 1170 (42000): BLOB/TEXT column ‘content’ used in key specification without a key length的问题解决：在表对应text类型列建立索引的时候，要确定该列的长度，具体怎么确定我不知道
mysql> ALTER TABLE 425dongaonews ADD INDEX CONTENT_INDEX(content); ERROR 1170 (42000): BLOB/TEXT column 'content' used in key specification without a key lengthmysql> CREATE INDEX content_index ON 425dongaonews(content(255)); Query OK, 0 rows affected (1.61 sec) Records: 0 Duplicates: 0 Warnings: 0 -
删除索引
mysql> drop index content_index on 425dongaonews; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 -
对比一下时间
#有索引 mysql> select count(*) from 425dongaonews where content like '%羽生结弦%'; +----------+ | count(*) | +----------+ | 184 | +----------+ 1 row in set (0.99 sec)mysql> select count(*) from 425dongaonews where content like '%羽生结弦%'; +----------+ | count(*) | +----------+ | 184 | +----------+ 1 row in set (0.97 sec)感觉删完更快了
-
查询表中的索引信息
mysql> show index from 425dongaonews; +---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | 425dongaonews | 0 | PRIMARY | 1 | 序号 | A | 7848 | NULL | NULL | | BTREE | | | +---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 1 row in set (0.00 sec) -
建立索引的原则
-
定义主键的数据列一定要建立索引。
-
定义有外键的数据列一定要建立索引。
-
对于经常查询的数据列最好建立索引。
-
对于需要在指定范围内的快速或频繁查询的数据列;
-
***经常用在***WHERE****子句中的数据列。
-
经常出现在关键字order by、group by、distinct后面的字段，建立索引。
如果建立的是复合索引，索引的字段顺序要和这些关键字后面的字段顺序一致，否则索引不会被使用。
-
对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。
-
对于定义为text、image和bit的数据类型的列不要建立索引。
-
对于经常存取的列避免建立索
-
限制表上的索引数目。对一个存在大量更新操作的表，所建索引的数目一般不要超过3个，最多不要超过5个。
索引虽说提高了访问速度，但太多索引会影响数据的更新操作。
- 对复合索引，按照字段在查询条件中出现的频度建立索引。在复合索引中，记录首先按照第一个字段排序。
对于在第一个字段上取值相同的记录，系统再按照第二个字段的取值排序，以此类推。
因此只有复合索引的第一个字段出现在查询条件中，该索引才可能被使用,因此将应用频度高的字段，放置在复合索引的前面，会使系统最大可能地使用此索引，发挥索引的作用。
未完待续。。。 -
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
