LYNX

Links

Tags

Categories

mysql笔记

通用

建表

create table if not exists customers(
cid char(4) not null,
cname char(20) not null,
city char(20),
discnt real,
primary key ( cid )
) character set = utf8 -- 支持中文;

修改并查看注释(comment)

alter table customers
change column cid
cid char(4) not null comment '编号',
change column cname
cname char(20) not null comment '姓名',
change column city
city char(20) comment '城市',
change column discnt
discnt real comment '折扣';
show full columns from customers;

insert去重

  • ignore
insert ignore into table (column)
values
...
  • on duplicate key update

TODO

  • replace into

TODO

更新表数据

update table_name set item="shit" where fuck="fuck";

每组前n

参考

计算时间差

SELECT TIMESTAMPDIFF(MONTH,'2009-10-01','2009-09-01');
SELECT TIMESTAMPDIFF(MONTH,'2009-10-01','2009-09-01') as a;

其中MONTH部分可以更改为

  • SECOND
  • MINUTE
  • HOUR
  • DAY
  • MONTH
  • YEAR

通配符

select season_id, id, title, p_year, c_year from season
where title like '%鲁路修%'
order by p_year;

其他

添加用户

insert

use mysql
insert into user ( Host, User, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv, Reload_priv, Shutdown_priv, Process_priv, File_priv, Grant_priv, References_priv, Index_priv, Alter_priv, Show_db_priv, Super_priv, Create_tmp_table_priv, Lock_tables_priv, Execute_priv, Repl_slave_priv, Repl_client_priv, Create_view_priv, Show_view_priv, Create_routine_priv, Alter_routine_priv, Create_user_priv, Event_priv, Trigger_priv, Create_tablespace_priv, ssl_type, ssl_cipher, x509_issuer, x509_subject, max_questions, max_updates, max_connections, max_user_connections, plugin, authentication_string, password_expired, password_last_changed, password_lifetime, account_locked )
values
('localhost', 'niabie', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', '', '', '', '', 0, 0, 0, 0, 'mysql_native_password', password(''), 'N', '2019-11-07 15:17:00', null, 'N');
flush privileges;# 更新

用户具有创建数据库等的权限,authentication_string不需要则填null

create

参考

create user lynx;
update user set Host='localhost', authentication_string=password('1111') where User='lynx';
flush privileges;# 更新
grant all on database.table to 'lynx'@'localhost';# 分配database的table权限给用户
show grants for lynx@localhost;# 显示用户权限

用户没有创建数据库的权限

修改数据库位置

  • 查看数据库存放位置
show variables like '%dir%';
-- 或
select @@datadir

修改数据库存放位置

参考:digitalocean

  1. 暂停mysql服务
sudo systemctl stop mysql
sudo systemctl status mysql
  1. 转移目录
sudo rsync -av /var/lib/mysql {dir}
sudo mv /var/lib/mysql /var/lib/mysql.bak
  1. 修改mysql配置

/etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]
datadir = {dir}/mysql/
  1. 修改apparmor访问rules

/etc/apparmor.d/tunables/alias

alias /var/lib/mysql -> {dir}/mysql/,
sudo systemctl restart apparmor
  1. 创建mysql所需的的最小文件结构
sudo mkdir /var/lib/mysql/mysql -p
  1. 重启mysql
sudo systemctl start mysql
sudo systemctl status mysql
# sudo rm -Rf /var/lib/mysql.bak
# sudo systemctl restart mysql

其他

  • 创建数据库并选择
create database homework3;
show databases;
use homework3;
show tables;
  • 查看版本等
select version();
select now();
select user();
  • 查看端口
show variables like 'port';

Tags

sql

Categories

1 / 1