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为女' 
);


-- 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;