MySQL入门学习(1):
服务开启与关闭:
开启MySQL服务:NET SATRT [MySQL服务名]
关闭MySQL服务:NET START 【MySQL服务名]
例如:NET START MYSQL56_1
NET STOP MYSQL56_1
查询已开启的服务;NET START
创建表:
例如:
(1) 建立教学管理中的三个基本表:
Students(S#,SNAME,AGE,SEX) 学生(学号,姓名,年龄,性别)
Courses(C#,CNAME,SCORE,PC#) 课程(课程号,课程名,学分,先行课号)
SC(S#,C#,GRADE) 选修(学号,课程号,成绩)
mysql> Create table Students(
-> SN CHAR(5),
-> SNAME CHAR(20) NOT NULL,
-> AGE INT,
-> SEX CHAR(2),
-> primary key(SN));
Query OK, 0 rows affected (0.03 sec)mysql> Create table Courses(
-> CN CHAR(5),
-> CNAME CHAR(20) NOT NULL,
-> Score float,
-> PCN CHAR(2),
-> primary key(CN));
Query OK, 0 rows affected (0.07 sec)mysql> Create table SC(
-> SN CHAR(5),
-> CN CHAR(5),
-> GRADE FLOAT,
-> PRIMARY KEY(SN,CN));
Query OK, 0 rows affected (0.08 sec)
添加数据:
(1) 用INSERT 命令输入数据。
表6 基本表Students的数据:
表7基本表Courses的数据
表8 基本表SC的数据(空格为未选修):
mysql> insert into students values('S1','LU',20,'M');
Query OK, 1 row affected (0.04 sec)mysql> insert into students values('S2','YIN',19,'M');
Query OK, 1 row affected (0.06 sec)mysql> insert into students values('S3','XU',18,'F');
Query OK, 1 row affected (0.05 sec)mysql> insert into students values('S4','QU',18,'F');
Query OK, 1 row affected (0.00 sec)mysql> insert into students values('S6','PAN',14,'M');
Query OK, 1 row affected (0.05 sec)mysql> insert into students values('S8','DONG',24,'M');
Query OK, 1 row affected (0.05 sec)mysql> INSERT INTO COURSES VALUES('C1','数学',4,'M');
Query OK, 1 row affected (0.07 sec)mysql> INSERT INTO COURSES VALUES('C2','英语',8,'M');
Query OK, 1 row affected (0.06 sec)mysql> INSERT INTO COURSES VALUES('C3','数据结构',4,'F');
Query OK, 1 row affected (0.06 sec)mysql> INSERT INTO COURSES VALUES('C4','数据库',3.5,'F');
Query OK, 1 row affected (0.07 sec)mysql> INSERT INTO COURSES VALUES('C5','网络',4,'M');
Query OK, 1 row affected (0.06 sec)mysql> insert into sc values('S1','C1',85);
Query OK, 1 row affected (0.00 sec)mysql> INSERT INTO SC VALUES('S1','C2',73);
Query OK, 1 row affected (0.01 sec)mysql> insert into sc values('S1','C4',89);
Query OK, 1 row affected (0.00 sec)mysql> insert into sc values('S1','C5',73);
Query OK, 1 row affected (0.00 sec)mysql> insert into sc values('S2','C1',90);
Query OK, 1 row affected (0.01 sec)mysql> insert into sc values('S2','C3',80);
Query OK, 1 row affected (0.01 sec)mysql> insert into sc values('S2','C4',85);
Query OK, 1 row affected (0.15 sec)mysql> insert into sc values('S3','C1',89);
Query OK, 1 row affected (0.00 sec)mysql> insert into sc values('S3','C2',86);
Query OK, 1 row affected (0.10 sec)mysql> insert into sc values('S4','C1',84);
Query OK, 1 row affected (0.10 sec)mysql> insert into sc values('S4','C2',82);
Query OK, 1 row affected (0.00 sec)………………………..
数据查询
( 1 ) 列出选修课程号为C2的学生学号与姓名。
(2) 检索选修课程名为“数学”的学生学号与姓名。
(3) 检索没有选修C2课程的学生姓名与年龄。
(4) 检索选修全部课程的学生姓名。
mysql> selECT STUDENTS.SN,STUDENTS.SNAME FROM STUDENTS,SC
-> WHERE (STUDENTS.SN=SC.SN) AND (SC.CN='C2');
+----+-------+
| SN | SNAME |
+----+-------+
| S1 | LU |
| S3 | XU |
| S4 | QU |
| S6 | PAN |
| S8 | DONG |
+----+-------+
5 rows in set (0.09 sec)
mysql> selECT STUDENTS.SN,STUDENTS.SNAME FROM STUDENTS,COURSES,SC
-> WHERE (STUDENTS.SN=SC.SN) AND (SC.CN=COURSES.CN) AND (COURSES.CNAME='网络');
+----+-------+
| SN | SNAME |
+----+-------+
| S1 | LU |
| S8 | DONG |
+----+-------+
2 rows in set (0.00 sec)
mysql> select students.SNAME,students.age
-> from students,sc
-> where students.sn=sc.sn and sc.cn='C2' and sc.grade IS NULL;
+-------+------+
| SNAME | age |
+-------+------+
| YIN | 19 |
+-------+------+
1 row in set (0.00 sec)
mysql> delete
-> from sc
-> where Grade is NULL;
Query OK, 10 rows affected (0.03 sec)mysql> select SNAME
-> from students
-> where not exists
-> (Select * from courses
-> where not exists
-> (select * from sc
-> where sc.sn=students.sn and sc.cn=courses.cn));
+-------+
| SNAME |
+-------+
| LU |
+-------+
1 row in set (0.00 sec)数据修改、删除
(1) 把C2课程的非空成绩提高10%。
(2) 在SC表中删除课程名为“物理”的成绩所对应的元组。
(3) 在S和SC表中删除学号为S8的所有数据。
mysql> update sc
-> set grade=grade*1.1
-> where cn='C2';
Query OK, 5 rows affected (0.07 sec)
Rows matched: 5 Changed: 5 Warnings: 0mysql> select * from sc
-> where cn='C2';
+----+----+-------+
| SN | CN | GRADE |
+----+----+-------+
| S1 | C2 | 80.3 |
| S3 | C2 | 94.6 |
| S4 | C2 | 90.2 |
| S6 | C2 | 82.5 |
| S8 | C2 | 93.5 |
+----+----+-------+
5 rows in set (0.00 sec)
mysql> delete
-> from sc
-> where cn=(select cn
-> from courses
-> where cname='物理');
Query OK, 0 rows affected (0.00 sec)
mysql> delete from sc
-> where sc.sn='S8';
Query OK, 4 rows affected (0.05 sec)mysql> delete from students where sn='S8';
Query OK, 1 row affected (0.00 sec)
视图的操作
(1) 建立男生学生的视图,属性包括学号,姓名,选修课程名和成绩。
(2) 在男生视图中查询平均成绩大于80分的学生学号和姓名。
mysql> create view M
-> as
-> select students.sn,sname,cname,grade
-> from students,courses,sc
-> where students.sn=sc.sn and courses.cn=sc.cn;
Query OK, 0 rows affected (0.06 sec)
mysql> select sn,sname
-> from M
-> where AVG(grade)>80
-> group by sn;
ERROR 1111 (HY000): Invalid use of group function此处WHERE子句不能使用聚集函数作为表达式作为条件表达式,因此执行此查询将会出现语法错误。
mysql> select sn,sname
-> from M
-> group by sn
-> HAVING AVG(grade)>80;
+----+-------+
| sn | sname |
+----+-------+
| S1 | LU |
| S2 | YIN |
| S3 | XU |
| S4 | QU |
| S6 | PAN |
+----+-------+
5 rows in set (0.00 sec)