求解,这段sql语句如何修改才能用到mysql中
create table teachers(
tid varchar(11) primary key not null check(tid like '[A-Z][A-Z][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),
tName varchar(60) not null,
tAge int check (tAge between 0 and 100) not null,
tSex char(2) default '男' check (tSex in ('男','女')),
tTel varchar(13) check(tTel like '1[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
or tTel like '[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
or tTel like '[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
or tTel like '[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),
tNation varchar(6) not null,
tICN varchar(18) unique not null check(tICN like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
or tICN like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9,X]'),
tAddress varchar(100) not null,
branchID int foreign key references branch(branchID),
joinDate datetime default getdate(),
pid int foreign key references privilege(pid),
)
create table teachers(
tid varchar(11) primary key not null check(tid like '[A-Z][A-Z][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),
tName varchar(60) not null,
tAge int check (tAge between 0 and 100) not null,
tSex char(2) default '男' check (tSex in ('男','女')),
tTel varchar(13) check(tTel like '1[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
or tTel like '[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
or tTel like '[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
or tTel like '[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),
tNation varchar(6) not null,
tICN varchar(18) unique not null check(tICN like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
or tICN like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9,X]'),
tAddress varchar(100) not null,
branchID int foreign key references branch(branchID),
joinDate datetime default getdate(),
pid int foreign key references privilege(pid),
)
tid varchar(11) primary key not null check(tid like '[A-Z][A-Z][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),
tName varchar(60) not null,
tAge int not null check (tAge between 0 and 100),
tSex char(2) default '男' check (tSex in ('男','女')),
tTel varchar(13) check(tTel like '1[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
or tTel like '[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
or tTel like '[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
or tTel like '[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),
tNation varchar(6) not null,
tICN varchar(18) unique not null check(tICN like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
or tICN like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9,X]'),
tAddress varchar(100) not null,
branchID int foreign key references branch(branchID),
joinDate datetime default now(),
pid int foreign key references privilege(pid),
) 外键问题自己解决,没看到你另外两张表
#1064 - 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 'foreign key references branch(branchID),
joinDate datetime default now(),
' at line 14 我在某处看见:
1,一般情况下,字段的default值,必须是一个常数,即不可以使用函数或表达式,比如now()或current_date;
仍然是有问题的:#1067 - Invalid default value for 'joinDate'
所以无法实现。除非用触发器去检查。
mysql> create table teachers(
-> tid varchar(11) primary key not null check(tid like '[A-Z][A-Z][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),
-> tName varchar(60) not null,
-> tAge int not null check (tAge between 0 and 100),
-> tSex char(2) default '.' check (tSex in ('.','.')),
-> tTel varchar(13) check(tTel like '1[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
-> or tTel like '[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
-> or tTel like '[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
-> or tTel like '[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),
-> tNation varchar(6) not null,
-> tICN varchar(18) unique not null check(tICN like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
-> or tICN like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9,X]'),
-> tAddress varchar(100) not null
-> );
Query OK, 0 rows affected (0.00 sec)
+----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| tid | varchar(11) | NO | PRI | NULL | |
| tName | varchar(60) | NO | | NULL | |
| tAge | int(11) | NO | | NULL | |
| tSex | char(2) | YES | | . | |
| tTel | varchar(13) | YES | | NULL | |
| tNation | varchar(6) | NO | | NULL | |
| tICN | varchar(18) | NO | UNI | NULL | |
| tAddress | varchar(100) | NO | | NULL | |
+----------+--------------+------+-----+---------+-------+
8 rows in set (0.01 sec)
其实我想问的是怎么在phpMyAdmin界面工具创建表时候给字段附加约束,比如说外键,自动增加等等许多的约束。有详细的内容和例子吗?
现在我在phpMyAdmin界面工具的sql操作语句中只总结了一点点的例子:
1.id varchar(11) primary key not null auto_increment(自动增加);
2.phpmyadmin中设置外键
alter table teachers
add foreign key(branchID) references branch(branchID)
on delete cascade
on update cascade
3.时间
ALTERTABLE`students`ADD`enrollmentDate` TIMESTAMP ONUPDATECURRENT_TIMESTAMPNOTNULL
4.create view view_sName_bName_sintegrals(stuid,stuname,banji,jifen)
as select students.sid,students.sName,banji.bName,integrals.sintegrals from students,banji,integrals
where students.sid = integrals.sid and students.bID = banji.bID
远远不够用,求教!
其实我想问的是怎么在phpMyAdmin界面工具创建表时候给字段附加约束,比如说外键,自动增加等等许多的约束。有详细的内容和例子吗?
现在我在phpMyAdmin界面工具的sql操作语句中只总结了一点点的例子:
1.id varchar(11) primary key not null auto_increment(自动增加);
2.phpmyadmin中设置外键
alter table teachers
add foreign key(branchID) references branch(branchID)
on delete cascade
on update cascade
3.时间
ALTERTABLE`students`ADD`enrollmentDate` TIMESTAMP ONUPDATECURRENT_TIMESTAMPNOTNULL
4.create view view_sName_bName_sintegrals(stuid,stuname,banji,jifen)
as select students.sid,students.sName,banji.bName,integrals.sintegrals from students,banji,integrals
where students.sid = integrals.sid and students.bID = banji.bID
远远不够用,求教!