Mysql查询、增删改语法，以及常见函数和命令

MySQL查询语法

一、基础查询

# 查询语句：
select 查询列表 from 表名
# 查询列表可以是：字段名、函数、变量、常量
select 字段名1,字段名2,字段名3 from 表名;
select 100*3;
select Database();
select Version();
select User();
select 字段名1*3 from 表名;

# 起别名查询，使用 as
select name as '用户姓名' from employees;
# 起别名查询，使用 空格
select name '用户姓名' from employees;

# concat() 字符函数，拼接字符串
select concat(first_name,last_name) as '姓名' from employees;
# MySQL中 + 号的作用，加法运算
# 两个操作数都是数值类型
	100+1.5
# 其中一个操作数是字符数据类型，将字符数据类型转换成数值类型，如果无法转换，则直接当做0处理。
	'name' + 100 ===> 100
# 其中一个操作数为null
	null+null ===> null
	null+100 ===> null

# 去重distinct
select distinct 字段名 from 表名;

# 查看表结构
desc 表名;
show columns from 表名;

# ifnull(表达式1,表达式2)
# 表达式1：可能为null的字段或者表达式
# 表达式2：如果表达式1为null则显示表达式2，否则显示表达式1
select 字段1,ifnull(字段1,'空') from 表名;

二、条件查询

select 查询列表 from 表名 where 筛选条件;
# 筛选条件，关系运算符、逻辑运算符、模糊查询（一般搭配通配符 _ % 使用）
# 关系运算符查询
	> < >= <= = <>（不等于）
# 逻辑运算符
	or and  not
# 模糊查询（一般搭配通配符 _ % 使用）
	like/not like
	# 常见通配符 _ 任意单个字符、% 任意多个字符，_ 和 % 可以同时使用。
	# MySQL中使用 \_ 表示 _
	select * from employees where last_name like '_\_%';
	in/not in
	# 查询某个字段的值是否在指定的列表之内
	in(常量值1,常量值2,常量值3,........)
	not in(常量值1,常量值2,常量值3,........)
	# in列表的值类型必须一致或者兼容
	# in列表不支持通配符
	between and/not between and
	# 判断某个字段的值是否处于 xx 之间，包含临界值
	is null/ is not null
	# 判断某一个字段是否是空值
	select * from 表名 where 字段1 is null;
# 对比
	= # 只能判断普通的内容，不能判断null值
	is # 只能判断null值
	<=> # 安全等于，既能判断普通内容，又能判断null值	

三、排序查询

select 查询列表 from 表名 where 字段 筛选条件 order by 字段 排序列表;
# 排序列表可以是单个字段、多个字段、表达式、函数、列表，以及以上的组合
asc # 升序
desc # 降序

四、分组查询

select 查询列表 from 表名 where 筛选条件 group  by 分组列表  having 分组后的筛选 order by 排序列表;
# 查询列表通常是分组函数和被分组的字段。
# 分组查询中的筛选分为两类
					筛选的基表			使用的关键字			位置
	分组前筛选		原始表				where				group by 之前
	分组后筛选		分组后的结果集		having				group by 之后
# 分组函数做筛选条件，只能放在having后面，不能放在where后面
# 按照多个字段分组的写法
	select * from 表名 group by 字段名1,字段名2;
# 和分组函数一同查询的字段必须是 group by后面出现的字段	
# 能用分组前筛选，尽量用分组前的筛选。

五、连接查询

SQL99内连接查询

查询两个表之间共有的数据，不分主从表查询。

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

SQL99外连接查询

外连接查询分为主从表，两表的顺序不能任意调换。通常以主表为主要的查询条件去查询另一个表中的数据；适用于主表中有，但从表中没有的记录。

# 左外连接查询，left  join 左边为主表
select 查询列表 from 表名1 别名 left join 表名2 别名 on 连接条件 where 筛选条件 group  by 分组列表 having 分组后的筛选 order by 排序列表;
# 右外连接查询，right  join 右边为主表
select 查询列表 from 表名1 别名 right join 表名2 别名 on 连接条件 where 筛选条件 group  by 分组列表 having 分组后的筛选 order by 排序列表;
# 全外连接查询
select 查询列表 from 表名1 别名 full join 表名2 别名 on 连接条件 where 筛选条件 group  by 分组列表 having 分组后的筛选 order by 排序列表;

六、子查询

当一个查询语句中嵌套了另外一个完整的select语句，则被嵌套的select语句被称为子查询或者内查询，外面的查询被称为主查询或者外查询。

# 按照子查询的位置分类
select 后面
	# 要求子查询的结果为单行单列
	select (select count(*) from employess where department_id = 50);
from 后面
	子查询的结果可以是多行多列
	
where 或者 having 后面
	子查询的结果必须是单列
		# 多行子查询（多行子查询对应了多行操作符 any/some all in）
			in 等于列表中的任意一个（not in 不等于）
			any/some 和子表返回的某一个值比较
			all 和子查询返回的所有值比较
		# 单行子查询（单行子查询对应了单行操作符 > < = >= <= <>）
exists 后面
	# 子查询结果必须是单列		
	select exists (select * from employees where last_name = '张三丰');		

七、分页查询

select 查询列表 from 表1 别名 join 表2 别名 on 连接条件 where 筛选条件 group by 分组 having 分组后筛选 order by 排序列表 limit 起始索引,每页显示数据条目数;

八、联合查询

查询结果来自多张表，多张表之间没有关联关系，这个时候使用联合查询，也称为union查询。

select 查询列表 from 表1 where 筛选条件
	union
select 查询列表 from 表2 where 筛选条件	

MySQL增删改语法

数据插入

# 插入单行数据
insert into 表名 (字段名1,字段名2,........) values (值1,值2,.......);
# 插入多行数据,Oracle不允许以此种方式插入数据
insert into 表名  (字段名1,字段名2,........) values (值1,值2,.......),(值1,值2,.......);

数据修改

update 表名 set 字段名=新值,字段名=新值,....... where 筛选条件;

数据删除

# delete 语句
delete from 表名 where 筛选条件;
# truncate 语句
truncate table 表名;
# delete 与  truncate 的区别
delete 会返回受影响行数，搭配where筛选条件使用，支持事务回滚。
truncate 不会返回受影响行数，不能搭配where筛选条件使用，不支持事务回滚。

MySQL常用函数

单行函数

concat() 拼接字符串
substr() 截取字符串
length() 获取字节长度
char_length()获取字符个数
upper() 变大写
lower()变小写
trim() 去字符串前后的指定字符，默认是空格
left() 截取子串
right() 截取子串
lpad() 左填充
rpad() 右填充
instr()

MySQL常见命令

# MySQL服务启动与停止
net start 服务名
net stop 服务名
# MySQL的登录与退出
mysql -hIP地址 -P端口号 -u用户名 -p密码
exit
# 查询当前所有的数据库
show databases;
# 使用指定的库
use 数据库名;
# 查看当前数据库的所有表
show tables;
# 查看其它数据库的所有表
show tables from 库名;
# 创建表
create table 表名;
# 查看表结构
desc 表名;
# 创建数据库
create database 库名;
# 导入SQL文件
source sql文件路径;
# 删除MySQL库
drop 库名;
# 查看MySQL服务器密码，修改用户密码
select *  from mysql.user;
update user set password=PASSWORD('root') where user='root';
# 刷新权限
flush privileges;
# 查看端口号
show global variables like 'port';
# 查看服务器版本
select version();
# 查看MySQL事务隔离级别
select @@tx_isolation;
# 设置MySQL事务隔离级别（global表示全局）
set session | global transaction isolation level 隔离级别;
# 查看MySQL数据库支持的数据库存储引擎和当前数据库存储引擎
show engines;
# 查看数据库事务隔离级别是否开启
show veriables like 'autocommit';