mysql学习

显示数据库中的表:

show tables;
或
show  tables  from  库名;

显示当前使用的数据库:
select database();

显示表结构:
desc 表名;

显示当前数据库版本:
select version();
或退出mysql管理系统后输入:
mysql --version
mysql -V

注释:
单行注释: #注释文字 或 – 注释文字(注意–后面有个空格)
多行注释: /* 注释文字 */

DQL:查
DML:增删改
DDL:定义语言
TCL:事务控制语言

DQL部分(查):

1.基础查询
select 查询列表 from 表名;
查询列表可以是: 表中的字段\常量值\表达式\函数
标识字段的时候可以用着重号 `
select * from 表名;
select 100;
select ‘john’;
select version();
select 100%98;
select 100%98 as 别名; (使用别名便于理解,查询字段有重名的情况时,使用别名可以区分开来)
select 100%98 别名;(省略了as关键字)
select salarty as “out put” from employee;
注:如果别名比较特殊的时候,别名需要添加双引号或单引号.

去重: select distinct department_id from employee; (在需要查询的字段前面加distinct关键字)
select “10” + “20”; 结果为30,mysql会试着将字符串转换成数字,如果转换成功,则进行运算.
select “john” + 100; 会将字符串转换成0从而参加运算.
select null + 100;只要其中一个为null,则结果为null.

concat 连接: select concat(lastname,firstname) as 姓名 from employee;

ifnull(表达式1,表达式2); 查询存在null时,使用这个,有点像三目运算符.

2.条件查询
语法:
select 查询列表 from 表名 where 筛选条件;(执行顺序为:先from 后筛选条件,最后查询列表)

分类:
一 按条件表达式筛选
条件运算符:
> < = != <> >= <=

二 按逻辑表达式筛选
作用:用于连接条件表达式
逻辑运算符:
&& || !
and or not

三 模糊查询
like
①一般和通配符搭配使用
%:任意多个字符,包含0个字符
:任意单个字符
select last_name,salary from employees where last_name like ‘__n_l%’;
注意:需要使用特殊字符的时候,需要使用转义字符,如 _ 将下划线转义成普通的下划线,而不是通配符.
同时也可以通过escape 指定转义字符
select last_name,salary from employees where last_name like 'KaTeX parse error: Double subscript at position 3: _n_̲l%' escape '’;
(上面就制定了$为转义字符)

between and
①可以提高语句的简洁度;
②包含临界值
③两个临界值的顺序不能颠倒,不然查询结果是不一样的.

in
判断某字段的值是否属于in列表中的一项
select * from 表 in(值1,值2,值3);
特点:
①比使用or更加的简单;
②in列表的值类型必须统一或兼容;
③值1,值2,值3中不支持通配符.

is null及 is not null
注:因为=或 <>不能判断null.
select last_name ,commission_pct from employee where commission is null;

安全等于:<=>
<=>: 安全等于可以判断null值,同时也可以像简单的**=**一样使用.
is null :仅仅可以判断null值.

3.排序查询

select 查询列表 from 表 where 筛选条件 order by 排序列表 asc|desc;
asc:升序,默认就是升序.
desc:降序
特点:
①order by 后面同样可以使用别名,函数,多个字段,单个字段.
②length(字段名)函数 :得到字段长度
③支持按多个字段进行排序:order by salary asc,employee_id desc;先按谁,谁将写在前面.
④order by 子句一般放在查询语句的最后面,除了limit子除外.

**

4.常见函数

概念:类似于Java中的方法,将一组逻辑语句封装在方法体中,对外暴露方法名,
好处:①隐藏实现细节 ②提高代码的重用性
调用: select 函数名(实参列表) [from 表];

分类:

1.单行行数
如:concat() length() ifnull() 等
字符函数
length(str) :获取参数的字节个数 length(‘john’);
concat(str,str):拼接字符串
upper(str):将参数转成大写
lower(str):将参数转成小写
substr()或substring(): 截取字符(注意sql中索引从1开始)
instr(str,substr):得到substr在str中的第一次出现的索引,没找到则返回0.
trim(str):去掉字符两边的空格.
trim(‘a’ from str):去掉str两边的a.
lpad(str,10,’%’):在str的左边填充(10-str的长度)个百分号号,所以指定的数量一定要大于str的长度,不然就会是截断现有的str.
rpad(str,10,’%’)在str的右边填充(10-str的长度)个百分号号.
replace(str,str1,str2):将str中的str1全部替换为str2.

数学函数

round(数值):四舍五入
round(数值1,数值2):数值1四舍五入,保留数值2个小数.
ceil(数值):向上取整,返回>=该参数的最小整数.
floor(数值):向下取整,返回<=该参数的最大整数.
truncate(数值1,数值2):截断,数值1保留数值2个小数,不实行四舍五入.
mod(数1,数2):取余.相当数1%数2,也是 a - (a/b) b 比如:mod(-10,-3) -10-(-10/-3)(-3)

日期函数
now():返回当前系统日期+时间
curdate():返回当前系统日期,不包含时间.
curtime():返回时间,不包含日期.
获取指定的年月日
year(now())或year(‘1998-1-1’)或year(某个字段):获取年
month(now()):获取月
monthname(now()):获取英文的月.
str_to_date(str,格式):将字符通过指定的格式转成日期
date_format(日期,格式):

%Y	4位的年份
%y	2位的年份
%m	月份(01,02…11,12 也就是会自动补全)
–	–
%c	月份(1,2,3…11,12) 不会自动补全
%d	日(01,02,…)
–	–
%H	小时(24小时制)
%h	小时(12小时制)
–	–
%i	分钟(00,01,…59)
–	–
%s	秒(00,01,…59)

date_format:将日期转换成字符
date_format(‘2018/6/6’,’%Y年%m月%d日’)

其他函数
version():查看当前数据库管理软件的版本
database():查看正在使用的数据库
user():查看当前账户.

流程控制函数
if函数:类似java中if …else…
select if(条件表达式,参数1,参数2); 如果条件表达式为true,则返回参数1,否则返回参数2.

case函数:
使用1:类似java中的switch case 的效果(更加适合等值的判断)
语法:
case 要判断的变量或表达式
when 常量1 then 要显示的值1或语句1;
when 常量2 then 要显示的值2或语句2;
…
else 要显示的值n或语句;
end

例子:查询员工工资,如果部门号为30,则工资为原工资的1.1倍,如果部门号为40,则工资为原工资的1.2倍.
select salary 原始工资,department_id,
cast department_id
when 30 then salary1.1
when 40 then salary1.2
when 50 then salary*1.3
else salary
end as 新工资
from employees;

使用2: case 函数 类似多重if(更加适合条件判断)
case
when 条件1 then 要显示的值1或语句1
when 条件2 then 要显示的值2或语句2
…
else 要显示的值n或语句n;
end

例子:查询员工的工资情况(如果员工工资>20000,则显示A级别…)
select salary,
case
when salary > 2000 then ‘a’
when salary > 1000 then ‘b’
when salary > 500 then ‘c’
else ‘d’
end as 工资级别
from employee;

5.分组函数

功能:做统计使用,又称为统计函数\聚合函数\组函数

分类

sum 求和
avg 平均值
max 最大值
min 最小值
count 计算个数

1.简单实用
select sum(salary) from employees;
select avg(salary) from employees;
select min(salary) from employees;
select max(salary) from employees;
select count(salary) from employees;

2.参数支持哪些类型
sum() 和 avg() 一般用于处理数值型
max() min() count() 可以处理任何类型

3.是否忽略null(null + 任何值等于null)
sum() avg() max() min() 是忽略null的;
count() 计算非null的个数;
即:所有的分组函数都忽略null值,即不参加运算.

4.和distinct搭配使用(所有的分组函数都可以搭配使用,也就是去重功能)
select sum(distinct salary), sum(salary) from employees;
select count(distinct salary), count(salary) from emploees;

5.count函数详细介绍
select count(salary) from employees;
select count (星号) from employees;
select count(1) from employees; 相当于个数据表多加了一列,这一列全部为1.
效率:
MYISAM存储引擎下,count(星号)的效率高;
innodb存储引擎下,count(星号) 和count(1)效率差不多.count(字段)效率最低

6.和分组函数一同查询的字段有限制,一般要求是group by 后的字段,使查询出来的表要有意义.

datediff(日期1,日期2):得到两个日期相差的天数

6.分组查询

语法:
select 分组函数,列(要求出现在group by 的后面)
from 表
[where 筛选条件]
group by 分组的列表
[order by 子句]

注意:查询列表比较特殊,要求是分组函数和group by后出现的字段

1. 使用where还是having主要根据筛选条件是原始表中的还是查询后表中的条件,如果是原始表中的条件,则可以使用where,否则使用having.
2. 分组函数(统计函数 聚合函数)做条件肯定是放在having子句中
3. 能用分组前筛选的,就优先考虑使用分组前筛选,性能更好.
4. group by子句支持单个字段分组,也支持多个字段分组(多个字段之间用逗号隔开,没有顺序要求),表达式和函数也可以(使用较少)
5. group by 和 having也支持使用别名

7.连接查询

含义:多表查询,当查询的字段来自多个表时,就会使用连接查询
笛卡尔乘积现象:表1 有m行 表2 有n行 结果为m x n 行
出现错误的原因:没有有效的连接条件
如何避免:添加有效的连接条件

分类:
按年代分类
sql92标准:仅仅支持内连接
1 等值连接

1. 多表等值连接的结果为多表的交集部分

2. n表连接,至少需要n-1个连接条件

3. 多表的顺序没有要求

4. 一般需要为表起别名

5. 可以搭配前面介绍的所有子句使用,比如排序\分组\筛选.

   sql99标准(推荐):支持内连接 + 外连接 (不支持全外连接)+ 交叉连接
   

按功能分类
内连接:
等值连接
非等值连接
自连接
外连接:
左外连接
右外连接
全外连接

交叉连接:

如果为表取了别名,则查询的字段不能用原来的表名去限定.

8.子查询

9.分页查询

10.union联合查询

将多条查询语句的结果合并成一个结果

语法

    查询语句1
    union
    查询语句2
    union
    ...;

select * from  employees where email like '%a' or department_id>90;
//上面的语句使用联合查询后
select * from employees where email like '%a%'
union
select * from employees where department_id>90;

应用场景:要查询的结果来自多个表,且多个表没有直接的连接关系,但查询的信息一致时(比如都是查询id,name,sex).

特点:

1. 要求多条查询语句查询的列数是一致的.
2. 要求多条查询语句查询每一列的类型和顺序是一致的.
3. 使用union进行查询时会自动去重,如果不想去重则使用union all 关键字.

DML语言(数据操作语言)

插入

语法1:
insert into 表名(列名,…) values (值1,…),(值,…),(值,…);
语法2:
insert into 表名 set 列名=值, 列名=值,…;

语法1 PK 语法2:
语法1执行插入多行,语法2不支持
insert into beauty values(1,‘娜扎’,‘1898888’),(2,‘热巴’,‘12333’);

语法1支持子查询,语法2不支持
insert into beauty(id,name,phone) select 26,‘宋茜’,‘1389999’);

要求:插入值的类型与列的类型一致或兼容;

修改

修改单表的记录

update  表名  set  列=新值,列=新值,列=值,...   where   筛选条件

修改多表的记录

sql92语法:
update 表1 别名,表2 别名
set 列=值,…
where 连接条件
and 筛选条件;

sql99语法:
update 表1 别名
inner | left | right join 表2 别名
on 连接条件
set 列=值,…
where 筛选条件;

删除

语法1:
单表的删除

// 以行为单位进行删除, 注意:如果不加where条件,则删除的是整个表
delete from 表 where  筛选条件;  

多表的删除
sql92语法:
delete 表1的别名,表2的别名
from 表1 别名,表2别名
where 连接条件
and 筛选条件;

sql99语法:
//如果只想删除关联起来的表中的一张表里面的相关记录,则在delete后面只写该表的别名,不删除的则不用写.
delete 表1的别名,表2的别名
from 表1 别名
inner | left | right join 表2 别名 on 连接条件
where 筛选条件

语法2:(清空)
truncate table 表名 (不能加where条件,删除整张表)

delete PK truncate

1. delete 可以加where条件,truncate不能加;
2. truncate删除,效率高一点;
3. 假如要删除的表中有自增长列,如果用delete删除后,再插入数据,自增长列的值从断点开始,而truncate删除后,再插入数据,自增长列的值重新开始.
4. truncate删除没有返回值,而delete删除有返回值.
5. truncate删除不能回滚,delete删除可以回滚.

DDL (数据定义语言)–库和表的管理

库的管理

创建:create
修改:alter
删除:drop

创建的语法:
create database 库名;
添加判断:
create database if not exists 库名; (如果库不存在才创建)

修改的语法:
一般情况不修改库名,容易造成数据的丢失,不安全.
修改库的字符集:
alter database 库名 character set 字符集名;

删除库:
drop database 库名;
添加条件:
drop database if exists 库名;

表的管理

创建:create
修改:alter
删除:drop

表的创建:
create table 表名(
列名 该列的类型 [(长度) 约束],
列名 该列的类型 [(长度) 约束],
列名 该列的类型 [(长度) 约束],
…
列名 该列的类型 [(长度) 约束],
)

表的修改:
修改列名
alter table 表名 change column 旧列名 新列名 列的类型;

修改列的类型或约束
alter table 表名 modify column 列名 列的新类型;

添加列
alter table 表名 add column 要添加的列名 列的类型;

删除列
alter table 表名 drop column 想要删除的列名;

修改表名
alter table 旧表名 rename to 新的表名

删除表
drop table 表名;

添加条件:
drop table if exists 表名;(在表存在的情况下才删除)

表的复制
1.仅仅复制表的结构
create table 新表 like 旧表;
2.复制表的结构和数据
create table 新表 select * from 旧表;
也可以复制其中部分数据:
create table 新表 select 要复制的内容 from 旧表 where 筛选条件;
如:create table student select id ,name from ss where name=‘小明’;
也可以只复制部分结构不复制数据,这时where条件设置成不成立,如1=2,或0.

垮库复制的话,需要在表的前面添加库名→库名.表名.

create table 新表 
select  *  
from  库名.表名;

常见的数据类型

数值型:
整型:tinyint smallint mediumint int|integer bigint
小数:
定点数
浮点数
字符型:
较短的文本:char varchar
较长的文本:text blob(保存较长的二进制数据)
日期型:
添加有符号的整型和无符号的整型(有符号的可以插入负数,没有符号的的不能插入负数)

create table  tab_int(
		t1 int, #有符号的
		t2 int unsigned  # 无符号的,加了关键字unsigned.
)

如果插入的数值超出了整型的范围,则会报错,并且插入临界值
**如果不设置长度,会有默认的长度,长度代表了显示的最大宽度,如果不够会用0在左边进行填充,不过必须搭配zerofill使用.例如下面的:
**

create table tab_int(
	t1  int(7)  zerofill,  #使用了zerofill,则整型自动变为无符号的,相当于默认添加了unsigned关键字.
	t2  int(8)  zerofill
)

小数

特点:
①float(M,D) Double(M,D) Decimal(M,D) Dec(M,D)
M:代表整数部位 + 小数部位的位数
D:代表小数部位的位数
如果超过范围,则插入临界值.
定义数据表的结构时,M和D 都可以省略,如果是decimal,则M默认为10,D默认为0,
如果是float和double,则会根据插入的数值的精度来决定精度.
②定点型的精度较高,如果要求插入数值的精度较高,则使用定点数类型,如货币运算.

类型使用原则:所选择的类型越简单越好,能保存数值的类型越小越好,这样最节省空间.

字符型

保存较短的文本:
char
varchar

保存较长的文本:
text
blob(较大的二进制)

特点:
char(m):代表定长的字符,空间耗费更大,性能更好.
varchar(m):代表可变长度的字符,更加节省空间,效率稍微比char低一些.
其中m代表最大的字符数,char(m)中的m可以省略,默认为1,而varchar(m)中的m不可以省略.

binary 和 varbinary
类似于char和varchar,不同的是它们包含二进制字符串而不包含非二进制字符串.

Enum类型
枚举类型
create table tab_char(
c1 enum(‘a’,‘b’,‘c’)
);

set类型
create table tab_set(
c1 set(‘a’,‘b’,‘c’)
);

同样的,和枚举一样,插入的值不区分大小写,如A 相当于a.

日期类型