专栏首页逸繁MySQL入门详解(一)---mysql的语言

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 ...

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • JS中的this指向问题

    可能会误以为window.doSth()是调用的,所以是指向window。虽然本例中window.doSth确实等于doSth。name等于window.nam...

    步履不停凡
  • MySQL入门详解(二)---mysql事务、锁、以及优化

    1.脏读:事务A读取了事务B更新的数据,然后B回滚操作,那么A读取到的数据是脏数据

    步履不停凡
  • js中var、let、const区别

    (2)当var关键字声明于函数外时是全局变量,此时不论在函数外部还是内部都可以访问到。

    步履不停凡
  • SQL命令

    MYSql在表(table_name)的指定位置(exist_field_name之后)添加字段(字段名,类型(长度): new_field_name,type...

    用户1396155
  • Database first with EntityFramework (Migration)安装和升级

    最近看了国外几个项目,发现用EntityFramework做Code First的项目现在很流行。 最让我有兴趣的一个功能则是,EntityFramework对...

    麦克-堂
  • sql语句中(+)的作用

    说明:以下示例中,表a是员工表,有a,b,c,d四个员工,性别都是男性m。表b是工资表,有a,b,d四个员工,工资对应的是1000,2000,4000。然后分别...

    职场亮哥
  • sql必会基础1

    ALTER TABLE old_table_name RENAME [TO] new_table_name

    Java架构师历程
  • 基于MTCNN的人脸自动对齐技术原理及其Tensorflow实现测试

            人脸识别是计算机视觉研究领域的一个热点。而人脸识别包含了诸多步骤,其实现技术流程如下图所示(摘自http://www.techshino.com...

    sparkexpert
  • Python操作Redis的最佳实践

    致码DevOps
  • Python+Selenium 技巧篇-svg标签内元素的xpath定位方式

    这种元素比较特殊,需要通过 name 属性来进行定位。 写法如下: //*[name()="svg"]//*[name()="image"]

    小蓝枣

扫码关注云+社区

领取腾讯云代金券