也许，关于mysql数据库的使用，你想让自己变得更专业些，至少看上去是如此，那么，我们就来小试牛刀吧！
注意，由于笔者打字有些快，所以下面创建的字段sanme,其实是sname,但笔者较为懒，就将错就错，只要不影响使用就行，只是不符合PIE原则，希望大家要引以为戒，不要像笔者这样懒啊！
tee i:/cyd.txt;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| cheng |
| cyd |
| cydbook |
| mysql |
| performance_schema |
| school |
| test |
| zheng |
+--------------------+
9 rows in set (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| cheng |
| cyd |
| cydbook |
| mysql |
| performance_schema |
| school |
| test |
| zheng |
+--------------------+
9 rows in set (0.00 sec)
mysql> use cheng;
Database changed
mysql> show tables;
+-----------------+
| Tables_in_cheng |
+-----------------+
| course |
| info |
+-----------------+
2 rows in set (0.01 sec)
mysql> create table student(
-> name char(20),
-> id int(10),
-> birthday char(20),
-> score float(20),
-> primary key(id));
Query OK, 0 rows affected (0.17 sec)
mysql> insert into student values(1,"张三","1996-01-09","89.0");
ERROR 1366 (HY000): Incorrect integer value: '张三' for column 'id' at row 1
mysql> drop table student;
Query OK, 0 rows affected (0.06 sec)
mysql> create table student(
-> id int(10),
-> name char(20),
-> birthday char(20),
-> score float(20),
-> primary key(id));
Query OK, 0 rows affected (0.17 sec)
mysql> insert into student values(1,"Mary","1998-02-20","89.0");
Query OK, 1 row affected (0.07 sec)
mysql> select * from student;
+----+------+------------+-------+
| id | name | birthday | score |
+----+------+------------+-------+
| 1 | Mary | 1998-02-20 | 89 |
+----+------+------------+-------+
1 row in set (0.00 sec)
mysql> use test;
Database changed
mysql> show tables;
Empty set (0.00 sec)
mysql> create table student(
-> sno char(10),
-> sanme char(10) unique,
-> sex varchar(10),
-> age char(10),
-> department char(8),
-> bplace char(10),
-> primary key(sno) );
Query OK, 0 rows affected (0.17 sec)
mysql> alter table student add stel char(12);
Query OK, 0 rows affected (0.26 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from student;
Empty set (0.00 sec)
mysql> alter table student drop column stel;
Query OK, 0 rows affected (0.27 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table student alter column department char(10);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'char(10)' at line 1
mysql> create table course(
-> cno char(10),
-> cname char(20) not null,
-> primary key(cno));
Query OK, 0 rows affected (0.17 sec)
mysql> create table enroll(
-> sno char(10),
-> cno char(10),
-> grade char(10),
-> primary key(sno,cno));
Query OK, 0 rows affected (0.15 sec)
mysql> create unique index sno_index on student(sno desc);
Query OK, 0 rows affected (0.20 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> create index s_index on student(age);
Query OK, 0 rows affected (0.19 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> drop index s_index on student;
Query OK, 0 rows affected (0.21 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from student;
Empty set (0.00 sec)
mysql> select * from course;
Empty set (0.00 sec)
mysql> select * from enroll;
Empty set (0.00 sec)
mysql> insert into student values("95001","胡峰","男","23","机电系","湖南");
Query OK, 1 row affected (0.08 sec)
mysql> insert into student values("95012","程军","男","22","计算机","山东");
Query OK, 1 row affected (0.07 sec)
mysql> insert into student values("95020","张春明","男","22","计算机","河北");
Query OK, 1 row affected (0.07 sec)
mysql> insert into student values("95022","丁晓春","男","20","计算机","湖北");
Query OK, 1 row affected (0.06 sec)
mysql> insert into course values("c1","数据库");
Query OK, 1 row affected (0.07 sec)
mysql> insert into course values("c2","操作系统");
Query OK, 1 row affected (0.08 sec)
mysql> insert into course values("c3","数据结构");
Query OK, 1 row affected (0.07 sec)
mysql> insert into course values("c4","软件工程");
Query OK, 1 row affected (0.10 sec)
mysql> insert into enroll values("95001","c1","90");
Query OK, 1 row affected (0.05 sec)
mysql> insert into enroll values("95001","c2","82");
Query OK, 1 row affected (0.07 sec)
mysql> insert into enroll values("95001","c3","95");
Query OK, 1 row affected (0.06 sec)
mysql> insert into enroll values("95001","c4","88");
Query OK, 1 row affected (0.06 sec)
mysql> insert into enroll values("95012","c2","93");
Query OK, 1 row affected (0.07 sec)
mysql> insert into enroll values("95012","c3","88");
Query OK, 1 row affected (0.06 sec)
mysql> insert into enroll values("95020","c2","83");
Query OK, 1 row affected (0.05 sec)
mysql> insert into enroll values("95020","c3","NULL");
Query OK, 1 row affected (0.07 sec)
mysql> insert into enroll values("95020","c4","88");
Query OK, 1 row affected (0.08 sec)
mysql> insert into enroll values("95022","c2","77");
Query OK, 1 row affected (0.06 sec)
mysql> insert into enroll values("95022","c3","71");
Query OK, 1 row affected (0.09 sec)
mysql> select * from student;
+-------+--------+------+------+------------+--------+
| sno | sanme | sex | age | department | bplace |
+-------+--------+------+------+------------+--------+
| 95001 | 胡峰 | 男 | 23 | 机电系 | 湖南 |
| 95012 | 程军 | 男 | 22 | 计算机 | 山东 |
| 95020 | 张春明 | 男 | 22 | 计算机 | 河北 |
| 95022 | 丁晓春 | 男 | 20 | 计算机 | 湖北 |
+-------+--------+------+------+------------+--------+
4 rows in set (0.00 sec)
mysql> select * from course;
+-----+----------+
| cno | cname |
+-----+----------+
| c1 | 数据库 |
| c2 | 操作系统 |
| c3 | 数据结构 |
| c4 | 软件工程 |
+-----+----------+
4 rows in set (0.00 sec)
mysql> select * from enroll;
+-------+-----+-------+
| sno | cno | grade |
+-------+-----+-------+
| 95001 | c1 | 90 |
| 95001 | c2 | 82 |
| 95001 | c3 | 95 |
| 95001 | c4 | 88 |
| 95012 | c2 | 93 |
| 95012 | c3 | 88 |
| 95020 | c2 | 83 |
| 95020 | c3 | NULL |
| 95020 | c4 | 88 |
| 95022 | c2 | 77 |
| 95022 | c3 | 71 |
+-------+-----+-------+
11 rows in set (0.00 sec)
mysql> select sno,sanme,age from student;
+-------+--------+------+
| sno | sanme | age |
+-------+--------+------+
| 95001 | 胡峰 | 23 |
| 95012 | 程军 | 22 |
| 95020 | 张春明 | 22 |
| 95022 | 丁晓春 | 20 |
+-------+--------+------+
4 rows in set (0.00 sec)
mysql> select distinct department from student;
+------------+
| department |
+------------+
| 机电系 |
| 计算机 |
+------------+
2 rows in set (0.00 sec)
mysql> select sanme,sex,2013-age from student;
+--------+------+----------+
| sanme | sex | 2013-age |
+--------+------+----------+
| 胡峰 | 男 | 1990 |
| 程军 | 男 | 1991 |
| 张春明 | 男 | 1991 |
| 丁晓春 | 男 | 1993 |
+--------+------+----------+
4 rows in set (0.00 sec)
mysql> select sanme,sex,2013-age as '出生年份' from student;
+--------+------+----------+
| sanme | sex | 出生年份 |
+--------+------+----------+
| 胡峰 | 男 | 1990 |
| 程军 | 男 | 1991 |
| 张春明 | 男 | 1991 |
| 丁晓春 | 男 | 1993 |
+--------+------+----------+
4 rows in set (0.00 sec)
mysql> select * from student where department='计算机';
+-------+--------+------+------+------------+--------+
| sno | sanme | sex | age | department | bplace |
+-------+--------+------+------+------------+--------+
| 95012 | 程军 | 男 | 22 | 计算机 | 山东 |
| 95020 | 张春明 | 男 | 22 | 计算机 | 河北 |
| 95022 | 丁晓春 | 男 | 20 | 计算机 | 湖北 |
+-------+--------+------+------+------------+--------+
3 rows in set (0.00 sec)
mysql> select * from student where department='计算机' and bplace='湖北';
+-------+--------+------+------+------------+--------+
| sno | sanme | sex | age | department | bplace |
+-------+--------+------+------+------------+--------+
| 95022 | 丁晓春 | 男 | 20 | 计算机 | 湖北 |
+-------+--------+------+------+------------+--------+
1 row in set (0.00 sec)
mysql> select sanme,sex,age from student where age>22
-> ;
+-------+------+------+
| sanme | sex | age |
+-------+------+------+
| 胡峰 | 男 | 23 |
+-------+------+------+
1 row in set (0.00 sec)
mysql> select sanme,age from student where age between 20 and 24;
+--------+------+
| sanme | age |
+--------+------+
| 胡峰 | 23 |
| 程军 | 22 |
| 张春明 | 22 |
| 丁晓春 | 20 |
+--------+------+
4 rows in set (0.00 sec)
mysql> select sanme,age from student where age>=20 and age<=24;
+--------+------+
| sanme | age |
+--------+------+
| 胡峰 | 23 |
| 程军 | 22 |
| 张春明 | 22 |
| 丁晓春 | 20 |
+--------+------+
4 rows in set (0.01 sec)
mysql> select sno,sanme,sex,bplace from student where bplace in('湖北');
+-------+--------+------+--------+
| sno | sanme | sex | bplace |
+-------+--------+------+--------+
| 95022 | 丁晓春 | 男 | 湖北 |
+-------+--------+------+--------+
1 row in set (0.00 sec)
mysql> select sno,sanme from student where sno in(select sno from enroll where grade>80);
+-------+--------+
| sno | sanme |
+-------+--------+
| 95020 | 张春明 |
| 95012 | 程军 |
| 95001 | 胡峰 |
+-------+--------+
3 rows in set (0.00 sec)
mysql> select * from student where sanme like '张%';
+-------+--------+------+------+------------+--------+
| sno | sanme | sex | age | department | bplace |
+-------+--------+------+------+------------+--------+
| 95020 | 张春明 | 男 | 22 | 计算机 | 河北 |
+-------+--------+------+------+------------+--------+
1 row in set (0.00 sec)
mysql> select * from student where sanme like '_春%';
Empty set (0.00 sec)
mysql> select sanme,sex from student where sanme like '_春%';
Empty set (0.00 sec)
mysql> select sanme,sex from student where sanme like '%春%';
+--------+------+
| sanme | sex |
+--------+------+
| 张春明 | 男 |
| 丁晓春 | 男 |
+--------+------+
2 rows in set (0.00 sec)
mysql> select * from student where sanme like '张春明';
+-------+--------+------+------+------------+--------+
| sno | sanme | sex | age | department | bplace |
+-------+--------+------+------+------------+--------+
| 95020 | 张春明 | 男 | 22 | 计算机 | 河北 |
+-------+--------+------+------+------------+--------+
1 row in set (0.00 sec)
mysql> select * from enroll where grade is NULL;
Empty set (0.00 sec)
mysql> select * from enroll where grade IS NULL;
Empty set (0.00 sec)
mysql> select COUNT(*) AS 总人数 from student;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '总人数 from student' at line 1
mysql> select avg(age) as '平均年龄' from student;
+----------+
| 平均年龄 |
+----------+
| 21.75 |
+----------+
1 row in set (0.00 sec)
mysql> select count(*) as '总人数‘ from student;
'> ';
+-------------------------+
| 总人数‘ from student;
|
+-------------------------+
| 1 |
+-------------------------+
1 row in set (0.00 sec)
mysql> select count(*) as '总人数' from student;
+--------+
| 总人数 |
+--------+
| 4 |
+--------+
1 row in set (0.01 sec)
mysql> select count(distinct sno) from enroll;
+---------------------+
| count(distinct sno) |
+---------------------+
| 4 |
+---------------------+
1 row in set (0.00 sec)
mysql> select max(grade) as '最高分' from enroll where cno='c1';
+--------+
| 最高分 |
+--------+
| 90 |
+--------+
1 row in set (0.00 sec)
mysql> select cno,count(sno) '选课人数' from enroll group by cno;
+-----+----------+
| cno | 选课人数 |
+-----+----------+
| c1 | 1 |
| c2 | 4 |
| c3 | 4 |
| c4 | 2 |
+-----+----------+
4 rows in set (0.00 sec)
mysql> select * from student order by age desc;
+-------+--------+------+------+------------+--------+
| sno | sanme | sex | age | department | bplace |
+-------+--------+------+------+------------+--------+
| 95001 | 胡峰 | 男 | 23 | 机电系 | 湖南 |
| 95012 | 程军 | 男 | 22 | 计算机 | 山东 |
| 95020 | 张春明 | 男 | 22 | 计算机 | 河北 |
| 95022 | 丁晓春 | 男 | 20 | 计算机 | 湖北 |
+-------+--------+------+------+------------+--------+
4 rows in set (0.00 sec)
mysql> select sno,grade from enroll where cno='c3' order by grade desc;
+-------+-------+
| sno | grade |
+-------+-------+
| 95020 | NULL |
| 95001 | 95 |
| 95012 | 88 |
| 95022 | 71 |
+-------+-------+
4 rows in set (0.00 sec)
mysql> select * from student order by department,age desc;
+-------+--------+------+------+------------+--------+
| sno | sanme | sex | age | department | bplace |
+-------+--------+------+------+------------+--------+
| 95001 | 胡峰 | 男 | 23 | 机电系 | 湖南 |
| 95012 | 程军 | 男 | 22 | 计算机 | 山东 |
| 95020 | 张春明 | 男 | 22 | 计算机 | 河北 |
| 95022 | 丁晓春 | 男 | 20 | 计算机 | 湖北 |
+-------+--------+------+------+------------+--------+
4 rows in set (0.00 sec)
mysql> select student.*,enroll.* from student,enroll where student.sno=enroll.sno;
+-------+--------+------+------+------------+--------+-------+-----+-------+
| sno | sanme | sex | age | department | bplace | sno | cno | grade |
+-------+--------+------+------+------------+--------+-------+-----+-------+
| 95001 | 胡峰 | 男 | 23 | 机电系 | 湖南 | 95001 | c1 | 90 |
| 95001 | 胡峰 | 男 | 23 | 机电系 | 湖南 | 95001 | c2 | 82 |
| 95001 | 胡峰 | 男 | 23 | 机电系 | 湖南 | 95001 | c3 | 95 |
| 95001 | 胡峰 | 男 | 23 | 机电系 | 湖南 | 95001 | c4 | 88 |
| 95012 | 程军 | 男 | 22 | 计算机 | 山东 | 95012 | c2 | 93 |
| 95012 | 程军 | 男 | 22 | 计算机 | 山东 | 95012 | c3 | 88 |
| 95020 | 张春明 | 男 | 22 | 计算机 | 河北 | 95020 | c2 | 83 |
| 95020 | 张春明 | 男 | 22 | 计算机 | 河北 | 95020 | c3 | NULL |
| 95020 | 张春明 | 男 | 22 | 计算机 | 河北 | 95020 | c4 | 88 |
| 95022 | 丁晓春 | 男 | 20 | 计算机 | 湖北 | 95022 | c2 | 77 |
| 95022 | 丁晓春 | 男 | 20 | 计算机 | 湖北 | 95022 | c3 | 71 |
+-------+--------+------+------+------------+--------+-------+-----+-------+
11 rows in set (0.00 sec)
mysql> select student.sno,sanme,grade from student,enroll where student.sno=enroll.sno and grade>=90;
+-------+-------+-------+
| sno | sanme | grade |
+-------+-------+-------+
| 95001 | 胡峰 | 90 |
| 95001 | 胡峰 | 95 |
| 95012 | 程军 | 93 |
+-------+-------+-------+
3 rows in set, 1 warning (0.00 sec)
mysql> select student.sno,sanme,cname,grade from student,enroll,course where student.sno=enroll.sno and enroll.cno=course.cno;
+-------+--------+----------+-------+
| sno | sanme | cname | grade |
+-------+--------+----------+-------+
| 95001 | 胡峰 | 数据库 | 90 |
| 95001 | 胡峰 | 操作系统 | 82 |
| 95001 | 胡峰 | 数据结构 | 95 |
| 95001 | 胡峰 | 软件工程 | 88 |
| 95012 | 程军 | 操作系统 | 93 |
| 95012 | 程军 | 数据结构 | 88 |
| 95020 | 张春明 | 操作系统 | 83 |
| 95020 | 张春明 | 数据结构 | NULL |
| 95020 | 张春明 | 软件工程 | 88 |
| 95022 | 丁晓春 | 操作系统 | 77 |
| 95022 | 丁晓春 | 数据结构 | 71 |
+-------+--------+----------+-------+
11 rows in set (0.01 sec)
mysql> select s1.sno,s1.sanme,s1.department from student s1,student s2 where s1.department=s2.department and s2.sanme='胡峰';
+-------+-------+------------+
| sno | sanme | department |
+-------+-------+------------+
| 95001 | 胡峰 | 机电系 |
+-------+-------+------------+
1 row in set (0.00 sec)
mysql> select student.sno,sanme,grade from student left outer join enroll on student.sno=enroll.sno and grade>90;
+-------+--------+-------+
| sno | sanme | grade |
+-------+--------+-------+
| 95020 | 张春明 | NULL |
| 95012 | 程军 | 93 |
| 95022 | 丁晓春 | NULL |
| 95001 | 胡峰 | 95 |
+-------+--------+-------+
4 rows in set, 1 warning (0.00 sec)
mysql> select student.sno,sanme,grade from student right outer join enroll on student.sno=enroll.sno and grade>90;
+-------+-------+-------+
| sno | sanme | grade |
+-------+-------+-------+
| NULL | NULL | 90 |
| NULL | NULL | 82 |
| 95001 | 胡峰 | 95 |
| NULL | NULL | 88 |
| 95012 | 程军 | 93 |
| NULL | NULL | 88 |
| NULL | NULL | 83 |
| NULL | NULL | NULL |
| NULL | NULL | 88 |
| NULL | NULL | 77 |
| NULL | NULL | 71 |
+-------+-------+-------+
11 rows in set, 1 warning (0.00 sec)
mysql> select s.*,e.sno,e.cno,e.grade from student as a inner join enroll as e on s.sno=e.sno where s.bplace='山东';
ERROR 1051 (42S02): Unknown table 's'
mysql> select sno,sanme,department from student where department in(select department from student where sanme='胡峰');
+-------+-------+------------+
| sno | sanme | department |
+-------+-------+------------+
| 95001 | 胡峰 | 机电系 |
+-------+-------+------------+
1 row in set (0.00 sec)
mysql> select * from student where sno in(select sno from enroll where grade<80);
+-------+--------+------+------+------------+--------+
| sno | sanme | sex | age | department | bplace |
+-------+--------+------+------+------------+--------+
| 95020 | 张春明 | 男 | 22 | 计算机 | 河北 |
| 95022 | 丁晓春 | 男 | 20 | 计算机 | 湖北 |
+-------+--------+------+------+------------+--------+
2 rows in set, 1 warning (0.01 sec)
mysql> select sno,sanme from student where sno in
-> (select sno from enroll where cno in(select cno from course where cname='数据库'));
+-------+-------+
| sno | sanme |
+-------+-------+
| 95001 | 胡峰 |
+-------+-------+
1 row in set (0.00 sec)
mysql> exit
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| cheng |
| cyd |
| cydbook |
| mysql |
| performance_schema |
| school |
| test |
| zheng |
+--------------------+
9 rows in set (0.00 sec)
mysql> use school;
Database changed
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| cheng |
| cyd |
| cydbook |
| mysql |
| performance_schema |
| school |
| test |
| zheng |
+--------------------+
9 rows in set (0.00 sec)
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| teacher |
+------------------+
1 row in set (0.00 sec)
mysql> use cyd;
Database changed
mysql> show tables;
+---------------+
| Tables_in_cyd |
+---------------+
| student |
| teacher |
+---------------+
2 rows in set (0.00 sec)
mysql> use cheng;
Database changed
mysql> show tables;
+-----------------+
| Tables_in_cheng |
+-----------------+
| course |
| info |
| student |
+-----------------+
3 rows in set (0.00 sec)
mysql> use zheng;
Database changed
mysql> show tables;
+-----------------+
| Tables_in_zheng |
+-----------------+
| account |
| user |
| users |
+-----------------+
3 rows in set (0.08 sec)
mysql> use test;
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| course |
| enroll |
| student |
+----------------+
3 rows in set (0.00 sec)
mysql> select sno,cno from enroll x
-> where grade>=(select avg(grade) from enroll y where y.sno=x.sno);
+-------+-----+
| sno | cno |
+-------+-----+
| 95001 | c1 |
| 95001 | c3 |
| 95012 | c2 |
| 95020 | c2 |
| 95020 | c4 |
| 95022 | c2 |
+-------+-----+
6 rows in set, 4 warnings (0.25 sec)
mysql> select sanme from student where exists
-> (select * from enroll where sno=student.sno and cno='c1');
+-------+
| sanme |
+-------+
| 胡峰 |
+-------+
1 row in set (0.00 sec)
mysql> select * from student where department='计算机' union select * from student where age<=19;
+-------+--------+------+------+------------+--------+
| sno | sanme | sex | age | department | bplace |
+-------+--------+------+------+------------+--------+
| 95012 | 程军 | 男 | 22 | 计算机 | 山东 |
| 95020 | 张春明 | 男 | 22 | 计算机 | 河北 |
| 95022 | 丁晓春 | 男 | 20 | 计算机 | 湖北 |
+-------+--------+------+------+------------+--------+
3 rows in set (0.09 sec)
mysql> select sno from enroll where cno='c1' intersect select sno from enroll where cno='c2';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'intersect select sno from enroll where cno='c2'' at line 1
mysql> select * from student where department='计算机' except select * from student where age<=19;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'except select * from student where age<=19' at line 1
mysql> insert into enroll(sno,cno)
-> select sno,'c5' from student;
Query OK, 4 rows affected (1.29 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from enroll;
+-------+-----+-------+
| sno | cno | grade |
+-------+-----+-------+
| 95001 | c1 | 90 |
| 95001 | c2 | 82 |
| 95001 | c3 | 95 |
| 95001 | c4 | 88 |
| 95001 | c5 | NULL |
| 95012 | c2 | 93 |
| 95012 | c3 | 88 |
| 95012 | c5 | NULL |
| 95020 | c2 | 83 |
| 95020 | c3 | NULL |
| 95020 | c4 | 88 |
| 95020 | c5 | NULL |
| 95022 | c2 | 77 |
| 95022 | c3 | 71 |
| 95022 | c5 | NULL |
+-------+-----+-------+
15 rows in set (0.00 sec)
mysql> update student set age=age-1;
Query OK, 4 rows affected (0.17 sec)
Rows matched: 4 Changed: 4 Warnings: 0
mysql> select sno,sanme,age from student;
+-------+--------+------+
| sno | sanme | age |
+-------+--------+------+
| 95001 | 胡峰 | 22 |
| 95012 | 程军 | 21 |
| 95020 | 张春明 | 21 |
| 95022 | 丁晓春 | 19 |
+-------+--------+------+
4 rows in set (0.00 sec)
mysql> update student set age=age+5 where sanme='丁晓春';
Query OK, 1 row affected (0.11 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from student where sanme='丁晓春';
+-------+--------+------+------+------------+--------+
| sno | sanme | sex | age | department | bplace |
+-------+--------+------+------+------------+--------+
| 95022 | 丁晓春 | 男 | 24 | 计算机 | 湖北 |
+-------+--------+------+------+------------+--------+
1 row in set (0.00 sec)
mysql> update enroll set grade=60 where cno in(
-> select cno from course where cname='数据库');
Query OK, 1 row affected (0.28 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> create view IS_Student as
-> select sno,sanme,sex,department from student where department='计算机';
Query OK, 0 rows affected (2.89 sec)
mysql> create view IS_Student1 as
-> select sno,sanme,age from student where department='计算机“ with check option;
'> ';
Query OK, 0 rows affected (1.92 sec)
mysql>
mysql> create view IS_Student1 as
-> select sno,sanme,age from student where department='计算机' with check option;
ERROR 1050 (42S01): Table 'IS_Student1' already exists
mysql> drop view IS_Student1;
Query OK, 0 rows affected (0.53 sec)
mysql> create view IS_Student1 as
-> select sno,sanme,age from student where department='计算机' with check option;
Query OK, 0 rows affected (1.56 sec)
mysql> create view IS_S1(sno,sanme,grade) as
-> select student.sno,sanme,grade from student,enroll
-> where department='计算机' and
-> student.sno=enroll.sno and cno='c1';
Query OK, 0 rows affected (2.52 sec)
mysql> create view IS_S2 as
-> select sno,sanme,grade from IS_S1 where grade>=90;
Query OK, 0 rows affected (4.39 sec)
mysql> create view BT_S(sno,sanme,birth) as
-> select sno,sanme,2017-age from student;
Query OK, 0 rows affected (0.38 sec)
mysql> create view S_G(sno,gavg) as
-> select sno,avg(grade) from enroll group by sno;
Query OK, 0 rows affected (1.03 sec)
mysql> select sno,sanme,sex,department from IS_Student where sex='男';
+-------+--------+------+------------+
| sno | sanme | sex | department |
+-------+--------+------+------------+
| 95012 | 程军 | 男 | 计算机 |
| 95020 | 张春明 | 男 | 计算机 |
| 95022 | 丁晓春 | 男 | 计算机 |
+-------+--------+------+------------+
3 rows in set (0.00 sec)
mysql> select IS_Student.sno,sanme from IS_Student,enroll
-> where IS_Student.sno=enroll.sno and enroll.cno='c1';
Empty set (0.00 sec)
mysql> select * from IS_Student1;
+-------+--------+------+
| sno | sanme | age |
+-------+--------+------+
| 95012 | 程军 | 21 |
| 95020 | 张春明 | 21 |
| 95022 | 丁晓春 | 24 |
+-------+--------+------+
3 rows in set (0.00 sec)
mysql> select * from S_G where gavg>=80;
+-------+-------+
| sno | gavg |
+-------+-------+
| 95001 | 81.25 |
| 95012 | 90.5 |
+-------+-------+
2 rows in set, 1 warning (0.00 sec)
mysql> insert into IS_Student values('95024','赵薇','女','计算机');
Query OK, 1 row affected (0.07 sec)
mysql> select * from IS_Student;
+-------+--------+------+------------+
| sno | sanme | sex | department |
+-------+--------+------+------------+
| 95012 | 程军 | 男 | 计算机 |
| 95020 | 张春明 | 男 | 计算机 |
| 95022 | 丁晓春 | 男 | 计算机 |
| 95024 | 赵薇 | 女 | 计算机 |
+-------+--------+------+------------+
4 rows in set (0.00 sec)
mysql> insert into IS_Student values("95056","程用东","男","计算机");
Query OK, 1 row affected (0.08 sec)
mysql> select * from IS_Student;
+-------+--------+------+------------+
| sno | sanme | sex | department |
+-------+--------+------+------------+
| 95012 | 程军 | 男 | 计算机 |
| 95020 | 张春明 | 男 | 计算机 |
| 95022 | 丁晓春 | 男 | 计算机 |
| 95024 | 赵薇 | 女 | 计算机 |
| 95056 | 程用东 | 男 | 计算机 |
+-------+--------+------+------------+
5 rows in set (0.00 sec)
mysql> delete from IS_Student where sno='95024';
Query OK, 1 row affected (0.09 sec)
mysql> select * from IS_Student;
+-------+--------+------+------------+
| sno | sanme | sex | department |
+-------+--------+------+------------+
| 95012 | 程军 | 男 | 计算机 |
| 95020 | 张春明 | 男 | 计算机 |
| 95022 | 丁晓春 | 男 | 计算机 |
| 95056 | 程用东 | 男 | 计算机 |
+-------+--------+------+------------+
4 rows in set (0.00 sec)
mysql> delete from IS_Student where sno="95056";
Query OK, 1 row affected (0.06 sec)
mysql> select * from IS_Student;
+-------+--------+------+------------+
| sno | sanme | sex | department |
+-------+--------+------+------------+
| 95012 | 程军 | 男 | 计算机 |
| 95020 | 张春明 | 男 | 计算机 |
| 95022 | 丁晓春 | 男 | 计算机 |
+-------+--------+------+------------+
3 rows in set (0.00 sec)
mysql> update IS_Student set department='文学' where sanme="丁晓春";
Query OK, 1 row affected (0.06 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from IS_Student;
+-------+--------+------+------------+
| sno | sanme | sex | department |
+-------+--------+------+------------+
| 95012 | 程军 | 男 | 计算机 |
| 95020 | 张春明 | 男 | 计算机 |
+-------+--------+------+------------+
2 rows in set (0.00 sec)
mysql> update IS_Student set sex='女' where sanme='张春明';
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from IS_Stduent;
ERROR 1146 (42S02): Table 'test.is_stduent' doesn't exist
mysql> select * from IS_Student;
+-------+--------+------+------------+
| sno | sanme | sex | department |
+-------+--------+------+------------+
| 95012 | 程军 | 男 | 计算机 |
| 95020 | 张春明 | 女 | 计算机 |
+-------+--------+------+------------+
2 rows in set (0.00 sec)
mysql> select * from student;
+-------+--------+------+------+------------+--------+
| sno | sanme | sex | age | department | bplace |
+-------+--------+------+------+------------+--------+
| 95001 | 胡峰 | 男 | 22 | 机电系 | 湖南 |
| 95012 | 程军 | 男 | 21 | 计算机 | 山东 |
| 95020 | 张春明 | 女 | 21 | 计算机 | 河北 |
| 95022 | 丁晓春 | 男 | 24 | 文学 | 湖北 |
+-------+--------+------+------+------------+--------+
4 rows in set (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| cheng |
| cyd |
| cydbook |
| mysql |
| performance_schema |
| school |
| test |
| zheng |
+--------------------+
9 rows in set (0.08 sec)
mysql> use cheng;
Database changed
mysql> show tables;
+-----------------+
| Tables_in_cheng |
+-----------------+
| course |
| info |
| student |
+-----------------+
3 rows in set (0.00 sec)
mysql> select * from student;
+----+---------------+------------+-------+
| id | name | birthday | score |
+----+---------------+------------+-------+
| 1 | ChengYongDong | 2017-12-26 | 89 |
| 2 | aa | 2017-12-19 | 22 |
| 3 | da | 2017-12-20 | 77 |
+----+---------------+------------+-------+
3 rows in set (0.00 sec)好了，关于Mysql的使用就到这里了，是不是有些成就感了呢！
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
