DDL操作数据库
-- 创建数据库 三种方式
CREATE DATABASE db1;
CREATE DATABASE IF NOT EXIST db1;
CREATE DATABASE db3 default character set gbk;
-- 查看数据库
SHOW DATABASES; -- 查看所有的数据库
-- 修改数据库
-- 修改数据库的字符集为utf-8;
ALTER DATABASE db3 CHARACTER SET utf8;
-- 删除数据库
DROP DATABASE db1;
-- 查看当前操作的数据库
SELECT DATABASE();
-- 切换数据库
USE test; -- use 数据库名
与表相关的
-- 创建表
CREATE TABLE student( id INT, NAME VARCHAR(50), birthday DATE );
-- 查看某数据库的所有表
SHOW TABLES;
-- 查看表结构
DESC student; -- desc 表名
-- 查看创建表的sql语句
SHOW CREATE TABLE student; -- show create table 表名。
-- 快速创建表结构相同的表
CREATE TABLE s1 LIKE student; -- create table 表2 like 表1;
-- 修改表结构
alter table 表名 add 字段名 字段类型(长度); -- 添加字段
alter table 表名 drop column 字段名;-- 删除字段
alter table 表名 change 现有字段名称 修改后字段名称 数据类型; -- 修改字段名称+类型
alter table 表名 modify column 字段名 字段类型(长度); -- 修改某字段的类型
-- 查看表的结构
DESC s1; -- DESC 表名。
-- 删除表
DROP TABLE S1;
DROP TABLE s1 IF EXISTS s1;
数据类型
类型 | 描述 |
int | 整型 |
double | 浮点型 |
varchar | 字符串型 |
date | 日期类型,格式为yyyy-MM-dd。(没有时分秒) |
DML操作表中的数据
-- 增删改的操作
INSERT INTO student(id,NAME,birthday) VALUES (1,'李四','2004-03-21');
-- insert into 表名(字段名)values(对应字段值);
-- 插入表的数据包含全部字段的话,可以省去前面的
INSERT INTO student VALUES(2,'张三','2005-05-16');
-- 蠕虫复制 两张表的结构一样
CREATE TABLE s1 LIKE student;
INSERT INTO s1 SELECT * FROM student;
INSERT INTO s1(id,NAME) SELECT id,NAME FROM student;
-- 更新表中某条数据
UPDATE student set birthday='2004-02-04';
-- update 表名 set 字段名=值 where 条件。
UPDATE student SET birthday='2008-05-09' WHERE id='1';
--删除表的数据
-- 删除student 表中id为1的记录
DELETE FROM student WHERE id=1;
-- 删除所有的表记录
DELETE FROM student; --这种情况下,自增字段在原先的基础上改变。
--将表删除再重新创建了一张结构相同的表
TRUNCATE TABLE s1; -- 解决自增字段的值在原先继续的问题。
DQL查询表中的数据
CREATE TABLE student3(
id INT, -- 编号
NAME VARCHAR(20), -- 姓名
age INT, sex VARCHAR(5), -- 年龄
address VARCHAR(100), -- 地址
math INT, -- 数学
english INT -- 英语成绩
);
INSERT INTO student3 VALUES (1,'马云',55,'男','杭州',66,78),
(2,'马化腾',45,'女','深圳',98,87),
(3,'马景涛',55,'男','香港',56,77),
(4,'柳岩',20,'女','湖南',76,65),
(5,'柳青',20,'男','湖南',86,NULL),
(6,'刘德华',57,'男','香港',99,99),
(7,'马德',22,'女','香港',99,99),
(8,'德玛西亚',18,'男','南京',56,65);
INSERT INTO student3(id,NAME,age,sex,address,math,english)VALUES
(9,'唐僧',25,'男','长安',87,78),
(10,'孙悟空',18,'男','高老庄',100,66),
(11,'猪八戒',22,'男','花果山',58,78),
(12,'沙僧',50,'男','流沙河',77,88),
(13,'白骨精',22,'女','白虎岭',66,66),
(14,'蜘蛛精',23,'女','盘丝洞',88,88);
-- 普通的查查看
-- 查询表的所有数据
SELECT * FROM student3;
-- 查询name和age
SELECT NAME,age FROM student3;
-- 查询并且给字段别名
SELECT NAME AS 姓名,age AS 年龄 FROM student3 AS stu;
-- 查询student3表中地址
SELECT address AS 地址 FROM student3;
-- 查询并去掉重复值
SELECT DISTINCT address FROM student3;
-- 查询数字成绩并且加5分
SELECT math+5 FROM student3;
-- 查询数学和英语成绩的总分
SELECT *,(math+english)AS 总分 FROM student3;
条件语句的语法
select 字段名 from 表名 where 条件
运算符
运算符 | 说明 |
>、<、<=、>=、=、<> | 不等于:<> 在MySQL中也可以用!= |
BETWEEN...AND | 在一个范围内,是个开区间(包含临界) |
IN(集合) | 集合表示多个值,用逗号分隔 |
LIKE | 模糊查询 |
IS NULL | 查询某一列的null值 |
AND、&& | 与 |
or、|| | 或 |
not 、! | 非 |
like模糊查询
like | like ‘通配符字符串’ |
% | 匹配任意多个字符串 |
_ | 匹配一个字符 |
-- 条件查询的情况
-- 查询语句的练习
-- 查询math分数大于80分的学生
SELECT * FROM student3 WHERE math>80;
-- 查询english 分数小于或者等于80分的学生
SELECT * FROM student3 WHERE english<=80;
-- 查询age等于20岁的学生
SELECT * FROM student3 WHERE age=20;
-- 查询age不等于20岁的学生,(2种方法)
SELECT * FROM student3 WHERE age<>20;
SELECT * FROM student3 WHERE age!=20;
SELECT * FROM student3 WHERE age<20 || age >20;
-- 查询age大于35 性别为男
SELECT * FROM student3 WHERE age>35 AND sex='男';
-- 查询age大于35 或者性别为男
SELECT * FROM student3 WHERE age>35 OR sex='男';
-- 查询id为1 3 5的学生 (or或者in)
SELECT * FROM student3 WHERE id = 1 OR id = 3 OR id=5;
SELECT * FROM student3 WHERE id IN(1,3,5);
-- 查询id不是为1 3 5的学生 (or或者in)
SELECT * FROM student3 WHERE id NOT IN(1,3,5);
-- 查询english成绩大于等于75,且小于等于90的学生
SELECT * FROM student3 WHERE english>=75 AND english<=90;
SELECT * FROM student3 WHERE english BETWEEN 75 AND 90;
-- 查询姓马的学生
SELECT * FROM student3 WHERE NAME '马%';
-- 查询姓名中包含德字的学生
SELECT * FROM student3 WHERE NAME LIKE '%德%';
-- 查询姓马,且姓名有两个字的学生
SELECT * FROM student3 WHERE NAME LIKE '马_';
-- 含有排序性质的查询
-- 单列排序:按照一个字段进行排序
-- 查询所有数据,按照年龄降序排序
SELECT * FROM student3 ORDER BY age DESC
-- desc 降序;asc 升序
SELECT * FROM student3 ORDER BY age ASC
-- 组合排序
-- 同时对多个字段排序,如果字段1的情况相同,则按第二个字段排序,以此类推。
-- 查找所有的数据,按照年龄的降序排序上
-- 年龄相同,再以数学成绩升序排序
SELECT * FROM student3 ORDER BY age DESC,math ASC;
-- 聚合函数 (常用的5种)
-- 查询学生总数
SELECT COUNT(id) FROM student3;
SELECT COUNT(*) FROM student3;
SELECT COUNT(*)AS 总人数 FROM student3;
-- 查询id字段,如果为null,则用0代替
SELECT IFNULL(id,0)FROM student3;
SELECT IFNULL(english,0)FROM student3;
-- count的时候会忽略掉为null的部分,可以通过ifnull来修正
SELECT COUNT(english)FROM student3;
SELECT COUNT(IFNULL(english,0))FROM student3;
-- 查询年龄大于20的总数
SELECT COUNT(*)FROM student3 WHERE age>20;
-- 查询数学成绩的总分
SELECT SUM(math) FROM student3;
-- 查询数学成绩平均分
SELECT AVG(math) FROM student3;
-- 查询数学数学成绩的最大分
SELECT MAX(math) FROM student3;
-- 查询数学成绩最低分
SELECT MIN(math) FROM student3;
-- 分组查询
-- 关键字 group by
SELECT * FROM student3 ORDER BY sex; SELECT * FROM student3 GROUP BY sex; -- 得到的每组的第一人。没啥用。
-- 分组查询通常和聚合函数一起用。
SELECT sex, AVG(math) AS 平均分 FROM student3 GROUP BY sex;
-- 查询年龄大于25岁的人,按照性别分组,统计每组的人数。
SELECT sex AS性别, COUNT(*) FROM student3 WHERE age>25 GROUP BY sex;
-- 查询年龄大于25岁的人,按照性别分组,统计每组的人数,并且只显示性别人数大于2的记录 -- 这里的顺序,分析条件的过滤时机。
SELECT sex AS性别,COUNT(*) FROM student3 WHERE age>25 GROUP BY sex HAVING COUNT(*)>2;
-- limit查询
-- 可以用来限制显示人数
-- 查询学生表的数组,从第三条开始,显示6条
-- 分页的情况下使用
-- 从2开始(不包含2),显示6条
select * from student3 limit 2,6;
约束
主键、唯一、非空、外键、检查约束(MySQL不支持check)
约束的作用:
对表中的数据进行限制,保证数据的正确性、有效性和完整性。一个表如果添加了约束,不正确 的数据将无法插入到表中。约束在创建表的时候比较适合添加。
一般来说,主键不选择业务信息相关的字段。主键是用来唯一标识数据库中的每一条记录。
主键可以用自增 AUTO_INCREMENT
主键 | primary key |
唯一 | unique |
非空 | not null |
外键 | foreign key |
检查约束 | check(MySQL不支持) |
外键约束
-- 部门表
CREATE TABLE department(
id INT PRIMARY KEY AUTO_INCREMENT,
dep_name VARCHAR(30),
dep_location VARCHAR(30)
);
-- 员工表
CREATE TABLE employee(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(30),
age INT,
dep_id INT -- 外键对应表的主键
);
INSERT INTO department VALUES (NULL,'研发部','广州'), (NULL,'销售部','深圳');
SELECT * FROM department;
INSERT INTO employee(NAME,age,dep_id) VALUES
('张三',20,1),
('李四',21,1),
('王五',20,1),
('老王',20,2),
('大王',22,2),
('小王',18,2);
CREATE TABLE employee(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(30),
age INT,
dep_id INT ,
CONSTRAINT emp_depid_fk FOREIGN KEY(dep_id) REFERENCES department(id) );
INSERT INTO employee(NAME,age,dep_id) VALUES
('张三',20,1),
('李四',21,1),
('王五',20,1),
('老王',20,2),
('大王',22,2),
('小王',18,2);
SELECT * FROM employee;
-- INSERT INTO employee(NAME,age,dep_id) VALUES('小红',35,6);
-- 删除外键约束
ALTER TABLE employee DROP FOREIGN KEY emp_depid_fk;
-- 添加外键约束(需要先把不符合的记录删除)
ALTER TABLE employee ADD CONSTRAINT emp_depid_fk FOREIGN KEY(dep_id) REFERENCES department(id);
-- 一般来说,企业在项目中并不添加物理的外键约束
级联更新和级联删除
CREATE TABLE employee(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(50), age INT, dep_id INT,
CONSTRAINT emp_depid_fk FOREIGN KEY (dep_id) REFERENCES department(id)
ON UPDATE CASCADE ON DELETE CASCADE
);
INSERT INTO employee(NAME,age,dep_id) VALUES
('张三',20,1),
('李四',21,1),
('王五',20,1),
('老王',20,2),
('大王',22,2),
('小王',18,2);
SELECT * FROM employee;
SELECT * FROM department;
UPDATE department SET id = 10 WHERE id= 1;
DELETE FROM department WHERE id = 10;
多表查询
-- 多表查询
CREATE TABLE dept(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)
);
INSERT INTO dept(NAME) VALUES
('开发部'),('市场部'),('财务部');
CREATE TABLE emp(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(50),
gender CHAR(1), -- 性别
salary DOUBLE, -- 工资
join_date DATE, -- 入职日期
dept_id INT,
FOREIGN KEY(dept_id) REFERENCES dept(id) -- 外键 关联部门表(部门表的主键)
);
TRUNCATE TABLE emp;
INSERT INTO emp(NAME,gender,salary,join_date,dept_id)VALUES
('孙悟空','男',7200,'2013-02-24',1),
('猪八戒','男',3600,'2010-12-02',2),
('唐僧','男',9000,'2008-08-08',2),
('白骨精','女',5000,'2015-10-07',3),
('蜘蛛精','女',4500,'2011-03-14',1);
-- 笛卡尔积
SELECT * FROM emp,dept;
-- 内连接查询(隐式内连接 就是显示内连接的简化写法)
SELECT * FROM emp,dept WHERE emp.dept_id = dept.id;
-- 显示内连接
SELECT * FROM emp INNER JOIN dept ON emp.dept_id = dept.id;
-- 查询唐僧的姓名,性别,工资,所在部门名
SELECT emp.name,gender,salary,dept.name FROM emp,dept WHERE emp.dept_id = dept.id AND emp.name ='唐僧';
SELECT emp.name,gender,salary,dept.name FROM emp INNER JOIN dept ON emp.dept_id = dept.id AND emp.name = '唐僧';
外连接
-- 外连接
INSERT INTO dept(NAME) VALUES('销售部');
SELECT * FROM emp; SELECT * FROM dept;
-- 利用内;但是我们要是想看部门 没有对应的
SELECT * FROM emp,dept WHERE emp.dept_id = dept.id;
-- 左外连接查询;如果没有匹配的也会显示,为null
-- 以哪个表为主表,则哪个在前
SELECT * FROM dept LEFT JOIN emp ON emp.dept_id = dept.id;
-- 右外连接查询,与左相反
INSERT INTO emp VALUES(NULL,'沙僧','男',6666,'2013-12-05',NULL);
SELECT * FROM dept RIGHT JOIN emp ON emp.dept_id = dept.id;
子查询
-- 查询开发部中有哪些员工
-- 1、查询开发部的部门id
SELECT id FROM dept WHERE NAME = '开发部';
-- 2、 查询部门为1的员工
SELECT * FROM emp WHERE dept_id = 1;
-- 利用子查询
SELECT * FROM emp WHERE dept_id = (SELECT id FROM dept WHERE NAME = '开发部');
-- 子查询的案例 -- 子查询的结果为单行单列
-- 肯定在where后面作为条件
-- 父查询使用:比较运算符 > < <> =等
-- 查询工资最高的员工有哪些?
SELECT MAX(salary) FROM emp;
SELECT * FROM emp WHERE salary = (SELECT MAX(salary) FROM emp);
-- 查询工资水平小于平均水平的员工有哪些?
SELECT AVG(salary) FROM emp;
SELECT * FROM emp WHERE salary <(SELECT AVG(salary) FROM emp);
-- 子查询的结果是单列多行,结果集类似于一个数组
-- 父查询使用in运算符 -- 查询工资大于5000的员工,来自于哪些部门的名字
SELECT dept_id FROM emp WHERE salary >5000;
SELECT NAME FROM dept WHERE id IN (SELECT dept_id FROM emp WHERE salary >5000);
-- 查询开发部与财务部所有员工的信息
SELECT id FROM dept WHERE NAME IN('开发部','财务部');
SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE NAME IN('开发部','财务部'));
-- 子查询结果只要多列,肯定在from后面作为表
-- 查询出2011年以后,入职的员工信息,包括部门名 SELECT * FROM emp WHERE join_date >'2011';
SELECT * FROM dept,(SELECT * FROM emp WHERE join_date >'2011')AS e WHERE dept.id = e.dept_id;
-- 也可以使用表连接(更好些,这种情况的子查询可以用表连接来替代) SELECT * FROM emp,dept WHERE emp.dept_id = dept.id AND join_date>'2011';