mysql常用操作與練習題

標籤：etc   查看   round   exp   插入資料   concat   char   mysqldump   format   

建立資料庫：
create database kaliboy_gbk DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;

create database kaliboy_utf8 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

顯示資料庫:
show databases;

show databases like ‘%kaliboy%‘;

串連顯示資料庫：
use kaliboy;

查看串連顯示資料庫：
select database();

刪除資料庫:
drop database kaliboy;

select version();

select user();
select now();

刪除mysql 系統多餘帳號：
文法：drop user "user"@"主機域"

drop user "kali"@"localhost"
delete from mysql.user where="root" and host="localhost";

通過grant命令建立使用者並授權
grant命令簡單文法如下:
grant all privileges on dname.* to [email protected] identified by ‘passwd‘;
授權命令 對應許可權目標：庫和表 使用者名稱和用戶端主機 使用者密碼

執行個體：
grant all privileges on test.* to [email protected] identified by ‘kali123‘;
flush privileges;

查看許可權：
show grants for [email protected];

create 和grant 配合法
首先建立使用者username及密碼passwd,授權主機localhost
create USER ‘wiki‘@‘localhost‘ IDENTIFIED by ‘passwd‘;

然後授權localhost主機上通過使用者username管理dbname資料庫的所有許可權無需密碼
grant all privileges ON dbname.* to ‘username‘@‘localhost‘;

收回許可權：
revoke insert on test.* from ‘kaliboy‘@‘localhost‘;

mysql 互動式參數-e

建立資料庫表：

create table subject_comment_manager(
subject_comment_manager_id bigint(12) NOT NULL auto_increment COMMENT ‘主鍵‘,
subject_type tinyint(2) NOT NULL COMMENT ‘素材類型‘,
subject_primary_key varchar(255) NOT NULL COMMENT ‘素材的主題‘,
subject_title varchar(255) NOT NULL COMMENT ‘素材的名稱‘，
edit_user_nick varchar(64) default NULL COMMENT ‘修改人‘,
edit_user_time timestamp NULL DEFAULT NULL COMMENT ‘修改時間‘,
edit_comment varchar(255) default NULL COMMENT ‘修改理由‘,
state tinyint(1) NOT NULL default ‘1‘ COMMENT ‘0 代表關閉,1代表正常‘,
PRIMARY KEY (‘subject_comment_manager_id‘), 主鍵
key ‘IDX_PRIMARYKEY‘(‘subject_primary_key‘(32)), 索引
KEY ‘IDX_SUBJECT_TITLE‘(‘subject_title‘(32)) 索引
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

資料庫索引：
create table student(
id int(4) not null AUTO_INCREMENT,
name char(20) not null,
age tinyint(2) not null default ‘0‘,
dept varchar(16) default null,
priary key(id), 主鍵
key index_name(name) 索引
);

刪除主鍵：
alter table student drop primary key;

添加主鍵：
alter table student change id id int primary key auto_increment;

刪除普通索引
alter table student drop index index_name

添加普通索引
alter table student add index index_name(name);

指定前n個字元建立索引

create index index_dept on student(dept(8));

查看索引：

show index from student\G

建立聯合索引：

create index ind_name_dept on student(name,dept);

建立唯一索引(非主鍵)

create unique index index_name

往表中插入資料:

create table test(
id int(4) NOT NULL AUTO_INCREMENT,
name char(20) NOT NULL,
PRIMARY KEY(id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

inert into test(id,name) values(1,‘wiki‘);

inert into test values(2,‘teachar‘),(3,‘student‘);

查詢資料:

使用explain查詢select查詢語句執行計畫

修改表中資料：

update test set name="高圓圓" where id="3";

flush-log 二進位bin日誌切割

防止資料庫誤操作：
登入加-U 參數

 

 

練習題：

多執行個體登入 -S 指定 mysql.sock路徑
mysql -u root -p -S /tmp/mysql.sock

查看資料庫版本及登入使用者是什麼.
select version();

select user();

建立GBK字元集的資料庫kaliboy，並查看已建庫的完整語句

學會用 help查看 help create database, show character set

create database kaliboy CHARACTER SET GBK COLLATE gbk_chinese_ci;

show create database kaliboy;

建立使用者kaliboy,使之可以管理資料庫kaliboy

grant all on kaliboy.* to [email protected]"localhost" Identified by "kali123";

flush privileges;

查看使用者的授權

show grants for [email protected];

查看當前資料庫裡有哪些使用者

select host,user from mysql.user;

進去kaliboy 資料庫

use kaliboy;

建立innodb引擎字元集為GBK表test，欄位為ID和name varchar(16),查看建表結構及SQL語句

create table test(
-> id int(4),
-> name varchar(16))
-> ENGINE=innodb default charset=gbk;

create table position(id int(4), name varchar(10), limit varchar(20)) ENGINE=innodb default charset=gbk;

show create table test\G;

插入一條資料 1 kaliboy

insert into test values(1,‘kaliboy‘);

批量插入資料2，花花公子.3,etiantian

insert into test values(2,‘指令碼小子‘),(3,‘tiantian‘),(4,‘xiaoqian‘),(5,"安全工程師");

查詢插入的所有記錄,查詢名字為kaliboy的記錄 查詢ID 大於1 的記錄

select * from test;

select * from test where name="kaliboy";

select * from test where id>1;

把資料id 等於1 的名字kaliboy更改為 kaligirl;

update test set name="kaligirl" where id=1;

在欄位name前插入age欄位,類型int(4)

alter table test add age int(4) after id;

備份kaliboy庫 與mysql庫

mysqldump -u root -p123456 --events -B kaliboy mysql>kali.sql

刪除表中的所有資料，並查看

use kaliboy;

delete from mysql;

select * from mysql;

truncate table mysql;

刪除表test和kaliboy 資料庫並查看

drop database test;

Linux命令列恢複以上刪除的資料

mysql -u root -p123456<kali.sql

把GBK字元集修改為UTF8

修改資料庫的編碼格式: alter database user character set gbk;

echo $LANG

mysql密碼丟了,如何找回來實戰

/etc/init.d/mysqld stop
mysqld_safe --defaults-file=/etc/my.cnf --skip-grant-table &

update mysql.user set password=password("123456") where user="root";

mysql內中文資料亂碼的原理及如何防止亂碼

 

在把ID 列設定為主鍵,在name欄位上建立普通索引

alter table tast add primary key(id);

在手機欄位上對前8個字元建立普通索引

alter table test add index show(shou(8));

查看建立的索引及索引類型資訊

show index from test\G;

刪除name,shouji列的索引

alter table test drop index name;

對name 列的前6個字元以及手機列的前8個字元組建聯合索引

alter table test add index lianhe(name(6),shou(8));

查詢手機號以135開頭的,名字為kaliboy的記錄

select * from test where name="kaliboy" and shou like "135%";

查詢上述語句的執行計畫(是否使用聯合索引等)

explain select * from test where name="kaliboy" shou like "135%";

建立主鍵索引

alter table test change id id int primary key auto_increment;

刪除索引：

alter table test drop index index_name;

添加索引：

alter table test add index index_name(name);

對前幾位字元建立索引:
文法

create index index_(列名) on (表名)（列名（8））

create index index_dept on test(dept(8));

查看資料庫大小：

要想知道每個資料庫的大小的話，步驟如下：

1、進入information_schema 資料庫（存放了其他的資料庫的資訊）

use information_schema;

 

2、查詢所有資料的大小：

select concat(round(sum(data_length/1024/1024),2),‘MB‘) as data from tables;

3、查看指定資料庫的大小：

比如查看資料庫home的大小

select concat(round(sum(data_length/1024/1024),2),‘MB‘) as data from tables where table_schema=‘home‘;

4、查看指定資料庫的某個表的大小

比如查看資料庫home中 members 表的大小

select concat(round(sum(data_length/1024/1024),2),‘MB‘) as data from tables where table_schema=‘home‘ and table_name=‘members‘;

 

mysql常用操作與練習題