MySQL常用操作

標籤：mysql常用操作

MySQL常用操作

授權超級使用者：

grant all privileges on *.* to ‘tangnanbing‘@‘%‘ identified by ‘[email protected]‘ with grant option;

查看庫：

show databases;

查看都有哪些庫  show databases;

查看某個庫的表 use db; show tables \G; 

查看錶的欄位 desc tb;

查看建表語句 show create table tb;

當前是哪個使用者  select user();

當前庫 select database();

建立庫 create database db1; 

建立表 create table t1 (id int, name char(40) adress varchar(30));  

char(10)              ‘aaa       ‘

varchar(10)          ‘aaa‘

查看資料庫版本 select version(); 

查看mysql狀態 show status;

修改mysql參數 show variables like ‘max_connect%‘; set global max_connect_errors = 1000; 

查看mysql隊列 show processlist; 

select * from information_schema.processlist where info is not null;

sleep的可以忽略，qurey查詢的才有

建立普通使用者並授權 grant all on *.* to databases1.user1 identified by ‘123456‘; 

grant all on db1.* to ‘user2‘@‘10.0.2.100‘ identified by ‘111222‘; 

grant all on db1.* to ‘user3‘@‘%‘ identified by ‘231222‘;insert into tb1 (id,name) values(1,‘aming‘);

更改密碼 UPDATE mysql.user SET password=PASSWORD("newpwd") WHERE user=‘username‘ ;   

查詢 select count(*) from mysql.user; select * from mysql.db; select * from mysql.db where host like ‘10.0.%‘; 

插入 update db1.t1 set name=‘aaa‘ where id=1;  

清空表 truncate table db1.t1; 

刪除表 drop table db1.t1; 

刪除資料庫 drop database db1; 

修複表 repair table tb1 [use frm];

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

echo "select user,host,password from mysql.user" |mysql -uroot -plingxiangxiang

mysql -uroot -p1234556 -e "select user,host,password into outfile ‘/home/mysql/1.txt‘ from mysql.user;"

；

增：insert into test.test (id, name) values (123, ‘ling‘);

insert into test.test values (value1_1, value2_2), (value2_1,value2_2), (value3_1, value3_2);

刪：delete from test.test where id in (123, 456);  

alter table test drop column dt;刪除欄位，test表，dt欄位

改：update msyql.user set password = password(‘lingxiangxiang‘)

alter table employees add primary key (emp_no);       增加主鍵

alter table employees drop/add column salaries;         刪除欄位

create table blog_blogmodel as select * from book_blogmodel;   建立一樣的新表

該表名：

查：select user, host, password from mysql.user where user = "root";

select * from msyql.user where conditions order by user [desc];

調整欄位順序：

1. ALTER TABLE `user_movement_log` CHANGE `GatewayId` `GatewayId` int not null default 0 AFTER RegionID  

2. alter table test2 drop column cj, drop column goushi;

排序：select * from test.test order by id asc/desc;

SELECT * FROM usersWHERE email NOT REGEXP ‘^[A-Z0-9._%-][email protected][A-Z0-9.-]+.[A-Z]{2,4}$‘

show global variables like ‘%read_only%‘;

alter user [email protected] identified by ‘‘;

匯出：

select * into outfile ‘/tmp/test/users.txt‘ fields terminated by ‘<[!]>‘ lines terminated by ‘<[end]>‘ from users;

匯入：

load data infile ‘/tmp/test/users.txt‘ into table gamedb.users fields terminated by ‘<[!]>‘ lines terminated by ‘<[end]>‘;

create index idx_name on salaries(emp_no);     建立salaries表的emp_no欄位的索引

show index from salaries\G;             查看索引

日期常用格式：‘year-month-day‘

複雜語句：

select * from (select * from employees order by emp_no desc) aa group by hire_date ;

先對emp_no 進行反向排序，然後在把hire_date分組

650) this.width=650;" src="https://s5.51cto.com/wyfs02/M01/98/D8/wKiom1lBAvSAkHU9AAEV5uswDR4019.jpg" title="001.jpg" alt="wKiom1lBAvSAkHU9AAEV5uswDR4019.jpg" />

內串連：join預設是inner

select * from employees inner join salaries on employees.emp_no = salaries.emp_no;

650) this.width=650;" src="https://s3.51cto.com/wyfs02/M00/98/D8/wKiom1lBAxWCv9pNAACuy2aOE5Q785.jpg" title="002.jpg" alt="wKiom1lBAxWCv9pNAACuy2aOE5Q785.jpg" />

左外連結：右外鏈：

select * from employees left join salaries on employees.emp_no = salaries.emp_no;

650) this.width=650;" src="https://s2.51cto.com/wyfs02/M01/98/D8/wKioL1lBAzLA0wXwAAEF46E0xkU235.jpg" title="003.jpg" alt="wKioL1lBAzLA0wXwAAEF46E0xkU235.jpg" />

select * from employees right join salaries on employees.emp_no = salaries.emp_no;  

650) this.width=650;" src="https://s3.51cto.com/wyfs02/M00/98/D8/wKiom1lBA1WiWLrAAADMDzzPFJQ251.jpg" title="004.jpg" alt="wKiom1lBA1WiWLrAAADMDzzPFJQ251.jpg" />

update的加強版

select * from employees;

650) this.width=650;" src="https://s4.51cto.com/wyfs02/M01/98/D8/wKioL1lBA3LghPXfAACJAiPvkA4746.jpg" title="005.jpg" alt="wKioL1lBA3LghPXfAACJAiPvkA4746.jpg" />

UPDATE `employees`

   SET emp_name = CASE emp_name

           WHEN ‘ling‘ THEN ‘lingjing‘

           WHEN ‘xiang‘ THEN ‘lingxiang‘

           ELSE ‘wang‘

           END

   WHERE gender=‘M‘;

650) this.width=650;" src="https://s2.51cto.com/wyfs02/M01/98/D8/wKiom1lBA4qQDS19AACLnVKUiEI509.jpg" title="006.jpg" alt="wKiom1lBA4qQDS19AACLnVKUiEI509.jpg" />

limit 1 ：列印一行

max（emp_no）：  最大

select max(emp_no) as emp_no from employees;       max(emp_no)改成名字emp_no

問題三：內連接 + 非關聯子查詢

SELECT s1.article, `dealer`, s1.price

   FROM `shop` AS s1

       JOIN (  SELECT `article`, MAX(price) AS price

                   FROM `shop`

                   GROUP BY `article`

           ) AS s2

       ON s1.article = s2.article

           AND s1.price = s2.price

;

本文出自 “12350027” 部落格，謝絕轉載！

MySQL常用操作