mysql如何创建表时约定一个字符串有多个不同范围的字符段格式？

[ytingting_gd]

求解，这段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),
)

[chengchow2001]

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,
   branchID int foreign key references branch(branchID),
   joinDate datetime default now(),
   pid int foreign key references privilege(pid),
 ) 

外键问题自己解决，没看到你另外两张表

[ytingting_gd]

MySQL 返回：
#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；

[ytingting_gd]

而且我删除那些相关外键的代码 仍然是有问题的：#1067 - Invalid default value for 'joinDate'

[ACMAIN_CHM]

MYSQL中不支持 CHECK. 所以无法实现。除非用触发器去检查。

[chengchow2001]

你版本的问题吧

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)

[chengchow2001]

mysql> desc teachers;
+----------+--------------+------+-----+---------+-------+
| 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)

[ytingting_gd]

(⊙o⊙)哦，我忘记说了，我使用的是phpMyAdmin界面工具的sql语句

[ytingting_gd]

(⊙o⊙)哦，我忘记说了，我使用的是phpMyAdmin界面工具的sql语句 其实我想问的是怎么在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 远远不够用，求教！