MySQL学习笔记——20170809

数据完整性

• 主键约束 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为女' );-- UNIQUECREATE TABLE `t_score` (`name`  VARCHAR(20) NOT NULL ,`score`  int NULL ,FOREIGN KEY (`name`) REFERENCES `t_user` (`name`));SHOW CREATE TABLE t_user;