MySQL 5.7.30一般的な操作
一、パスワード
二、タイムゾーン
三、ストレージエンジン
四、建表関連
五、文字セット
六、インポートエクスポート
七、トリガー
八、事務
九、SQL Mode
#
update mysql.user set authentication_string = password('123') where user = 'root' and host = 'localhost';
二、タイムゾーン
#
show variables like '%time_zone';
# ( )
set global time_zone = '+8:00';
flush privileges;
三、ストレージエンジン
#
show engines;
#
CREATE TABLE ai
(
i bigint(20) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (i)
) ENGINE = MyISAM
DEFAULT CHARSET = gbk;
#
alter table table_name engine = innodb;
四、建表関連
# ,\G
show create table ai \G;
#
show table status like 'table_name' \G;
#
check table table_name;
#
repair table table_name;
# ( 0 null )
create table table_name
(
i smallint not null auto_increment
)engine=innodb;
#
alter table table_name auto_increment = n;
# ( )
select last_insert_id();
# /
set foreign_key_checks = 0/1(0: 1: )
# MEMORY , HASH BTREE
create index mem_hash using hash on table_name(id);
#
ALTER TABLE < > [ ]
- ADD COLUMN < > < >
- CHANGE COLUMN < > < > < >
- ALTER COLUMN < > {
SET DEFAULT < > | DROP DEFAULT}
- MODIFY COLUMN < > < >
- DROP COLUMN < >
- RENAME TO < >
-
#
ALTER TABLE < > ADD < > < > [ ] [FIRST | AFTER ];
: alter table actor add column age int after actor_id;
#
ALTER TABLE < > MODIFY < > < >
:alter table actor modify age char(3);
#
ALTER TABLE < > DROP < >;
:alter table actor drop age;
#
ALTER TABLE < > CHANGE < > < > < >;
:alter table actor change actor_name actors_name char(100);
#
ALTER TABLE < > RENAME [TO] < >;
:alter table actor rename to actors;
五、文字セット
#
show character set;
#
desc information_schema.character_sets;
#
show collation like 'gbk%';
#
mysqld --default-character-set=gbk
#
show variables like 'character_set_server';
#
show variables like 'collation_server';
六、インポートエクスポート
#【 】
#--default-character-set=gbk:
#-d ,
#databasename,
#createtab.sql,
[root@localhost ~]# mysqldump -uroot -p --default-character-set=gbk -d databasename > createtab.sql
#【 】
#--quick: 。 msqldump , 。
#--no-create-info: CREATE TABLE
#--extended-insert: VALUES INSERT 。
#--default-character-set=latin1:
#databasename,
#data.sql,
[root@localhost ~]# mysqldump -uroot -p --quick --no-create-info --extended-insert --default-character-set=latin1 databasename > data.sql
七、トリガー
#1、 【 (Permanent Table) , (Temporary Table) 】
CREATE TRIGGER trigger_name trigger_time trigger_event
ON tbl_name FOR EACH ROW trigger_stmt
#
delimiter //
create trigger tr_test
after insert
on actor
for each row
begin
insert into payment(staff_id, amount) values (new.actor_id, new.actor_id);
end //
------------------------ ------------------------------------
1、trigger_time:
- BEFORE
- AFTER
2、trigger_event:
- INSERT
- UPDATE
- DELETE
3、 ,
4、new:
5、old:
6、 , UPDATE INSERT, :
- BEFORE INSERT
- BEFORE UPDATE
- AFTER UPDATE
INSERT, :
- BEFORE INSERT
- AFTER INSERT
------------------------ ------------------------------------
#2、
DROP TRIGGER [schema_name.]trigger_name
#3、
show triggers \G --
select * from information_schema.triggers;
use information_schema; desc triggers;
#4
八、事務
START TRANSACTION | BEGIN [WORK]
COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
SET AUTOCOMMIT = {
0 | 1}
- START TRANSACTION: , UNLOCK TABLES;
- BEGIN:
- COMMIT:
- ROLLBACK:
- CHAIN: ,
- RELEASE:
- SET AUTOCOMMIT: ,1 ,0
#
savepoint point_name;
九、SQL Mode
#1、 SQL Mode
select @@sql_mode;
#2、 SQL Mode
SET [SESSION | GLOBAL] sql_mode = 'modes';
- SESSION:
- GLOBAL: ,