MySQL第二讲 一一一一 MySQL语句进阶

MySQL语言分为：DCL（数据库控制语言）、DDL（数据库定义语言）、DQL（数据库查询语言）、DML（数据库操作语言），这一节我们先从mysql的语言开始。

 

DCL：数据库控制语言，用来设置数据库用户或角色权限的语句，关键字grant revoke等

 

mysql -u 用户名 -p 密码 -h 服务器IP地址 -P服务器MySQL端口号 -D数据库名

 

当客户端连接到服务器是，MySQL访问控制有两个阶段：连接验证 对用户名密码，请求验证 对权限验证

 

默认表：MySQL安装时自动创建一个名为mysql的数据库，包含五个权限表：

 

user表包含用户账号密码主机信息 (Host：账号作用IP范围，%-为不限制)

 

db表包含某用户对数据库访问权限

 

table_priv表和columns_priv表包含表列级权限

 

procs_priv 表存储函数和存储过程权限

#创建新用户账户
CREATE USER username@hostname IDENTIFIED BY 'password'
"%.baidu.com","%"/"-"
​
#查看用户权限
SHOW GRANTS FOR username@hostname
​
#删除用户账户
DROP USER username@hostname[，username@hostname]
​
#添加权限
GRANT privilege[,privilege] ON privilege_level #指定一个或多个权限或'ALL' 在层级(表)
TO user [IDENTIFIED BY 'password'] #给某个用户
[REQUIRE tsl_option] #安全的连接
[WITH [GRANT_OPTION | resource_option]] #是否可以被改掉(选可以)
//GRANT SELECT,UPDATE,INSERT ON alibaba.* TO rfc;
​
#允许远程连接
grant all privileges on *.* to 'root@%' identified by '123456' with grant with grant option;
flush privileges; #刷新权限
​
#撤销权限
REVOKE privilege_type [(colimn_list)] [,privilege_type [(colimn_list)]]
ON [object_type] privilege_level 
FROM user[,user]
​
#修改密码
1.用SET PASSWORD命令：
登录之后
set password for username@host = '123456'
2.用UPDATE编辑user表：
use mysql;
update user set password=password('123456') where user='user';
flush privileges;
3.用mysqladmin：
mysqladmin -u用户名 -p旧密码 password 新密码
4.在忘记root密码或初始化密码时:
windows为例:
    关闭正在运行的MySQL服务
    打开DOS窗口，转到mysql的bin目录
    mysqld --skip-grant-tables  #此DOS窗口运行着跳过权限检查启动mysql的命令,8.0版本使用mysqld --console --skip-grant-tables --shared-memory
    再打开一个DOS窗口输入mysql回车，use mysql;
    改密码
    flush privileges;
    退出quit
    注销系统
​
#数据库备份
mysqldump -u[username] -p[password] [database_name] > [dump_file.sql]
mysqldump -u[username] -p[password] --no-date [database_name] > [dump_file.sql] #不包含数据只导出结构
mysqldump -u[username] -p[password] --no-create-info [database_name] > [dump_file.sql] #不包含结构只导出数据
mysqldump -u[username] -p[password] --all-database > [all_dbs_dump_file.sql] #导出全部数据库
​
#数据库维护
#分析表语句
ANALYZE TABLE 表名1[,表名2] #返回表名 操作 分析状态 和信息,会尝试修复
#优化表语句
OPTIMIZE TABLE tabe_name; #优化由于表操作导致的碎片问题
#检查表语句
CHECK TABLE table_name; #仅检查
#修复表语句
REPAIR TABLE table_name; #仅修复

DDL：数据库定义语言，用于描述数据库中创建库表，关键字create alter drop等

#创建数据库
CREATE DATABASE [IF NOT EXISTS] database_name;
​
#删除数据库
DROP DATABASE [IF EXISTS] database_name;
​
#创建表
CREATE TABLE [IF NOT EXISTS] table_name(
    colume_list
)engine=table_type;
#定义列
字段名 数据类型 整型宽度、字符大小极限65535 是否可空NOT NULL 是否自增auto_increment 默认值default 索引 主键primary key 注释等等
​
eg:create table tname(
id int(10) auto_increment primary key, #auto_increment自增 primary key主键
name varchar(255),
age varchar(10),
sex varchar(10)） default charset=utf8;
​
#修改表
ALTER TABLE table_name CHANGE column_name new_column_name(不改写原来的) varchar(200) not null;
ALTER TABLE table_name ADD column_name varchar(200) not null [FIRST | AFTER 字段];
ALTER TABLE DROP column_name;
#删除索引
ALTER TABLE 'table_name' DROP UNIQUE(index) name;
#添加索引
ALTER TABLE 'table_name' ADD UNIQUE(name) #唯一索引
ALTER TABLE 'table_name' ADD FULLTEXT(name) #全文索引
ALTER TABLE 'table_name' ADD INDEX(name) #普通索引
#修改引擎 
ALERT TABLE table_name ENGINE = InnoDB;

空值有NULL和None之分：NULL占了空间但内容是空的，None没有内容也不占空间 数据类型：

• 数值类型：TINYINT 1字节 范围255；SMALLINT 2字节 范围65535；MEDIUMINT 3字节 8388607；INT 4字节 2^32；BIGINT 8字节 2^64;FLOAT 4字节；DOUBLE 8字节；DECIMAL(M,D) M整体位数，D小数位数；

  UNSIGHED无符号 ；zerofill剩余宽度用0补齐

• 字符类型：CHAR 255字节(定长字符串,无论存多少内容255字节都是)；VARCHAR 0-65535字节；TINYBLOB 0-255字节 存储二进制字符串；TINYTEXT 0-255字节 短文本；BLOB 0-65535字节 长二进制字符串;TEXT 0-65535字节 文本；MEDIUM BLOB/TEXT...；ENUM 数据长度为1则为0,1,2；SET 集合；

• 时间类型：DATE 3字节 YYYY-MM-DD；TIME 3字节 HH:MM:SS；YEAR 1字节 YYYY；DATETIME 8字节 YYYY-MM-DD HH:MM:SS；TIMESTAMP 4字节 时间戳 默认插入/更新数据时的值；

• 空间数据类型：GEOMETRY POINT 等等

 

数值类型(宽度) 字符类型(长度) 枚举enum("1","0")

 

引擎类型：

• InnoDB：健壮的事务型存储引擎。表文件夹中存放.frm表结构文件 date文件夹中.ibdata1存放数据 .ibd存放索引
• MyISAM：访问速度很快，但不支持外键和事务。每个MyISAM在磁盘上生成三个文件 .frm 表结构信息 .MYD 数据 .MYI 存储索引

设置引擎:默认InnoDB 显示所有支持的引擎:SHOW ENGINEs 字符集:默认latin1

 

索引类型：

• 主键索引：primary

  在一个表中唯一，值不能重复，在数据查写读时能按一定顺序和排列工作

• 唯一键：unique

  一个表中可以同时给多个字段设置unique，设置了的字段值不能重复null除外

• 普通索引：index

  没有其他约束

  文本索引：fulltext

  5.7版本之后使用，加快大批量文本中的查找效率

• mysql外键

  副表的非主键字段与主表的主键字段关联，那么这个副表的非主键字段称为外键

  在副表中添加一个主键中不存在的数据，操作会报错
  ​
  如果在主表修改或删除，副表中有对应数据，主表会默认阻止

#创建外键
  [CONSTRAINT constraint_name] #约束名
  FOREIGN KEY [foreign _key_name] (columns)
  REFERENCES parent_table(columns_name)
  ON DELETE 模式 #模式：district默认严格模式 cascade关联 set null将管理数据设置null no action什么都不做
  ON UPDATE 模式;
  
  #添加外键
  ALTER TABLE table_name ADD constraint FK_ID foreign key(key_name) REFERENCES parent_table(columns_name);
  
  #删除外键
  ALTER TABLE table_name DROP FOREIGN KEY key_name

DQL：数据库查询语言，查询，关键字select等

SELECT
column_1,column_2,...
FROM table_1
[INNER | LEFT |RIGHT] JOIN table_2 ON CONDITIONS
WHERE conditions
GROUP BY column_1
HAVING group_conditions
ORDER BY column limit offset,length
​
#条件查询
select * from 表名 [where 条件];
select field1,field2,...fieldn... from 表名 [where 条件];
where后面的条件可以用>、<、>=、<=、!=等多种比较运算符，多个条件之间可以用or、and、not等逻辑运算符、
between begin_expr AND end_expr;<= >=;eg:BETWEEN CAST('2000-01-01' AS DATE) AND CAST('2001-01-01' AS DATE)、
LIKE 'value' #精准查询，可加% _ 如果需要可以加 ESCAPE'\'转译,此处\也可以指定为其他符号、in eg；
where in ...#不确定字符串包含关键字查找 FIND_IN_SET(要包含字符串，字段)、
group by 表达式，表达式... having group_conditions #GROUP BY分组 having分组后进行过滤 配合聚合函数avg()计算一组值平均值 count()计算表中行数 instr()返回子字符串在字符串中第一次出现位置 sun()计算一组数值综合 min()在一组数值中找到最小值 max()在一组数值中找到最大值、
order by asc升序 desc降序，多列排序时后一列是在前一列基础上排，自定义 field(column,"A","D","B","C")
​
#去重查询
select distinct 字段 from 表名;
eg: select distinct name from students;//查询名字不相同的学生;
　　select distinct name,age from students;//查询名字和年龄同时不同的学生
　　1.distinct必须放在最开头
　　2.distinct只能使用需要去重的字段进行操作。  ----也就是说我sidtinct了name,age两个字段，我后面想根据id进行排序，是不可以的，因为只能name,age两个字段进行操作.
　　3.distinct去重多个字段时，含义是：几个字段 同时重复 时才会被 过滤。
​
#排序查询
select * from 表名 [where 条件] [ order by  field1 [desc/asc],field2 [desc/asc]... ];
​
eg:select *from student order by age desc;//查询学生表并按年龄降序排列。
1.desc 降序排列，asc 升序排列
2.order by 后面可以跟多个不同的排序字段，每个排序字段都可以有不同的排序顺序。
3.如果排序字段的值一样，则相同的字段按照第二个排序字段进行排序。
4.如果只有一个排序字段，则字段相同的记录将会无序排列。
​
#限制查询
select ... [limit 起始偏移量,行数];
​
eg:select * from student order by mark desc limit 5;//取出成绩前五名的学生(省略了起始偏移量，此时默认为0)
默认情况下，起始偏移量为0，只写记录行数就可以。
​
#聚合查询
select 字段 fun_name from 表名 [where 条件] [group by field1,field2...] [with rollup] [having 条件];
1.fun_name 表示要做的聚合操作，也就是说聚合函数，常用的有 : sum(求和)、count(*)(记录数)、max(最大值)、min(最小值)。
2.group by关键字 表示要进行分类聚合的字段。比如要按照部门分类统计员工数量，部门就应该写在group by 后面。
3.with rollup 是可选语法，表明是否对分类聚合后的结果进行再汇总
4.having 关键字表示对分类后的结果再进行条件过滤。
​
#表连接
表连接分为内连接和外连接。
他们之间最主要的区别：内连接仅选出两张表中互相匹配的记录，外连接会选出其他不匹配的记录。
内连接:
... from table1 inner join table2 on table1.name=table2.name;
外连接 分为左连接和右连接
左连接：包含所有左边表中的记录，甚至是右边表中没有和他匹配的记录。
... from table1 left join table2 on table1.name=table2.name;
右连接：包含所有右边表中的记录，甚至是右边表中没有和他匹配的记录。
... from table1 right join table2 on table1.name=table2.name;
交叉连接：
...from table1 cross join table2 #产生a*b条，笛卡尔乘积 
​
#联合查询
把多个SELECT查询结果合并起来
select column_1 from table1 union select column_2 from table2
列名总显示第一个查询语句列名，默认清除重复项，union all不去重
​
#子查询
select * from table1 where id =(select column from table2 where ...)
select * from table1 where id = and exisit (select column from table2 where ...) #加上and exists 有就查，没有不会报错
​
#查询用户信息
select user()； #查看当前用户
select user,host,db,command FROM information_schema.processlist; #查询连接的用户信息
​
#查看查询语句运行效率
explain select语句

DML：数据库操作语言，插入更新删除，关键字insert update delete等

#插入
insert into tname(id,name) values ("1","xm") [ON DUPLICATE KEY UPDATE id=id+1];
​
#拷贝表，具有SELECT字句的INSERT
create table new_table_name like table_name;
insert into new_table_name select * from table_name;
​
#替换
replace into tname(id,name) values ("1","xm");
​
#更新
UPDATE [LOW_PRIORITY]#优先查询再更新 [IGNORE]#忽略错误 table_name SET column='value'
UPDATE table_name SET column=(select * from table_name)
​
#关联更新
updata table1,table2 set table1.arr=val,table2.arr=val where table1.column=value and table2.column=value
updata table1 join table2 set table1.column='val' , table2.column='val' where table1.col='value' and table2.col='value'
​
#删除
delete * from table_name [order by id desc] [limit row_count] [where ];
truncate * from table_name ; #这种方式效率很快，且重置自增
​
#关联删除
delete table1,table2 from table1 inner join table2 on...;
​
#设置写内存配置
max_allowed_packet=1M #限制接收的数据包大小，打的插入和更新会被限制导致失败
net_buffer_length=2K #insert语句缓存值2K-16M
bulk_insert_buffer_size=8M #一次性insert语句插入大小

常用内置函数

1.聚合函数
avg() #返回平均值，计算中忽略NULL
count() #返回行数，不忽略NULL
sum() #求和
min() 
max()
2.字符串函数
group_concat() #select group_cancat(column) from table group by .. 将各项连接
concat() # select cancat(column1,column2) from table 将内容连接起来 
concat_ws() # select cancat_ws("-",column1,column2) from table 将内容以-连接起来 
left() #从左边取x个字符 left("abcde","3") -> "abc"
replace() #替换 replace(整体字符串对象,被替换字符串，新字符串) update products set productsDescription = REPLACE(productsDescription,"old","new")
substring() #截取 substring(string,start[,length])start是从1开始计数的
trim() #trim( [ {both|leading|trailing} [removed_str] ],obj ) 参数：从两边去，前面去，后面去，去掉什么(不传为空格)
format() #保留位数 format(N,D[,locale]) N数字，D格式化小数位数,表示方式默认en_US
3.日期和时间函数
curdate() #返回当前日期 2000-01-01
now() #返回当前时间 2000-01-01 00:00:00 此时间只算程序执行时的时间
sysdate() #返回当前系统时间 2000-01-01 00:00:00 此时间时刻运算
sleep()
day() month() year() week()今年第几周 weekday()周一到周日对应0-6 dayname() #返回指定日期函数，设置dayname显示中文:SET @@lc_time_names='zh_CN'
datediff() #计算两时间之差天数 datediff('2000-01-01','2000-01-02')
timediff() #计算两时间之差时间返回00:00:00 参数可以是日期也可以是时间
timestampdiff() #timestampdiff(单位,start,end) 计算两时间之差返回单位可以是 day week month hour quarter second minute...
date_add()/datetime_add() #将时间间隔添加到date或datetime date_add(start,INTERVAL 数字 单位) 也可以例date_add(2000-01-01,INTERVAL '1:1' day_month)

视图

1.创建视图：
create view viewname as select ...
2.修改视图：
alter view viewname as select ...
或
create or replace view as select ...
3.删除视图:
drop view viewname;
4.查看视图：
show full tables;
​
临时表与视图区别：视图一直存在，而临时表存在于内存中当客户端会话结束时自动删除
创建临时表 create temporary table tempname select ...

 

 

 

通过命令来备份数据库：

通过数据库软件里面的，mysqldump模块来操作，如下：

　　mysqldump -u root db1 > db1.sql -p;　　　　 //没有-d就是备份的时候：数据表结构和数据都备份（下载）下载下来

　　mysqldump -u root -d db1>db1.sql -p;　　　　//有-d就是备份的时候就是：只是备份数据表结构

 

导入备份的sql文件到数据库里：

　　create database db5;

　　mysqldump -u root -d db5<db1.sql -p;　　　　//这句话的意思就是，把db1.sql这个备份了的的数据表，导入到db5中 。其实，就是备份中的大于号变成了小于号而已

 

MySQL数据库进阶

后续将结合案例来运用查询语句!!!!!

　　临时表：

(select * from 表名 where num>60) as B //这个就组成了临时表
　　　　用法：
　　　　　　select sid from (select * from score where num>60) as B ; //这里的sid必须是 * 这里的字段能找到，不然找不到之后就报错

 

 

 　　动态目标查询语句：

　　一种写法：
　　select 
   　　id,
　　   name,
　　   1,
　　   (select count(1) from tb)   //这句号叫做动态目标查询
　　from tb2
//值得注意的是，平时我们会经常用到count来做统计，所以在count的这里一般用count(1)或这count(id)来运用就可以解决问题

又是另一种方法：
　　SELECT 
  　　 student_id,
 　　  (select num from score as s2 where s2.student_id=s1.student_id and course_id = 1) as 语文,
 　　  (select num from score as s2 where s2.student_id=s1.student_id and course_id = 2) as 数学,
 　　  (select num from score as s2 where s2.student_id=s1.student_id and course_id = 3) as 英语
　　from score as s1;

　　case when作为select目标条件的语句：

         select 
            course_id,
            max(num),
            min(num),
            min(num)+1,
            case when min(num) <10 THEN 0 ELSE min(num) END as c   //“case when min(num) <10 THEN 0 ELSE min(num) END ”  有点像三元运算符
         from score GROUP BY course_id;
         
         select 
　　　　　　　　course_id,
　　　　　　　　avg(num),
　　　　　　　　sum(case when num <60 THEN 0 ELSE 1 END),
　　　　　　　　sum(1),
　　　　　　　　sum(case when num <60 THEN 0 ELSE 1 END)/sum(1) as jgl 
　　　　　from score 
　　　　　GROUP BY course_id 
　　　　　order by AVG(num) asc,jgl desc;

SQL语句的补充

之前我们学习了sql的大部分语句写法，这里我们再补充几个知识点。

记得我们之前讲了sql语句中的联表知识：left join 、right join、inner join

上面的这几个联表其实是属于：左右联表（join）

而与之对应的是：上下联表（union）

　　上下联表两种写法，以及他们之间的不同点：

　　　　

      -- 第1、union
　　　　　　　　-- 使用union的时候，如果两张表结合之后，存在的记录有值是一模一样的话，会自动删除后面重复的值
　　　　　　select id name from tb1
　　　　　　union
　　　　　　select sid sname from tb2

　　　　-- 第2、union all　　
　　　　　　　　-- 使用union all的时候，不会删除重复的记录　　　　　　　　
　　　　　　select id name from tb1
　　　　　　union all
　　　　　　select sid sname from tb2    

 

 

 

 

 

#创建新用户账户CREATE USER username@hostname IDENTIFIED BY 'password'"%.baidu.com","%"/"-"​#查看用户权限SHOW GRANTS FOR username@hostname​#删除用户账户DROP USER username@hostname[，username@hostname]​#添加权限GRANT privilege[,privilege] ON privilege_level #指定一个或多个权限或'ALL' 在层级(表)TO user [IDENTIFIED BY 'password'] #给某个用户[REQUIRE tsl_option] #安全的连接[WITH [GRANT_OPTION | resource_option]] #是否可以被改掉(选可以)//GRANT SELECT,UPDATE,INSERT ON alibaba.* TO rfc;​#允许远程连接grant all privileges on *.* to 'root@%' identified by '123456' with grant with grant option;flush privileges; #刷新权限​#撤销权限REVOKE privilege_type [(colimn_list)] [,privilege_type [(colimn_list)]]ON [object_type] privilege_level FROM user[,user]​#修改密码1.用SET PASSWORD命令：登录之后set password for username@host = '123456'2.用UPDATE编辑user表：use mysql;update user set password=password('123456') where user='user';flush privileges;3.用mysqladmin：mysqladmin -u用户名 -p旧密码 password 新密码4.在忘记root密码或初始化密码时:windows为例:    关闭正在运行的MySQL服务    打开DOS窗口，转到mysql的bin目录    mysqld --skip-grant-tables  #此DOS窗口运行着跳过权限检查启动mysql的命令,8.0版本使用mysqld --console --skip-grant-tables --shared-memory    再打开一个DOS窗口输入mysql回车，use mysql;    改密码    flush privileges;    退出quit    注销系统​#数据库备份mysqldump -u[username] -p[password] [database_name] > [dump_file.sql]mysqldump -u[username] -p[password] --no-date [database_name] > [dump_file.sql] #不包含数据只导出结构mysqldump -u[username] -p[password] --no-create-info [database_name] > [dump_file.sql] #不包含结构只导出数据mysqldump -u[username] -p[password] --all-database > [all_dbs_dump_file.sql] #导出全部数据库​#数据库维护#分析表语句ANALYZE TABLE 表名1[,表名2] #返回表名 操作 分析状态 和信息,会尝试修复#优化表语句OPTIMIZE TABLE tabe_name; #优化由于表操作导致的碎片问题#检查表语句CHECK TABLE table_name; #仅检查#修复表语句REPAIR TABLE table_name; #仅修复