1、表复制
-- 自我复制数据(蠕虫复制)
-- 有时，为了对某个sq|语句进行效率测试，我们需要海量数据时，可以使用此法为表创建
-- 海量数据。coyptab.sql
-- 演示如何自我赋值
-- 先将emp表中的部分数据复制到mytab_01
CREATE TABLE mytab_01 (id int,`name` VARCHAR(32),sal DOUBLE,job VARCHAR(32),depton INT);
DESC mytab_01
SELECT * FROM mytab_01;
-- 先将emp表中的部分数据复制到mytab_01
INSERT INTO mytab_01 (id,`name`,sal,job,depton) SELECT empno,ename,sal,job,depton FROM emp;
-- 自我复制 n*2自我复制
-- SELECT COUNT(*) FROM mytab_01; 13条
INSERT INTO mytab_01 SELECT * FROM mytab_01;
-- SELECT COUNT(*) FROM mytab_01; 26条
-- 思考题:如何删除掉一张表重复记录
-- 先准备这样一张表
-- 复制表结构
CREATE TABLE mytab_02 LIKE emp;
DESC mytab_02
INSERT INTO mytab_02 SELECT * FROM emp; --多执行几遍
-- 去重mytab_02的记录
-- 思路
-- (1) 先创建一 张临时表my_ tmp，该表的结构和my_ tab02一 样
-- (2) 把my_ tab02的记录通过distinct 关键字处理后把记录复制到my_ tmp
-- (3) 清除掉my_ tab02 记录
-- (4) 把my_ _tmp 表的记录复制到my_ tab02
-- (5) drop掉临时表my_ .tmp
CREATE TABLE my_tmp LIKE mytab_02;
INSERT INTO my_tmp SELECT DISTINCT * FROM mytab_02;
DELETE FROM mytab_02;
INSERT INTO mytab_02 SELECT * FROM my_tmp;
DROP TABLE my_tmp;
SELECT * FROM mytab_02;
2、合并查询
-- 有时在实际应用中，为了合并多个select语句的结果，可以使用集合操作符号
-- union , union all union.sql
-- 1. union all
-- 该操作符用于取得两个结果集的并集。当使用该操作符时，不会取消重复行。
SELECT ename,sal,job FROM emp WHERE sal>2500; -- 5条
SELECT ename,sal,job FROM emp WHERE job='MANAGER'; -- 3条
SELECT ename,sal,job FROM emp WHERE sal>2500 UNION ALL
SELECT ename,sal,job FROM emp WHERE job='MANAGER'; -- 8条,其中重复数据2条
-- 1. union
-- 该操作符用于取得两个结果集的并集。当使用该操作符时，自动会重复
SELECT ename,sal,job FROM emp WHERE sal>2500 UNION
SELECT ename,sal,job FROM emp WHERE job='MANAGER'; -- 6条,无重复数据
3、外连接
-- 使用我们学习过的多表查询的SQL，看看效果如何?
SELECT
dname,
ename,
job
FROM
dept,
emp
WHERE
emp.depton = dept.depton
GROUP BY
dname;
4、左连接和右连接
-- 1.左外连接(如果左侧的表完全显示我们就说是左外连接)
-- 2.右外连接(如果右侧的表完全显示我们就说是右外连接)
-- 为了讲清楚，我们举例说明。
-- 准备两张测试表
CREATE TABLE stu (id INT,`name` VARCHAR(32));
INSERT INTO stu VALUES (1,'Jack'),(2,'Tom'),(3,'Kity'),(4,'nono');
SELECT * FROM stu;
CREATE TABLE exam (id INT,grade INT);
INSERT INTO exam VALUES (1,56),(2,76),(11,8);
SELECT * FROM exam;
-- 3.使用左连接(显示所有人的成绩，如果没有成绩，也要显示该人的
-- 姓名和i0号,成绩显示为空)
-- 一般的联查，会产生笛卡尔积的问题，带条件筛选以后没有匹配到数据无法显示
SELECT stu.id,`name`,grade FROM stu,exam WHERE stu.id=exam.id;
-- 改用左外连接
SELECT stu.id,`name`,grade FROM stu LEFT JOIN exam ON stu.id=exam.id;
-- 4.右连接(显示所有成绩，如果没有名字四配，显示空)
SELECT stu.id,`name`,grade FROM stu RIGHT JOIN exam ON stu.id=exam.id;
-- 练习
-- 列出部门名称和这些部门J的员工信息(名字和工作)，同时列出那些没有员工
-- 的部门名。5min
-- 1.使用左外连接实现
SELECT
dname,
ename,
job,
dept.depton
FROM
dept
LEFT JOIN emp ON emp.depton = dept.depton;
-- 2.使用右外连接实现
SELECT
dname,
ename,
job,
dept.depton
FROM
emp
RIGHT JOIN dept ON emp.depton = dept.depton;
MySql约束
1、主键
1.1、MySQL约束
约束用于确保数据库的数据满足特定的商业规则。在mysq|中,约束包括: not null,unique,primary key,foreign key,和check五种
1.2、主键使用
-- 主键使用的细节讨论
CREATE TABLE t17 (
id
INT PRIMARY KEY,
`name` VARCHAR (32),
`email` VARCHAR (32));
-- primarykey不能重复而且不能为null。
-- 一-张表最多只能有一个主键，但可以是复合主键(比如 id+name)
CREATE TABLE t18 (
id
INT PRIMARY KEY,
-- `name` VARCHAR (32) PRIMARY KEY, 错误的写法
`email` VARCHAR (32));
-- 演示复合主键id+name
CREATE TABLE t18 (
id INT,
`name` VARCHAR (32),
`email` VARCHAR (32),
PRIMARY KEY (id,`name`) -- 这里就是复合主键
);
DESC t18;
INSERT INTO t18 VALUES (1,'张三','zhangsan@qq.com');
INSERT INTO t18 VALUES (1,'李四','李四@qq.com');
SELECT * FROM t18;
-- 主键的指定方式有两种
CREATE TABLE t19 (
id
INT ,
`name` VARCHAR (32) PRIMARY KEY,
`email` VARCHAR (32));
-- 第二种方式
CREATE TABLE t20 (
id INT,
`name` VARCHAR (32),
`email` VARCHAR (32),
PRIMARY KEY(`name`));
DESC t20;
-- INSERT INTO t18 VALUES (1,'张三','zhangsan@qq.com'); 失败
-- 直接在字段名后指定:字段名primakry key
-- 在表定义最后写primary key(列名) ;
-- 使用desc表名，可以看到primary key的情况
3、not null、unqiue
CREATE TABLE t21 (
id INT UNIQUE, -- 表示id列是不可以重复的
`name` VARCHAR (32),
`email` VARCHAR (32));
INSERT INTO t21 VALUES (1,'张三','zhangsan@qq.com');
-- INSERT INTO t21 VALUES (1,'李四','lisi@qq.com'); 报错：重复的id
-- unqiue使用细节
-- 如果没有指定 not nul1,则unique字段可以有多个null
-- 如果一个列（字段）是unique not null使用效果类似主键
INSERT INTO t21 VALUES (NULL,'张三','zhangsan@qq.com');
INSERT INTO t21 VALUES (NULL,'李四','lisi@qq.com');
SELECT *FROM t21;
-- 一张表可以有多个unique
CREATE TABLE t22 (
id INT UNIQUE, -- 表示id列是不可以重复的
`name` VARCHAR (32) UNIQUE,
`email` VARCHAR (32));
3、外键
-- 外键演示
-- 先创建主表my_class
CREATE TABLE my_class ( id INT PRIMARY KEY, class_name VARCHAR ( 32 ) NOT NULL DEFAULT '' );
-- 再创建从表
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,'WEB');
INSERT INTO my_class VALUES (300,'PHP');
SELECT * FROM my_class;
INSERT INTO my_stu VALUES (1,'tom',100);
INSERT INTO my_stu VALUES (2,'jack',200);
INSERT INTO my_stu VALUES (3,'hsp',300);
SELECT * FROM my_stu;
DESC my_stu;
-- INSERT INTO my_stu VALUES (4,'tim',400); 失败
INSERT INTO my_stu VALUES (5,'king',null); -- 可以，因为外键没有写not null
SELECT * FROM my_stu;
-- 一旦建立外主外键的关系，数据就不能随意删除了
-- DELETE FROM my_class where id=100; 外键约束失败
4、check
-- 演示check的使用
-- mysq15.7目前还不支持check , 只做语法校验，但不会生效
-- 了解
-- 学习oracle、sql server,这两个数据库是真的生效
-- 生效
CREATE TABLE t23 (id INT PRIMARY KEY,`name` VARCHAR(32),sex VARCHAR(6) CHECK(sex IN('man','woman')),sal DOUBLE CHECK (sal>1000 AND sal<2000));
-- 添加数据
INSERT INTO t23 VALUES(1,'jack','mind',1);
SELECT * from t23;
5、建表练习
-- 商店销售系统库表设计
-- ●商店售货系统表设计案例[先练,再评10min]
-- 现有一个商店的数据库shop_ _db, 记录客户及其购物情况，由三个表组成:
-- 1建表，在定义中要求声明[进行合理设计]:
-- (1)每个表的主外键;
-- (2)客户的姓名不能为空值;
-- (3)电邮不能够重复;
-- (4)客户的性别[男|女] check枚举..
-- (5)单价unitprice在1.0 - 9999.99之间check
CREATE DATABASE shop_db CHARACTER SET utf8 COLLATE utf8_bin;
-- 商品goods (商品号goods_ id,商品名goods_ name, 单价unitprice,商品类别
-- category,供应商provider);
CREATE TABLE goods (
goods_id INT PRIMARY KEY,
goods_name VARCHAR ( 64 ) NOT NULL DEFAULT '',
unitprice DECIMAL ( 10,2 ) NOT NULL DEFAULT 0 CHECK (
unitprice IN ( unitprice > 1.0 AND unitprice < 9999.99 )),
category INT NOT NULL DEFAULT 0,
provider VARCHAR ( 64 )NOT NULL DEFAULT '');
-- 客户customer (客户号customer _id,姓 名name,住址address,电邮email性别sex,身
-- 份证card_id);
CREATE TABLE customer (
customer_id CHAR(8) PRIMARY KEY,
`name` VARCHAR ( 64 ) NOT NULL DEFAULT '',
address VARCHAR ( 64 ) NOT NULL DEFAULT '',
email VARCHAR ( 64 ) UNIQUE NOT NULL,
sex ENUM ( '男', '女' ) NOT NULL,
card_id CHAR(18)
-- FOREIGN KEY (customer _id) REFERENCES googs(good_id)
);
-- 购买purchase (购买订单号order_ id,客户号customer_id,商品号goods_ id,购买数
-- 量nums);
CREATE TABLE purchase (
order_id INT UNSIGNED PRIMARY KEY,
customer_id CHAR ( 8 ) NOT NULL DEFAULT '',
goods_id INT NOT NULL DEFAULT 0,
nums INT NOT NULL DEFAULT 0,
FOREIGN KEY ( customer_id ) REFERENCES customer ( customer_id ),
FOREIGN KEY ( goods_id ) REFERENCES goods ( goods_id ));
版权声明：本文为YYBDESHIJIE原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。