mysql笔记

/* 实体完整性

1.主键约束 （primary key）
注：每个表都要有一个主键，数据唯一，且不能为null
*/

CREATE DATABASE day05;
USE day05;

– 主键约束

CREATE TABLE student(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) UNIQUE NOT NULL,
address VARCHAR(50) DEFAULT ‘北京’
);

ALTER TABLE student ADD PRIMARY KEY(id,name);

INSERT INTO student VALUES(1,”xx”);
INSERT INTO student VALUES(2,”yy”);

DROP TABLE student;

SELECT * FROM student;

– 域完整性

– 1.非空约束 UNIQUE NOT NULL

– 2.default 默认约束

– 引用完整性

– 学生表
CREATE TABLE student(
stuid VARCHAR(10)PRIMARY KEY,
stuname VARCHAR(50)
);

– 分数表
CREATE TABLE score(
stuid VARCHAR(10),
score INT,
courseid INT,
CONSTRAINT fk_stu_sco FOREIGN KEY(stuid) REFERENCES student(stuid)
);

SELECT * FROM student;
INSERT INTO student VALUES(‘1001’,’张三峰’);
INSERT INTO student VALUES(‘1002’,’张无忌’);
INSERT INTO student VALUES(‘1003’,’王尼玛’);
INSERT INTO student VALUES(‘1004’,’王老五’);
INSERT INTO student VALUES(‘1005’,’刘老六’);

SELECT * FROM score;
INSERT INTO score VALUES(‘1001’,98,1);
INSERT INTO score VALUES(‘1002’,95,1);
INSERT INTO score VALUES(‘1002’,67,2);
INSERT INTO score VALUES(‘1003’,83,2);
INSERT INTO score VALUES(‘1003’,57,3);
INSERT INTO score VALUES(‘1005’,23,3);

SELECT * FROM student s,score c WHERE s.stuid=c.stuid;

SELECT * FROM student s LEFT JOIN score c ON s.stuid=c.stuid;

– ————联合查询——————
create table A(
name varchar(10),
score int
);

create table B(
name varchar(10),
score int
);

INSERT into A values(‘a’,10),(‘b’,20),(‘c’,30);
insert into B values(‘a’,10),(‘b’,20),(‘d’,40);

– 合并结果集 要合并输出，列信息必须一样，如果多了就只查相同的就可以了

SELECT * FROM A
UNION – 重复的只输出一次
SELECT * FROM B;

SELECT * FROM A
UNION ALL – 并集output
SELECT * FROM B;

CREATE TABLE stu1(
sid INT PRIMARY KEY ,
sname VARCHAR(50)
);

CREATE TABLE tea1(
tid INT,
tname VARCHAR(50)
);

CREATE TABLE tea_stu_rel(
tid INT,
sid INT
);

ALTER TABLE tea_stu_rel ADD CONSTRAINT fk_tid FOREIGN KEY(tid) REFERENCES tea1(tid);

ALTER TABLE tea_stu_rel ADD CONSTRAINT fk_ssid FOREIGN KEY(sid) REFERENCES stu1(sid);

– 内连接查询

SELECT s.stuid,s.stuname,c.score FROM day05.student s JOIN score c ON s.stuid = c.stuid;

SELECT s.stuid,s.stuname,c.score FROM day05.student s ,score c WHERE s.stuid = c.stuid;

– 外连接查询
– 参照left左边那个表，
SELECT * FROM day05.student s LEFT JOIN score c ON s.stuid = c.stuid;
– 参照right右边的表
SELECT * FROM day05.student s RIGHT JOIN score c ON s.stuid = c.stuid;

– 自然连接

SELECT * FROM day05.student NATURAL JOIN score;

– 子查询（存在两个或两个以上的select）

SELECT * FROM mydb1.emp1 WHERE deptno=(
SELECT deptno FROM mydb1.emp1 WHERE ename=’SCOTT’);

SELECT * FROM mydb1.emp1 WHERE sal>(SELECT sal FROM mydb1.emp1 WHERE ename=’JONES’);

SELECT * FROM mydb1.emp1 WHERE (job,sal) IN (SELECT job,sal FROM mydb1.emp1 WHERE ename=’MARTIN’);

SELECT * FROM mydb1.emp1 WHERE empno IN (SELECT mgr FROM mydb1.emp1 GROUP BY mgr HAVING count(mgr)>=2);

– 自连接

SELECT ename FROM mydb1.emp1 WHERE empno=(
SELECT mgr FROM mydb1.emp1 WHERE empno=7369);

SELECT e1.empno,e1.ename,e1.mgr,e2.ename FROM mydb1.emp1 e1,mydb1.emp1 e2 WHERE e1.mgr=e2.empno AND e1.empno=7369;

SELECT * FROM mydb1.emp1 WHERE sal IN (SELECT max(sal) FROM mydb1.emp1 GROUP BY deptno);

– mysql 函数

SELECT addtime(‘15:51:00’,’1:1:1’);

SELECT current_date(),current_time(),current_timestamp();

SELECT year(now()),month(now()),day(now()),DATE(now());

SELECT date_sub(now(),INTERVAL 1 YEAR);

SELECT concat(ename,job) FROM mydb1.emp1;

SELECT instr(‘bjhbnjaaahjhijk’,’aaa’);

SELECT left(‘fshiuasdjhfujdhafu’,8);

SELECT right(‘dsfasfs’,2);

SELECT replace(‘dsjaahiuhaiuhauihgauihgaiugaaahuiiaiojhaaajiaaijaikjaiaa’,’aa’,’bb’);

SELECT strcmp(‘aac’,’aac’);

SELECT conv(96537532573879,10,16);

SELECT format(12.2546467465,5);

SELECT rand(now());

create DATABASE mydb2;