MySQL笔记

纸瓷

登录MySQL数据库

安装完毕后，点开MySQL 8.0 Command Line Client输入密码
或者cmd进入mysql.exe所在目录输入
mysql -h localhost -u root -p然后输入密码
MySQL数据库基本操作

显示数据库 show databases;

创建数据库 create database db_name;

删除数据库 drop database db_name;

用数据库 use db_name;

创建表

create table t_name(
    id int(11),
    name varchar(25),
    salary float
);查看表 show tables;

主键约束 primary key

create table t_name(
    id int(11) primary key,
    name varchar(25),
    salary float
);
or
create table t_name(
    id int(11) primary key,
    name varchar(25),
    salary float,
    primary key(id,name)
);外键约束 constraint fk_emp_dept1 foreign key(deptId) references tb_dept1(id)

create table tb_dept1(
    id int(11) primary key,
    name varchar(22) not null,
    location varchar(50)
);
create table tb_emp5(
    id int(11) primary key,
    name varchar(25),
    deptId int(11),
    salary float,
    constraint fk_emp_dept1 foreign key(deptId) references tb_dept1(id)
)非空约束not null

看上面这个代码中的 not null 使得name字段不能为空

唯一性约束 unique

允许为空，一列或几列中不能重复
create table tb_dept2(
    id int(11) primary key,
    name varchar(22) unique,
    location varchar(50)
);
or
create table tb_dept3(
    id int(11) primary key,
    name varchar(22),
    location varchar(50),
    constraint sth unique(name)
);tips:一个表中可以有多个 unique，只能有一个 primary key
默认约束 default 1111

create table tb_dept2(
    id int(11) primary key,
    name varchar(22) not null,
    deptId int(11) default 1111,
    salary float
);指定员工部门编号为1111
设置表的属性值自动增加 auto_increment
一个表只能有一个字段使用该约束
查看表的基本结构

describe 表名 or desc 表名
查看表的详细结构

show create table 表名 \G
查看所有数据表

show tables
修改表名

alter table <旧表名> rename <新表名>
修改字段数据类型

alter table <表名> modify <字段名> <数据类型>
修改字段名

alter table <表名> change <旧字段名> <新字段名> <新数据类型>
添加字段

alter table <表名> add <新字段名> <数据类型> [约束条件] [FIRST | AFTER 已存在字段名]
FIRST为可选参数，将添加的字段为表的第一个字段，AFTER为可选参数，将添加字段添加到指定已存在字段名后面
删除字段

alter table <表名> drop <字段名>
修改字段排列位置

alter table <表名> modify <字段1> <数据类型> first|after <字段2>
更改表的存储引擎

alter table <表名> engine=<更改后的存储引擎名>
删除表的外键约束

alter table <表名> drop foreign key <外键约束名>
删除没有被关联的表

drop table  [if exists] 表1,表2,...,表n
删除被其他表关联的主表

1.先删除子表，再删除父表
2.删除外键约束，再删表
数据类型和运算符

数据类型

数值数据类型:

• 整数类型：tinyint,smallint,mediumint,int,bigint
  
• 浮点小数数据类型：float,double
  
• 定点小数类型：decimal
  

日期/时间类型：year,time,date,datetime,timestamp
字符串类型：char,varchar,binary,varbinary,blob,text,enum,set
运算符

算术运算符：+,-,*,/,%
比较运算符：>,<,=,>=,<=,!=,in,between,and,is null,greatest,least,like,regexp
逻辑运算符：not或者!,and或者&&,or或者||,异或xor
位运算：&,|,~,^,<<,>>
索引的操作

大大加快查询速度
在book表中的year_publication字段上建立普通索引

create table book
(
    bookid int not null,
    bookname varchar(255) not null,
    authors varchar(255) not null,
    info varchar(255) null,
    comment varchar(255) null,
    year_publication year not null,
    index(year_publication)
);创建唯一索引

create table t1
(
    id int not null,
    name char(30) not null,
    unique index UniqIdx(id)
);创建单列索引

index SingleIdx(name(20))
创建组合索引

index MultiIdx(id,name,age(100))
创建全文索引

fulltext index FullTxtIdx(info)
创建空间索引

空间索引必须在MyISAM类型的表中创建，且空间类型的字段不能为空。
create tabel t5(
    g geometry not null,
    spatial index spatIdx(g)
)engine=MyISAM;在已有的表上创建索引

alter table book add index BkNameIdx(bookname(30));
create index BkNameIdx on book(bookname);
删除索引

alter table table_name drop index index_name;
drop index index_name on table_name;
视图的操作

在单表上创建视图

create table t (quantity int, price int);
insert into t values(3,50);
create view view_t as select quantity,price,quantity*price from t;
select * from view_t;

-- or

create view view_t2(qty,price,total) as select quantity,price,quantity * price from t;
select * from view_t2;在多表上创建视图

-- 创建表
create table student (stuid int, name char(20));
create table stu_info (stuid int, name char(20), home char(20));
insert into student values(1,'wanglin1'),(2,'gaoli'),(3,'zhanghai');
insert into stu_info values(1,'wuban','henan'),(2,'liuban','hebei'),(3,'qiban','shandong');
-- 创建视图
create view stu_glass (id,name,home) as select student.stuid,student.name,stu_info.home from  student,stu_info where student.stuid=stu_info.stuid;查看视图

describe 视图名
show table status like '视图名' \G
show create view 视图名 \G
select * from information_schema.views \G
修改视图

create or replace view view_t as select * from t
alter view view_t as select quantity from t
更新视图

update view_t set quantity=5
insert into t values (3,5)
delete from view_t2 where price=5
删除视图

drop view if exists stu_glass
插入、更新与删除数据

插入数据

为表的所有字段插入数据

create table person(
    id int unsigned not null auto_increment,
    name char(40) not null default '',
    age int not null default 0,
    info char(50) null,
    primary key(id)
);
select * from person;
insert into person (id, name, age, info) values (1,'green',21,'lawyer');
select * from person;
insert into person (age, name, id, info) values (22,'suse',2,'dancer');
select * from person;
insert into person values (3,'mary',24,'musician');
select * from person;为表的指定字段插入数据

insert into person (name,age,info) values ('willam',20,'sports man');
insert into person (name,age) values ('Laura',25);同时插入多条记录

insert into person (name,age,info)
values ('evans',27,'secretary'),
('dale',22,'cook'),('edison',28,'singer');将查询结果插入表中

create table person_old(
    id int unsigned not null auto_increment,
    name char(40) not null default '',
    age int not null default 0,
    info char(50) null,
    primary key(id)
);
select * from person_old;
insert into person_old values (11,'harry',20,'student'),(12,'beckham',31,'police');
select * from person_old;
insert into person select id,name,age,info from person_old;-- 将查询结果插入表中
select * from person_old;更新数据

update person set age=15,name='LiMing' where id=11;
select * from person;
select * from person;
update person set info='student' where age between 19 and 22;
select * from person;删除数据

删除单条

select * from person;
delete from person where id=11;
select * from person;删除多条

delete from person between 19 and 22;删除全表

delete from person;

truncate table person; -- 删除表再重建MySQL函数

数学函数

求绝对值：abs(x)
求圆周率：pi()
求平方根：sqrt(x)
求余：mod(x,y)
向上取整：ceil(x),ceiling(x)
向下取整：floor
获得随机数：rand(),rand(x)
四舍五入：round(x),round(x,y),truncate(x,y)
符号函数：sign(x)
幂运算：pow(x,y),power(x,y),exp(x)
对数运算：log(x),log10(x)
角度与弧度相互转换：radians(x)(角->弧),degrees(x)
三角函数：sin(x),asin(x),cos(x),acos(x),tan(x),atan(x),cot(x)
字符串函数

字符串中字符数和字符串字节长度：char_length(s),length(s)
合并字符串：concat(s1,s2,...),concat(separator,s1,s2,...)
替换字符串：insert(s1,x,len,s2)
字母大小写转换：lower(s),lcase(s),upper(s),ucase(s)
获取指定长度字符串：left(s,n),right(s,n)
填充字符串：lpad(s1,len,s2),rpad(s1,len,s2)
删除空格：ltrim(s),rtrim(s),trim(s)
删除指定字符串：trim(s1 from s)
重复生成字符串：repeat(s,n)
空格函数：space(n)
替换函数：replace(s,s1,s2)
比较字符串大小；strcmp(s1,s2)
获取子串：substring(s,n,len),mid(s,n,len)
匹配字符串开始位置：locate(s1,s2),position(s1 in s2),instr(s1,s2)
字符串逆序：reverse(s)
返回指定位置的字符串：elt(n,s1,s2,...)
返回指定字符串位置：field(s,s1,s2,...)
返回子串位置：find_in_set(s1,s2)
选取字符串：make_set(x,s1,s2,...)
日期和时间函数

获取当前日期：curdate(),current_date()
获取当前时间：curtime(),current_time()
获取当前日期时间：current_timestamp(),localtime(),now(),sysdate()
时间变UNIX时间戳：unix_timestamp()
UNIX时间戳变时间：from_unixtime(date)
UTC日期时间：utc_date(),utc_time()
获取月份：month(),monthname()
获取星期 ：dayname(d),dayofweek(d),weekday()
获取星期数：week(d),weekofyear(d)
获取天数：dayofyear(d),dayofmonth(d)
获取年份，季度，小时，分钟和秒钟：year(),quarter(),minute(),second()
获取日期指定值：extract(type from date)
时间和秒钟转换函数：time_to_sec(time),sec_to_time(time)
计算日期和时间的函数：date_add(),adddate(),date_sub(),subdate(),addtime(),subtime(),date_dief()
格式化日期和时间的函数：date_format(date,format)
条件判断函数

if(expr,v1,v2),expr为true则返回v1,否则v2
ifnull(v1,v2),v1不为null返回v1,否则v2
case函数

case expr when v1 then r1 [when v2 then r2] end
系统信息函数

获取mysql版本号：version()
获取连接数：connection_id(),show processlist
获取数据库名：database(),schema()
获取用户名：user(),current_user,current_user(),system_user(),session_user()
获取字符串的字符集：charset()
获取字符排列方式：collation()
获取最后一个自动生成的ID：last_insert_id()
加密解密函数

加密函数：password(str),md5(str),encode(str,pswd_str)使用pswd_str作为密码加密str,使用decode(crypt_str,pswd_str)解密crypt_str
其它函数

格式化函数：format(x,n),将x格式化并保留小数点后n位
不同进制的数字进行转换：conv(n,from_base,to_base)
IP地址与数字转换：inet_aton(ip)
加锁和解锁：get_lock(str,timeout),release_lock(str)
重复执行指定操作：benchmark(count,expr)重复count次执行expr
改变字符集：conver('string' using latin1)
改变数据类型：cast(x, as type)
查询数据

基本查询语句

基本格式
select
    {*|<字段列表>}
    [
        from <表1>,<表2>...
        [where <表达式>]
        [group by <group by definition>]
        [having <expression> [{<operator><expression>}...]]
        [order by <order by definition>]
        [limit [<offset>,] <row count>]
    ]
select [字段1，字段2,...,字段n]
from [表或视图]
where [查询条件]演示
create table fruits(
    f_id char(10) not null,
    s_id int not null,
    f_name char(255) not null,
    f_price decimal(8,2) not null,
    primary key(f_id)
);
insert into fruits (f_id, s_id, f_name, f_price) values
('a1',101,'apple',5.2),
('b1',101,'blackberry',10.2),
('bs1',102,'orange',11.2),
('bs2',105,'melon',8.2),
('t1',102,'banana',10.3),
('t2',102,'grape',5.3),
('o2',103,'coconut',9.2),
('c0',101,'cherry',3.2),
('a3',103,'apricot',2.2),
('l2',104,'lemon',6.4),
('b2',104,'berry',7.6),
('m1',106,'mango',15.6),
('m2',105,'xbabay',2.6),
('t4',107,'xbababa',3.6),
('m3',105,'xxtt',11.6),
('b5',107,'xxxx',3.6);
select * from fruits;单表查询

查询所有字段

select * from fruits;
select f_id,s_id,f_name,f_price from fruits;查询指定字段

#查询单个字段
select f_name from fruits;
#查询多个字段
select f_name, f_price from fruits;查询指定记录

SELECT f_name, f_price
FROM fruits
WHERE f_price = 10.2;带 IN 关键字的查询

IN
SELECT s_id,f_name,f_price
FROM fruits
WHERE s_id IN (101,102)
ORDER BY f_name;NOT IN
SELECT s_id,f_name,f_price
FROM fruits
WHERE s_id NOT IN (101,102)
ORDER BY f_name;带 BETWEEN AND 的范围查询

BETWEEN AND
SELECT f_name, f_price
FROM fruits
WHERE f_price BETWEEN 2.00 AND 10.20;NOT BETWEEN AND
SELECT f_name, f_price
FROM fruits
WHERE f_price NOT BETWEEN 2.00 AND 10.20;##### 带 LIKE 的字符匹配查询
#1.百分号%，可以匹配任意长度的字符，甚至包括空字符
##查找所有以b字母开头的水果
SELECT f_id, f_name
FROM fruits
WHERE f_name LIKE 'b%';
##查找所有含g字母的水果
SELECT f_id, f_name
FROM fruits
WHERE f_name LIKE '%g%';
##查找所有以b开头y结尾字母的水果
SELECT f_id, f_name
FROM fruits
WHERE f_name LIKE 'b%y';
#2.下划线_,一次只能匹配一个字符
##查找所有以y结尾字母且y前只有4位的水果
SELECT f_id, f_name
FROM fruits
WHERE f_name LIKE '____y';查询空值

SELECT f_id, f_name
FROM fruits
WHERE f_name IS NULL;(IS NOT NULL)带 AND 的多条件查询

SELECT f_id, f_price, f_name
FROM fruits
WHERE s_id = '101' AND f_price >=5;
#----------------------------------
SELECT f_id, f_price, f_name
FROM fruits
WHERE s_id IN ('101','102') AND f_price >=5;带 OR 的多条件查询

SELECT f_id, f_price, f_name
FROM fruits
WHERE s_id = '101' OR s_id = '102';查询结果不重复

SELECT DISTINCT s_id
FROM fruits;对查询结果排序

单列排序
SELECT f_name
FROM fruits
ORDER BY f_name;多列排序
SELECT f_name, f_price
FROM fruits
ORDER BY f_name, f_price;指定排序方向

SELECT f_name, f_price
FROM fruits
ORDER BY f_price DESC;(ASC)分组查询

[GROUP BY 字段] [HAVING <条件表达式>]
#"字段"为进行分组时所依据的列名称；"HAVING <条件表达式>"指定满足表达式限定条件的结果将被显示1.创建分组
SELECT s_id, COUNT(*) AS Total
FROM fruits
GROUP BY s_id;

SELECT s_id, GROUP_CONCAT(f_name) AS Names
FROM fruits
GROUP BY s_id;2.使用 HAVING 过滤分组
SELECT s_id, GROUP_CONCAT(f_name) AS Names
FROM fruits
GROUP BY s_id HAVING COUNT(f_name)>1;3.在 GROUP BY 子句中使用 WITH ROLLUP，在所有查询出的分组记录之后增加一条记录，该记录计算查询出的所有记录的总和，即统计记录数量
SELECT s_id, COUNT(*) AS Total
FROM fruits
GROUP BY s_id WITH ROLLUP;4.多字段分组
先按第一个字段分组，然后对第1个字段值相同的记录，再根据第二个字段分组，以此类推。
SELECT *
FROM fruits
GROUP BY s_id,f_name;5.GROUP BY 和 ORDER BY 一起使用（ORDER BY与ROLLUP互斥）
SELECT s_id, f_price
FROM fruits
GROUP BY s_id
ORDER BY f_price;使用 LIMIT 限制查询结果的数量

# 从第4条记录开始的4条记录
SELECT *
FROM fruits
LIMIT 3,4;使用集合函数查询

COUNT()函数

SELECT COUNT(*) AS total
FROM fruits;
####
SELECT COUNT(s_id) AS total
FROM fruits;
#可与GROUP BY使用，计算每个分组的总数SUM()函数

AVG()函数

MAX()函数

MIN()函数

连接查询

关系型数据库主要特点，实现多表查询