MySQL使用与练习01

MySQL数据库概述

用来存储数据和管理数据,最终形成类似于表格的样子,有行有列(字段)
分类: 关系型数据库 和 非关系型数据库

• 关系型数据库， Oracle、MySQL、SQLServer、Access
• 非关系型数据库， MongoDB、Redis、Solr、ElasticSearch、Hive、HBase

数据库排行

数据库排行网

关系型数据库

关系型数据库有特定的组织方式，其以行和列的形式存储数据，以便于用户理解。关系型数据库这一系列的行和列被称为表，一组表组成了数据库。用户通过查询来检索数据库中的数据，而查询是一个用于限定数据库中某些区域的执行代码。关系模型可以简单理解为二维表格模型，而一个关系型数据库就是由二维表及其之间的关系组成的一个数据集合。

数据库的结构

SQL语句

定义
结构化查询语言(Structured Query Language)简称SQL(发音：/ˈes kjuː ˈel/ “S-Q-L”)，是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统；同时也是数据库脚本文件的扩展名。

SQL 是1986年10 月由美国国家标准局（ANSI）通过的数据库语言美国标准，接着，国际标准化组织（ISO）颁布了SQL正式国际标准。
————————————————

分类

• DML（Data Manipulation Language）数据操纵语言，对数据进行CRUD
  如：insert，delete，update，select（插入、删除、修改、检索）
  简称CRUD操新增Create、查询Retrieve、修改Update、删除Delete
• DDL（Data Definition Language）数据库定义语言，创建库，创建表
• DCL（Data Control Language）数据库控制语言，细粒度的管理操作数据库的权限
• DQL 数据库查询语言,对数据发起查询需求

MySQL基础用法

登录数据库

Mysql -u root -p root; #-u 用户名 -p 密码

exit #退出

查询数据库

show databases ;# 显示所有数据库

创建数据库

create database 库名 ; # 创建数据库名
#例：
create database cgb_des DEFAULT CHARACTER SET utf8; #创建数据库并设置字符集

删除数据库

drop database 库名 ;# 删除数据库

use 库名 ;# 使用数据库

MySQL数据库表相关操作

• 操作表或创建个表要使用数据库(use 数据库名)，在某数据库下操作表

创建表

create table 表名(字段1名 字段1类型,字段2名 字段2类型);
# 例:
create table person(name varchar(10),age int);

create table tb_door(
id int primary key auto_increment, #设置主键并且自增
door_name varchar(100),
tel varchar(50)
);

查询当前数据库下的所有表:

show tables;

删除表:

 drop table 表名;

修改表:

alter table 表名 add column 字段名 字段类型(长度);

#例：
alter table tb_door add column money NUMERIC(7,2)

查看描述表详情 (创建表的所含字段，类型约束)

desc 表名;

删除表字段:

alter table 表名 drop 字段名;

修改字段名和类型

alter table 表名 change 原名 新名 新类型;

修改字段类型和位置

alter table 表名 modify 字段名 新类型 first /after xxx
#例：
alter table student modify id int first;

#位置例:
alter table student modify id int after name;

对表插入记录

insert into 表名 values(字段1的值,字段2的值,字段3的值,字段4的值);
#例:
insert into person(name) values(‘Jerry’);
``
批量插入格式:

```bash
#例:
insert into person values(‘aa’,10),(‘bb’,20),(‘cc’,30);
insert into person(name) values(‘Jerry’),(‘yyy’);`

查询表内容

Select * from 表名;

查询显示中文乱码解决办法

set names gbk/utf8;#解决中文乱码(可插入中文)

查看（创建的）表结构

desc tb_door;

修改数据（记录）:

 update 表名 set 字段名=新值;
 update 表名 set 字段名=值 where 条件;
 #例：
 update person set age=18 where name='刘德华';

删除数据（记录）

#格式:
delete from 表名 where 条件;
例:
delete from person where name='Jerry';

• 排序 order by (按照某个条件排序)

Select * from tb_door order by tel desc; # desc降序 asc 升序

查询表里记录总数

Select count(*) from tb_door;

MySQL字段约束

主键约束 primary key

概述:
主键:表示数据唯一性的字段称为主键
约束:创建表是给字段添加限制条件
主键约束:限制值唯一且非空

DROP TABLE IF EXISTS t1; #如果表存在则删除，慎用会丢失数据，练习使用。使用前确认同名表数据是否影响

#格式: 
create table t1(id int primary key,name varchar(10));
#主键自增约束 primary key auto_increment
#格式:
create table t2(id int primary key auto_increment,name varchar(10));

唯一约束unique

唯一约束：如果为一个列添加了唯一约束，那么这个列的值就必须是唯一的（即不能重复），但可以为空。
添加唯一约束，例如为username添加唯一约束及非空约束：

#例1：
create table test( id int primary key auto_increment, username varchar(50) unique--唯一约束 );

#例2：
create table test(
id int primary key auto_increment,
username varchar(50) unique--唯一约束
);
show tables;
insert into test values(null,'lisi');
insert into test values(null,'lisi');--username的值要唯一,重复会报错的
select * from test;

默认约束 default

#默认约束:给指定字段设置默认值

• 例

DROP TABLE IF EXISTS tb_user; #如果表存在则删除，慎用会丢失数据
 
CREATE TABLE tb_user (
 
id INT PRIMARY KEY NOT NULL AUTO_INCREMENT, #自增主键
 
NAME VARCHAR(50) NOT NULL UNIQUE, #非空，唯一索引
 
sex CHAR(2) DEFAULT '男', #默认值
 
phone CHAR(18),
 
age INT,
 
createdTime DATE DEFAULT NOW()
 
);
 
DESC tb_user;

非空约束

非空约束：如果为一个列添加了非空约束，那么这个列的值就不能为空，但可以重复。

添加非空约束，例如为password添加非空约束：

create table user(
id int primary key auto_increment,
password varchar(50) not null   #not null 非空
);
show tables;
insert into user values(null,null);//不符合非空约束
insert into user values(null,123;);//OK

 CREATE TABLE f(
 id INT PRIMARY KEY AUTO_INCREMENT,#主键,自动递增 
sex VARCHAR(10) DEFAULT '男' #设置默认值 )

外键约束 foreign key

foreign key( 本表主键)–》（子表） references 关联表（主表）的主键

• 例：

DROP TABLE IF EXISTS tb_user_address; #如果表存在则删除，慎用会丢失数据
 
DROP TABLE IF EXISTS tb_user; #如果表存在则删除，慎用会丢失数据
 
CREATE TABLE tb_user (
 
id INT PRIMARY KEY NOT NULL AUTO_INCREMENT, #自增主键
 
NAME VARCHAR(50) NOT NULL UNIQUE, #非空，唯一索引
 
sex CHAR(2) DEFAULT '男', #默认值
 
phone CHAR(18),
 
age INT,
 
CHECK (age>0 AND age<=200),
 
);
 
CREATE TABLE tb_user_address (
 
user_id INT PRIMARY KEY NOT NULL,
 
address VARCHAR(200),
 
foreign key(user_id) REFERENCES tb_user(id)
 
);
 
DESC tb_user;
 
tb_user_address中user_id字段录入tb_user表不存在的主键值，将报错

子表添加记录时ID 必须取自于主表，删除主表记录时，必须没有被子表使用，确定是否是子表foreign key

检查约束 check

#检查约束:给指定字段设置合法值的检查规则 
CREATE TABLE g( 
id INT PRIMARY KEY AUTO_INCREMENT,#主键,自动递增
 age INT, 
CHECK(age>0 AND age<200)#设置检查 )

MySQL数据类型

字符

char长度固定，不足使用空格填充，最多容纳2000个字符，char(11)存储abc，占11位。查询速度极快但浪费空间
varchar变长字符串，最多容纳4000个字符，varchar(11)存储abc，只占3位。查询稍慢，但节省空间。Oracle为varchar2
大文本: 大量文字（不推荐使用，尽量使用varchar替代）
以utf8编码计算的话，一个汉字在u8下占3个字节
注：不同数据库版本长度限制可能会有不同

数字

tinyint,int整数类型
float,double小数类型
numberic(5,2) decimal(5,2)—也可以表示小数,表示总共5位,其中可以有两位小数
decimal和numeric表示精确的整数数字

日期

date 包含年月日
time时分秒
datetime包含年月日和时分秒
timestamp时间戳，不是日期，而是从1970年1月1日到指定日期的毫秒数

图片

blob 二进制数据，可以存放图片、声音，容量4g。早期有这样的设计。但其缺点非常明显，数据库庞大，备份缓慢，这些内容去备份多份价值不大。同时数据库迁移时过大，迁移时间过久。所以目前主流都不会直接存储这样的数据，而只存储其访问路径，文件则存放在磁盘上。

基础函数

lower

SELECT 'ABC',LOWER('ABC') from dept; #--数据转小写

upper

select upper(dname) from dept; #--数据转大写

length

select length(dname) from dept; #--数据的长度

substr

SELECT dname,SUBSTR(dname,1,3) FROM dept; #--截取[1,3]

concat

select dname,concat(dname,'123') X from dept #--拼接数据

replace

select dname,replace(dname,'a','666') X from dept #--把a字符替换成666

ifnull

select ifnull(comm,10) comm from dept2 #判断,如果comm是null,用10替换

round & ceil & floor

round四舍五入，ceil向上取整，floor向下取整

• 直接四舍五入取整

select comm,round(comm) from emp ;

• 四舍五入并保留一位小数

select comm,round(comm,1) from emp ;

• ceil向上取整,floor向下取整

select comm,ceil(comm) ,floor(comm) from emp

Uuid （实现唯一值）

SELECT UUID();

返回uuid：a08528ca-741c-11ea-a9a1-005056c00001

now

select now() #-- 年与日 时分秒
select curdate() #--年与日
select curtime() #--时分秒
year & month & day #年月日

• hour()时 minute()分 second()秒

select now(),hour(now()),minute(now()),second(now()) from emp ;

• –year()年 month()月 day()日

select now(),year(now()),month(now()),day(now()) from emp ;

转义字符

'作为sql语句符号，内容中出现单撇就会乱套，进行转义即可

select 'ab'cd'  #-- 单引号是一个SQL语句的特殊字符
select 'ab\'cd' #--数据中有单引号时,用一个\转义变成普通字符

MySQL查询

条件查询

distinct
使用distinct关键字，去除重复的记录行

SELECT loc FROM dept;

SELECT DISTINCT loc FROM dept;

where
注意:where中不能使用列别名!!

select * from emp

select * from emp where 1=1 --类似没条件

select * from emp where 1=0 --条件不成立

select * from emp where empno=100 --唯一条件

select * from emp where ename='tony' and deptno=2 --相当于两个条件的&关系

select * from emp where ename='tony' or deptno=1 --相当于两个条件的|关系

select name, sal from emp where sal=1400 or sal=1600 or sal=1800;

-- 或

select name, sal from emp where sal in(1400,1600,1800);

select name, sal from emp where sal not in(1400,1600,1800);

like
通配符%代表0到n个字符，通配符下划线_代表1个字符

select * from emp where ename like 'l%' --以l开头的

select * from emp where ename like '%a' --以a结束的

select * from emp where ename like '%a%' --中间包含a的

select * from emp where ename like 'l__' --l后面有两个字符的 _代表一个字符位置

null

select * from emp where mgr is null --过滤字段值为空的

select * from emp where mgr is not null --过滤字段值不为空的

between and
between 值 and 值：寻找在…之间的值。
not between 值and值：寻找不再…之间的值。

注意点：between and 和not between and：是左右两边值包含。

SELECT * FROM emp

select * from emp where sal<3000 and sal>10000

select * from emp where sal<=3000 and sal>=10000--等效

select * from emp where sal between 3000 and 10000--等效

limit
分数最高的记录：按分数排序后，limit n，返回前n条。Oracle做的很笨，实现繁琐，后期有介绍，而mysql做的很棒，语法简洁高效。在mysql中，通过limit进行分页查询：

select * from emp limit 2 --列出前两条

select * from emp limit 1,2 --从第二条开始,展示2条记录

select * from emp limit 0,3 --从第一条开始,展示3条记录--前三条

order by

desc降序 asc 升序

SELECT * FROM emp order by sal #默认升序

SELECT * FROM emp order by sal desc #降序

聚合 aggregation 函数

count
统计

select count(*) from emp --底层优化了

select count(1) from emp --效果和*一样

select count(comm) from emp --慢,只统计非NULL的

max / min
最大值 最小值

select max(sal) from emp --求字段的最大值

select max(sal) sal,max(comm) comm from emp

select min(sal) min from emp --获取最小值

select min(sal) min,max(sal) max from emp --最小值最大值

SELECT ename,MAX(sal) FROM emp group by ename --分组

sum / avg
求和/平均数

select count(*) from emp --总记录数

select sum(sal) from emp --求和

select avg(sal) from emp --平均数

分组 group

用于对查询的结果进行分组统计

group by表示分组, having 子句类似where过滤返回的结果

group by
查询条件,类似where,但是group by只能配合having
#每个部门每个岗位的最高薪资和平均薪资，结果中的非聚合列必须出现在分组中，否则业务意义不对

SELECT deptno,MAX(sal),AVG(sal) FROM emp

GROUP BY deptno #按照deptno分组

SELECT job,MAX(sal),AVG(sal) FROM emp

GROUP BY job #按照job分组

SELECT deptno,job,MAX(sal),AVG(sal) FROM emp

GROUP BY deptno,job #deptno和job都满足的

having
#平均工资小于8000的部门

select deptno, AVG(sal) from emp
group by deptno #按部门分组
having AVG(sal)<8000 #查询条件,类似where,但是group by只能配合having

#deptno出现的次数
SELECT deptno,COUNT(deptno) FROM emp

GROUP BY deptno #按deptno分组

HAVING COUNT(deptno)>1 #次数多的