文章目录
前言
MySQL 是一个关系型数据库管理系统,由瑞典 MySQL AB 公司开发,属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一。
一、MySQL 是什么?
MySQL 是一种关系型数据库管理系统,关系数据库将数据保存在不同的表中,而不是将所有数据放在一个大仓库内,这样就增加了速度并提高了灵活性。MySQL所使用的 SQL 语言是用于访问数据库的最常用标准化语言。MySQL 软件采用了双授权政策,分为社区版和商业版,由于其体积小、速度快、总体拥有成本低,尤其是开放源码这一特点,一般中小型和大型网站的开发都选择 MySQL作为网站数据库。
二、MySQL 基础命令(sql语句最后需要有分号结尾)
1、连接数据库
mysql -uroot -pmysql
-- 不显示密码
mysql -uroot -p
2、退出数据库
quit
exit
ctrl + d
3、显示数据库版本
select version();
4、显示时间
selectnow();
5、导入sql文件数据
source 具体地址/areas.sql;-- windows中文报错问题解决,改变编码模式
chcp 65001
三、数据库的操作命令
1、查看所有数据库
showdatabases;
2、查看当前使用的数据库
selectdatabase();
3、创建数据库
createdatabase 数据库名 charset=utf8;
4、查看创建数据库的语句
showcreatedatabase 数据库名;
5、使用数据库
use 数据库名;
6、删除数据库
dropdatabase 数据库名;
四、数据库表的操作命令
1、查看当前数据库中所有的数据表
showtables;
2、创建数据库表
-- 数据库常用的数据类型-- int 整型-- int unsigned 无符号整形-- decimal 小数;如 decimal(5, 2) 表示共存5位数,小数占 2 位-- varchar 表示可变长度的字符串;如varchar(3),填充'ab'时就会存储'ab',3表示字符数-- char 表示固定长度的字符串;如char(3),如果填充'ab'时会补一个空格为'ab ',3表示字符数-- date, time, datetime 日期时间-- enum 枚举类型-- 数据库常用的数据约束-- auto_increment 表示自动增长-- not null 表示不能为空-- primary key 表示主键;类型大多为 int unsigned-- foreign key 外键-- unique 惟一;此字段的值不允许重复-- default 默认值--create table 表名(字段 类型 约束,[字段 类型 约束]……);-- 例子createtable students(
id intunsignedprimarykeyauto_incrementnotnull,
name varchar(30)notnull,
age intunsigneddefault'保密',
sex enum('男','女')default'保密');
3、外键的使用
-- 外键是一种约束,约束了插入或者更新必须在外键的表中存在这个数据-- 1.表已经存在后添加外键的命令-- alter table 主表名 add foreign key (主表的外键) references 外键表(外键主键);altertable goods addforeignkey(cate_id)references goods_cates(id);-- 2.在创建主表表的同时设置外键 (注意:goods_cates和goods_brands两个外键表必须先创建,之后再创建goods(主表))-- foreign key (主表的外键) references 外键表(外键表的主键)createtableifnotexists goods_key(
id intprimarykeyauto_incrementnotnull,
name varchar(40)default'',
price decimal(5,2),
cate_id intunsigned,
brand_id intunsigned,
is_show bitdefault1,
is_saleoff bitdefault0,foreignkey(cate_id)references goods_cates(id));-- 3.如何取消外键约束-- 需要先获取外键约束名称,该名称系统会自动生成,可以通过查看表创建语句来获取名称-- show create table 表名;showcreatetable goods;-- 获取名称之后就可以根据名称来删除外键约束-- alter table 表名 drop foreign key 外键名;-- CONSTRAINT `goods_ibfk_1`--删除外键的功能altertable goods dropforeignkey goods_ibfk_1;--删除外键的标识altertable goods dropkey cate_id;
4、查看表的创建语句
showcreatetable 表名;
5、查看表结构
desc 表名;
6、删除表
droptable 表名;
7、修改表的字段
-- 添加字段altertable 表名 add 列名 类型;-- 修改字段,不重命名版altertable 表名 modify 列名 类型及约束;-- 修改字段,重命名版altertable 表名 change 原名 新名 类型及约束;-- 删除字段altertable 表名 drop 列名;
8、修改表名
renametable 原表名 to 新表名;
五、表中数据的基本操作
1、添加数据
-- 全列插入,字段不写也行,不过要一一对应,也可以全列多行插入insertinto 表名(id,name,age,height,weight,sex,cls_id,is_delete)values(0,'小张',25,175,56,'男',1,0);-- 部分插入,也可以部分列多行插入insertinto 表名(字段1,...)values(值1,...);-- 单列多行插入insertinto 表名(字段)values(值1),(值2),...;
2、删除表中数据
deletefrom 表名
-- 条件删除deletefrom 表名 where 条件
3、修改数据
update 表名 set 字段1=值1,字段2=值2...where 条件;-- 全部修改;在修改的时候要注意加条件,如果不加就是全部修改update 表名 set age =20;-- 按条件修改update 表名 set age =50where id =2;-- 按条件修改多个值update 表名 set sex ="女",age=15where id=5;
4、查询数据
-- * 代表表中所有的字段,也可以手动指定想要显示的字段,将*改成想要显示字段的字段名即可(字段名之间以 ,隔开)select*from 表名;-- 条件查询select...from 表名 where...-- 比较运算符:>,>=,<,<=,!= <>,=-- 例子select id,name from 表名 where age >18;-- 逻辑查询-- 逻辑运算符:and,or,not;逻辑运算符一般不单独使用,可以和其它查询方式联合使用-- 例子select id,name,age from 表名 where age >18and age <28;-- 模糊查询(like)-- %替换任意个,_ 替换1个-- 例子select*from 表名 where name like'小_%';-- 范围查询-- 例子:查询年龄为18或34的姓名(in (18,34)表示在一个非连续的范围内)select name from 表名 where age in(18,34);-- 例子:年龄不是18或34岁的信息(not in (18,34)不非连续的范围之内)select*from 表名 where age notin(18,34);-- 查询年龄在18到34之间的的信息(between ... and ...表示在一个连续的范围内,包含两端的数据)select*from 表名 where age between18and34;-- 查询 年龄不在18到34之间的的信息(not between ... and ...表示不在一个连续的范围内)select*from students where age notbetween18and34;
5、空判断
-- 判空is null(height:身高)select*from 表名 where height isnull;-- 判非空is not nullselect*from 表名 where height isnotnull;
6、消除重复行(查性别)
-- distinct 字段 selectdistinct sex from 表名;
7、起别名
-- 使用as为列指定别名(这个只针对当前这一次查询显示)select 字段[as 别名], 字段[as 别名]from 表名;-- 使用as为表指定别名select 别名.字段 ....from 表名 as 别名;
8、表数据的排序
-- order by 字段 asc(或desc)-- asc从小到大排列,即升序-- desc从大到小排序,即降序select*from 表名 orderby age asc;-- order by 多个字段-- 按照身高从高到矮排序, 如果身高相同的情况下按照年龄从小到大排序, 如果年龄也相同那么按照id从大到小排序;排序有优先级,第一个优先级最高select*from 表名 orderby height desc, age asc,id desc;
9、表数据的聚合函数
-- count 总数-- 查询男性有多少人selectcount(*)from students where sex ='男';-- max 最大值-- 查询最大的年龄selectmax(age)from students;-- min 最小值-- 查询最小的年龄selectmin(age)from students;-- sum 求和-- 计算所有人的年龄总和selectsum(age)from students;-- avg 平均值-- 计算平均年龄selectavg(age)from students;-- 计算平均年龄 sum(age)/count(*)selectsum(age)/count(*)from students;-- round(数值,小数位数)函数,四舍五入-- 计算所有人的平均年龄,保留2位小数selectround(avg(age),2)from students;
10、分组
-- group by-- 按照性别分组,查询所有的性别=select sex from students groupby sex;-- group by+聚集函数-- 计算每种性别中的人数=select sex,count(*)from students groupby sex;-- 查询每组性别的平均年龄=select sex,avg(age)from students groupby sex;-- group by+having-- having 作用和where类似都是过滤数据的,但having是过滤分组数据的,只能用于group by-- 按照sex字段进行分组,统计分组条数大于6的-- select 分组字段 from 表名 group by 分组字段 having 分组的条件;=select sex,count(*)from students groupby sex havingcount(*)>=6;-- group by+group_concat(字段名)-- group_concat(字段名): 统计每个分组指定字段的信息集合,每个信息之间使用逗号进行分割-- 查询同种性别中的姓名=select sex,group_concat(name)from students groupby sex;-- having(注意having和group by 连用 having后通常也要跟 聚合函数)-- 查询平均年龄超过30岁的性别,以及姓名=select sex,avg(age),group_concat(name)from students groupby sex havingavg(age)>25;-- 查询每种性别中的人数多于2个的信息=select sex,count(*),group_concat(name)from students groupby sex havingcount(*)>5;-- with rollup 汇总的作用(了解)=select sex,count(*)from students groupby sex with rollup;=select sex,count(*),group_concat(name)from students groupby sex with rolluphavingcount(*)>2;
11、分页
-- 分页用在数据量大的时候,分批传输数据,提升用户体验-- limit start, count-- limit 放在最后面(注意)-- 起始位置 = (页数-1)*每一页展示的个数-- 限制查询出来的数据个数-- 查询前5个数据 0从第一个数据开始=select*from areas limit0,5;
12、连接查询
-- inner join ... on-- select ... from 表A inner join 表B on 条件;-- 查询有能够对应班级的学生以及班级信息=select*from students innerjoin classes on students.cls_id = classes.id;-- 按照要求显示姓名、班级,取别名是为了分清两个name;s_name和c_name=select students.name as s_name,classes.name as s_name from students innerjoin classes on students.cls_id = classes.id;-- 给数据表起名字(用在自联操作时候,很少用),将students取别名为s;将classes取别名为c=select s.name as s_name,c.name as c_name from students as s innerjoin classes as c on s.cls_id = c.id;-- 查询有能够对应班级的学生以及班级信息,显示学生的所有信息 students.*,只显示班级名称 classes.name.=select students.*,classes.name from students innerjoin classes on students.cls_id = classes.id;-- 在以上的查询中,将班级名显示在第1列=select classes.name,students.*from students innerjoin classes on students.cls_id = classes.id;-- 查询有能够对应班级的学生以及班级信息, 按照班级名进行排序=select classes.name,students.*from students innerjoin classes on students.cls_id = classes.id orderby classes.name asc;-- 当时同一个班级的时候,按照学生的id进行从小到大排序=select classes.name,students.*from students innerjoin classes on students.cls_id = classes.id orderby classes.name asc,students.id asc;-- left join-- 查询每位学生对应的班级信息-- 左联,左边的表的数据都显示,不管在右边有没有找到对应的,如果没有找到就填Null=select*from students leftjoin classes on students.cls_id = classes.id;-- 查询没有对应班级信息的学生=select*from students leftjoin classes on students.cls_id = classes.id where classes.id isnull;-- right join on-- 将数据表名字互换位置,用right join on完成=select*from classes rightjoin students on students.cls_id = classes.id;
13、子查询
-- 标量子查询: 子查询返回的结果是一个数据(一行一列)-- 列子查询: 返回的结果是一列(一列多行)-- 行子查询: 返回的结果是一行(一行多列)-- 查询出高于平均身高的信息(height)=selectavg(height)from students;=select*from students where height >172.076923;=select*from students where height >(selectavg(height)from students);-- 查询学生的班级号能够对应的学生名字=select id from classes;=select*from students where cls_id in(1,2);=select*from students where cls_id in(select id from classes);
14、自联
-- 自已联接自己的表,注意要给表取别名-- 数据操作前的准备-- 查询一共有多少个省=selectcount(*)from areas;=selectcount(*)from areas where pid isnotnull;=selectcount(*)from areas where pid isnotnull;-- 查询省的名称为“山西省”的所有城市=select aid from areas where atitle='山西省';=select*from areas where pid =140000;=select*from areas where pid =(select aid from areas where atitle='山西省');-- 查询市的名称为“广州市”的所有区县=select*from areas where pid =(select aid from areas where atitle='广州市');-- 自联,将areas取别名为a1和a2,两个表a1和a2连接查询=select*from areas as a1 innerjoin areas as a2 on a1.pid = a2.aid where a2.atitle='北京市';
15、修改表结构
-- 1.创建 "商品种类表" -goods_catescreatetable goods_cates(id intunsignedprimarykeyauto_increment,name varchar(50)notnull);-- 2.创建 "商品品牌表" -goods_brandscreatetable goods_brands(id intunsignedprimarykeyauto_increment,name varchar(50)notnull);-- 3.把查询出来的种类数据插入到"商品种类表" -goods_catesinsertinto goods_cates(name)(select cate_name from goods groupby cate_name);-- 4.把查询出来的品牌数据插入到"商品品牌表" -goods_brandsinsertinto goods_brands(name)(select brand_name from goods groupby brand_name);-- 注意:通过特殊的update进行更新,两张表连接的时候 select * from 这个必须删除-- 5.将goods表中的cates_name,修改成 goods_cates表中的 id update( goods innerjoin goods_cates on goods.cate_name = goods_cates.name)set goods.cate_name = goods_cates.id;-- 6.将goods表中的brand_name,修改成 goods_brands表中的 idupdate(goods innerjoin goods_brands on goods.brand_name = goods_brands.name)set goods.brand_name = goods_brands.id;-- 注意类型必须跟外键的主键类型一致-- 7.修改goods表中cate_name和brand_name字段的名字和类型,alter table 表名 change 旧字段 新字段 类型altertable goods change cate_name cate_id intunsigned;altertable goods change brand_name brand_id intunsigned;
16、事务
-- 查看存储引擎,一般默认是InnoDB 类型show engines;-- 开启事务begin;-- 或者starttransaction;-- 提交事务commit;-- 回滚事务rollback;-- 注意-- MySQL数据库默认采用自动提交(autocommit)模式,如果没有显示的开启一个事务,那么每条sql语句都会被当作一个事务执行提交的操作set autocommit =0;
-- 表示取消自动提交事务模式,需要手动执行commit完成事务的提交
17、索引
-- 索引-- 加快数据库的查询速度-- 查看表中的已有索引-- show index from 表名;showindexfrom goods;-- 索引的创建-- 索引名不指定,默认使用字段名-- alter table 表名 index 索引名(字段名)altertable goods addindex id_name(name);-- 索引的删除-- alter table 表名 drop index 索引名;altertable goods dropindex id_name;
版权归原作者 桑落东篱 所有, 如有侵权,请联系我们删除。