MySQL，做最好的java程序员

关于MySQL的学习

MySQL主要有创建数据库，连接数据库，创建表单，插入数据，查询插入内容等等

简单创建一个基本MySQL

– 创建学生管理系统的数据库
CREATE DATABASE zcs_db;

– 连接studentsystem这个数据库
USE zcs_db;

– 创建一个学生表

CREATE TABLE user
(
u_id INT,
u_name VARCHAR(50),
passwork VARCHAR(2),
);

– 查看学生表结构
DESC student;

– 插入五条数据
INSERT INTO user VALUES(1,‘’,'’);
INSERT INTO user VALUES(2,‘soulmate’,‘47’);
– 查询学生信息
SELECT * FROM student;
SELECT * FROM student WHERE

约束

– 单字段主键约束
CREATE TABLE emp
(
id INT PRIMARY KEY,
name VARCHAR(20),
dept_id INT,
salary FLOAT
);

– 插入数据，验证主键
INSERT INTO emp VALUES(1,‘小明’,4,5000);

CREATE TABLE emp_1
(
id INT ,
name VARCHAR(20),
dept_id INT,
salary FLOAT,
PRIMARY KEY(id)
);

– 多字段联合主键约束
CREATE TABLE emp_2
(
id INT ,
name VARCHAR(20),
dept_id INT,
salary FLOAT,
PRIMARY KEY(id,name)
);

– 插入数据，验证主键
INSERT INTO emp_2 VALUES(1,‘小明’,4,5000);
INSERT INTO emp_2 VALUES(1,‘小红’,4,5000);

– 外键约束
CREATE TABLE dept
(
id INT PRIMARY KEY,
name VARCHAR(20),
location VARCHAR(50)
)ENGINE=InnoDB;

CREATE TABLE emp_3
(
id INT PRIMARY KEY,
name VARCHAR(20),
dept_id INT,
salary FLOAT,
CONSTRAINT fk_emp_dept FOREIGN KEY(dept_id) REFERENCES dept(id)
)ENGINE=InnoDB;

–
INSERT INTO emp_3 VALUES(1,‘小明’,4,5000);

INSERT INTO dept VALUES(4,‘大数据4班’,‘广现’);

INSERT INTO emp_3 VALUES(1,‘小明’,4,5000);

– 非空约束
CREATE TABLE emp_4
(
id INT PRIMARY KEY,
name VARCHAR(20) NOT NULL,
dept_id INT,
salary FLOAT
);
– 插入一条姓名null的语句
INSERT INTO emp_4 VALUES(1,’’,4,5000);
INSERT INTO emp_4 VALUES(2,NULL,4,5000);

– 唯一性约束
CREATE TABLE emp_5
(
id INT PRIMARY KEY,
name VARCHAR(20) UNIQUE,
dept_id INT,
salary FLOAT
);
– 插入一条姓名null的语句
INSERT INTO emp_5 VALUES(1,‘你好’,4,5000);
INSERT INTO emp_5 VALUES(2,NULL,4,5000);
INSERT INTO emp_5 VALUES(3,’’,4,5000);

– 默认约束
CREATE TABLE emp_6
(
id INT PRIMARY KEY,
name VARCHAR(20),
dept_id INT,
salary FLOAT DEFAULT 2000
);

INSERT INTO emp_6(id,name,dept_id) VALUES(2,‘你好’,4);

– 属性值自动增长约束
CREATE TABLE emp_7
(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20),
dept_id INT,
salary FLOAT
);
INSERT INTO emp_7(name,dept_id) VALUES(‘你好’,4);

修改

– 修改表明名

ALTER TABLE class_1 RENAME TO tb_student_1;

–修改数据表的字段名
ALTER TABLE tb_student_1 CHANGE name st_name VARCHAR(25);
ALTER TABLE tb_student_1 CHANGE class_id st_class_id int(11);

–修改数据表字段的类型
ALTER TABLE tb_student_1 MODIFY st_name CHAR(20);

–添加字段(默认添加在已有字段后面)
ALTER TABLE student_4 ADD phone INT;

–添加字段(添加到所有字段前面)
ALTER TABLE student_4 ADD test_id INT FIRST;

–添加字段(添加到某个字段的后面)
ALTER TABLE student_4 ADD sex CHAR(3) AFTER st_name;
y
–删除数据表中的某个字段

ALTER TABLE student DROP test_id

–更改数据表的存储引擎
ALTER TABLE user_1 ENGINE=MYISAM;

–创建一个zcs_article文章表
CREATE TABLE zcs_article
(
a_id INT PRIMARY KEY AUTO_INCREMENT,
content VARCHAR(250) NOT NULL,
weiter VARCHAR(20) NOT NULL,
at_id INT,
CONSTRAINT fa_a_at FOREIGN KEY (at_id) REFERENCES zcs_article_type(at_id)
)ENGINE=InnoDB;

–文章类型表
CREATE TABLE zcs_article_type
(
at_id INT PRIMARY KEY AUTO_INCREMENT,
at_name VARCHAR(250) NOT NULL
)ENGINE=InnoDB;

INSERT INTO zcs_article_type VALUES(2,250);
INSERT INTO zcs_article_type VALUES(3,250);
INSERT INTO zcs_article_type VALUES(4,250);
INSERT INTO zcs_article_type VALUES(5,250);
INSERT INTO zcs_article_type VALUES(6,250);

INSERT INTO zcs_article VALUES(22,’***’,222,2);
INSERT INTO zcs_article VALUES(33,’***’,222,2);
INSERT INTO zcs_article VALUES(44,’***’,222,2);
INSERT INTO zcs_article VALUES(55,’***’,222,2);
INSERT INTO zcs_article VALUES(66,’***’,222,2);
INSERT INTO zcs_article VALUES(77,’***’,222,2);
INSERT INTO zcs_article VALUES(88,’***’,222,2);
INSERT INTO zcs_article VALUES(99,’***’,222,2);
INSERT INTO zcs_article VALUES(21,’***’,222,2);
INSERT INTO zcs_article VALUES(25,’***’,222,2);

	 SELECT * FROM zcs_article_type;
	 SELECT * FROM zcs_article;