students +------------+-------+-------------+---------------------+| student_id | name  | description | create_time         |+------------+-------+-------------+---------------------+| 1          | name1 | desc1       | 1970.01.01 00:00:01 || 2          | name2 | desc2       | 1970.01.01 00:00:02 || 3          | name3 | desc3       | 1970.01.01 00:00:03 || 4          | name4 | desc4       | 1970.01.01 00:00:04 |+------------+-------+-------------+---------------------+student_quotas+------------+----------+-------------+| student_id | resource | quota_value |+------------+----------+-------------+| 1          | animal   |       40964 || 1          | gas      |       40961 || 1          | oil      |          22 || 1          | water    |          20 || 2          | animal   |       40964 || 2          | gas      |       40957 || 2          | oil      |          25 || 2          | water    |          21 || 3          | animal   |       40962 || 3          | gas      |       40959 || 3          | oil      |          21 || 3          | water    |          23 || 4          | animal   |       40965 || 4          | gas      |       40963 || 4          | oil      |          24 || 4          | water    |          22 |+------------+----------+-------------+数据库操作：    show databases;    CREATE DATABASE student_db DEFAULT CHARACTER SET 'utf8';    use student_db;    drop database student_db;表操作：    show tables;    drop view student_resource;    drop table student_quotas;     drop table students;    desc students;    desc student_quotas;    desc student_resource;表操作    CREATE TABLE students (     student_id varchar(50) NOT NULL,     name varchar(50) NOT NULL,     description text,  # 0-65535     create_time varchar(40) DEFAULT '1970.01.01 00:00:01',     PRIMARY KEY (student_id)    )ENGINE=InnoDB DEFAULT CHARSET=utf8;    CREATE TABLE student_quotas (     student_id varchar(50) NOT NULL,     resource varchar(20) NOT NULL,     quota_value int DEFAULT 0,     index idx_student_id(student_id),     PRIMARY KEY (student_id, resource),     FOREIGN KEY fk_student(student_id)     REFERENCES students(student_id)     ON DELETE CASCADE    )ENGINE=InnoDB DEFAULT CHARSET=utf8;视图操作    create view student_resource as    select water_table.student_id, water, oil, gas, animal    from     (    (select student_id, quota_value as water    from student_quotas    where resource = "water") as water_table,    (select student_id, quota_value as oil    from student_quotas    where resource = "oil") as oil_table,    (select student_id, quota_value as gas    from student_quotas    where resource = "gas") as gas_table,    (select student_id, quota_value as animal    from student_quotas    where resource = "animal") as animal_table    )    where water_table.student_id = oil_table.student_id     and oil_table.student_id = gas_table.student_id    and gas_table.student_id = animal_table.student_id;数据操作：    增：        insert into students(name, student_id, description, create_time) values        ('name1', '1', 'desc1', '1970.01.01 00:00:01'),        ('name2', '2', 'desc2', '1970.01.01 00:00:02'),        ('name3', '3', 'desc3', '1970.01.01 00:00:03'),        ('name4', '4', 'desc4', '1970.01.01 00:00:04');        insert into student_quotas(student_id, resource, quota_value) values        ('1', 'water', 20), ('1', 'oil', 22), ('1', 'gas', 40961), ('1', 'animal', 40964),        ('2', 'water', 21), ('2', 'oil', 25), ('2', 'gas', 40957), ('2', 'animal', 40964),        ('3', 'water', 23), ('3', 'oil', 21), ('3', 'gas', 40959), ('3', 'animal', 40962),        ('4', 'water', 22), ('4', 'oil', 24), ('4', 'gas', 40963), ('4', 'animal', 40965);    删：        delete from students;        delete from student_quotas;        delete from students where student_id = '1';    改：        update students set name = '%s', description = '%s' where student_id = '%s';        update student_quotas set resource = '%s', quota_value = %d where student_id = '%s' and resource = '%s';    查：        select * from students;        select * from student_quotas;        select count(*) from students;        select count(*) from students where student_id like '%3%';#下划线_需要转义\_        student_id + 组合查询:        select name, students.student_id, description, create_time, resource, quota_value        from students, student_quotas        where students.student_id = '1' and students.student_id = student_quotas.student_id;            指定字段student_id排序查询        select name, students.student_id, description, create_time, resource, quota_value        from students, student_quotas        where students.student_id like '%3%' and students.student_id = student_quotas.student_id        order by students.student_id asc        limit 0, 2;        按资源值(非字段，但是是固定的值)排序查询(借助于视图：创建资源值字段water, oil, gas, animal)        select name, students.student_id, description, resource, quota_value        from student_resource, students, student_quotas        where student_resource.student_id like '%2%' and student_resource.student_id=students.student_id and students.student_id = student_quotas.student_id        order by water asc        limit 0, 2;