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

上篇回顾:聊聊数据库~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 -y
2.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.cnf
2.3.为什么是这个路径.png

4.所有配置修改都需要重新启动下:sudo systemctl restart mysql
2.4.记得重启一下

5.第一次初始化和MariaDB不太一样:sudo mysql_secure_installation(其他一路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(MySQLSQLServerMSSQL)通用的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
  3. 索引:index
  4. 视图:view
    • PS:如果有数据库迁移的需求则不建议使用
    • PS:MySQL的视图功能不是特别完素,尽量不使用
  5. 存储过程:procedure
  6. 存储函数:function
  7. 触发器:trigger
  8. 事件调度器:event、scheduler
  9. 用户:user
  10. 权限:privilege

PS:MySQL常见的文件类型:

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

2.MySQL标准存储引擎

2.1.MySQL

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

  1. MyISAM:只支持表级锁,不支持事务
  2. InnoDB:支持事务间隙锁行锁等等

2.2.MariaDB

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

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

  1. MyISAM ==> Aria:支持崩溃后的恢复
  2. 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. 字符型:
    1. 定长字符型:
      • char():不区分字符大小写类型的字符串,max:255个字符
      • binary():区分字符大小写类型的二进制字符串
    2. 变长字符型:
      • varchar(): 不区分字符大小写类型的字符串
        • max:65535(2^16 - 1)个字节(utf8编码下最多支持21843个字符
        • 可以理解为SQLServernvarchar
      • varbinary():区分字符的大小写类型的二进制字符串
    3. 对象存储:
      • text:不区分字符大小写的不限长字符串
        • 最大长度为65,535(2^16 - 1)个字符
        • 如果值包含多字节字符,则有效最大长度减少
      • blob:区分字符大小写的不限长二进制字符串
    4. 内建类型:(不推荐使用)
      • enum:单选字符串数据类型,适合表单中的单选值
      • set:多选字符串数据类型,适合表单的多选值
      • PS:MySQL系独有,SQLServer没有
  2. 数值型:
    1. 精确数值型:
      • 整型:int
        1. bool:布尔类型(MySQL没有)
          • PS:SQLServerbit
          • 相当于MySQLtinyint(1)
        2. tinyint:微小整型(1字节,8位)
          • [-2^7, 2^7)(-128 ~ 127)
          • 无符号:[0, 2^8)(0 ~ 255)
        3. smallint(2bytes,16bit):小整型
          • 无符号:0 ~ 65535
        4. mediumint(3bytes,24位):中等整型
          • PS:SQLServer中没这个类型
        5. int(4bytes,32bit)
          • [-2^31, 2^31)[-2147483648,2147483648)
          • 无符号:[0, 2^32)[0,4294967296)
        6. bigint(8bytes,64bit)
          • [-2^63, 2^63)
          • 无符号:[0, 2^64)
    2. 浮点类型:
      • float:单精度浮点数(4字节)
      • double:双精度浮点数(8字节)
        • PS:SQLServerfloat类型相当于MySQLdouble
      • decimal:精确小数类型(比double更精确)
        • 钱相关用的比较多:decimal(位数,小数点位数)
        • eg:decimal(2,1) => x.x
  3. 日期和时间类型:(和MySQL一样)
    1. date:日期(3bytes)
    2. time:时间(3bytes)
    3. year:年
      • eg:year(2)00~99(1bytes)
      • eg:year(4)0000~9999(1bytes)
      • PS:SQLServer没有这个类型
    4. datetime:既有时间又有日期(8bytes)
    5. timestamp:时间戳(4bytes)【精度更高】
  4. 修饰符:
    • 所有类型都适用:
      • 是否为null:null | not null
      • 默认值:default xxx_value
      • 主 键:primary key
      • 唯一键:unique key
    • 数值类型适用:
      • 无符号:unsigned(MySQL系独有)
      • 自增长:auto_increment (一般只用于整型,MSSQL是identity
        • 获取ID:last_insert_id()
    • PS:多列设置
      1. 主键:primary key(xx,...)
      2. 唯一键:unique key(xx,...)
      3. 索引: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;
  2. 删除数据库:
    • drop database [if exists] db_name;
  3. 创建表:
    • create table [if not exists] tb_name(列名1,数据类型 修饰符,列名2,数据类型 修饰符);
  4. 删除表:
    • drop table [if exists] db_name.tb_name;
  5. 修改表:
    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 tb_name alter 列名 set default df_value
      • 删除字段:drop
        • alter table tb_name drop 字段名
    2. 索引
      • 添加索引:add(常用:create index index_name on tb_name(列名,...);
        • alter table tb_name add index [ix_name] (列名,...);
        • 添加唯一键:alter table tb_name add unique [uq_name] (列名,列名2...);
        • PS:不指定索引名字,默认就是第一个字段名
      • 删除索引:drop(常用:drop index index_name on tb_name
        • alter table tb_name drop index index_name;
        • 删除唯一键:alter table tb_name drop index uq_name;
        • PS:唯一键的索引名就是第一个列名
      • PS:一般在经常用做查询条件的列设置索引
    3. 表选项
      • 可以参考这篇文章:https://www.cnblogs.com/huangxm/p/5736807.html
  6. SQL Model:定义MySQL对约束的响应行为:
    • 会话修改:
      • mysql> set [session] sql_model='xx_mode'
      • mysql> set @@session.sql_mode='xx_mode'
      • PS:只在当前会话生效
    • 全局修改:需要有权限,并且不会立即生效,对以后新建的会话生效(从全局继承的)
      • mysql> set global sql_mode='xx_mode'
      • mysql> set @@global.sql_mode='xx_mode'
      • PS:MySQL重启后失效
    • 配置修改:永远生效:
      • eg:vi /etc/my.cnf,在[mysqld]下添加sql_mode='xx',然后重启数据库
      • PS:从MySQL8开始,可通过set persist命令将全局变量的修改持久化到配置文件中
        • 持久化到/var/lib/mysql/mysqld-auto.cnf配置文件中
        • eg:set persist log_timestamps='SYSTEM';(需要root权限)
    • 常用mode:(阿里服务器默认是:strict_trans_tables
      • traditional:使用传统模型,不允许对非法值做插入操作
      • strict_trans_tables:对所有支持事物类型的表做严格约束
      • strict_all_tables:对所有表做严格约束
      • 查询当前设置: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 auto_increment,                       -- 主键,自增长【获取ID:last_insert_id()】
    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 uq_users_email (email),
    index ix_users_createtime_updatetime (createtime, updatetime) -- 索引,不指定名字默认就是字段名
)
--   表选项
--   engine = 'innodb', -- 引擎
--   character set utf8, -- 字符集
--   collate utf8_general_ci, -- 排序规则
;
3.1.3.修改表
-- 修改表 mysql> help alter table

-- 3.1.添加一列 alter table tb_name 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 tb_name drop 字段名
alter table dotnetcrazy.users
    drop uid;

-- 3.3.添加索引 alter table tb_name add index [ix_name] (列名,...)
alter table dotnetcrazy.users
    add index ix_users_ucode (ucode); -- 不指定名字默认就是字段名
-- add index (ucode, tel); -- 不指定索引名字,默认就是第一个字段名

-- 添加唯一键 alter table tb_name add unique [uq_name] (列名,列名2...)
alter table dotnetcrazy.users
    add unique uq_users_tel_ucode (tel, ucode);
-- add unique (tel, ucode);-- 不指定索引名字,默认就是第一个字段名

-- 3.4.删除索引 alter table tb_name drop index ix_name
alter table dotnetcrazy.users
    drop index ix_users_ucode;

-- 删除索引(唯一键) alter table tb_name drop index uq_name
alter table dotnetcrazy.users
    drop index uq_users_tel_ucode;
-- 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:\Works\SQL\dotnetcrazy_data.mdf'--存放路径(包含文件后缀名)
    )
  log on --日记
  (
    name ='dotnetcrazy_Log',
    size =5 mb,
    filegrowth =5%,
    filename =N'D:\Works\SQL\dotnetcrazy_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 tb_name add 列名 数据类型 修饰符
-- 在email后面添加手机号码列
alter table users
  add tel varchar(20) not null;

-- 3.1.1.添加含唯一键的列
-- 先添加列
alter table users
  add uid bigint not null
-- 再添加约束 alter table tb_name add constraint uq_name
alter table users
  add constraint uq_users_uid unique (uid); -- 自定义名称

-- 3.1.2.定义和约束一步走(系统设置名字)
-- alter table users
--   add uid bigint not null unique; -- 默认名称

-- 3.2.含唯一键的列
-- 3.2.1.删除约束 alter table tb_name drop constraint uq_name
if exists(select *
          from sysobjects
          where name = 'uq_users_uid')
alter table users
  drop constraint uq_users_uid;

-- 3.2.2.删除列 alter table tb_name drop column 字段名
alter table users
  drop column uid;

-- 3.3.修改字段
-- 3.3.1.修改列名:exec sp_rename '表名.旧列名','新列名';
exec sp_rename '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
  2. MySQL可以设置无符号unsigned,MSSQL不可以直接设置无符号整型,需要通过约束之类的来限制
  3. alter table的时候,MSSQL没有[first | after 列名],而且语法差别也挺大

4.增删改查(CURD)

4.1.MySQL

select语句执行流程

  1. from 表
  2. [inner|left|right] join 表 on 条件
  3. where 条件
    • 对select的结果进行过滤
  4. group by 字段
    • 根据指定条件把查询结果进行分组,以用做聚合运算
  5. having 条件
    • 对分组聚合运算(group by)后的结果进行过滤
  6. order by 字段 [asc|desc]
    • 根据指定字段对查询结果进行排序(默认升序asc
  7. select 字段
  8. 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 file_records;

-- 查询来源url(分组方式)
select url
from file_records
group by url;

-- 分别统计一下url出现的次数(分组+聚合)
-- 分组一般都和聚合函数一起使用
select url, count(*) as count
from file_records
group by url;

-- 分别统计一下url出现的次数,已经删除的文件不算进去
select url, count(*) as count
from file_records
group by url
having count > 3; -- 在group by的结果上筛选

-- 分别统计一下url出现的次数并查出对应的id
select group_concat(id) as ids, url
from file_records
group by url;

-- 内连接查询 innet join tb_name on 关联条件
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 users
         inner join file_records on file_records.user_id = users.id -- 连接条件
where users.datastatus = 1
  and file_records.datastatus = 1
order by file_records.file_name desc; -- 文件名降序排序

-- MySQL没有`select top n`语法,可以使用 limit来实现,eg:top 5
select *
from file_records
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 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
limit 0,5;

-- 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
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 view_userinfo as
select id, username, password, email, tel, datastatus
from dotnetcrazy.users;

-- 查询视图
select id, username, password, email, tel, datastatus
from dotnetcrazy.view_userinfo;

-- 删除视图
drop view if exists view_userinfo;

附录

知识点:

-- 把ip转换成int
select inet_aton('43.226.128.3'); -- inet6_aton()
-- 把int转换成ip
select inet_ntoa('736264195'); -- inet6_ntoa() ipv6

-- 将多个字符串连接成一个字符串
select concat(user_id, ',', file_name, ',', ip, ',', url) as concat_str
from file_records;

-- 将多个字符串连接成一个字符串+可以一次性指定分隔符
select concat_ws(',', user_id, file_name, ip, url) as concat_str
from file_records;

-- 在有group by的查询语句中,select指定的字段要么就包含在group by语句的后面,作为分组的依据,要么就包含在聚合函数中
-- group_concat():将group by产生的同一个分组中的值连接起来,返回一个字符串结果
select group_concat(file_name) as file_name, url, count(*)
from file_records
group by url;

-- having一般对group by的结果进行筛选,where是对原表进行筛选
select group_concat(file_name) as file_name, group_concat(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.sqlhttps://github.com/lotapp/BaseCode/blob/master/database/SQL/city2017.sql

-- 编号,文件名,文件MD5,Meta(媒体类型),当前用户,请求IP,来源地址,请求时间,数据状态
drop table if exists file_records;
create table if not exists file_records
(
    id         int unsigned auto_increment primary key,
    file_name  varchar(100)     not null,
    md5        char(32)         not null,
    meta_type  tinyint unsigned not null default 1,
    user_id    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 file_records(file_name, md5, meta_type, user_id, ip, url, createtime, datastatus)
values ('2.zip', '3aa2db9c1c058f25ba577518b018ed5b', 2, 1, inet_aton('43.226.128.3'), 'http://baidu.com', now(), 1),
       ('3.rar', '6f401841afd127018dad402d17542b2c', 3, 3, inet_aton('43.224.12.3'), 'http://qq.com', now(), 1),
       ('7.jpg', 'fe5df232cafa4c4e0f1a0294418e5660', 4, 5, inet_aton('58.83.17.3'), 'http://360.cn', now(), 1),
       ('9.png', '7afbb1602613ec52b265d7a54ad27330', 5, 4, inet_aton('103.3.152.3'), 'http://cnblogs.com', now(), 1),
       ('1.gif', 'b5e9b4f86ce43ca65bd79c894c4a924c', 6, 3, inet_aton('114.28.0.3'), 'http://qq.com', now(), 1),
       ('大马.jsp', 'abbed9dcc76a02f08539b4d852bd26ba', 9, 4, inet_aton('220.181.108.178'), 'http://baidu.com', now(),
        99);

4.2.SQLServer

select语句执行流程:

  1. from 表
  2. join类型 join 表 on 条件
  3. where 条件
    • 对select的结果进行过滤
  4. group by 字段
    • 根据指定条件把查询结果进行分组,以用做聚合运算
  5. having 条件
    • 对分组聚合运算(group by)后的结果进行过滤
  6. select distinct 字段
  7. order by 字段 [asc|desc]
    • 根据指定字段对查询结果进行排序(默认升序asc
  8. 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 file_records;

-- 查询来源url(分组方式)
select url
from file_records
group by url;

-- 分别统计一下url出现的次数(分组+聚合)
-- 分组一般都和聚合函数一起使用
select url, count(*) as count
from file_records
group by url;

-- 分别统计一下url出现的次数,已经删除的文件不算进去
select url, count(*) as count
from file_records
group by url
having count(*) > 3; -- 在group by的结果上筛选,★写成count就不行了★

-- 分别统计一下url出现的次数并查出对应的id
-- SQLServer2017新增string_agg
select ids =(select stuff((select ',' + cast(id as varchar(20)) from file_records as f
 where f.url = file_records.url for xml path ('')), 1, 1, '')),url from file_records
group by url;

-- 内连接查询 innet join tb_name on 关联条件
select file_records.id,
       users.id                   as uid,
       users.username,
       users.email,
       file_records.file_name,
       file_records.md5,
       file_records.ip,
       file_records.url
from users
         inner join file_records on file_records.user_id = users.id -- 连接条件
where users.datastatus = 1
  and file_records.datastatus = 1
order by file_records.file_name desc; -- 文件名降序排序

-- 显示前5个数据
select top 5 * from file_records;

-- 分页查询 第3页,每页5条
select *
from (select row_number() over (order by username desc, file_name desc) as id,
             file_records.id                                            as fid,
             users.id                                                   as uid,
             users.username,
             users.email,
             file_records.file_name,
             file_records.md5,
             file_records.ip,
             file_records.url
      from file_records
               inner join users on file_records.user_id = users.id) as temp
where id > (3 - 1) * 5 and id <= 3 * 5;

-- 简单提一下视图:
-- 存在就删除
if exists(select *
          from sysobjects
          where name = N'view_userinfo')
    begin
        drop view view_userinfo
    end
-- 创建视图
create view view_userinfo 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 file_records for xml path ('');

-- 移除多余的字符
-- STUFF(<character_expression>,<开始>,<长度>,<character_expression>)
-- 将字符串插入到另一个字符串中。它会删除开始位置第一个字符串中的指定长度的字符,然后将第二个字符串插入到开始位置的第一个字符串中
select stuff((select ',' + cast(id as varchar(20))
              from file_records 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 命令
    • PS:版本查询:select version();
  2. 查看字符集:show character set;
    • utf8:使用1~3bytes来表示一个Unicode字符(常用)
    • utf8mb4:使用1~4bytes来表示一个Unicode字符(Emoji表情 or 不常用汉字
  3. 排序规则:show collation;
    • eg:show collation where Collation like "%utf8%";
  4. 查看引擎:show engines;
    • InnoDB是默认存储引擎
  5. 查看所有数据库:show databases;
  6. 切换数据库:use db_name;
  7. 查看所有表:show tables;
  8. 显示表状态:show table status;
    • eg:show table status like 'users';
  9. 显示表结构:desc tb_name;
  10. 查看创建表时的SQL:show create table tb_name;
  11. 显示表的索引:show indexes from tb_name

PS:\G可以竖排显示show table status like 'users'\G

最后YY几句:

  1. 没使用Linux之前,我认为C#是最优美、性价比最高、最简单的语言,之后发现Python才是最简单的语言C#只能是最优美、性价比最高的语言
    • 现在准备接触Golang,最终评价先待定吧
  2. 刚接触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/mariadb_data_types.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才能创建主键
posted @ 2019-02-19 11:14 鲲逸鹏 阅读(...) 评论(...) 编辑 收藏