mysql文法總結和練習

標籤：使用者名稱   insert   localhost   identified   mysql文法   

#增加使用者
#格式:grant 許可權 on 資料庫.* to 使用者名稱@登入主機 identified by ‘密碼‘
/*
如，增加一個使用者user1密碼為password1，讓其可以在本機上登入， 並對所有資料庫有查詢、插入、修改、刪除的許可權。首先用以root使用者連入mysql，然後鍵入以下命令： 
grant select,insert,update,delete on *.* to [email protected] Identified by "password1"; 
如果希望該使用者能夠在任何機器上登陸mysql，則將localhost改為"%"。 
如果你不想user1有密碼，可以再打一個命令將密碼去掉。 
grant select,insert,update,delete on mydb.* to [email protected] identified by ""; 
*/

grant all privileges on wpj1105.* to [email protected] identified by ‘123‘;   #all privileges 所有許可權

show grants for [email protected]‘localhost‘; #查看使用者具有哪些許可權。

revoke select on ucyzw.* from [email protected]‘localhost‘; #取消使用者授權。

#----------------------------
#-----MySql資料庫操作基礎-----

#顯示資料庫
show databases;

#判斷是否存在資料庫wpj1105,有的話先刪除
drop database if exists wpj1105;

#建立資料庫
create database wpj1105;

#刪除資料庫
drop database wpj1105;

#使用該資料庫
use wpj1105;

#顯示資料庫中的表
show tables;

#先判斷表是否存在,存在先刪除
drop table if exists student;

#建立表
create table student(
id int auto_increment primary key,
name varchar(50),
sex varchar(20),
date varchar(50),
content varchar(100)
)default charset=utf8;

查看錶結構：

desc:

#刪除表
drop table student;

#查看錶的結構
describe student;  #可以簡寫為desc student;

查看索引：

show indexes from student;

#插入資料
insert into student values(null,‘aa‘,‘男‘,‘1988-10-2‘,‘......‘);
insert into student values(null,‘bb‘,‘女‘,‘1889-03-6‘,‘......‘);
insert into student values(null,‘cc‘,‘男‘,‘1889-08-8‘,‘......‘);
insert into student values(null,‘dd‘,‘女‘,‘1889-12-8‘,‘......‘);
insert into student values(null,‘ee‘,‘女‘,‘1889-09-6‘,‘......‘);
insert into student values(null,‘ff‘,‘null‘,‘1889-09-6‘,‘......‘);
#查詢表中的資料
select * from student;
select id,name from student;

#修改某一條資料
update student set sex=‘男‘ where id=4;

#補充update資料庫多表查詢的賦值(語句要掌握。有時候線上資料庫要修複)：

UPDATE Track,MV

SET Track.is_show=MV.is_show

WHERE Track.trkid=MV.mvid and trkid<6  #多表查詢的時候注意也要找相等關係

#刪除資料
delete from student where id=5;

# and 且
select * from student where date>‘1988-1-2‘ and date<‘1988-12-1‘;

# or 或
select * from student where date<‘1988-11-2‘ or date>‘1988-12-1‘;
   
#between
select * from student where date between ‘1988-1-2‘ and ‘1988-12-1‘;

#in 查詢制定集合內的資料
select * from student where id in (1,3,5);

#排序 asc 升序  desc 降序
select * from student order by id asc;

#分組查詢 #彙總函式 
select max(id),name,sex from student group by sex;

select min(date) from student;

select avg(id) as ‘求平均‘ from student;

select count(*) from student;   #統計表中總數

select count(sex) from student;   #統計表中性別總數  若有一條資料中sex為空白的話,就不予以統計~

select sum(id) from student;

#查詢第i條以後到第j條的資料(不包括第i條)
select * from student limit 2,5;  #顯示3-5條資料

#鞏固練習
create table xiaoluo

(id int auto_increment primary key,

name varchar(50) not null,

sex varchar(50) not null,

age int unsigned,   #年齡不能為負值

sno int unique)default charset=utf8;

drop table xiaoluo;
desc xiaoluo;

insert into xiaoluo (id,name,sex,age,sno) values (null,‘濤哥‘,‘男‘,68,1);
insert into xiaoluo(id,name,sex,age,sno) values (null,‘aa‘,‘男‘,68,2);
insert into xiaoluo(id,name,sex,age,sno) values (null,‘平平‘,‘男‘,35,3);
...

select * from xiaoluo;

#修改資料 
update xiaoluo set age=66 where id=2;
update xiaoluo set name=‘花花‘,age=21,sex=‘女‘ where id=2
delete from xiaoluo where age=21;

#常用查詢語句
select name,age ,id from xiaoluo
select * from xiaoluo where age>40 and age<60;  #and
select * from xiaoluo where age<40 or age<60;  #or
select * from xiaoluo where age between 40 and 60 #between
select * from xiaoluo where age in (30,48,68,99);     #in 查詢指定集合內的資料
select * from xiaoluo order by age desc;      #order by （asc升序 des降序）

#分組查詢
select name,max(age) from xiaoluo group by sex;  #按性別分組查年齡最大值
#彙總函式
select min(age) from xiaoluo;
select avg(age) as ‘平均年齡 ‘ from xiaoluo;
select count(*) from xiaoluo;  #統計表中資料總數
select sum(age) from xiaoluo;

#修改表的名字
#格式:alter table tbl_name rename to new_name
alter table xiaoluo rename to a;
 
#表結構修改
create table test
(
id int not null auto_increment primary key, #設定主鍵
name varchar(20) not null default ‘NoName‘, #設定預設值
department_id int not null,
position_id int not null,
unique (department_id,position_id) #設定唯一值
);

#修改表的名字
#格式:alter table tbl_name rename to new_name
alter table test rename to test_rename;

#向表中增加一個欄位(列)
#格式:alter table tablename add columnname type;/alter table tablename add(columnname type);
alter table test add  columnname varchar(20);

#修改表中某個欄位的名字
alter table tablename change columnname newcolumnname type;  #修改一個表的欄位名
alter table test change name uname varchar(50);

select * from test;

#表position 增加列test
alter table position add(test char(10));
#表position 修改列test
alter table position modify test char(20) not null;
#表position 修改列test 預設值
alter table position alter test set default ‘system‘;
#表position 去掉test 預設值
alter table position alter test drop default;
#表position 去掉列test
alter table position drop column test;
#表depart_pos 刪除主鍵
alter table depart_pos drop primary key;
#表depart_pos 增加主鍵
alter table depart_pos add primary key PK_depart_pos
(department_id,position_id);

#用文本方式將資料裝入資料庫表中（例如D:/mysql.txt）
load data local infile "D:/mysql.txt" into table MYTABLE;

#匯入.sql檔案命令（例如D:/mysql.sql）
source d:/mysql.sql;  #或者 mysql -uroot daname<mysql.sql

二進位日誌的用法：

備份二進位日誌可以使用：

mysqlbinlog mysql-bin.xxx > xx.sql（起始點還原）

或者根據時間點：

mysqlbinlog --start-datetime="2014-12-13 17:00:00" --stop-datetime="2014-12-13 19:20:00" /home/mysql/data/mysql-bin.000008 >/home/test/uclog2.sql

當主從同步出現時間位移報錯可以根據上面步驟，找出出錯點再同步：

 MASTER_HOST=‘192.168.1.8‘,MASTER_USER=‘rsync‘,MASTER_PASSWORD=‘123456‘,MASTER_PORT=3306,MASTER_LOG_FILE=‘mysql-bin.000008‘,MASTER_LOG_POS=272267720（找出錯的時間點cat uclog2.sql）

#

#mysqlbinlog

--start-position    起始位置

--stop-position    結束位置

--start-datetim    起始時間

--stop-datetim    結束時間

mysql被設定無密碼登入(find / -name my.cnf)：

第一：設定檔或者開機檔案裡面有：--skip-grant-tables 選項。

第二：或者在設定檔裡面加入了密碼：

[client]

root=xiaoluo

password=xsdasy892

mysql用戶端程式使用：

mysqladmin -uroot -p -h192.168.10.1 ping #測試對方是否線上

mysqladmin processlist  #查看使用者並發線程

mysqladmin status #查看資料庫狀態，包括滿查詢

#shell 允許mysql語句的時候直接 mysql -e "xxx(mysql語句)" 就可以編寫指令碼。

事物：mysql有交易隔離等級，根據資料的一致性要求。可以對隔離等級最佳化，事物預設提交：

START TRANSACTION;  #啟動事物；

ROLLBACK  ;#出錯的時候事物復原:已經提交了的事物是不能復原的

COMMIT ；  #事務提交

變數查看事務是否預設提交，1是預設提交，為了減少iO效能的消耗，建議關閉自動認可。

 set autocomit=0

mysql> select @@autocommit;

+--------------+

| @@autocommit |

+--------------+

|            1 |

+--------------+

1 row in set (0.00 sec)

鎖機制和查看：

mysql> show status like ‘Table%‘

+----------------------------+-------+

| Variable_name              | Value |

+----------------------------+-------+

| Table_locks_immediate      | 1147  |

| Table_locks_waited         | 0     |

| Table_open_cache_hits      | 0     |

| Table_open_cache_misses    | 0     |

| Table_open_cache_overflows | 0     |

+----------------------------+-------+

5 rows in set (0.00 sec)

如果Table_locks_waited值比較高，說明是已經有鎖表。

二、通過show processlist查看，要是存在比較多的鎖，臨時的解決方案是直接把idkill掉。但是不是最終辦法，要尋找到是什麼原因引起的鎖。是否是硬體問題。或者是程式本身問題，還有就是那條語句滿查詢引起的鎖。然後慢慢最佳化。可以加緩衝。

本文出自 “小羅” 部落格，請務必保留此出處http://xiaoluoge.blog.51cto.com/9141967/1589793

mysql文法總結和練習