【MySQL】黑马教程数据库（语法）

• 本文章主要依据b站黑马程序员数据库教程，网课链接如下【黑马程序员 MySQL数据库入门到精通，从mysql安装到mysql高级、mysql优化全囊括】https://www.bilibili.com/video/BV1Kr4y1i7ruvd_source=3d0775dec8c2cf72e4a9116d7fa64797
• 这篇文章是在学习中的自用笔记，纯手工敲上去的，现分享给大家交流学习，若有错误欢迎并感谢各位大佬指正

启动与停止

通过win r运行窗口 services.msc,右键启动停止

cmd命令行 net start/stop mysql80

连接

快捷方式

命令行 mysql -u root -p密码

SQL(*)

DDL

- 查询 
show databases;
- 查询当前数据库 
select database ();
- 创建 
create database wuxie;  
- 删除 drop database wuxie;
- 使用/切换 use wuxie;

• DDL-表操作-查询

-查询当前数据库所有表（先use指令）
show tables;
-查询表结构
desc 表名；[把创建的表格可视化]
-查询指定表的建表语句
show create table 表名； [更详细]

• DDL-表操作-创建

create table 表名(
字段1 字段1类型 [comment 注释1]，
......
字段n,字段n类型 [comment 注释n][无，]
)[comment 表注释]；

create table tb1(
id int comment '编号',
name varchar(50) comment '姓名'，    
#（50）和define很像
age int comment '年龄'，
gender varchar(1) comment '性别'
)comment '用户表'；

• DDL-表操作-数据类型

数值类型，字符串类型，日期时间类型

char和varchar

存储方式：CHAR类型在存储数据时会固定使用指定长度的空间，不管实际存储的数据是否达到了最大长度。而VARCHAR类型只会占用实际存储的数据所需的空间，不会浪费额外的空间。

长度限制：对于CHAR类型，你需要指定字段的固定长度。例如，CHAR(10)表示该字段的长度为10个字符。而对于VARCHAR类型，你也需要指定最大长度，但它可以根据实际存储的数据而变化，不一定要使用全部的最大长度。

• DDL-表操作-修改

添加字段

alter table 表名 add 字段名 类型（长度）;

修改字段数据类型

alter table 表名 modify 字段名 新数据类型 （长度）;

修改字段名和字段类型

alter table 表名 change 旧字段名 新字段名 数据类型 （长度）;

删除字段

alter table 表名 drop 字段名；

修改表名

alter table 表名 rename to 新表名；

• DDL-表操作-删除

删除表

drop table 表名；

删除指定表，重创该表格（=清空表格）

truncate table 表名；

DML

• DML-添加数据

给指定字段添加数据

insert into 表名(字段1，字段2...) values(值1，值2...);
insert into score(id,name,chinese,math,english) values (1,'tt',66,66,66)

给全部字段添加数据

insert into 表名 values(值1，值2...);

批量添加数据

insert into 表名(字段1，字段2...) values(值1，值2...)，(值1，值2，...)，(值1，值2，...)；#字符串和日期在引号内
insert into表名 values(值1，值2，...)，(值1，值2，...)，(值1，值2，...)；

• DML-修改数据

update 表名 set 字段名1=值1，字段名2=值2[where 条件]；

• DML-删除数据（update设为none）

delete from 表名[where 条件]；

DQL

• DQL-基本查询

查询多个字段

 select 字段1，字段2...from 表名；
 select *from 表名；

设置别名

select 字段1 as 别名，字段2 as 别名... from 表名；

去除重复记录

select distinct 字段 from 表名；

• DQL-条件查询

select 字段 from 表名 where 条件列表；

查询年龄等于18或20或40的员工
select *from emp where age in (18,20,40);
查询姓名为两个字的员工信息
select *from emp where name like "__";
查询身份证号最后一位为X的员工
select *from emp where name like "%X";

聚合函数：count min max avg sum

select 聚合函数（字段） from 表名;

• DQL-分组查询

select 字段 from 表名 [where 条件] group by 字段名 [having 分组后的条件]
select gender,count(*) from emp group by gender;
select avg(age) from emp group by gender;
select aderss,count(*) from emp where age<45 group by adress having count(*)>=3;

顺序：where>聚合函数>having

• DQL-排序查询

select 字段 from 表名 order by 字段1 排序方式1，字段2，排序方式2；
//多次排序默认前一个相同再排
排序方式：asc升序（默认）；desc降序

• DQL-分页查询

select 字段 from 表名 limit 起始索引，每页查询记录数；

查询性别为男，年龄在20-40之间前五个员工的信息，

对查询结果按年龄升序，年龄相同按入职时间升序

select *from emp where gender=‘男’ and age between 20 and 40 and order by age asc,time asc limit 5;

DCL

• DCL-管理用户

• DCL-权限控制
  

grant all on * . * to ‘wuxie’@‘%’;

函数

select 函数 (参数)

字符串函数

concat(s1,s2,...sn)           字符串拼接
lower(str)                    将字符串转化为小写
upper(str)                    将字符串转化为大写
lpad(str,n,pad)               左填充（字符串，长度，填充物）
rpad(str,n,pad)               右填充（字符串，长度，填充物）
trim(str)                     去掉字符串头部和尾部空格
substring(str,start,len)      返回字符串从str位置起的len个长度的字符串

数值函数

ceil          向上取整
floor         向下取整
mod(x,y)      返回x/y的模
rand ()       返回0-1内的随机数
round(x,y)    求参数x的四舍五入值保留y位小数

日期函数

select name,datediff(curdate,entrydate) as ‘entrydays’ from emp order by entrydays desc

流程控制语句

if(value,t,f)          如果value为ture返回t否则返回f
ifnull(value1,value2)  如果value1不为null（‘ ’不算）则返回1否则返回2
select 
     name,
     (case adress when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end) as '工作地址'
from emp;

约束

多个约束空格分开

字段 类型 约束

约束演示

自动增长 auto_increment

外键约束

• 添加外键

create table 表名(
字段名 数据类型，
...
constraint [外键名]foreign key(外键字段名) references 主表（主表列名)
）；
alter table 表名 add constraint 外键名 foreign key（外键字段名） references 主表列名；

• 删除外键

alter table 表名 dorp foreign key 外键名；

• 删除/更新行为

alter table 表名 add constraint 外键名 foreign key（外键字段名） references 主表列名 on update 行为 on delete 行为；

多表查询

select *from emp ,dept where emp.dept_id=dept.id;
select *from emp e inner join dept d on e.dept_id =d.id; 

内连接(两表∩)

-隐式内连接
select 字段列表 from 表1，表2 where 条件...；（上述案例）
-显式内连接
select 字段列表 from 表1 [inner] join 表2 on 连接条件(where)...;

别名：select *from emp e,dept d即可,但不能再用原名

外连接 (有null的情况)

select 字段列表 from 表1 left outer join 表2 on 条件
查询左表包含∩

自连接(看作两个表)

select a.name b.name from emp a,emp b where a.managerid=b.id;

联合查询(列数一致)

select *from emp where salary<5000
union all(去all降重)
select *from emp where age>50;

子查询

• 标量子查询

select *from t1 where column1=(select column1 from t2);

• 列子查询

select  * from emp where dept_id in(select id from dept where name='' or name ='');

• 行子查询

select * from emp where (select salary,managerid)=(select salary,managerid from emp where name='张无忌');

• 表子查询

事务

查看/设置事务提交方式

select @@autocommit;
set @@autocommit=0(手动)/1(自动);
commit;//提交事务
rollback;//回滚事务
start transaction 或 begin//开启事务

引擎

-查看引擎
show engines;
-在表注释之前添加引擎 
engines=

索引

B-Tree

5阶的B-Tree一个节点存储四个数据五个指针

B+Tree根节点实现索引，叶子节点实现数据存储，形成单向链表

hash

对等比较（in,=)不支持范围查询（between,<,>)

• 如果存在主键，主键索引就是聚集索引

​ 如果不存在主键，使用第一个唯一索引作为聚集索引

• 聚集索引叶子节点下面竖着挂着一行的数据

​ 二级索引叶子节点下面挂主键值

​ 回表查询：从二级索引到聚集索引

语法

-创建索引
create [unique|fulltext] index 索引名 on 表名（列名/字段名）
-查看索引
show index from 表名；
-删除索引 
drop index 索引名 on 表名；

执行频率

show global status like 'Com____';

视图(*)

虚拟表，数据动态生成，不保存数据

-创建视图
create view 视图名 as select 字段名 from 表名(来自基表查询返回的数据)  [with cascaded(前面的条件也接受检查)/local check option] ；
-查询视图
show create view 视图名；
select *from 视图名；
-修改视图
alter view 视图名 as select 字段名 巴拉巴拉限制条件；
create or replace view 视图名 as select 同上；
-删除视图
drop view 视图名；

1. LOCAL：使用此选项时，在执行视图查询时，如果其中包含另一个被更改或删除的视图，则仅修改当前视图，不会影响到其他视图。

2. CASCADE：使用此选项时，在执行视图查询时，如果其中包含另一个被更改或删除的视图，则将对所有相关视图执行相同的更改或删除操作。

   总的来说LOCAL 仅影响当前视图，而 CASCADE 将影响所有相关视图。

value

在 SQL 中，value 通常用于表示某个数据对象的具体值。它可以在各种 SQL 语句中使用，例如 SELECT、INSERT、UPDATE 和 DELETE，这些语句都需要对数据库中的数据进行读取、插入、更新或删除操作。

1. 插入数据：INSERT 语句可以将 value 插入到数据库表中。

   INSERT INTO table_name (column1, column2, column3) VALUES (value1, value2, value3);
   INSERT INTO employees (first_name, last_name, age) VALUES ('Alice', 'Smith', 25);
   
   

2. 更新数据：UPDATE 语句可以使用 SET 关键字将 value 更新到数据库表中。

   UPDATE table_name SET column_name = value WHERE condition;
   UPDATE employees SET age = 30 WHERE first_name = 'Alice';
   
   

3. 进行比较：在 SELECT 或 WHERE 语句中，可以使用 value 进行数值、字符串或日期等数据类型的比较。

   sql复制代码SELECT * FROM table_name WHERE column_name > value; -- 返回所有大于 value 的行
   SELECT * FROM table_name WHERE column_name LIKE 'value%'; -- 返回所有以 value 开头的行
   SELECT * FROM table_name WHERE date_column >= value; -- 返回所有日期大于等于 value 的行
   SELECT * FROM employees WHERE age > 30;
   
   

4. 设置默认值：可以使用 DEFAULT 关键字来设置 value 作为列的默认值，当该列没有指定值时，会使用该默认值。

   sql复制代码CREATE TABLE table_name (
       column1 datatype DEFAULT value,
       column2 datatype DEFAULT value,
       ...
   );
   CREATE TABLE employees (
       id INT PRIMARY KEY,
       first_name VARCHAR(50) NOT NULL,
       last_name VARCHAR(50) NOT NULL,
       age INT DEFAULT 18
   );
   
   

视图更新

视图的行和基础表的行一对一对应

1.聚合函数

2.distinct

3.group by

4.having

5.union

存储

-创建
delimiter $$
create procedure p1(in|out|inout)
begin 
    操作;
end;(;变$$)
-调用
call p1();
-查看
select *from information_schema.routines
show create procedure p1();
-删除
drop procedure p1()

• 变量

-查看变量
show [session|global] variables;
select @@[session|global] 系统变量名;
-设置变量
set[session|global] 变量名=值；
set @@[session|global] 变量名=值；

两个@代表系统变量，一个@代表用户自定义变量

• 用户自定义变量

-赋值
select @wuxie:=
select 字段名 into @wuxie from 表名；
-使用
select @wuxie

• 局部变量

declare 变量名 类型

if

if 条件1 then
...
elseif 条件2 then 
...
else
...
end if;

case

case
when 条件1 then
sql1;
when 条件2 then
sql2;
else
条件n;
end case;

while

while 条件 do
sql
end while;

repeat

（do while）满足until退出循环

repeat
sql
until 条件
end repeat;

loop(不增加退出条件死循环)

[begin_lable:]loop
sql
end loop[begin_lable:]
leave lable退出指定标记的循环
iterate lable直接进入下一次循环

游标(查询数据结果)

-声明
declare 名称 cursor for 查询语句;
-打开
open 游标名称;
-获取记录
fetch 名称 into 变量;
-关闭
close 名称;

• 小tips

分号断句
无大小写限制
空格可以使用任意个
– #来注释