SQL笔记：

最新推荐文章于 2024-09-14 18:42:05 发布

Say：Hi~

最新推荐文章于 2024-09-14 18:42:05 发布

阅读量301

点赞数 4

文章标签： sql 笔记

本文链接：https://blog.csdn.net/qq_53866837/article/details/137357877

版权

1：语法注意：大小写任意
可单行多行书写以分行‘；’结束
注释：单行 -- 空格或者# 多行 /* */

2：DBMS（数据库管理系统）由数据库和表组成

3：MySQL初始化：mysqld --initialize --user=mysql --console
安装MySQL服务：muqsld --install
启动：net start mysql
登录：mysql -uroot -p 临时密码：)bCltf4yhJY9
改密码： ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '201114';
修改root用户权限 create user 'root'@'%' IDENTIFIED WITH mysql_native_password BY '201114';
3：MySQL卸载： (1)停止服务：
打开DOS，使用net stop mysql命令
（2）卸载程序：控制面板卸载
（3）删除项目根目录文件夹：
进行MySQL安装位置，删除解压文件
（4）删除c盘下C:\ProgramData\MySQL所有文件
4：DDL（不涉及数据）：对数据库的操作：
产看所有库：show databases;
创建数据库：creat database[is not exists] mydb1 [charset=utf8]
切换（选择数据库）：use mydb1;
删除数据库： drop database [if exists] mydb1;
修改数据库编码：alter database mydb1 character set utf8;

-- 1、DDL操作
-- 产看所有数据库
show databases;
-- 创建数据库
create database if not exists mydb1;
-- 选择使用哪一个数据库
use mydb1;
-- 删除数据库
drop database if exists mydb1;
-- 修改数据库编码
alter database mydb1 character set utf8;

对表的常用操作：
-- 创建表格式
/* create table [if not exists] 表名（
字段名1 类型[(宽度)] [约束条件] [comment '字段说明']，
字段名2 类型[(宽度)] [约束条件] [comment '字段说明']，
.......
）[表的一些设置]；*/
-- 查看所有表
show tables
-- 查看指定某个表的创建语句
show creat table 表名；
-- 查看表结构
desc 表名
-- 删除表
drop table 表名

create database if not exists mydb1;
use mydb1;
create table if not exists student(
       sid int,
       name varchar(20),
       gender varchar(20),
       age int,
       birth date,
       address varchar(20)
);
show tables;
show create table student;
desc student;
drop table student;

修改表结构：
-- 3、对表结构的操作
-- 修改表添加列
-- alter table 表名 add 列名类型（长度） [约束]；
use mydb1;
alter table student add money varchar(20);

-- 修改列名和类型
-- 语法：alter table 表名 change 旧列名新列名类型（长度）
alter table student change money department varchar(30);

-- 删除表：
-- alter table 表名 drop 列名；
alter table student drop department；

-- 修改表名
-- rename table 表名 to 新表名
rename table stu to stu；

5：DML操作：数据操作语言，主要对mysql数据进行增删改
（1）、插入数据：
insert into 表（列名1，列名2，列名3...）values（值1，值2，值3。。。）
insert into 表 values（值1，值2，值3.。。。）向表中插入所有列（这个方式必须所有都赋满值）

（2）、数据修改：
update 表名 set 字段名=值，字段名=值。。。
update 表名 set 字段名=值，字段名=值。。。where 条件

例如：
所有同学地址改为重庆：
update student set address = '重庆'
id为1004的学生地址改为北京
update student set address= '北京' where id = 1004
id为1005的学生地址改为北京，成绩修改为100
update student set address= '北京'，

（3）、数据删除
-- delete from 表名[where 条件]；
-- truncate table 表名或者 truncate 表名
-- 注意：delete 和truncate原理不同 delet只删除内容，truncate类似于drop table，可以理解为是将整个表和删除，然后再创建
--
-- 例如：
-- 删除sid为1004的学生数据
-- delete from student where sid = 1004；
-- 删除表所有数据
-- delete from student；
-- 清空表数据
-- truncate table student；
-- truncate student；
--

6：MYSQL约束（只在创建表时，对表的列添加限制条件）：保证数据的完整和有效
*（1) 主键约束primary key（每个表最多一个，约束的列唯一且不为空）
添加单列主键：
1 在创建表时，直接在后面添加关键字 primary key
2 在创建表后，在最后添加语句constraint primary key（主键列名称） constraint primary可省略
联合主键（多个主键）：
create table 表名（
。。。
primary key （字段1.字段2.。。。字段n）
）；
修改表结构添加：事后再添加
create table 表名（
。。。
）；
alter table 表名 add primary key（列名1，列名2.。。）
删除主键：
alter table 表名 drop primary key；
*（2）自增长约束auto_increment（设定主键自增长时，会自动赋值。只能赋值主键）->默认初始值为1，增加一个类型，加1.必须只能是整数
设置自增长：
create table 表名（
id int primary key auto_increment，
。。。
）；
规定auto_increment起始值
方式1 ：
create table 表名（
id int primary key auto_increment，
。。。
）auto_increment=100；
方式2：
create table 表名（
id int primary key auto_increment，
。。。
）；
alter table 表名 auto_increment=100;
(3) 非空约束not null(强制不为空)
方法一：
create table 表名（
id int not NULL，
name varchar（10）not NULL，
。。。
）；
方法二：
alter table 表名 modify name varchr（20）not null；
alter table 表名 modify id int not null；
（4）唯一性约束unique（记录中字段不可以重复出现）
方式1：
create table 表名（
id int，
name varchar（10），
phone_number varchar(20) unique:
。。。
）；
方式2：
alter table 表名 add constraint 约束名（主要用于删除时候例如phone_unique） unique(列)；
（5）默认约束default（给个默认值）
方式1：
字段名数据类 default 默认值；
方式2：
alter table 表名 modify 列名类型 default 默认值；
（6）零填充约束zerofill（该字段值小于定义长度时，前面补0）
create table 表名（
id int zerofoll，
name varchar（10），
）；
删除：alter table t_user12 modify id int;
（7）外键约束约束foreign key

7：DQL操作
7.1基本查询
语法：
select
[all|distinct]
目标列的表达式别名
目标列的表达式别名...
form 表名或者试图别名，表名或者试图别名，
[where 条件表达式]
[group by 列名]
[having 条件表达式]
[order by 列名 [asc|desc]]
[limt 数字或者列表]

简化版
select*|列名 from 表 where 条件

-- 1、查询所有商品
select * from product;
-- 2、查询商品名字和价格
select pname,price from product;
-- 3、别名查询，使用关键字是as
-- 3.1、表别名
select * from product as p;
-- 3.2、列别名
select pname as pn from product;
-- 4.去掉重复值
select distinct price from product;
-- 5.查询结果是表达式（运算查询）：把所有商品价格+10显示
select pname,price+10 from product;

-- 运算符查询：
-- 算数运算符
上调十元
select pname,price+10 as p from product;
上调十倍
select pname,price*10 as p from product;

-- 比较运算符
-- 逻辑运算符
select * from product where pname='海尔洗衣机';
select * from product where price !=800;
select * from product where price > 800;
-- 价格在200-1000
select * from product where price between 200 and 1000;
select * from product where price >=200 and price<=1000;
select * from product where price >=200 && price<=1000;
-- 价格是200或者800
select * from product where price in(200,800);
select * from product where price=200 or price=1000;
select * from product where price=200 || price=1000;
-- 查询含有'鞋'的所有商品
select * from product where pname like '%鞋%';
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求最小值,如果求最小值时，有个值为null，则不会进行比较，直接为null
select least (10,5,20) as small_number;
-- 使用greatest求最大值，如果求最大值时，有个值为null，则不会进行比较，直接为null
select greatest (10,5,20) as big_number;
-- 位运算符（了解就好）

7.2 排序查询 order by
--
-- 语法：select 字段1，字段2...from 表名 order by 字段名1 [asc(升序)|desc（降序）],字段名[asc|desc]...
-- 1、价格降序排
select * from product order by price desc;
-- 2、价格降序的基础上，以分类排序（降序）
select * from product order by price desc,category_id asc;
-- 3、显示商品价格（去重）并降序排
select distinct price from product order by price desc;

7.3-- 聚合查询：
-- 之前查询都是横向查询，而聚合查询时纵向查询，对一列值进行计算，返回一个单一的值，会忽略空值。

-- count()记录指定列不为空数目，不统计null
-- 查询总条目
select count(pid) from product;
select count(*) from product;
查询大于200的总条数
select count(*) from product where price>200;
-- sum()计算指定列数值和，指定列为字符时，计算结过为0。
select sum(price) from product where category_id='c001';
-- max()指定列里最大值，指定列为字符时，使用字符串排序运算
select max(price) from product;
-- min()指定列最小值，指定列为字符时，使用字符串排序运算
select min(price) from product;
-- avg()指定列平均值，指定列为字符时，计算结果为0
select avg(price) from product;

7.4-- 分组查询：group by（对查询信息进行分组）

-- select 字段1 字段2....from 表名 group by 分组字段 having 分组条件；

-- 根据category_id分类，计算每个category_id的数目
select category_id, count(*) from product group by category_id;

-- having 筛选，分组查询后的筛选必须用having，不用where
select category_id, count(*) from product group by category_id having count(*) > 1;

select category_id, count(*) as cn from product group by category_id having cn > 4 order by cn asc;
-- 执行顺序：
-- from 表   第一步
-- 连接类型 join 表2   第二步
-- on 连接条件   第三步
-- where 筛选条件   第四步
-- group by 分组列表   第五步
-- having 分组后的筛选条件   第六步
-- select 查询列表（字段）   第七步
-- order by 排序列表   第八步
-- limit 偏移，条目数   第九步

7.5-- 分页查询：（由于数据大，对数据需要采取分页显示）
-- 方式1：select 字段1，字段2，...from 表名 limit n;
-- 方式2：select 字段1，字段2，...from 表名 limit m,n;
-- m:整数，表示从第几页索引开始，计算方式（当前页-1）*每页显示条数
-- n:整数，表示查询多少条数据

-- 查询product表前五条记录
select * from product limit 5;
-- 从第4条显示，显示5条
select * from product limit 3,5;

select * from product limit 0,5;
select * from product limit 5,5;
select * from product limit 10,5;

7.6---- insert into select:把一张表的数据导入另一张表中(要求另一张表必须存在)
-- insert into Table2(field1,field2,...)select vaule1,vaule2,...from Table1
-- 或者：
-- insert into Table2 select* from Table1
--

insert into product2(pname,price) select pname,price from product;、

insert into product2 select category_id,count(*) from product group by category_id;

7.7-- 正则表达式REGEXP：字符串匹配规则，本身也是一个字符串，用此来描述，定义匹配规则用于检索，替换那些符合某个规则的文本。
-- 通过REGEXP关键字支持正则表达式进行字符串匹配
-- ^ 匹配输入字符串开始的位置
select 'abc' REGEXP '^a'; --1
select * from product where pname REGEXP '^海';

-- $ 匹配输入字符串结束的位置
select 'abc' REGEXP 'a$'; --0
select 'abc' REGEXP 'c$'; --1
select * from product where pname REGEXP '裤$';

-- . 匹配除"\n"之外的任何单个字符
select 'abc' REGEXP '.b'; -- 1
select * from product where pid REGEXP '.1';
select * from product where pid REGEXP '1.';

-- [...] 匹配集合，匹配所包含的任意一个字符，例如'[abc]'可以匹配'plain'中的'a'。
select 'abc' REGEXP '[xyz]'; -- 0
select 'abc' REGEXP '[xaz]'; -- 1
select * from product where pid REGEXP '[123456789]';

-- [^...] 匹配字符集合。匹配未包含的任意字符。例如，'[^abc]'可以匹配'plain'中的'p'
-- 匹配除了后面集合中有的元素，要是前面有后面没有的，则为1
select 'a' REGEXP '[^abc]'; -- 0
select 'x' REGEXP '[^abc]'; -- 1
select 'abc' REGEXP '[^a]';

-- * 匹配前面子表达式0次或者多次，例如 zo*能匹配'z'以及'zoo'。*等价于{0，}
* 表示匹配前一个字符零次或多次，
select 'stab' REGEXP '.ta*b'; -- 1
select 'stb' REGEXP '.ta*b'; -- 1
select '' REGEXP 'a*b'; -- 0

-- + 匹配前面子表达式1次或者多次，例如 'zo+'能匹配'zo'以及'zoo',但不能匹配'z'。+等价于{1，}
select 'auuuu' REGEXP 'au+'; -- 1
-- {n} n是一个非负整数，匹配确定n次。例如 'o{2}'不能匹配“Bob”中的'o',但是能匹配“food”中的两个o。
select 'auuuuc' REGEXP 'au{4}c'; -- 1
select 'auuuuc' REGEXP 'au{1}c'; -- 0

-- {n,m} m和n均时非负整数，其中n<=m。最少匹配n次且最多匹配m次

select 'auuuuc' REGEXP 'au{1,4}c'; -- 1
select 'auuuuc' REGEXP 'au{1,2}c'; -- 0

-- (abc) 有括号就是整体匹配没括号就是单个匹配
select 'auuuuc' REGEXP 'a(uuuu)c'; -- 1
select 'xababy' REGEXP 'x(ab)*y'; -- 1
select 'xababy' REGEXP 'x(ab){1,2}y'; -- 1

8、多表操作：
MySQL多表之间的关系可以概括为：一对一、一对多/多对一关系，多对多

一对一：
一个学生只有一张身份证；一张身份证只能对应一学生。
在任一表中添加唯一外键，指向另一方主键，确保一对一关系。
一般一对一关系很少见，遇到一对一关系的表最好是合并表。

一对多/多对一：
部门和员工
分析：一个部门有多个员工，一个员工只能对应一个部门
实现原则：在多的一方建立外键，指向一的一方的主键

多对多：
学生和课程
分析：一个学生可以选择很多门课程，一个课程也可以被很多学生选择
原则：多对多关系实现需要借助第三张中间表。中间表至少包含两个字段，将多对多的关系，拆成一对多的关系，中间表至少要有两个外键，这两个外键分别指向原来的那两张表的主键

外键约束：
MySQL 外键约束（FOREIGN KEY）是表的一个特殊字段，经常与主键约束一起使用。对于两个具有关联关系的表而言，相关联字段中主键所在的表就是主表（父表），外键所在的表就是从表（子表）

*定义一个外键时，需要遵守下列规则：

*主表必须已经存在于数据库中，或者是当前正在创建的表。

*必须为主表定义主键。

*主键不能包含空值，但允许在外键中出现空值。也就是说，只要外键的每个非空值出现在指定的主键中，这个外键的内容就是正确的。

*在主表的表名后面指定列名或列名的组合。这个列或列的组合必须是主表的主键或候选键。

*外键中列的数目必须和主表的主键中列的数目相同。

*外键中列的数据类型必须和主表主键中对应列的数据类型相同。

*先添加主表数据，在添加从表数据。

8.1一对多建立关联：
-- 外键约束
-- 方式1 constraint 外键名 foreign key 字段名1，字段名2...REFERENCES 主表名主键列1，主键列2...
create DATABASE mydb3;
creat table if not EXISTS 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 cmp_fk foreign key (dept_id) REFERENCES dept (detpno)
);
例如：constraint cmp_fk foreign key (dopt id) REFERENCES dept (detpno)

-- 方式二：创建表之后，alter table (表名) add constraint 外键名 foreign key 列名 REFERENCES 主表名列名 ------如果没有外键名则直接是add foreign key

8.2多对多建立关联（创建中间表）：
利用中间表（从表外键列）和主表联系
修改删除时，中间从表可删除，但两边主表受从表依赖的数据信息不能删除或者修改

8.3多表查询（查询两个及以上表的查询）（外键约束对于多表查询并无影响）：
（1）交叉连接查询（产生笛卡尔积，了解）：
select* from A，B；
（2）内连接查询（使用关键字inner join --inner可以省略）
隐式内连接：selesct * from A，B where 条件
显示内连接：selesct * from A inner join B on 条件
（3）外连接查询：（使用关键字outer join --outer可以省略）
左外连接：left outer join
select * from A left outer join B on条件；
右外连接：right outer join
select * from A right outer join B on条件；
满外连接：full outer join
select *from A full outer join B on 条件；
（4）子查询
select的嵌套。返回类型分为四种：
1.单行单列：返回的是一个具体列的内容，可以理解为一个单值数据
2.单行多列：返回一行数据中多个列的内容
3.多行多列：返回多行记录之中的同一列内容，相当于给一个操作范围
4.多行多列：查询返回结果是一张临时表
（5）表自关联：
将一张表当成多张表来用

多表查询例子：
use mydb3;
-- 部门表
create table if not exists dept3(
deptno varchar(20)primary key, -- 部门号
name varchar(20)); -- 部门名字

-- 员工表
create table if not exists emp3(
   eid varchar(20) primary key, -- 员工编号
   ename varchar(20), -- 员工名字
   age int, -- 员工年龄
   dept_id varchar(20)); -- 员工所属部门

   insert into dept3 values ('1001','研发部'),
('1002','销售部'),
               ('1003','财务部'),
               ('1004','人事部');
insert into emp3 values
('1','乔峰',20,'1001'),
('2','段誉',21,'1001'),
               ('3','虚竹',23,'1001'),
               ('4','阿紫',18,'1001'),
               ('5','扫地僧',85,'1002'),
                   ('6','李秋水',33,'1002'),
                   ('7','鸠摩智',50,'1002'),
                   ('8','天山童姥',60,'1003'),
                   ('9','慕容博',58,'1003'),
                   ('10','丁春秋',71,'1005');

内连接查询：
查看所有员工信息：
内连接隐式：select* from dept3,emp3 where dept3.deptno = emp3.dept_id;
内连接显示：select* from dept3 inner join emp3 on dept3.deptno = emp3.dept_id;

产看研发部门的员工：
内连接显示：select* from dept3 join emp3 on dept3.deptno = emp3.dept_id and (dept3.deptno=1001 or dept3.deptno=1002) ;

产看每个部门的员工数，并升序排序：
内连接显示：select dept3.name,deptno,count(*) from dept3 join emp3 on dept3.deptno = emp3.dept_id GROUP BY dept3.name,deptno ORDER BY count(*) ASC ;

查询人数大于三的部门，并按照人数降序排序（在次注意执行顺序）。
select dept3.name,deptno,count(*) '总人数' from dept3 join emp3 on (dept3.deptno = emp3.dept_id ) GROUP BY dept3.name,deptno HAVING count(*)>=3 ORDER BY count(*) DESC ;

外连接查询：
左外连接：
查询哪些部门有员工，哪些部门没员工
select * from dept3 left join emp3 on dept3.deptno = emp3.dept_id;

右外连接：
查询哪些员工有部门，哪些员工没部门
select * from dept3 right join emp3 on dept3.deptno = emp3.dept_id;

满外连接：mysql用union实现->起到连接作用
select * from dept3 left join emp3 on dept3.deptno = emp3.dept_id
union
select * from dept3 right join emp3 on dept3.deptno = emp3.dept_id;

子查询
select * from emp3 where dept_id in (select deptno from dept3 where name='销售部' or name='研发部');

select * from emp3 where dept_id=(select deptno from dept3 where name='销售部') or dept_id=(select deptno from dept3 where name='研发部');

查询研发部20岁以下的员工信息,部门信息
select * from dept3 join emp3 on dept3.deptno = emp3.dept_id and (emp3.age<=20 and dept3.name='研发部');
select * from dept3 join emp3 on dept3.deptno = emp3.dept_id and (age<=20 and name='研发部');
子查询
select * from emp3 where age<=30;
select * from dept3 where name='研发部'; 一行多列
select * from (select* from dept3 where name='研发部') t1 join (select * from emp3 where age<=30 ) t2 on t1.deptno = t2.dept_id;--- 多行多列注意本行建立了两个新表则必须重新起名