MySQL学习笔记（1）数据表操作

---在test数据库下创建数据表employee---
CREATE TABLE employee(
	id SMALLINT,
	num CHAR(10),
	name VARCHAR(20),
	sex ENUM('男','女'),
	birthday DATE,
	address VARCHAR(50),
	salary FLOAT(8,2)
	);

--查看test数据库下的所有表
SHOW TABLES;

--查看指定数据表employee的详细信息
SHOW CREATE TABLE employee;

-- 查看employee表的表结构
DESC employee;

DESCRIBE employee;

SHOW COLUMNS FROM employee;

-- 删除employee表

DROP TABLE IF EXISTS employee;


---添加记录---
INSERT employee VALUES(1,'8133500125','张三','男','1969-05-18','无锡万科城市花园',8010.75);
INSERT employee(name,birthday)VALUES('小王','1990-09-05');

---查询记录---
SELECT * FROM employee;

---非空约束----
CREATE TABLE t1(
  id SMALLINT NOT NULL,
  name VARCHAR(20)
  );
--测试
INSERT t1 VALUES(NULL,'tom');

---自增，主键约束----
---列级---
CREATE TABLE t2(
  id SMALLINT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(20) NOT NULL,
  age TINYINT
  );

---表级---
CREATE TABLE t2(
  id SMALLINT  AUTO_INCREMENT,
  name VARCHAR(20) NOT NULL,
  age TINYINT,
  PRIMARY key(id)
  );
--测试
INSERT t2 VALUES(NULL,'tom',20),
                (NULL,'rose',22);

---唯一约束---
---列级---
CREATE TABLE t3(
  id SMALLINT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(20) NOT NULL,
  age TINYINT,
  num CHAR(10) UNIQUE
  );
---表级---
CREATE TABLE t3(
  id SMALLINT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(20) NOT NULL,
  age TINYINT,
  num CHAR(10),
  UNIQUE(num)
  );
--测试
INSERT t3 VALUES(NULL,'tom',20,101),
                (NULL,'rose',22,102);

---默认约束只有列级约束---
CREATE TABLE t4(
  id SMALLINT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(20) NOT NULL,
  sex ENUM('男','女') DEFAULT '男',
  num CHAR(10) UNIQUE
  );

---外键约束---
---父表---
CREATE TABLE province(
  id SMALLINT PRIMARY KEY AUTO_INCREMENT,
  pname VARCHAR(10) NOT NULL UNIQUE
  );
INSERT province(pname) VALUES('江苏'),('湖北');
                          
---子表表级约束---
 CREATE TABLE users(
 	id SMALLINT PRIMARY KEY AUTO_INCREMENT,
 	name VARCHAR(20) NOT NULL,
 	pid SMALLINT ,
 	FOREIGN KEY(pid) REFERENCES province(id)
 	 	); 

 CREATE TABLE users(
  id SMALLINT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(20) NOT NULL,
  pid SMALLINT ,
  CONSTRAINT pid_id FOREIGN KEY(pid) REFERENCES province(id)
    );

 INSERT users VALUES(NULL,'tom',1);
 INSERT users VALUES(NULL,'rose',NULL);
 INSERT users VALUES(NULL,'tina',3);
--测试

---增加列---
ALTER TABLE users ADD age TINYINT AFTER name;
ALTER TABLE users ADD address VARCHAR(50) FIRST;



---删除列----
ALTER TABLE users DROP address ;


---增加主键---

CREATE TABLE USERS1(
 id SMALLINT,
 name VARCHAR(20) NOT NULL,
 pid SMALLINT
 );

ALTER TABLE users1 ADD  PRIMARY KEY(id);
---删除主键---

ALTER TABLE users1 DROP  PRIMARY KEY;


---增加唯一约束---
ALTER TABLE users1 ADD  UNIQUE(name);

---删除唯一约束---
SHOW INDEXES FROM users1\G;

ALTER TABLE users1 DROP INDEX name;


----增加外键----

ALTER TABLE users1 ADD FOREIGN KEY(pid) REFERENCES province(id);

----删除外键----
ALTER TABLE users1 DROP FOREIGN KEY users1_ibfk_1;


---修改列定义-----
ALTER TABLE users1 MODIFY name VARCHAR(30) AFTER pid;

---修改列名称----
ALTER TABLE users1 CHANGE name username VARCHAR(50);

----改表名----
ALTER TABLE users1 RENAME TO users_p;
RENAME TABLE users_p TO newusers;

--改存储引擎
ALTER TABLE employee ENGINE=MyISAM;

--改字符集
ALTER TABLE employee CHARSET=GBK;