目录
1、DDL操作
//查看所以数据库
SHOW DATABASES;
//创建数据库
CREATE DATABASE mydb1;
CREATE DATABASE mydbq1;
//选择使用哪一个数据库
USE mydb1;
//删除数据库
DROP DATABASE mydbq1;
//在数据库里创建一个表
USE mydb1;
CREATE TABLE IF NOT EXISTS student(
sid INT,
NAME VARCHAR(20),
gender VARCHAR(20),
age INT,
birth DATE,
address VARCHAR(20),
score DOUBLE
);
//查看当前数据库所以的表
SHOW TABLES;
//查看所以的表的创建语句
SHOW CREATE TABLE student;
//查看表结构
DESC student;
//修改表结构
1、添加列:添加一个新字段 dept 院系
ALTER TABLE student ADD dept VARCHAR(20);
2、修改表的列名和类型
ALTER TABLE student CHANGE dept department VARCHAR(30);
3、删除列
ALTER TABLE student DROP department;
4、修改表名
RENAME TABLE student stu;
2、DML操作
-- 1、数据的插入
-- 格式1：INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...);
INSERT INTO student(sid,NAME,gender,age,birth,address,score)
VALUES (1001,'张三','男',18,'2001-12-12','北京',90.2),
(1002,'李四','男',17,'2002-8-12','上海',88.2),
(1003,'王芳','女',19,'2000-9-18','广州',96),
(1004,'小华','女',17,'2002-5-16','北京',93);
INSERT INTO student (sid) VALUES(1005);
-- 格式2:INSERT INTO 表名 VALUES (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...);
INSERT INTO student VALUES
(1005,'张伟','男',19,'2000-8-22','北京',88),
(1006,'李四','男',17,'2002-6-14','上海',86),
;
-- 修改数据
-- 格式1、UPDATE 表名 SET 字段名1 = 值1, 字段名2 = 值2, ... [ WHERE 条件 ];
-- 格式2、UPDATE emp SET name = 'Jack' WHERE id = 1;
-- 1、将所以学生的地址修改为重庆
UPDATE student SET address='重庆';
-- 2、将ID为1004的学生的地址修改为北京
UPDATE student SET address='重庆' WHERE sid=1004;
-- 3、将ID为1004的学生的地址修改为北京，并把成绩修改为1004；
UPDATE student SET address='北京' ,score=100 WHERE sid=1004;
UPDATE student SET sid=1006 WHERE NAME='张伟';
-- 删除数据
-- DELETE FROM 表名 [ WHERE 条件 ];
-- truncate table 表名或者 truncate 表名
1、删除sid为1005的学生
DELETE FROM student WHERE sid=1005;
2、删除表的所以数据
DELETE FROM student;
3、清空表数据
TRUNCATE TABLE student;
-- delete只删内容，而truncate类似于drop table，可以理解为删除整个表
3、MQSQL 的约束
-- 主键约束：primary key
-- 方法1：create table 表名(
-- ···
-- <字段名><数据类型> primary key，
-- ···
-- );
CREATE TABLE mydb1.emp1(
sid INT PRIMARY KEY,
NAME VARCHAR(20),
daptid INT,
salary DOUBLE
);
-- 方法2：create table 表名(
-- ···
-- [constraint <约束名>] primary key[字段名]，-- [constraint <约束名>] 可以省略
-- );
CREATE TABLE mydb1.emp2(
sid INT ,
NAME VARCHAR(20),
daptid INT,
salary DOUBLE，
CONSTRAINT pk PRIMARY KEY(sid)
);
-- 主键的作用
INSERT INTO emp1 VALUES(10001,'王芳',20,20384);
INSERT INTO emp1 VALUES(10001,'李丽',10,9973);-- 报错
INSERT INTO emp1 VALUES(10002,'李丽',10,9973);-- 成功
-- 联合主键:相加不能重复，不能为空
CREATE TABLE mydb1.emp3(
sid INT ,
NAME VARCHAR(20),
salary DOUBLE,
deptid INT,
PRIMARY KEY(sid,NAME)
);
CREATE TABLE mydb1.emp4(
eid INT ,
NAME VARCHAR(20),
deptid INT,
salary DOUBLE
);
-- 添加主键
-- alter table 数据表名 add primary key(数据字段)
ALTER TABLE emp4 ADD PRIMARY KEY(eid);
-- 删除主键不分单链多链)
-- alter table 数据表名 drop primary key;
ALTER TABLE emp4 DROP PRIMARY KEY;
-- 自增长约束
-- 与主键约束联合使用，去自动增加主键
CREATE TABLE user1(
sid INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)
);
INSERT INTO user1 VALUES(NULL,'张三');
INSERT INTO user1(NAME) VALUES('李四');
-- 自定义增长
CREATE TABLE user2(
sid INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)
) AUTO_INCREMENT =100;
DELETE FROM user1；-- 删除之后，自增长还是从下一个值的基础上加1
INSERT INTO user1 VALUES(NULL,'张三');
INSERT INTO user1(NAME) VALUES('李四');
TRUNCATE user1；-- truncate 之后从 1 开始
-- 非空约束
-- 字段 数据类型 not null;
CREATE TABLE user3(
eid INT NOT NULL,
NAME VARCHAR(20),
deptid INT,
salary DOUBLE
);
INSERT INTO user3(NAME) VALUES('zhangsan');-- 不可以
INSERT INTO user3(eid,NAME) VALUES(1001,NULL);-- 可以
-- 删除非空约束 alter table 表名 modify 字段 类型
-- 唯一约束
USER mydb1;
CREATE TABLE user4(
id INT,
NAME VARCHAR(20),
phone_number VARCHAR(13) UNIQUE -- 唯一约束
);
-- 删除唯一约束
ALTER TABLE user4 DROP INDEX phone_number;
4、DQL操作
-- 数据准备
CREATE DATABASE mydb2;
USER mydb2;
CREATE TABLE mydb2.product(
pid INT PRIMARY KEY AUTO_INCREMENT,
pname VARCHAR(20) NOT NULL,
price DOUBLE,
category_id VARCHAR(20) -- 种类分类
);
INSERT INTO product VALUES(NULL,'海尔洗衣机',5000,'c001'),
(NULL,'美的空调',4500,'c002'),
(NULL,'格力冰箱',6000,'c003'),
(NULL,'九阳豆浆机',3000,'c004');
INSERT INTO product VALUES(NULL,'啄木鸟衬衣',360,'c002'),
(NULL,'恒源祥西裤',800,'c002'),
(NULL,'花花公子夹克',440,'c002'),
(NULL,'劲霸休闲裤',266,'c002'),
(NULL,'海澜之家卫衣',180,'c002'),
(NULL,'杰克琼斯运动裤',430,'c002'),
(NULL,'兰蔻面霜',300,'c003'),
(NULL,'雅诗兰黛精华水',200,'c003'),
(NULL,'香奈儿香水',350,'c003'),
(NULL,'SK-II神仙水',350,'c003'),
(NULL,'资生堂粉底液',80,'c003'),
(NULL,'老北京方便面',56,'c004'),
(NULL,'良品铺子海带丝',17,'c004'),
(NULL,'三只松鼠坚果',88,NULL);
-- 1、查询所有产品
SELECT pid,pname,price,category_id FROM product;
SELECT * FROM product;
-- 2、查询商品名和商品价格
SELECT pname,price FROM product;
-- 3、别名查询，使用关键字as(as可以省略)
-- 3.1表别名
SELECT * FROM product AS p;
SELECT * FROM product p;
-- 3.2列别名
SELECT pname AS '商品名',price '商品价格' FROM product;
-- 4、去除重复值(distinct)
SELECT DISTINCT price FROM product;
SELECT DISTINCT * FROM product;
-- 5、查询结果是表达式（运算查询）：将所有商品加价10
SELECT pname,price +10 new_price FROM product;
-- 条件查询、
-- 语法：
-- SELECT 字段列表 FROM 表名 WHERE 条件列表;
-- 条件：
-- 比较运算符 功能
-- > 大于
-- >= 大于等于
-- < 小于
-- <= 小于等于
-- = 等于
-- <> 或 != 不等于
-- BETWEEN … AND … 在某个范围内（含最小、最大值）
-- IN(…) 在in之后的列表中的值，多选一
-- LIKE 占位符 模糊匹配（_匹配单个字符，%匹配任意个字符）
-- IS NULL 是NULL
-- 逻辑运算符 功能
-- AND 或 && 并且（多个条件同时成立）
-- OR 或 || 或者（多个条件任意一个成立）
-- NOT 或 ! 非，不是
-- 查询大于600元的所有商品
SELECT * FROM product WHERE price>= 600;
-- 查询小于600元大于60的所有商品
SELECT * FROM product WHERE price BETWEEN 600 AND 60;
SELECT * FROM product WHERE price<= 600 AND price >60;
SELECT * FROM product WHERE price<= 600 && price >60;
-- 查询商品是200或者800的所有产品
SELECT * FROM product WHERE price IN(80,600);
SELECT * FROM product WHERE price= 600 OR price =80;
SELECT * FROM product WHERE price= 600 || price =80;
-- 查询含有“裤”字的所有产品(模糊匹配（_匹配单个字符，%匹配任意个字符）)
SELECT * FROM product WHERE pname LIKE '%裤';
-- 查询以“海”开头的所有商品
SELECT *FROM product WHERE pname LIKE '海%';
-- 查询第二个字为“蔻”的所有商品("_"下划线匹配单个字符)
SELECT *FROM product WHERE pname LIKE '_蔻%';
-- 查询category_id为null的商品
SELECT * FROM product WHERE category_id IS NULL;
-- 查询category_id不为null的商品
SELECT * FROM product WHERE category_id IS NOT NULL;
-- 使用least求最小值,
SELECT LEAST(10,5,20)AS small_number;
SELECT LEAST(10,NULL,20);-- 如果一个值为null，则不会比较，直接返回null
-- 使用greatest求最大值
SELECT GREATEST(10,5,20)AS small_number;
-- 排序查询 order by
SELECT * FROM product ORDER BY price ASC;-- 升序
SELECT * FROM product ORDER BY price DESC;-- 降序
-- 去重复,并排序
SELECT DISTINCT price FROM product ORDER BY price DESC;
-- 聚合查询（聚合函数）
-- 常见聚合函数：
-- 函数 功能
-- count 统计数量
-- max 最大值
-- min 最小值
-- avg 平均值
-- sum 求和
-- 查询商品的总数据
SELECT COUNT(pid) FROM product;
SELECT COUNT(*) FROM product;
-- 查询价格大于200的商品数
SELECT COUNT(pid) FROM product WHERE price >200;
-- 查询是商品的价格总和
SELECT SUM(price) FROM product ;
-- 查询是商品的价格最大值
SELECT MAX(price) FROM product;
-- 查询分类为“c002”的所有商品的平均价格
SELECT * FROM product WHERE category_id='c002';-- 先找出“c002”
SELECT AVG(price) FROM product WHERE category_id='c002';
-- 聚合查询对null值的处理 不计入到数据中
-- 分组查询 group by
-- 语法：SELECT 字段列表 FROM 表名 [ WHERE 条件 ] GROUP BY 分组字段名 [ HAVING 分组后的过滤条件 ];
-- 统计各个分类的个数
SELECT category_id,COUNT(pid) FROM product GROUP BY category_id;
-- 统计各个分类的个数且只显示大于4 的信息
SELECT category_id,COUNT(pid) cnt FROM product GROUP BY category_id HAVING cnt >4;
-- 执行时机不同：where是分组之前进行过滤，不满足where条件不参与分组；having是分组后对结果进行过滤。
-- 判断条件不同：where不能对聚合函数进行判断，而having可以。
-- 分页查询limit
-- 语法：
-- SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数;
-- 例子：
-- 查询product数据，展示前5条
-- SELECT * FROM product LIMIT 5;
-- 展示从第5条开始，显示条
-- SELECT * FROM product LIMIT 5, 5;
-- 将一张表插入到另外一张表中 insert into select
CREATE TABLE product2(
pname VARCHAR(20),
peice DOUBLE
);
INSERT INTO product2 SELECT pname,price FROM product;
-- --查询练习（1）
user mydb2;
CREATE TABLE student(
id int,
name varchar(20),
gender varchar(20),
chinese int,
english int,
math int
);
INSERT INTO student(id,NAME,gender,chinese,english,math)VALUES(1,'张明','男',89,78,90);
INSERT INTO student(id,NAME,gender,chinese,english,math) VALUES(2,'李进','男',67,53,95);
INSERT INTO student(id,NAME,gender,chinese,english,math) VALUES(3,'王五','女',87,78,77);
INSERT INTO student(id,NAME,gender,chinese,english,math) VALUES(4,'李一','女',88,98,92);
INSERT INTO student(id,NAME,gender,chinese,english,math) VALUES(5,'李财','男',82,84,67);
INSERT INTO student(id,NAME,gender,chinese,english,math) VALUES(6,'张宝','男',55,85,45);
INSERT INTO student(id,NAME,gender,chinese,english,math)VALUES(7,'黄蓉', '女',75,65,30);
INSERT INTO student(id,NAME,gender,chinese,english,math) VALUES(7,'黄蓉', '女',75,65,30);
-- 查询表中所有的数据
select *from student;
-- 查询表中所有学生的姓名对应的英语成绩
SELECT name,english from student;
-- 过滤重复的数据
select distinct *from student;
-- 统计总分
select name ,chinese+english+math as total_score from student;
-- 在所有学生的总分上加10分特长分
select name ,(chinese+english+math)+10 as total_score from student;
-- 使用别名表示学生分数
select name,chinese '语文成绩',english '英语成绩',math '数学成绩' from student;
-- 查询英语成绩大于90的
select *from student where english>90;
-- 查询总分大于200的同学
select * from student where (chinese+english+math) > 200;
-- 查询英语成绩在80-90 之间
select *from student where english between 80 and 90;
select *from student where english >= 80 and english<=90;
-- 查询英语成绩不在80-90 之间
select *from student where english not between 80 and 90;
select *from student where not (english >= 80 and english<=90);
-- 查询所有姓李同学的英语成绩
select name,english from student where name like '李%';
-- 查询数学分80,且语文80 的同学
select *from student where math=80 and chinese=80;
-- 对数学成绩进行降序
select *from student order by math desc;
-- 对姓李的总分成绩进行排序
select *from student where name like '李%' order by (chinese+english+math) desc;
-- 查询男女生分别有多少人，并将人数按降序排序，查询出人数大于4的性别人数
select gender,count(*) as cnt from student group by gender having cnt>4 order by cnt desc;
-- --查询练习（2）
user mydb2;
create table emp(
empno int, -- 员工编号
ename varchar(20),
job varchar(50),
mgr int, -- 上级领导编号
hiredate date,
sal int,-- 薪资
comm int, -- 奖金
deptno int -- 部门
);
INSERT INTO emp VALUES(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO emp VALUES(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO emp VALUES(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO emp VALUES(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO emp VALUES(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO emp VALUES(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO emp VALUES(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
INSERT INTO emp VALUES(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20);
INSERT INTO emp VALUES(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO emp VALUES(7844,'TURNER','SALESMAN',7698,'1981-09-08', 1500,0,30);
INSERT INTO emp VALUES(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20);
INSERT INTO emp VALUES(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
INSERT INTO emp VALUES(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
INSERT INTO emp VALUES(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);
-- 排序姓名第二个字母不是“A”且薪水大于1000的员工信息，按年薪排序
select *from emp where ename not like'_A%' and sal>1000 order by (sal*12+comm) desc;
select *from emp where ename not like'_A%' and sal>1000 order by (sal*12+ifnull(comm,0)) desc;
-- 求每一个部门的平均薪水
select deptno ,avg(sal) from emp group by deptno;
-- 求每个部门每个岗位最高的薪水
select deptno,job,max(sal) from emp group by deptno,job;
-- 求平均薪水大于2000的部门编号
select deptno ,avg(sal) from emp group by deptno having avg(sal)>2000;
-- 查询员工工资最高和最低之间的差距
select max(sal)-min(sal)from emp ;
-- 外键约束
-- 添加外键：
-- CREATE TABLE 表名(
-- 字段名 字段类型,
-- ...
-- [CONSTRAINT] [外键名称] FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名)
-- );
-- ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表(主表列名);
-- 例子
-- alter table emp add constraint fk_emp_dept_id foreign key(dept_id) references dept(id);
一对多
案例：部门与员工
关系：一个部门对应多个员工，一个员工对应一个部门
实现：在多的一方建立外键，指向一的一方的主键
-- 部门表 主表
create table dept(
detpno varchar(20) primary key,
name varchar(20)，
);
-- 员工表 副表
create table emp(
eid varchar(20) primary key,
ename varchar(20),
age int,
dept_id varchar(20),
constraint emp_fk foreign key(dept_id) references dept(detpno)
);
-- 操作
-- 1、添加主表数据
-- 注意必须先给主表添加数据
insert into dept values('1001','研发部');
insert into dept values('1002','销售部') ;
insert into dept values('1003','财务部');
insert into dept values('1004','人事部');
-- 2、添加从表数据
-- 给从表添加数据时，外键列不能随便写，必须依赖主表的主键列
insert into emp values('1','介峰',20,'1001');
insert into emp values('2','段誉',21,'1001');
insert into emp values('3','虚竹',23,'1001');
insert into emp values('4','阿紫',18,'1002');
insert into emp values('5','扫地僧',35,'1002');
insert into emp values('6','李秋水',33,'1003');
insert into emp values('7','鸠摩智',50,'1003');
insert into emp values('8','天山童姥',60,'1004');
insert into emp values('8','天山童姥',60,'1005');-- 不可以
-- 3、删除数据
-- 1、在主表classes中没有的数据值，在副表中是不可以使用的
-- 2、主表中的记录被副表引用，是不可以被删除的
alter from dept where deptno='1004';-- 不可以
-- 4、删除外键约束
-- ALTER TABLE 表名(从表) DROP FOREIGN KEY 外键名;
alter table emp drop foreign key emp_fk；
多对多
案例：学生与课程
关系：一个学生可以选多门课程，一门课程也可以供多个学生选修
实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键
-- 创建学生表（左侧主表）
create table students(
sid int primary key auto_increment,
name varchar(20),
age int,
gender varchar(20)
);
-- 课程表（右侧主表）
create table course(
cid int primary key auto_increment,
cidname varchar(20)
);
-- 创建中间表（从表）
create table score(
sid int,
cid int,
score double
);
-- 4、创建外键约束（2次）
alter table score add foreign key(sid) references students(sid);
alter table score add foreign key(cid) references course(cid)
-- 正则表达式
MySQL 正则表达式
在前面的章节我们已经了解到MySQL可以通过 LIKE ...% 来进行模糊匹配。
MySQL 同样也支持其他正则表达式的匹配， MySQL中使用 REGEXP 操作符来进行正则表达式匹配。
如果您了解PHP或Perl，那么操作起来就非常简单，因为MySQL的正则表达式匹配与这些脚本的类似。
下表中的正则模式可应用于 REGEXP 操作符中。
模式 描述
^ 匹配输入字符串的开始位置。如果设置了 RegExp 对象的 Multiline 属性，^ 也匹配 '\n' 或 '\r' 之后的位置。
$ 匹配输入字符串的结束位置。如果设置了RegExp 对象的 Multiline 属性，$ 也匹配 '\n' 或 '\r' 之前的位置。
. 匹配除 "\n" 之外的任何单个字符。要匹配包括 '\n' 在内的任何字符，请使用像 '[.\n]' 的模式。
[...] 字符集合。匹配所包含的任意一个字符。例如， '[abc]' 可以匹配 "plain" 中的 'a'。
[^...] 负值字符集合。匹配未包含的任意字符。例如， '[^abc]' 可以匹配 "plain" 中的'p'。
p1|p2|p3 匹配 p1 或 p2 或 p3。例如，'z|food' 能匹配 "z" 或 "food"。'(z|f)ood' 则匹配 "zood" 或 "food"。
* 匹配前面的子表达式零次或多次。例如，zo* 能匹配 "z" 以及 "zoo"。* 等价于{0,}。
+ 匹配前面的子表达式一次或多次。例如，'zo+' 能匹配 "zo" 以及 "zoo"，但不能匹配 "z"。+ 等价于 {1,}。
{n} n 是一个非负整数。匹配确定的 n 次。例如，'o{2}' 不能匹配 "Bob" 中的 'o'，但是能匹配 "food" 中的两个 o。
{n,m} m 和 n 均为非负整数，其中n <= m。最少匹配 n 次且最多匹配 m 次。
-- 实例
-- 了解以上的正则需求后，我们就可以根据自己的需求来编写带有正则表达式的SQL语句。以下我们将列出几个小实例(表名：person_tbl )来加深我们的理解：
-- 查找name字段中以'st'为开头的所有数据：
mysql> SELECT name FROM person_tbl WHERE name REGEXP '^st';
-- 查找name字段中以'ok'为结尾的所有数据：
mysql> SELECT name FROM person_tbl WHERE name REGEXP 'ok$';
-- 查找name字段中包含'mar'字符串的所有数据：
mysql> SELECT name FROM person_tbl WHERE name REGEXP 'mar';
-- 查找name字段中以元音字符开头或以'ok'字符串结尾的所有数据：
mysql> SELECT name FROM person_tbl WHERE name REGEXP '^[aeiou]|ok$';
2222
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
