Mysql基礎操作

標籤：添加使用者   指定位置   表結構   datetime   char   aot   unique   年齡   upd   

1、資料庫操作

　　進入命令列：mysql -uroot -p123456

　　查看資料庫：show databases;

　　查看資料庫建立語句：show create database test;

　　建立資料庫：create database test charset utf8mb4;

　　選擇資料庫：use test;

　　刪除資料庫：drop database test;

　　備份指定資料庫：mysqldump -uroot -p  test >test.sql   註：由於mysql5.6版本不支援在命令列介面輸入密碼，所以暫不輸入密碼，斷行符號後輸入密碼即可。

　　備份所有資料庫：mysqldump -uroot -p  -A >test.sql 

　　恢複指定資料庫：mysql -uroot -p test<test.sql

　　恢複所有資料庫：mysql -uroot -p  -A <test.sql

2、表操作：

　　資料表條件約束：

　　　　自增：aotu_increment

　　　　主鍵：primary key

　　　　非空：not null

　　　　預設值：default ‘xx‘

　　　　唯一：unique

　　　　指定字元集：charset utf8mb4

　　查看錶：

　　　　查看錶：show tables;

　　　　查看建立表語句：show create table user;

　　　　查看錶結構：desc user;

　　建立表：

create table user(    id int auto_increment primary key,  #ID自增並設定為主鍵    name varchar(10) not null,          #姓名不允許為空白    sex varchar(5) default 1,           #性別預設1    phone bigint not null unique,       #電話不允許為空白並且唯一    create_time datetime default now(), #建立時間預設目前時間    addr varchar(50));

　　刪除表：

　　　　刪除表：drop table user;

　　修改表：

　　　　修改表名稱：alter table user rename user1;

　　　　修改欄位類型並重新命名：alter table user change  name names varchar(15);

　　　　修改欄位類型：alter table user modify name varchar(15);

　　　　在指定位置新增欄位：alter table user add password varchar(20) not null after name;

3、資料操作

　　增加資料：

　　　　表中新增資料：insert into user values(‘‘,‘張三‘,‘男‘,‘1234567890‘,NOW(),‘北京市海澱區‘);

　　　　表中指定欄位添加資料：insert into user(name,phone,create_time) values(‘張三‘,‘12348567890‘,NOW());  註：如果欄位設定非空，則必須添加

　　刪除資料：

　　　　刪除表中資料：delete from user;  註：自增長ID不會清空，還會從原有的ID開始繼續增長

　　　　清空表：truncate user; 

　　　　刪除表中指定的資料：delete from user where id=1;

　　更改資料：

　　　　更改表中所有資料：update user set sex=‘男‘;

　　　　更改表中指定資料：update user set sex=‘男‘ where  name=‘張三‘;

　　　　更改表中多個欄位：update user set sex=‘男‘,addr=‘北京市朝陽區‘ where  name=‘張三‘;

　　　　在原有資料基礎上更改：update user set phone=phone+1 where  name=‘張三‘;

　　查詢資料：

　　　　查詢前5條資料：select * from user limit 5;

　　　　查詢第3至6條資料：select * from user limit 2,4;  註：從第幾條開始查詢(下標從0開始)，查詢多少條

　　　　查詢指定欄位：select  name,sex,phone from user;

　　　　單表查詢：select * from user where name=‘張三‘ and sex=‘男‘;  註：and表示多個條件必須同時滿足

　　　　　　　　　select * from user where name=‘張三‘  or  sex=‘男‘;  註：or表示有其中一個條件滿足即可

　　　　　　　　　select * from user where  sex !=‘男‘;  #查詢不等於男生的資訊,也可以用<>

　　　　　　　　　select * from user where name like ‘張%‘;     #查詢姓張的使用者

　　　　　　　　　select * from user where name like ‘張_‘; 　　#查詢姓張並且姓名為2個字的使用者

　　　　　　　　   select * from user where name in (‘張三‘,‘李四‘,‘王五‘);　　#查詢姓名為張三、李四、王五的資訊

　　　　　　　 　  select * from user where  phone between 13700000000 and 13712345678;　　#查詢手機號在13700000000和13712345678之間的使用者

　　　　　　　 　  select * from user order by create_time desc;　　#查詢按照使用者建立時間倒序顯示，預設升序asc

　　　　　　　　   select * from user where  addr=‘‘ or addr is null;　　#查詢地址為空白或為null的使用者

　　　　　　　　　select distinct name from user;　　#不顯示重複的姓名

　　　　　　　　　select count(*) from user where sex=‘女‘;　　#統計女生有多少人

　　　　　　　　　select max(age),min(age),avg(age),sum(age) from user where sex=‘男‘;　　#尋找男生年齡最大、最小、平均、總和

　　　　　　　　　select *,COUNT(sex) from user GROUP BY sex having sex=‘女‘;　　#按照性別分組，並顯示女生有多少人

　　　　多表查詢：

　　　　　　　　select * from user u,user_group g where u.id=g.id　　　　#查詢兩張表共有的資料

　　　　　　　　select * from user u inner join user_group g on u.id=g.id;　　#查詢兩張表共有的資料

　　　　　　　　select * from user u left join user_group g on u.id=g.id;　　#左邊所有的資料都查出來，右邊如果有匹配的則查出來

　　　　　　　　select * from user u right join user_group g on u.id=g.id;　　#右邊所有的資料都查出來，左邊如果有匹配的則查出來

　　　　　　　　select * from user u left join user_group g on u.id=g.id
　　　　　　　　union
　　　　　　　　select * from user u right join user_group g on u.id=g.id;　　#左邊和右邊匹配的資料全部查出來並去重(union all不會去重)，相當於oracle的全串連

　　　　　　　　select * from (select id,name,sex from user where sex=‘女‘) user;　　#把查詢結果作為一張表查詢

　　　　　　　　select * from user where id in(select id from user_group where g_name=‘電腦‘);　　#子查詢，查詢學電腦的使用者資訊

4、使用者管理

　　添加使用者：

　　　　insert into user (user,host,password) values(‘xiaoxitest‘,‘%‘,PASSWORD(‘123456‘)); #添加xiaoxitest使用者並允許遠端電腦登入，密碼為：123456

　　更改使用者：

　　　　update user set password=password("654321") where user=‘xiaoxitest‘; #更改使用者密碼

　　　　update user set user=‘xiaoxi‘ where user=‘xiaoxitest‘;　　#更改使用者名稱xiaoxitest為xiaoxi

　　刪除使用者：

　　　　delete from user where user=‘xiaoxi‘;　　#刪除xiaoxi使用者

5、許可權管理

　　使用者授權：

　　授權格式：grant 許可權 on 資料庫.* to 使用者名稱@登入主機 identified by "密碼" with grant option;

　　　　grant all on  *.* to ‘xiaoxi‘@‘%‘ IDENTIFIED BY ‘123456‘ with grant option;  #表示為xiaoxi使用者添加所有資料庫所有許可權，並可以給其他人授權。

　　　　grant all on  test.* to ‘xiaoxi‘@‘%‘ IDENTIFIED BY ‘123456‘ ;　　#表示為xiaoxi使用者添加test資料庫所有許可權

　　　　grant select on  *.* to ‘xiaoxi‘@‘%‘ IDENTIFIED BY ‘123456‘ ;　　#表示為xiaoxi使用者添加所有資料庫查詢許可權

　　取消授權：

　　　　Revoke select on *.* from ‘xiaoxi‘@‘%‘;　　#表示為xiaoxi使用者取消所有資料庫查詢許可權

　　　　Revoke all on *.* from ‘xiaoxi‘@‘%‘;　　#表示為xiaoxi使用者取消所有資料庫所有許可權

　　重新整理許可權：

　　　　flush privileges;

　　　　　　　　

Mysql基礎操作