数据库的表

1.学生表：Student (Sno, Sname, Ssex , Sage, Sdept)
学号，姓名，性别，年龄，所在系 Sno为主键
课程表：Course (Cno, Cname,)
课程号，课程名 Cno为主键
学生选课表：SC (Sno, Cno, Score)
学号，课程号，成绩 Sno,Cno为主键

1.用SQL语句创建学生表student，定义主键，姓名不能重名，性别只能输入男或女，所在系的默认值是 “计算机”。

#学生表
mysql> create table student(
    -> Sno varchar(20) not null primary key,
    -> Sname varchar(20) unique,
    -> Ssex varchar(20) check(Ssex='男' or Ssex='女'),
    -> Sage int(20),
    -> Sdept varchar(20) default '计算机'
    -> );
Query OK, 0 rows affected, 1 warning (0.02 sec)

#课程表
mysql> create table course(
    -> Cno varchar(20) primary key,
    -> Cname varchar(20)
    -> );
Query OK, 0 rows affected (0.03 sec)

#学生选课表
mysql> create table sc(
    -> Sno varchar(20),
    -> Cno varchar(20) primary key,
    -> Score varchar(20)
    -> );
Query OK, 0 rows affected (0.02 sec)

2.修改student 表中年龄（age）字段属性，数据类型由int 改变为smallint。

mysql> alter table student modify Sage smallint;

mysql> desc student;
+-------+-------------+------+-----+-----------+-------+
| Field | Type        | Null | Key | Default   | Extra |
+-------+-------------+------+-----+-----------+-------+
| Sno   | varchar(20) | NO   | PRI | NULL      |       |
| Sname | varchar(20) | YES  | UNI | NULL      |       |
| Ssex  | varchar(20) | YES  |     | NULL      |       |
| Sage  | smallint    | YES  |     | NULL      |       |
| Sdept | varchar(20) | YES  |     | 计算机    |       |
+-------+-------------+------+-----+-----------+-------+
5 rows in set (0.00 sec)

3.为SC表建立按学号（sno）和课程号（cno）组合的升序的主键索引，索引名为SC_INDEX 。

mysql> create unique index SC_INDEX on sc(Sno asc,Cno asc);

#查看
mysql> show create table sc\G
*************************** 1. row ***************************
       Table: sc
Create Table: CREATE TABLE `sc` (
  `Sno` varchar(20) DEFAULT NULL,
  `Cno` varchar(20) NOT NULL,
  `Score` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`Cno`),
  UNIQUE KEY `SC_INDEX` (`Sno`,`Cno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

4.创建一视图 stu_info,查询全体学生的姓名，性别，课程名，成绩。

mysql> create view stu_info as select Sname,Ssex,Cno,Score from student,sc where student.Sno=sc.Sno;

2.创建学生信息表：学号(主键)，姓名，年龄, 班级(外键关联到班级表的班级编号) 班级表：班级编号(主键), 班级名称

#班级表
mysql> create table 班级表(
    -> 班级编号 int primary key,
    -> 班级名称 varchar(30) 
    -> );
Query OK, 0 rows affected (0.02 sec)

#学生信息表
mysql> create table 学生信息表(
    -> 学号 int primary key,
    -> 姓名 varchar(30),
    -> 年龄 int,
    -> 班级 int,
    -> foreign key (班级) references 班级表(班级编号)
    -> );
Query OK, 0 rows affected (0.01 sec)

3.创建一张表(id, data): 创建一个存储过程，要求可以调用存储过程向表中插入200条，2000条，20000条 提示调用存储过程：call procedure_name(参数)

#创建表
mysql> create table storage_proc(
    -> id int primary key auto_increment,
    -> data varchar(64)
    -> );
Query OK, 0 rows affected (0.01 sec)

创建一个脚本文件
[root@0918db ~]# vim sto.sql
delimiter $$
create procedure procedure_data(IN num int)
begin
     declare i int default 1;
     while i <= num
     do
         insert into storage_proc(data) values(uuid());
         set i=i+1;
     end while;
end
$$

mysql> source /root/sto.sql
Query OK, 0 rows affected (0.00 sec)

mysql> show create procedure procedure_data\G
*************************** 1. row ***************************
           Procedure: procedure_data
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `procedure_data`(IN num int)
begin
     declare i int default 1;
     while i <= num
     do
         insert into storage_proc(data) values(uuid());
         set i=i+1;
     end while;
end
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

#调用存储过程
mysql> call procedure_data(200);
Query OK, 1 row affected (0.01 sec)

mysql> call procedure_data(2000);
Query OK, 1 row affected (0.05 sec)

mysql> call procedure_data(20000);
Query OK, 1 row affected (0.45 sec)

4.题3中相同的表：创建一个函数，要求可以调用函数向表中插入200条，2000条，20000条 提示调用函数：select function_name(参数)

#打开开关
log_bin_trust_function_creators=true;


#insert ignore忽略重复值
mysql> create function function_data(num int) returns varchar(64)
begin   declare i int default 1;   while i <= num DO     insert ignore into storage_proc (id, data) values (i, concat('Data', i));
   set i = i + 1;   end while;  return concat('Successfully inserteed',num,'records.');  end$$
Query OK, 0 rows affected (0.00 sec)


mysql> delimiter ;
mysql> select function_data(200);
+----------------------------------+
| function_data(200)               |
+----------------------------------+
| Successfully inserted200records. |
+----------------------------------+
1 row in set (0.00 sec)

mysql> select function_data(2000);
+-----------------------------------+
| function_data(2000)               |
+-----------------------------------+
| Successfully inserted2000records. |
+-----------------------------------+
1 row in set (0.02 sec)

mysql> select function_data(20000);
+------------------------------------+
| function_data(20000)               |
+------------------------------------+
| Successfully inserted20000records. |
+------------------------------------+
1 row in set (0.26 sec)

mysql>