當前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

MySQL（数据库）

發(fā)布時間：2023/12/14 数据库 24 豆豆

生活随笔收集整理的這篇文章主要介紹了 MySQL（数据库）小編覺得挺不錯的,現(xiàn)在分享給大家,幫大家做個參考.

mysql

常用命令：
創(chuàng)建庫：

create database rzh;

查看所有的數(shù)據(jù)庫

show databases;

查看所有表：

show tables;

切換庫：

use rzh;

創(chuàng)建表表名(字段字段類型)：

create tables test(id int);

不同的字段會有不同種類的字段類型
姓名：字符串類型
年齡：數(shù)字類型
出生：日期類型（時間）
語法：
create table 表名 (
字段一數(shù)據(jù)類型[完整性約束條件],數(shù)據(jù)唯一,數(shù)據(jù)非空,添加默認值
字段二數(shù)據(jù)類型[完整性約束條件],
字段三數(shù)據(jù)類型[完整性約束條件],
字段四數(shù)據(jù)類型[完整性約束條件],
);

表名：student
名字(name)：自定義 char,varchara(6)
年齡(age)：自定義 int
體重(weight)：自定義 int
身高(height)：自定義 float(3,2)
create table student (
id int,
name varchar(10),
age int,
weight float(3,1),
height float(3,2)
);
desc student; #查看表結(jié)構(gòu)
Field：字段 Type：數(shù)據(jù)類型 Null：空 Key：完整性約束條件 Default：默認值 Extra：自定義

select * from student; #查看表中所有信息

表名：student
學號名字年齡體重身高
1 佩奇 6 180 80
2 喬治 3 150 50
3 迪迦 1024 1000 300

完整性約束條件：目的是為了限制數(shù)據(jù)的寫入，保證數(shù)據(jù)的完整性
完整性約束條件是對字段進行限制，要求對字段進行操作的時候符合要求

主鍵約束 primary key
外鍵約束 foreign key
唯一性約束 unique
非空約束 not null
自增約束 auto_increment
默認值約束 default

主鍵的作用：非空且唯一
如果字段設(shè)置了主鍵約束，那么該字段的值非空且唯一，可以定義聯(lián)合主鍵格式
添加單個主鍵：
直接添加在數(shù)據(jù)類型之后
字段數(shù)據(jù)類型主鍵約束

create table test(id int primary key); insert into student values(1,'佩奇',6,180,80);

向表中插入數(shù)據(jù)

create table student_new ( id int, name varchar(10), age int, weight float(3), height float(3) ); insert into student_new values(1,'佩奇',3,150,50); insert into student_new values(2,'迪迦',1024,88.8,3.24); insert into student_new values(3,'喬治',3,150,50);

添加聯(lián)合主鍵：

create table student_pri ( id int, name varchar(10), age int, weight float(3), height float(3), primary key (id,name) #添加聯(lián)合主鍵 ); insert into student_pri values(1,'佩奇',6,180,80); insert into student_pri values(2,'喬治',3,150,50); insert into student_pri values(3,'迪迦',1024,1000,300); insert into student_pri values(4,'雷歐',1086,1300,295); insert into student_pri values(5,'賽文',1100,1250,333);

聯(lián)合主鍵就是用2個或2個以上的字段組成主鍵。用這個主鍵包含的字段作為主鍵，這個組合在數(shù)據(jù)表中是唯一，且加了主鍵索引，其中一個字段一定要有值

唯一約束，unique：這個字段值必須是唯一的，不能重復
直接添加到數(shù)據(jù)類型后面
創(chuàng)建表：

create table student_uni ( id int unique, name varchar(10), age int, weight float(3), height float(3), primary key (id,name) );

查看表結(jié)構(gòu)：

desc student_uni;

插入值：

insert into student_uni values(1,'賽文',1100,1250,333); insert into student_uni(id) values(1);

非空約束： not null 這個字段的值不能為空
直接添加到數(shù)據(jù)類型后面
創(chuàng)建表：

create table student_nn ( id int not null, name varchar(10), age int, weight float(3), height float(3) );

默認值約束： default：如果某個字段設(shè)置了默認值約束，在寫入數(shù)據(jù)時，如果沒有對這個字段賦值，則會使用默認值

create table student_de ( id int, name varchar(10), age int default '18', weight int, height int );

插入數(shù)據(jù)：

insert into student_de(id) values(1);

查看：

select * from student_de;

insert into 表名 values(值); #一定是對表的所有數(shù)據(jù)進行數(shù)據(jù)添加
insetr into 表名(字段) values(值); #對表中指定的字段進行數(shù)據(jù)添加
insert into 表名 values(值),(值),(值),(值);
insert into 表名 values(1,‘rzh’,18,18,18),(2,‘rzh’,16,16,16);

自增約束： 特殊的約束條件，用于為表中寫入新的記錄生成唯一的值
自增約束不能單獨存在，必須要有主鍵約束
一個表里面只能有一個自增約束字段
格式：
字段數(shù)據(jù)類型 auto_increment

create table student_auto ( id int unique auto_increment, #唯一性約束條件 name char(10), age int default '18', weight int, height int ); insert into student_auto(name) values('rzh'); #向表中插入數(shù)據(jù) select * from student_auto; #查看表中信息 desc student_auto; #查看表結(jié)構(gòu)

id從1開始，每次自增1

查看自增約束的相關(guān)變量
語法：

show variables like 'auto_increment%';

show variables 變量加個s代表復數(shù)
like 模糊匹配

set auto_increment_offset=2; set auto_increment_increment=2;

外鍵約束：
用來建立兩個表格之間的聯(lián)系，一張表可以對應某一個字段設(shè)置外鍵，也可以對多個字段設(shè)置外鍵，添加外鍵的表屬于子表，綁定外鍵的屬于父表，要求子表與父表之間存在關(guān)系的字段數(shù)據(jù)類型得一致父表的那個字段必須是主鍵

例：
表一班級表班級名稱 mysql 學號(主鍵) 姓名
表二學生信息表學號姓名年齡性別
格式：
constraint 外鍵約束名(自定義) foreign key(子表字段) references 父表名主鍵
…………student_class……………學號……………………class……學號

父表：

create table class ( class_id int primary key, class_name char(10) not null default 'mysql' );

子表：

create table student ( id int, name char(10) not null default 'xxx', age int not null default '18', sex char(2) not null default '男', constraint student_class foreign key(id) references class(class_id) );

小練習：
表名：students
學號 …… 姓名……年齡 …… 體重…… 身高…… 班級
1…………佩奇……6…………180………80
2…………喬治……3…………150………50
3…………迪迦……1024……1000………300
4…………雷歐……1086……1300………295
5…………賽文……1100……1250………333
表名：
班級名稱……班級id……學生
小豬班………1…………佩奇喬治
奧特曼班……2…………迪迦雷歐賽文
小矮子班……3…………佩奇喬治
高個子班……4…………迪迦雷歐賽文

創(chuàng)建父表：

create table class2 ( class_id int unique auto_increment, #唯一性自增約束 class_name char(10) not null, #非空 student_name char(10) not null, primary key (class_id,class_name) #聯(lián)合主鍵約束 );

創(chuàng)建子表：

create table student3 ( id int, name char(10) not null default 'xxx', age int not null default '18', #默認值約束 weight int not null, height int not null, class_id int, constraint students_class4 foreign key(id) references class2(class_id) #外鍵約束 );

向classs（父）表中插入數(shù)據(jù)：

insert into class2 values(1,'小豬班','佩奇喬治'); insert into class2 values(2,'奧特曼班','迪迦雷歐賽文'); insert into class2 values(3,'小矮子班','佩奇喬治'); insert into class2 values(4,'高個子班','迪迦雷歐賽文');

向students（子）表中插入數(shù)據(jù)：

insert into student3 values(1,'佩奇',6,180,80,1); insert into student3 values(2,'喬治',3,150,50,1); insert into student3 values(3,'迪迦',1024,1000,300,2); insert into student3 values(4,'雷歐',1086,1300,295,2); insert into student3 values(5,'賽文',1100,1250,333,2); #因為父表中只有四個id數(shù)，所以這條命令可能會插入不進去 insert into student3 values(1,'佩奇',6,180,80,3); insert into student3 values(2,'喬治',3,150,50,3); insert into student3 values(3,'迪迦',1024,1000,300,4); insert into student3 values(4,'雷歐',1086,1300,295,4); insert into student3 values(5,'賽文',1100,1250,333,4);

查看表結(jié)構(gòu)：
父表：

子表：

mysql> desc student3; +--------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+----------+------+-----+---------+-------+ | id | int(11) | YES | MUL | NULL | | | name | char(10) | NO | | xxx | | | age | int(11) | NO | | 18 | | | weight | int(11) | NO | | NULL | | | height | int(11) | NO | | NULL | | +--------+----------+------+-----+---------+-------+ 5 rows in set (0.00 sec)

查看表中信息：

mysql> select * from class2; +----------+--------------+----------------------+ | class_id | class_name | student_name | +----------+--------------+----------------------+ | 1 | 小豬班 | 佩奇喬治 | | 2 | 奧特曼班 | 迪迦雷歐賽文 | | 3 | 小矮子班 | 佩奇喬治 | | 4 | 高個子班 | 迪迦雷歐賽文 | +----------+--------------+----------------------+ 4 rows in set (0.00 sec)mysql> select * from student3; +------+--------+------+--------+--------+ | id | name | age | weight | height | +------+--------+------+--------+--------+ | 1 | 佩奇 | 6 | 180 | 80 | | 2 | 喬治 | 3 | 150 | 50 | | 3 | 迪迦 | 1024 | 1000 | 300 | | 4 | 雷歐 | 1086 | 1300 | 295 | +------+--------+------+--------+--------+ 4 rows in set (0.00 sec)

mysql常用增刪改查命令：

1、查看指定表格的內(nèi)容：

select * from 表名

2、更改/更新表格中的數(shù)據(jù)：

語法 update 表名 set 字段=新值,[字段=新值......] [where 條件語句] 例： update class set teacher=‘xmj’ where class_id=1

3、刪除數(shù)據(jù)表中的數(shù)據(jù)

delete from 表名 #刪除數(shù)據(jù)表中的數(shù)據(jù)，不會釋放表空間,可以找回，只是用戶看不見 truncate table 表名 #刪除數(shù)據(jù)會釋放空間，數(shù)據(jù)再也找不回

4、刪除表結(jié)構(gòu)

drop table 表名刪除指定的表名 drop table 表1，表2，表3... 刪除多個表

5.修改表名

alter table 表名 rename to 新表名例：alter table test rename to test1;

6、修改字段的數(shù)據(jù)類型

alter table 表名 modify 字段名數(shù)據(jù)類型 alter table testq modify id char(3);

7.修改字段名稱

alter table 表名 change 字段名新字段名新數(shù)據(jù)類型; 例：alter table testq change id testq_id int;

8.添加新的字段

alter table 表名 add 新字段名數(shù)據(jù)類型 [完整性約束條件] [first | after] alter table testq add name char unique first;

9.刪除字段

alter table 表名 drop 字段名例：alter table testq drop name1;

10.刪除外鍵

alter table 表名 drop foreign key 外鍵名； alter table testq drop foreign key student_class;

11、修改表的存儲引擎

alter table 表名 engine=存儲引擎 alter table student engine=‘myisam’； show databases； show engines\G alter table sw add name char unique first age；

12、修改并調(diào)整字段位置

alter table 表名 change 舊字段新字段字段類型完整性約束條件 first|after 字段名 alter table test change name new char unique after age;

數(shù)據(jù)類型
數(shù)據(jù)類型主要分為整數(shù)和浮點數(shù)

整數(shù)的特點：正數(shù)，負數(shù)不帶小數(shù)點

整數(shù)：
數(shù)值類型：存儲需求符號取值范圍（±）無符號取值范圍
tinyint 很小的整數(shù) -127~127 0–255 1字節(jié)
smallint 小的整數(shù) -32768~32767 2字節(jié)
mediumint 中等的整數(shù) 3字節(jié)
int 普通的整數(shù) 4字節(jié)
bigint 大的整數(shù) 8字節(jié) 2^64-1
1字節(jié)是8位二進制

create table test ( a tinyint, b smallint, c mediumint, d int, e bigint ); insert into test values(127,255,111,3442,312); #取值范圍不能超過數(shù)值類型所對應的最大值 mysql> select * from test; +------+------+------+------+------+ | a | b | c | d | e | +------+------+------+------+------+ | 127 | 255 | 111 | 3442 | 312 | +------+------+------+------+------+ 1 row in set (0.00 sec)

數(shù)值類型的顯示位數(shù)并不嚴格，
數(shù)據(jù)類型后面加上unsigned，就是無符號取值范圍

create table test1 ( a tinyint (1) unsigned );

浮點數(shù)： 計算機在處理小數(shù)的能力有限
float（x,y): 單精浮點數(shù)，
x表示精度最多多少位，小數(shù)和整數(shù)之和
y表示標度，表示有幾位小數(shù)

mysql> create table test3 (-> a float(3,1),-> b float(3,0),-> c float-> ); Query OK, 0 rows affected (0.00 sec)mysql> insert into test3 values(111,1111,111); ERROR 1264 (22003): Out of range value for column 'a' at row 1

double(x,y)： 雙精度浮點數(shù)，占8字節(jié)存儲x的有效位為16位

mysql> create table test4 ( a double(10,5), b double ); Query OK, 0 rows affected (0.31 sec)mysql> insert into test4 values(11111.11111,1111111111); Query OK, 1 row affected (0.00 sec)mysql> select * from test4; +-------------+------------+ | a | b | +-------------+------------+ | 11111.11111 | 1111111111 | +-------------+------------+ 1 row in set (0.00 sec)

decimal(x,y)： 定點數(shù)，占16字節(jié)，x有效位65位
y也就是小數(shù)部分，最高30位（默認情況下）

mysql> create table test5 ( a decimal(10,2) ); Query OK, 0 rows affected (0.01 sec)mysql> insert into test5 values(10000000.99); Query OK, 1 row affected (0.00 sec)mysql> select * from test5; +-------------+ | a | +-------------+ | 10000000.99 | +-------------+ 1 row in set (0.00 sec)

日期類型：
year： 年顯示格式有多種
第一種：以四位字符串格式表示，范圍‘1901’-‘2155’
第二種：以四位數(shù)據(jù)類型表示，范圍1901-2155
第三種：以兩位數(shù)值類型表示，范圍‘00’-‘99’
第四種：以兩位數(shù)值類型表示，范圍00-99
00-69 表示 2000-2068
70-69 表示 1970-1999

create table test6 ( a year ); insert into test6 values('2020',20,'20',2020)

date:日期
格式一：‘YYYY-MM-DD’,YYYY表示年，MM表示月，DD表示天
‘1000-01-01’~‘9999-12-31’
格式二：‘YY-MM-DD’
‘YY’取值范圍‘00’-‘99’ ‘00-69’表示’2000’-‘2069’ ‘70-99’,‘1970-1999’
格式三：YYMMDD

create table test7 ( a date ); insert into test7 values('2020-12-09'),('20-12-09'),(201209); select * from test7; +------------+ | a | +------------+ | 2020-12-09 | | 2020-12-09 | | 2020-12-09 | +------------+

time:時間
格式一：‘HH:MM:SS’ HH代表時 MM代表分 SS代表秒
格式二：‘HHMMSS’
格式三：‘D HH:MM:SS’ D表示天數(shù)，會計算成對應的小時與HH的值相加，D的取值范圍-34~34
時間的取值范圍‘-829:59:59’-'838:59:59’大概是35天前，三十五天后
負數(shù)表示今天以前

create table test8 ( a time ); insert into test8 values('10:27:25'),('102725'),('-1 10:27:25'); select * from test8; +-----------+ | a | +-----------+ | 10:27:25 | | 10:27:25 | | -34:27:25 | +-----------+mysql> insert into test8 values('10:27:25'),('102725'),('-25 10:27:25'); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0mysql> select * from test8; +------------+ | a | +------------+ | 10:27:25 | | 10:27:25 | | -34:27:25 | | 10:27:25 | | 10:27:25 | | -610:27:25 | +------------+ 6 rows in set (0.00 sec)

datetime：日期與時間
格式一：‘YYYY-MM-DD HH;MM;SS’
取值范圍：‘1010-01-01 00:00:00’~‘9999-12-31 99:99:99’
格式二：‘YY-MM-DD HH:MM:SS’
取值范圍：‘00’-‘99’,‘00’-'69’表示‘2020’-‘2069’ ‘70’-‘99’表示‘1970’-‘1999’
格式三：格式三：‘YYYYMMDDHHMMSS’,‘YYMMDDHHMMSS’

create table test9 ( a datetime ); insert into test9 values('2020-12-09 10:42:40'),(20201209104240); select * from test9; +---------------------+ | a | +---------------------+ | 2020-12-09 10:42:40 | | 2020-12-09 10:42:40 | +---------------------+

字符串數(shù)據(jù)類型：
大概分為兩種：
文本字符串和二進制字符串

文本字符串：
char(M)： 固定長度文本字符串，M表示寬度，最多可以寫入并顯示M個字符
M的取值范圍是0-255，如果對字段寫入的數(shù)據(jù)的字符數(shù)小于M，那么會以空格補充至M個字符，如果超出則會報錯或警告

create table rzh_01( name char(7) ); insert into rzh_01 values('qwerr'),('qwerui');

varchar(M)： 不定長度文本字符串，M顯示寬度表示最多可以顯示M個字符
M的取值范圍是1-21844
如果顯示的字符小于M，不會進行空格填充
如果寫入的字符大于M，那么多出的字符會被截取或者報錯
varchar會額外占用1-2個字節(jié)去記錄寫入的實際寬度的字符長度
字符個數(shù)<=255使用1字節(jié)，否則使用2字節(jié)

create table rzh_02( name varchar(3) ); insert into rzh_02 values('qwe'); insert into rzh_02 values('qwert'); #超過會被截取或報錯類型支持存儲大小和最大支持

tinytext	非常小的文本字符串支持2^8-1 255字節(jié)
text	小的文本字符串支持2^16-1
mediumtext	中等大小的文本字符串支持2^24-1
longtext	大的文本字符串支持2^32-1

varchar的值超過21844會自動切換成mediumtext

create table rzh_3( a varchar(22000) );

enum枚舉： 是一個字符串對象，在給定的范圍內(nèi)選擇一個值
格式：
字段 enum(值1，值2，值3……值n)
如果值后面有空格鍵，會把空格刪除,最多可以有65535個值
把值稱為元素，每個值都有自己的編號，編號從1開始
寫入數(shù)據(jù)時選擇不存在的編號會導致字段內(nèi)容為空

create table rzh_4( id tinyint, name char(11), fenshu tinyint, cj enum('優(yōu)秀','良好','及格','不及格') ); insert into rzh_4 values(1,'rzh',90,'優(yōu)秀');

set： 集合，是一個字符串對象，可以在給定范圍以內(nèi)。選取0個或多個數(shù)值
格式：
字段 set(值1，值2……值n)
#值最多可以有64個，在寫入數(shù)據(jù)的時候可以選擇多個值寫入
指定的值會按照設(shè)置的順序自動排列
如果一個值選中了多次，會自動去重，重復的值，只會出現(xiàn)一次
如果寫入不存在的值，會被刪除

create table rzh_5( a set('a','b','c','d','e') ); insert into rzh_5 values('a,b,c'),('e,d,c,a');

二進制類型字符串：
屬于字節(jié)流，不受字符集的限制，可以指定可寫入字節(jié)的大小
一般用于存儲圖片，視頻，音頻等媒體數(shù)據(jù)
bit(M)，M指的是字段可以存儲的位的大小，M的取值范圍是1-65

create table rzh_6( a bit(10) ); insert into rzh_6 values('c'),('a'),('c'); select bin(a) from rzh_6;

binary(M): 固定長度二進制字符串，M表示可以存儲字節(jié)的大小
M的取值范圍是0-255，如果寫入的數(shù)據(jù)小于M的值，那么會以\0進行填充
\0一樣是沒有實際意義的

create table rzh_7( a binary(4) ); insert into rzh_7 values(1),('a'),('ab12'); insert into rzh_7 values(1),('a'),('哈哈'); #會告訴你超出范圍

數(shù)據(jù)庫默認utf-8字符集，字母字符數(shù)字都占一個字節(jié)，漢字占3字節(jié)
varbinary(M)： 不定長度二進制字符串，M表示可以存儲字節(jié)的大小
M的取值范圍0-65535，如果寫入的數(shù)據(jù)大小小于M的值，不會進行\(zhòng)0補充
會占用額外的1-2字節(jié)存儲實際的大小

create table rzh_8( a varbinary(6) ); insert into rzh_8 values(111111),('哈哈');

二進制類型：

類型支持存儲大小

tinyblob	非常小的二進制字符串 2^8
blob	小的二進制字符串 2^16
mediumblob	中等大小的二進制字符串 2^24
longblob	大的二進制字符串 2^32

create table rzh_9( name char(11), img mediumblob ); insert into rzh_9 values('xx',load_file('/usr/local/mysql/data/5f8d2f350b621.jpg') );

算數(shù)運算符
+：加法運算
-：減法運算
*：乘法運算
/：除法運算
%：取余

比較運算符

>：大于 =：等于 <：小于 >=: <= !=,<> <=>

1、=等于運算符，用來判斷數(shù)值，字符串，表達式是否相等，
如果返回值是1，則相等；返回0，代表不等，如果比較值為null，則返回null
2、<=>安全等于運算符，用來判斷數(shù)值，字符串，和表達式是否相等
可以對空值進行比較，如果相等則返回1，不等返回0

對空值的判斷
is null：判斷一個值是否為空，是返回1，不是返回0
isnull：判斷一個值是否為空，是返回1，不是返回0
is not null：判斷一個值是否不為空，如果不為空返回1，為空返回0
between and：判斷一個值是否在給定的范圍以內(nèi)

select 6 between 1 and 9；

返回最大值與最小值
least：當存在有兩個或兩個以上數(shù)據(jù)時，返回最小值，如果有null，返回null

select least(1,2,3,4,5,null),least('a','b','c');

greatest：當存在有兩個或者兩個以上數(shù)據(jù)時，返回最大值，如果有null，則返回null

select greatest(1,2,3,4,5,null),least('a','b','c');

in：判斷一個值是否在指定的列表中
not in：判斷一個值是否不在指定的列表中

select 5 in(1,2,3,4,5,6,null),'a' not in(1,2,3,4,5,6);

like：用來匹配字符串，符號_用于匹配任意一個字符，符號%匹配任意個任意字符
如果字符匹配則返回1，不匹配返回0

select 'zyg' like 'zy_','szjdha' like 's%','sljd' like 'z%jb_cj';

regexp：正則表達式匹配，用來匹配字符串，如果匹配返回1，不匹配返回0

^:匹配以...開頭的字符串 $:匹配以...結(jié)尾的字符串 . 匹配任意一個字符 *：表示重復前面的字符任意次 .* ：表示任意個任意字符 []：用于匹配括號內(nèi)的任意字符 select 'sjk' regexp '^s','sjk' regexp 'k$','sjk' regexp 's.k'; select 'sjk' regexp 's*','sjk' regexp 'j.*','sjk' regexp '[aed]';

邏輯運算符
not,!:邏輯非運算符
and,&&:邏輯與運算符
or,||:邏輯或運算符
xor:邏輯異運算符

1.not , ! 當操作數(shù)為0時返回1，當操作數(shù)是不為0是返回0，當操作數(shù)是null，返回null

select not 0,not 1,not null,not 'a';

2.and,&&當所有操作數(shù)為非零且值不為空時返回1，任意操作數(shù)為0返回0
當任意操作數(shù)不為0且不為空返回1

select null or 1,0 or 1,1 or 2;

3.or,||:當所有的操作數(shù)都為0時返回0，當操作數(shù)為0和null或者全為null返回null
當任意操作數(shù)不為0且不為空返回1

select null or 1,0 or 1,1 or 2;

4.xor:當任意一個操作數(shù)為NULL返回NULL，對于非NULL的操作數(shù)
如果兩個操作數(shù)都為0或都不為0則返回0
如果一個操作數(shù)為0，一個操作數(shù)不為0則返回1

位運算符

|：或 &：與 ^位異 ~位取反 <<位左移 >>位右移

1.|：對應的二進制有一個或兩個為1，則為1，反之為0

15|10 8|10 1 2 4 8 1 1 1 1 0001 0 1 0 1 0101 1111 0101 select 15 | 10,10 | 8;

2.&:對應的二進制都為1則是1，否則為0

15&10 1111 0101 0101

3.^位異運算，對應的二進制一樣則為0，不同則為1

15^10 1111 1110 0101 1010

4,<<將指定的二進制向左移n位（值變大）

select 15 << 1; 1 2 4 8 16 1 1 1 1 0 0 1 1 1 1

5,>>將指定的二進制向右移n為（值變小）

select 15 >> 1；

6，~位取反：將對應的二進制的每一位進行反轉(zhuǎn)，1變成0，0變成1，需要和&搭配使

~10 & 20 1 2 4 8 16 0 1 0 1 0 1 0 1 0 1 0 0 1 0 1 0 0 10 1

函數(shù)：
具有一定的功能，能夠使我們的計算變得簡單，將一些功能和步驟進行封裝

分類：
數(shù)字類：
1、abs(n)：絕對值

mysql> select abs(-1),abs(10),abs(0); +---------+---------+--------+ | abs(-1) | abs(10) | abs(0) | +---------+---------+--------+ | 1 | 10 | 0 | +---------+---------+--------+ 1 row in set (0.00 sec)

2、pi()：圓周率

mysql> select pi(); +----------+ | pi() | +----------+ | 3.141593 | +----------+ 1 row in set (0.00 sec)

3、sqrt(x)：求平方根，負數(shù)沒有平方根

mysql> select sqrt(18),sqrt(20); +-------------------+------------------+ | sqrt(18) | sqrt(20) | +-------------------+------------------+ | 4.242640687119285 | 4.47213595499958 | +-------------------+------------------+ 1 row in set (0.00 sec)

4、mod(x,y)：求余函數(shù)

select mod(19,2);

5、獲取整數(shù)的函數(shù)
ceil(x)：獲取的值最接近原數(shù)，且不小于原數(shù)的整數(shù)

mysql> select ceil(1.11); +------------+ | ceil(1.11) | +------------+ | 2 | +------------+

ceiling(x)：獲取的值最接近原數(shù)，且不小于原數(shù)的整數(shù)

mysql> select ceiling(1.11); +---------------+ | ceiling(1.11) | +---------------+ | 2 | +---------------+ 1 row in set (0.01 sec)

floor(x)：獲取的值最接近原數(shù)，且大于原數(shù)的整數(shù)

mysql> select floor(1.11),floor(1.99); +-------------+-------------+ | floor(1.11) | floor(1.99) | +-------------+-------------+ | 1 | 1 | +-------------+-------------+ 1 row in set (0.00 sec)

6、rand()：獲取一個隨機數(shù)的函數(shù)
獲取的范圍在0-1之間，每一次執(zhí)行得到的數(shù)都不一樣

mysql> select rand(),rand(),rand(); +--------------------+--------------------+---------------------+ | rand() | rand() | rand() | +--------------------+--------------------+---------------------+ | 0.2071449199757957 | 0.7698830969351187 | 0.22798075548185107 | +--------------------+--------------------+---------------------+ 1 row in set (0.00 sec)

rand(x)：獲取一個隨機數(shù)，根據(jù)x返回一個確定的隨機數(shù)

mysql> select rand(6),rand(7),rand(8),rand(6); +--------------------+--------------------+---------------------+--------------------+ | rand(6) | rand(7) | rand(8) | rand(6) | +--------------------+--------------------+---------------------+--------------------+ | 0.6563190842571847 | 0.9065021936842261 | 0.15668530311126755 | 0.6563190842571847 | +--------------------+--------------------+---------------------+--------------------+ 1 row in set (0.00 sec)mysql> select rand(7); +--------------------+ | rand(7) | +--------------------+ | 0.9065021936842261 | +--------------------+ 1 row in set (0.00 sec)

7、四舍五入函數(shù)
round(x)：對x進行四舍五入，保留整數(shù)，不保留小數(shù)
select(x,y)：對x進行四舍五入，y是值從第幾位數(shù)開始

mysql> select round(12.345,2),round(12.345,-2); +-----------------+------------------+ | round(12.345,2) | round(12.345,-2) | +-----------------+------------------+ | 12.35 | 0 | +-----------------+------------------+ 1 row in set (0.00 sec)mysql> select round(12.345,2),round(12.345,-1); +-----------------+------------------+ | round(12.345,2) | round(12.345,-1) | +-----------------+------------------+ | 12.35 | 10 | +-----------------+------------------+ 1 row in set (0.00 sec)

y是正數(shù)，表示小數(shù)點之后幾位，y是負數(shù)，表示小數(shù)點前幾位

8、truncate(x,y)：截取數(shù)值的函數(shù)，對x進行截取，y為正數(shù)，對小數(shù)部分進行截取，y為負數(shù)，則對整數(shù)部分進行截取

mysql> select truncate(123.456,2),truncate(123.456,-2); +---------------------+----------------------+ | truncate(123.456,2) | truncate(123.456,-2) | +---------------------+----------------------+ | 123.45 | 100 | +---------------------+----------------------+ 1 row in set (0.00 sec)

9、sign(x)：符號函數(shù)，x為正數(shù)時返回1，x為0時返回0，x為負數(shù)時，返回-1
x為null，返回null

10、pow(x,y)：冪運算函數(shù)，已知底數(shù)為5，指數(shù)為2，求5的2次方是多少

mysql> select pow(5,2); +----------+ | pow(5,2) | +----------+ | 25 | +----------+ 1 row in set (0.00 sec)

pow(x,y)：求x的y次方是多少

exp(y)：求e的y次方

mysql> select exp(1); +-------------------+ | exp(1) | +-------------------+ | 2.718281828459045 | +-------------------+ 1 row in set (0.00 sec)

11、對數(shù)的運算 ，已知底數(shù)為5，結(jié)果為25，求指數(shù)
log()：以e為底

mysql> select log(25); +--------------------+ | log(25) | +--------------------+ | 3.2188758248682006 | +--------------------+ 1 row in set (0.00 sec)

log 10():以10為底

mysql> select log10(100); +------------+ | log10(100) | +------------+ | 2 | +------------+ 1 row in set (0.00 sec)

12、角度和弧度互相轉(zhuǎn)化的函數(shù)
一個圓角，圓心角360°
弧度為2π弧度
180°對應π
90°對應1/2π

radians(x)：將角度轉(zhuǎn)化成弧度

mysql> select radians(180),radians(90),radians(360); +-------------------+--------------------+-------------------+ | radians(180) | radians(90) | radians(360) | +-------------------+--------------------+-------------------+ | 3.141592653589793 | 1.5707963267948966 | 6.283185307179586 | +-------------------+--------------------+-------------------+ 1 row in set (0.00 sec)mysql> select pi(); +----------+ | pi() | +----------+ | 3.141593 | +----------+ 1 row in set (0.00 sec)

degrees(x)：將弧度轉(zhuǎn)化為角度

mysql> select degrees(pi()),degrees(pi()/2),degrees(pi()*2); +---------------+-----------------+-----------------+ | degrees(pi()) | degrees(pi()/2) | degrees(pi()*2) | +---------------+-----------------+-----------------+ | 180 | 90 | 360 | +---------------+-----------------+-----------------+ 1 row in set (0.00 sec)

角A的領(lǐng)邊是b，對邊是a，斜邊是c

13、求正弦函數(shù)和反正弦函數(shù)
sin就是對邊比斜邊
sin(x)：求弧度為x的正弦值

mysql> select sin(pi()/6); +---------------------+ | sin(pi()/6) | +---------------------+ | 0.49999999999999994 | +---------------------+ 1 row in set (0.00 sec)

asin(x):求正弦函數(shù)值對應的弧度

mysql> select degrees(asin(0.5)); +--------------------+ | degrees(asin(0.5)) | +--------------------+ | 30.000000000000004 | +--------------------+ 1 row in set (0.00 sec)

14,、余弦函數(shù)

mysql> select cos(pi()/4); +--------------------+ | cos(pi()/4) | +--------------------+ | 0.7071067811865476 | +--------------------+ 1 row in set (0.00 sec)

acos(x)：求余弦函數(shù)值對應的弧度

mysql> select degrees(acos(0.7)); +--------------------+ | degrees(acos(0.7)) | +--------------------+ | 45.5729959991943 | +--------------------+ 1 row in set (0.00 sec)

15、正切函數(shù)

mysql> select tan(pi()/4); +--------------------+ | tan(pi()/4) | +--------------------+ | 0.9999999999999999 | +--------------------+ 1 row in set (0.01 sec)

atan(x)：求正切函數(shù)值對應的弧度

mysql> select degrees(atan(1)); +------------------+ | degrees(atan(1)) | +------------------+ | 45 | +------------------+ 1 row in set (0.00 sec)

16、余切函數(shù)

mysql> select cot(pi()/6),cot(pi()/3); +--------------------+-------------------+ | cot(pi()/6) | cot(pi()/3) | +--------------------+-------------------+ | 1.7320508075688774 | 0.577350269189626 | +--------------------+-------------------+ 1 row in set (0.00 sec)

字符串函數(shù)

1、計算字符長度的函數(shù)
char_length(str)：統(tǒng)計字符串的個數(shù)，空格也算一個字符

mysql> select char_length('shao zhuang bu nu li'); +-------------------------------------+ | char_length('shao zhuang bu lu li') | +-------------------------------------+ | 20 | +-------------------------------------+ 1 row in set (0.00 sec)mysql> select char_length('少壯不努力'); +--------------------------------+ | char_length('少壯不努力') | +--------------------------------+ | 5 | +--------------------------------+ 1 row in set (0.00 sec)

2、合并字符串函數(shù)
concat(s1,s2…sn)：值如果有null，則返回null
(‘老’)(‘大’)(‘徒傷悲’)

mysql> select concat('老','大','徒','傷','悲'); +---------------------------------------+ | concat('老','大','徒','傷','悲') | +---------------------------------------+ | 老大徒傷悲 | +---------------------------------------+ 1 row in set (0.00 sec)

concat_ws(x,s1,s2)：s1 x s2使用x作為分隔符，合并字符串

mysql> select concat_ws('@','1612977378','qq.com'); +--------------------------------------+ | concat_ws('@','1612977378','qq.com') | +--------------------------------------+ | 1612977378@qq.com | +--------------------------------------+ 1 row in set (0.00 sec)

3、替換字符串函數(shù)
insert(s1,x,len,s2)：從字符串s1x位開始，使用字符串s2進行替換，替換len的長度

mysql> select insert('xuexiaiwo',6,2,'buai'); +--------------------------------+ | insert('xuexiaiwo',6,2,'buai') | +--------------------------------+ | xuexibuaiwo | +--------------------------------+ 1 row in set (0.00 sec)

4、大小寫轉(zhuǎn)換函數(shù)
lower(str)：大寫轉(zhuǎn)換成小寫
lcase(str)：大寫轉(zhuǎn)換成小寫

mysql> select lower('SoS'),lcase('OvO'); +--------------+--------------+ | lower('SoS') | lcase('OvO') | +--------------+--------------+ | sos | ovo | +--------------+--------------+ 1 row in set (0.00 sec)

upper(str)：小寫轉(zhuǎn)換成大寫
ucase(str)：小寫轉(zhuǎn)換成大寫

mysql> select upper('SoS'),ucase('OvO'); +--------------+--------------+ | upper('SoS') | ucase('OvO') | +--------------+--------------+ | SOS | OVO | +--------------+--------------+ 1 row in set (0.00 sec)

5、獲取指定長度的字符串
left(s,n)：從左開始獲取字符串n位之后的字符
right(s,n)：從右開始獲取字符串n位之后的字符

mysql> select left('shaozhuangbunuli',4),right('shaozhuangbunuli',6); +----------------------------+-----------------------------+ | left('shaozhuangbunuli',4) | right('shaozhuangbunuli',6) | +----------------------------+-----------------------------+ | shao | bunuli | +----------------------------+-----------------------------+ 1 row in set (0.00 sec)

6、填充字符串函數(shù)
lpad(s1,len,s2)：將字符串s1通過字符串s2向左填充至len個字符
rpad(s1,len,s2)：將字符串s1通過字符串s2向右填充至len個字符

mysql> select lpad('456',6,'123'),rpad('456',6,'789'); +---------------------+--------------------+ | lpad('456',6,'123') | rpad('456',6,'789') | +---------------------+--------------------+ | 123456 | 456789 | +---------------------+--------------------+ 1 row in set (0.00 sec)

7、刪除空格的函數(shù)
ltrim(s)：刪除字符左邊的函數(shù)
rtrim(s)：刪除字符右邊的函數(shù)
trim(s)：刪除字符兩邊的空格

mysql> select ' AK47 ' as a,ltrim(' AK47')as a,rtrim(' AK47')as a,trim(' AK47')as a; +--------+------+-------+------+ | a | a | a | a | +--------+------+-------+------+ | AK47 | AK47 | AK47 | AK47 | +--------+------+-------+------+ 1 row in set (0.00 sec)

as表示重命名或者別名的意思

8、trim(s1 from str)：刪除指定字符串的函數(shù)，刪除字符串str兩邊的帶有字符串s1的內(nèi)容

mysql> select trim('AK' from 'AKAWMAKM4AK'); +-------------------------------+ | trim('AK' from 'AKAWMAKM4AK') | +-------------------------------+ | AWMAKM4 | +-------------------------------+ 1 row in set (0.00 sec)

9、repeat(str,n)：重復生成指定的字符串，將字符串str重復n次

mysql> select repeat('你kin',2); +--------------------+ | repeat('你kin',2) | +--------------------+ | 你kin你kin | +--------------------+ 1 row in set (0.00 sec)

10、space(n)：空格函數(shù)，生成n個空格

11、replace(s,s1,s2)：替換函數(shù)，將字符串中的字符s1替換成s2，區(qū)分大小寫

mysql> select replace('kin','k','K'); +------------------------+ | replace('kin','k','K') | +------------------------+ | Kin | +------------------------+ 1 row in set (0.00 sec)

12、strcmp(s1,s2)：比較字符串之間的大小

mysql> select strcmp('a','A'); +-----------------+ | strcmp('a','A') | +-----------------+ | 0 | +-----------------+ 1 row in set (0.00 sec)

如果s1>s2結(jié)果返回1，如果s1=s2結(jié)果返回0，如果s1<s2結(jié)果返回-1

13、獲取子字符串的函數(shù)
substring(str,n,len)：對字符串str的第n位開始獲取字符串，獲取len個字符
n為正表示從左開始數(shù)，n為負表示從右開始數(shù)0

mysql> select substring('kong ni ji wa',1,4); +--------------------------------+ | substring('kong ni ji wa',1,4) | +--------------------------------+ | kong | +--------------------------------+ 1 row in set (0.00 sec)

mid(str,n,len)：對字符串str的第n位開始獲取字符串，獲取len個字符

14、匹配字符串開始位置的函數(shù)
locate(str1,str)：str1在str的第幾位開始
position(str1 in str)：str1在str的第幾位開始
instr(str,str1)

mysql> select locate('zhi','xueyizhiyong'),position('xue' in 'xueyizhiyong'); +------------------------------+-----------------------------------+ | locate('zhi','xueyizhiyong') | position('xue' in 'xueyizhiyong') | +------------------------------+-----------------------------------+ | 6 | 1 | +------------------------------+-----------------------------------+ 1 row in set (0.00 sec) mysql> select instr('xueyizhiyong','yi'); +----------------------------+ | instr('xueyizhiyong','yi') | +----------------------------+ | 4 | +----------------------------+ 1 row in set (0.00 sec)

15、reverse(s)：對字符串s中的字符進行反轉(zhuǎn)

mysql> select reverse('7654321'); +--------------------+ | reverse('7654321') | +--------------------+ | 1234567 | +--------------------+ 1 row in set (0.00 sec)

16、elt(n,s1,s2,s3…sn)：返回指定位置的字符串

mysql> select elt(3,'ni','hao','ya','word'); +-------------------------------+ | elt(3,'ni','hao','ya','word') | +-------------------------------+ | ya | +-------------------------------+ 1 row in set (0.00 sec)

17、field(s,s1,s2…sn)：返回指定字符串位置的函數(shù)

mysql> select field('how','are','you'); +--------------------------+ | field('how','are','you') | +--------------------------+ | 0 | +--------------------------+ 1 row in set (0.00 sec)

18、find_in_set(s1,s2)：返回字符串s1在字符串集合s2中的位置

mysql> select find_in_set('zhi','xue,yi,zhi,yong'); +--------------------------------------+ | find_in_set('zhi','xue,yi,zhi,yong') | +--------------------------------------+ | 3 | +--------------------------------------+ 1 row in set (0.00 sec)

查詢

#創(chuàng)建子表 mysql> create database rzh; Query OK, 1 row affected (0.01 sec)mysql> use rzh; Database changed mysql> create table student(-> id int primary key auto_increment, #自增約束-> name varchar(6) default null, #默認值約束，默認為空-> class_id int default 1,-> sex char(3) not null,-> cj int default 0-> ); Query OK, 0 rows affected (0.10 sec)mysql> insert into student values(1,'迪迦',1,'G',88); Query OK, 1 row affected (0.00 sec)mysql> insert into student values(2,'賽文',1,'G',78); Query OK, 1 row affected (0.00 sec)mysql> insert into student values(3,'戴拿',1,'G',79); Query OK, 1 row affected (0.00 sec)mysql> insert into student values(4,'賽羅',2,'G',85); Query OK, 1 row affected (0.00 sec)mysql> insert into student values(5,'泰羅',2,'G',87); Query OK, 1 row affected (0.00 sec)mysql> insert into student values(6,'雷歐',2,'G',88); Query OK, 1 row affected (0.00 sec)mysql> insert into student values(7,'熊大',3,'G',86); Query OK, 1 row affected (0.29 sec)mysql> insert into student values(8,'熊二',3,'G',85); Query OK, 1 row affected (0.00 sec)mysql> insert into student values(9,'喜羊羊',4,'G',90); Query OK, 1 row affected (0.01 sec)mysql> insert into student values(10,'懶羊羊',4,'G',65); Query OK, 1 row affected (0.00 sec)mysql> insert into student values(11,'美羊羊',4,'M',86); Query OK, 1 row affected (0.00 sec)mysql> insert into student values(12,'灰太狼',5,'G',95); Query OK, 1 row affected (0.00 sec)mysql> insert into student values(13,'紅太狼',5,'M',80); Query OK, 1 row affected (0.00 sec 創(chuàng)建父表 mysql> create table class(-> id int primary key auto_increment,-> name char(6) default null-> ); Query OK, 0 rows affected (0.05 sec)mysql> insert into class values(1,'老奧特曼班'); Query OK, 1 row affected (0.00 sec)mysql> insert into class values(2,'小奧特曼班'); Query OK, 1 row affected (0.00 sec)mysql> insert into class values(3,'狗熊班'); Query OK, 1 row affected (0.01 sec)mysql> insert into class values(4,'肥羊班'); Query OK, 1 row affected (0.00 sec)mysql> insert into class values(5,'大灰狼班'); Query OK, 1 row affected (0.00 sec)

1、基本查詢

語法： select * from 表名；例： select * from student; select * from class;

2、條件查詢： 根據(jù)條件語句對數(shù)據(jù)表進行數(shù)據(jù)的查詢，返回的是符合條件的數(shù)據(jù)
格式：
select {*|字段名} from table where 條件語句

條件語句中常用的查詢條件：

#比特符：=，>,<,>=,<=,!=,<>

例：
查詢迪迦的相關(guān)信息

mysql> select * from student where name='迪迦'; +----+--------+----------+-----+------+ | id | name | class_id | sex | cj | +----+--------+----------+-----+------+ | 1 | 迪迦 | 1 | G | 88 | +----+--------+----------+-----+------+ 1 row in set (1.74 sec)

查詢老奧特曼班學生的名字

mysql> select * from class where name='老奧特曼班'; +----+-----------------+ | id | name | +----+-----------------+ | 1 | 老奧特曼班 | +----+-----------------+ 1 row in set (0.00 sec)mysql> select * from student where class_id=1; +----+--------+----------+-----+------+ | id | name | class_id | sex | cj | +----+--------+----------+-----+------+ | 1 | 迪迦 | 1 | G | 88 | | 2 | 賽文 | 1 | G | 78 | | 3 | 戴拿 | 1 | G | 79 | +----+--------+----------+-----+------+ 3 rows in set (0.00 sec)

查詢成績達到85-100分之間的小朋友

mysql> select * from student where cj between 85 and 100; +----+-----------+----------+-----+------+ | id | name | class_id | sex | cj | +----+-----------+----------+-----+------+ | 1 | 迪迦 | 1 | G | 88 | | 4 | 賽羅 | 2 | G | 85 | | 5 | 泰羅 | 2 | G | 87 | | 6 | 雷歐 | 2 | G | 88 | | 7 | 熊大 | 3 | G | 86 | | 8 | 熊二 | 3 | G | 85 | | 9 | 喜羊羊 | 4 | G | 90 | | 11 | 美羊羊 | 4 | M | 86 | | 12 | 灰太狼 | 5 | G | 95 | +----+-----------+----------+-----+------+ 9 rows in set (0.00 sec)

#確定集合：in，not in

例：查看班級編號1,2,3的學生的id和姓名

mysql> select id,name from student where class_id in (1,2,3); +----+--------+ | id | name | +----+--------+ | 1 | 迪迦 | | 2 | 賽文 | | 3 | 戴拿 | | 4 | 賽羅 | | 5 | 泰羅 | | 6 | 雷歐 | | 7 | 熊大 | | 8 | 熊二 | +----+--------+ 8 rows in set (0.01 sec)

#字符匹配：like，not like
常用的通配符符號_表示任意一個字符，%表示任意個任意字符

例：找到成績?yōu)?5分的所有同學

mysql> select * from student where cj like (85); +----+--------+----------+-----+------+ | id | name | class_id | sex | cj | +----+--------+----------+-----+------+ | 4 | 賽羅 | 2 | G | 85 | | 8 | 熊二 | 3 | G | 85 | +----+--------+----------+-----+------+ 2 rows in set (0.00 sec)

找到所有姓賽的同學

mysql> select * from student where name like '賽%'; +----+--------+----------+-----+------+ | id | name | class_id | sex | cj | +----+--------+----------+-----+------+ | 2 | 賽文 | 1 | G | 78 | | 4 | 賽羅 | 2 | G | 85 | +----+--------+----------+-----+------+ 2 rows in set (0.00 sec)

#空值判斷：is null，is not null
例：
查看學生表的成績字段是否存在空值

mysql> select * from student where cj is null; Empty set (0.00 sec)mysql> select * from student where cj is not null; +----+-----------+----------+-----+------+ | id | name | class_id | sex | cj | +----+-----------+----------+-----+------+ | 1 | 迪迦 | 1 | G | 88 | | 2 | 賽文 | 1 | G | 78 | | 3 | 戴拿 | 1 | G | 79 | | 4 | 賽羅 | 2 | G | 85 | | 5 | 泰羅 | 2 | G | 87 | | 6 | 雷歐 | 2 | G | 88 | | 7 | 熊大 | 3 | G | 86 | | 8 | 熊二 | 3 | G | 85 | | 9 | 喜羊羊 | 4 | G | 90 | | 10 | 懶羊羊 | 4 | G | 65 | | 11 | 美羊羊 | 4 | M | 86 | | 12 | 灰太狼 | 5 | G | 95 | | 13 | 紅太狼 | 5 | M | 80 | +----+-----------+----------+-----+------+ 13 rows in set (0.00 sec)

#多重條件查詢：當查詢的條件有多個時，可以通過and，or，not對查詢條件進行鏈接和限制

條件1 and 條件2 and 條件。。。查詢條件需要滿足前后所有的條件
例：
查詢班級編號為2且分數(shù)大于85分的同學的姓名

mysql> select name from student where class_id=2 and cj>85; +--------+ | name | +--------+ | 泰羅 | | 雷歐 | +--------+ 2 rows in set (0.00 sec)

條件1 or 條件2 or 。。。。查詢條件只需滿足任一條件即可
例：
查詢是女生或成績低于85分的同學的姓名

mysql> select name from student where sex='M' or cj<85; +-----------+ | name | +-----------+ | 賽文 | | 戴拿 | | 懶羊羊 | | 美羊羊 | | 紅太狼 | +-----------+ 5 rows in set (0.00 sec)

not條件：返回不滿足查詢條件的查詢結(jié)果
例：
查詢班級編號不是2的同學

mysql> select * from student where not class_id=2; +----+-----------+----------+-----+------+ | id | name | class_id | sex | cj | +----+-----------+----------+-----+------+ | 1 | 迪迦 | 1 | G | 88 | | 2 | 賽文 | 1 | G | 78 | | 3 | 戴拿 | 1 | G | 79 | | 7 | 熊大 | 3 | G | 86 | | 8 | 熊二 | 3 | G | 85 | | 9 | 喜羊羊 | 4 | G | 90 | | 10 | 懶羊羊 | 4 | G | 65 | | 11 | 美羊羊 | 4 | M | 86 | | 12 | 灰太狼 | 5 | G | 95 | | 13 | 紅太狼 | 5 | M | 80 | +----+-----------+----------+-----+------+ 10 rows in set (0.00 sec)

3、對查詢結(jié)果進行排序
格式：
select {*|字段} from 表名 order by 字段名 [desc]
例：
將成績從低到高進行排序

mysql> select * from student order by cj; +----+-----------+----------+-----+------+ | id | name | class_id | sex | cj | +----+-----------+----------+-----+------+ | 10 | 懶羊羊 | 4 | G | 65 | | 2 | 賽文 | 1 | G | 78 | | 3 | 戴拿 | 1 | G | 79 | | 13 | 紅太狼 | 5 | M | 80 | | 4 | 賽羅 | 2 | G | 85 | | 8 | 熊二 | 3 | G | 85 | | 11 | 美羊羊 | 4 | M | 86 | | 7 | 熊大 | 3 | G | 86 | | 5 | 泰羅 | 2 | G | 87 | | 6 | 雷歐 | 2 | G | 88 | | 1 | 迪迦 | 1 | G | 88 | | 9 | 喜羊羊 | 4 | G | 90 | | 12 | 灰太狼 | 5 | G | 95 | +----+-----------+----------+-----+------+ 13 rows in set (0.00 sec)

例：將成績從高到低進行排序

mysql> select * from student order by cj desc; +----+-----------+----------+-----+------+ | id | name | class_id | sex | cj | +----+-----------+----------+-----+------+ | 12 | 灰太狼 | 5 | G | 95 | | 9 | 喜羊羊 | 4 | G | 90 | | 1 | 迪迦 | 1 | G | 88 | | 6 | 雷歐 | 2 | G | 88 | | 5 | 泰羅 | 2 | G | 87 | | 11 | 美羊羊 | 4 | M | 86 | | 7 | 熊大 | 3 | G | 86 | | 8 | 熊二 | 3 | G | 85 | | 4 | 賽羅 | 2 | G | 85 | | 13 | 紅太狼 | 5 | M | 80 | | 3 | 戴拿 | 1 | G | 79 | | 2 | 賽文 | 1 | G | 78 | | 10 | 懶羊羊 | 4 | G | 65 | +----+-----------+----------+-----+------+ 13 rows in set (1.74 sec)

4、分頁查詢：當查詢結(jié)果數(shù)量很多時，可以通過分頁查詢來進行控制
格式：
select {*|字段名} from 表名 [where 條件語句] limit offset

例：查看學生表中前3行的數(shù)據(jù)

mysql> select * from student limit 3; +----+--------+----------+-----+------+ | id | name | class_id | sex | cj | +----+--------+----------+-----+------+ | 1 | 迪迦 | 1 | G | 88 | | 2 | 賽文 | 1 | G | 78 | | 3 | 戴拿 | 1 | G | 79 | +----+--------+----------+-----+------+ 3 rows in set (0.00 sec)

例：查看學生表中第5行到第9行的信息

select * from student limit 5 offset 4;

例：查看學生表中第3行到第六行

select * from student limit 2,4;

5、分組查詢：對查詢結(jié)果進行分組顯示，通常會搭配聚合函數(shù)一起使用
也可以和group_concat()搭配使用顯示每個組成員的信息

格式：
select {*|字段|count(字段)|groupconcat(字段)} from 表名
[where 條件語句] group by 字段[having 條件語句]

常用的聚合函數(shù) ：
count()：統(tǒng)計計數(shù)
max()：求最大值
min()：求最小值
sum()：求和
avg()：求平均值

例：將學生按照性別分組，并且統(tǒng)計男生多少人，女生多少人

mysql> select sex,count(sex) from student group by sex; +-----+------------+ | sex | count(sex) | +-----+------------+ | G | 11 | | M | 2 | +-----+------------+ 2 rows in set (0.00 sec)

例：將學生按照班級分組，并統(tǒng)計每個班的人數(shù)，把名字列出

mysql> select class_id,count(class_id) num,group_concat(name) name from student group by class_id; +----------+-----+-------------------------------+ | class_id | num | name | +----------+-----+-------------------------------+ | 1 | 3 | 迪迦,賽文,戴拿 | | 2 | 3 | 賽羅,泰羅,雷歐 | | 3 | 2 | 熊二,熊大 | | 4 | 3 | 喜羊羊,懶羊羊,美羊羊 | | 5 | 2 | 灰太狼,紅太狼 | +----------+-----+-------------------------------+ 5 rows in set (0.00 sec)

例：將學生按成績分組，統(tǒng)計每個成績的人數(shù)，把名字列出

mysql> select cj,count(cj) num,group_concat(name) name from student group by cj; +------+-----+------------------+ | cj | num | name | +------+-----+------------------+ | 65 | 1 | 懶羊羊 | | 78 | 1 | 賽文 | | 79 | 1 | 戴拿 | | 80 | 1 | 紅太狼 | | 85 | 2 | 賽羅,熊二 | | 86 | 2 | 美羊羊,熊大 | | 87 | 1 | 泰羅 | | 88 | 2 | 雷歐,迪迦 | | 90 | 1 | 喜羊羊 | | 95 | 1 | 灰太狼 | +------+-----+------------------+ 10 rows in set (0.00 sec)

6、多表查詢：從指定的多張表進行查詢
格式：
select {*|字段} from 表1，表2… [where 條件語句]
例：同時查看學生表和班級表中的數(shù)據(jù)

mysql> select * from student,class;

7、連接查詢，在多表查詢時，通過對多表中合適的字段建立關(guān)系，優(yōu)化查詢結(jié)果
內(nèi)連接：自然連接查詢內(nèi)查詢
外連接：左連接右連接
自然連接查詢：通過自然連接查詢的方式查詢班級和學生信息表的信息

select * from student,class where student.class_id=class.id; select student.name,student.cj,class.name from student,class where student.class_id=class.id;

內(nèi)查詢：
格式：
select {*|字段} from 表1 inner join 表2 on 連接條件

select student.name,student.cj,class.name from student inner join class on student.class_id=class.id;

外連接-左連接
返回表1都存在的行，如果建立左連接關(guān)系的字段，只有表一存在，那么表二對應內(nèi)容會
顯示null
格式
select {*|字段} from 表1 left outer join 表2 on 連接條件

select student.name,student.cj,class.name from student leftouter join class on student.class_id=class.id;

外連接-右連接
返回表2都存在的行，如果建立右連接關(guān)系的字段的值，只有表2存在，那么表一
相對應的值會以null顯示
格式
select {*|字段} from 表1 right outer join 表2 on 連接條件

select * from student right outer join class on student.class_id=class.id; insert into class values(6,'鎧甲勇士班'); insert into student values(13,'奧利給班',7,'G',18);

8、聯(lián)合查詢：把兩次或多次查詢結(jié)果和并起來，要求對兩張或多張表查詢結(jié)果的字段數(shù)量必須一致，數(shù)據(jù)類型和字段名可以不一樣，顯示查詢結(jié)果時，字段名以第一張表的字段名顯示

格式：

select {*|字段} from 表名 union [all] select {*|字段} from 表名

例：

select id,class_id from student union select * from class;

9、嵌套查詢：將一個查詢塊（一個select-from-where語句稱為一個查詢塊）
嵌套在另一個查詢塊的where子句中或having短句的條件中查詢

嵌套查詢的三種類型：
#使用IN的子查詢：在嵌套查詢中，子查詢的結(jié)果如果是一個集合，就可以使用關(guān)鍵字
IN來調(diào)用子查詢的結(jié)果

例：
查詢成績和熊二，熊大一樣的學生的名字分數(shù)

mysql> select name,cj from student where cj in (select cj from student where name='熊二' or name='熊大'); +-----------+------+ | name | cj | +-----------+------+ | 賽羅 | 85 | | 熊大 | 86 | | 熊二 | 85 | | 美羊羊 | 86 | +-----------+------+ 4 rows in set (0.00 sec)

查詢成績和熊二，熊大不一樣的學生的名字分數(shù)

mysql> select name,cj from student where cj not in (select cj from student where name='熊二' or name='熊大'); +-----------+------+ | name | cj | +-----------+------+ | 迪迦 | 88 | | 賽文 | 78 | | 戴拿 | 79 | | 泰羅 | 87 | | 雷歐 | 88 | | 喜羊羊 | 90 | | 懶羊羊 | 65 | | 灰太狼 | 95 | | 紅太狼 | 80 | +-----------+------+ 9 rows in set (0.00 sec)

#帶有比較符號的嵌套查詢：如果子查詢語句返回的結(jié)果是單個值，那么父查詢就可以通過比較符來調(diào)用子查詢的結(jié)果

例：
查詢成績大于迪迦的學生的名字和分數(shù)

mysql> select name,cj from student where cj>(select cj from student where name='迪迦'); +-----------+------+ | name | cj | +-----------+------+ | 喜羊羊 | 90 | | 灰太狼 | 95 | +-----------+------+ 2 rows in set (0.00 sec)

#通過any和all的子查詢，當子查詢的個數(shù)有多個時，可以使用any和all搭配比較符來調(diào)用子查詢的結(jié)果

例：查詢成績低于喜羊羊，迪迦，熊大的名字和分數(shù)

mysql> select name,cj from student where cj < all (select cj from student where name='喜羊羊' or name='迪迦' or name='熊大'); +-----------+------+ | name | cj | +-----------+------+ | 賽文 | 78 | | 戴拿 | 79 | | 賽羅 | 85 | | 熊二 | 85 | | 懶羊羊 | 65 | | 紅太狼 | 80 | +-----------+------+ 6 rows in set (0.00 sec)

查詢練習

1、創(chuàng)建數(shù)據(jù)表employee和dept，并插入數(shù)據(jù)

mysql> create database rzh; Query OK, 1 row affected (0.00 sec)mysql> use rzh; Database changed#創(chuàng)建employee表 mysql> create table employee( e_id int(11) not null unique,-> e_name varchar(50) not null, -> e_sex char(2), dept_id int(11) not null, -> e_job varchar(50) not null,-> e_salary int(11) not null, -> hireDate date not null , -> constraint employee_dept foreign key(dept_id) references dept(dept_id) ); Query OK, 0 rows affected (0.03 sec)#創(chuàng)建dept表 mysql> create table dept(-> dept_id int(11) primary key not null unique auto_increment,-> d_name varchar(50) not null,-> d_location varchar(100)-> ); Query OK, 0 rows affected (0.00 sec)查看一下表結(jié)構(gòu)： mysql> desc employee; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | e_id | int(11) | NO | PRI | NULL | | | e_name | varchar(50) | NO | | NULL | | | e_sex | char(2) | YES | | NULL | | | dept_id | int(11) | NO | | NULL | | | e_job | varchar(50) | NO | | NULL | | | e_salary | int(11) | NO | | NULL | | | hireDate | date | NO | | NULL | | +----------+-------------+------+-----+---------+-------+ 7 rows in set (0.00 sec)mysql> desc dept; +------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+------+-----+---------+----------------+ | dept_id | int(11) | NO | PRI | NULL | auto_increment | | d_name | varchar(50) | NO | | NULL | | | d_location | varchar(100) | YES | | NULL | | +------------+--------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec) #向employee表中插入數(shù)據(jù) mysql> insert into employee values(1001,'SMTTH','M',20,'CLERK',800,'2005-11-12'); Query OK, 1 row affected (0.00 sec)mysql> insert into employee values(1002,'ALLEN','F',30,'SALESMAN',1600,'2003-05-12'); Query OK, 1 row affected (0.01 sec)mysql> insert into employee values(1003,'WARO','F',30,'SALESMAN',1250,'2003-05-12'); Query OK, 1 row affected (0.00 sec)mysql> insert into employee values(1004,'JONES','M',20,'MANAGER',2975,'1998-05-18'); Query OK, 1 row affected (0.00 sec)mysql> insert into employee values(1007,'CLARK','M',10,'MANAGER',2450,'2002-09-12'); Query OK, 1 row affected (0.29 sec)mysql> insert into employee values(1008,'SCOTT','M',20,'ANALYSE',3000,'2003-05-12'); Query OK, 1 row affected (0.00 sec)mysql> insert into employee values(1009,'KING','F',10,'PRESIDENT',5000,'1995-01-01'); Query OK, 1 row affected (0.00 sec)mysql> insert into employee values(1010,'TURNER','F',30,'SALESMAN',1500,'1997-10-12'); Query OK, 1 row affected (0.00 sec)mysql> insert into employee values(1011,'ADAMS','M',20,'CLERK',1100,'1999-10-05'); Query OK, 1 row affected (0.00 sec)mysql> insert into employee values(1012,'JAMES','F',30,'CLERK',950,'2008-06-15'); Query OK, 1 row affected (0.00 sec)#向dept表中添加數(shù)據(jù) mysql> insert into dept values(10,'ACCOUNTINC','shanghai'); Query OK, 1 row affected (0.00 sec)mysql> insert into dept values(20,'RESEARCH','Beijing'); Query OK, 1 row affected (0.00 sec)mysql> insert into dept values(30,'SALES','ShenZhen'); Query OK, 1 row affected (0.00 sec)mysql> insert into dept values(40,'OPERATTONS','FuJian'); Query OK, 1 row affected (0.00 sec)

2、在employee表中，查詢所有記錄的e_id，e_name和e_salary字段值

mysql> select e_id,e_name,e_salary from employee -> ; +------+--------+----------+ | e_id | e_name | e_salary | +------+--------+----------+ | 1001 | SMTTH | 800 | | 1002 | ALLEN | 1600 | | 1003 | WARO | 1250 | | 1004 | JONES | 2975 | | 1005 | MARTIN | 1250 | | 1006 | BLAKE | 2850 | | 1007 | CLARK | 2450 | | 1008 | SCOTT | 3000 | | 1009 | KING | 5000 | | 1010 | TURNER | 1500 | | 1011 | ADAMS | 1100 | | 1012 | JAMES | 950 | +------+--------+----------+ 12 rows in set (0.00 sec)

3、在employee表中，查詢dept_id等于10和20的所有記錄

mysql> select * from employee where dept_id in (10,20); +------+--------+-------+---------+-----------+----------+------------+ | e_id | e_name | e_sex | dept_id | e_job | e_salary | hireDate | +------+--------+-------+---------+-----------+----------+------------+ | 1001 | SMTTH | M | 20 | CLERK | 800 | 2005-11-12 | | 1004 | JONES | M | 20 | MANAGER | 2975 | 1998-05-18 | | 1007 | CLARK | M | 10 | MANAGER | 2450 | 2002-09-12 | | 1008 | SCOTT | M | 20 | ANALYSE | 3000 | 2003-05-12 | | 1009 | KING | F | 10 | PRESIDENT | 5000 | 1995-01-01 | | 1011 | ADAMS | M | 20 | CLERK | 1100 | 1999-10-05 | +------+--------+-------+---------+-----------+----------+------------+ 6 rows in set (0.00 sec)

4、在employee表中，查詢工資范圍在800-2500之間的員工的信息

mysql> select * from employee where e_salary between 800 and 2500; +------+--------+-------+---------+----------+----------+------------+ | e_id | e_name | e_sex | dept_id | e_job | e_salary | hireDate | +------+--------+-------+---------+----------+----------+------------+ | 1001 | SMTTH | M | 20 | CLERK | 800 | 2005-11-12 | | 1002 | ALLEN | F | 30 | SALESMAN | 1600 | 2003-05-12 | | 1003 | WARO | F | 30 | SALESMAN | 1250 | 2003-05-12 | | 1005 | MARTIN | M | 30 | SALESMAN | 1250 | 2001-06-12 | | 1007 | CLARK | M | 10 | MANAGER | 2450 | 2002-09-12 | | 1010 | TURNER | F | 30 | SALESMAN | 1500 | 1997-10-12 | | 1011 | ADAMS | M | 20 | CLERK | 1100 | 1999-10-05 | | 1012 | JAMES | F | 30 | CLERK | 950 | 2008-06-15 | +------+--------+-------+---------+----------+----------+------------+ 8 rows in set (0.00 sec)

5、在employee表中，查詢部門編號為20的部門中的員工信息

mysql> select * from employee where dept_id=20; +------+--------+-------+---------+---------+----------+------------+ | e_id | e_name | e_sex | dept_id | e_job | e_salary | hireDate | +------+--------+-------+---------+---------+----------+------------+ | 1001 | SMTTH | M | 20 | CLERK | 800 | 2005-11-12 | | 1004 | JONES | M | 20 | MANAGER | 2975 | 1998-05-18 | | 1008 | SCOTT | M | 20 | ANALYSE | 3000 | 2003-05-12 | | 1011 | ADAMS | M | 20 | CLERK | 1100 | 1999-10-05 | +------+--------+-------+---------+---------+----------+------------+ 4 rows in set (0.00 sec)

6、在employee表中，查詢每個部門最高工資的員工信息

mysql> select * from employee order by e_salary desc; +------+--------+-------+---------+-----------+----------+------------+ | e_id | e_name | e_sex | dept_id | e_job | e_salary | hireDate | +------+--------+-------+---------+-----------+----------+------------+ | 1009 | KING | F | 10 | PRESIDENT | 5000 | 1995-01-01 | | 1008 | SCOTT | M | 20 | ANALYSE | 3000 | 2003-05-12 | | 1004 | JONES | M | 20 | MANAGER | 2975 | 1998-05-18 | | 1006 | BLAKE | F | 30 | MANAGER | 2850 | 1997-02-15 | | 1007 | CLARK | M | 10 | MANAGER | 2450 | 2002-09-12 | | 1002 | ALLEN | F | 30 | SALESMAN | 1600 | 2003-05-12 | | 1010 | TURNER | F | 30 | SALESMAN | 1500 | 1997-10-12 | | 1005 | MARTIN | M | 30 | SALESMAN | 1250 | 2001-06-12 | | 1003 | WARO | F | 30 | SALESMAN | 1250 | 2003-05-12 | | 1011 | ADAMS | M | 20 | CLERK | 1100 | 1999-10-05 | | 1012 | JAMES | F | 30 | CLERK | 950 | 2008-06-15 | | 1001 | SMTTH | M | 20 | CLERK | 800 | 2005-11-12 | +------+--------+-------+---------+-----------+----------+------------+ 12 rows in set (0.00 sec)

7、查詢員工BLAKE所在部門和部門所在地

8、使用連接查詢、查詢所有員工的部門和部門信息

9、在employee表中，計算每個部門各多少名員工

mysql> select dept_id,count(dept_id) from employee group by dept_id; +---------+----------------+ | dept_id | count(dept_id) | +---------+----------------+ | 10 | 2 | | 20 | 4 | | 30 | 6 | +---------+----------------+ 3 rows in set (0.00 sec)

10、在employee表中，計算不同類型職業(yè)的總工資數(shù)

11、在employee表中，計算不同部門的平均工資

mysql> select dept_id,avg(e_salary) from employee group by dept_id; +---------+---------------+ | dept_id | avg(e_salary) | +---------+---------------+ | 10 | 3725.0000 | | 20 | 1968.7500 | | 30 | 1566.6667 | +---------+---------------+ 3 rows in set (0.00 sec)

12、在employee表中，查詢工資低于1500的員工信息

mysql> select * from employee where e_salary<1500; +------+--------+-------+---------+----------+----------+------------+ | e_id | e_name | e_sex | dept_id | e_job | e_salary | hireDate | +------+--------+-------+---------+----------+----------+------------+ | 1001 | SMTTH | M | 20 | CLERK | 800 | 2005-11-12 | | 1003 | WARO | F | 30 | SALESMAN | 1250 | 2003-05-12 | | 1005 | MARTIN | M | 30 | SALESMAN | 1250 | 2001-06-12 | | 1011 | ADAMS | M | 20 | CLERK | 1100 | 1999-10-05 | | 1012 | JAMES | F | 30 | CLERK | 950 | 2008-06-15 | +------+--------+-------+---------+----------+----------+------------+ 5 rows in set (0.00 sec)

13、在employee表中，將查詢記錄先按照部門編號由高到底排列，在按員工工資由高到底排列

mysql> select * from employee order by dept_id desc,e_salary desc; +------+--------+-------+---------+-----------+----------+------------+ | e_id | e_name | e_sex | dept_id | e_job | e_salary | hireDate | +------+--------+-------+---------+-----------+----------+------------+ | 1006 | BLAKE | F | 30 | MANAGER | 2850 | 1997-02-15 | | 1002 | ALLEN | F | 30 | SALESMAN | 1600 | 2003-05-12 | | 1010 | TURNER | F | 30 | SALESMAN | 1500 | 1997-10-12 | | 1003 | WARO | F | 30 | SALESMAN | 1250 | 2003-05-12 | | 1005 | MARTIN | M | 30 | SALESMAN | 1250 | 2001-06-12 | | 1012 | JAMES | F | 30 | CLERK | 950 | 2008-06-15 | | 1008 | SCOTT | M | 20 | ANALYSE | 3000 | 2003-05-12 | | 1004 | JONES | M | 20 | MANAGER | 2975 | 1998-05-18 | | 1011 | ADAMS | M | 20 | CLERK | 1100 | 1999-10-05 | | 1001 | SMTTH | M | 20 | CLERK | 800 | 2005-11-12 | | 1009 | KING | F | 10 | PRESIDENT | 5000 | 1995-01-01 | | 1007 | CLARK | M | 10 | MANAGER | 2450 | 2002-09-12 | +------+--------+-------+---------+-----------+----------+------------+ 12 rows in set (0.00 sec)

14、在employee表中，查詢員工姓名以字母A或S開頭的員工信息

mysql> select * from employee where e_name like 'A%' or e_name like 'S%'; +------+--------+-------+---------+----------+----------+------------+ | e_id | e_name | e_sex | dept_id | e_job | e_salary | hireDate | +------+--------+-------+---------+----------+----------+------------+ | 1001 | SMTTH | M | 20 | CLERK | 800 | 2005-11-12 | | 1002 | ALLEN | F | 30 | SALESMAN | 1600 | 2003-05-12 | | 1008 | SCOTT | M | 20 | ANALYSE | 3000 | 2003-05-12 | | 1011 | ADAMS | M | 20 | CLERK | 1100 | 1999-10-05 | +------+--------+-------+---------+----------+----------+------------+ 4 rows in set (0.00 sec)

15、在employee表中，查詢到目前為止，工齡大于等于10年的員工信息

mysql> select * from employee where hireDate<=date_sub(curdate(),interval 10 year); +------+--------+-------+---------+-----------+----------+------------+ | e_id | e_name | e_sex | dept_id | e_job | e_salary | hireDate | +------+--------+-------+---------+-----------+----------+------------+ | 1001 | SMTTH | M | 20 | CLERK | 800 | 2005-11-12 | | 1002 | ALLEN | F | 30 | SALESMAN | 1600 | 2003-05-12 | | 1003 | WARO | F | 30 | SALESMAN | 1250 | 2003-05-12 | | 1004 | JONES | M | 20 | MANAGER | 2975 | 1998-05-18 | | 1005 | MARTIN | M | 30 | SALESMAN | 1250 | 2001-06-12 | | 1006 | BLAKE | F | 30 | MANAGER | 2850 | 1997-02-15 | | 1007 | CLARK | M | 10 | MANAGER | 2450 | 2002-09-12 | | 1008 | SCOTT | M | 20 | ANALYSE | 3000 | 2003-05-12 | | 1009 | KING | F | 10 | PRESIDENT | 5000 | 1995-01-01 | | 1010 | TURNER | F | 30 | SALESMAN | 1500 | 1997-10-12 | | 1011 | ADAMS | M | 20 | CLERK | 1100 | 1999-10-05 | | 1012 | JAMES | F | 30 | CLERK | 950 | 2008-06-15 | +------+--------+-------+---------+-----------+----------+------------+ 12 rows in set (0.00 sec)

16、計算所有女員工（F）的年齡

mysql> select e_name,e_sex,year(curdate()) -year(hireDate) as year from employee where e_sex='F'; +--------+-------+------+ | e_name | e_sex | year | +--------+-------+------+ | ALLEN | F | 17 | | WARO | F | 17 | | BLAKE | F | 23 | | KING | F | 25 | | TURNER | F | 23 | | JAMES | F | 12 | +--------+-------+------+ 6 rows in set (0.00 sec)

17、使用LIMIT查詢從第3條記錄開始到第6條記錄

mysql> select * from employee limit 2,4; +------+--------+-------+---------+----------+----------+------------+ | e_id | e_name | e_sex | dept_id | e_job | e_salary | hireDate | +------+--------+-------+---------+----------+----------+------------+ | 1003 | WARO | F | 30 | SALESMAN | 1250 | 2003-05-12 | | 1004 | JONES | M | 20 | MANAGER | 2975 | 1998-05-18 | | 1005 | MARTIN | M | 30 | SALESMAN | 1250 | 2001-06-12 | | 1006 | BLAKE | F | 30 | MANAGER | 2850 | 1997-02-15 | +------+--------+-------+---------+----------+----------+------------+ 4 rows in set (0.00 sec)

18、查詢銷售人員（SALSEMAN）的最低工資

19、查詢名字以字母N或者S結(jié)尾的記錄

mysql> select * from employee where e_name like '%N' or e_name like '%S'; +------+--------+-------+---------+----------+----------+------------+ | e_id | e_name | e_sex | dept_id | e_job | e_salary | hireDate | +------+--------+-------+---------+----------+----------+------------+ | 1002 | ALLEN | F | 30 | SALESMAN | 1600 | 2003-05-12 | | 1004 | JONES | M | 20 | MANAGER | 2975 | 1998-05-18 | | 1005 | MARTIN | M | 30 | SALESMAN | 1250 | 2001-06-12 | | 1011 | ADAMS | M | 20 | CLERK | 1100 | 1999-10-05 | | 1012 | JAMES | F | 30 | CLERK | 950 | 2008-06-15 | +------+--------+-------+---------+----------+----------+------------+ 5 rows in set (0.00 sec)

20、查詢在BeiJing工作的員工的姓名和職務

21、使用左連接方式查詢employee和dept表

mysql> select * from employee left outer join dept on employee.dept_id=dept.dept_id; +------+--------+-------+---------+-----------+----------+------------+---------+------------+------------+ | e_id | e_name | e_sex | dept_id | e_job | e_salary | hireDate | dept_id | d_name | d_location | +------+--------+-------+---------+-----------+----------+------------+---------+------------+------------+ | 1007 | CLARK | M | 10 | MANAGER | 2450 | 2002-09-12 | 10 | ACCOUNTINC | shanghai | | 1009 | KING | F | 10 | PRESIDENT | 5000 | 1995-01-01 | 10 | ACCOUNTINC | shanghai | | 1001 | SMTTH | M | 20 | CLERK | 800 | 2005-11-12 | 20 | RESEARCH | Beijing | | 1004 | JONES | M | 20 | MANAGER | 2975 | 1998-05-18 | 20 | RESEARCH | Beijing | | 1008 | SCOTT | M | 20 | ANALYSE | 3000 | 2003-05-12 | 20 | RESEARCH | Beijing | | 1011 | ADAMS | M | 20 | CLERK | 1100 | 1999-10-05 | 20 | RESEARCH | Beijing | | 1002 | ALLEN | F | 30 | SALESMAN | 1600 | 2003-05-12 | 30 | SALES | ShenZhen | | 1003 | WARO | F | 30 | SALESMAN | 1250 | 2003-05-12 | 30 | SALES | ShenZhen | | 1005 | MARTIN | M | 30 | SALESMAN | 1250 | 2001-06-12 | 30 | SALES | ShenZhen | | 1006 | BLAKE | F | 30 | MANAGER | 2850 | 1997-02-15 | 30 | SALES | ShenZhen | | 1010 | TURNER | F | 30 | SALESMAN | 1500 | 1997-10-12 | 30 | SALES | ShenZhen | | 1012 | JAMES | F | 30 | CLERK | 950 | 2008-06-15 | 30 | SALES | ShenZhen | +------+--------+-------+---------+-----------+----------+------------+---------+------------+------------+ 12 rows in set (0.00 sec)

22、查詢所有2001~2005年入職的員工的信息，查詢部門編號為20和30的員工信息并使用UNION合并兩個查詢結(jié)果

mysql> select * from employee where year(hireDate) between 2001 and 2005 union all select * from employee where dept_id in (20,30); +------+--------+-------+---------+----------+----------+------------+ | e_id | e_name | e_sex | dept_id | e_job | e_salary | hireDate | +------+--------+-------+---------+----------+----------+------------+ | 1001 | SMTTH | M | 20 | CLERK | 800 | 2005-11-12 | | 1002 | ALLEN | F | 30 | SALESMAN | 1600 | 2003-05-12 | | 1003 | WARO | F | 30 | SALESMAN | 1250 | 2003-05-12 | | 1005 | MARTIN | M | 30 | SALESMAN | 1250 | 2001-06-12 | | 1007 | CLARK | M | 10 | MANAGER | 2450 | 2002-09-12 | | 1008 | SCOTT | M | 20 | ANALYSE | 3000 | 2003-05-12 | | 1001 | SMTTH | M | 20 | CLERK | 800 | 2005-11-12 | | 1002 | ALLEN | F | 30 | SALESMAN | 1600 | 2003-05-12 | | 1003 | WARO | F | 30 | SALESMAN | 1250 | 2003-05-12 | | 1004 | JONES | M | 20 | MANAGER | 2975 | 1998-05-18 | | 1005 | MARTIN | M | 30 | SALESMAN | 1250 | 2001-06-12 | | 1006 | BLAKE | F | 30 | MANAGER | 2850 | 1997-02-15 | | 1008 | SCOTT | M | 20 | ANALYSE | 3000 | 2003-05-12 | | 1010 | TURNER | F | 30 | SALESMAN | 1500 | 1997-10-12 | | 1011 | ADAMS | M | 20 | CLERK | 1100 | 1999-10-05 | | 1012 | JAMES | F | 30 | CLERK | 950 | 2008-06-15 | +------+--------+-------+---------+----------+----------+------------+ 16 rows in set (0.00 sec)

23、使用LIKE查詢員工姓名中包括字母a的記錄

mysql> select * from employee where e_name like '%a%'; +------+--------+-------+---------+----------+----------+------------+ | e_id | e_name | e_sex | dept_id | e_job | e_salary | hireDate | +------+--------+-------+---------+----------+----------+------------+ | 1002 | ALLEN | F | 30 | SALESMAN | 1600 | 2003-05-12 | | 1003 | WARO | F | 30 | SALESMAN | 1250 | 2003-05-12 | | 1005 | MARTIN | M | 30 | SALESMAN | 1250 | 2001-06-12 | | 1006 | BLAKE | F | 30 | MANAGER | 2850 | 1997-02-15 | | 1007 | CLARK | M | 10 | MANAGER | 2450 | 2002-09-12 | | 1011 | ADAMS | M | 20 | CLERK | 1100 | 1999-10-05 | | 1012 | JAMES | F | 30 | CLERK | 950 | 2008-06-15 | +------+--------+-------+---------+----------+----------+------------+ 7 rows in set (0.00 sec)

24、使用regexp查詢員工姓名中包含T，C或者M的3個字母中任意1個記錄

mysql> select * from employee where e_name regexp '[TCM]'; +------+--------+-------+---------+----------+----------+------------+ | e_id | e_name | e_sex | dept_id | e_job | e_salary | hireDate | +------+--------+-------+---------+----------+----------+------------+ | 1001 | SMTTH | M | 20 | CLERK | 800 | 2005-11-12 | | 1005 | MARTIN | M | 30 | SALESMAN | 1250 | 2001-06-12 | | 1007 | CLARK | M | 10 | MANAGER | 2450 | 2002-09-12 | | 1008 | SCOTT | M | 20 | ANALYSE | 3000 | 2003-05-12 | | 1010 | TURNER | F | 30 | SALESMAN | 1500 | 1997-10-12 | | 1011 | ADAMS | M | 20 | CLERK | 1100 | 1999-10-05 | | 1012 | JAMES | F | 30 | CLERK | 950 | 2008-06-15 | +------+--------+-------+---------+----------+----------+------------+ 7 rows in set (0.00 sec)

視圖：
1、視圖是一個虛擬表，本質(zhì)是根據(jù)sql語句獲取的動態(tài)數(shù)據(jù)集，并為其命名
用戶在使用的時候，只需要指定名稱就可以獲取相對應的結(jié)果

select * from 表名；

2、視圖雖然是虛擬表，但是一樣有行有列，視圖的數(shù)據(jù)不會真實保存在我們的數(shù)據(jù)庫里面，行和列來自于定義的視圖查詢語句中所指的表，數(shù)據(jù)庫只存放視圖的定義，并不會存放視圖的數(shù)據(jù)，當表發(fā)生變化時，視圖的數(shù)據(jù)也會隨之改變

創(chuàng)建一個依賴表：

create table test( id int primary key, name char(6) not null ); insert into test values(1,'哈哈'); insert into test values(2,'嘻嘻'); insert into test values(3,'嘿嘿'); insert into test values(4,'呵呵');create table test_1( id int primary key, cj int not null, constraint test_test foreign key(id) references test(id) ); insert into test_1 values(1,100); insert into test_1 values(2,90); insert into test_1 values(3,80); insert into test_1 values(4,70);

創(chuàng)建視圖：
語法：
create view 視圖名 as 查詢語句

例：查詢班上成績大于85且姓嘻的同學

mysql> select test.name,test.id,test_1.cj from test,test_1 where name like '嘻%' and cj>85 and test.id=test_1.id; +--------+----+----+ | name | id | cj | +--------+----+----+ | 嘻嘻 | 2 | 90 | +--------+----+----+ 1 row in set (0.00 sec)

將查詢到的結(jié)果做成視圖

mysql> create view test_cj as select test.name,test.id,test_1.cj from test,test_1 where name like '嘻%' and cj>85 and test.id=test_1.id; Query OK, 0 rows affected (0.00 sec)

重新添加數(shù)據(jù)，再次查詢，驗證視圖是否是動態(tài)查詢

mysql> insert into test values(7,'賽羅'); Query OK, 1 row affected (0.00 sec)mysql> insert into test_1 values(7,'90'); Query OK, 1 row affected (0.00 sec)mysql> select * from test_cj;

數(shù)據(jù)表的改變是否會影響視圖的調(diào)用
修改嘻嘻的成績到85分以下，再查詢視圖

update test_1 set cj=84 where id=2; select * from test_cj;

修改表名，再查詢（有影響）

alter table test_1 rename cj;

修改字段名，查詢（有影響）

alter table test change name new_name char(6);

依賴的表結(jié)構(gòu)發(fā)生改變，會影響視圖的調(diào)用

視圖的優(yōu)點：
1、簡單化：可以將復雜的查詢語句，通過簡單的調(diào)用就可以完成
2、安全性：通過視圖用戶只能夠查詢和修改能見到的數(shù)據(jù)，其他在視圖中沒有的數(shù)據(jù)用戶看不見的，也無法獲取
3、邏輯數(shù)據(jù)獨立性：視圖可以幫助用戶屏蔽真實表結(jié)構(gòu)變化帶來的影響

對視圖進行數(shù)據(jù)的寫入
語法
create view 視圖名 as 查詢語句[with [cascades]|[local] check option]

with check option:如果創(chuàng)建視圖的語句中添加了with check option子句
mysql會通過視圖檢查正在更改的每個行，例如寫入，刪除，使其符合視圖的定義，因為mysql允許基于另一個視圖創(chuàng)建視圖，他還會檢查依賴的視圖中的規(guī)則來保證數(shù)據(jù)的一致性，為了確定檢查范圍，mysql提供了兩個選項[cascades]|[local]如果在with check option 子句中沒有明確指定使用哪個選項，默認使用cascades

cascades:表示更新視圖時需要滿足所有視圖和表的條件
local：表示更新視圖時，只需要滿足指定的視圖和表的條件

創(chuàng)建一個基本表

create table tb(id int primary key);

基于tb創(chuàng)建視圖v1，不添加with check option 子句
此時，向視圖v1插入數(shù)據(jù)時只需要滿足tb表的完整性約束條件就可以了

insert into tb values(1),(2),(3),(4),(5),(6),(7),(8),(11); create view v1 as select * from tb where id > 10;

查看不到小于10的數(shù)據(jù)，但是可以插入
基于tb表創(chuàng)建視圖v2，添加with check option子句

create view v2 as select * from tb where id > 10 with check option;

同時滿足依賴表以及視圖的規(guī)則
基于視圖2創(chuàng)建視圖3，不添加with check option

create view v3 as select * from v2;

向v3寫入數(shù)據(jù)時，只需要滿足依賴表的完整性約束條件就可以
基于視圖2，創(chuàng)建視圖4，添加with check option

create view v4 as select * from v2 where id < 15 with check option;

向v4寫入數(shù)據(jù)時，需要同時滿足依賴表，v2，v4的所有條件
基于視圖2創(chuàng)建視圖5

create view v5 as select * from v2 where id < 20 with local check option;

向v5插入數(shù)據(jù)時，只需要滿足v5和依賴表的條件即可；
視圖在哪些情況下，是不能夠插入數(shù)據(jù)
1.視圖中不包含基本表的非空的字段

test --- v1 idnot null id name age 1zs 17 3

空值和非空會沖突
2.在定義視圖的查詢語句中，select后的字段使用了juhe函數(shù)
3.在定義視圖的查詢語句中，select后的字段使用了數(shù)學表達式
4.在定義視圖的查詢語句中，使用了group by union 等子句
234.對原始表進行查詢，聚合，數(shù)學表達式都要先計算，在插入
已經(jīng)不是對原表進行查詢了
視圖的算法
創(chuàng)建分數(shù)表

create table score( id int, name char(6), sex char(2), score int ); insert into score values(1,'one','M',88), (2,'two','G',99),(3,'three','M',100),(4,'four','G',66), (5,'five','G',85),(6,'six','G',86);

查詢班上男生的最高分，和女生的最高分

select max(score),sex from score group by sex; select * from (select * from score order by score desc) as s1 group by sex; group_concat()

將子查詢定義為視圖

create view score_v2 as select * from score order by score desc;

通過視圖v2查詢男女最高分的信息

select * from score_v2 group by sex;

視圖查詢： select * from score order by score desc
查詢： select * from score_v2 group by sex;
真實執(zhí)行的命令：

select * from (select * from score order by score desc) as s1 group by sex;

語法
create algorithm=算法 view 視圖名 as 查詢語句
merge：使用merge算法，mysql會將指定查詢語句和視圖的查詢語句進行合并
組成單個查詢語句之后再進行查詢，如果查詢語句中包含
group by,limit,union,union all,子查詢時不能使用merge算法
temptable:如果使用temptable算法，mysql會將視圖的查詢語句進行執(zhí)行得到的結(jié)果作為
指定的查詢語句的條件，使用該算法，視圖不能進行更新數(shù)據(jù)
merge,temptable優(yōu)先使用merge

創(chuàng)建算法為temptable視圖

create algorithm=temptable view score_v3 as select * from score order by score desc; select * from score_v3 group by sex;

視圖的特點
創(chuàng)建視圖的時候添加with check option的作用是什么
視圖算法中merge 和 temptable的特點和區(qū)別是什么
對已經(jīng)創(chuàng)建的視圖進行修改

語法 replace (代替)
create or replace view 視圖名 as 查詢語句

create table test(id int,name char(5)); create view v1 as select * from test; create or replace view v1 as select * from score;

更新視圖中的數(shù)據(jù)
語法
update 視圖名 set 字段=新值 [where 條件語句]；

update score_v2 set score=66 where name='two';

刪除視圖
語法
drop view 視圖名 #如果指定的視圖不存在，會報錯
drop view if exists 視圖名#如果指定的視圖不存在，會警告

mysql的索引：

使用索引可以提高數(shù)據(jù)庫對特定數(shù)據(jù)的查詢速度，是單獨存儲在磁盤上的
數(shù)據(jù)結(jié)構(gòu)，包含對數(shù)據(jù)庫中某一列數(shù)據(jù)的引用，有效的建立索引可以提高數(shù)據(jù)庫的查詢速度，索引可以通過存儲引擎實現(xiàn)，每種存儲引擎帶有特定的索引功能，每一種索引，每個表最少支持創(chuàng)建16個索引，總索引長度至少支持256字節(jié)

mysql數(shù)據(jù)庫常用的索引數(shù)據(jù)結(jié)構(gòu)是btree，多路搜索樹

索引的優(yōu)點：
1，加快查詢速度，提高查詢效率
2，有多種索引類型的選擇，可以創(chuàng)建唯一索引，來保證數(shù)據(jù)表中的唯一性
3，實現(xiàn)數(shù)據(jù)的完整性，加速表與表之間的連接
4，減少排序和分組的時間

索引的缺點
1，創(chuàng)建索引需要消耗磁盤空間，索引越多占用的空間越大
2，創(chuàng)建索引以及維護索引需要耗費大量時間，隨著數(shù)據(jù)量的增加，耗費的時間更多
3，對表當中的數(shù)據(jù)增刪改查時，索引動態(tài)維護降低對數(shù)據(jù)的操作效率

索引的分類

唯一索引： 允許在定義的索引的列上插入空值，但是值不能重復，可以手動給指定的字段
創(chuàng)建唯一索引，如果一個字段帶有唯一性約束，那么最高字段會被默認創(chuàng)建唯一索引
主鍵索引： 唯一索引的特殊情況，如果一個字段帶有主鍵約束，那么這個字段會默認
創(chuàng)建主鍵索引，在定義主鍵索引的字段上不允許寫入空值，值也不能重復
單列索引： 對某一個字段創(chuàng)建索引，也稱之為普通索引
組合索引： 多個字段組合在一起創(chuàng)建一個索引
全文索引： 在定義的索引列上支持值得全文查找，允許在索引定義的列上插入重復值和
空值，只能對char,varchar,text這三種類型的列上創(chuàng)建全文索引，而且數(shù)據(jù)表的存儲引擎為
myisam類型，全文索引一般用于文本類型的數(shù)據(jù)查找，如小說，文檔等
空間索引： 對用來表示地理位置的字段可以創(chuàng)建空間索引

索引：日常生活中最常見的索引
索不知道他的意思suo，查字典，偏旁，部首，拼音

唯一性索引
語法：
create table 表名(
字段數(shù)據(jù)類型 [完整性約束條件]
);

unique index 索引名(字段) create table test_1( id int, name char(3), unique index ui(id) );

主鍵索引
語法：
create table 表名(
字段數(shù)據(jù)類型 [完整性約束條件]
。。。。。。。
primary key 索引名（字段）
);

create table test_pri( id int, name char(3), primary key pi(id) );

創(chuàng)建單列索引
語法：
create table 表名（
字段數(shù)據(jù)類型 [完整性約束條件]
…
index 索引名(字段)
）；

create table dex( id int, name char(4), index op(id) );

創(chuàng)建組合索引

create table 表名( 字段1 數(shù)據(jù)類型 [完整性約束條件] 字段2 數(shù)據(jù)類型 [完整性約束條件] 字段3 數(shù)據(jù)類型 [完整性約束條件] 字段4 數(shù)據(jù)類型 [完整性約束條件] key multiidx(字段1，字段2......) ); create table multiidx_test( id int, name char(5), age int, key multiidx(name,age) );

創(chuàng)建全文索引
語法：
create table 表名(
字段數(shù)據(jù)類型 [完整性約束]
。。。。。。
fulltext index 索引名(字段)
);

例： create table fulltext_index( id int, text mediumtext, fulltext index fl(text) )engine=myisam;

創(chuàng)建空間索引
create table 表名(
字段數(shù)據(jù)類型 [完整性約束條件]
。。。。。。。
spatial index 索引名(字段)
);

例 create table spatial_index( name char(4), home geometry not null, geometry經(jīng)緯度 spatial index go(home) )engine=myisam;

對已經(jīng)創(chuàng)建好的表，添加索引
語法
alter table 表名 add 索引類型索引名

例： create table add_index( id int, name char(5) ); alter table add_index add index q(name);

刪除指定的索引

alter table 表名 drop index 索引名 alter table add_index drop index q;

查詢指定數(shù)據(jù)表的索引信息

show index from dex\GTable: dex 表名Non_unique: 1 表示該索引是否是唯一索引 1表示不是， 0表示是Key_name: op 索引名稱Seq_in_index: 1 索引序列號Column_name: id 創(chuàng)建索引的字段名Collation: A 字段以什么形式存儲在索引中，A表示升序，null沒有分類Cardinality: 0 索引中唯一值的數(shù)目估值Sub_part: NULL 索引的長度Packed: NULL 是否對關(guān)鍵字進行了壓縮。如果為null代表沒有Null: YES 表示索引字段是否能為null值Index_type: BTREE 索引類型Comment: 注釋 Index_comment: 注釋

觸發(fā)器：

觸發(fā)器是與表相關(guān)的數(shù)據(jù)庫對象，在滿足定義條件時觸發(fā)，并執(zhí)行觸發(fā)器中定義的語句

創(chuàng)建觸發(fā)器：
語法
create trigger trigger_name trigger_time trigger_event on tb_name for each row trigger_stmt
釋義：
trigger_name：觸發(fā)器的名字
trigger_time：觸發(fā)器定義語句執(zhí)行的時間，分為before(在…之前)和after(在…之后)
trigger_event：觸發(fā)事件，insert，updata，delete
tb_name：對哪個表創(chuàng)建觸發(fā)器
for each row：表示數(shù)據(jù)表中任何一條記錄上的操作滿足觸發(fā)條件都會觸發(fā)該觸發(fā)器
trigger_stmt：觸發(fā)器的程序體，可以是一條sql語句，也可以是多條，程序體中還可以聲明變量和調(diào)用變量，如果是多條語句，每條語句使用，作為分隔符/結(jié)束符，此時需要通過delimiter 修改數(shù)據(jù)庫的結(jié)束符

創(chuàng)建用戶信息表

mysql> create table user(-> id int primary key,-> name char(5) not null,-> add_time int default null,-> comment char(30)-> ); Query OK, 0 rows affected (0.31 sec)

創(chuàng)建日志表：

mysql> create table log(-> id int primary key auto_increment,-> date int default null,-> log_info varchar(255) default null-> ); Query OK, 0 rows affected (0.01 sec)

創(chuàng)建觸發(fā)器：

mysql> delimiter # mysql> create trigger user_log after insert on user for each row -> begin-> set @s1='用戶創(chuàng)建成功';-> set @s2=concat(new.name,@s1);-> insert into log(date,log_info) values (new.add_time,@s2);-> end # Query OK, 0 rows affected (0.00 sec)

用戶信息表插入數(shù)據(jù)驗證觸發(fā)器

mysql> delimiter # mysql> insert into user values(1,'張三',20201221,'采花大盜-張三');-> # Query OK, 1 row affected (0.01 sec)mysql> select * from user; -> # +----+--------+----------+---------------------+ | id | name | add_time | comment | +----+--------+----------+---------------------+ | 1 | 張三 | 20201221 | 采花大盜-張三 | +----+--------+----------+---------------------+ 1 row in set (0.01 sec)mysql> select * from log;-> # +----+----------+--------------------------+ | id | date | log_info | +----+----------+--------------------------+ | 1 | 20201221 | 張三用戶創(chuàng)建成功 | +----+----------+--------------------------+ 1 row in set (0.00 sec)

new：創(chuàng)建觸發(fā)器的表中新添加的一行數(shù)據(jù)
old：創(chuàng)建觸發(fā)器的表中新刪除的一行數(shù)據(jù)
new. 字段名：表示新增加行的指定字段的值
old.字段名：表示新刪除行的指定字段的值

查看已經(jīng)創(chuàng)建的觸發(fā)器信息

select * from information_schema.triggers\G

刪除觸發(fā)器

drop trigger 觸發(fā)器名

mysql用戶管理

查看數(shù)據(jù)庫已經(jīng)創(chuàng)建的用戶的信息

select * from mysql.user\G mysql> select * from mysql.user\G *************************** 1. row ***************************Host: localhost #表示哪些主機可以通過數(shù)據(jù)庫用戶名密碼連接到數(shù)據(jù)庫，如果是localhost表示是本地連接，如果是網(wǎng)段或IP表示遠程連接User: root #用戶名，root為管理員用戶Password: *AC241830FFDDC8943AB31CBD47D758E79F7953EA #用戶名的密碼，加密后存儲#權(quán)限列，用戶對數(shù)據(jù)庫的操作權(quán)限，y有權(quán)限，n沒有權(quán)限Select_priv: Y #查詢Insert_priv: Y #寫入Update_priv: Y #更新Delete_priv: Y #刪除數(shù)據(jù)Create_priv: Y #創(chuàng)建Drop_priv: Y #刪除表，庫，視圖，觸發(fā)器Reload_priv: Y #用戶可以執(zhí)行刷新和重新加載mysql所有各種內(nèi)部緩存的特定命令，比如日志，權(quán)限，查詢和表，重新加載權(quán)限表Shutdown_priv: Y #關(guān)閉mysql服務Process_priv: Y #用戶可以通過show Processlist命令查看其它用戶的進程File_priv: Y #可以將外部文件導入mysqlGrant_priv: Y #給其他用戶授權(quán)的權(quán)限References_priv: Y #占位，沒有具體的功能Index_priv: Y #是否可以創(chuàng)建，刪除，查詢所有等Alter_priv: Y #修改表結(jié)構(gòu)Show_db_priv: Y #通過show查看數(shù)據(jù)庫，數(shù)據(jù)庫。視圖Super_priv: Y #超級權(quán)限，用戶可以刪除其他用戶的進程，修改全局變量，執(zhí)行關(guān)于復制和日志相關(guān)的命令Create_tmp_table_priv: Y #創(chuàng)建臨時表Lock_tables_priv: Y #對表加鎖Execute_priv: Y #執(zhí)行函數(shù)Repl_slave_priv: Y #從服務器從主節(jié)點復制數(shù)據(jù)的權(quán)限Repl_client_priv: Y #用戶可以查看從節(jié)點和主節(jié)點的信息Create_view_priv: Y #創(chuàng)建視圖Show_view_priv: Y #查看視圖Create_routine_priv: Y #創(chuàng)建函數(shù)Alter_routine_priv: Y #修改函數(shù)Create_user_priv: Y #創(chuàng)建用戶Event_priv: Y #創(chuàng)建事件Trigger_priv: Y #創(chuàng)建，修改，查看觸發(fā)器 Create_tablespace_priv: Y #創(chuàng)建表空間 #安全列ssl_type: #是否支持ssl標準對密碼加密ssl_cipher: #是否支持ssl標準對密碼加密x509_issuer: #是否支持x509標準的字段x509_subject: #是否支持x509標準的字段max_questions: 0 #用戶每小時允許執(zhí)行查詢的操作次數(shù)，0表示不限制max_updates: 0 #用戶每小時允許執(zhí)行更新操作次數(shù)，0表示不限制max_connections: 0 #用戶每小時允許執(zhí)行連接操作次數(shù)，0表示不限max_user_connections: 0 #用戶允許同時建立連接的次數(shù)，0表示不限制plugin: mysql_native_password #識別用戶身份的插件authentication_string: #記錄用戶密碼password_expired: N

創(chuàng)建數(shù)據(jù)庫用戶

create user 用戶名@主機 [identified by '密碼']; 例： create user 'rzh'@'192.168.10.%' identified by '123.com'; flush privileges; mysql> EXIT Bye [root@localhost ~]# mysql -urzh -p123.com -h 192.168.10.3 ....... Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>

授權(quán)用戶： 使用root用戶進行授權(quán)
語法
grant 權(quán)限 on 庫.表（字段） to 用戶名@主機 [identified by ‘密碼’]
[with grant option]
例：

mysql> grant all on *.* to 'rzh'@'192.168.10.%' identified by '123.com'; Query OK, 0 rows affected (0.00 sec)mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)mysql> exit Bye [root@localhost ~]# mysql -urzh -p123.com -h 192.168.10.3 mysql> create database haha; Query OK, 1 row affected (0.00 sec)

給指定的用戶授權(quán)：如果指定的用戶不存在，則先創(chuàng)建該用戶，在進行授權(quán)
如果指定的用戶存在，那么就只是修改這個用戶的權(quán)限

撤銷用戶的權(quán)限

第一種：撤銷全部權(quán)限
語法
revoke 權(quán)限1，權(quán)限2,… on 庫.表 from ‘用戶名’@‘主機名’

revoke all on *.* from 'rzh'@'192.168.10.%'；

第二種：

revoke insert on sjk.* from 'rzh'@'192.168.10.%';

修改用戶密碼
第一種
語法：
mysqladmin -u 用戶名 -h 主機名 -p password
例：

mysqladmin -u rzh -h192.168.10.3 -p password 123456

第二種
通過update 更新user表修改
例：

update mysql.user set password=password('123456') where user='rzh' and host='192.168.10.%'; flush privileges;

第三種：
修改本機的密碼

set password=password('123456');

通過set修改指定用戶的密碼

set password for 'rzh'@'192.168.10.%'=password '123456';

第四種

grant all on *.* to 'rzh'@'192.168.10.%' identified by '123456';

刪除用戶：
drop user ‘用戶名’@‘主機名’

drop user 'rzh'@'192.168.10.%';

mysql事務：

事務的特點： ACID
A原子性： 一個事務中，所有的操作要么都成功，要么都失敗，不會在中間某個環(huán)節(jié)結(jié)束，事務在執(zhí)行的過程中發(fā)生錯誤會被回滾到事務開啟前的狀態(tài)
C一致性： 在事務開始之前和事務開始之后，數(shù)據(jù)庫的完整性沒有被破壞，也就是寫入的數(shù)據(jù)必須符合所有的預設(shè)規(guī)則
I隔離性： 數(shù)據(jù)庫運行多個事務并同時對其數(shù)據(jù)進行讀寫和修改能力；隔離性可以防止多個事務并發(fā)執(zhí)行由于交叉執(zhí)行而導致數(shù)據(jù)不一致

事務的隔離等級分為：
未提交讀已提交讀可重復讀可串行讀

D持久性： 事務處理結(jié)束后，對數(shù)據(jù)的修改就是永久的，即使系統(tǒng)故障也不會丟失

事務的常用操作：
begin 開始一個事務
rollback 事務回滾
commit 事務確認
開啟事務 begin 或 start transaction
提交事務：將事務所執(zhí)行的sql語句執(zhí)行結(jié)果寫入數(shù)據(jù)庫并結(jié)束該事務
commit
事務回滾：撤銷事務中所執(zhí)行的sql語句并結(jié)束事務 rollback
保存點：指定一個保存點，在執(zhí)行回滾操作的時候，可以選擇回滾點指定回滾
而不是回到最開始的地方
savepoint 保存點名
rollback to 保存點名

創(chuàng)建一個表賬單信息表：

create table bank( id int, name char(6), money int ); insert into bank values(1,'迪迦','100'),(2,'戴拿','80');

事務的隔離級別
1、未提交讀： read-uncommitted,如果一個事務已經(jīng)開啟寫數(shù)據(jù)，則另一個事務不能同時進行寫的操作，但其他事務能夠讀取該行數(shù)據(jù)，可能出現(xiàn)臟讀，也就是事務B讀取到了事務A未提交的數(shù)據(jù)

查看當前事務隔離等級

mysql> select @@tx_isolation; +-----------------+ | @@tx_isolation | +-----------------+ | REPEATABLE-READ | #可重復讀 +-----------------+ 1 row in set (0.00 sec)

臨時修改隔離等級

mysql> set session tx_isolation='read-uncommitted'; Query OK, 0 rows affected (0.00 sec)例： A：寫 begin; update bank set money=money+100 where name='迪迦'; update bank set money=money+50 where name='戴拿'； B: begin; select * from bank; set session tx_isolation='read-uncommitted'; select @@tx_isolation;

2.已提交讀： read-committed 如果是一個讀事務，則允許其他事務讀寫；如果是一個寫事務，在未提交以前，其他事務看不到數(shù)據(jù)的修改，該隔離級別避免了臟讀，但是可能出現(xiàn)不可重復讀，也就是事務A先讀取數(shù)據(jù)，此時事務B更新了數(shù)據(jù)并提交
此時事務A再次讀取該數(shù)據(jù)時，數(shù)據(jù)已經(jīng)發(fā)生改變，也就是同一行的數(shù)據(jù)多次讀取不一致叫做不可重復讀

set session tx_isolation='read-committed'; select @@tx_isolation; A:讀 begin; select * from bank; B:寫 begin; update bank set money=money-10 where name='迪迦';

#B修改迪迦這一行數(shù)據(jù)時，如果沒有提交，則A是看不見修改的結(jié)果，只有dangB提交以后
A才能夠看見最終結(jié)果，避免了臟讀，
在B已經(jīng)修改完畢，但沒有提交事務，此時A不能對同一行進行數(shù)據(jù)修改

3、可重復讀： repeatable read 開啟一個讀事務，此時對數(shù)據(jù)的多次讀取結(jié)果都是一樣的；如果事務提交前，數(shù)據(jù)發(fā)生了改變，是看不到變化的，也就是只要讀到事務開啟，那么對于數(shù)據(jù)的查詢來說，多次執(zhí)行結(jié)果都是一致的，解決了不可重復讀的問題，但是可能導致幻讀，也就是事務中的查詢結(jié)果與數(shù)據(jù)表真實的結(jié)果不一致

set session tx_isolation='repeatable-read'; select @@tx_isolation; A:讀 begin; select * from bank; B:寫 begin; update bank set money=money+50 where name='戴拿';

A:可重復讀
select * from bank;即使B對數(shù)據(jù)進行了修改，A事務的查詢結(jié)果任然保持不變
對此查看的結(jié)果，與之前一樣，這就是可重復讀，但是由于看到結(jié)果與真實的數(shù)據(jù)不同，索引產(chǎn)生了幻讀

4、可串行讀 serializable 提供嚴格的事務隔離，他要求事務序列化執(zhí)行，事務只能一個接一個的執(zhí)行，不能并發(fā)執(zhí)行，這種隔離級別是最高的，同樣這種隔離級別會導致效率低下能解決臟讀，不可重復讀，幻讀，所以很少使用

set session tx_isolation='serializable'; select @@tx_isolation; A： begin; update bank set money=money+50 where name='迪迦'; B begin; update bank set money=money-50 where name='迪迦';

mysql日志：

錯誤日志：
特點：
錯誤日志是默認啟用的，一般存放在數(shù)據(jù)目錄下，默認文件名hostname.err
錯誤日志中除了記錄數(shù)據(jù)庫運行時出現(xiàn)的錯誤等相關(guān)信息外，默認還記錄數(shù)據(jù)庫的初始化信息數(shù)據(jù)庫啟動，關(guān)閉，重啟過程中的輸出信息

通過配置文件開啟或關(guān)閉錯誤日志

log-error=[/路徑/文件名]

跟錯誤日志相關(guān)的變量

show variables like 'log_error'; show variables like 'log_warnings';該變量的值用來控制警告信息是否可以記錄到錯誤日志中 log_warning=0 錯誤日志中不記錄警告等信息 log_warning=1 錯誤日志中記錄警告類型信息 log_warning=2 如果值大于1.錯誤日志中也會記錄數(shù)據(jù)庫連接失敗的信息

日志刷新

flush logs; mysqladmin -uroot -p123.com flush-logs

查詢?nèi)罩?/strong>
特點： 查詢?nèi)罩居涗浟怂械腗ySQL的操作信息，無論是否成功，默認情況下該日志時關(guān)閉的，需要手動開啟，日志文件名默認為hostname.log

通過配置文件開啟查詢?nèi)罩?br /> vim /etc/my.cnf
添加
log=[/路徑/日志名]

查詢?nèi)罩鞠嚓P(guān)變量
show variables like ‘%general_log%’;查詢?nèi)罩镜臓顟B(tài)和日志文件存放位置
show variables like ‘%log_output%’;查看日志信息記錄的時文件還是數(shù)據(jù)表

修改查詢?nèi)罩緺顟B(tài)
set global general_log=ON|OFF;

修改日志信息存儲方式
set global log_output=table|file;

查詢?nèi)罩緮?shù)據(jù)表
select * from mysql.general_log;
刷新查詢?nèi)罩?br /> flush logs;
mysqladmin -uroot -p123.com flush-logs

慢查詢?nèi)罩?/strong>
特點： 用來記錄mysql運行中響應時間查過指定值的sql語句，還可以記錄沒有使用索引的查詢
語句（可選），當我們需要對查詢?nèi)罩菊Z句進行測試優(yōu)化時，可以開啟慢查詢?nèi)罩?/p>
通過配置文件開啟慢查詢?nèi)罩?br /> vim /etc/my.cnf
log-slow-querise=[/路徑/日志名]
long_query_time=2
slow_query_log=on|off
slow-query-log-file=[/路徑/日志名]
long_query_time=2

查詢慢查詢相關(guān)的變量
show variables like ‘%slow_query_log%’;
set global slow_query_log=‘ON’|‘OFF’; 開啟或關(guān)閉慢查詢?nèi)罩?br /> show variables like ‘long_query_time’;
set global long_query_time=3;#修改慢查詢的臨界值，重新登入MySQL生效

刷新查詢?nèi)罩?br /> flush logs;
mysqladmin -uroot -p123.com flush-logs

4.二進制日志
特點： 是一個二進制文件，記錄會對數(shù)據(jù)庫中數(shù)據(jù)進行修改的sql語句增刪改，不會
記錄查詢語句還可以用于主從復制

通過配置文件開啟二進制日志
vim /etc/my.cnf
添加
log-bin=[/路徑/日志名]
expire_logs_days=0#是否對二進制文件內(nèi)容進行自動清除，0表示不清除，如果
為2表示每隔2天自動清除，值可以自定義
max_binlog_size=1G #指定單個二進制日志文件的存儲大小。如果文件大小超過該值
會生成一個新的二進制文件繼續(xù)存儲

查看二進制日志的相關(guān)變量
show variables like ‘%binlog%’;

查看二進制文件內(nèi)容
mysqlbinlog 二進制日志文件名

##binlog_cache_size:二進制日志緩存大小，當客戶端連接數(shù)據(jù)庫執(zhí)行事務或?qū)?br /> memory存儲引擎的表進行增刪改操作時，對于的二進制日志信息不會直接寫入磁盤中
而是先寫入給客戶端分配的內(nèi)存緩存中
##binlog_format 二進制日志格式 statement row mixed
statement:數(shù)據(jù)庫執(zhí)行的sql語句是什么日志就記錄什么，不會考慮這條sql語句的執(zhí)行
是否會對多行產(chǎn)生影響，占用日志空間少
row：記錄數(shù)據(jù)庫執(zhí)行的sql語句對所有產(chǎn)生數(shù)據(jù)變化的行，對于主從復制來說，如果一臺
sql語句對多行數(shù)據(jù)進行了修改，可以考慮使用row，該格式相較于statement占用的
二進制空間大
mixed：混合模式，數(shù)據(jù)庫根據(jù)情況對statement和row兩個格式自動選擇

刷新查詢?nèi)罩?/p> flush logs; mysqladmin -uroot -p123.com flush-logs
重啟mysql
查看二進制日志及文件大小
show binary logs;
查看當前正在使用的二進制日志
show master status;
刪除二進制日志
1.直接刪除，rm -rf 日志文件在這個目錄/usr/local/mysql/data
2.reset master;刪除除當前正在使用的二進制以外的日志文件，把當前正在使用的二進制日志
變成mysql-bin.000001
3.purge master logs to 日志文件：刪除指定索引前的所有日志文件
不包括當前正在使用的

mysql備份

全量備份： 全量備份也可以叫做完全備份，對數(shù)據(jù)庫中所有的數(shù)據(jù)進行備份（可以是全部數(shù)據(jù)庫中的數(shù)據(jù)，也可以是指定數(shù)據(jù)庫中的數(shù)據(jù)）
差異備份： 差異備份也叫做差量備份，每次備份時對上一次全備份以后變化的數(shù)據(jù)進行備份
增量備份： 增量備份是對上一次備份時的數(shù)據(jù)進行比較，備份變化的數(shù)據(jù)
時間點恢復： 備份惹怒執(zhí)行前如果數(shù)據(jù)丟失，可以通過二進制日志恢復指定時間段的數(shù)據(jù)
熱備： 也可以稱之為熱備份，在數(shù)據(jù)庫運行時（可讀可寫）進行數(shù)據(jù)備份
myisam（引擎）不支持熱備，innodb（默認引擎）支持
溫備： 在數(shù)據(jù)庫進行數(shù)據(jù)備份，但只能執(zhí)行讀操作，不能進行寫操作
冷備： 對數(shù)據(jù)庫冷備期間，不能對數(shù)據(jù)庫進行任何操作，也就意味著備份期間，數(shù)據(jù)庫停止工作
物理備份： 直接對數(shù)據(jù)庫的數(shù)據(jù)文件進行備份
邏輯備份： 將數(shù)據(jù)庫數(shù)據(jù)表以及對數(shù)據(jù)操作的sql語句進行備份

通過二進制日志進行數(shù)據(jù)恢復
語法：
mysqlbinlog [option] 日志名 | mysql -u root -p123.com
[option]：
–start-date: 開始時間
–stop-date:結(jié)束時間
–start-position:開始位置
–stop-position:結(jié)束位置
例：恢復從2020-12-29 9:18-2020-12-29 9:25之間的數(shù)據(jù)
mysqlbinlog --start-date=‘2020-12-29 9:18’ --stop-date=‘2020-12-29 9:25’
mysql-bin.000001 | mysql -u root -p123.com
對數(shù)據(jù)庫進行修改
創(chuàng)建一個庫，在庫里面創(chuàng)建一個表，對表插入數(shù)據(jù)，刷新表，刪除表
刷新
201229 10:03:25
201229 10:03:08
mysqlbinlog --start-date='2020-12-29 10:03:08 ’
–stop-date=‘2020-12-29 10:03:25’
mysql-bin.000002 | mysql -u root -p123.com
–start-position
–stop-position
mysqlbinlog --start-position=42 --stop-position=274 \
mysql-bin.000002 | mysql -u root -p123.com
2.mysqldump是以邏輯備份的方式備份數(shù)據(jù)的，會將數(shù)據(jù)庫中的數(shù)據(jù)轉(zhuǎn)化成sql語句然后進行備份
例：
1.備份sjk中的數(shù)據(jù)，不包含數(shù)據(jù)庫sjk本身，只備份其中的數(shù)據(jù)表以及表內(nèi)的數(shù)據(jù)
mysqldump -uroot -p123.com sjk > /sjk_tables.sql
還原
mysql -u root -p123.com sjk(指定某個數(shù)據(jù)庫)< /sjk_tables.sql
2.備份sjk中表1和表2的表結(jié)構(gòu)和數(shù)據(jù)
mysqldump -uroot -p123.com sjk 表1 表2 > /sjk_tables.sql
還原
mysql -u root -p123.com sjk(指定某個數(shù)據(jù)庫)< /sjk_tables.sql
3.備份sjk,包含數(shù)據(jù)庫本身以及數(shù)據(jù)庫中表結(jié)構(gòu)和表數(shù)據(jù)
mysqldump -uroot -p123.com --databases sjk > /sjk_all.sql
還原
mysql -u root -p123.com < /sjk_all.sql
4.同時備份多個數(shù)據(jù)庫，比如sjk_01,sjk_02,sjk_03
mysqldump -uroot -p123.com --databases sjk_01 sjk_02 sjk_03 > /sjk123_all.sql
還原
mysql -u root -p123.com < /sjk123_all.sql
5.備份所有的數(shù)據(jù)庫以及數(shù)據(jù)庫中的表結(jié)構(gòu)和表數(shù)據(jù)
mysqldump -u root -p123.com --all-databases > /all.sql
還原
mysql -u root -p123.com < /all.sql
6.只備份表結(jié)構(gòu)，不備份庫和數(shù)據(jù)
mysqldump -u root -p123.com -d sjk > /sjk_tab.sql
還原
mysql -u root -p123.com 庫名< /sjk_tab.sql
實現(xiàn)增量備份的腳本
#!/bin/bash backupDir=/backup/zengliang mysqlDir=/usr/local/mysql/data logFile=/backup/back.log binFile=/usr/local/mysql/data/mysql-bin.indexmysqladmin -uroot -p123.com flush-logscount=`wc -l $binFile | awk '{print $1}'` num=0for file in `cat $binFile` do base=`basename $file` num=`expr $num + 1` if [ $num -eq $count ]thenecho $base skip! >> $logFile elsedest=$backupDir/$baseif [ -e $dest ]thenecho $base exist! >> $logFileelsecp $mysqlDir/$base $backupDirecho $base dump! >> $logFilefi fi done
實現(xiàn)全量備份的腳本
#!/bin/bash backupDir=/backup/quanliang date=`date +%Y%m%d` cd $backupDir dumpFile=$date.sql GZdumpFile=$date.sql.tgzmysqldump -uroot -p123.com --all-databases --flush-logs --single-transaction > $dumpFile/bin/tar -zcf $GZdumpFile $dumpFile rm -rf $backupDir/$dumpFile
編寫計劃任務實現(xiàn)定時備份
crontab -e 0 2 * * 1-6 /bin/bash -x /zengliang.sh 0 2 * * 0 /bin/bash -x /quanliang.sh
總結(jié)

以上是生活随笔為你收集整理的MySQL（数据库）的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。

数据库

MySQL

歡迎分享！

轉(zhuǎn)載請說明來源于"生活随笔"，并保留原作者的名字。

本文地址：MySQL（数据库）

上一篇： 5G标准——3GPP TS 38.401

下一篇： python操作excel 2016

最新發(fā)布

mysql 查询重写_mysql 学习 - 查询重写规则

linux cmake编译源码,linux安装mysql（源码）以及cmake编译

mysql的复合类型_PLSQL 复合类型数据

mysql group by 区间_SQL按区间进行group by

mysql 查询重写_mysql 学习 - 查询重写规则

mysql f参数_MySQL 数据类型

mysql group by 区间_SQL按区间进行group by

mysql f参数_MySQL 数据类型

熱門推薦

mysql 免密码登录

mysql 免密码登录

標簽云

连接数据库

单元格

蓝牙耳机

程序语言

微信游戏

软件安装

双系统

游戏开发者

设计理念

计算机资源

MysqlDataSource

argparse

order_Java

以避免

mp3_Android

mysql_Java

音频播放器

_JavaSE

類代碼

forEachOrdered