上篇回顾:聊聊数据库~SQL环境篇php
PS:先使用root建立数据库,而后再受权grant all privileges on 数据库.* to 用户名@"%" identified by "密码";
并刷新flush privileges;
html
查看权限:show grants for dnt;
前端
效果:
python
以前有园友说,为啥不顺便说说UbuntuServer
的部署呢?呃。。。通常来讲公司服务器都是CentOS
的占大多数,而后UbuntuServer
更多的是我的云服务比较多(推荐初创公司使用),毕竟它们两个系统追求的不太同样,一个是追求稳(部署麻烦),一个是追求软件尽可能新的状况下稳定(更新太快)mysql
那么长话短说,步入正轨:linux
Ubuntu不得不说的就是这个apt
出问题的处理 :(换源就不说了/etc/apt/sources.list
)git
# 通常删除这几个锁文件,而后再从新配置下就能够了 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
Ubuntu
推荐使用MySQL
(毕竟同是5.x
用起来基本上差很少,安装过程和以前说的CentOS 下 MariaDB
差很少,全部命令前加个sudo
)github
1.安装比较简单:sudo apt install mysql-server -y
web
2.容许远程链接:注释掉 bind-address=127.0.0.1
(/etc/mysql/mysql.conf.d/mysqld.cnf
)
sql
PS:经常使用配置(/etc/mysql/mysql.conf.d/mysqld.cnf
)
3.关于为何是这个路径的说明:sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf
4.全部配置修改都须要从新启动下:sudo systemctl restart mysql
5.第一次初始化和MariaDB不太同样:sudo mysql_secure_installation
(其余一路y
便可)
须要选下你设置root密码的复杂度:(通常1就能够了,就算设置再复杂,入了系统也是虚的)
PS:能够看看拓展文章:Ubuntu16安装mysql5.7未提示输入密码,安装后修改mysql默认密码 和 【不推荐】修改mysql密码策略
6.而后输入密码你就能够登陆了sudo mysql -uroot -p
(PS:你直接sudo mysql
也能够直接登陆)
这边我就不像上节课一步步演示了,直接受权和建立一步走了grant all privileges on 数据库.* to "用户名"@"%" identified by "复杂密码";
7.记得flush privileges;
刷新一下系统表
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
脚本示例: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的分支,类似度仍是很大的)
来讲说传统概念:
create、drop、alter
insert、delete、update、select
grant
(受权)、revoke
(回收)PS:CURD
(定义了用于处理数据的基本原子操做):建立(Create)更新(Update)读取(Retrieve)删除(Delete)操做
关系型数据库常见组件:
数据库迁移
的需求则不建议使用PS:MySQL常见的文件类型:
先说说MySQL
标准存储引擎(表类型
):
MyISAM
:只支持表级锁
,不支持事务
InnoDB
:支持事务
、间隙锁
、行锁
等等首先是插件式存储引擎(表类型
)的改进和扩充 PS:其实也就是支持更多的存储引擎(包括自定义的)
MariaDB
对标准存储引擎进行了改造升级:
MyISAM
==> Aria
:支持崩溃后的恢复InnoDB
==> XtraDB
:优化存储性能还进行了不少扩展并开发了新的功能(也提供了不少测试工具),好比添加一些NoSQL
的功能(SQLServer
也扩展了NoSQL
)
官方文档:
https://mariadb.com/kb/en/library/data-types
https://dev.mysql.com/doc/refman/5.7/en/data-types.html
以MariaDB
为例,简单列举下经常使用类型:(倾体说明和MySQL
不同)
char()
:不区分字符大小写类型的字符串,max:255个字符
varchar()
: 不区分字符大小写类型的字符串
utf8编码下最多支持21843个字符
)SQLServer
的nvarchar
text
:不区分字符大小写的不限长字符串
单选值
多选值
MySQL系
独有,SQLServer
没有SQLServer
是bit
MySQL
的tinyint(1)
tinyint
:微小整型(1字节,8位)
[-2^7, 2^7)
(-128 ~ 127
)[0, 2^8)
(0 ~ 255
)0 ~ 65535
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)
double
:双精度浮点数(8字节)
SQLServer
的float
类型至关于MySQL
的double
decimal
:精确小数类型(比double更精确)
decimal(位数,小数点位数)
decimal(2,1)
=> x.x
MySQL
同样)
year(2)
:00~99(1bytes)
year(4)
:0000~9999(1bytes)
SQLServer没有这个类型
datetime
:既有时间又有日期(8bytes)timestamp
:时间戳(4bytes)【精度更高】null
| not null
default xxx_value
primary key
unique key
unsigned
(MySQL系独有)auto_increment
(通常只用于整型,MSSQL是identity
)
last_insert_id()
primary key(xx,...)
unique key(xx,...)
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
知识点归纳:
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;
alter table tb_name add 列名 数据类型 修饰符 [first | after 列名];
[first | after 列名]
alter table tb_name change 旧列名 新列名 类型 类型修饰符
alter table tb_name modify 列名 类型 类型修饰符
alter table tb_name alter 列名 set default df_value
alter table tb_name drop 字段名
create index index_name on tb_name(列名,...);
)
alter table tb_name add index [ix_name] (列名,...);
alter table tb_name add unique [uq_name] (列名,列名2...);
drop index index_name on tb_name
)
alter table tb_name drop index index_name;
alter table tb_name drop index uq_name;
https://www.cnblogs.com/huangxm/p/5736807.html
SQL Model
:定义MySQL对约束的响应行为:
set [session] sql_model='xx_mode'
set @@session.sql_mode='xx_mode'
set global sql_mode='xx_mode'
set @@global.sql_mode='xx_mode'
vi /etc/my.cnf
,在[mysqld]
下添加sql_mode='xx'
,而后重启数据库set persist
命令将全局变量的修改持久化到配置文件中
/var/lib/mysql/mysqld-auto.cnf
配置文件中set persist log_timestamps='SYSTEM';
(须要root权限)strict_trans_tables
)
traditional
:使用传统模型,不容许对非法值作插入操做strict_trans_tables
:对全部支持事物类型的表作严格约束strict_all_tables
:对全部表作严格约束select @@sql_mode
-- 若是存在就删除数据库 drop database if exists dotnetcrazy; -- 建立数据库 create database if not exists dotnetcrazy;
-- 若是存在就删除表 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, -- 排序规则 ;
-- 修改表 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';
示例服务器:SQLServer 2014
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;
--存在就删除表 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.添加一列 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;
知识回顾:
课后拓展:
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
简单列举下上面的区别(欢迎补充):
auto_increment
,MSSQL是identity
unsigned
,MSSQL不能够直接设置无符号整型,须要经过约束之类的来限制alter table
的时候,MSSQL没有[first | after 列名]
,并且语法差异也挺大select语句执行流程:
from 表
[inner|left|right] join 表 on 条件
where 条件
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 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.sql
:https://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);
select语句执行流程:
from 表
join类型 join 表 on 条件
where 条件
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 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);
MySQL>
help 命令
select version();
show character set;
Emoji表情
or 不经常使用汉字
)show collation;
show collation where Collation like "%utf8%";
show engines;
InnoDB是默认存储引擎
show databases;
use db_name;
show tables;
show table status;
show table status like 'users';
desc tb_name;
show create table tb_name;
show indexes from tb_name
show variables like '%dataDir%';
select connection_id();
PS:\G能够竖排显示
:show table status like 'users'\G
最后YY几句:
Linux
以前,我认为C#是最优美、性价比最高、最简单的语言
,以后发现Python才是最简单的语言
,C#只能是最优美、性价比最高的语言
MySQL
真的比SQLServer
方便SQLServer
真的太方便了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才能建立主键