MySQL

存储引擎
基础操作
其他操作
算数运算符
比较运算符
逻辑运算符
聚合函数
分组
视图
函数
谓词
集合运算
示例
数据类型

安装``yum install mariadb mariadb-server.x86_64 -y
``

启动systemctl start mariadb

查看3306端口是否启动ss -tanl | grep 3306

初始化mysql_secure_installation

mysql连接方式：

TCP/IP：建立一个网络的连接请求，一般情况下客户端在一台服务器上，而实例在另外一台服务器上

命名管道：如果两个需要互相通信的进程在同一台服务器上，就可以使用命名管道，但是需要在配置文件中启用——enable-named-pipe选项

共享内存：通过在配置文件中添加——shared-memory实现，在实际连接的过程中需要添加——protocol=memory选项

unix套接字

vim /etc/my.cnf.d/clint.cnf
user=root
password=.....

可以免输入密码登录数据库

存储引擎

InnoDB\MyISAM\NDM\Memory\Archive\Federated\Maria

查询当前数据库支持的存储引擎show engines \G;

修改默认存储引擎配置文件

cat /etc/my.cnf
default-storage-engine=INNODB

建表时指定存储引擎
create table xx(xxxx) ENGINE=MyISAM

修改表的存储引擎alter table xx engine =xxxx;

InnoDB和MyISAM的区别

基础操作

mysql -u用户名 -p密码

use database_name选择数据库

create database database_name;创建数据库

show databases;查看所有数据库

select user();查询当前用户

set password = password('xxxxx');给当前数据库设置密码

增删改查

创建表

同一表中字段名不能相同，同一库中表名不能相同
创建表

create table table_name(
    字段名 类型 约束条件,
    字段名 类型 约束条件,
    ......
    字段名 类型 约束条件
);

create table table_name (xxxxx....,a1 int default 0);
使用default可以设置默认值

插入数据

insert into table_name (字段名，字段名...字段名) values (值,值....值);

当想插入默认值的时候，值处直接填入default就可以，或者直接略过，如果省略的字段没有默认值，那么值会变成null

insert into table_name (a1,a2,a3) select b1,b2,b3 from table_name;从别的表当中获取数字插入表中

insert into table_name(a1,a2,a3) selct b1,sum(b1),sum(b2) from table group by b1;

insert into table_name values (全部值);

update table_name SET 名=值......;

查看表结构、信息

select * form table_name;

show tables;

desc table_name;

show create table table_name;建表信息

select * from 库名.表名;

select distinct name from table;只返回不同的值，但是NULL也会被保留，distinct只能放在第一个列名之前

select name from table_name limit n;限制返回n行

select name from table_name limit n offset m;限制返回n行，从m行开始

排序检索

select name from table_name order by name1,name2...;取出一列或多列的名字，以此对输出进行排序，order by 语句只能出现在所有语句中的最后一条

select name from table_name order by n,m...;使用数字代表对应列的位置进行排列
select a1, a2, a3 from table_name order by 1;此处的order by1指以a1为基准进行排序

....order by name1 desc,name2;name1降序排序

排序当中，null的值会聚在一端，视具体数据库为准，且order by可以使用别名

修改

alter table 旧表名 rename 新表名;      /修改表名

alter table 表名 add 字段名[完整性约束条件] /增加字段;

alter table 表名 drop 字段名; /删除字段

alter table 表名 modify 字段名 类型 约束条件 change 旧字段名 新字段名 新数据类型 约束条件;  /修改字段

ALTER TABLE 表名
    ADD 字段名 数据类型 约束条件 FIRST;
    ADD 字段名 数据类型 约束条件 AFTER 字段名;
    CHANGE 旧字段名 新字段名 新数据类型 完整性约束条件 FIRST;
    MODIFY 字段名 数据类型 约束条件 AFTER 字段名；
//修改字段排列顺序，在新增字段时指定字段位置

alter table table_name modify 字段 类型 primary key auto_increment设置自增主键

updare table_name set field=value...  where....更新值

事务：
在同一个处理单元中执行一系列更新的集合

start transaction; --mysql
DML1;
DML2;
DML3;
...

commit/rollback;提交或回滚

删除

drop table table_name;删表

alter table table_name drop 字段;

delete from table_name where\orderby\limit;不使用where时删除全部行，但是保留表

where:where子句要紧跟在from子句之后，且不能和聚合函数一起使用

select .... from where name='xx';
where只能指定行的筛选条件

其他操作

注释

方式一
avatar

方式二
avatar

方式三
avatar

别名

select name as别名 from table_name;如果别名要使用中文，必须用双引号括起来而不是单引号

avatar

算数运算符

select price * 2 as 'doublue price' from table;从表中将价格这个字段乘以二的结果读出

四种运算符：+、-、*、/
当数字与NULL运算时，计算结果都是NULL，并且NULL的真值为不确定

比较运算符

=等于
<>不等于
>=大于等于
>大于
<=小于等于
<小于

select xxx from table where a_date < '2010-01-01';日期

select xxxx from table where price1 - price2 >= 1000;与算术运算符连用

当比较的是字符串类型的大小时，是按照字典的顺序来排，如'195'比'2'要小，因为1排在2之前

比较运算符不能用在NULL值上，如果想要判断值是否为NULL，可以使用 'is null'字段来判断，相反，需要使用'is not null'

逻辑运算符

  1. NOT:表示否定
    select xx from xxx where NOT price > 1000;
  2. AND\OR:并且\或,注意and的优先级要比or高！

select xx from xxx where a>10 and b < 50;

  1. 使用()来提升关系

聚合函数

COUNT:计算表中的行数

SUM:数据的合计

AVG:平均

MAX:最大

MIN:最小

select count(*) from table_name;
使用count(*)时，会包含null的行，如果使用count(列名)，则不会计算null行

select sum(price) from table;

select distinct count(xx) from table;统计时去除重复

select sum(distinct price) from table;计算时去除重复

聚合函数不能和where一起使用！只有having子句、order by可以

分组

select xxx from table_name group by x1;
以x1为分组

group by 需要写在from\where语句之后
null也会被当做一组特定的数据处理.

select 列 from table_name where xxxxxx group by x1;先进行过滤在进行汇总

使用group by时，select 子句中不好出现聚合键以外的列名

group by的排序是随机的，且不能够使用别名

select a1,sum(b1) as sum_b1 from table group by a1 with rollup;此处在计算各组之和之后还会显示一行总和

指令的执行顺序：FROM--WHERE--GROUP BY--HAVING--SELECT--ORDER BY

avatar

having
having和where一样都是可以用来指定条件的语句，但是where只能指定记录行的条件，不能用来指定组的条件

having必须放在group by 后面

select xxx from table group by a1 having count(*)>2;此处的count为分组后各组各自统计的数

having子句中出现的列必须出现在groupby子句中，或者在聚合函数中使用

视图

视图不会保存数据，他保存的是从表中取数据用的select语句
create view 视图名字[(列名1，列名2...)] as select....

可以使用视图来创建视图，但是不推荐，同时，定义视图的时候不能使用order by语句，因为视图的数据行是没有顺序的

视图内的数据也可以进行和表一样的更新，但是要注意视图内数据和原表的关系，有些更新会导致错误

删除语句drop view name(列名...)

子查询

create view name xxx as select xxx from xxx group by;等同于
select xxx from (select xxx from xxx froup by xx)as name

标量子查询：返回一行一列的结果*


select a1,a2,a3,(select avg(price) from table) as avg_price from table;

select a1,avg(price) from table group by table having avg(price) > (select avg(price) from table);

关联子查询

SELECT product_type, product_name, sale_price
 FROM Product AS P1 
 WHERE sale_price > (SELECT AVG(sale_price)
 FROM Product AS P2 
 WHERE P1.product_type = P2.product_type
 GROUP BY product_type);

函数

  1. ABS()绝对值函数
  2. mod()求余
  3. round()四舍五入
  4. concat(a1,a2,a3)拼接字符串
  5. length()字符串重读
  6. lower()转为小写
  7. replace(对象，替换前，替换后)字符串替换
  8. subsrting(对象 from 起始位置 for 截取的字数)截取字符串
  9. current_date当前日期
  10. cast(转换前的值 as 想转换的类型)类型转换
  11. coalesce(数据，数据。。。)返回可变参数从左开始第一个不是null的值

谓词

LIKE:部分查询

SELECT *
 FROM SampleLike
 WHERE strcol LIKE 'ddd%';字符串开头一致

 SELECT *
 FROM SampleLike
 WHERE strcol LIKE '%ddd%';中间包含

 SELECT *
 FROM SampleLike
 WHERE strcol LIKE 'abc_ _';开头一致且后面跟两个字符

between

SELECT product_name, sale_price
 FROM Product
 WHERE sale_price BETWEEN 100 AND 1000;会包含临界值

in作为范围

SELECT product_name, purchase_price
 FROM Product
 WHERE purchase_price (not) IN (320, 500, 5000);

in作为子查询

SELECT product_name, sale_price
 FROM Product
 WHERE product_id (not) IN (SELECT product_id 
 FROM ShopProduct
 WHERE shop_id = '000C');从内层开始查

exist:判断是否存在满足某种条件的记录并返回真值

SELECT product_name, sale_price
 FROM Product AS P 
 WHERE EXISTS (SELECT *
 FROM ShopProduct AS SP 
 WHERE SP.shop_id = '000C'
 AND SP.product_id = P.product_id);exist只关心记录是否存在，与返回的列是那些并没有关系

CASE

CASE when <求值表达式> then <表达式>
     when <>           then <>
     ...
     else <表达式>
END

SELECT product_name,
 CASE WHEN product_type = '衣服'
 THEN 'A ：' | | product_type
 WHEN product_type = '办公用品'
 THEN 'B ：' | | product_type
 WHEN product_type = '厨房用具'
 THEN 'C ：' | | product_type
 ELSE NULL
 END AS abc_product_type
 FROM Product;

 SELECT SUM(CASE WHEN product_type = '衣服' 
 THEN sale_price ELSE 0 END) AS sum_price_clothes,
 SUM(CASE WHEN product_type = '厨房用具' 
 THEN sale_price ELSE 0 END) AS sum_price_kitchen,
 SUM(CASE WHEN product_type = '办公用品' 
 THEN sale_price ELSE 0 END) AS sum_price_office
 FROM Product;

集合运算

union

取交集，运算对象的记录的列数必须相同，且运算对象的列的类型必须一致，orderby只能放在最后使用，union会进行去重，如果需要保留重复，则使用union all

select sname from stu_info union select name from cs;

自然连接

select * from stu_info natural join cs;
自然连接会自动处理，但是需要一样的字段名、属性，并且要设置主键

内联结

select a.p1,a.p2,b.t1 from att as a1 inner join byy as b on a.p1=b.t1;

on是用于写连接条件的，必须书写在from 和 where 之间

SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name, P.sale_price, IP.inventory_quantity
 FROM ShopProduct AS SP INNER JOIN Product AS P 
 ON SP.product_id = P.product_id
 INNER JOIN InventoryProduct AS IP 
 ON SP.product_id = IP.product_id
 WHERE IP.inventory_id = 'P001';多表联结

外联结

SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name, P.sale_price
FROM ShopProduct AS SP (RIGHT/LEFT) OUTER JOIN Product AS P 
ON SP.product_id = P.product_id;

与内联结相比，会包含原表中不存在的信息，使用left或right来确定谁是主表，最终的表中会包含主表中的所有信息

avatar

示例

create table stu_info(sno int primary key auto_increment, sname varchar(20), sclass varchar(20), ssex enum('男','女'));

alter table stu_info charset=utf8;

alter table stu_info modify ssex enum('男','女');
/////以上为错误演示

create table stu_info(sno int primary key auto_increment, sname varchar(20), sclass varchar(20), ssex enum('男','女')) charset=utf8;
////正确示例，这样在输入中文数据时不会变成问号

insert into stu_info (sname,sclass,ssex) values ('张三','信息安全1','男');

insert into stu_info (sno,sname,sclass,ssex) values ('100','李四','计算机2','女');

insert into stu_info (sname,sclass,ssex) values ('王五','软件3','男');


select * from stu_info;

avatar

alter table stu_info rename info_stu;修改表名

alter table stu_info add phoneno int(20);增加电话字段

alter table stu_info add birth date after ssex;
将新增加的生日字段加在性别后面

update stu_info set birth='2000-01-01' where sno='1';更新张三的生日

select sname from stu_info limit 2 offset 2;
从第二行限制返回两行

insert into stu_info values ('55','小明','通信2','女','2001-3-3','13485858588');


select * from stu_info where birth is not null;选出填写了生日的人

select count(*) from stu_info;查看表一共有几行

select max(sno) from stu_info;查看表中学号最大的

select ssex,count(*) from stu_info group by ssex;
计算男女人数

select ssex,count(*) from stu_info group by ssex having count(*) > 2;晒出人数多的性别

create table simp_stu (no int(11),name varchar(20)) charset=utf8;创建新表

insert into simp_stu select sno,sname from stu_info;从前表中获取数据插入其中

start transaction;
update stu_info set birth = '2000-10-10' where birth is null;
update stu_info set phoneno = '123456' where phoneno is null;
commit;使用事务进行批量更新

select substring(phoneno from 1 for 3) from stu_info;截取手机号的前三位

select * from stu_info where birth like '2001%';查询2001年生的

select sname, case when birth < '2001-1-1' then concat('A',birth)  else 'B' end as '时代' from stu_info;根据出生日期划分学生 


create table cs (name varchar(20),class varchar(20)) charset=utf8;创建课表
insert into cs values ('张三','信息论'),('张三','离散数学'),('王五','JAVA');
insert into cs values ('alias','C++');
select sname from stu_info union select name from cs;选取两张表学生的姓名

select a.sname,a.sclass,b.class from stu_info as a inner join cs as b on 
a.sname=b.name;选出既在学生表中，又在选课表中选了课的学生的信息

数据类型

数值型：int,unsigned int,decimal,float,double,real

字符型：

定长：char(),binary

变长：varchar(),varvinary

text,blob：保存较大文本使用，text只能保存字符数据，删除会导致留下“空洞”，可以使用OPTIMIZE TABLE 表名来进行优化

同时，可以使用索引来提高大短文本的精准查询的性能，例

create table t (in varchar(10),context blob,hash_value varchar(40));

insert into t values(1,repeat('xxx',2),md5(context));hash_value用于存储context的md5散列值

select * from t where hash_value=md5(repeat(xxx,2));

或者也可以使用前缀索引

desc select * from t where context like 'xxx%' \G;

时间日期型：
date,time,datetime,timestamp

date:年月日

datetime:年月日时分秒

time:时分秒

timestamp:需要经常插入或者更新当前日期为系统时间，格式YYYY-MM-DD HH:MM:SS

year:年

插入当前时间的函数：now()

枚举

ENUM:单选// 使用中文要记得指定charset=utf8

SET:多选

触发器

mysql> create table t_control(id int primary key);
Query OK, 0 rows affected (0.11 sec)

mysql> insert into t_control values (1);
Query OK, 1 row affected (0.05 sec)

mysql> create table t_bluerosehero(id int primary key,col int);
Query OK, 0 rows affected (0.11 sec)

mysql> delimiter //
mysql> create trigger tr_t_bluerosehero_bi before insert on t_bluerosehero
    -> for each row
    -> begin
    ->  if new.col>30 then
    ->          insert into t_control values (1);
    ->  end if;
    -> end;
    -> //
Query OK, 0 rows affected (0.08 sec)

标签: none

评论已关闭