MySQL语法
MySQL介绍
-
数据库（DataBase，简称DB）：据库是存放数据的仓库，它的存储空间很大，可以存放百万条、千万条、上亿条数据。因此，开发中只要涉及到数据，就一定会用到数据库；
-
数据库管理系统（Data Base Manage System，简称DBMS）：数据库管理系统是一种操纵和管理数据库的大型软件，用于建立、使用和维护数据库。在很多地方，数据库管理系统被简称为数据库，实际上是两个不同的概念，不要混淆。因此，使用缩写会更好。
-
关系型DBMS的分类
- 市面上使用的DBMS主要分为两类：关系型（SQL）、非关系型（NoSQL），关系型数据库的市场份额远大于非关系型；
- Oracle：Oracle是一种企业级的关系型数据库管理系统，具有强大的数据处理能力和高可靠性，适用于大型企业和关键业务系统；
- MySQL：MySQL是由瑞典MySQL AB公司开发（后来被Oracle公司收购）的一种开源SQL数据库管理系统，它提供了丰富的功能和良好的性能，并且支持大量的操作系统和编程语言，因此广受开发者的欢迎；
- Microsoft SQL Server：Microsoft SQL Server是微软公司出品的一款关系型数据库管理系统，它集成了高效的数据处理引擎和强大的商业智能功能，广泛应用于各种规模的企业级应用中；
- PostgreSQL：PostgreSQL是一款开源的对象-关系型数据库管理系统，它具有高度的可扩展性和灵活性，并且支持复杂的查询语言和存储过程；
- DB2：DB2则是一种面向企业级应用的关系型数据库管理系统，它拥有强大的数据安全性和稳定性，常被用于处理大量的关键业务数据；
- 目前，个人及中小型企业大部分使用MySQL（免费、功能强大）。
-
MySQL结构：MySQL和其他关系型数据库一样，由二维表格模型组成，每张数据表都由若干行和列组成，一行成为一个记录，一列称为一个字段（或者属性）
-
MySQL安装
-
下载MySQL社区版安装程序（https://dev.mysql.com/downloads/installer/）
-
选择安装类型
-
选择安装路径
-
默认选择所有组件（点击Execute，稍等片刻）
-
配置信息，直接next
-
网络配置，直接next
-
选择默认的，直接next
-
为默认用户root创建密码，输入之后check成功，点击next
-
在windows上注册MySQL服务
-
直接next
-
直接Execute
-
点击Cancel
-
在命令行中，启动和停止MySQL服务
- 启动：net start mysql80 - 停止：net stop mysql80 -
在命令行中，连接上MySQL
- mysql -u root -p - 回车之后输入root用户的密码
-
-
MySQL规范
- MySQL不区分大小写，但是关键字建议使用大写；
- MySQL语句以分号结尾
基础
- SQL语言是一种结构化查询语言，用于存取数据以及查询、更新和管理关系数据库系统 。SQL语言包括数据定义语言（DDL）、数据操作语言（DML）、数据查询语言（DQL）、数据控制语言（DCL）等部分。
- use db_name; 进入db_name数据库（在操作表/数据之前，必须进入数据库）
- MySQL的数据类型与其他编程语言类似有很多，最常用的有两个：整型（int ）、字符型（varchar(length)）
- 注释：单行注释（# 或者 --空格），多行注释（/**/）
- 点号：A数据库中的B数据表中的C字段，可表示为 A.B.C
DDL
Data Defination Language，数据定义语言。主要用于数据库结构和表结构的定义，有四个关键字：create、drop、alter、truncate。
查询
show databases; #查询MySQL管理的所有数据库
show tables; #查询当前数据库中的所有表
select database(); #查询当前使用的数据库
desc 表名; #查询当前表的表结构
show create table 表名; #查询当前表的建表语句
CREATE
-
作用：用于创建结构（创建一个数据库、创建一张表）；
-
语法
- 创建数据库 create database [if not exists] 数据库; 如果不存在，则创建数据库 - 创建表结构 create table [if not exists] 表名(字段1 类型 [约束], 字段2 类型 [约束], ..., 字段n 类型 [约束]); -
演示
# 创建一个数据库，名称为 School create database if not exists School; # 创建一张表，名称为 Students，字段为id, name, age, sex create table if not exists Students(id int, name varchar(20), age int, sex varchar(1)); /* if not exists 的作用在于，如果这个东西（数据库/表）已经存在，再次创建不会报错，只会警告；如果省略了if not exists语句，就会报错，因此使用create创建结构时，推荐加上 if not exists */ # 再次创建Students表，感受 if not exists作用 create table if not exists Students; #不会报错 create table Students; #报错
DROP
-
作用：删除数据库或表
-
语法
# 删除表 drop table [if not exists] 表名; # 删除数据库 drop database [if not exists] 数据库; -
演示
/*
DB: Users
Table: infos(id, name, age, sex)
*/
# 删除表
drop table if not exists infos;
# 删除数据库
drop database if not exists users;
TRUNCATE
-
作用：清空表中的所有数据（只作用于表）
-
语法
truncate table 表名; -
演示
/*
DB: Users
Table: infos(id, name, age, sex)
*/
truncat table infos;
ALTER
-
作用：修改表结构
-
语法
#重命名表 alter table 原来的表名 rename to 新的表名; #添加一个新的字段 alter table 表 add 字段 类型 [comment] [约束条件]; #删除字段 alter table 表 drop 字段; #修改字段类型 alter table 表 modify 字段 类型; #修改字段的名称及类型 alter table 表 change 原来的字段名 新的字段名 类型; -
演示
/* DB: Users Table: infos(id, name, age, sex) */ #重命名表 alter table infos rename to informations; #添加一个新的字段 alter table informations add salaries int [comment"工资"]; #删除字段 alter table informations drop salaries; #修改字段类型 alter table informations modify name varchar(15); #修改字段的名称及类型 alter table informations change sex gender varchar(1);
DML
-
Data Manipulation Language，数据操纵语言。对表中的数据进行增删改的操作。
-
语法
#增加（insert into...value...）：value后面的一个括号表示一个记录 insert into 表(字段1,字段2, ..., 字段n) value (value1,value2, ..., valuen), (value1,value2, ..., valuen), ..., (value1,value2, ..., valuen); #删除（delete from...where...） delete from 表 where条件 #修改（update...set...where...） update 表 set 字段=值 [where条件]; -
演示
/* DB: Users Table: infos(id, name, age, sex) */ #增加 insert into infos(name,age,sex) value ("BrankYeen",20,"男"),("尹少欣",21,"男"),("李雪",22,"女");#添加了三个记录 #删除 delete from infos where name="尹少欣"; #删除name="尹少欣"这行记录 #修改 update infos set age=18 where name="李雪";
DQL
-
Data Query Language，数据查询语言。用于查询表中的数据。
-
语法
select column_name from table_name [where条件] [group by分组 having条件] [order by排序] [limit分页]
条件查询
-
select 字段 from 表 where条件：从表中搜索满足条件的数据
-
比较运算符：>、>=、<、<=、<>/!=、between...and...、in、like、is null。如果为真，返回1；如果为假，返回0。
-
逻辑运算符：and/&&、or/||、not/!。如果为真，返回1；如果为假，返回0。
-
演示
select 1>2; select 3<>4; select id between 4 and 10 from students; #查询[4,10]的id字段 select name from students where id in (1,2,3,5,7); #在students表中，从id为1或2或3或5或7的记录中，查询name字段 select name from students where name like '%e%'; #在students表中，搜索name字段中含有字母"e"的记录，其中%表示任意多个字符
别名
-
如果在一个SQL语句中，多次使用表名或字段，且名称较长，此时可以使用别名进行简化；
-
注意，起别名之后，只能使用该别名，不能再使用原来的名字；
-
语法
字段/表 [as] 别名 -
演示
/* DB: Users Table: infos(id, name, age, sex) */ #搜索info表中所有女性的姓名 select name from infos as i where i.sex='女';
分组
-
配合聚合函数，利用分组信息进行统计，后面可以通过having条件进行筛选过滤；
-
聚合函数
/* DB: Users Table: infos Column: id, name, age, sex */ #sum()：求和 select sum(id) from infos; #avg()：求平均 select avg(id) from infos; #max()：最大值 select max(id) from infos; #min()：最小值 select min(id) from infos; #count()：求个数 select count(id) from infos;
排序
-
对表中的某一字段进行升序或降序后，搜索数据数据；
-
语法
#升序 order by [asc]; #降序 order by desc; -
演示
/* DB: Users Table: infos(id, name, age, sex) */ #搜索infos表中name字段的数据，并按照age字段进行升序排列 select name from infos order by age asc; #搜索infos表中name字段的数据，型按照age字段进行降序排列 select nmae from infos order by id desc;
分页
-
如果查询到的结果很多，可以进行分页展示，一次只展示某几个数据；
-
语法
select 字段 from 表 from limit start,length #范围是[start,start+length)，索引从0开始 -
演示
/* DB: Users Table: infos(id, name, age, sex) */ #搜索info表中的name字段，展示0、1、2这三条数据 select name from infos limit 0,3; #搜索info表中的name字段，展示3、4、5这三条数据 select name from infos limit 3,3;
DCL
-
Data Control Language，数据控制语言。用于管理数据库的用户，DBA（数据库管理员）用的较多；
-
语法
#查询用户 select * from mysql.users; #创建用户 create user '用户名'@'主机名' identified by '密码'; #修改密码 alter user '用户名'@'主机名' identified with '旧密码' by '新密码'; #删除用户 drop user '用户名'@'主机名'; -
演示
#查询用户 select * from mysql.users; #创建用户：用户名-brankyeen，主机-localhost，密码-123.com create user 'brankyeen'@'localhost' identified by '123.com'; #将密码修改为administrator alter user 'brankyeen'@'lccalhost' identified with '123.com' by 'administrator'; #删除brankyeen drop user 'brankyeen'@'localhost'; -
权限控制
all/all privileges #所有权限 select #查询权限 insert #添加数据权限 update #修改数据权限 delete #删除数据权限 alter #修改结构权限 drop #删除结构权限 create #创建结构权限 #查询用户的权限信息 show grants for '用户名'@'主机名'; #授予权限 grant 权限列表 on 数据库.表 to '用户名'@'主机名'; #撤销权限 revoke 权限列表 on 数据库.表 from '用户名'@'主机名';#查看brankyeen拥有的所有权限 show grants for 'brankyeen'@'localhost'; #将所有表的查询、添加数据权限授予给brankyeen grant select,insert on *.* to 'brankyeen'@'localhost'; #撤销brankyeen所有的权限 revoke all on *.* from 'brankyeen'@'localhost';
函数
字符串函数
# concat(str1, str2, ..., strn) 将多个字符串拼接成一个字符串
select concat('Hello', 'MySQL', 'DataBase');
#lower(str)将字符串全部转为小写，upper(str)将字符串全部转为大写
select lower('HElL0');
select upper('MySQL');
#lpad(str,n,character)左填充character字符，rpad(str,n,character)又填充character
select lapd('BrankYeen', 10, '@');
select rpad('尹少欣', 10, '#');
#trim(str)去除字符串前后的空格
select strim(' Hello Mysql ');
#substring(str,start,len) 从start位置开始往后截取len个字符
select substring("I Love BrankYeen", 3, 5);
数值函数
#ceil()向上取整 floor()向下取整
select ceil(1.2);
select floor(2.1);
#mod(x,y) x%y
select mod(11,2);
#rand() 0~1之间的随机数
select floor(rand()*2);
#round() 四舍五入并保留小数位数
select round(3.1415926,3);
日期函数
#curdate()当前日期 curtime()当前时间 noe()当前日期及时间
select curdate();
select curtime();
select now();
#year()年份 month()月份 day()天数
select year(curdate());
select month(curdate());
select day(curdate());
#date_add()
select date_add(curdate(),interval 30 day);
#datediff() 间隔时间
select datediff(curdate(),'1945-10-1');
流程函数
#if(exp, v1, v2) 如果exp的值为真，返回v1，否则返回v2
select if(1+1=2, '真', '假');
#ifnull(exp, v) 如果exp的值为null，返回v，否则返回exp
select ifnull(id, "有空值") from students;
#case [exp] when v1 then r1 else r2 end; 如果exp的值为v1，返回r1，否则返回r2
select case name when "BrankYeen" then "好帅" else "还行" end from students;
约束
-
作用于表中的字段，用来限制规定字段应满足的条件，一般在创建表结构时使用；
-
语法
/* * not null 非空，该字段中的数据不能为空 * unique 唯一，该字段中的数据不能重复 * primary key 主键，该字段中的数据不能为空，且数据不能重复（primary key = not null + unique） * default 默认，该字段中的默认值 * check 检查，可以用来自定义条件（8.0.16版本之后） * foreign key 外键，该字段中的值是另外一张表中的某一字段（一般是主键） *//* * 创建users表 * id int类型 主键约束 自增 * name varchar类型 非空约束 * age int类型 非空约束 * sex varchar类型 sex的值只能是'男'或'女' * phone varchar类型 唯一约束 默认值为null */ create table if not exsits users(id int primary key auto_increment, name varchar(20) not null, age int(3) not null, sex varchar(1) check(sex='男' or sex='女'), phone varchar(11) unique default null); -
外键约束
/* * 添加外键：alter table 表 add constratint 名称 foreign key 本表字段 references 父表字段 * 删除外键：alter table 表 drop 名称 */ #外键行为 /* * no action 不允许删除和修改被关联的字段 * restrict 不允许删除和修改被关联的字段 * cascade 可以删除和修改被关联的字段，并实时更新子表中的值 * set null 可以删除和修改被关联的字段，并将子表中的值设置为null * set default 可以删除和修改被关联的字段，并将子表中的值设置为默认值 * 添加外键行为：alter table 表 add constraint 名称 foreign key 本表字段 references 父表字段 on update 外键行为 on delete 外键行为 */#创建users(id,type,identity_type) create table if not exists identity(id int primary key auto_increment, type varchar(20),identity_type int default 3); #将users表的identity_type与identity表的id字段关联 alter table users add constraint fk_iid foreign key(identity_type) references identity(id); #删除fk_iid外键 alter table users drop fk_iid; #添加fk_iid外键，并可以删除和修改父表中被关联的字段 alter table users add constraint fk_iid foreign key(identity_type) references identity(id) on update cascade on delete cascade;
多表查询
多表关系
- 一对一：一张父表对应一张子表，常常用来将一张很大的表拆分成两张表，提高查询速率；
- 一对多：一张父表对应多张子表，应用场景类似于一个部门对应多个员工的情况；
- 多对多：一张父表对应多张子表，一张子表关联着多张父表，应用场景类似于一个学生对应多门课程和多门课程对应多个学生的场景。
多表查询
-
在一次查询中，从多张表里搜索数据；
-
语法
# select ... from table1, table2, ..., tablen; # 类似于循环嵌套，从table1里面查询一行记录，会table2里面查询所有记录，这种现象叫做”笛卡尔积“ /* for record1 in table1: print(record1) for record2 in table2: print(record2) ... */ -
连接查询（在逻辑上，将多张表合并成为一张表进行查询，合并的条件可以进行指定）
#内连接：查询多张表的交集部分 /* * 隐式：select ... from table1,table2 where... * 显式：select ... from table1 inner join table2 on 条件 */ select emp.name,dept.name from emp,dept where emp.id=dept.id; #外连接：将某张表中不满足条件的记录也查询出来 /* * 左外连接（将左表中不满足条件的记录也查询出来）：select ... from table1 left outer join table2 on 条件 * 右外连接（将右表中不满足条件的记录也查询出来）：select ... from table1 right outer join table2 on 条件 */ -
联合查询（将多个查询结果合并成为一个新的结果集）
#select ... from table1 union [all] select ... from table2; union all不去重，union 去重 #注意：每个单独的select语句返回的结果中，字段数和字段类型必须一致 select name from students union select subject from courses; -
子查询（嵌套查询）
/* * DB:School * Table:students(id,name,age,sex,subject,cid),courses(id,subject,teacher),classes(id,class) */ #标量子查询：子查询语句返回的结果是一个标量，即单个值（数字、日期等） select class from classes where id=(select cid from students where name="BrankYeen"); #列子查询：子查询返回的结果是一列数据 select name from students where subject in (select subject from courses where id between 2 and 7); #行子查询：子查询返回的结果是一行记录 select name from students where subject=(select subject from students where name="BrankYeen"); #表子查询：子查询返回的结果是一张表
事务
-
对数据库进行多个操作时，可以将这这几个操作放到一个事务当中，只有当它们全部正确时才会提交给数据库一起执行，一旦有任何错误所有操作都不会执行（类似于用git条文件至仓库）；
-
步骤：开启事务、添加任务、提交事务（MySQL自动提交）、回滚事务
-
语法
#开启事务 start transaction; #或者 start begin; #添加任务 insert into users(name,age,sex) value("BrankYeen", 20, '男'); #把注释打开，MySQL会报错，如果没有事务，insert语句会执行成功且update不会被执行；如果开启了事务，那么两条语句都不会被执行 update users set age=18 where name="BrankYeen"; #提交事务 commit; #回滚事务(如果任务出错，撤销所有操作) rollback; -
四大特性（ACID）
- 原子性（Atomicity）：要么所有的操作都执行，要么都不执行；
- 一致性（Consistency）：
- 隔离性（Isolation）：如果有多个事务同时操作同一个数据库/表，互不影响（这也和git非常相似），不过要看MySQL设置的隔离级别；
- 持久性（Durability）：一旦提交成功，结果将会被直接写入到磁盘当中，而不只是缓存在内存中；
-
并发事务产生的问题（隔离级别低了）
- 脏读：事务A读取到了事务B还没有提交过来的数据；
- 不可重复读：受到事务B的影响，事务A前后读取同一记录，得到不同的结果；
- 幻读：受到事务B的影响，事务A读取不到事务B已经提交的数据；
-
事务隔离级别
- read uncommitted：不能解决任何问题，性能最高
- read commited：只解决脏读，性能第二
- repeated read：只解决幻读，性能第三
- serializable：解决了所有事务问题，性能最低
#查看当前的隔离级别 select @@transaction_isolation; #设置隔离级别 set session transaction isolation level 隔离级别;
浙公网安备 33010602011771号