* 唯一约束修饰的字段具有唯一性,不能重复。但可以为NULL。 * 案例:给某一列添加unique drop table if exists t_user; create table t_user( id int, username varchar(255) unique // 列级约束 ); insert into t_user values(1,'zhangsan'); insert into t_user values(2,'zhangsan'); ERROR 1062 (23000): Duplicate entry 'zhangsan' for key 'username'
insert into t_user(id) values(2); insert into t_user(id) values(3); insert into t_user(id) values(4); * 案例:给两个列或者多个列添加unique drop table if exists t_user; create table t_user( id int, usercode varchar(255), username varchar(255), unique(usercode,username) // 多个字段联合起来添加1个约束unique 【表级约束】 );
insert into t_user values(1,'111','zs'); insert into t_user values(2,'111','ls'); insert into t_user values(3,'222','zs'); select * from t_user; insert into t_user values(4,'111','zs'); ERROR 1062 (23000): Duplicate entry '111-zs' for key 'usercode'
drop table if exists t_user; create table t_user( id int, usercode varchar(255) unique, username varchar(255) unique ); insert into t_user values(1,'111','zs'); insert into t_user values(2,'111','ls'); ERROR 1062 (23000): Duplicate entry '111' for key 'usercode' * 注意:not null约束只有列级约束。没有表级约束。
1.2、主键约束
* 怎么给一张表添加主键约束呢? drop table if exists t_user; create table t_user( id int primary key, // 列级约束 username varchar(255), email varchar(255) ); insert into t_user(id,username,email) values(1,'zs','zs@123.com'); insert into t_user(id,username,email) values(2,'ls','ls@123.com'); insert into t_user(id,username,email) values(3,'ww','ww@123.com'); select * from t_user; +----+----------+------------+ | id | username | email | +----+----------+------------+ | 1 | zs | zs@123.com | | 2 | ls | ls@123.com | | 3 | ww | ww@123.com | +----+----------+------------+
insert into t_user(id,username,email) values(1,'jack','jack@123.com'); ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
insert into t_user(username,email) values('jack','jack@123.com'); ERROR 1364 (HY000): Field 'id' doesn't have a default value
* 使用表级约束方式定义主键: drop table if exists t_user; create table t_user( id int, username varchar(255), primary key(id) ); insert into t_user(id,username) values(1,'zs'); insert into t_user(id,username) values(2,'ls'); insert into t_user(id,username) values(3,'ws'); insert into t_user(id,username) values(4,'cs'); select * from t_user;
insert into t_user(id,username) values(4,'cx'); ERROR 1062 (23000): Duplicate entry '4' for key 'PRIMARY'
以下内容是演示以下复合主键,不需要掌握: drop table if exists t_user; create table t_user( id int, username varchar(255), password varchar(255), primary key(id,username) ); insert .......
* mysql提供主键值自增:(非常重要。) drop table if exists t_user; create table t_user( id int primary key auto_increment, // id字段自动维护一个自增的数字,从1开始,以1递增。 username varchar(255) ); insert into t_user(username) values('a'); insert into t_user(username) values('b'); insert into t_user(username) values('c'); insert into t_user(username) values('d'); insert into t_user(username) values('e'); insert into t_user(username) values('f'); select * from t_user;
insert into t_class values(101,'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'); insert into t_class values(102,'yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy');
insert into t_student values(1,'zs1',101); insert into t_student values(2,'zs2',101); insert into t_student values(3,'zs3',102); insert into t_student values(4,'zs4',102); insert into t_student values(5,'zs5',102); insert into t_student values(6,'zs6',102); select * from t_class; select * from t_student;
insert into t_student values(7,'lisi',103); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`bjpowernode`.INT `t_student_ibfk_1` FOREIGN KEY (`classno`) REFERENCES `t_class` (`cno`))
mysql> select * from t_user; +----+----------+ | id | username | +----+----------+ | 1 | zs | | 4 | wangwu | | 5 | rose | | 6 | jack | +----+----------+ 4 rows in set (0.00 sec)
* 演示两个事务,假如隔离级别 演示第1级别:读未提交 set global transaction isolation level read uncommitted; 演示第2级别:读已提交 set global transaction isolation level read committed; 演示第3级别:可重复读 set global transaction isolation level repeatable read;
5.4、面向视图操作? mysql> select * from myview; +-------+--------+ | empno | ename | +-------+--------+ | 7369 | SMITH | | 7499 | ALLEN | | 7521 | WARD | | 7566 | JONES | | 7654 | MARTIN | | 7698 | BLAKE | | 7782 | CLARK | | 7788 | SCOTT | | 7839 | KING | | 7844 | TURNER | | 7876 | ADAMS | | 7900 | JAMES | | 7902 | FORD | | 7934 | MILLER | +-------+--------+
create table emp_bak as select * from emp; create view myview1 as select empno,ename,sal from emp_bak; update myview1 set ename='hehe',sal=1 where empno = 7369; // 通过视图修改原表数据。 delete from myview1 where empno = 7369; // 通过视图删除原表数据。