MySQL statement test--data definition

MySQL statement test--data definition

First, mode/*1. Mode Delete operation */drop schema Zyl;         /* After adding cascade or restrict error *//*2. Mode creation Operation */create schema Zyl; /* After adding authorizaition error * */second, Form/*1. Table Creation Action *//* (1) CREATE TABLE mode one: After creating the schema separately, double-click the schema on the left to bold and create Table*/create Table Student (Sno char (9) Primary key,/* column-level integrity constraint, Sno is the main code */sname char (unique),/*sname takes a unique value */ssex char (2), Sage SMA Llint,sdept char); CREATE TABLE Course (Cno char (9) Primary key,/* column-level integrity constraint, Cno is the main code */cname char (+) NOT NULL,/*cname does not For empty */cpno char (4),/*cpno means first course */ccredit smallint,foreign key (cpno) references Course (Cno)//table-level integrity constraints, CPNO is external code, The referenced table is course, the Reference column is cno*/), create TABLE SC (Sno char (9), Cno char (4), Grade smallint,primary key (SNO,CNO),/* Main code consists of two attributes, must be defined as table-level Integrity */foreign key (Sno) references student (Sno),/* table-level integrity constraints, SNO is outer code, referenced table is student*/foreign key (Cno) References Course (Cno)/* table-level integrity constraints, CPNO is a foreign code, the referenced table is Course, the reference column is cno*/);/* (2) method for creating tables Two: Create table schema name. Table name; S_t.student (Sno char (9) Primary key,/* column-level integrity constraint, Sno is the main code */sname CHAR (c) unique,/*sname takes a unique value */ssex char (2), Sage smallint,sdept char (20));/* (3) Create TABLE method Three: Create the schema at the same time this method is invalid, error create SC Hema testcreate table Student (Sno char (9) Primary key,/* column-level integrity constraint, Sno is the main code */sname char (a) unique,/*sname takes a unique value */ssex char (2), Sage smallint,sdept char (20)), *//*2. Table modification Operation *//* (1) Add column, regardless of whether the table has data, the newly added column is null value ALTER TABLE name add new column name data type (integrity constraint) ; */alter table Student Add S_entrance date;/* (2) Add table-level constraint ALTER TABLE name add tag integrity constraint; */alter table course add unique (Cna          me);/* (3) Modify the data type not the ALTER TABLE name on the book ALTER column name data type; Instead, ALTER TABLE name modify column name data type; */alter table student Modify column Sage int;/* (4) Delete columns, followed by cascade or restrict still error a Lter table name drop column name; */alter table Student Drop s_entrance;/* (5) Delete integrity constraint, error ALTER TABLE table name drop constraint integrity constraint name; *//  The delete operation of the table *//* (1) Normal table delete drop table table name [cascade/restrict]; *//*① not add cascade, the direct deletion, at the same time, SC table is also deleted */drop table Student;/*② plus cascade or restrict, run unsuccessful */drop table student cascade;/* (2) Table delete operation with view drop tabLe table name _/restrict/cascade;*/create table Student (Sno char (9) Primary key,/* column-level integrity constraint, Sno is the main code */sname char (a) unique, /*sname takes a unique value */ssex char (2), Sage smallint,sdept char ()), CREATE view is_student/*student table to establish views */asselect Sno,sname,    Sagefrom studentwhere sdept= "is";d ROP table student restrict; /* Delete Student table succeeded, is_student view not deleted */drop View is_student restrict;drop table student Cascade;     /* Delete Student table succeeded, is_student view did not delete */drop view is_student cascade;drop table student; /* Delete Student table succeeded, is_student view was not deleted */drop view is_student; index/*1. Create Unique/cluster index index name on table name (column name order (asc/de SC)); *//* index: In a relational database, an index is a single, physical storage structure that sorts the values of one or more columns in a database table, a collection of one or more column values in a table, and a logical pointer list corresponding to the data pages that physically identify those values in the table. The index acts as a catalog of books, and you can quickly find what you want based on the page numbers in the catalog. Unique: Indicates that each index value for this index only corresponds to a unique data record cluster: Indicates that the index to be established is a clustered index (7th 7.5.2) ASC is ascending, desc is descending, and the default value is asc*/create unique index Stusno On student (Sno), create unique index cousno on Course (Cno), create unique index scno on SC (Sno asc,cno desc),/*2. Index modification and deletion are required operation on the basis of a table *//* (1) Modify index (index rename) operation   ALTER TABLE table name Rename index old index name to new indexed name; */alter index scno rename to Scsno;  /* ERROR */alter Table SC Rename index scno to Scsno; /* Execution succeeded *//* (2) Index delete operation ALTER TABLE name DROP index name; */create Unique index stusname on student (Sname); ALTER TABLE student DROP INDEX stusname;

MySQL statement test-data definition