MySQL基础

数据库类型

关系型数据库：Relational Database Management System RDBMS

所谓的关系型数据库RDBMS，是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据

核心元素：

• 数据行(记录)
• 数据列(字段)
• 数据表(数据行的集合)
• 数据库(数据表的集合)

非关系型数据库：

安装mysql数据库：

1.打开虚拟机终端（小黑框）

2.输入 sudo apt-get install mysql-server

启动数据库	sudo service mysql start
查看进程中是否存在mysql服务	ps ajx|grep mysql
停止服务	sudo service mysql stop
重启服务	sudo service mysql restart

关系型数据库的主要产品：

​ oracle：在以前的大型项目中使用,银行,电信等项目

​ mysql：web时代使用最广泛的关系型数据库

​ ms sql server：在微软的项目中使用

​ sqlite：轻量级数据库，主要应用在移动平台

支持多种存储引擎：

MyiSAM

InnoDB

MEMORY(HEAP)

MERGE

BDB(BerkeleyDB)、EXAMPLE、FEDERATED

ARCHIVE、CSV、BLACKHOLE

在终端进入mysql：mysql -uroot -p(输入密码，可直接跟，例如（-p密码）)

如果-p后不加空格，则默认为密码，如果加空格，则为数据库名称，如果无此数据库，则报错（无论密码正确与否）

退出：quit / exit / ctrl+d

查看版本：select version();

查看当前时间：select now();

创建数据库：

​ create database 名字 charset=utf8; (选择解释语言,可以识别中文)

​ create database 名字;

查看创建的数据库：show create database 名字;

查看当前使用的数据库：select database();

查看所有数据库：show databases;

删除数据库：drop database 名字;

选择数据库：use 名字；

数据类型：

整数	Int , bit(1/8字节)
字符串	Varchar(数字) 表示字符上限 Char(数字) 字符上限，若不够自动补空格
日期时间	date, time,datetime
枚举类型	enum(’’,’’…)多选一
浮点型	decimal(3,2)表示共3位，小数点后有两位

约束：

int unsigned	无符号整形
auto_increment	表示自动增长
not null	表示不能为空
default	默认值
primary key	表示主键
unique	唯一的
foreign key	外 键

创建表：create table 表名(字段 类型 约束 [,字段 类型 约束]);

查看所有表：show tables;

查看表结构：desc 表名;

查看创建表语句：show create table 表名;

Demo:

创建 classes 表(id、name)
create table classes(
id int unsigned primary key auto_increment not null,
name varchar(20) not null
);

创建 students 表(id、name、age、high (decimal)、gender (enum)、cls_id)
create table students(
id int unsigned primary key auto_increment not null,
name varchar(20) not null,
age int unsigned,
high decimal(5,2),
gender enum("男性","女性","中性","保密") default "保密",
cls_id int unsigned
);

修改表名： alter table 表名 rename to 新表名;

修改列（改名字）：alter table 表名 change 原名 新名类型及约束;

​ (不改名字）：alter table 表名 modify 列名 类型;

修改表-删除字段：

alter table 表名 drop 列名;	删除列
alter table 表名 add 列名;	添加列

​ 数据表的增删查改

查： select

select * from 表名;	查询所有列
select id,name from 表名;	查询指定列
select name as ‘名字’ , age as ‘年龄’ from 表名;	本次查询将name改为名字。。。
select distinct name from student;	取消重复的行

条件查询：

使用where筛选：

select id,name from classes where id=1;

where 后可支持多种运算符：

比较运算符：> ,< ,<= ,>= ,!= ,=

select id,name,age from student where id>3

逻辑运算符：and , or , not

select id,name,gender from student where id>3 and gender=‘女’;

模糊查询：like

% 表示多个任意字符

Select * from students where name like ‘黄%’;(黄开头

Select * from students where name like ‘%黄%’;（包含黄

_ 表示一个任意字符,有几个表示几个字符

Select * from students where name like ‘黄_’;(两个字，黄开头

Select * from students where name like ‘黄’;(三个字，中间有黄

范围查询：between …and \ in (1,2,3,…)

select name from student where age between 20 and 30;

空判断： is null

查询身高为空的信息

select * from students where height is null;

判断非空 ：is not null

查询身高为非空的信息

select * from students where height is not null;

优先级问题：

小括号>not>比较运算符>逻辑运算符

and 比 or 先运算，如果同时出现并希望先算or，需要结合()使用

增： insert

括号里跟内容，有多少列就应该跟多少字段
部分列插入，值的顺序与给出的列对应

改： update

update 表名 set 列名=值,列名=值… where 条件

例：update students set name=‘张四’ where id=3;	把id=3 的地方的名字改为张四

删： delete

物理删除：

delete from 表名 where 条件;

例：delete from students where id=5;

**逻辑删除：**本质就是修改操作，加一个标志代表已经删除，其实并没有删除。

update students set is_delete=1 where id = 2;

排序：order by 字段

升序：asc

降序：desc

例：

查询年龄大于20，按年龄升序排列

select * from students where age > 20 and order by age asc;

显示所有的学生信息，先按照年龄从大–>小排序，当年龄相同时 按照身高从高–>矮排序

select * from students order by age desc,height desc

常用的5个聚合函数：

总数：count(*)

select count(*) from stundents;（查学生总数

最大值：max(列)

select max(age) from students;(查年龄最大的

最小值： min(列)

select min(age) from students;(查年龄最小的

求和：sum(列)

select sum(age) from students;(求年龄的和

求平均值：avg(列),

round(avg(),1) 保留一位小数…

select avg(age) from students;(求年龄的平均值

去重：distinct(列)

select distinct(name) from students;(去掉重复的信息

分组：

group by

select gender from students group by gender;

group by + group_concat() 显示分过组的信息

select gender,group_concat(id) from students group by gender;

group by + 聚合函数

select gende,max(id) from students group by gender;(最大id

group by + having 在group中，不用where，用having

按gender分组，查询id数量大于三的分组

select gender,group_concat(id) from students group by gender having count(id) > 3;

group by + with rollup

在最后新增一行，来记录当前列里所有记录的处理

最后会在末尾新增一行，输出所有的年龄，不分组

select gender,group_concat(age) from students group by gender with rollup;

最后会在末尾新增一行，输出所有数据的平均数

select gender,avg(age) from students group by gender with rollup;

分页： limit

start代表开始，count代表获取数量

select * from 表名 limit start,count

求第n页的数据

select * from students limit (n-1)*m,m;

连接查询：join

内连接查询(inner join)：查询结果为两个表匹配到的数据

select * from students inner join classes;

右链接查询(right join)：查询的结果为两个表匹配到的数据，右表特有的数据，对于左表中不存在的数据使用null填充

select * from students as s right join classes as c on s.cls_id = c.id;

左链接查询(left join)：查询结果为两个表匹配到的数据，左左表特有的数据，对于右表中不存在的数据使用null填充

select * from students as s left join classes as c on s.cls_id = c.id;

子查询：在一个select中嵌入了另一个select语句，被嵌入的称为子查询

标量子查询

列子查询

行子查询

主查询：主要的查询对象，相对于子查询

(主查询)select * from students where age > (select avg(age) from students(子查询));

子查询特定关键字：in

select *from students where cls_id in (select id from classes);

外键：保证数据的完整性和准确性，在一定程度上起到了相互约束的作用

从文件中导入数据到mysql

1.在同一路径下打开mysql

2.source 文件名.sql

自关联：起别名，自己关联自己

查询省的名称为“山西省”的所有城市

select city.* from areas as city inner join areas as province on city.pid=province.aid where province.atitle=‘山西省’;

显示每种商品平均价格：

select cate_name(商品分类字段) avg(price) from goods group by cate_name;

查询每种类型的商品中 最贵、最便宜、平均价、数量

select cate_name,max(price),min(price),avg(price),count(*) from goods group by cate_name;

查询所有价格大于平均价格的商品，并降序

select id,name,price from goods where price > avg(price) order by price desc;

查询每种类型中最贵的电脑信息

select * from goods inner join (select cate_name,max(price) as max_price from goods group by cate_name) as good on goods.cate_name = good.cate_name and goods.price = good.max_price;

更新表结构（同步数据，修改表结构）,添加外键

同步数据： update (goods inner join goods_cates on goods.cate_name = goods_cates.name) set goods.cate_name = goods_cates.id;

修改表结构： alter table goods change cate_name cate_id int unsigned not null;

添加外键： alter table goods add foreign key(cate_id) references goods_cates(id);

开启运行时间监测：set profiling = 1;

查看执行时间：show profiles;（用于查看有索引和无索引运行所需时间差)

视图

视图的作用：

1.提高了重用性，就像一个函数

2.对数据库重构，却不影响程序的运行

3.提高了安全性能，可以对不同的用户

4.让数据更加清晰

创建视图：create view 视图名称 as select语句;

查看视图：show tables;

使用试图：select * from 视图名称;

删除视图：drop view 视图名称;

事务

事务：开启事务后执行修改命令，变更会维护到本地缓存中，而不维护到物理表中

事务四大特性(简称ACID)

原子性(Atomicity)

一致性(Consistency)

隔离性(Isolation)

持久性(Durability)

开启事务：start transaction \ begin

回滚事务：rollback;

提交事务：commit;

事务存储点：savepoint s1(名称);

事务存储点回滚：rollback to s1;

注意：

1.修改数据的命令会自动的触发事务，包括insert、update、delete

2.而在SQL语句中有手动开启事务的原因是：可以进行多次数据的修改，如果成功一起成功，否则一起会滚到之前的数据

索引

索引：索引的目的在于提高查询效率

注意：

1.要注意的是，建立太多的索引将会影响更新和插入的速度，因为它需要同样更新每个索引文件。对于一个经常需要更新和插入的表格，就没有必要为一个很少使用的where字句单独建立索引了，对于比较小的表，排序的开销不会很大，也没有必要建立另外的索引。

2.建立索引会占用磁盘空间

创建索引：create index 索引名称 on 表名(字段名(数字限制));

create index index_name on goods(name(10));

查看索引： show index from 表名;

show index from goods;

删除索引：drop index 索引名称 on 表名

drop index index_name on goods;