mysql数据库维护_mysql数据库-基础--长期维护

###############    数据库    ##############

主要是通过这个学习到什么？1，库的操作2，表的操作，包括查询，多表查询，子查询3，视图，事务，索引，锁，

###############    数据库操作    ##############

"""数据库操作：

1.创建数据库

#创建一个名字为 db_name 的数据库,并指定当前库的编码集为utf8

CREATE DATABASE db_name charset utf8;

2.查看数据库

#查询当前用户下所有数据库

show databases;

3.选择数据库

USE db_name;

4.删除数据库

DROP DATABASE db_name;"""

###############    表操作    ##############

"""表操作：

1，创建表：

语法：

CREATE TABLE 表名(

字段名1 类型[(宽度) 约束条件],

字段名2 类型[(宽度) 约束条件],

字段名3 类型[(宽度) 约束条件]

)ENGINE=innodb DEFAULT CHARSET utf8;

实例：

create table student(

id int not null auto_increment primary key,

name varchar(250) not null,

age int not null,

sex enum('男','女') not null default '男',

salary double(10,2) not null

)engine=innodb default charset=utf8;

ps: not null :表示此列不能为空

auto_increment :表示自增长,默认每次增长+1

注意:自增长只能添加在主键或者唯一索引字段上

primary key :表示主键(唯一且不为空)

engine =innodb :表示指定当前表的存储引擎

default charset utf8 :设置表的默认编码集

2，查询表：

select name,sex from student;

或者: select * from student;

#查看表结构

例: desc student;

#查看创建表信息

show create table student;

3，修改表结构：

#添加表字段

alter table 表名 add 字段名 类型 约束;

例如: alter table student add age int not null default 0 after name;

ps: after name 表示在name字段后添加字段 age.

#修改表字段

方式一: alter table student modify 字段 varchar(100) null;

方式二: alter table student change 旧字段 新字段 int not null default 0;

ps:二者区别:

change 可以改变字段名字和属性

modify只能改变字段的属性

#删除表字段 :

alter table student drop 字段名;

#更新表名称:

rename table 旧表名 to 新表名;

4，删除表：

#删除表

drop table 表名;

#清空表

truncate table 表名;

5，复制表：

#只复制表结构和表中数据

CREATE TABLE tb2 SELECT * FROM tb1;

ps:主键自增/索引/触发器/外键 不会 被复制

#只复制表结构

create table tb2 like tb1;

ps: 数据/触发器/外键 不会被复制

6，数据类型：大致可以分为四类：数值、字符串类型、日期/时间和其他类型。

数值型

二进制类型: bit[(M)]

整数类型: tinyint[(m)] int[(m)] bigint[(m) 作用：存储年龄，等级，id，各种号码等

小数型: decimal[(m[,d])](特别的：对于精确数值计算时需要用此类型) FLOAT[(M,D)] DOUBLE[(M,D)] (数值越大，越不准确)

字符型: char (m) varchar(m) text text数据类型用于保存变长的大字符串，

枚举类型(了解): enum

集合类型(了解): set

日期/时间类型: DATE 日期值 TIME 时间值或持续时间 YEAR 年份值 DATETIME 混合日期和时间值 TIMESTAMP 时间戳"""

###############    表操作    ##############

"""数据操作：

1，插入数据：

#语法一: 按字段进行插入

insert into 表(字段1,字段2 ...) values (值1,值2 ...);

#语法二:按字段顺序插入

insert into 表 values (值1,值2 ...);

#语法三: 插入多条记录

insert into 表 values (值1,值2 ...) ,(值1,值2 ...) ,(值1,值2 ...);

#语法四:插入查询结果

insert into 表(字段1,字段2 ...) select 字段1,字段2 ... from 表;

2，更新数据：

#语法一: 更新整表数据

update 表 set 字段1= '值1', 字段2='值2' ... ;

#语法二:更新符合条件字段3的数据

update 表 set 字段1= '值1', 字段2='值2' ... where 字段3 = 值3;

3，删除数据：

#语法一:整表数据删除

delete from 表 ;

#语法二:删除符合 where后条件的数据

delete from 表 where 字段1=值1;"""

###############    单表查询    ##############

"""单表查询：

一.简单查询

#查询所有字段信息

select * from person;

#查询指定字段信息

select id,name,age,sex,salary from person;

#别名查询，使用的as关键字，as可以省略的

select name,age as'年龄',salary '工资' from person;

#直接对列进行运算,查询出所有人工资,并每人增加100块.

select (5/2);

select name, salary+100 from person;

#剔除重复查询

select distinct age from person;

二 条件查询

#比较运算符: > < >= <= = <>(!=) is null 是否为null

select * from person where age = 23;

select * from person where age <> 23;

select * from person where age is null;

select * from person where age is not null;

#逻辑运算符: 与 and 或 or

select * from person where age = 23 and salary =29000;

select * from person where age = 23 or salary =29000;

三 区间查询

# 使用 between...and 进行区间 查询

select * from person where salary between 4000 and 8000;

ps: between...and 前后包含所指定的值

等价于 select * from person where salary >= 4000 and salary <= 8000;

四 集合查询

#使用 in 集合(多个字段)查询

select * from person where age in(23,32,18);

等价于: select * from person where age =23 or age = 32 or age =18;

#使用 in 集合 排除指定值查询

select * from person where age not in(23,32,18);

五 模糊查询

#模糊查询 like %:任意多个字符, _:单个字符

#查询姓名以"张"字开头的

select * from person where name like '张%';

#查询姓名以"张"字结尾的

select * from person where name like '%张';

#查询姓名中含有"张"字的

select * from person where name like '%张%';

#查询 name 名称 是四个字符的人

select * from person where name like '____';

#查询 name 名称 的第二个字符是 'l'的人

select * from person where name like '_l%';

#排除名字带 a的学生

select * from student where name not like 'a%'

六 排序查询

升序:ASC 默认为升序

降序:DESC

PS:排序order by 要写在select语句末尾

#按人员工资正序排列,注意:此处可以省略 ASC关键字

select * from person order by salary ASC;

select * from person order by salary;

#工资大于5000的人,按工资倒序排列

select * from person where salary >5000 order by salary DESC;

#按中文排序

select * from person order by name;

#强制中文排序

select * from person order by CONVERT(name USING gbk);

ps:UTF8 默认校对集是 utf8_general_ci , 它不是按照中文来的。你需要强制让MySQL按中文来排序

七 聚合函数

聚合函数: 对列进行操作,返回的结果是一个单一的值,除了 COUNT 以外，都会忽略空值

COUNT：统计指定列不为NULL的记录行数；

SUM：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；

MAX：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；

MIN：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；

AVG：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；

#格式:

select 聚合函数(字段) from 表名;

#统计人员中最大年龄、最小年龄，平均年龄分别是多少

select max(age),min(age),avg(age) from person;

八 分组查询

#分组查询格式:

select 被分组的字段 from 表名 group by 分组字段 [having 条件字段]

ps: 分组查询可以与 聚合函数 组合使用.

#查询每个部门的平均薪资

select avg(salary),dept from person GROUP BY dept;

#查询每个部门的平均薪资 并且看看这个部门的员工都有谁?

select avg(salary),dept,GROUP_CONCAT(name) from person GROUP BY dept;

#GROUP_CONCAT(expr):按照分组，将expr字符串按逗号分隔，组合起来

#查询平均薪资大于10000的部门, 并且看看这个部门的员工都有谁?

select avg(salary),dept,GROUP_CONCAT(name) from person GROUP BY dept; having avg(salary)>10000;

九 分页查询

好处:限制查询数据条数,提高查询效率

#查询前5条数据

select * from person limit 5;

#查询第5条到第10条数据

select * from person limit 5,5;

#查询第10条到第15条数据

select * from person limit 10,5;

十 正则表达式

MySQL中使用 REGEXP 操作符来进行正则表达式匹配。

# ^ 匹配 name 名称 以 "e" 开头的数据

select * from person where name REGEXP '^e';

# $ 匹配 name 名称 以 "n" 结尾的数据

select * from person where name REGEXP 'n$';

# . 匹配 name 名称 第二位后包含"x"的人员 "."表示任意单个字符

select * from person where name REGEXP '.x';

# [abci] 匹配 name 名称中含有指定集合内容的人员

select * from person where name REGEXP '[abci]';

# [^alex] 匹配 不符合集合中条件的内容 , ^表示取反

select * from person where name REGEXP '[^alex]';

#注意1：^只有在[]内才是取反的意思，在别的地方都是表示开始处匹配

#注意2 : 简单理解 name REGEXP '[^alex]' 等价于 name != 'alex'

# 'a|x' 匹配 条件中的任意值

select * from person where name REGEXP 'a|x';

#查询以w开头以i结尾的数据

select * from person where name regexp '^w.*i$';

#注意:^w 表示w开头, .*表示中间可以有任意多个字符, i$表示以 i结尾

十一 SQL 语句关键字的执行顺序

select name, max(salary)

from person

where name is not null

group by name

having max(salary) > 5000

order by max(salary)

limit 0,5"""

###############    多表查询    ##############

"""多表查询：

一.多表联合查询

#多表查询语法

select 字段1,字段2... from 表1,表2... [where 条件]

注意: 如果不加条件直接进行查询,则会出现以下效果,这种结果我们称之为 笛卡尔乘积

#查询人员和部门所有信息

select * from person,dept where person.did = dept.did;

#注意: 多表查询时,一定要找到两个表中相互关联的字段,并且作为条件使用

二 多表连接查询

#多表连接查询语法(重点)

SELECT 字段列表

FROM 表1 INNER|LEFT|RIGHT JOIN 表2

ON 表1.字段 = 表2.字段;

1 内连接查询 (只显示符合条件的数据)

#查询人员和部门所有信息

select * from person

inner join dept

on person.did =dept.did;

效果: 大家可能会发现, 内连接查询与多表联合查询的效果是一样的.

2 左外连接查询 (左边表中的数据优先全部显示)

#查询人员和部门所有信息

select * from person

left join dept

on person.did =dept.did;

效果:人员表中的数据全部都显示,而 部门表中的数据符合条件的才会显示,不符合条件的会以 null 进行填充.

3 右外连接查询 (右边表中的数据优先全部显示)

#查询人员和部门所有信息

select * from person

right join dept

on person.did =dept.did;

效果:正好与[左外连接相反]

4 全连接查询(显示左右表中全部数据)

全连接查询：是在内连接的基础上增加 左右两边没有显示的数据

注意: mysql并不支持全连接 full JOIN 关键字

注意: 但是mysql 提供了 UNION 关键字.使用 UNION 可以间接实现 full JOIN 功能

#查询人员和部门的所有数据

SELECT * FROM person LEFT JOIN dept ON person.did = dept.did

UNION

SELECT * FROM person RIGHT JOIN dept ON person.did = dept.did;

三 复杂条件多表查询

1. 查询出 教学部 年龄大于20岁,并且工资小于40000的员工,按工资倒序排列.(要求:分别使用多表联合查询和内连接查询)

#1.多表联合查询方式:

select * from person p1,dept d2 where p1.did = d2.did

and d2.dname='python'

and age>20

and salary <40000

ORDER BY salary DESC;

#2.内连接查询方式:

SELECT * FROM person p1 INNER JOIN dept d2 ON p1.did= d2.did

and d2.dname='python'

and age>20

and salary <40000

ORDER BY salary DESC;

2.查询每个部门中最高工资和最低工资是多少,显示部门名称

select MAX(salary),MIN(salary),dept.dname from

person LEFT JOIN dept

ON person.did = dept.did

GROUP BY person.did;

3，三张表查询，sql如下：,

select a.uid,a.uname,a.upsw,a.urealname,a.utel,a.uremark, b.rid,b.rname,b.rremark,c.deptid,c.deptname,c.deptremark

from table1 a,table2 b,table3 c

where a.sems_role_rid=b.rid and a.udeptid=c.deptid

或者：

select a.uid,a.uname,a.upsw,a.urealname,a.utel,a.uremark, b.rid,b.rname,b.rremark,c.deptid,c.deptname,c.deptremark

from table1 a

left join table2 b on a.sems_role_rid=b.rid

left join table3 c on a.udeptid=c.deptid

LEFT JOIN 可以实现统一数据库多表联合查询符合条件的数据。

四 子语句查询

1.作为表名使用

select * from (select * from person) as 表名;

2，作为字段的值

select name,salary from person where salary=(select max(salary) from person);

五 SQL逻辑查询语句执行顺序(重点***)

SELECT DISTINCT

FROM

JOIN

ON

WHERE

GROUP BY

HAVING

ORDER BY

LIMIT """

###############   约束    ##############

"""约束

外键约束

1，创建表时,同时创建外键约束

CREATE TABLE IF NOT EXISTS dept (

did int not null auto_increment PRIMARY KEY,

dname VARCHAR(50) not null COMMENT '部门名称'

)ENGINE=INNODB DEFAULT charset utf8;

CREATE TABLE IF NOT EXISTS person(

id int not null auto_increment PRIMARY KEY,

name VARCHAR(50) not null,

age TINYINT(4) null DEFAULT 0,

sex enum('男','女','人妖') NOT NULL DEFAULT '人妖',

salary decimal(10,2) NULL DEFAULT '250.00',

hire_date date NOT NULL,

dept_id int(11) DEFAULT NULL,

CONSTRAINT fk_did FOREIGN KEY(dept_id) REFERENCES dept(did) -- 添加外键约束

)ENGINE = INNODB DEFAULT charset utf8;

2，已经创建表后,追加外键约束

#添加外键约束

ALTER table person add constraint fk_did FOREIGN key(dept_id) REFERENCES dept(did);,

#删除外键约束

ALTER TABLE person drop FOREIGN key fk_did;

注：插入数据时，先插入主表中的数据，再插入从表中的数据。

删除数据时，先删除从表中的数据，再删除主表中的数据。

###########################################

其他约束类型：

1.非空约束

关键字: NOT NULL ,表示 不可空. 用来约束表中的字段列

create table t1(

id int(10) not null primary key,

name varchar(100) null

);

2.主键约束

用于约束表中的一行，作为这一行的标识符，在一张表中通过主键就能准确定位到一行，因此主键十分重要。

create table t2(

id int(10) not null primary key

);

注意: 主键这一行的数据不能重复且不能为空。

3.唯一约束

关键字: UNIQUE, 比较简单，它规定一张表中指定的一列的值必须不能有重复值，即这一列每个值都是唯一的。

create table t4(

id int(10) not null,

name varchar(255) ,

unique id_name(id,name)

);

//添加唯一约束

alter table t4 add unique id_name(id,name);

//删除唯一约束

alter table t4 drop index id_name;

4.默认值约束

关键字: DEFAULT

create table t5(

id int(10) not null primary key,

name varchar(255) default '张三'

);

#插入数据

INSERT into t5(id) VALUES(1),(2);

注意: INSERT语句执行时.，如果被DEFAULT约束的位置没有值，那么这个位置将会被DEFAULT的值填充"""

###############   约束    ##############