蠕虫复制
为了对sql语句进行测试,需要海量数据时可以使用蠕虫复制为表创建海量数据。
测试代码
# 表的复制
CREATE TABLE my_table(
id INT UNSIGNED NOT NULL DEFAULT 0,
`name` VARCHAR(32) NOT NULL DEFAULT ' ',
salary DECIMAL(15,4) UNSIGNED NOT NULL DEFAULT 0,
job VARCHAR(64) NOT NULL DEFAULT ' ',
deptno INT UNSIGNED NOT NULL);
DESC my_table;
-- 表的自我复制
-- 1.把emp表复制到my_table 表中
INSERT INTO my_table
(id, `name`,salary, job, deptno)
SELECT empno, ename, sal, job, deptno FROM emp;
SELECT * FROM my_table;
-- 2.自我复制
INSERT INTO my_table
SELECT * FROM my_table;
SELECT * FROM my_table;
SELECT COUNT(*) FROM my_table;
表中数据去重
# 表的复制
CREATE TABLE my_table(
id INT UNSIGNED NOT NULL DEFAULT 0,
`name` VARCHAR(32) NOT NULL DEFAULT ' ',
salary DECIMAL(15,4) UNSIGNED NOT NULL DEFAULT 0,
job VARCHAR(64) NOT NULL DEFAULT ' ',
deptno INT UNSIGNED NOT NULL);
DESC my_table;
-- 表的自我复制
-- 1.把emp表复制到my_table 表中
INSERT INTO my_table
(id, `name`,salary, job, deptno)
SELECT empno, ename, sal, job, deptno FROM emp;
SELECT * FROM my_table;
-- 2.自我复制
INSERT INTO my_table
SELECT * FROM my_table;
SELECT * FROM my_table;
SELECT COUNT(*) FROM my_table;
-- 表中数据去重
CREATE TABLE my_table01 LIKE emp;
DESC my_table01;
INSERT INTO my_table01
SELECT * FROM emp;
SELECT * FROM my_table01;
-- 模拟去重步骤
-- 1.创建新表,与原表结构相同
CREATE TABLE my_table02 LIKE my_table01;
-- 2.把原表的数据通过distinct关键字处理后把记录复制到新表中
INSERT INTO my_table02
SELECT DISTINCT * FROM my_table01;
-- 3.清楚原表中的数据
DELETE FROM my_table01;
-- 4.把新表中的数据复制到原表中
INSERT INTO my_table01
SELECT * FROM my_table02;
-- 5.drop掉临时的新表
DROP TABLE my_table02;
SELECT * FROM my_table01;
合并查询
1)union all操作符用于取得两个结果集的并集,使用该操作符不会取消重复行;
2)union操作符用于取得两个结果集的并集,使用该操作符会取消重复行。
# 合并查询
SELECT ename, sal, job FROM emp
WHERE sal > 2000
SELECT ename, sal, job FROM emp
WHERE job = 'MANAGER';
-- union all 将两个查询结果合并(不会去重)
SELECT ename, sal, job FROM emp WHERE sal > 2000
UNION ALL
SELECT ename, sal, job FROM emp WHERE job = 'MANAGER';
-- union 将两个查询结果合并(去重)。
SELECT ename, sal, job FROM emp WHERE sal > 2000
UNION
SELECT ename, sal, job FROM emp WHERE job = 'MANAGER';
外连接
1)左外连接(左侧的表完全显示称为左外连接)
SELECT ... FROM 左表名 LEFT JOIN 右表名
ON 条件
2)右外连接(右侧的表完全显示称为右外连接)
SELECT ... FROM 左表名 RIGHT JOIN 右表名
ON 条件
# 外连接
-- 查询出部门名称和部门员工名称和工作,同时查询出没有员工的部门
SELECT dname, ename, job FROM dept, emp
WHERE dept.`deptno` = emp.`deptno`
ORDER BY dname;
#创建学生表和学生成绩表
CREATE TABLE stu(
id INT,
`name` VARCHAR(32));
CREATE TABLE exam(
id INT,
grade DECIMAL(4,2));
INSERT INTO stu VALUES(1,'jack'), (2,'tom'), (3,'kity'), (4, 'nono');
INSERT INTO exam VALUES (1,56), (1,76), (11,8);
SELECT * FROM stu;
SELECT * FROM exam;
-- 使用左连接查询所有人的成绩,如果没有成绩也要显示该学生的姓名和id
-- 子查询
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`;
-- 右外连接(显示所有成绩,如果没有姓名则显示空)
SELECT exam.`id`, `name`, grade FROM stu RIGHT JOIN exam -- stu.id 无法显示 exam表中的独有的id
ON stu.`id` = exam.`id`;
-- 列出部门名称和这些部门员工信息(名字和工作),同时列出那些没有员工的部门名称
-- 右连接
SELECT dname, ename, job FROM emp RIGHT JOIN dept
ON emp.`deptno` = dept.`deptno`;
-- 左连接
SELECT dname, ename, job FROM dept LEFT JOIN emp
ON emp.`deptno` = dept.`deptno`;
MySQL约束
基本介绍
约束用于确保数据库数据满足特定的商业规则。在mysql中,约束包括:not null , unique , primary key , foreign key , check 五种。
primary key(主键约束)
用于唯一的标识表行的数据,当定义主键约束后该列不能重复
字段名 字段类型 primary key
细节说明
1)primary key不能重复而且不能为null;
2)一张表最多只能有一个主键,但可以是复合主键;
3)主键的指定方式有两种:
①直接在字段名后指定:字段名 primary key
②在表定义最后写 primary key(列名);
4)查看表结构(desc 表名),可以看到primary key 的情况;
5)在实际开发中每个表往往都有主键。
# 主键使用
CREATE TABLE people(
id INT PRIMARY KEY, -- 表示id为主键,主键是不能重复的
`name` VARCHAR(32),
email VARCHAR(64));
DESC people;
-- 向表中添加数据
INSERT INTO people VALUES
(1,'tom','tom@outlook.com');
SELECT * FROM people;
INSERT INTO people VALUES
(2,'jack','jack@outlook.com');
SELECT * FROM people;
-- INSERT INTO people VALUES
-- (1,'pero','pero@outlook.com'); -- 报错:Duplicate entry '1' for key 'PRIMARY'
-- INSERT INTO people VALUES
-- (null,'pero','pero@outlook.com'); -- 报错:Column 'id' cannot be null
-- 定义为主键的列数据不可以重复
-- CREATE TABLE num(
-- id INT PRIMARY KEY, -- 表示id为主键,主键是不能重复的
-- -- 报错:Multiple primary key defined(一张表最多只能有一个主键)
-- `name` VARCHAR(32) primary key,
-- email VARCHAR(64));
-- 复合主键
CREATE TABLE peo(
id INT,
`name` VARCHAR(32),
email VARCHAR(64),
PRIMARY KEY(id, `name`)); -- 在表定义后指定主键
DESC peo; -- 可以显示约束情况
INSERT INTO peo VALUES
(1,'tom','tom@outlook.com');
SELECT * FROM peo;
INSERT INTO peo VALUES
(1,'jack','jack@outlook.com');
SELECT * FROM peo;
-- 报错:Duplicate entry '1-tom' for key 'PRIMARY'(当id与name都相同时,违反了主键原则)
-- INSERT INTO peo VALUES
-- (1,'tom','tomplus@outlook.com');
-- SELECT * FROM peo;
not null(非空约束)
如果在列上定义了not null,那么当初如数据时必须为列提供数据
字段名 字段类型 not null
unique(唯一约束)
当定义了唯一的约束后,改列值是不能重复的
字段名 字段类型 unique
细节说明
1)如果没有指定not null,则unique字段可以有多个null
2)一张表可以有多个unique字段
# unique的使用
CREATE TABLE t5(
id INT UNIQUE, -- 表示id列是不可以重复的
`name` VARCHAR(32),
email VARCHAR(64));
SELECT * FROM t5;
INSERT INTO t5 VALUES (1,'tom','tom@outlook.com');
-- 报错:Duplicate entry '1' for key 'id' ,违反unique约束
INSERT INTO t5 VALUES (1,'jack','jack@outlook.com');
-- 如果没有指定not null,则unique字段可以有多个null
INSERT INTO t5 VALUES (NULL,'lucy','lucy@outlook.com');
INSERT INTO t5 VALUES (NULL,'smith','smith@outlook.com');
INSERT INTO t5 VALUES (NULL,'jery','jery@outlook.com');
SELECT * FROM t5;
-- 如果一个列是unique not null 使用效果类似 primary key
-- 一张表可以有多个unique字段
CREATE TABLE t6(
id INT UNIQUE,
`name` VARCHAR(32) UNIQUE,
email VARCHAR(64) UNIQUE);
DESC t6;
foreign key(外键约束)
用于定义主表和从表之间的关系:外键约束要定义在从表上,主表则必须具有主键约束或是unique约束,当定义外键约束后,要求外键列数据必须在主表的主键列存在或是为null。
FOREIGN KEY (本表字段名) REFERENCES 主表名 (主键名或unique字段名)
细节说明
1)外键指向的表的字段,要求是primary key或者是unique;
2)表的类型是innodb,这样的表才支持外键;
3)外键字段的类型要和主键字段的类型一致(长度可以不同);
4)外键字段的值必须在主键字段中出现过,或者为null(前提是外键字段允许为null);
5)一旦建立主外键的关系,数据不能随意删除了。
# 外键约束
-- 1.先创建主表 class表
CREATE TABLE class(
class_id INT PRIMARY KEY,
class_name VARCHAR(32) NOT NULL DEFAULT ' ');
CREATE TABLE students(
stu_id INT PRIMARY KEY,
stu_name VARCHAR(32) NOT NULL DEFAULT ' ',
class_id INT,
-- 指定外键关系
FOREIGN KEY (class_id) REFERENCES class(class_id));
INSERT INTO class VALUES (100,'java'),(200,'web');
SELECT * FROM class;
INSERT INTO students VALUES (1,'tom',100), (2,'jack',200);
-- 报错:Cannot add or update a child row: a foreign key constraint fails
-- (`pero_db03`.`students`, CONSTRAINT `students_ibfk_1` FOREIGN KEY (`class_id`)
-- REFERENCES `class` (`class_id`))
-- INSERT INTO students VALUES (3,'lucy',300);
INSERT INTO students VALUES (4,'smith',NULL); -- 外键没有写not null ,该行创建成功
SELECT * FROM students;
-- 一旦建立主外键的关系,数据不能随意删除了。
-- 如果外键没有数据指向主键中的数据,那么主键中的该数据就可以删除
-- 错误代码: 1451
-- Cannot delete or update a parent row:
-- a foreign key constraint fails (`pero_db03`.`students`,
-- CONSTRAINT `students_ibfk_1` FOREIGN KEY (`class_id`)
-- REFERENCES `class` (`class_id`))
-- delete from class
-- where class_id = 100;
check(检查约束)
用于强制行数据必须满足的条件,例如定义了check约束条件,如果数据不在列值的要求范围内就会提示错误;oracle和sql server均支持check,但是mysql5.7不支持check,只做语法校验但不会生效。
列名 类型 check(条件)
# check约束
CREATE TABLE t7(
id INT PRIMARY KEY,
`name` VARCHAR(32),
sex CHAR(6) CHECK (sex IN('man','woman')), -- 使用枚举:sex enum('男', '女') not null
sal DOUBLE CHECK (sal >1000 AND sal <2000));
DESC t7;
INSERT INTO t7 VALUES (1,'tom','people',5000);
SELECT * FROM t7;
练习代码
-- 数据库shop_db,记录客户及其购物情况,有三个表;
CREATE DATABASE shop_db; -- 创建数据库
#创建表【进行合理设计】
-- 要求:1.每个表的主外键;2.客户姓名不能是null值;3.电邮不能重复;
-- 4.客户的性别【男|女】5.单价unitprice在1.0-9999.99之间check。
-- 商品goods(商品号goods_id,商品名goods_name,单价unitprice,商品类别category,供应商peovider)
CREATE TABLE goods(
goods_id INT,
goods_name VARCHAR(32),
unitprice DECIMAL(6,2) CHECK(1.0 < unitprice AND unitprice < 9999.99),
category VARCHAR(32),
peovider VARCHAR(32),
PRIMARY KEY(goods_id, goods_name));
-- 客户customer(客户号customer_id,姓名name,住址address,电邮email,性别sex,身份证号card_id)
CREATE TABLE customer(
customer_id INT,
`name` VARCHAR(32),
address VARCHAR(32),
email VARCHAR(32) UNIQUE,
sex VARCHAR(6) CHECK(sex IN('man', 'woman')), -- 使用枚举:sex enum('男', '女') not null
card_id CHAR(18) UNIQUE,
PRIMARY KEY(customer_id, `name`));
-- 购买purchase(购买订单号order_id,客户号customer_id,商品号goods_id,购买数量nums)
CREATE TABLE purchase(
order_id INT,
customer_id INT,
goods_id INT,
nums INT,
FOREIGN KEY (customer_id) REFERENCES customer(customer_id),
FOREIGN KEY (goods_id) REFERENCES goods(goods_id));
DESC goods;
DESC customer;
DESC purchase;
自增长
在添加数据时表中的某列数据实现自行增长。
字段名 整型 primary key auto_increment
细节说明
1)一般来说自增长是和primary key配合使用的;
2)自增长也可以单独使用(但是需要配合unique);
3)自增长修饰的字段为整数型的(虽然小数也可以但是很少使用);
4)自增长默认从1开始,也可以通过命令alter table 表名 auto_increment = new_start_num;
5)如果添加数据时,给自增长字段指定的值,则以指定的值来存储数据。
CREATE TABLE t1(
id INT PRIMARY KEY AUTO_INCREMENT, -- 自增长
`name` VARCHAR(32) NOT NULL DEFAULT ' ',
email VARCHAR(32) NOT NULL DEFAULT ' ');
DESC t1;
-- 自增长添加数据
INSERT INTO t1 VALUES(NULL,'tom','tom@outlook.com'); -- id指定null,但是存储为1
INSERT INTO t1 VALUES(NULL,'jack','jack@outlook.com'); -- id指定null,但是存储为2
INSERT INTO t1 (`name`, email) VALUES('lucy','lucy@outlook.com'); -- id没有指定,存储为3
SELECT * FROM t1;
-- 修改默认的自增长值
CREATE TABLE t2(
id INT PRIMARY KEY AUTO_INCREMENT, -- 自增长
`name` VARCHAR(32) NOT NULL DEFAULT ' ',
email VARCHAR(32) NOT NULL DEFAULT ' ');
ALTER TABLE t2 AUTO_INCREMENT = 100; -- 修改默认的自增长值为100
INSERT INTO t2 VALUES(NULL,'tom','tom@outlook.com');
SELECT * FROM t2;
INSERT INTO t2 VALUES(108, 'jack', 'jack@outlook.com');
SELECT * FROM t2;
-- 之后的值,从最近一次添加的id数值之上再自增长
INSERT INTO t2 VALUES(NULL, 'jack', 'jack@outlook.com'); -- id增长为109
SELECT * FROM t2;