数据库操作

1
2
3
4
5
6
7
8
9
10
#创建数据库
create database test default charset utf8;
#查看有哪些数据库
show databases;
#查看数据库的创建语句
show create database test;
#修改数据库的选项信息
alter database test default charset gbk;
#删除数据库
drop database test;

表操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
#创建表
create table test(
id int unsigned not null auto_increment comment 'id',
content varchar(100) not null default '' comment '内容',
time datetime not null comment '时间',
primary key (id)
)engine=InnoDB default charset=utf8 comment '测试表';
#查看表
show tables;
#模糊查询,_代表任意单个字符,%匹配任意字符
show tables like '_模糊表名%';
#查看建表语句
show create table test;
#查看表结构
desc test;
#删除表
drop table test;
#修改表名
alter table test rename to newtest;
#增加一列
alter table test add name char(10);
#删除一列
alter table test drop name;
#修改字段类型
alter table test modify name varchar(100);
#修改字段顺序,把某字段放在最前面
alter table test modify name varchar(100) first;
#修改字段顺序,把某字段放在另一字段后面
alter table test modify name varchar(100) after time;
#重命名字段
alter table test change name username varchar(50);
#修改表选项信息
alter table test engine Myisam default charset gbk;

数据操作

1
2
3
4
5
6
7
8
#插入数据
insert into user(id,name,age) values(1,'aaa',18);
#查询name字段
select name from user where age>0;
#删除数据
delete from user where age<1;
#修改字段
update user set age=20 where name='aaa';

MySQL 数据类型

  • 数值型

    TINYINT 小整数
    SMALLINT 大整数
    MEDIUMINT 大整数
    INT 大整数
    BIGINT 极大整数
    FLOAT 单精度浮点数
    DOUBLE 双精度浮点数
    DECIMAL(M,D) 高精度数,对应 Java 的 BigDecimal
  • 字符串型

    CHAR 定长字符串
    VARCHAR 变长字符串
    TINYBLOB 短二进制字符串
    TINYTEXT 短文本
    BLOB 长二进制字符串
    TEXT 长文本
    MEDIUMBLOB 中长二进制字符串
    MEDIUMTEXT 中长文本
    LONGBLOB 极大二进制字符串
    LONGTEXT 极大文本
  • 日期时间型

    DATE YYYY-MM-DD 日期
    TIME HH:MM:SS 时间
    YEAR YYYY 年份
    DATATIME YYYY-MM-DD HH:MM:SS 日期+时间
    TIMESTAMP YYYYMMDD HHMMSS 日期+时间,时间戳

索引

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
1、主键索引:加速查询+列值唯一(不可以有null)+表中只有一个
primary key
2、唯一索引:加速查询+列值唯一(可以有null)
unique key
3、全文索引:对文本内容分词搜索
fulltext index
4、普通索引:仅加速查询
key 或 index
5、组合索引:多列值组成一个索引,专门用于组合搜索

#创建主键索引
primary key (id)
#创建唯一索引
create unique index idx_age on user(age);
#创建普通索引
create index idx_age on user(age);
#删除索引
drop index idx_age on user;

外键

1
2
3
#外键约束:foreign key 被约束的表叫做副表,外键设置在副表上面,外键引用主键字段所在的表叫做主表
#外键语法:constraint 外键名 foreign key(外键字段) reference 主表名(引用字段)
#外键操作:有外键时,添加数据先添加主表数据再添加副表;修改删除数据先修改删除副表数据再修改主表

存储引擎

1
2
3
#innodb与myisam主要区别
1、innodb支持事务,myisam不支持
2、innodb支持行级锁也支持表级锁,myisam只支持表级锁

insert

1
2
#复制表
insert into table1 select * from table2;

select

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
#语法
select [select选项] *|字段列表 [as 别名] from 表名 [where子句][group by子句][having子句][order by子句][limit子句];
#select选项
all:默认值,所有结果
distinct:去重

#where子句运算符
--比较运算符
<,>,<=,>=,=,!=
between and | not between and --例:between A and B;
in | not in
is null | is not null
like --通配符_代表任意单个字符,%代表任意字符
--逻辑运算符
&&(and),||(or),!(not)

#查询id 1 2 3并且age大于18
select * from stu where id in(1,2,3) and age>18;
#查询id区间[2,5]并且模糊查询name以m开头的学生
select * from stu where id between 2 and 5 and name like 'm%';
#查询id 5,或age大于19
select * from stu where id in(5) || age>19;

#group by子句
#查询各班总分数,分组班级降序
select class,sum(score) as sum from stu group by class desc;
#查询每个班最高成绩,分数要大于80分,分组班级升序
select id,class,max(score) as max from stu where score>80 group by class asc;

#having子句
#与where区别:where是把磁盘上的数据筛选到内存上,而having是把内存中的数据再次筛选
#where不可以使用统计函数,一般需用统计函数配合group by 才会用到having
#查询各班80分以内的最低分,分组班级
select class,min(score) from stu group by class having min(score)>80;

#order by子句
#查询score大于80,排序score升序和name降序
select * from stu where score>80 order by score asc,name desc;

#limit offset,length offset是偏移量默认0,length是需要显示的记录数
#查询score大于80,排序score升序和name降序,偏移量为1,显示3条记录
select * from stu where score>80 order by score asc,name desc limit 1,3;

update

1
update 表名 set 字段1=值1,字段n=值n [where条件] [order by 字段名 asc|desc] [limit];

delete

1
delete from 表名 [where条件] [order by 字段名 asc|desc] [limit];

联合查询

1
2
3
4
5
6
7
#联合查询就是将多个查询结果纵向拼接
#语法
select 语句1 union select 语句2 union select 语句n;
#查询A班最高成绩和B班最低成绩
(select name,class,score from stu where class='A' order by score desc limit 1)
union
(select name,class,score from stu where class='B' order by score asc limit 1);

连接查询

1
2
3
4
5
6
7
8
#交叉连接 cross join 两张表做笛卡尔积,有n1*n2条记录
select *|字段 from1 join2;
#内连接 inner join 两张表中存在相同字段
select stu.*,tcher.class as t_class,tcher.name as t_name from stu join tcher on stu.class=tcher.class;
#左外连接 left join 左表记录都会出现,而右表若对应数据不存在则以null填充
select stu.*,tcher.class as t_class,tcher.name as t_name from stu left join tcher on stu.class=tcher.class;
#右外连接 right join 右表数据都会出现,而左表若对应数据不存在则以null填充
select stu.*,tcher.class as t_class,tcher.name as t_name from stu right join tcher on stu.class=tcher.class;

image.pngimage.png

子查询

1
2
3
#子查询的结果被主查询使用
#查询班级最高分的学生记录
select * from stu where score = (select max(score) as max from stu);

事务

1
2
3
4
5
6
7
8
9
10
#开启事务
start transaction; 或 begin;
#提交事务
commit;
#回滚事务
rollback;
#修改事务自动提交
set autocommit = 0 | 1 (0取消自动提交,1自动提交)
#查看
show variables like 'autocommit';

用户权限管理

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
#用户信息表 mysql.user表
#创建用户
create user 用户名[@主机地址] identified by '密码';
create user user1[@localhost] identified by '123';
#查看mysql.user表的host,user,password
select host,user,password from mysql.user;
#重命名用户
rename mysql.user oldname[@oldhost] to newname[@newhost];
#设置密码
set password for user1 = password('newpass');
#删除用户
drop user1[@localhost];
#分配权限给用户
grant 权限列表 on *|库名.*|表名 to 用户名[@主机地址] [identified by "密码"][with grant option];
权限列表:all [privileges]、deleteselectupdateinsert
grant insert,update on *.* to user1@localhost identified by '123' with grant option;
#刷新权限
flush privileges;
#查看权限
show grants for user1@localhost;
#撤销权限
revoke update on *.* from user1@localhost;
revoke all privileges,grant option from user1@localhost; --撤销所有权限

sql 练习题

表名和字段

1
2
3
4
5
6
7
8
1、学生表
student(s_id,s_name,s_birth,s_sex) --学生编号,学生姓名,出生年月,学生性别
2、课程表
course(c_id,c_name,t_id) --课程编号,课程名称,教师编号
3、教师表
teacher(t_id,t_name) --教师编号,教师姓名
4、成绩表
score(s_id,c_id,s_score) --学生编号,课程编号,分数

测试数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
#学生表
create table student(
s_id varchar(20),
s_name varchar(20) not null default '',
s_birth varchar(20) not null default '',
s_sex varchar(20) not null default '',
primary key (s_id)
);
#课程表
create table course(
c_id varchar(20),
c_name varchar(20) not null default '',
t_id varchar(20) not null,
primary key (c_id)
);
#教师表
create table teacher(
t_id varchar(20),
t_name varchar(20) not null default '',
primary key (t_id)
);
#成绩表
create table score(
s_id varchar(20),
c_id varchar(20),
s_score int(3),
primary key (s_id,c_id)
);

#插入学生表测试数据
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);

练习题和 sql 语句

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
# 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
select a.*,b.s_score as 01_score,c.s_score as 02_score
from sql_test.student a,score b,score c
where a.s_id=b.s_id
and a.s_id=c.s_id
and b.c_id=01
and c.c_id=02
and b.s_score>c.s_score;

# 2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
select a.*,b.s_score as 01_score,c.s_score as 02_score
from sql_test.student a,score b,score c
where a.s_id=b.s_id
and a.s_id=c.s_id
and b.c_id=01
and c.c_id=02
and b.s_score<c.s_score;

# 3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
select a.s_id,a.s_name,round(avg(b.s_score),2)
from sql_test.student a
join score b
on a.s_id=b.s_id
group by a.s_id
having round(avg(b.s_score),2)>=60;

# 4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
# (包括有成绩的和无成绩的)
select a.s_id,a.s_name,round(avg(b.s_score),2) as avg_score
from sql_test.student a
join score b
on a.s_id=b.s_id
group by a.s_id
having avg_score<60
union
select a.s_id,a.s_name,0 as avg_score
from sql_test.student a
where a.s_id not in(select distinct b.s_id from score b);

# 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
select a.s_id,a.s_name,count(b.c_id),sum(b.s_score)
from sql_test.student a
left join score b #因为有的同学没有选课不会出现在score表中,所以用左外连接让student表全部显示
on a.s_id=b.s_id
group by a.s_id;

# 6、查询"李"姓老师的数量
select count(t_id) from teacher where t_name like '李%';

# 7、查询学过"张三"老师授课的同学的信息
select a.*
from sql_test.student a
join score b on a.s_id=b.s_id
where b.c_id=(select c.c_id from sql_test.course c where c.t_id in (select t.t_id from teacher t where t.t_name = '张三'));

# 8、查询没学过"张三"老师授课的同学的信息
select a.* from
sql_test.student a
where a.s_id not in
(select a.s_id from sql_test.student a join sql_test.score b on a.s_id=b.s_id and b.c_id in
(select d.c_id from sql_test.course d join teacher t on d.t_id=t.t_id where t.t_name='张三'));

# 9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
select a.* from
sql_test.student a,score b,score c
where a.s_id=b.s_id and a.s_id=c.s_id and b.c_id='01' and c.c_id='02';

# 10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
select a.* from
sql_test.student a
where a.s_id in (select a.s_id from score a where a.c_id='01')
and a.s_id not in (select a.s_id from score a where a.c_id='02');

# 11、查询没有学全所有课程的同学的信息
select a.* from
sql_test.student a
left join score b on a.s_id=b.s_id
group by a.s_id
having count(b.c_id)<(select count(*) from sql_test.course);

# 12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息


# 13、查询和"01"号的同学学习的课程完全相同的其他同学的信息

#下面的语句是找到'01'同学学习的课程数

#下面的语句是找到学过‘01’同学没学过的课程,有哪些同学。并排除他们

#下面的语句是找到‘01’同学没学过的课程

#下面的语句是找出‘01’同学学习的课程

#下面的条件是排除01同学


# 14、查询没学过"张三"老师讲授的任一门课程的学生姓名


# 15、查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩


# 16、检索"01"课程分数小于60,按分数降序排列的学生信息


# 17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩


# 18.查询各科成绩最高分、最低分和平均分:以如下形式显示:课程ID,课程name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率
#及格为>=60,中等为:70-80,优良为:80-90,优秀为:>=90


# 19、按各科成绩进行排序,并显示排名
# mysql没有rank函数

# 20、查询学生的总成绩并进行排名


# 21、查询不同老师所教不同课程平均分从高到低显示


# 22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩


# 23、统计各科成绩各分数段人数:课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比


# 24、查询学生平均成绩及其名次


# 25、查询各科成绩前三名的记录
# 1.选出b表比a表成绩大的所有组
# 2.选出比当前id成绩大的 小于三个的


# 26、查询每门课程被选修的学生数


# 27、查询出只有两门课程的全部学生的学号和姓名


# 28、查询男生、女生人数


# 29、查询名字中含有"风"字的学生信息


# 30、查询同名同性学生名单,并统计同名人数


# 31、查询1990年出生的学生名单


# 32、查询每门课程的平均成绩,结果按平均成绩降序排列,平均成绩相同时,按课程编号升序排列


# 33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩


# 34、查询课程名称为"数学",且分数低于60的学生姓名和分数


# 35、查询所有学生的课程及分数情况;


# 36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数;


# 37、查询不及格的课程


#38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名;


# 39、求每门课程的学生人数


# 40、查询选修"张三"老师所授课程的学生中,成绩最高的学生信息及其成绩

# 查询老师id

# 查询最高分(可能有相同分数)

# 查询信息


# 41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩


# 42、查询每门功成绩最好的前两名
# 牛逼的写法


# 43、统计每门课程的学生选修人数(超过5人的课程才统计)。要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列


# 44、检索至少选修两门课程的学生学号


# 45、查询选修了全部课程的学生信息


#46、查询各学生的年龄
# 按照出生日期来算,当前月日 < 出生年月的月日则,年龄减一


# 47、查询本周过生日的学生


# 48、查询下周过生日的学生


# 49、查询本月过生日的学生


# 50、查询下月过生日的学生