mysql速查

yum -y install mysql  mysql-server  mysql-devel

wget https://dev.mysql.com/get/mysql-apt-config_0.8.15-1_all.deb
sudo dpkg -i mysql-apt-config_0.8.15-1_all.deb
sudo apt-get update
sudo apt-get install mysql-server
vim /etc/mysql/conf.d/mysql.cnf
systemctl restart mysql.service

vim /etc/my.cnf

#添加配置字符集和最大查询语句大小
[mysqld]
init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake

max_allowed_packet=20M
# MySQL 5.7 之后MySQL不能DISTINCT和ORDER BY同时使用
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
bind-address=0.0.0.0

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

chkconfig mysqld on

service mysqld start
service mysqld stop
service mysqld restart
service mysqld status

mysqladmin -u root password 123456

mysql -uroot -p123456

use mysql;
select user,host from user;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'password' PASSWORD EXPIRE NEVER;
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';
flush privileges;

use mysql;
SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user; #查看权限
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION; #配置root以123456密码在任何ip远程登录，不要配置弱密码！！！
flush privileges; #应用配置

create user 'xxx'@'%' identified by '123';
show grants for 'yu'@'%';
DROP USER 'yu'@'localhost';

show VARIABLES like '%max_allowed_packet%'; #查看
set global max_allowed_packet = 2*1024*1024*10配置为20M

select version(); #显示版本
select now(); #显示当前时间
select user(); #显示当前时间
show warnings; #查看警告信息

brew services stop mysql
mysqld --skip-grant-tables # 跳过验证

mysql -uroot -p # 让输入密码时，直接回车

UPDATE mysql.user SET authentication_string='' WHERE user='root' and host='localhost';
exit

brew services start mysql

mysql -uroot -p # 让输入密码时，直接回车

use mysql;
select user,host from user;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'password' PASSWORD EXPIRE NEVER;
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';
flush privileges;

create database db_name;
create database if not exists db_name character set utf8; #存在不报错，字符集utf8

 show databases; #查看所有数据库
 show create database db_name; #查看数据库创建信息
 show global variables like '%datadir%'; #查看数据库数据文件位置

alter database dbname character set utf8 #修改数据库编码

drop database db_name;
drop database if exists db_name; #不存在不报错

use db_name;
# 最简写法
create table user(
	username varchar(20),
	age tinyint unsigned,
	salary float(8,2)
);

#带约束创建
create table user(
	id int unsigned auto_increment primary key, #主键约束 id自动递增
	username varchar(20) not null unique key, #唯一约束
	age tinyint unsigned null default 18, #默认值
	sex enum('1','2','3') default 3,
	salary float(8,2)
);

CREATE TABLE Course(
	Cno char(5) NOT NULL,
	Cname char(20) NOT NULL,
	Chour tinyint NULL,
	Creadit decimal(18, 2) NULL,
	constraint PK_Course PRIMARY KEY(Cno) #主键
);

CREATE TABLE TC(
	Tno char(6) NOT NULL,
	Cno char(5) NOT NULL,
	constraint PK_TC_ PRIMARY KEY (Tno ASC,	Cno ASC), #联合主键
	constraint FK_TNO foreign key (Tno) references Teacher(Tno), #外键
	constraint FK_CNO foreign key (Cno) references Course(Cno) ON DELETE RESTRICT ON UPDATE CASCADE, #外键带on delelte on update 默认为RESTRICT
	# 可选值：RESTRICT、CASCADE、SET NULL 、NO ACTION 表示拒绝、级联、设空、拒绝
);


#创建过程中插入
create table xxx(
	...
)
select ...

#查看所有数据表
show tables;
show tables from mysql;
#查看表结构
show columns from user;
#查看索引、约束名
show indexes from user;
#查看表外键
show create table user;

#添加列
alter table user add password varchar(20) not null after username;
#
alter table user add (
password varchar(20) not null after username,
truename varchar(20) null
);
#添加约束
alter table user add constraint keyname primary key (id); #主键
alter table user add constraint keyname unique (username); #非空
alter table user add constraint keyname foreign key (pid) references ptable(id); #外键
alter table user add alter age set default 18; #默认
#修改列定义
alter table user modify id int not null first; #属性
alter table user change id u_id int not null first; #修改列名和属性
#修改表名
alter table user rename user2;
rename table user2 to user;

#删除列
alter table user drop password;
alter table user drop password, age;
#删除约束
alter table user add alter age drop default; #删除默认
alter table user drop primary key; #删除主键
alter table user drop index username #删除唯一
alter table user drop foreign key fk_name; #删除外键

#1
insert into user values(null,'tom', 25, 10245.25,md5('123456')), (default,'john', default, 10245.25,md5('123456')); #全部必须赋值
insert user(username, salary) values('jackson', 45666.5);
#2 可以使用子查询
insert user set username='tom', password=md5('123456');
#3 将select结果插入
insert user(username) select username from user1 where id=1;

#用法
SELECT select_expr [,select_expr...]
[
FROM tbl_references
[WHERE where_condition]
[GROUP BY {col_name | position} [ASC | DESC],...]
[HAVING where_condition]
[ORDER BY {col_name | expo | position}  [ASC | DESC],...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
]
#例子
#1、
select * from user;
#2、
select user.id, user.name from user;
#3、字段别名
select id as userId, username as uname from user;
#4、where 条件表达式
select id, username from user where id = 1;
#5、group by 分组
select sex from user group by sex; #返回记录中出现的别的值
#6、having
select sex, age from user group by sex having age > 35; #报错，age必须在聚合函数中，或者在group by后面出现
select sex, avg(age) from user group by sex having age > 35; #正确
select sex from user group by sex having count(id) > 35; #正确
#7、order by 排序
select * from user order by id desc; #从大到小
select * from user order by id asc, age desc; #id从小到大，age从大到小
#8、limit 分页
select * from user limit 10,5; #[偏移量,]每页数目

#8、子查询（必须在小括号内），返回结果可以是标量、列、行或者子查询
select * from student where classId=(select id from class where name="计算机科学与技术");
select * from goods where price >= (select round(avg(price),2) from goods);
#any，any满足子查询的一个结果，all满足子查询全部结果
select * from goods where price >= any (select price from goods where type="笔记本");
select * from goods where typeId in (select id from goods where type="笔记本");

/*
9、表连接 join ... on ...
inner join 查询满足on的两表中交集，仅显示满足on件的记录，公共记录
left join 左表中的全部和左右表满足on的交集，对于左表中记录按照on连接右表，右表查不到记录的的为null
right join 类似上
full join mysql 不支持，相当于left + right并去重的结果
cross join 笛卡尔连接 左右叉乘
*/
select goods.name, category.name from goods inner join category where goods.category = category.id;

update user set age = age+5 where id = 1; #单表更新
#多表更新，
update goods inner join category on goods.category = category.name set category = category.id;

delete from user where id = 1; #单表删除
#多表删除

create table provinces( #创建参考表
id int auto_increment primary key,
pname varchar(20) not null
);

create table users(
id int unsigned primary key auto_increment,
username varchar(20) not null,
pid int,
foreign key (pid) references provinces (id) #添加外键约束
);

#参照操作
foreign key (pid) references provinces (id) [on delete cascade]
/*
cascade ： 父表删除更新，子表跟随
set null ： 父表删除更新，子表外键设为 null
restrict ： 拒绝对父表跟新
no action ： 类似restrict
*/

select concat('姓','名');
select concat_ws('-','a','b','c'); #输出 a-b-c

select format(1234.567,2); #输出 1234.57

select lower('MySql'); #输出mysql
select upper('MySql'); #输出MYSQL

select left('MySql',2); #输出My
select right('MySql', 3); #输出Sql

select length("length"); #返回6

select ltrim(" 你好 "); #删除前导空格
select rtrim(" 你好 "); #删除后续空格
select trim(" 你好 "); #删除前后空格
select trim(leading '?' from '???你好?????'); #输出你好?????
select trim(trailing '?' from '???你好?????'); #???输出你好
select trim(both '?' from '???你好?????'); #输出你好

select substring('MySql', 1, 2); #输出My
select replace('??My??Sql??','?',!'); #输出!!My!!Sql!!

select 'MySql' like '_y%'; #输出1表示true,_任意1个字符，任意字符多个

select seil(3.01); #4
select seil(3.99); #3
select 3 div 4; #0整除，运算符
select 5 mod 4; #求余数，运算符
select power(2,3); #8，幂运算
select round(3.4); #四舍五入
select truncate(125.89, -1); #120数字截取

select 35 between 1 and 35; #true，闭区间
select 35 in(1,23,35); #true
select null is null; #true，判断是否为空

select now(); #日期时间
select curdate(); #日期
select curtime(); #时间
select date_add('2016-11-13',interval 365 day); #日期变化，增加1年
select date_add('2016-11-13',interval 1 year); #日期变化，增加1年

select datediff('2015-1-1','2016-1-1); #日期差值 -365
select date_format('2014-3-12','%m/%d%Y'); #03/12/2014

select connect_id(); #输出线程id
select database(); #输出打开的数据库
select last_insert_id(); #最后插入记录的id
select user(); #当前用户
select version(); #数据库版本
select row_count(); #上条记录影响多少条记录

select avg(id) from user; #输出id的平均值
select count(id) from user; #输出记录数目
select min(id) from user; #输出最小值
select max(id) from user; #输出最大值
select sum(id) from user; #求和

select md5('123456');
set password=password('123456'); #更改mysql数据库当前用户密码，修密码

create [define= {user | current_user}]
procedure sp_name([in | out | inout 参数名 类型[,...]])
[特性...] 过程体
/**
特性
 COMMENT 'string'
 {CONTAINS SQLNO SQLREADS SQL DATAMODIFIES SQL DATASQL SECURITY{DEFINERINVOKER}
 COMMENT:注释
 CONTAINS SQL:包含SQL语句, 但不包含读或写数据的语句
 NO SQL:不包含SQL语句
 READS SQL DATA:包含读数据的语句
 MODIFIES SQL DATA:包含写数据的语句
 SQL SECURITY {DEFINERINVOKER}指明谁有权限来执行
过程体
 (1)过程体由合法的SQL语句构成；
 (2)过程体可以是任意SQL语句;对表格进行增删，连接，但是不能创建数据表<br>
 (3)过程体如果为复合结构则使用BEGIN...END语句
 (4)复合结构可以使用条件、循环等控制语句
*/

#无参数存储过程
create procedure getVersion() select version();
call getVersion();#调用

#有in参数存储过程
delimiter //
create procedure removeUserById(in uid int unsigned)
begin
delete from user where id = uid;
end
//
delimiter ;
call removeUserById(1);#调用

#带有in out的语句，删除并获取剩余总数
delimiter //
create procedure removeUserByIdAndGetUserNums(in uid int unsigned, out userNums int unsigned)
begin
delete from user where id = uid;
select count(id) from user into userNums;
end
//
delimiter ;
call removeUserByIdAndGetUserNums(1, @nums);#调用，用户变量
select @nums; #查看变量

drop procedure getVersion;

show create table user; #查看表的存储引擎

#配置默认的存储引擎
default-storage-engine = InnoDB

#创建表示指定存储引擎
create table user(
......
) engine = InnoDB;

#修改现有表的存储引擎
alter table user engine = InnoDB;

ALTER TABLE 表名字 ADD INDEX 索引名 (列名);
CREATE INDEX 索引名 ON 表名字 (列名);

ALTER TABLE employee ADD INDEX idx_id (id);  #在employee表的id列上建立名为idx_id的索引

CREATE INDEX idx_name ON employee (name);   #在employee表的name列上建立名为idx_name的索引

SHOW INDEX FROM 表名字;

CREATE VIEW 视图名(列a,列b,列c) AS SELECT 列1,列2,列3 FROM 表名字;

ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    VIEW view_name [(column_list)]
    AS select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]

DROP VIEW [IF EXISTS]
    view_name [, view_name] ...
    [RESTRICT | CASCADE]

LOAD DATA INFILE '文件路径' INTO TABLE 表名字
[FIELDS TERMINATED BY '分隔符']  [OPTIONALLY ENCLOSED BY '数据包裹符']
[LINES TERMINATED BY '记录分隔符\n']
(列名1,列名2);

SELECT 列1，列2 INTO OUTFILE '文件路径和文件名'
[FIELDS TERMINATED BY '分隔符']  [OPTIONALLY ENCLOSED BY '数据包裹符']
 [LINES TERMINATED BY '记录分隔符\n']
FROM 表名字;

mysqldump -u root 数据库名>备份文件名;   #备份整个数据库

mysqldump -u root 数据库名 表名字>备份文件名;  #备份整个表

mysql -u root -p 密码
source /tmp/SQL6/MySQL-06.sql

mysqldump --single-transaction --all-databases backup_sunday_1_PM.sql

mysqldump --single-transaction --flush-logs --master-data=2
           --all-databases > backup_sunday_1_PM.sql

-- Position to start replication or point-in-time recovery from
-- CHANGE MASTER TO MASTER_LOG_FILE='gbichot2-bin.000007',MASTER_LOG_POS=4;

mysqldump --single-transaction --flush-logs --master-data=2
       --all-databases --delete-master-logs > backup_sunday_1_PM.sql

mysql < backup_sunday_1_PM.sql

mysqlbinlog gbichot2-bin.000007 gbichot2-bin.000008 | mysql

vi /etc/mysql/my.cnf
#打开log bin行

shell> service mysql restart --log-bin
shell> mysql -u root
sql> show variables like 'log_%'; #查看log_bin是否启动

shell> mysql -u root -e 'SHOW BINLOG EVENTS \G'

shell> mysqlbinlog --stop-date="2015-8-6 10:01:00" /var/log/mysql/bin.123456 \
     | mysql -u root -p mypwd

shell> mysqlbinlog --start-date="2014-10-29 9:55:00" --stop-date="2014-10-29 10:05:00" \
      /var/log/mysql/bin.123456 > /tmp/mysql_restore.sql

shell> mysqlbinlog --stop-position="368312" /var/log/mysql/bin.123456 \
    | mysql -u root -pmypwd

shell> mysqlbinlog --start-position="368315" /var/log/mysql/bin.123456 \
    | mysql -u root -pmypwd \

shell> mysql -u root

GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost' IDENTIFIED BY 'some_pass' WITH GRANT OPTION;

GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%' IDENTIFIED BY 'some_pass' WITH GRANT OPTION;

GRANT RELOAD,PROCESS ON *.* TO 'admin'@'localhost';

GRANT USAGE ON *.* TO 'dummy'@'localhost';

FLUSH PRIVILEGES;

GRANT ALL ON customer.* TO 'francis'@'localhost'
IDENTIFIED BY 'frank'
WITH MAX_QUERIES_PER_HOUR 20
MAX_UPDATES_PER_HOUR 10
MAX_CONNECTIONS_PER_HOUR 5
MAX_USER_CONNECTIONS 2;

GRANT USAGE ON *.* TO 'francis'@'localhost'
    WITH MAX_QUERIES_PER_HOUR 100;

GRANT USAGE ON *.* TO 'francis'@'localhost'
    WITH MAX_CONNECTIONS_PER_HOUR 0;

mysqladmin -u user_name -h host_name password "newpwd"

SET PASSWORD FOR 'jeffrey'@'%' = PASSWORD('biscuit');

SET PASSWORD = PASSWORD('biscuit');

GRANT USAGE ON *.* TO 'jeffrey'@'%' IDENTIFIED BY 'biscuit';

# 停止 mysql 服务
sudo service mysql stop

# 使用 mysql_safe 方式启动 mysql 服务
sudo mysqld_safe --skip-grant-tables&

#进入数据库
mysql -uroot mysql

> UPDATE user SET password=PASSWORD("shiyanlou") WHERE user='root';
> FLUSH PRIVILEGES;