南天软件
1.
create table t_test(
id int(4) not null primary key auto_increment,
time varchar(20) not null,
degree varchar(10) not null,
score double(3,1));
2.
insert into t_test values(1,'2005-05-09','优',98.1);
3.
MariaDB [mysql]> select degree,count(*),avg(score) from t_test group by degree;
+--------+----------+------------+
| degree | count(*) | avg(score) |
+--------+----------+------------+
| 优 | 1 | 98.00000 |
| 及格 | 1 | 89.50000 |
| 良 | 1 | 89.50000 |
+--------+----------+------------+
3 rows in set (0.000 sec)
4.
MariaDB [mysql]> update t_test set time='2005-05-11' where degree='及格';
Query OK, 0 rows affected (0.000 sec)
Rows matched: 1 Changed: 1 Warnings: 0
青牛软件
B
C
A
A
区别2:truncate属于DDL,会自动提交,而delete属于DML,不会自动提交
区别2:truncate是删除全部数据且删除数据不可回滚,而delete可以删除部分数据也可以删除全部数据且delete删除数据可以回滚。
区别3:truncate的执行速度比delete快
区别4:执行truncate会释放空间,能够降低最高水位线(HWM),而执行delete不会释放空间,不能降低水位线,并且用delete删除,表的大小没有改变。
select * from A where time benteen '2006-01-01' and '2006-01-31';
create alter drop
3306
A
C
C
D(insert可以不要into)
D
C
汉邦高科
8.使用mysqldump备份
备份所有库:[root@kongd ~]# mysqldump -uroot -p -A -B > all.db.sql
备份student库:mysqldump -uroot -p -B student > student.sql
备份表:mysqldump -uroot -p student score > student_score.sql
恢复:
方法1: mysql -uroot -p < 备份文件
方法2:进入数据库 source 备份文件
9.grant all on * to smart identified by "123456";
深圳国电
1.表:table1 (FId,Fclass,Fscore),用最高效最简单的SQL列出各班成绩最高的列表,显示班级、成绩两个字段。
select Fclass,max(Fscore) from table1 group by Fclass;
2.
select * from db1.abc order by db1.abc.uid desc limit 0,20;
3.
4.
11. select insert_time,count(*) from submit_message_send_histroy_201503 group by insert_time;
12.show open tables where In_use > 0;
13.update monitor_info set monitor_command="info01.log" where monitor_command="info.log";
5.根据题目条件,写出相应的命令:
(一 )学生表:Student (Sno, Sname, Ssex , Sage, Sdept)
学号,姓名,性别,年龄,所在系 Sno为主键
课程表:Course (Cno, Cname,)
课程号,课程名 Cno为主键
学生选课表:SC (Sno, Cno, Score)
学号,课程号,成绩 Sno,Cno为主键
1.用SQL语句创建学生表student,定义主键,姓名不能重名,性别只能输入男或女,所在系的默认值是 “计算机”。
create table student1(sno int(10) primary key,sname varchar(20) unique,ssex varchar (10),ssage int(10),sdept varchar(20) default '计算机',check(ssex in ('男','女')));
或者 ssex enum('男','女')
create table course(cno int(10) primary key,cname varchar(16)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
create table sc(sno int(10),cno int(10), score int(10),primary key (sno,cno),foreign key(sno) references student1(sno),foreign key(cno) references course(cno));
2.修改student 表中年龄(age)字段属性,数据类型由int 改变为smallint。
mysql> alter table Student modify Ssage smallint;
Query OK, 8 rows affected (0.16 sec)
Records: 8 Duplicates: 0 Warnings: 0
3.为SC表建立按学号(sno)和课程号(cno)组合的升序的主键索引,索引名为SC_INDEX 。
MariaDB [mysql]> create unique index sc_index on sc(sno asc,cno asc);
Query OK, 0 rows affected, 1 warning (0.135 sec)
Records: 0 Duplicates: 0 Warnings: 1
4.向student表添加一条纪录:200201,张三,男,21,计算机。
MariaDB [mysql]> insert into student1
-> values(200201,'张三','男',21,'计算机');
ERROR 1062 (23000): Duplicate entry '张三' for key 'sname'
#不能添加,因为姓名唯一
5.选修了2号课程且成绩低于70的的学生每人成绩增加5分。
MariaDB [mysql]> update sc set score=score+5 where cno=2 and score<=70;
Query OK, 3 rows affected (0.001 sec)
Rows matched: 3 Changed: 3 Warnings: 0
6.删除选修了课程名称为“单片机”的学生的选课成绩记录。
MariaDB [mysql]> delete from sc where cno=(select cno from course where cname='单片机');
Query OK, 5 rows affected (0.000 sec)
7.创建一视图 stu_info,查询全体学生的姓名,性别,课程名,成绩。
MariaDB [mysql]> create view stu_info as select student1.sname,student1.ssex,course.cno,sc.score from student1,sc,course where student1.sno=sc.sno and sc.cno=course.cno;
Query OK, 0 rows affected (0.036 sec)
8.查询不及格学生的姓名。
MariaDB [mysql]> select sname,score from sc,student1 where sc.sno=student1.sno and score<60;
+--------+-------+
| sname | score |
+--------+-------+
| 孙八 | 55 |
| 张三 | 55 |
| 武十 | 58 |
| 王五 | 54 |
| 钱七 | 53 |
+--------+-------+
5 rows in set (0.000 sec)
9.查询选修四门以上课程的学生学号。
MariaDB [mysql]> select sno from (select sno,count(*) num from sc group by sno)t where t.num>=4;
+------+
| sno |
+------+
| 1003 |
| 1006 |
+------+
2 rows in set (0.000 sec)
10.查询2号课程的最高分的学生的姓名。
MariaDB [mysql]> select sname from student1 where sno=(select sno from (select sno,max(score) from sc where cno=2)t);
+--------+
| sname |
+--------+
| 张三 |
+--------+
1 row in set (0.001 sec)
为保证答案一致性,表中添加数据如下:
insert into Student
values(1001,‘张三’,‘男’,21,‘计算机’);
准备插入数据
INSERT INTO student1
VALUES (1002, ‘李四’, ‘男’, 20, ‘英语’);
INSERT INTO student1
VALUES (1003, ‘王五’, ‘男’, 22, ‘计算机’);
INSERT INTO student1
VALUES (1004, ‘赵六’, ‘女’, 20, ‘英语’);
INSERT INTO student1
VALUES (1005, ‘钱七’, ‘男’, 21, ‘计算机’);
INSERT INTO student1
VALUES (1006, ‘孙八’, ‘男’, 20, ‘计算机’);
INSERT INTO student1
VALUES (1007, ‘胡九’, ‘女’, 19, ‘计算机’);
INSERT INTO student1
VALUES (1008, ‘武十’, ‘男’, 20, ‘计信管’);
mysql> select * from Student;
±-----±-------±-----±------±----------+
| Sno | Sname | Ssex | Ssage | Sdept |
±-----±-------±-----±------±----------+
| 1001 | 张三 | 男 | 21 | 计算机 |
| 1002 | 李四 | 男 | 20 | 英语 |
| 1003 | 王五 | 男 | 22 | 计算机 |
| 1004 | 赵六 | 女 | 20 | 英语 |
| 1005 | 钱七 | 男 | 21 | 计算机 |
| 1006 | 孙八 | 男 | 20 | 计算机 |
| 1007 | 胡九 | 女 | 19 | 计算机 |
| 1008 | 武十 | 男 | 20 | 计信管 |
±-----±-------±-----±------±----------+
CREATE TABLE Course
(
Cno
int(4) unsigned NOT NULL DEFAULT ‘0’ COMMENT ‘课程号’,
Cname
varchar(16) DEFAULT NULL COMMENT ‘课程名’,
PRIMARY KEY (Cno
)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO course
VALUES (1, ‘英语’);
INSERT INTO course
VALUES (2, ‘高数’);
INSERT INTO course
VALUES (3, ‘计算机’);
INSERT INTO course
VALUES (4, ‘单片机’);
INSERT INTO course
VALUES (5, ‘java’);
INSERT INTO course
VALUES (6, ‘MySQL’);
INSERT INTO course
VALUES (7, ‘Linux’);
INSERT INTO course
VALUES (8, ‘C++’);
mysql> select * from Course;
±----±----------+
| Cno | Cname |
±----±----------+
| 1 | 英语 |
| 2 | 高数 |
| 3 | 计算机 |
| 4 | 单片机 |
| 5 | java |
| 6 | MySQL |
| 7 | Linux |
| 8 | C++ |
±----±----------+
8 rows in set (0.00 sec)
INSERT INTO sc
VALUES (1001, 2, 45);
INSERT INTO sc
VALUES (1001, 3, 85);
INSERT INTO sc
VALUES (1001, 4, 74);
INSERT INTO sc
VALUES (1002, 1, 90);
INSERT INTO sc
VALUES (1003, 1, 54);
INSERT INTO sc
VALUES (1003, 2, 62);
INSERT INTO sc
VALUES (1003, 3, 81);
INSERT INTO sc
VALUES (1003, 4, 80);
INSERT INTO sc
VALUES (1003, 5, 72);
INSERT INTO sc
VALUES (1003, 6, 88);
INSERT INTO sc
VALUES (1003, 7, 77);
INSERT INTO sc
VALUES (1003, 8, 67);
INSERT INTO sc
VALUES (1004, 1, 92);
INSERT INTO sc
VALUES (1005, 1, 53);
INSERT INTO sc
VALUES (1005, 4, 63);
INSERT INTO sc
VALUES (1005, 7, 77);
INSERT INTO sc
VALUES (1006, 1, 70);
INSERT INTO sc
VALUES (1006, 2, 82);
INSERT INTO sc
VALUES (1006, 4, 78);
INSERT INTO sc
VALUES (1006, 6, 68);
INSERT INTO sc
VALUES (1006, 8, 55);
INSERT INTO sc
VALUES (1007, 2, 76);
INSERT INTO sc
VALUES (1007, 4, 90);
INSERT INTO sc
VALUES (1007, 6, 89);
INSERT INTO sc
VALUES (1008, 1, 81);
INSERT INTO sc
VALUES (1008, 2, 48);
mysql> select * from SC;
±-----±----±------+
| Sno | Cno | Score |
±-----±----±------+
| 1001 | 2 | 45 |
| 1001 | 3 | 85 |
| 1001 | 4 | 74 |
| 1001 | 6 | 80 |
| 1001 | 7 | 88 |
| 1002 | 1 | 90 |
| 1003 | 1 | 54 |
| 1003 | 2 | 62 |
| 1003 | 3 | 81 |
| 1003 | 4 | 80 |
| 1003 | 5 | 72 |
| 1003 | 6 | 88 |
| 1003 | 7 | 77 |
| 1003 | 8 | 67 |
| 1004 | 1 | 92 |
| 1005 | 1 | 53 |
| 1005 | 4 | 63 |
| 1005 | 7 | 77 |
| 1006 | 1 | 70 |
| 1006 | 2 | 82 |
| 1006 | 4 | 78 |
| 1006 | 6 | 68 |
| 1006 | 8 | 55 |
| 1007 | 2 | 76 |
| 1007 | 4 | 90 |
| 1007 | 6 | 89 |
| 1008 | 1 | 81 |
| 1008 | 2 | 48 |
±-----±----±------+
28 rows in set (0.00 sec)