MySQL資料庫(一)

標籤：userinfo   unique   change   資料操作   修改   flush   join   utf8   語句   

win安裝

#Windows:    #可執行檔        點點點    #壓縮包        #放置任意目錄        #初始化            服務端：E:\wupeiqi\mysql-5.7.16-winx64\mysql-5.7.16-winx64\bin\mysqld --initialize-insecure                    # 使用者名稱 root 密碼：空        #啟動服務端：            E:\wupeiqi\mysql-5.7.16-winx64\mysql-5.7.16-winx64\bin\mysqld\mysqld                     #用戶端串連：            E:\wupeiqi\mysql-5.7.16-winx64\mysql-5.7.16-winx64\bin\mysqld\mysql -u root -p                         發送指令：                show databases;                create database db1;                     #環境變數的配置：            E:\wupeiqi\mysql-5.7.16-winx64\mysql-5.7.16-winx64\bin            mysqld                     #windows服務：            E:\wupeiqi\mysql-5.7.16-winx64\mysql-5.7.16-winx64\bin\mysqld --install            net start MySQL                         E:\wupeiqi\mysql-5.7.16-winx64\mysql-5.7.16-winx64\bin\mysqld --remove                         net start MySQL            net stop MySQL

使用者管理

• 使用者管理

#建立使用者create user '使用者名稱'@'IP地址' identified by '密碼';# 刪除使用者drop user '使用者名稱'@'IP地址';#修改使用者rename user '使用者名稱'@'IP地址'; to '新使用者名稱'@'IP地址';#修改密碼set password for '使用者名稱'@'IP地址' = Password('新密碼');

• 授權管理

show grants for '使用者'@'IP地址'                 # -- 查看許可權grant  許可權 on 資料庫.表 to   '使用者'@'IP地址'     # -- 授權revoke 許可權 on 資料庫.表 from '使用者'@'IP地址'     # -- 取消許可權

• 許可權說明

all privileges  除grant外的所有許可權select          僅查許可權select,insert   查和插入許可權...usage                   無存取權限alter                   使用alter tablealter routine           使用alter procedure和drop procedurecreate                  使用create tablecreate routine          使用create procedurecreate temporary tables 使用create temporary tablescreate user             使用create user、drop user、rename user和revoke  all privilegescreate view             使用create viewdelete                  使用deletedrop                    使用drop tableexecute                 使用call和預存程序file                    使用select into outfile 和 load data infilegrant option            使用grant 和 revokeindex                   使用indexinsert                  使用insertlock tables             使用lock tableprocess                 使用show full processlistselect                  使用selectshow databases          使用show databasesshow view               使用show viewupdate                  使用updatereload                  使用flushshutdown                使用mysqladmin shutdown(關閉MySQL)super                   使用change master、kill、logs、purge、master和set global。還允許mysqladmin????????調試登陸replication client      伺服器位置的訪問replication slave       由複製從屬使用

資料庫操作

• 建立資料庫

# utf-8CREATE DATABASE 資料庫名稱 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;create database olddog CHARACTER SET utf8  COLLATE utf8_general_ci; # gbkCREATE DATABASE 資料庫名稱 DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;

• 資料庫常用操作

show create database oldboy\G;      #查看建立庫的資訊 show databases;show databases like "%old%";select database();           #查看進入的資料庫

• 常用命令

select version();       #查看版本select user();          #查看當前的使用者select now();           #查看目前時間help create database    #查看建立資料庫協助show character set;     #查看字元集 create database oldboy CHARACTER SET utf8 COLLATE utf8_general_ci;   #建立資料庫grant all on oldboy.* to [email protected] identified by '123456';       #授權使用者show grants for [email protected];                                       #查看使用者的許可權select user,host from mysql.user;                                    #查看有哪些使用者use test;mysql> create table test(    -> id int(4),    -> name varchar(16)    -> )ENGINE=innodb default charset=utf8;show create table test\G;                                       #查看建立的表desc test;                                                      #查看錶結構insert into test values(1,'oldboy');                            #插入資料update 表名 set 欄位=“” where 欄位......;                       #修改欄位資料delete from 表名 where 條件；                                   #刪除欄位資料 2、查詢（DQL）    select user,host,password from mysql.user;        #正常查詢    select user,host,password from mysql.user order by user asc;   #升序查詢    select user,host,password from mysql.user order by user desc;  #倒序查詢 3、資料操作語言（DML）INSERT UPDATE DELETE    delete from mysql.user where user="tom"; 4、事物處理語言（DPL）BEGIN TRANSACYION，COMMIT，ROLLBACK5、資料控制語言（DCL）GRANT REVOKE6、資料定義原因（DDL）CREATE DROP ALTER

表操作

#建立表   create table 表名(    列名  類型  是否可以為空白，    列名  類型  是否可以為空白)ENGINE=InnoDB DEFAULT CHARSET=utf8   #刪除表   drop table 表名   #清空表   delete from 表名truncate table 表名   #推薦使用      #修改表   　　#添加列：alter table 表名 add 列名 類型　　#刪除列：alter table 表名 drop column 列名　　#修改列：        alter table 表名 modify column 列名 類型;  -- 類型        alter table 表名 change 原列名 新列名 類型; -- 列名，類型     　　#添加主鍵：        alter table 表名 add primary key(列名);　　#刪除主鍵：        alter table 表名 drop primary key;        alter table 表名  modify  列名 int, drop primary key;    #自增主鍵修改  alter table db1 AUTO_INCREMENT=10;     　　#添加外鍵：alter table 從表 add constraint 外鍵名稱（形如：FK_從表_主表） foreign key 從表(外鍵欄位) references 主表(主鍵欄位);　　#刪除外鍵：alter table 表名 drop foreign key 外鍵名稱     　　#修改預設值：ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;　　#刪除預設值：ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;      #修改表名rename table 表名old to 表名new;alter table 表名old rename to 表名new;　

表內容操作

• 增

insert into 表 (列名,列名...) values (值,值,值...)insert into 表 (列名,列名...) values (值,值,值...),(值,值,值...)insert into 表 (列名,列名...) select (列名,列名...) from 表

• 刪

delete from 表delete from 表 where id＝1 and name＝'alex'

• 改

update 表 set name ＝ 'alex' where id>1

• 查

select * from 表select * from 表 where id > 1select nid,name,gender as gg from 表 where id > 1　　

資料庫關係

• 自增 步長

注意：1、對於自增列，必須是索引（含主鍵）。         2、對於自增可以設定步長和起始值MySQL: 自增步長       基於會話層級：             show session variables like 'auto_inc%';      #查看會話變數             set session auto_increment_increment=2;   #設定會話步長             set session auto_increment_offset=10;        #設定會話起始值      基於全域層級：             show global  variables like 'auto_inc%';    #查看全域變數             set global auto_increment_increment=2;  #設定全域步長             set global auto_increment_offset=10;      #設定全域起始值

• 外鍵綁定兩個主鍵

create table db1(    cid int not null auto_increment,    id1 int not null,    id2 int,    primary key(cid,id1)    )engine=innodb default charset=utf8;create table db2(    sid int not null auto_increment primary key,    ic1 int,    ic2 int,    constraint db2_db1 foreign key(ic1,ic2) references db1(cid,id1)    )engine=innodb default charset=utf8;

• 外鍵 唯一 一對一示範

create table user_info(    uid int not null auto_increment primary key,    name varchar(32) not null,    usertype int not null    )engine=innodb default charset=utf8;create table admain_info(    id int not null auto_increment primary key,    user_id int not null,    unique admin_user (user_id),    constraint admin_user foreign key(user_id) references user_info(uid)    )engine=innodb default charset=utf8;insert into user_info(name,usertype) values("alex",1),("egon",2),("tom",3);insert into admain_info(user_id) values(1),(2),(3);

• 外鍵 唯一 多對多示範

create table user(    uid int not null auto_increment primary key,    name varchar(32) not null,    gender ENUM("男","女") not null    )engine=innodb default charset=utf8;create table host(    hid int not null auto_increment primary key,    name varchar(32) not null    )engine=innodb default charset=utf8;create table user_host(    id int not null auto_increment primary key,    uid int not null,    hid int not null,    unique uid_hid (uid,hid),    constraint user_host_user foreign key(uid) references user(uid),    constraint user_host_host foreign key(hid) references host(hid)    )engine=innodb default charset=utf8;insert into user(name,gender) values("alex","男"),("egon","男"),("tom","男");insert into host(name) values("host1"),("host2"),("host3");insert into user_host(uid,hid) values(1,1),(1,2),(1,3);insert into user_host(uid,hid) values(2,1),(2,2),(2,3);insert into user_host(uid,hid) values(3,1),(3,2),(3,3);

• 外鍵 一對多示範

create table user_info(    uid int not null auto_increment primary key,    name varchar(32) not null,    usertype int not null    )engine=innodb default charset=utf8;create table admin_info(    id int not null auto_increment primary key,    user_id int not null,    constraint admin_user foreign key(user_id) references user_info(uid)    )engine=innodb default charset=utf8;insert into user_info(name,usertype) values("alex",1),("egon",2),("tom",3);insert into admin_info(user_id) values(1),(2),(3);

SQL語句資料

• 常用

1、增insert into 表 (列名,列名...) values (值,值,值...);insert into 表 (列名,列名...) values (值,值,值...),(值,值,值...);insert into 表 (列名,列名...) select (列名,列名...) from 表;insert into tb11(name,age) values('alex',12);insert into tb11(name,age) values('alex',12),('root',18);insert into tb12(name,age) select name,age from tb11; 2、刪delete from 表;delete from 表 where id＝1 and name＝'alex';delete from tb12 where id >=2 or name='alex'; 3、改update 表 set name＝'alex' where id>1;update tb12 set name='alex' where id>12 and name='xx'update tb12 set name='alex',age=19 where id>12 and name='xx' 4、查select * from tb12;        select id,name from tb12;select id,name as cname from tb12 where id > 10 or name ='xxx';

• 其他

a、條件select * from 表 where id > 1 and name != 'alex' and num = 12;select * from 表 where id between 5 and 16;select * from 表 where id in (11,22,33)select * from 表 where id not in (11,22,33)select * from 表 where id in (select nid from 表)b、萬用字元select * from 表 where name like 'ale%'  - ale開頭的所有（多個字串）select * from 表 where name like 'ale_'  - ale開頭的所有（一個字元）c、限制select * from 表 limit 5;            - 前5行select * from 表 limit 4,5;          - 從第4行開始的5行select * from 表 limit 5 offset 4    - 從第4行開始的5行d、排序select * from 表 order by 列 asc              - 根據 “列” 從小到大排列select * from 表 order by 列 desc             - 根據 “列” 從大到小排列select * from 表 order by 列1 desc,列2 asc    - 根據 “列1” 從大到小排列，如果相同則按列2從小到大排序e、分組select num from 表 group by numselect num,nid from 表 group by num,nidselect num,nid from 表  where nid > 10 group by num,nid order nid descselect num,nid,count(*),sum(score),max(score),min(score) from 表 group by num,nidselect num from 表 group by num having max(id) > 10特別的：group by 必須在where之後，order by之前f、連表select * from userinfo5,department5                select * from userinfo5,department5 where userinfo5.part_id = department5.idselect * from userinfo5 left join department5 on userinfo5.part_id = department5.id# userinfo5左邊全部顯示# select * from userinfo5 right join department5 on userinfo5.part_id = department5.id# department5右邊全部顯示select * from userinfo5 innder join department5 on userinfo5.part_id = department5.id將出現null時一行隱藏select * from department5 left join userinfo5 on userinfo5.part_id = department5.idleft join userinfo6 on userinfo5.part_id = department5.id

MySQL資料庫(一)