数据库操作语句4

#表复制
CREATE TABLE tab01(
	id INT,
	`name` VARCHAR(32),
	sal DOUBLE,
	job VARCHAR(32),
	deptno INT)
#显示表的结构
DESC tab01
SELECT *FROM tab01

#把emp表的记录复制到tab01
INSERT INTO tab01(id,`name`,sal,job,deptno)
		SELECT empno,ename,sal,job,deptno FROM emp

#自我复制
INSERT INTO tab01 SELECT *FROM tab01


#删除一张表的重复记录

#创建一张重复表
CREATE TABLE tab02 LIKE emp
INSERT INTO tab02 SELECT *FROM emp

#1.创建一张临时表，和tab02结构相同
CREATE TABLE temp LIKE tab02
#2.用distinct去重并自我复制,这里其实就已经去重完了，可以把原表删了，把temp改个名
INSERT INTO temp SELECT DISTINCT *FROM tab02
#3.清除tab02的记录
DELETE FROM tab02
#4.把temp表中的数据复制到tab02
INSERT INTO tab02 SELECT *FROM temp
#5.删除temp表
DROP TABLE temp

#合并查询  UNION ALL将两个查询结果合并显示但不会去重,UNION会去重
SELECT ename,sal,job FROM emp WHERE sal>2500
UNION
SELECT ename,sal,job FROM emp WHERE job='MANAGER'

#外连接

#列出部门名称和这些部门的员工名称和工作，同时显示没有员工的部门

#使用左连接实现:将dept表和emp表的数据进行匹配，即便emp中没有依然会显示dept表的数据
SELECT dname,ename,job 
	FROM dept LEFT JOIN emp
	ON dept.deptno=emp.`deptno`
	
#使用右连接实现:将emp表和dept表的数据进行匹配，即便左中没有依然会显示右表的数据
SELECT dname,ename,job 
	FROM emp RIGHT JOIN dept
	ON dept.deptno=emp.`deptno`
	
#主键的使用,主键列的值不能重复且不为空
#一张表只能有一个主键，但可以有复合主键
#主键的定义方式:可以在列的后面定义PRIMARY KEY，也可以在表的最后一行定义PRIMARY KEY()
#使用DESC可以查看表的主键情况
CREATE TABLE tab03(
	id INT ,
	`name` VARCHAR(32),
	#复合主键,id和name不能同时相同
	PRIMARY KEY(id,`name`)
	)
INSERT INTO tab03 VALUES(01,'rick')
INSERT INTO tab03 VALUES(01,'morty')
SELECT *FROM tab03

#一张表中可以有多个unique，unique限制该列唯一，但如果没有设置not null的话会有多个值

#主外键
#主表 my_class
CREATE TABLE my_class(
	id INT PRIMARY KEY,
	`name` VARCHAR(32) NOT NULL DEFAULT '')

#从表my_stu
CREATE TABLE my_stu(
	id INT PRIMARY KEY,
	`name` VARCHAR(32) NOT NULL DEFAULT '',
	class_id INT,
	#指定外键关系
	FOREIGN KEY (class_id)REFERENCES my_class(id))
INSERT INTO my_class VALUES(100,'java'),(200,'java')
SELECT *FROM my_class
INSERT INTO my_stu VALUES(01,'rick',100),(02,'morty',200)
#因为主键的原因没有300的班级所以加入不进去
INSERT INTO my_stu VALUES(03,'rick',300)
SELECT *FROM my_stu


#表的类型是innodb, 这样的表才支持外键
#外键字段的类型要和主键字段的类型一致(长度可以不同)
#外键字段的值,必须在主键字段中出现过，或者为null前提是外键字段允许为null
#一旦建立主外键的关系，数据不能随意删除了

#check只是起到语法检测的作用，并不会阻止数据的加入
CREATE TABLE tab04(
	sex VARCHAR(32) CHECK (sex IN('man','woman')),
	sal DOUBLE CHECK (sal>1000 AND sal<2000))
INSERT INTO tab04 VALUES('mid',5000)
SELECT *FROM tab04

CREATE DATABASE shop_db

CREATE TABLE goods(
	goods_id INT PRIMARY KEY,
	goods_name VARCHAR(32),
	unitprice DOUBLE CHECK(unitprice>1.0 AND unitprice<9999.99),
	categroy VARCHAR(32),
	provider VARCHAR(32))


CREATE TABLE customer(
	customer_id INT PRIMARY KEY,
	customer_name VARCHAR(32) NOT NULL DEFAULT "",
	address VARCHAR(32),
	email VARCHAR(32) UNIQUE,
	sex VARCHAR(32)CHECK(sex IN('man','woman')),
	card_id INT)
	

CREATE TABLE purchase(
	order_id INT,
	customer_id INT NOT NULL DEFAULT 0,
	goods_id INT NOT NULL DEFAULT 0,
	nums INT,
	FOREIGN KEY (customer_id) REFERENCES customer(customer_id),
	FOREIGN KEY (goods_id) REFERENCES goods(goods_id)
	)
	
#自增长，一般和主键配合使用,类型为整形,也可以单独使用(配合unique)
CREATE TABLE tab05(
	id INT PRIMARY KEY AUTO_INCREMENT,
	`name` VARCHAR(32) NOT NULL DEFAULT "")
SELECT *FROM tab05
#这里如果id为空则自增长从1开始每次+1
INSERT INTO tab05 VALUES(NULL,'rick')
#不指定增长值默认为null
INSERT INTO tab05 (`name`) VALUES('morty')
#在未向表中添加数据时，可以修改自增长的初始值
ALTER TABLE tab05 AUTO_INCREMENT=10
#如果给自增长的列添加指定值后，自增长的起始值就会从指定值开始


#索引，用于优化搜索，但会增加源文件的大小，也会影响对表的修改
#没设置索引之所以慢是因为，每次查找都是全表扫描
#设置索引之后就会形成一个索引的数据结构例如：二叉树，会大大减少查找时间

DROP TABLE tab05
CREATE TABLE tab05(
	id INT,
	`name` VARCHAR(32),
	sal DOUBLE
	)

#创建普通索引
CREATE INDEX name_index ON tab05(`name`)
#创建唯一索引,当该列确定不重复的时候使用
CREATE UNIQUE INDEX sal ON tab05(id)
#显示表是否有索引
SHOW INDEX FROM tab05
SHOW KEYS FROM tab05
SHOW INDEXES FROM tab05
DESC tab05
#修改索引:先删除在添加
#添加主键索引
ALTER TABLE tab05 ADD PRIMARY KEY (id)
#删除索引
DROP INDEX sal ON tab05
#删除主键索引
ALTER TABLE tab05 DROP PRIMARY KEY
SELECT *FROM tab05


#事务
#开始事务
START TRANSACTION
#设置保存点
SAVEPOINT a
#执行dml操作
INSERT INTO tab05 VALUES(1,'rick',100);

SAVEPOINT b

INSERT INTO tab05 VALUES(2,'morty',200);
#回退到b
ROLLBACK TO b
#直接回退到事务开始的状态
ROLLBACK
#提交事务，所有的保存点不再生效
COMMIT
#查看当前会话的隔离级别
SELECT @@tx_isolation
#查看当前系统的隔离级别
SELECT @@global.tx_isolation
#更改会话隔离级别为READ UNCOMMITTED
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
#更改系统的隔离级别,默认为 repeatable read
SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

#修改存储引擎
ALTER TABLE tab05 ENGINE=INNODB

外连接outer join：附表中值可能存在null的情况

内连接inner join：两个表值都存在

①A inner join B：取交集

②A left join B：取A全部，B没有对应的值，则为null

③A right join B：取B全部，A没有对应的值，则为null

④A full outer join B：取并集，彼此没有对应的值为null

上述4种的对应条件，在on后填写。