Error message here!

Hide Error message here!

忘记密码?

Error message here!

请输入正确邮箱

Hide Error message here!

密码丢失?请输入您的电子邮件地址。您将收到一个重设密码链接。

Error message here!

返回登录

Close

聊聊数据库~3.SQL基础篇

鲲逸鹏 2019-02-19 11:14:00 阅读数:224 评论数:0 点赞数:0 收藏数:0

上篇回顾:聊聊数据库~SQL环境篇

扩展:为用户添加新数据库的权限

PS:先使用root创建数据库,然后再授权

grant all privileges on 数据库./* to 用户名@"%" identified by "密码";并刷新

flush privileges;

3.1.为用户添加新数据库权限.png

查看权限:

show grants for dnt;3.2.查看权限.png

效果:3.3.效果.png

1.3.MySQL部署

之前有园友说,为啥不顺便说说

UbuntuServer的部署呢?呃。。。一般来说公司服务器都是

CentOS的占大多数,然后

UbuntuServer更多的是个人云服务比较多(推荐初创公司使用),毕竟它们两个系统追求的不太一样,一个是追求稳(部署麻烦),一个是追求软件尽量新的情况下稳定(更新太快)

那么长话短说,步入正轨:

1.Ubuntu最常见的包问题

Ubuntu不得不说的就是这个**apt出问题的处理** :(换源就不说了

/etc/apt/sources.list)/# 一般删除这几个锁文件,然后再重新配置下就可以了 sudo rm /var/lib/dpkg/lock sudo rm /var/lib/dpkg/lock-frontend sudo rm /var/lib/apt/lists/lock sudo rm /var/cache/apt/archives/lock /# 简写(千万注意空格,不然你就是rm -rf / + 跑路了) /# sudo rm /var/lib/apt/lists/lock /var/cache/apt/archives/lock /var/lib/dpkg/lock /var/lib/dpkg/lock-frontend /# 重新配置下 sudo dpkg --configure -a

2.安装注意(Ubuntu的特点就是使用起来简单)

Ubuntu推荐使用

MySQL(毕竟同是

5.x用起来基本上差不多,安装过程和之前说的

CentOS 下 MariaDB差不多,所有命令前加个**sudo**)

1.安装比较简单:

sudo apt install mysql-server -y2.1.Ubuntu.png

2.允许远程连接:

注释掉 bind-address=127.0.0.1(

/etc/mysql/mysql.conf.d/mysqld.cnf)2.2.远程连接.png

PS:常用配置(

/etc/mysql/mysql.conf.d/mysqld.cnf)2.2.1.常用配置.png

3.关于为什么是这个路径的说明:

sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf2.3.为什么是这个路径.png

4.所有配置修改都需要重新启动下:

sudo systemctl restart mysql2.4.记得重启一下

5.第一次初始化和MariaDB不太一样:

sudo mysqlsecureinstallation(其他一路

y即可)

需要选下你设置root密码的复杂度:(一般1就可以了,就算设置再复杂,入了系统也是虚的)2.5.初始化-注意下密码复杂程度

PS:可以看看拓展文章:Ubuntu16安装mysql5.7未提示输入密码,安装后修改mysql默认密码【不推荐】修改mysql密码策略

6.然后输入密码你就可以登录了

sudo mysql -uroot -p(PS:你直接

sudo mysql也可以直接登录)

这边我就不像上节课一步步演示了,直接授权和创建一步走了

grant all privileges on 数据库./* to "用户名"@"%" identified by "复杂密码";2.6.授权创建一步走.png

7.记得

flush privileges;刷新一下系统表2.7.测试.png

PS:数据文件一般都是放在

/var/lib/mysql中

课后拓展:

浅析MySQL 8忘记密码处理方式 https://www.cnblogs.com/wangjiming/p/10363357.html MySQL5.6更改datadir数据存储目录 https://www.cnblogs.com/ding2016/p/7644675.html

1.4.基础(MySQL and SQLServer)

脚本示例:https://github.com/lotapp/BaseCode/tree/master/database/SQL

**PS:在MySQL中运行SQL脚本:

mysql < script.sql**

后面代码优先使用通用SQL(

MySQL和

SQLServer(

MSSQL)通用的SQL语句),逆天好几年没用

SQLServer了(几年前讲过MSSQL),这边就一带而过(欢迎纠错)

**PS:后面

MariaDB我也直接说成

MySQL了(有区别的地方我会说下,毕竟也是MySQL的分支,相似度还是很大的)**

1.概念

1.1.传统概念

来说说传统概念:

  1. 关系型数据库中的关系:表(行、列)
  2. 设计范式
  • 第1范式:字段是原子性的
  • 第2范式:每个表需要一个主键
  • 第3范式:任何表都不应该有依赖于其他非主键表的字段
  • DDL:数据定义语言(Data Defination Language)
  • create、drop、alter
  • DML:数据操作语言(Data Manipulation Language)
  • **
    insert、delete、update、select
    **
  • DCL:数据库控制语言(Data Control Language)
  • grant
    (授权)、

revoke(回收)

**PS:

CURD(定义了用于处理数据的基本原子操作):创建(Create)更新(Update)读取(Retrieve)删除(Delete)操作**

1.2.常见组件

关系型数据库常见组件:

  1. 数据库:database
  2. :table
  • 行:row
  • 列:column
  • 索引:index
  • 视图:view
  • PS:如果有

数据库迁移的需求则不建议使用

  • PS:MySQL的视图功能不是特别完素,尽量不使用
  • 存储过程:procedure
  • 存储函数:function
  • 触发器:trigger
  • 事件调度器:event、scheduler
  • 用户:user
  • 权限:privilege

PS:MySQL常见的文件类型:

  1. 数据:数据文件、索引文件
  2. 日记:错误日志、查询日志、慢查询日志、二进制日志、(重做日志、撤销日志、中继日志)

2.MySQL标准存储引擎

2.1.MySQL

先说说

MySQL标准存储引擎(**表类型**):

  1. **
    MyISAM
    **:只支持

表级锁,不支持

事务

  1. **
    InnoDB
    **:支持

事务、

间隙锁、

行锁等等

2.2.MariaDB

首先是**插件式存储引擎(

表类型)的改进和扩充** PS:其实也就是支持更多的存储引擎(包括自定义的)

MariaDB对标准存储引擎进行了改造升级:

1.MyISAM==>

Aria:支持崩溃后的恢复1.InnoDB==> **XtraDB**:优化存储性能

还进行了很多扩展并开发了新的功能(也提供了很多测试工具),比如添加一些

NoSQL的功能(

SQLServer也扩展了

NoSQL)

3.创建、删除(数据库 | 表)

字段类型(含异同)

官方文档:

  • https://mariadb.com/kb/en/library/data-types
  • https://dev.mysql.com/doc/refman/5.7/en/data-types.html

MariaDB为例,简单列举下常用类型:(倾体说明和

MySQL不一样)

  1. 字符型:
  2. 定长字符型:
  • **
    char()
    **:不区分字符大小写类型的字符串,

max:255个字符

  • binary():区分字符大小写类型的二进制字符串
  • 变长字符型:
  • **
    varchar()
    **: 不区分字符大小写类型的字符串
  • max:65535(2^16 - 1)个字节(

utf8编码下最多支持21843个字符)

  • 可以理解为

SQLServer的

nvarchar

  • varbinary():区分字符的大小写类型的二进制字符串
  • 对象存储:
  • **
    text
    **:不区分字符大小写的不限长字符串
  • 最大长度为65,535(2^16 - 1)个字符
  • 如果值包含多字节字符,则有效最大长度减少
  • blob:区分字符大小写的不限长二进制字符串
  • 内建类型:(不推荐使用)
  • enum:单选字符串数据类型,适合表单中的

单选值

  • set:多选字符串数据类型,适合表单的

多选值

  • **PS:

MySQL系独有,

SQLServer没有**

  • 数值型:
  1. 精确数值型:
  • 整型:int
  1. bool:布尔类型(MySQL没有)
  • **PS:

SQLServer是

bit**

  • **相当于

MySQL的

tinyint(1)**

  • **
    tinyint
    **:微小整型(1字节,8位)
  • [-2^7, 2^7)
    (

-128 ~ 127)

  • 无符号:

[0, 2^8)(

0 ~ 255)

  • smallint(2bytes,16bit):小整型
  • 无符号:

0 ~ 65535

  • mediumint(3bytes,24位):中等整型
  • PS:SQLServer中没这个类型
  • **
    int
    **(4bytes,32bit)
  • [-2^31, 2^31)

[-2147483648,2147483648)

  • 无符号:

[0, 2^32),

[0,4294967296)

  • **
    bigint
    **(8bytes,64bit)
  • [-2^63, 2^63)
  • 无符号:

[0, 2^64)

  • 浮点类型:
  • float:单精度浮点数(4字节)
  • **
    double
    **:双精度浮点数(8字节)
  • **PS:

SQLServer的

float类型相当于

MySQL的

double**

  • **
    decimal
    **:精确小数类型(比double更精确)
  • 钱相关用的比较多:

decimal(位数,小数点位数)

  • eg:

decimal(2,1)=>

x.x

  • 日期和时间类型:(和

MySQL一样)

  1. date:日期(3bytes)
  2. time:时间(3bytes)
  3. year:年
  • eg:

year(2):

00~99(1bytes)

  • eg:

year(4):

0000~9999(1bytes)

  • **PS:

SQLServer没有这个类型**

  • **
    datetime
    **:既有时间又有日期(8bytes)
  • **
    timestamp
    **:时间戳(4bytes)【精度更高】
  • 修饰符:
  • 所有类型都适用:
  • 是否为null:

null|

not null

  • 默认值:

default xxx_value

  • 主 键:

primary key

  • 唯一键:

unique key

  • 数值类型适用:
  • **无符号:

unsigned**(MySQL系独有)

  • 自增长:**
    auto_increment
    ** (一般只用于整型,MSSQL是

identity)

  • 获取ID:

lastinsertid()

  • PS:多列设置
  1. 主键:

primary key(xx,...)

  1. 唯一键:

unique key(xx,...)

  1. 索引:

index index_name (xx,...)

PS:现在新版本数据库兼容了SQLServer的

nvarchar写法(

执行成功后数据类型变成varchar)【不推荐使用】

课后拓展:MySQL:char、varchar、text的区别 https://dev.mysql.com/doc/refman/5.7/en/char.html https://blog.csdn.net/brycegao321/article/details/78038272

3.1.MySQL

知识点概括:

  1. 创建数据库:
  • create database [if not exists] db_name;
  • 删除数据库:
  • drop database [if exists] db_name;
  • 创建表:
  • create table [if not exists] tb_name(列名1,数据类型 修饰符,列名2,数据类型 修饰符);
  • 删除表:
  • drop table [if exists] db_name.tb_name;
  • 修改表:
  1. 字段
  • 添加字段:add
  • alter table tb_name add 列名 数据类型 修饰符 [first | after 列名];
  • **PS:SQLServer没有

[first | after 列名]**

  • 修改字段:alter、change、modify
  • 修改字段名:

alter table tb_name change 旧列名 新列名 类型 类型修饰符

  • 修改字段类型:

alter table tb_name modify 列名 类型 类型修饰符

  • 添加默认值:

alter table tbname alter 列名 set default dfvalue

  • 删除字段:drop
  • alter table tb_name drop 字段名
  • 索引
  • 添加索引:add(常用:**
    create index indexname on tbname(列名,...);
    **)
  • alter table tbname add index [ixname] (列名,...);
  • 添加唯一键:

alter table tbname add unique [uqname] (列名,列名2...);

  • PS:不指定索引名字,默认就是第一个字段名
  • 删除索引:drop(常用:**
    drop index indexname on tbname
    **)
  • alter table tbname drop index indexname;
  • 删除唯一键:

alter table tbname drop index uqname;

  • PS:唯一键的索引名就是第一个列名
  • PS:一般在经常用做查询条件的列设置索引
  • 表选项
  • 可以参考这篇文章:

https://www.cnblogs.com/huangxm/p/5736807.html

  • **
    SQL Model
    **:定义MySQL对约束的响应行为:
  • 会话修改:
  • mysql>

set [session] sql_model='xx_mode'

  • mysql>

set @@session.sqlmode='xxmode'

  • PS:只在当前会话生效
  • 全局修改:需要有权限,并且不会立即生效,对以后新建的会话生效(从全局继承的)
  • mysql>

set global sqlmode='xxmode'

  • mysql>

set @@global.sqlmode='xxmode'

  • PS:MySQL重启后失效
  • 配置修改:永远生效:
  • eg:

vi /etc/my.cnf,在

[mysqld]下添加

sql_mode='xx',然后重启数据库

  • 常用mode:(阿里服务器默认是:

stricttranstables)

  • **
    traditional
    **:使用传统模型,不允许对非法值做插入操作
  • **
    stricttranstables
    **:对所有支持事物类型的表做严格约束
  • strictalltables
    :对所有表做严格约束
  • 查询当前设置:**
    select @@sql_mode
    **
  • 详情可以查看我之前写的文章:https://www.cnblogs.com/dotnetcrazy/p/10374091.html

3.1.1.创建、删除数据库

-- 如果存在就删除数据库 drop database if exists dotnetcrazy; -- 创建数据库 create database if not exists dotnetcrazy;

3.1.2.创建、删除表

-- 如果存在就删除表 drop table if exists dotnetcrazy.users; -- mysql> help create table(低版本的默认值不支持函数) -- 创建表 create table users(字段名 类型 修饰符,...) create table if not exists dotnetcrazy.users ( id int unsigned autoincrement, -- 主键,自增长【获取ID:lastinsertid()】 username varchar(20) not null, password char(40) not null, -- sha1:40 email varchar(50) not null, ucode char(36) not null,-- default uuid(), -- uuid createtime datetime not null,-- default now(), updatetime datetime not null,-- default now(), datastatus tinyint not null default 0, -- 默认值为0 primary key (id), -- 主键可多列 unique uqusersemail (email), index ixuserscreatetimeupdatetime (createtime, updatetime) -- 索引,不指定名字默认就是字段名 ) -- 表选项 -- engine = 'innodb', -- 引擎 -- character set utf8, -- 字符集 -- collate utf8generalci, -- 排序规则 ;

3.1.3.修改表

-- 修改表 mysql> help alter table -- 3.1.添加一列 alter table tbname add 列名 数据类型 修饰符 [first | after 列名] alter table dotnetcrazy.users add uid bigint not null unique first; -- MSSQL没有[first | after 列名] -- 在email后面添加手机号码列 -- 手机号不会用来做数学运算,varchar可以模糊查询(eg:like ‘138%’) -- 牵扯到国家代号时,可能出现+、-、()等字符,eg:+86 alter table dotnetcrazy.users add tel varchar(20) not null after email; -- 3.2.删除一列 alter table tbname drop 字段名 alter table dotnetcrazy.users drop uid; -- 3.3.添加索引 alter table tbname add index [ixname] (列名,...) alter table dotnetcrazy.users add index ixusersucode (ucode); -- 不指定名字默认就是字段名 -- add index (ucode, tel); -- 不指定索引名字,默认就是第一个字段名 -- 添加唯一键 alter table tbname add unique [uqname] (列名,列名2...) alter table dotnetcrazy.users add unique uquserstelucode (tel, ucode); -- add unique (tel, ucode);-- 不指定索引名字,默认就是第一个字段名 -- 3.4.删除索引 alter table tbname drop index ixname alter table dotnetcrazy.users drop index ixusersucode; -- 删除索引(唯一键) alter table tbname drop index uqname alter table dotnetcrazy.users drop index uquserstelucode; -- drop index tel; -- 唯一键的索引名就是第一个列名 -- 3.5.修改字段 -- 1.修改字段名:alter table tb_name change 旧列名 新列名 类型 类型修饰符 -- 此时一定要重新指定该列的类型和修饰符 alter table dotnetcrazy.users change ucode usercode char(36); -- default uuid(); -- 2.修改字段类型 alter table dotnetcrazy.users modify username varchar(25) not null; -- 3.添加默认值:alter table tb_name alter 列名 set default df_value alter table dotnetcrazy.users alter password set default '7c4a8d09ca3762af61e59520943dc26494f8941b';

3.2.SQLServer

示例服务器:

SQLServer 2014

3.2.1.创建、删除数据库

use master --存在就删除 if exists(select /* from sysdatabases where Name = N'dotnetcrazy') begin drop database dotnetcrazy end --创建数据库(简化版:create database dotnetcrazy) create database dotnetcrazy on primary --数据库文件,主文件组 ( name ='dotnetcrazy_Data', --逻辑名 size =10 mb, --初始大小 filegrowth =10%, --文件增长 maxsize =1024 mb, --最大值 filename =N'D:WorksSQLdotnetcrazy_data.mdf'--存放路径(包含文件后缀名) ) log on --日记 ( name ='dotnetcrazy_Log', size =5 mb, filegrowth =5%, filename =N'D:WorksSQLdotnetcrazy_log.ldf' ); -- 切换数据库 use dotnetcrazy;

3.2.2.创建、删除表

--存在就删除表 if exists(select /* from sysobjects where name = N'users') begin drop table users end -- dotnetcrazy.dbo.users create table users ( id int identity, -- 主键,自增长 username nvarchar(20) not null, email varchar(50) not null, password char(40) not null, -- sha1 ucode char(36) not null default newid(), -- guid createtime datetime not null default getdate(), updatetime datetime not null default getdate(), datastatus tinyint not null default 0, -- 默认值为0 primary key (id), -- 主键可多列 unique (email), index ix_users_createtime_updatetime (createtime, updatetime) -- 索引 );

3.1.3.修改表

-- 3.1.添加一列 alter table tbname add 列名 数据类型 修饰符 -- 在email后面添加手机号码列 alter table users add tel varchar(20) not null; -- 3.1.1.添加含唯一键的列 -- 先添加列 alter table users add uid bigint not null -- 再添加约束 alter table tbname add constraint uqname alter table users add constraint uqusersuid unique (uid); -- 自定义名称 -- 3.1.2.定义和约束一步走(系统设置名字) -- alter table users -- add uid bigint not null unique; -- 默认名称 -- 3.2.含唯一键的列 -- 3.2.1.删除约束 alter table tbname drop constraint uqname if exists(select /* from sysobjects where name = 'uqusersuid') alter table users drop constraint uqusersuid; -- 3.2.2.删除列 alter table tbname drop column 字段名 alter table users drop column uid; -- 3.3.修改字段 -- 3.3.1.修改列名:exec sprename '表名.旧列名','新列名'; exec sprename 'users.ucode', 'usercode'; -- 3.3.2.修改字段类型 alter table users alter column username varchar(25) not null; -- 3.3.3.添加默认值:alter table tb_name alter 列名 set default df_value alter table users add default '7c4a8d09ca3762af61e59520943dc26494f8941b' for password;

知识回顾:

  1. 01.SQLServer性能优化之---强大的文件组(分盘存储)
  2. 02.SQLServer性能优化之---水平分库扩展
  3. 03.SQLServer性能优化之---存储优化系列

课后拓展:SQLServer突破内存限制: https://www.cnblogs.com/zkweb/p/6137423.html 官方demo: https://www.microsoft.com/en-us/sql-server/developer-get-started/python/ubuntu 官方文档: https://docs.microsoft.com/zh-cn/sql/linux/sql-server-linux-overview?view=sql-server-2017 PS:SQL Server默认端口为TCP 1433

3.3.区别

简单列举下上面的区别(欢迎补充):

  1. **MySQL自增长是

auto_increment,MSSQL是

identity**

  1. **MySQL可以设置无符号

unsigned,MSSQL不可以直接设置无符号整型,需要通过约束之类的来限制**

  1. **
    alter table
    的时候,MSSQL没有

[first | after 列名],而且语法差别也挺大**

4.增删改查(CURD)

4.1.MySQL

select语句执行流程

1.from 表1.[inner|left|right] join 表 on 条件1.where 条件

  • 对select的结果进行过滤
  • group by 字段
  • **根据指定条件把查询结果进行

分组,以用做

聚合运算**

  • having 条件
  • **对分组聚合运算(

group by)后的结果进行过滤**

  • order by 字段 [asc|desc]
  • 根据指定字段对查询结果进行排序(默认升序

asc)

  • select 字段
  • limit [偏移量,]显示数量
  • 显示多少条数据 | 分页显示

增删改

-- 4.1.插入 help insert -- 自增长主键和默认值的字段可以不写 insert into dotnetcrazy.users(username, password, email, tel, usercode, createtime, updatetime, datastatus) values ('dnt', '7c4a8d09ca3762af61e59520943dc26494f8941b', 'dnt@qq.com', '18738002038', uuid(), now(), now(), 1); -- 批量插入 insert into dotnetcrazy.users(username, password, email, tel, usercode, createtime, updatetime, datastatus) values('xxx', '7c4a8d09ca3762af61e59520943dc26494f8942b', 'xxx@qq.com', '13738002038', uuid(), now(), now(), 0),('mmd', '7c4a8d09ca3762af61e59520943dc26494f8941b', 'mmd@qq.com', '13718002038', uuid(), now(), now(), 1),('小张', '7c4a8d09ca3762af61e59520943dc26494f8941b', 'zhang@qq.com', '13728002038', uuid(), now(), now(), 1); -- 4.2.修改 help update update dotnetcrazy.users set datastatus=99, updatetime = now() where username = 'mmd'; -- 一定要有where条件!开发中一般都是先写where条件再写update -- 4.3.删除 -- 删除数据(自增长不重置)help delete; delete from dotnetcrazy.users where datastatus = 0; -- 删除全部数据(自增长重置)help truncate; truncate table dotnetcrazy.users;

查询

-- 数据构造见附录 -- 4.4.查询 help select -- 查询来源url(去重后) select distinct url from filerecords; -- 查询来源url(分组方式) select url from filerecords group by url; -- 分别统计一下url出现的次数(分组+聚合) -- 分组一般都和聚合函数一起使用 select url, count(/) as count from file_records group by url; -- 分别统计一下url出现的次数,已经删除的文件不算进去 select url, count(/) as count from filerecords group by url having count > 3; -- 在group by的结果上筛选 -- 分别统计一下url出现的次数并查出对应的id select groupconcat(id) as ids, url from filerecords group by url; -- 内连接查询 innet join tbname on 关联条件 select filerecords.id, users.id as uid, users.username, users.email, filerecords.filename, filerecords.md5, inetntoa(filerecords.ip) as ip, filerecords.url from users inner join filerecords on filerecords.userid = users.id -- 连接条件 where users.datastatus = 1 and filerecords.datastatus = 1 order by filerecords.filename desc; -- 文件名降序排序 -- MySQL没有select top n语法,可以使用 limit来实现,eg:top 5 select /* from filerecords limit 5; -- limit 0,5 -- 分页查询 -- page:1,count=5 ==> 0,5 ==> (1-1)/5,5 -- page:2,count=5 ==> 5,5 ==> (2-1)/5,5 -- page:3,count=5 ==> 10,5 ==> (3-1)/5,5 -- 推理:limit (page-1)/count,count select filerecords.id, users.id as uid, users.username, users.email, filerecords.filename, filerecords.md5, inetntoa(filerecords.ip) as ip, filerecords.url from filerecords inner join users on filerecords.userid = users.id limit 0,5; -- limit后面跟表达式就会报错 select filerecords.id, users.id as uid, users.username, users.email, filerecords.filename, filerecords.md5, inetntoa(filerecords.ip) as ip, filerecords.url from filerecords inner join users on filerecords.userid = users.id limit 5,5; -- limit (2-1)/*5,5; -- limit错误写法 -- limit要放在最后 select file_records.id, users.id as uid, users.username, users.email, file_records.file_name, file_records.md5, inet_ntoa(file_records.ip) as ip, file_records.url from file_records inner join users on file_records.user_id = users.id order by username desc, file_name desc limit 10,5; -- 先order by排完序,然后再取第三页的5个数据 -- 查找一下从来没上传过文件的用户 -- right join:以右边表(users)为基准连接 select file_records.id as fid, users.id as uid, users.username, users.email, file_records.file_name, file_records.md5, inet_ntoa(file_records.ip) as ip, file_records.url from file_records right join users on file_records.user_id = users.id where users.datastatus = 1 and file_records.id is null order by username desc, file_name desc; -- 自连接案例: -- 二级联动 p:province,c:city,a:area -- 前端一般都会显示省级信息,用户选择后可以获得对应的二三级信息 select c.name, a.name from city_infos as c inner join city_infos as a on a.pcode = c.code where c.pcode = '320000'; -- pcode设置为索引 -- 通过省名称查询 select p.name, c.name, a.name from city_infos as c inner join city_infos as p on c.pcode = p.code inner join city_infos as a on a.pcode = c.code where p.name = '江苏省';

视图

-- 简单提一下视图: -- 创建视图 create view viewuserinfo as select id, username, password, email, tel, datastatus from dotnetcrazy.users; -- 查询视图 select id, username, password, email, tel, datastatus from dotnetcrazy.viewuserinfo; -- 删除视图 drop view if exists view_userinfo;

附录

知识点:-- 把ip转换成int select inetaton('43.226.128.3'); -- inet6aton() -- 把int转换成ip select inetntoa('736264195'); -- inet6ntoa() ipv6 -- 将多个字符串连接成一个字符串 select concat(userid, ',', filename, ',', ip, ',', url) as concatstr from filerecords; -- 将多个字符串连接成一个字符串+可以一次性指定分隔符 select concatws(',', userid, filename, ip, url) as concatstr from filerecords; -- 在有group by的查询语句中,select指定的字段要么就包含在group by语句的后面,作为分组的依据,要么就包含在聚合函数中 -- groupconcat():将group by产生的同一个分组中的值连接起来,返回一个字符串结果 select groupconcat(filename) as filename, url, count(/*) from filerecords group by url; -- having一般对group by的结果进行筛选,where是对原表进行筛选 select groupconcat(filename) as filename, groupconcat(url) as url, count(/*) as count from file_records group by url having count >= 3; -- 四舍五入到指定位数 select round(3.12345, 4); -- 存小数数据为了不损伤精读一般都是转成整数,eg:3.1415 ==> 整数:31415,倍数:10000

数据构造

city_data.sql:https://github.com/lotapp/BaseCode/blob/master/database/SQL/city2017.sql-- 编号,文件名,文件MD5,Meta(媒体类型),当前用户,请求IP,来源地址,请求时间,数据状态 drop table if exists filerecords; create table if not exists filerecords ( id int unsigned autoincrement primary key, filename varchar(100) not null, md5 char(32) not null, metatype tinyint unsigned not null default 1, userid int unsigned not null, ip int unsigned not null, url varchar(200) not null default '/', createtime datetime not null, -- default now(), datastatus tinyint not null default 0 ); -- 可以插入2~3次(方便下面演示) insert into filerecords(filename, md5, metatype, userid, ip, url, createtime, datastatus) values ('2.zip', '3aa2db9c1c058f25ba577518b018ed5b', 2, 1, inetaton('43.226.128.3'), 'http://baidu.com', now(), 1), ('3.rar', '6f401841afd127018dad402d17542b2c', 3, 3, inetaton('43.224.12.3'), 'http://qq.com', now(), 1), ('7.jpg', 'fe5df232cafa4c4e0f1a0294418e5660', 4, 5, inetaton('58.83.17.3'), 'http://360.cn', now(), 1), ('9.png', '7afbb1602613ec52b265d7a54ad27330', 5, 4, inetaton('103.3.152.3'), 'http://cnblogs.com', now(), 1), ('1.gif', 'b5e9b4f86ce43ca65bd79c894c4a924c', 6, 3, inetaton('114.28.0.3'), 'http://qq.com', now(), 1), ('大马.jsp', 'abbed9dcc76a02f08539b4d852bd26ba', 9, 4, inetaton('220.181.108.178'), 'http://baidu.com', now(), 99);

4.2.SQLServer

select语句执行流程:

1.from 表1.join类型 join 表 on 条件1.where 条件

  • 对select的结果进行过滤
  • group by 字段
  • **根据指定条件把查询结果进行

分组,以用做

聚合运算**

  • having 条件
  • **对分组聚合运算(

group by)后的结果进行过滤**

  • select distinct 字段
  • order by 字段 [asc|desc]
  • 根据指定字段对查询结果进行排序(默认升序

asc)

  • top 多少行
  • 类比

limit

增删改

-- 4.1.插入 help insert -- 自增长主键和默认值的字段可以不写 insert into dotnetcrazy.dbo.users(username, password, email, tel, usercode, createtime, updatetime, datastatus) values ('dnt', '7c4a8d09ca3762af61e59520943dc26494f8941b', 'dnt@qq.com', '18738002038', newid(), getdate(), getdate(), 1); -- 批量插入 SQLServer一次批量插入最多1000行左右 insert into dotnetcrazy.dbo.users(username, password, email, tel, usercode, createtime, updatetime, datastatus) values ('xxx', '7c4a8d09ca3762af61e59520943dc26494f8942b', 'xxx@qq.com', '13738002038', newid(), getdate(), getdate(), 0), ('mmd', '7c4a8d09ca3762af61e59520943dc26494f8941b', 'mmd@qq.com', '13738002038', newid(), getdate(), getdate(), 1), ('小明', '7c4a8d09ca3762af61e59520943dc26494f8941b', 'xiaoming@qq.com', '13718002038', newid(), getdate(), getdate(), 1), ('小张', '7c4a8d09ca3762af61e59520943dc26494f8941b', 'zhang@qq.com', '13728002038', newid(), getdate(), getdate(), 1), ('小潘', '7c4a8d09ca3762af61e59520943dc26494f8941b', 'pan@qq.com', '13748002038', newid(), getdate(), getdate(), 1), ('小周', '7c4a8d09ca3762af61e59520943dc26494f8941b', 'zhou@qq.com', '13758002038', newid(), getdate(), getdate(), 1), ('小罗', '7c4a8d09ca3762af61e59520943dc26494f8941b', 'luo@qq.com', '13768002038', newid(), getdate(), getdate(), 1); -- 4.2.修改 help update update dotnetcrazy.dbo.users set datastatus=99, updatetime = getdate() where username = 'mmd'; -- 一定要有where条件!开发中一般都是先写where条件再写update -- 4.3.删除 -- 删除数据(自增长不重置)help delete; delete from dotnetcrazy.dbo.users where datastatus = 0; -- 删除全部数据(自增长重置)help truncate; truncate table dotnetcrazy.dbo.users;

查询

-- 查询来源url(去重后) select distinct url from filerecords; -- 查询来源url(分组方式) select url from filerecords group by url; -- 分别统计一下url出现的次数(分组+聚合) -- 分组一般都和聚合函数一起使用 select url, count(/) as count from file_records group by url; -- 分别统计一下url出现的次数,已经删除的文件不算进去 select url, count(/) as count from filerecords group by url having count(/*) > 3; -- 在group by的结果上筛选,★写成count就不行了★ -- 分别统计一下url出现的次数并查出对应的id -- SQLServer2017新增stringagg select ids =(select stuff((select ',' + cast(id as varchar(20)) from filerecords as f where f.url = filerecords.url for xml path ('')), 1, 1, '')),url from filerecords group by url; -- 内连接查询 innet join tbname on 关联条件 select filerecords.id, users.id as uid, users.username, users.email, filerecords.filename, filerecords.md5, filerecords.ip, filerecords.url from users inner join filerecords on filerecords.userid = users.id -- 连接条件 where users.datastatus = 1 and filerecords.datastatus = 1 order by filerecords.filename desc; -- 文件名降序排序 -- 显示前5个数据 select top 5 / from file_records; -- 分页查询 第3页,每页5条 select / from (select rownumber() over (order by username desc, filename desc) as id, filerecords.id as fid, users.id as uid, users.username, users.email, filerecords.filename, filerecords.md5, filerecords.ip, filerecords.url from filerecords inner join users on filerecords.userid = users.id) as temp where id > (3 - 1) / 5 and id <= 3 / 5; -- 简单提一下视图: -- 存在就删除 if exists(select /* from sysobjects where name = N'viewuserinfo') begin drop view viewuserinfo end -- 创建视图 create view viewuserinfo as select id, username, password, email, tel, datastatus from users; -- 查询视图 select id, username, password, email, tel, datastatus from view_userinfo;

附录

知识点:select getdate() as datatime, newid() as uuid; -- 类似于concat的效果 select cast(id as varchar(20)) + ',' from filerecords for xml path (''); -- 移除多余的字符 -- STUFF(expression>,<开始>,<长度>,expression>) -- 将字符串插入到另一个字符串中。它会删除开始位置第一个字符串中的指定长度的字符,然后将第二个字符串插入到开始位置的第一个字符串中 select stuff((select ',' + cast(id as varchar(20)) from filerecords for xml path ('')), 1, 1, '');

数据构造:

--存在就删除表 if exists(select /* from sysobjects where name = N'file_records') begin drop table file_records end -- 因为SQLServer的int没有unsigned,所以推荐使用bigint create table file_records ( id bigint identity (1,1) primary key, file_name varchar(100) not null, md5 char(32) not null, meta_type tinyint not null default 1, user_id int not null, ip bigint not null, -- 在程序中自行转换 url varchar(200) not null default '/', createtime datetime not null default getdate(), datastatus tinyint not null default 0 ); -- 可以插入3次(方便下面演示) insert into file_records(file_name, md5, meta_type, user_id, ip, url, createtime, datastatus) values ('2.zip', '3aa2db9c1c058f25ba577518b018ed5b', 2, 1, 736264195, 'http://baidu.com', getdate(), 1), ('3.rar', '6f401841afd127018dad402d17542b2c', 3, 3, 736103427, 'http://qq.com', getdate(), 1), ('7.jpg', 'fe5df232cafa4c4e0f1a0294418e5660', 4, 5, 978522371, 'http://360.cn', getdate(), 1), ('9.png', '7afbb1602613ec52b265d7a54ad27330', 5, 4, 1728288771, 'http://cnblogs.com', getdate(), 1), ('1.gif', 'b5e9b4f86ce43ca65bd79c894c4a924c', 6, 3, 1914437635, 'http://qq.com', getdate(), 1), ('大马.jsp', 'abbed9dcc76a02f08539b4d852bd26ba', 9, 4, 3702877362, 'http://baidu.com', getdate(), 99);

5.MySQL命令扩展:

  1. 命令帮助

MySQL>**help 命令**

  1. 查看字符集:**
    show character set;
    **
  • utf8:使用1~3bytes来表示一个Unicode字符(常用)
  • utf8mb4:使用1~4bytes来表示一个Unicode字符(

Emoji表情or

不常用汉字)

  • 排序规则:

show collation;

  • eg:

show collation where Collation like "%utf8%";

  • 查看引擎:

show engines;

  • InnoDB是默认存储引擎
  • **查看所有数据库:

show databases;**

  • **切换数据库:

use db_name;**

  • **查看所有表:

show tables;**

  • **显示表状态:

show table status;**

  • eg:

show table status like 'users';

  • **显示表结构:

desc tb_name;**

  • **查看创建表时的SQL:

show create table tb_name;**

  • **显示表的索引:

show indexes from tb_name**

**PS:

G可以竖排显示:

show table status like 'users'G**

最后YY几句:

  1. 没使用

Linux之前,我认为

C/#是最优美、性价比最高、最简单的语言,之后发现

Python才是最简单的语言,

C/#只能是最优美、性价比最高的语言

  • 现在准备接触Golang,最终评价先待定吧
  • 刚接触MySQL发现SQLServer真的很方便,研究MySQL越深越发现==>平心而讲:
  • **对应开发人员来说,

MySQL真的比

SQLServer方便**

  • **对于运维人员来说,

SQLServer真的太方便了**

  • PS:中小企业如果没有专门运维人员,还是推荐

SQLServer,如果有运维人员或者团队有点

Linux运维功底的还是选择

MySQL吧

送大家一句话:**思维局限在一个维度里,认知就会发生偏移,希望大家能够勇于尝试和突破~**

因为时间问题之后的SQL案例就不对比演示了,直接全部

MySQL走起(之后只能说尽量加上

SQLServer版的演示)

下节预估:查询优化

课外拓展:MySQL在线IDE:phpMyAdmin https://www.phpmyadmin.net/downloads/ MySQL最火工具:Navicat Premium https://www.cnblogs.com/dotnetcrazy/p/9711198.html MySQL最佳工具:dbForge Studio for MySQL https://www.devart.com/dbforge/mysql/studio/download.html 【跨平台】SQLServer工具:SqlOps https://www.cnblogs.com/dunitian/p/8045081.html https://github.com/Microsoft/azuredatastudio/releases 【跨平台】都支持:JetBrains DataGrip 【推荐】 https://www.cnblogs.com/dotnetcrazy/p/9711763.html MariaDB数据类型 https://www.w3cschool.cn/mariadb/mariadbdatatypes.html MySQL 数据类型 https://www.w3cschool.cn/mysql/mysql-data-types.html (MariaDB)MySQL数据类型详解和存储机制 https://www.cnblogs.com/f-ck-need-u/archive/2017/10/25/7729251.html Sql Server中的数据类型和Mysql中的数据类型的对应关系 https://blog.csdn.net/lilong329329/article/details/78899477 ALTER TABLE和CREATE INDEX的区别 https://blog.csdn.net/qq_34578253/article/details/72236808 1. create index必须提供索引名,对于alter table,如果你不提供索引名称,MySQL会自动创建索引名称(默认为第一个列名) 2. create index一个语句一次只能建立一个索引,alter table可以在一个语句建立多个,如: - `ALTER TABLE HeadOfState ADD PRIMARY KEY (ID), ADD INDEX (LastName,FirstName);` 3. 只有alter table才能创建主键

版权声明
本文为[鲲逸鹏]所创,转载请带上原文链接,感谢
https://www.cnblogs.com/dotnetcrazy/p/10399838.html

编程之旅,人生之路,不止于编程,还有诗和远方。
阅代码原理,看框架知识,学企业实践;
赏诗词,读日记,踏人生之路,观世界之行;

支付宝红包,每日可领