创建数据库
CREATE DATABASE 数据库名;
例子:CREATE DATABASE std,
进入数据库
USE 数据库名;
例子:USE std;
为数据库创建表
CREATE TABLE 表名
(属性名 数据类型, …, …);
CREATE TABLE STUDENT
(Sno char(5),Sname char(10),Sage smallint,Ssex char(2),Sdept char(15));
添加约束
Sno唯一且不为空
alter table student modify Sno char(5)not null unique
Sage默认20
alter table student modify Sage default 20;
Ssex设置约束范围
alter table student add constraint Csex check(Ssex in('男','女'));
check语句不对输入的数据进行检测,只是为了数据的规范。
可以用set
alter table student modify Ssex set('男','女')not null;
设置主键约束
alter table student add constraint primary key(Sno);
//constraint 后面可以自定义约束名,若没有约束名,则系统会产生默认的约束名
查询
常用的分组函数
查询总学生数(总行数)
select count(*) as '学生总数' from student;
查询002号课程的平均成绩
select avg(grade) as '002平均成绩' from SC where Cno = '002';
查询001最高分与最低分
select max(grade) as '001号课程最高分', min(grade) as '001号课程最低分' from sc where Cno = '001';
每门课的平均成绩
select Cno,avg(grade) '平均成绩' from sc group by Cno;
学生总分
select Sno,sum(grade) as '总分' from sc group by Sno;
having与where的区别
having只能在有group by才能使用,只作用与分组,每个分组作用一次
where则作用于整个表和视图,是逐条记录的过滤
查询总分超过200的学生的学号和总成绩
select Sno,sum(grade) from sc group by Sno having sum(grade)>200;
查询所有成绩为优秀的学生的学号(每门成绩均大于90)
mysql> select * from sc ;
+——+—–+——-+
| Sno | Cno | Grade |
+——+—–+——-+
| 1001 | 001 | 60 |
| 1001 | 002 | 90 |
| 1001 | 003 | 50 |
| 1002 | 001 | 69 |
| 1002 | 002 | 98 |
| 1002 | 003 | 0 |
| 1003 | 001 | 60 |
| 1003 | 002 | 60 |
| 1003 | 003 | 60 |
| 1004 | 001 | NULL |
| 1004 | 002 | NULL |
| 1004 | 003 | NULL |
| 1005 | 001 | 99 |
| 1005 | 002 | 96 |
| 1005 | 003 | 93 |
| 1006 | 001 | NULL |
| 1006 | 002 | 99 |
| 1006 | 003 | 93 |
+——+—–+——-+
mysql> select Sno from sc where grade != 'NULL' group by Sno having min(grade)>90;
+——+
| Sno |
+——+
| 1005 |
| 1006 |
+——+
所以上面的查询方法有漏洞
select Sno from sc where Sno not in (select Sno from sc where grade is NULL) group by Sno having min(grade)>90;//空值不能与任何数比较,所以子查询那里只能用is不能用=;
mysql> select Sno from sc where Sno not in (select Sno from sc where grade is NULL) group by Sno having min(grade)>90;
+——+
| Sno |
+——+
| 1005 |
+——+
1 row in set
多表查询
等值查询
学生所选的选修课程
select student.*,sc.* from student,sc where student.Sno = sc.Sno;
字符串连接查询
将学号和姓名在同一列显示
select concat(Sno,Sname) from student;
表的别名
select A.*,B.* from student A ,sc B where A.Sno = B.sno;
外连接
select student.*,sc.* from student left join sc on student.Sno = SC.Sno;
符合条件查询
查询选修课程号002成绩大于90的学生信息
select student.Sno,Sname,Ssex,Sage from student,sc where sc.Sno=student.Sno and Cno = '002' and grade >= 90;
子查询
查询年龄大于所有学生平均年龄的学生信息
select student.* from student where Sage > (select avg(Sage) from student);
数据的更新
插入数据
insert into 表名 values(…, …, …)
修改数据
将学生1001的年龄改为22
update student set Sage = 22 where Sno = 1001;
删除数据
delete from 表名 where 约束条件
创建数据库用户
create user 'wss'@localhost identified by '123';
授予wss用户在student上所有权限,并允许它授予其他用户权利
grant all privileges on student to wss@localhost with grant option;
收回wss用户对student的所有权限
revoke all privileges on student from wss@localhost;
拒绝权限
deny 权限名 on 表名 to 用户名;
创建视图
将学生号,总成绩,平均成绩定义成一个视图
create view vw_gradestate as select Sno,sum(grade) as totalgrade,avg(grade) from sc as avg group by Sno;
查询视图
select * from vw_gradestate;
mysql学习笔记,仅供参考,不足之处请指出