ALTER TABLE添加一列并且添加检查约束-mysql教程-PHP中文网

首页

数据库

mysql教程

ALTER TABLE添加一列并且添加检查约束

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 07, 2016 pm 05:45 PM

creatednbspsql

oracle的约束

* 如果某个约束只作用于单独的字段，即可以在字段级定义约束，也可以在表级定义约束，但如果某个约束作用于多个字段，
必须在表级定义约束
* 在定义约束时可以通过constraint关键字为约束命名，如果没有指定，oracle将自动为约束建立默认的名称

定义primary key约束(单个字段）
create table employees (empno number(5) primary key,...)

sql>
sql> create table emp (empno number(4) not null,
2                    ename varchar2(10),
3                    job varchar2(9),
4                    mgr number(4),
5                    hiredate date,
6                    sal number(7, 2),
7                    comm number(7, 2),
8                    deptno number(2));

table created.

sql>
sql> insert into emp values (7369, 'smith', 'clerk',    7902, to_date('17-dec-1980', 'dd-mon-yyyy'), 800, null, 20);

1 row created.

sql> insert into emp values (7499, 'allen', 'salesman', 7698, to_date('20-feb-1981', 'dd-mon-yyyy'), 1600, 300, 30);

1 row created.

sql> insert into emp values (7521, 'ward', 'salesman', 7698, to_date('22-feb-1981', 'dd-mon-yyyy'), 1250, 500, 30);

1 row created.

sql> insert into emp values (7566, 'jones', 'manager', 7839, to_date('2-apr-1981', 'dd-mon-yyyy'), 2975, null, 20);

1 row created.

sql> insert into emp values (7654, 'martin', 'salesman', 7698,to_date('28-sep-1981', 'dd-mon-yyyy'), 1250, 1400, 30);

1 row created.

sql> insert into emp values (7698, 'blake', 'manager', 7839,to_date('1-may-1981', 'dd-mon-yyyy'), 2850, null, 30);

1 row created.

sql> insert into emp values (7782, 'clark', 'manager', 7839,to_date('9-jun-1981', 'dd-mon-yyyy'), 2450, null, 10);

1 row created.

sql> insert into emp values (7788, 'scott', 'analyst', 7566,to_date('09-dec-1982', 'dd-mon-yyyy'), 3000, null, 20);

1 row created.

sql> insert into emp values (7839, 'king', 'president', null,to_date('17-nov-1981', 'dd-mon-yyyy'), 5000, null, 10);

1 row created.

sql> insert into emp values (7844, 'turner', 'salesman', 7698,to_date('8-sep-1981', 'dd-mon-yyyy'), 1500, 0, 30);

1 row created.

sql> insert into emp values (7876, 'adams', 'clerk', 7788,to_date('12-jan-1983', 'dd-mon-yyyy'), 1100, null, 20);

1 row created.

sql> insert into emp values (7900, 'james', 'clerk', 7698,to_date('3-dec-1981', 'dd-mon-yyyy'), 950, null, 30);

1 row created.

sql> insert into emp values (7902, 'ford', 'analyst', 7566,to_date('3-dec-1981', 'dd-mon-yyyy'), 3000, null, 20);

1 row created.

sql> insert into emp values (7934, 'miller', 'clerk', 7782,to_date('23-jan-1982', 'dd-mon-yyyy'), 1300, null, 10);

1 row created.

sql>
sql> * from emp;

     empno ename      job              mgr hiredate         sal       comm     deptno
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 smith      clerk           7902 17-dec-80        800                    20
      7499 allen      salesman        7698 20-feb-81       1600        300         30
      7521 ward       salesman        7698 22-feb-81       1250        500         30
      7566 jones      manager         7839 02-apr-81       2975                    20
      7654 martin     salesman        7698 28-sep-81       1250       1400         30
      7698 blake      manager         7839 01-may-81       2850                    30
      7782 clark      manager         7839 09-jun-81       2450                    10
      7788 scott      analyst         7566 09-dec-82       3000                    20
      7839 king       president            17-nov-81       5000                    10
      7844 turner     salesman        7698 08-sep-81       1500          0         30
      7876 adams      clerk           7788 12-jan-83       1100                    20

     empno ename      job              mgr hiredate         sal       comm     deptno
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7900 james      clerk           7698 03-dec-81        950                    30
      7902 ford       analyst         7566 03-dec-81       3000                    20
      7934 miller     clerk           7782 23-jan-82       1300                    10

14 rows selected.

sql>
sql> alter table emp
2      add (
3        gender   varchar(10));

table altered.

sql>
sql> alter table emp
2      add constraint ck_gender
3      check (gender in ('male', 'female'));

table altered.

sql>
sql> update emp set gender = 'male' where mod(empno,2) = 0;

10 rows updated.

sql>
sql> select * from emp;

     empno ename      job              mgr hiredate         sal       comm     deptno gender
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ----------
      7369 smith      clerk           7902 17-dec-80        800                    20
      7499 allen      salesman        7698 20-feb-81       1600        300         30
      7521 ward       salesman        7698 22-feb-81       1250        500         30
      7566 jones      manager         7839 02-apr-81       2975                    20 male
      7654 martin     salesman        7698 28-sep-81       1250       1400         30 male
      7698 blake      manager         7839 01-may-81       2850                    30 male
      7782 clark      manager         7839 09-jun-81       2450                    10 male
      7788 scott      analyst         7566 09-dec-82       3000                    20 male
      7839 king       president            17-nov-81       5000                    10
      7844 turner     salesman        7698 08-sep-81       1500          0         30 male
      7876 adams      clerk           7788 12-jan-83       1100                    20 male

     empno ename      job              mgr hiredate         sal       comm     deptno gender
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ----------
      7900 james      clerk           7698 03-dec-81        950                    30 male
      7902 ford       analyst         7566 03-dec-81       3000                    20 male
      7934 miller     clerk           7782 23-jan-82       1300                    10 male

14 rows selected.

sql>
sql> update emp set gender = 'female' where mod(empno,2) = 1;

4 rows updated.

sql>
sql> select ename, job, gender from emp order by gender, ename;

ename      job       gender
---------- --------- ----------
allen      salesman female
king       president female
smith      clerk     female
ward       salesman female
adams      clerk     male
blake      manager   male
clark      manager   male
ford       analyst   male
james      clerk     male
jones      manager   male
martin     salesman male

ename      job       gender
---------- --------- ----------
miller     clerk     male
scott      analyst   male
turner     salesman male

14 rows selected.

sql>
sql> insert into emp (empno, ename, gender) values (8001, 'pat', 'unknown');
insert into emp (empno, ename, gender) values (8001, 'pat', 'unknown')
*
error at line 1:
ora-02290: check constraint (sys.ck_gender) violated

sql>
sql> drop table emp;

table dropped.

声明

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系admin@php.cn

在MySQL中使用视图的局限性是什么？May 14, 2025 am 12:10 AM

mysqlviewshavelimitations：1）他们不使用Supportallsqloperations，限制DatamanipulationThroughViewSwithJoinSorsubqueries.2）他们canimpactperformance，尤其是withcomplexcomplexclexeriesorlargedatasets.3）

确保您的MySQL数据库：添加用户并授予特权May 14, 2025 am 12:09 AM

porthusermanagementInmysqliscialforenhancingsEcurityAndsingsmenting效率databaseoperation.1）usecReateusertoAddusers，指定connectionsourcewith@'localhost'or@'％'。

哪些因素会影响我可以在MySQL中使用的触发器数量？May 14, 2025 am 12:08 AM

mysqldoes notimposeahardlimitontriggers，butacticalfactorsdeterminetheireffactective：1）serverConfiguration impactactStriggerGermanagement; 2）复杂的TriggerSincreaseSySystemsystem load; 3）largertablesslowtriggerperfermance; 4）highConconcConcrencerCancancancancanceTigrignecentign; 5）; 5）

mysql：存储斑点安全吗？May 14, 2025 am 12:07 AM

Yes,it'ssafetostoreBLOBdatainMySQL,butconsiderthesefactors:1)StorageSpace:BLOBscanconsumesignificantspace,potentiallyincreasingcostsandslowingperformance.2)Performance:LargerrowsizesduetoBLOBsmayslowdownqueries.3)BackupandRecovery:Theseprocessescanbe

mySQL：通过PHP Web界面添加用户May 14, 2025 am 12:04 AM

通过PHP网页界面添加MySQL用户可以使用MySQLi扩展。步骤如下：1.连接MySQL数据库，使用MySQLi扩展。2.创建用户，使用CREATEUSER语句，并使用PASSWORD()函数加密密码。3.防止SQL注入，使用mysqli_real_escape_string()函数处理用户输入。4.为新用户分配权限，使用GRANT语句。

mysql：blob和其他无-SQL存储，有什么区别？May 13, 2025 am 12:14 AM

mysql'sblobissuitableForStoringBinaryDataWithInareLationalDatabase，而alenosqloptionslikemongodb，redis和calablesolutionsoluntionsoluntionsoluntionsolundortionsolunsolunsstructureddata.blobobobsimplobissimplobisslowderperformandperformanceperformancewithlararengelitiate;

mySQL添加用户：语法，选项和安全性最佳实践May 13, 2025 am 12:12 AM

toaddauserinmysql，使用：createUser'username'@'host'Indessify'password'; there'showtodoitsecurely：1）choosethehostcarecarefullytocon trolaccess.2）setResourcelimitswithoptionslikemax_queries_per_hour.3）usestrong，iniquepasswords.4）Enforcessl/tlsconnectionswith

MySQL：如何避免字符串数据类型常见错误？May 13, 2025 am 12:09 AM

toAvoidCommonMistakeswithStringDatatatPesInMysQl，CloseStringTypenuances，chosethirtightType，andManageEngencodingAndCollationsEttingsefectery.1）usecharforfixed lengengters lengengtings，varchar forbariaible lengength，varchariable length，andtext/blobforlabforlargerdata.2 seterters seterters seterters seterters

See all articles