数据完整性
- 主键约束 PRIMARY KEY
- 外键约束 PRIMARY KEY (Engine需要是InnoDB)
- 非空约束 NOT NULL
- 默认约束 DEFAULT
- 唯一约束 UNIQUE
修改数据表
- ALTER TABLE t_user
- ADD 字段 属性 约束
- DROP 字段 属性 约束
- MODIFY 字段 属性
- CHANGE 字段 属性
示例:
ALTER TABLE t_score ADD FOREIGN KEY (stu_id) REFERENCES t_student(stu_id);-- 添加外键
ALTER TABLE t_score ADD address VARCHAR(50);
ALTER TABLE t_student MODIFY stu_gender bit(1);
ALTER TABLE t_student CHANGE stu_gender stu_gender2 bit(1);-- 可以改名
ALTER TABLE t_student MODIFY stu_gender bit(1) DEFAULT 0 COMMENT '性别,1为男0为女' ;-- 默认值 注释
SHOW CREATE TABLE t_user;-- 查看一下t_user表的创建属性
练习
-- 创建数据库
CREATE DATABASE d_test;
DROP DATABASE d_test;
-- 创建表
CREATE TABLE t_user(
id int PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20),
age int
);
-- 增、删、改、查
INSERT INTO t_user (name, age) VALUES ("xiaowang", 22);
DELETE FROM t_user WHERE name="zhangsan";
UPDATE t_user SET name="xiaowang" WHERE id=4;
SELECT * FROM t_user WHERE gender="female" AND age<50;
SELECT * FROM t_user WHERE sid="S_1001" OR name="lisi";
SELECT * FROM t_user WHERE sid="S_1001" OR sid="S_1002" OR sid="S_1003";
SELECT * FROM t_user WHERE NOT (sid="S_1001" OR sid="S_1002" OR sid="S_1003");
SELECT * FROM t_user WHERE age IS NULL;
SELECT * FROM t_user WHERE age>=20 AND age<=50;
SELECT * FROM t_user WHERE name LIKE "___";
SELECT * FROM t_user WHERE name LIKE "__i";
SELECT * FROM t_user WHERE name LIKE "Z%";
SELECT * FROM t_user WHERE name LIKE "_i%";
SELECT * FROM t_user WHERE name LIKE "%a%";
SELECT DISTINCT gender FROM t_user;
SELECT ename,sal+IFNULL(comm, 0) FROM t_user;
SELECT * FROM t_user ORDER BY age ASC;-- 升序
SELECT * FROM t_user ORDER BY age DESC;-- 降序
SELECT * FROM t_user ORDER BY sal DESC, empno ASC;
SELECT COUNT(*) FROM t_user WHERE sal>2500;
SELECT depton,SUM(empno) FROM t_user GROUP BY depton;
SELECT depton,SUM(sal) FROM t_user GROUP BY depton HAVING SUM(sal)>9000;
--
CREATE TABLE t_student2(
stu_id VARCHAR(6) PRIMARY KEY NOT NULL,
stu_name VARCHAR(10) NOT NULL,
stu_gender bit(1) NOT NULL COMMENT '性别,1为男0为女'
);
-- UNIQUE
CREATE TABLE `t_score` (
`name` VARCHAR(20) NOT NULL ,
`score` int NULL ,
FOREIGN KEY (`name`) REFERENCES `t_user` (`name`)
);
SHOW CREATE TABLE t_user;