MySQL基础语法

目录：

一、服务器操作
  1、MySQL服务的启动和停止。
  2、MySQL服务的登录和退出。
  3、MySQL编码。
  4、mysql常见命令。
二、DQL（数据查询语言）
  2.1、条件查询。
  2.2、常见函数。
  2.3、连接查询。
   select 查询列表 from 表1 别名 inner|left|right|full【outer】 join 表2 别名 on 连接条件 where 筛选条件;
  2.4、分页查询（Limit）。
  2.5、联合查询（union，union all）。
  2.6、子查询。
三、DML（数据操纵语言）
  1、插入。
  2、修改。
  3、删除。
四、DDL语言（数据定义语言）
  ★ 库的管理
  ★ 表的管理
五、事务
六、视图
七、存储过程
八、函数
九、索引

一、服务器操作

1、MySQL服务的启动和停止：

• net start 服务名
• net stop 服务名

2、MySQL服务的登录和退出

• 登录：mysql 【-h 主机名 -P 端口号】 -u 用户名 -p 密码
• 退出：exit或ctrl+C

3、MySQL编码：

• 查看MySQL编码：SHOW VARIABLES LIKE ‘char%’;
• 设置编码格式：SET NAMES utf8

4、mysql常见命令：

1. 查看当前所有的数据库：show databases;
2. 打开指定的库：use 库名;
3. 查看当前库的所有表：show tables from 库名;
4. 查看表结构：desc 表名;
5. 查看服务器版本：
     方式一：登录到mysql服务端：select version();
     方式二：没有登录到mysql服务端：mysql --version或mysql --V

二、DQL（数据查询语言）

1、语法总结：

select 查询列表      ⑦
from 表1 别名       ①
连接类型 join 表2 别名   ③
on 连接条件         ②
where 筛选         ④
group by 分组列表     ⑤
having 筛选         ⑥
order by排序列表     ⑧
limit 起始条目索引，条目数; ⑨

2、DQL详细介绍

  2.1、条件查询。
  2.2、常见函数。
  2.3、连接查询。
   select 查询列表 from 表1 别名 inner|left|right|full【outer】 join 表2 别名 on 连接条件 where 筛选条件;
  2.4、分页查询（Limit）。
  2.5、联合查询（union，union all）。
  2.6、子查询。

三、DML（数据操纵语言）

1、插入

insert into 表名(字段名,…) values(值,…);

2、修改

修改单表的记录
update 表名 set 字段=值,字段=值 【where 筛选条件】;
修改多表的记录
update 表1 别名
left|right|inner join 表2 别名
on 连接条件
set 字段=值,字段=值

3、删除

使用delete（DML）
delete from 表名 【where 筛选条件】【limit 条目数】
使用truncate（DDL）
truncate table 表名

delete和truncate 区别

delete	truncate
DML	DDL
逐条删除	先删除表再创建表
支持事务操作	不支持事务操作,执行效率要高

注：truncate 比 delete速度快，且使用的系统和事务日志资源少。

四、DDL语言（数据定义语言）

★ 库的管理

1、创建库

create database 【if not exists】 库名【 character set 字符集名】;

2、修改库

alter database 库名 character set 字符集名;

3、删除库

drop database 【if exists】 库名;

★ 表的管理

1、创建表

create table 【if not exists】 表名(
  # 创建表时设置自增长列
  字段名 字段类型 【约束】 auto_increment,
  字段名 字段类型 【约束】,
  。。。
  字段名 字段类型 【约束】
)

2、修改表

2.1、添加列

alter table 表名 add column 列名 类型 【first|after 字段名】;

2.2、修改列的类型或约束

alter table 表名 modify column 列名 新类型 【新约束】;

2.3、修改列名

alter table 表名 change column 旧列名 新列名 类型;

2.4、删除列

alter table 表名 drop column 列名;

2.5、修改表名

alter table 表名 rename 【to】 新表名;

3、删除表

drop table【if exists】 表名;

4、约束

create table 表名(
  字段名 字段类型 not null,#非空
  字段名 字段类型 primary key,#主键
  字段名 字段类型 unique,#唯一
  字段名 字段类型 default 值,#默认
  constraint 约束名 foreign key(字段名) references 主表（被引用列）
)
修改表时添加或删除约束
1、非空
添加非空
alter table 表名 modify column 字段名 字段类型 not null;
删除非空
alter table 表名 modify column 字段名 字段类型 ;
2、默认
添加默认
alter table 表名 modify column 字段名 字段类型 default 值;
删除默认
alter table 表名 modify column 字段名 字段类型 ;
3、主键
添加主键
alter table 表名 add【 constraint 约束名】 primary key(字段名);
删除主键
alter table 表名 drop primary key;
4、唯一
添加唯一
alter table 表名 add【 constraint 约束名】 unique(字段名);
查看唯一，找出索引名
show keys from 表名;
删除唯一
alter table 表名 drop index 索引名;
5、外键
添加外键
alter table 表名 add【 constraint 约束名】 foreign key(字段名) references 主表（被引用列）;
删除外键
alter table 表名 drop foreign key 约束名;

关于外键：

插入数据，先插入主表
删除数据，先删除从表
可以通过以下两种方式来删除主表的记录
#方式一：级联删除
ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE CASCADE;
#方式二：级联置空
ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE SET NULL;

5、数据类型
一、数值型
1、整型
tinyint、smallint、mediumint、int/integer、bigint
1    2     3      4      8

特点：
①都可以设置无符号和有符号，默认有符号，通过unsigned设置无符号。
②如果超出了范围，会报out or range异常，插入临界值。
③长度可以不指定，默认会有一个长度。
长度代表显示的最大宽度，如果不够则左边用0填充，但需要搭配zerofill，并且默认变为无符号整型。

2、浮点型
定点数：decimal(M,D)
浮点数:
  float(M,D) 4
  double(M,D) 8
特点：
①M代表整数部位+小数部位的个数，D代表小数部位。
②如果超出范围，则报out orrange异常，并且插入临界值。
③M和D都可以省略，但对于定点数，M默认为10，D默认为0。
④如果精度要求较高，则优先考虑使用定点数。

二、字符型
char、varchar、binary、varbinary、enum、set、text、blob
char：固定长度的字符，写法为char(M)，最大长度不能超过M，其中M可以省略，默认为1。
varchar：可变长度的字符，写法为varchar(M)，最大长度不能超过M，其中M不可以省略。

三、日期型
year年
date日期
time时间
datetime 日期+时间   8
timestamp 日期+时间  4

五、事务

特点（ACID）

A 原子性：一个事务是不可再分割的整体，要么都执行要么都不执行。
C 一致性：一个事务可以使数据从一个一致状态切换到另外一个一致的状态。
I 隔离性：一个事务不受其他事务的干扰，多个事务互相隔离的。
D 持久性：一个事务一旦提交了，则永久的持久化到本地。

隔离级别

	脏读	不可重复读	幻读
read uncommitted:读未提交	×	×	×
read committed：读已提交	√	×	×
repeatable read：可重复读	√	√	×
serializable：串行化	√	√	√

操作
显式开启事务：set autocommit=0;
设置回滚点：savepoint 回滚点名;
结束事务
提交：commit;
回滚：rollback;

六、视图

是对查询结果的一个封装,数据来自于表，一般用于查询。

1、创建或修改

create or replace view 视图名
as
查询语句;

2、删除

drop view 视图1，视图2,…;

3、查看

desc 视图名;
show create view 视图名;

七、存储过程

1、创建

# DELIMITER 定义新的换行符
DELIMITER //
create procedure 存储过程名(参数模式 参数名 参数类型)
begin
  存储过程体;
end//
注意：
1.参数模式：in、out、inout，其中in可以省略
2.存储过程体的每一条sql语句都需要用分号结尾

2、调用

call 存储过程名(实参列表)
举例：
调用in模式的参数：call sp1（‘值’）;
调用out模式的参数：
set @name;
call sp1(@name); #@name是调用后的返回值
select @name; #显示调用后的返回值
调用inout模式的参数：set @name=值; call sp1(@name); select @name;

3、查看

show create procedure 存储过程名;

4、删除

drop procedure 存储过程名;

八、函数

1、创建

create function 函数名(参数名 参数类型) returns 返回类型
begin
  函数体
end
注意：函数体中肯定需要有return语句。

2、调用

select 函数名(实参列表);

3、查看

show create function 函数名;

4、删除

drop function 函数名；

九、索引

1、创建

# 唯一索引、全文索引、空间索引三种不同的索引类型
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX idx_table_col
[USING index_type]
ON table_name(columnname(length),…);

2、修改

–先删除
ALTER TABLE table_name DROP INDEX index_name;
–再以修改后的内容创建同名索引
CREATE INDEX index_name ON user (username(8));

3、删除

ALTER TABLE table_name DROP INDEX index_name;

4、查看索引

SHOW INDEX FROM table_name [FROM db_name];

假如一个表有10万行记录，有一个字段A只有T和F两种值，且每个值的分布概率为50%，那么对这种表A字段建索引一般不会提高数据库的查询速度。
索引的选择性是指索引列中不同值的数目与表中记录数的比。如果一个表中有2000条记录，表索引有1980个不同的值，name这个索引的选择性就是1980/2000=0.99.一个索引的选择性越接近于1，这个索引的效率就越高。

相关优化：

• explain命令
• 不在索引列做任何操作，如计算、类型转换等。
• 尽量不使用!=、is null、or等。
• like不用以通配符%开头，可以放后面，实在无法避免就用覆盖索引。
• 字符串要加引号。
• show profile

---

2、DQL详细介绍：

2.1、条件查询

1、简单条件运算符
< = <> != >= <= <=>安全等于
2、逻辑运算符
&& and
|| or
! not
3、模糊查询
like:一般搭配通配符使用，可以判断字符型或数值型
通配符：%任意多个字符，_任意单个字符
between and
in
is null /is not null：用于判断null值

2.2、常见函数

一、单行函数

1.1、字符函数
distinct:去重
concat:连接
substr:截取子串
upper:变大写
lower：变小写
replace：替换
length：获取字节长度
trim:去前后空格
lpad：左填充
rpad：右填充
instr:获取子串第一次出现的索引

1.2、数学函数
ceil:向上取整
round：四舍五入
mod:取模
floor：向下取整
truncate:截断
rand:获取随机数，返回0-1之间的小数

1.3、日期函数
now：返回当前日期+时间
year:返回年
month：返回月
day:返回日
date_format:将日期转换成字符
curdate:返回当前日期
str_to_date:将字符转换成日期
curtime：返回当前时间
hour:小时
minute:分钟
second：秒
datediff:返回两个日期相差的天数
monthname:以英文形式返回月

二、分组函数

2.1、分类
max 最大值
min 最小值
sum 和
avg 平均值
count 计算个数

2.3、连接查询

• 内连接
  • 等值
  • 非等值
  • 自连接
• 外连接
  • 左外
  • 右外
  • 全外（mysql不支持）
• 交叉连接

内连接

select 查询列表
from 表1 别名
【inner】 join 表2 别名 on 连接条件
where 筛选条件
group by 分组列表
having 分组后的筛选
order by 排序列表
limit 子句;

外连接

select 查询列表
from 表1 别名
left|right|full【outer】 join 表2 别名 on 连接条件
where 筛选条件
group by 分组列表
having 分组后的筛选
order by 排序列表
limit 子句;

注：MySQL不支持全外连接，可用 左外 union 右外 实现。

交叉连接

select 查询列表
from 表1 别名
cross join 表2 别名;

2.4、分页查询

假如要显示的页数为page，每一页条目数为size
select 查询列表
from 表
limit (page-1)*size,size;

2.5、联合查询

union 去重，union all包含重复项。

2.6、子查询

出现的位置：
  * where后作为条件存在
  * from后作为表存在(多行多列)