【数据库】mysql

参考资料

[1] https://www.cnblogs.com/2020j...
[2] mysql教程
[3] Ubuntu 18.04 安装 mysql 并且设置远程可登陆连接
[4] Windows下安装MySQL详细教程

1.安装

1.1 ubuntu安装

https://zhuanlan.zhihu.com/p/...

1.1.1 方式一：从 Ubuntu 仓库安装 MySQL

首先，输入下列命令确保你的仓库已经被更新：

$ sudo apt update

现在，安装 MySQL 5.7，简单输入下列命令：

$ sudo apt install mysql-server -y

就是这样！简单且高效。
如果报错：E: Unable to locate package MySQL-server，尝试方法二。

1.1.2 方式二：使用官方仓库安装 MySQL

我们将使用 Curl 命令去下载这个软件包：

$ curl -OL https://dev.mysql.com/get/mysql-apt-config_0.8.12-1_all.deb

根据当前的 MySQL 版本，它有可能不同。使用 dpkg 去开始安装 MySQL：

$ sudo dpkg -i mysql-apt-config*

更新你的仓库：

$ sudo apt update

要实际安装 MySQL，我们将使用像第一个方法中同样的命令来安装：

$ sudo apt install mysql-server -y

1.1.3 验证是否安装成功

使用root用户启动服务

$ sudo su
$ service mysql start
 * Starting MySQL database server mysqld                                                                                                          
 * su: warning: cannot change directory to /nonexistent: No such file or directory

如果报su: warning: cannot change directory to /nonexistent: No such file or directory,则
解决链接：https://www.cnblogs.com/cnwcl...

$ service mysql stop
$ usermod -d /var/lib/mysql/ mysql
$ service mysql start

此时再执行

root@DESKTOP-HMTA87I:/home/wzz# service mysql start
* Starting MySQL database server mysqld

$ ps -aux |grep mysql
mysql     5816  0.1  0.0  10656   812 ?        S    15:41   0:00 /bin/sh /usr/bin/mysqld_safe
mysql     5963  7.1  1.6 2600508 279716 ?      Sl   15:41   0:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --log-error=/var/log/mysql/error.log --pid-file=DESKTOP-HMTA87I.pid
root      6036  0.0  0.0  16208  1284 tty4     S    15:41   0:00 grep --color=auto mysql

1.2 centos安装

1.3 windows安装

Windows下安装MySQL详细教程

2. 配置

https://blog.csdn.net/weixin_...
登录成功之后设置新密码:注意: 设置的新密码必须包含大写字母、小写字母、数字、标点符号，且密码长度至少为 8密码设置之后安装即完成

 $ mysql> ALTER USER 'wzz'@'localhost' IDENTIFIED BY '123456';
 
 
 #允许wzz用户可以从任意机器上登入mysql
 mysql> GRANT ALL PRIVILEGES ON *.* TO wzz@"%" IDENTIFIED BY "123456"; 

 mysql> exit # 退出mysql

 # 登录 mysql(使用新设置的密码)
 $ mysql -u root -p

配置文件：/etc/mysql/mysql.conf.d

3. 使用

完整的DQL语句

select  5
  ..
 from   1 
  ..
 where   2
  ..
 group by  3
  ..
 having  4
  ..
 order by  6
  ..

3.1 连接数据库

mysql -h 10.0.0.0 -P 3306 -u wzz -p  
# 连接数据库 密码：123456 
# -h : 指定客户端所要登录的 MySQL 主机名, 登录本机(localhost 或 127.0.0.1)该参数可以省略;
# -u : 登录的用户名;
# -p : 告诉服务器将会使用一个密码来登录, 如果所要登录的用户名密码为空, 可以忽略此选项。
 
use your_database      # 进入your_database数据库  
desc your_table        # 查看表的定义
status;                # 查看当前信息
truncate your_table；  # 清空your_table表

3.2 show

SHOW DATABASES;                         # 查询数据库
show tables;                            # 查看数据表

3.3 create

create database your_database           # 创建your_database数据库   
create table (字段1 类型，字段2 类型)   # 创建数据表   
create table new_table like src_table;  # 复制表


CREATE TABLE IF NOT EXISTS `user`(
   `id` INT UNSIGNED AUTO_INCREMENT,
   `group` VARCHAR(100) NOT NULL,
   `rank` VARCHAR(40) NOT NULL,
   PRIMARY KEY ( `id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

3.4 select

select * from 表名                                         # 查看所有的字段记录 
select id from 表名                                        # 查看单个的字段记录 
select id,age from 表名                                    # 查看多个字段的记录 
select * from 表名 where 条件                              # 按条件查询 
select * from 表名 order by 字段名 asc/desc                # 排序查询 
select * from 表名 limit 2,5                               # 限制查询 从第二个开始向后查五个(不包括第二个)
select * from 表名 limit 5                                 #查询五个
select * from 表名 where 字段 like '%2%'                   # 查询包含字符2的数据，%为全部
select * from 表名 where 字段 like '_2%'                   # 查询第二个字符为2的数据
select * from 表名 where 字段 is not null                  # 找出非null的数据
select * from 表名 where 字段<40 or age<60;                # or
select * from 表名 where 字段>'1988-1-2' and 字段<'1988-12-1';# and 且
select * from 表名 where 字段 between 40 and 60            # between
select * from 表名 where 字段 in (30,48,68,99);            # in 查询指定集合内的数据
select max/min/avg/count/sum(字段) from 表名group by 字段; #分组查询
select distinct 字段 from 表名                             # distinct去重
select concat(字段1, 字段2) as 拼接 from 名                # concat拼接两个字符串
select 100*2, 100+2, 100+'20', '100'+'210',null+'20';      # 200,102,120,310,null
select count(*) from 表名 group by 字段 having count(*)>2; # 数目大于2

# as关键字可以省略
select empno,ename,sal * 12 yearsal from emp;

# 查询所有字段
select * from emp; # 实际开发中不建议使用*，效率较低。

找出工资不等于3000的？

select ename,sal from emp where sal <> 3000;
select ename,sal from emp where sal != 3000;
找出工资在1100和3000之间的员工，包括1100和3000？
  

select ename,sal from emp where sal >= 1100 and sal <= 3000;
select ename,sal from emp where sal between 1100 and 3000; // between...and...是闭区间 [1100 ~ 3000]
select ename,sal from emp where sal between 3000 and 1100; // 查询不到任何数据
select ename from emp where ename between 'A' and 'C';

SELECT id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 0; 每页3行，查看第一页的数据

3.4 update

update 表名 set 字段名=值;   # 修改数据 不带条件更改字段的所有记录 
insert
insert into 表名 (字段名，字段名) value (值，值)       # 添加一条记录 
insert into 表名 values(值1，值2)(值1，值2)(值1，值2)  # 添加多条记录 
insert into 表名 values (值1，值2)                     # 添加不指定字段名
alter
alter table 表名 rename 新表名                         # 修改表名       
alter table 表名 add 字段定义                          # 添加字段     
alter table 表名 drop 字段名                           # 删除字段     
alter table 表名 change 旧字段名 字段定义              # 修改字段     
alter table 表名 modify 字段名 新的字段类型            # 修改字段类型 
alter table 表名 alter column字段名 set default默认值; # 修改默认值
alter table t_student modify student_name varchar(100) ; # 长度需要更改为100

# 如果想在一个已经建好的表中添加一列，可以用诸如：
alter table TABLE_NAME add column NEW_COLUMN_NAME varchar(20) not null;
# 这条语句会向已有的表中加入新的一列，这一列在表的最后一列位置。如果我们希望添加在指定的一列，可以用：
alter table TABLE_NAME add column NEW_COLUMN_NAME varchar(20) not null after COLUMN_NAME;
# 注意，上面这个命令的意思是说添加新列到某一列后面。如果想添加到第一列的话，可以用：
alter table TABLE_NAME add column NEW_COLUMN_NAME varchar(20) not null first;

使用ADD INDEX idx_score (score)就创建了一个名称为idx_score，使用列score的索引。索引名称是任意的，索引如果有多列，可以在括号里依次写上，例如：

ALTER TABLE students ADD INDEX idx_name_score (name, score); 

3.5 delete

delete from your_table where id=3       # 删除id为3的记录
delete from your_table where 字段=值;   # 删除数据：   
drop
drop database your_database             # 删除your_database数据库   
drop table                              # 删除数据表   

3.6 别名中有中文

select ename,sal * 12 as 年薪 from emp;   # 错误
select ename,sal * 12 as '年薪' from emp; # 正确
注意：标准sql语句中要求字符串使用单引号括起来。虽然mysql支持双引号，尽量别用。

3.7 导入与导出sql文件

mysql导入导出sql文件

3.8 count(*)和count

count(*)和count(具体的某个字段)，他们有什么区别？

 count(*)    # 不是统计某个字段中数据的个数，而是统计总记录条数。（和某个字段无关）
 count(comm) # 表示统计comm字段中不为NULL的数据总数量。

3.9 常见字符串函数

lower(column|str) # 将字符串参数值转换为全小写字母后返回
upper(column|str) # 将字符串参数值转换为全大写字母后返回
concat(column|str1, column|str2,...) # 将多个字符串参数首尾相连后返回
concat_ws(separator,str1,str2,...) # 将多个字符串以给定的分隔符separator首尾相连后返回
substr(str,pos[,len]) # 从源字符串str中的指定位置pos开始取一个字串并返回
length(str) # 返回字符串的存储长度
char_length(str) # 返回字符串中的字符个数
instr(str, substr) # 从源字符串str中返回子串substr第一次出现的位置
lpad(str, len, padstr) # 在源串的左填充字符padstr到指定的长度len，返回填充后的字符串
rpad(str, len, padstr) # 在源串的右填充字符padstr到指定的长度len，返回填充后的字符串
trim([{both | leading | trailing} [remstr] from] str), trim([remstrfrom] str) # 从源字符串str中去掉两端、前缀或后缀字符remstr并返回；如果不指定remstr，则去掉str两端的空格；不指定both \ leading\trailing ，则默认为both。
replace(str, from_str, to_str) # 在源字符串str中form_str（大小写敏感）被to_str替换
ltrim(str)，rtrim(str) # 去掉字符串的左边或右边的空格(左对齐、右对齐)
repeat(str, count) # 将字符串str重复count次后返回
reverse(str) # 将字符串str反转后返回
char(N,... [using  charset_name]) # 将每个参数N解释为整数（字符的编码），并返回每个整数对应的字符所构成的字符串(NULL值被忽略)。
format(X,D[,locale]) # 以格式‘#,###,###.##’格式化数字X;D指定小数位数locale指定国家语
space(N) # 返回由N个空格构成的字符串
left(str, len) # 返回最左边的len长度的子串
right(str, len) # 返回最右边的len长度的子串
strcmp(expr1,expr2) # 若相等则返回0；如果expr1小于expr2则返回-1；否则返回1

3.10 常见数学函数

abs(x) # 返回x的绝对值
pi() # 返回圆周率 
mod(x,y) # 返回余数。也适合带有小数部分的数据值，返回除法运算后的精确余数。
ceil(X) # 返回不小X的最小整数值，返回值转为一个BIGINT.
ceilint(X) # 同ceil(X)
floor(X) # 返回不大于X的最大整数值，返回值转为一个BIGINT.
rand(X) 返回一个随机浮点值，范围在0~1之间，X为整数，它被称作种子值，用来产生重复序列。即当Ｘ值相同时，产生的随机数也相同；
round(X)和round(X,Y) # 四舍五入函数，对Ｘ值按照Ｙ进行四舍五入，Ｙ可以省略，默认值为０;若Ｙ不为０，则保留小数点后面指定Ｙ位。
truncate(X,Y) # 与round(X,Y)功能类似，但不进行四舍五入，只进行截取。
sign(X) # 返回参数Ｘ的符号，Ｘ的值为负、零或正数时返回结果依次为－１，０或１
pow(X,Y)与power(X,Y) # 功能相同，用于返回Ｘ的Ｙ次乘方的结果值
log(X)和log10(X) # 对数运算函数（Ｘ必须为正数），log(X)-返回Ｘ的自然对数(X相对于基数e的对数)　LOG10(X)-返回x的基数为10的对数：
radians(X) 和 degrees(X) # 角度与弧度转换函数 

3.10 常见日期函数

now()              # 获得当前日期+时间（date + time）函数
sysdate()          # 获得当前日期+时间（date + time）
current_timestamp()# 获得当前时间戳函数
date_format('2008-08-08 22:23:01', '%Y%m%d%H%i%s');# 日期/时间转换为字符串函数
str_to_date('08.09.2008 08:09:30', '%m.%d.%Y %h:%i:%s')#（字符串转换为日期）函数
to_days('2008-08-08') #（日期、天数）转换函数
time_to_sec('01:00:05'); -- 3605 # 时间、秒）转换函数
makedate(2001,32); -- '2001-02-01'# 拼凑日期、时间函数
maketime(12,15,30); -- '12:15:30''# 拼凑日期、时间函数
unix_timestamp('2008-08-08'); -- 1218124800 #（Unix 时间戳、日期）转换函数
unix_timestamp(1218124800); -- '2008-08-08 00:00:00'
set @dt = now();
date_add(@dt, interval 1 hour); -- add 1 hour # 为日期增加一个时间间隔
date_add(@dt,interval '01:15:30' hour_second) # 2008-08-09 13:28:03
date_sub('1998-01-01 00:00:00', interval '1 1:1:1' day_second) # 为日期减去一个时间间隔
datediff('2008-08-08', '2008-08-01'); -- 7 # 日期、时间相减函数
timediff('08:08:08', '00:00:00'); -- 08:08:08 # 两个日期相减 time1 - time2，返回 time 差值
timestamp('2008-08-08'); -- 2008-08-08 00:00:00
timestamp('2008-08-08 08:00:00', '01:01:01');  # -- 2008-08-08 09:01:01
timestamp('2008-08-08 08:00:00', '10 01:01:01'); #  -- 2008-08-18 09:01:01
timestampadd(day, 1, '2008-08-08 08:00:00'); # -- 2008-08-09 08:00:00
date_add('2008-08-08 08:00:00', interval 1 day); # -- 2008-08-09 08:00:00
timestampdiff(year,'2002-05-01','2001-01-01'); # -- -1
timestampdiff(day ,'2002-05-01','2001-01-01'); # -- -485
timestampdiff(hour,'2008-08-08 12:00:00','2008-08-08 00:00:00'); # -- -12
convert_tz('2008-08-08 12:00:00', '+08:00', '+00:00'); # -- 2008-08-08 04:00:00
date_add('2008-08-08 12:00:00',interval -8 hour); # -- 2008-08-08 04:00:00
date_sub('2008-08-08 12:00:00',interval 8 hour); # -- 2008-08-08 04:00:00
timestampadd(hour, -8, '2008-08-08 12:00:00'); # -- 2008-08-08 04:00:00