首頁  >  文章  >  資料庫  >  MySQL常用語句講解匯總

MySQL常用語句講解匯總

巴扎黑
巴扎黑原創
2017-07-17 09:51:561679瀏覽

 

一資料庫常用操作

  • # mysql -u+username -p+password:登陸資料庫管理系統,如mysql -uroot -p123。

  • create database dbName:建立資料庫。

  • drop database dbName:刪除資料庫。

  • use dbName:使用指定資料庫,因為資料庫管理系統中可能存在多個資料庫,透過名稱使用指定的資料庫。

  • show databases:展示資料庫管理系統中全部資料庫。

  • select database():顯示正在使用的資料庫。

  • set character_set_results=gbk:許多資料庫不支援中文,設定編碼方式,讓中文正常顯示,主要用於解決在DOS視窗中顯示查詢結果時中文亂碼的問題。

  • source+path(.sql文件路徑):匯入資料庫文件,產生資料庫,即根據sql腳本產生資料庫。

  • mysqldump mydatabase>儲存目錄-uroot -p123:將資料庫的全部資訊儲存到硬碟中的.sql腳本檔案中,由於已經指定使用者名稱與密碼,所以在不進入資料庫的情況下操作(語句結尾不加分號)。

  • create user username identified by 'password':建立指定名稱與密碼的用戶,允許該用戶存取資料庫。

  • drop user username"%":刪除使用者。

二表常用操作

  • create table tableName(columnName type(length)) :建立資料庫。

  • drop table tableName:刪除資料庫。

  • alter table tableName add XXXX:在資料庫中增加欄位。

  • alter table tableName drop columnName:刪除欄位。

  • alter table tableName change oldName newName XXXX:覆蓋欄位。

  • alter table tableName modify columnName XXXX:修改欄位屬性。

  • create table copyTable as select columnName/* from oldTable:複製表,可以複製全部字段,也可以複製部分字段。

  • mysqldump mydatabase mytable>儲存目錄-uroot -p123:將資料庫中的某一張表中的全部資訊儲存到硬碟中的.sql腳本檔案中,注意:資料庫與表之間用分號隔開,形成兩個字符,一個代表資料庫,一個代表資料庫中的表,如果用"."連接,會被當做一個字符,即資料庫,因為系統中不存在該名稱的資料庫,運行時產生錯誤。

  • select@@identity:取得剛剛插入的資料的id。

三SQL語言分類

#1.DDL

##Data Definition Language,資料定義語言,create/alter/drop。
    create:用於資料庫與表格的建立。
    alter:用於表格的修改。

  •        alter table tbName add  XXXX:在表格中新增字段,或為字段增加約束。

  •        alter table tbName mofify XXXX:修改欄位。

  •        alter table tbName drop XXXX:刪除欄位或限制。

    drop:用於刪除資料庫、表格、欄位、欄位限制。

2.DML

Data Manipulation Language,資料操縱語言,update/delete/insert。

3.DQL

Data Query Language,資料查詢語言,select。

4.TCL

Transaction Control Language,事務控制語言,commit/rollback。

5.DCL

Data Control Language,資料控制語言,控制存取資料庫的權限,grant/revoke。

四DQL語句構成

select columnName from tbName where 过滤条件 group by 分组字段 having 分组后过滤条件 order by 排序字段 limit;

1.執行順序

from——where——group by——having——select——order by ——limit
  1. from:進入表格

  2. where:對直接從資料庫取得的資料即原始資料進行篩選。

  3. group by:後面跟著用作分組的字段,可以是單一字段,也可以是組合字段。分組以後,對各組資料分別進行處理,“select 分組字段,分組處理函數”,select加其他字段,將隨機生成,毫無意義;

  4. having:對分組後的組進行過濾,保留或刪除某些組,不是對組內資料的處理,所以過濾條件中只能使用分組函數傳回的字段,一個能反映組的特性的字段。

  5. select:取得資料。

  6. order by:排序,asc升序,desc降序。排序欄位可以有多個,先依照前面的欄位排序,再依照後面的欄位排序。

  7. limit:截取查詢結果,limit n--從0到n的數據,不包括第n條;limit begin,length--從指定的起點截取指定長度的資料,常用於分頁。

2.where與having區別

where用於在分組前過濾從資料庫中獲得的原始數據,結果是刪除某些行,保留某些行,是針對行的操作;having用於在分組後對組進行過濾,結果是刪除某些組,保留某些組,是針對組的操作。

3.重新命名

select oldName (as) newName from table;

对查询结果中的字段重新命名,操作查询结果必须使用新的名称,不改变数据库中的名称。
 

4.自增自减

sql支持在现有字段值基础上进行操作自增自减操作,如 update tb_xxx set count=count+5 。

五 条件查询

用在where语句中,对从数据库获得的原始数据进行过滤。

  •  =:等于,可用于任何数据类型。

  •  <:大于。

  • >:小于。

  •  !=:不等于,另一种方式<>。

  •  is null:为null。

  •  is not null:非空。

  •  and:并且,同时满足两个条件,and的优先级高于or,在两者同时存在的情况下先执行and。

  •  or:或者,满足其中一个条件即可。

  •  between A and B:取值在某个连续区间内,A<=B。

  • in(list01,list02,...):取值在某个数据量有限的列表中。

  • not in(list01,list02):取值不在某个数据量有限的列表中。

  • like:模糊查询,%代表0-n个字符,_代表一个字符,查找符合指定模板的字符。

六 数据处理函数

数据处理函数又叫做单行处理函数,用来处理单行数据,字段名作为方法的参数。

  • Lower():将查询结果全部转化为小写。

  • Upper():将查询结构全部转化为大写。

  • trim():去除前导与后导空格。

  • substr(columnName,begin,length):截取查询结果的一部分。

  • round():四舍五入。

  • rand():生成随机数。

  • ifnull(columnName,defaultValue):如果查询得到的数据为null,则赋一个默认值。

七 分组函数

分组函数又叫做多行处理行数,按照分组字段,对每一组数据分别进行处理,每组数据输出一条结果。分组行数自动忽略null。
分组函数不能使用在where子句中,如 select xxx from xxx where xxx>sum(xxx),因为分组函数只能在分组后使用。

  •  sum():求和。

  •  max():求最大值。

  •  min():求最小值。

  •  avg():求平均值。

  •  count():获得记录总数,count(*)获得包括null在内的全部记录数,count(columnName)获得指定字段非null记录数。

八 其他关键字

1.distinct

select distinct columnName01,columnName02 from table;

去除重复指定字段的重复记录,既可以是单个字段,也可以是组合字段。distinct前面不可以出现任何字段,因为前面字段的数据全部取出,后面的字段去除了重复记录,导致两个字段记录数不相等,产生冲突。

2.union

select columnName01,...from tbName01 union select columnName02,...from tbName02;

合并结果集,将两次分别查询的结果合并为一个结果,把后面字段的查询结果追加到前面对应位置字段的查询结果后面。

  • 前后查询字段的个数必须相同,无法合并。

  • 当前后对应位置的两个字段名称不同时,合并后的结果采用前面的字段名。

九 日期处理函数

1.通用性

由于不同数据库处理日期的方式不一样,为了提高数据的适用性,数据库在存储日期时通常采用字符串形式。

2.字符串转化为日期

str_to_date(字符串,格式);

如果底层日期储存采用的是date类型,而输入的是字符串,那么在将数据存储到数据库前需要将字符串转化为date类型,而这一操作只是告诉系统将字符串转化为date类型数据,并不能改变数据库底层的储存样式。
date类型数据在数据库统一采用"%Y-%d-%m %H-%i-%s"的存储。

 3.日期转化为字符串

date_format(日期字段,格式):

如果数据底层采用日期存储格式,那么在获取日期以后可以改变查询结果集中日期的格式。

4.默认格式

"%Y-%d-%m %H-%i-%s"

MySQL中date类型数据的默认格式,如果字符串符合该格式,插入或者查询时被自动转化为date类型。

5.日期对比

在SQL语句中,采用关系运算符><=比较日期类型数据。

十 表连接

1.背景

理论上将全部数据放到同一张表中很难实现,实际上即使实现了,表也很庞大,很冗杂,不便于查询与维护,因此将不同的数据存放到不同的表中,需要时连接各表进行查询。

2.连接条件

两张表连接时,将其中一张表中的每行数据与另外一张表的全部数据进行对比,如果满足给定的条件,则将这两行数据合并为一行,否则舍弃,继续对比剩余行。

3.笛卡尔现象

如果表连接时未设置连接条件,那么返回的查询结果数目是两张表行数的乘积,这种现象被叫做笛卡尔现象。

4.连接的几种方式

⑴内连接

select t1.columnName01,t2.columnName02 from tbName01 t1(inner)join tbName02 t2 on 连接条件;
  • 两张表进行连接查询,如果只显示匹配数据,那么采用内连接的方式。

  • 为了指明字段所属的表,为每一张参与连接的表指定别名,通过别名调用其中的字段。

 ⑵外连接

select t1.columnName01,t2.columnName02 from tbName01 t1 right/left (outer) join tbName02 t2 on 连接条件;

外连接将连接指向的一张表中的数据全部返回,另一种表对应字段无匹配数据时用null填充。在外连接中使用left,左边表的数据被全部取出;使用right,右边表的数据被全部取出。

5.多张表连接查询

一张表A与多张表连接查询,表A与其他表分别进行连接,最终的结果是各个独立连接查询结果相同A表数据的组合,即将某一个连接查询结果中的某一行A表字段全部取出,如果其他连接查询结果都具有该A字段,则所有具有该字段的行合并为一行。
 

6.别名

表连接查询时通常通过表的别名来引用字段,如果某个字段只出现在一张表中可以省略别名。

十一 约束

 1.约束类型

数据库可以添加的约束有非空约束(not null)、唯一性约束(unique)、主键约束(primary key)、    外键约束(foreign key)。

2.unique

unique约束用来约束一个字段不能有重复数据,允许多行数据同时为null。

多个字段可以联合起来添加unique约束,只有在多个字段对应数据均相同的情况下,才认为是重复数据。

create tabletableName(columnName01,columnName02,unique(columnName01, columnName02));

3.primary key

数据库中的重复数据不仅对实际应用没有意义,反而占用系统资源,使数据库冗杂,操作缓慢,为了避免重复数据 的出现,为表中的每一个行数据均设置一个唯一性标识,这个标识就是primary key。被设定为priamry key的字段 不仅不可以为null,而且不可以重复,但不等于同时设置了not null与unique,因为还为该字段添加了索引index。

主键字段的创建

create table tableName(id int(1) primary key auto_increment);
  • 其中auto_increment为是主键的生成 策略,表示主键的值自动生成。

  • MySQL并未实现主键生成策略sequence,可以借助Hibernate框架实现。

  • 一张表中只能存在一个唯一性标识,即只能有一个主键。 

  • 按照取值性质不同,主键可以分为: 1.自然主键:主键值是自然数,主键与当前业务无关,同一行中其他数据的改变不会导致主键值的改变。 2.业务主键:主键值与业务相关,同一行中其他数据的改变可能导致主键的改变,一般主键不推荐设置成这种方式。

  • 主键可以由一个字段构成,也可以由多个字段联合构成。在采用联合主键的情况下,可能出现非主键字段对主键 字段的部分依赖,产生数据冗杂,违法数据库设计第二范式,因此谨慎使用联合主键。

4.foreign key    

⑴背景

如果一张表A的某个字段引用另一张表B中的某个字段,为了保证引用的正确,在A表中添加外键。

⑵外键字段的创建

create table tbName(id (1) primary key auto_increment,name varchar(10) not ,classNo (1),foreign key(classNo) 
references 父表(父表中被引用字段));
  • 在表创建完成以后,可以为表中字段添加约束:alter table tbName add 约束定义形式。

  • 一张表可以有多个外键字段,外键字段可以为null。

  • 父表中被引用字段数据不能重复,必须有unique约束。

⑶级联操作

为了保证子表中的数据跟随父表的变化而变化,可以为外键添加级联操作。
级联关系是父表控制子表,使子表发生同样的变化,不是子表控制父表,子表的变化不影响父表。不设置级联操作时,父表被引用字段被引用数据不能更改,外键字段可以更改为父表中主键字段的其他值。
级联更新:

create table tbName(id (1) primary key auto_increment,name varchar(10) not (1),foreign key(classNo) 
references 父表(父表中被引用字段)on update cascade);

当父表中的数据更新时,子表中的数据做出同样的更新。
级联删除:

create table tbName(id (1) primary key auto_increment,name varchar(10) not (1),foreign key(classNo) 
references 父表(父表中被引用字段)on delete cascade);

当父表中的数据被删除时,子表中对应的数据也同样被删除。
同时为外键设置级联更新与级联删除:

create table tbName(id (1) primary key auto_increment,name varchar(10) not (1

⑷删除顺序

由于子表依赖于父表,创建时先创建父表,删除时先删除子表。

⑸一对多关联关系

典型的一对多关系,在多的一方添加外键,即A表中的多行数据对应B表中的一行数据,那么在A表中添加外键,引用B表中的数据。

⑹约束名

可以使用“constraint name +约束定义形式”的格式,在表创建添加约束的同时,为约束设定名称,以便后续操作。

⑺外部定义

在表定义外部,通过约束名称删除约束:

alter table tbName drop foreign key name;

⑻外键使用限制

设置外键后,需要同时维护两张表,增加了数据库负担,降低了性能,因此尽量避免使用外键。

十二 索引

1.未使用索引时表的检索方式?全盘检索。

2.什么是索引?

  • 索引是一种提高查询效率的策略,对应于一个文件,文件中保存了索引字段排序后的信息以及其他信息。

  • 索引相当于一本书的目录,可以快速定位到一列数据中具有特定值的行。

3.索引适用的条件

  • 数据量较大。

  • 数据很少被DML语句操作,因为索引字段发生改变,索引文件需要更新,降低了表的更新速度。

  • 索引字段经常出现在where语句中。

4.可以为一张表中的多个字段添加索引,多个索引有主次之分。

5.创建索引

create index indexName on tableName(columnName);

6.删除索引

drop index indexName on tableName;

7.索引的创建与删除格式不同与约束的格式,因为索引与约束是不同类型的属性。

十三 事务

1.什么是事务?

不可再分割的工作单元,保证多个DML语句同时有效或者同时无效。

2.事务操作基于内存

事务基于内存中的数据进行操作,每一步操作都会被保存到内存中,提交时将内存中的操作结果同步到数据库,并且清空内存;回滚时删除内存中的操作。在事务提交前,事务内部的每一步操作都会基于内存中的数据被执行,而不是在事务提交的一刻才全部执行,而是在事务提交前分步执行,事务提交时将执行结果写入数据库。可以概括为事务基于内存,分步执行。

3.特性(ACID)

  • A原子性:事务是一个最小的单元,不可再分。

  • C一致性:事务中的所有操作或者全部起作用,或者全部失效,不存在部分起作用,部分不起作用的情况。

  • I隔离性:事务之间相互隔离,彼此相互独立。

  • D持久性:事务对数据库的操作会反映到数据库中。

4.关闭自动提交

MySQL在默认情况下自动提交事务,即每执行一句DML语句,就会同步到数据库,关闭自动提交的两种方式:

start transaction-- DML---commit,或者start transaction--DML--rollback;
set autocommit=off(on);

以上两种方式只对当前会话有效。

5.隔离级别

  • read uncommitted:一个事务可以读取另一个事务未提交的数据,这个数据被称为脏数据。

  • read committed:其他事务频繁地提交数据,另个事务(时间跨度大)在未提交之前多次读取提交的数据,可能出现多次读取的数据不一致。

  • repeatable read:事务首次从数据库中读取数据,然后将数据保存在内存中,以后不是从数据库,而是从内存中读取数据。这种隔离级别可能导致读取的数据与数据库中数据不一致,出现“幻想”。

  • serializable:一个事务操作完数据以后,其他事务才可以操作。

6设置隔离级别

  • set global transaction isolation level XXXX:对所用会话有效。

  • set session transaction isolation level XXXX:只对当前会话有效。

7.查看隔离级别

  • select@@tx_isolation: 查看当前会话的隔离级别,另一种方式 select@@session.tx_isolation。

  • select@@global.tx_isolation: 查看全局的事务隔离级别。

十四 行级锁

1.什么是行级锁?

行级锁是一个数据库事务隔离方面的概念,为查询到的数据加锁,当前事务提交之前,不允许其他事务访问锁定的数据。

2.行级锁实现

select ...for update;

在一般的DQL语句结尾添加“for update”,DQL语句查询的字段就被锁,然后再在事务内部对锁定的数据进行其他操作。

3.数据库线程安全问题的几种解决方案

  1. java方面:为操作数据库的代码添加synchronized同步机制。

  2. 事务方面:serializable串行化。

  3. SQL语句方面:行级锁。

十五 视图View

1.什么是视图?

视图相当于表的一个副本,是表隐藏了部分数据与实现细节的结果,提供了一种比较安全的操作数据的方式。

2.创建

create view 视图名 as select columnName01 as newName01,columnName02,...from tbName;

3.同步

视图具有与表相同的属性与操作,两者绑定,对一个的操作也是对另一个的操作;

十六 SQL注入

1.什么是SQL注入?

攻击者将SQL语句通过表单输入或者请求地址中的请求参数注入到服务器中,执行恶意的SQL命令,如select * from table where name='name01'and password='n'or'a'='a';

2.SQL发生的根本原因

编译时把包含SQL关键字的用户输入一同编译,这样SQL语句发生扭曲。

3.防止SQL注入的一种方案

将SQL语句的框架与用户输入的参数分开,先编译框架,然后接收参数,这样就会把含有SQL关键字的输入当做普通字符串了,不会执行恶意的SQL命令。    

十七 存储引擎(MySQL专有)

1.什么是存储引用?

数据库管理系统简化了数据库的创建与维护,但底层依然没有跳过对数据的基础处理,比如将数据存放在什么样的文件中,以什么样的结构存放、怎么访问与修改等问题,存储引擎是数据库管理系统的底层实现,是数据的底层具体存储方式,是一种数据的存储技术。
 2.存储引擎是一个表级概念,可以为同一数据库中不同的表设置不同的存储引擎。

3.分类

⑴InnoDB

MySQL默认的存储引擎,支持事务、外键、行级锁,MySQL程序崩溃后,提供自动恢复,数据及其他信息以.frm文件格式存储;

⑵MyISAM

    底层有3个文件:

  • frm:存放表结构。

  • MYD:存放数据。

  • MYI:存放索引。

特点:

  • 不支持事务。

  • 支持压缩只读表。

  • 该引擎使用了索引,因此适用环境受索引的约束,主要用于数据量较大、数据很少更改的表。

⑶Memory

  • 数据与索引存储在内存中,因此检索速度最快,缺点是无法持久。

  • 以.frm文件格式存储信息。

  • 不支持事务。

 4.显示MySQL支持的引擎

show engines\G:

十八資料庫設計三範式

設計資料庫時應遵循的基本原則:

1.有主鍵,欄位不可再分

主鍵不僅可以作為資料的唯一標識,避免資料重複,同時內含索引,提高了查詢速度。 假如某個字段還可以再分,該字段相比與分割後的字段,維護不方便,查詢速度慢。

2.非主鍵欄位完全依賴主鍵,不能產生部分依賴

這條規範主要是為了限制聯合主鍵的使用,使得一張表中只能保存一種實體的資訊。使用聯合主鍵時,非主鍵欄位可能只對聯合主鍵的一個欄位存在依賴關係。詳細說,假定一張表採用聯合主鍵KA、KB,其中一個非主鍵字段C只依賴KB,由於一行資料的唯一性由KA與KB共同決定,KB的某一值可以與KA的多個值搭配出現在多行,那麼凡是在KB某個值出現的行,對應的字段C某個確定的值C0也必定出現,這樣C0就重複出現在多行中,造成資料冗餘。

3.不能出現傳遞依賴

某個非主鍵欄位C依賴另一個非主鍵欄位B,而B完全依賴主鍵字段A,那麼C對A的依賴關係是透過B的傳遞建立起來的,C對A的依賴就是傳遞依賴。如果B重複出現,那麼C跟著重複出現。應該另外建立一張表,以B為主鍵儲存B與C,在原表中為B建立外鍵,指向該表。

總之,將不同的物件分開儲存。而實際開發中,不一定要嚴格遵守三範式,以滿足專案需求為最高目的,有時為了追求速度可以犧牲結構。

 

以上是MySQL常用語句講解匯總的詳細內容。更多資訊請關注PHP中文網其他相關文章!

陳述:
本文內容由網友自願投稿,版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容,請聯絡admin@php.cn