翼度科技»论坛 编程开发 mysql 查看内容

MySQL必知必会

7

主题

7

帖子

21

积分

新手上路

Rank: 1

积分
21
一、Mysql

1、什么是数据库?

(1)数据库(database)

数据库是保存有组织的数据的容器(通常是一个文件或一组文件),数据库是DBMS(数据库管理系统或数据库软件)创建和操作的容器;
(2)表、列、行、主键、数据类型分别是什么
①表:表是由某些特定的字段组成的统一的结构化的存储数据的容器;
②列:列是组成表结构的最基本的单位;
③行:一行表示一条数据库记录,存储着记录信息;
④主键:主键是由数据库表中的一列或者多个列组成的,主键需要唯一;主键不能为空,主键
⑤数据类型:表中的每一个列,定义时都需要设定数据类型;
(3)SQL与SQL的编写规则(大概规则)

①SQL是专门与数据库实现通信的统一语言,但是不同的DBMS的SQL会有小的差异,不会完全相同;
②SQL编写规范
a)SQL语言不区分大小写,但是一般SQL开发人员,关键字使用大写,表名和列名使用小写;
b)在SQL语句中,空格都会忽略掉,所有一般开发人员为了方便读和调试会对SQL语句进行换行;
2、Mysql中一些常用的语句

(1)use:表示切换到需要的数据库

①use databasename:切换到databasename数据库;如果使用命令行连接Mysql数据库,需要使用该语句才能切换到对应的数据库
(2)show:展示数据库服务中的信息,相当于查看的功能;

①show databases:查看数据库服务中的所有数据库;
②show tables:查看数据库服务中的所有表;
③show colums from tablename:查看表结构中所有字段;
④show status:显示服务器的状态信息;
⑤show grants:显示当前登录用户的权限信息;
⑥show warnings:显示服务器警告信息;
⑦show errors:显示服务器错误信息;
⑧net start/stop mysqlname:开启或者关闭mysql服务;
⑨show global variables like 'port':查看服务开启的端口;
3、表结构的增删改查

(1)创建表:CREATE TABLE TABLENAME

①创建表基本语句
  1. CREATE TABLE tablename(
  2. columname type 约束条件 comment '字段名字'
  3. )ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='表名';
  4. CREATE TABLE IF NOT EXISTS tablename(
  5. columname type 约束条件 COMMENT '字段名字'
  6. )ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='表名';
复制代码
②创建表时常用的约束条件
  1. a)主键(PRIMARY KEY)<br>b)外键(constraints foreign_key_idname foreign key(column) references tablename(columnname))<br>c)不为空(NOT NULL)<br>d)自增(AUTO_INCREMENT)
  2. e)默认值(DEFAULT)
  3. f)字段备注信息(COMMENT)<br>
复制代码
③实例
  1. CREATE TABLE IF NOT EXISTS customers
  2. (
  3.   cust_id      int       NOT NULL  AUTO_INCREMENT COMMENT '顾客ID',
  4.   cust_name    char(50)  NOT NULL comment '顾客名',
  5.   cust_address char(50)  COMMENT '顾客的地址',
  6.   cust_city    char(50)  COMMENT '顾客的城市',
  7.   cust_state   char(5)   COMMENT '顾客的州',
  8.   cust_zip     char(10)  COMMENT '顾客的邮政编码',
  9.   cust_country char(50)  COMMENT '顾客的国家',
  10.   cust_contact char(50)  COMMENT '顾客的联系名',
  11.   cust_email   char(255) DEFAULT '123@qq.com'COMMENT '顾客的联系email地址',
  12.   PRIMARY KEY(cust_id)
  13. ) ENGINE=InnoDB COMMENT='顾客表';
复制代码
④自增AUTO_INCREMENT相关
a)每个表只能有一个列上加AUTO_INCERMENT,且该字段必须被索引上;
b)如果在insert时设置了自增的值,则自增的值会被手工设置的值覆盖,且后续自增的值从手工设置的值往后增;
c)在Mysql中可以使用select last_insert_id()查看自增量的最后一个值;
⑤引擎
a)InnoDB:该引擎是可靠的事务处理引擎,但是不支持全文本搜索;
b)MEMORY:等同于MyISAM,由于是存储与内存所以速度较快,适用于临时表;
c)MyISAM:性能极高,支持全文本搜索,但是不支持事务;
(2)修改表:ALTER TABLE TABLENAME 
  1. ALTER TABLE TABLENAME 后续的操作;
  2. ALTER TABLE TABLENAME ADD COLUMN COLUMNNAME INT;
  3. ALTER TABLE TABLENAME DROP COLUMN COLUMNNAME;
  4. ALTER TABLE TABLENAME MODIFY COLUMN COLUMNNAME NEWTYPE;
  5. ALTER TABLE TABLENAME RENAME COLUMNNAME TO NEWCOLUMNNAME;
复制代码
(3)删除表:DROP TABLE TABLENAME
  1. DROP TABLE TABLENAME;
  2. RENAME TABLE TABLENAME TO NEWTABLENAME,TABLENAME1 to NEWTABLENAME1; 
复制代码
4、表数据的增删改查

(1)插入数据:INSERT INTO TABLENAME...
  1. INSERT INTO TABLENAME(COLUMN1,COLUMN2,COLUMN3,...) VALUES(VALUE1,VALUE2,VALUE3,...);
  2. INSERT INTO TABLENAME(COLUMN1,COLUMN2,COLUMN3,...) VALUES(VALUE1,VALUE2,VALUE3,...),(VALUE1,VALUE2,VALUE3,...),...;
  3. INSERT LOW_PRIORITY ...;
  4. INSERT INTO TABLENAME(COLUMN1,COLUMN2,COLUMN3,...) SELECT VALUE1,VALUE2,VALUE3,... FROM TABLENAME1;
复制代码
(2)更新数据:UPDATE TABLENAME SET...
  1. UPDATE TABLENAME SET COLUMNNAME='NEWVALUE' ;
  2. UPDATE TABLENAME SET COLUMNNAME='NEWVALUE' WHERE ...;
  3. UPDATE TABLENAME SET COLUMNNAME='NEWVALUE',COLUMNNAME1='NEWVALUE1' WHERE ...;
  4. UPDATE IGNORE TABLENAME SET COLUMNNAME='NEWVALUE' WHERE ...;如果存在错误时,忽略错误,继续往下执行
复制代码
(3)删除数据:DELETE FROM TABLENAME...
  1. DELETE FROM TABLENAME WHERE...;删除表中的特定行或所有行
  2. TRUNCATE TABLENAME;--删除表中所有的数据,且直接提交不能回滚
复制代码
5、查询数据库

(1)检索数据
a)DISTINCT
  1. SELECT DISTINCT COLUMNNAME FROM TABLENAME;
  2. SELECT DISTINCT COLUMNNAME,COLUMNNAME1 FROM TABLENAME;
  3. SELECT DISTINCT *FROM TABLENAME; --过滤掉完全相同的行,或者把所有只要存在一个内容不同的数据都查询出来;
复制代码
b)LIMIT
  1. SELECT *FROM TABLENAME LIMIT 5;--查询小于等于5条数据
  2. SELECT *FROM TABLENAME LIMIT 5,5;--查询从行5开始往后的5条或5条以内的数据
  3. SELECT *FROM TABLENAME LIMIT 5 offset 5;--同上
复制代码
(2)排序数据:ORDER BY COLUMNNAME 
  1. SELECT *FROM TABLENAME ORDER BY COLUMNNAME ;--默认是按照升序排列的(ASC)
  2. SELECT *FROM TABLENAME ORDER BY COLUMNNAME DESC ;--设置降序
  3. SELECT *FROM TABLENAME ORDER BY COLUMNNAME DESC,COLUMNNAME DESC;--如果需要按照多个列排序,中间使用逗号隔开,多个列都需要降序的话多个列都需要加DESC
复制代码
(3)过滤数据:WHERE
  1. SELECT *FROM TABLENAME WHERE COLUMNNAME...;
  2. SELECT *FROM TABLENAME WHERE COLUMNNAME BETWEEN ... AND ...;
  3. SELECT *FROM TABLENAME WHERE COLUMNNAME> ... AND ;
  4. SELECT *FROM TABLENAME WHERE COLUMNNAME< ... OR ...;
  5. SELECT *FROM TABLENAME WHERE COLUMNNAME<= ... OR ...;
  6. SELECT *FROM TABLENAME WHERE COLUMNNAME>= ... OR ...;
  7. SELECT *FROM TABLENAME WHERE COLUMNNAME= ... OR ...;
  8. SELECT *FROM TABLENAME WHERE COLUMNNAME!= ... OR ...;
  9. SELECT *FROM TABLENAME WHERE COLUMNNAME<> ... OR ...;
  10. SELECT *FROM TABLENAME WHERE COLUMNNAME IS NULL;
  11. SELECT *FROM TABLENAME WHERE COLUMNNAME IS NOT NULL;
  12. SELECT *FROM TABLENAME WHERE COLUMNNAME IN(...);
复制代码
(4)使用通配符和正则表达式查询数据:LIKE&REGEXP
  1. 通配符:
  2. SELECT *FROM TABLENAME WHERE COLUMNNAME LIKE '%jack';--%匹配任意多个字符
  3. SELECT *FROM TABLENAME WHERE COLUMNNAME LIKE '_jack';--%匹配单个字符
  4. <br>正则表达式:
  5. SELECT *FROM TABLENAME WHERE COLUMNNAME REGEXP 'jack';--表示匹配COLUMNNAME列中包含jack的行;<br>SELECT *FROM TABLENAME WHERE COLUMNNAME REGEXP 'jack|jack1';--表达式中的|表示或者;<br>SELECT *FROM TABLENAME WHERE COLUMNNAME REGEXP '.jack';--表达式中的.表示匹配任意字符;<br>SELECT *FROM TABLENAME WHERE COLUMNNAME REGEXP '[123]ton';--表达式中的[]表示匹配[]中的任意字符,表示或的意思;<br>SELECT *FROM TABLENAME WHERE COLUMNNAME REGEXP '[1-9]ton';--表达式中的[1-9a-z]表示匹配1-9,a-z中的任意数字或者字母;<br>SELECT *FROM TABLENAME WHERE COLUMNNAME REGEXP '\\.'--表达式中\\表示转义,若特殊字符. - []这些内容,可以在前面加上转义字符表示查询特殊字符内容;<br>SELECT *FROM TABLENAME WHERE COLUMNNAME REGEXP '[0-9]*';--表达式中的*表示匹配0个或者多个内容;<br>SELECT *FROM TABLENAME WHERE COLUMNNAME REGEXP '[0-9]+';--表达式中的+表示匹配1个或者多个内容;<br>SELECT *FROM TABLENAME WHERE COULMNNAME REGEXP '[0-9]?';--表达式中的?表示匹配0个或者1个内容;<br>SELECT *FROM TABLENAME WHERE COLUMNNAME REGEXP '[0-9]{n,m}';--表达式中的{n,m}表示匹配n到m之间个内容;<br>SELECT *FROM TABLENAME WHERE COLUMNNAME REGEXP '[0-9]{n,}';--表达式中的{n,}表示匹配大于等于n个内容;<br>SELECT *FROM TABLENAME WHERE COLUMNNAME REGEXP '[0-9]{n}';--表达式中的{n}表示匹配n个内容;<br>SELECT *FROM TABLENAME WHERE COLUMNNAME REGEXP '^main';--表达式中的^表示匹配开头;<br>SELECT *FROM TBALENAME WHERE COLUMNNAME REGEXP '$main';--表达式中的$表示匹配结尾;
复制代码
(5)Mysql的内置函数
  1. ①计算字段
  2. SELECT CONCAT(COLUMNNAME ,'(',COLUMNNAME,')') AS COLUMNNAMENEW FROM TABLENAME;
  3. ②去除空格字段
  4. SELECT RTRIM(COLUMNNME) FROM TABLENAME;<br>SELECT LTRIM(COLUMNNAME)FROM TABLENAME;
  5. ③使用别名<br>SELECT AVG(COULUMNAME) as COLUMNNAME1 FROM TABLENAME;
  6. ④执行算数计算
  7. +\-\*\/<br>SELECT COLUMNNAME*2,FROM TABLENAME;
  8. ⑤使用数据处理函数
  9. ABS--绝对值<br>PI--Π<br>Sqrt--返回数的平方根<br>Sin--正弦<br>Cos--余弦<br>Tan--正切
  10. ⑥聚集函数
  11. a)AVG:返回指定COLUMNNAME的平均值;
  12. SELECT AVG(COLUMNNAME)FROM TABLENAME;<br>SELECT AVG(COLUMNNAME) AS COLUMNNAME1 FROM TABLENAME;<br>
  13. b)SUM:返回指定列值的总和;<br>SELECT SUM(COLUMNNAME) FROM TABLENAME;<br>
  14. c)COUNT:返回表中行数数量;
  15. SELECT COUNT(*) FROM TABLENAME;--返回表中行数数量,包括NULL的行<br>SELECT COUNT(COLUMNNAME)FROM TABLENAME;--返回表中行数数量,不包括指定COLUMNNAME为NULL的;<br>
  16. d)MIN和MAX:查询表中特特定列值的最大或者最小值;<br>SELECT MIN(COLUMNNAME) FROM TABLENAME;<br>SELECT MAX(COULUNNAME) FROM TABLENAME;
复制代码
(6)分组数据
  1. SELECT COLUMNNAME FROM TABLENAME GROUP BY COLUMNNAME ORDER BY COLUMNAME1 DESC;(在ORDER BY后面的COLUMNNAME可以不是GROUP BY后面的COLUMNNAME,但是根据规范最好是,如下语句)<br>SELECT COULMNNAME FROM TABLENAME GROUP BY COLUMNNAME ORDER BY COLUMNNAME DESC;<br>
  2. GROUP BY语句使用注意事项
  3. ①GROUP BY 语句在WHERE之后,ORDER BY之前
  4. ②在SELECT语句中使用GROUP BY时,需要查询展示的列在GROUP BY中必须也要有,不然查询出来的结果会不准确;如果在SELECT 中使用表达式,在GROUP BY中也必须使用表达式,不能使用别名;聚集函数可以在SELECT中存在,但是在GROUP BY中不存在;
  5. ③如果分组中存在NULL值,则NULL分为单独一组,多个NULL值分为一组;<br><br>HAVING过滤分组<br>WHERE过滤行而HAVING过滤分组,WHERE在分组之前过滤,HAVING在分组之后过滤;<br><br>SELECT COLUMNNAME,COUNT(*) FROM TABLENAME GROUP BY COLUMNNAME HAVING COUNT(*)>8;<br>SELECT COLUMNANME,COUNT(*) FROM TABLENAME WHERE COLUMNANEM ='' GROUP BY COLUMNNAME HAVING COUNT(*)>8;<br><br>Mysql中的关键字的顺序:<br>SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT;
复制代码
(7)子查询
  1. SELECT *FROM TABLENAME WHERE COLUMNNAME IN (SELECT COLUMNNAME FROM TABLENAME1 WHERE COLUMNNAME1='')<br><br>SELECT COLUMNNAME (SELECT COUNT(*) FROM TABLENAME WHERE ......s)AS COLUMNNAME FROM TABLENAME ;
复制代码
(8)组合查询
  1. 组合查询:执行多个SELECT 查询语句,并将结果作为单个结果集返回,使用UNION关键字将多个SELECT 查询结果集组合成一个结果集返回;
  2. SELECT COLUMNNAME,COLUMNNAME1 FROM TABLENAME UNION SELECT COLUMNNAME2,COLUMNNAME3 FROM TABLENAME1;
  3. UNION规则
  4. ①UNION必须由两条或两条以上的SELECT语句组成,语句之间用关键字UNION分隔;
  5. ②UNION中的每个查询必须包含相同的列、表达式或聚集函数;
  6. ③列数据类型必须兼容:类型不必完全相同,但必须是DBMS可以隐含地转换的类型(例如,不同的数值类型或不同的日期类型);
  7. ④使用UNION时默认取消掉了重复行,如果需要不取消重复行需要使用UNION ALL;<br>⑤如果需要对UNION的结果排序,只需要在最后一个SELECT 查询语句后面加上ORDER BY即可;
复制代码
(9)Mysql中的连接方式
①内连接
  1. a)内连接:自然连接(Naturaljoin)是一种特殊的等值连接,它要求两个关系中进行比较的分量必须是相同的属性组,并且在结果中把重复的属性列去掉;
  2. SELECT *FROM TABLENAME INNER JOIN TABLENAME1 ON TABLENAME.COLUMNNAME = TABLENAME.COLUMNNAME;(比较的分量必须是相同的属性组)
  3. b)等值连接:等值连接是关系运算-连接运算的一种常用的连接方式。是条件连接(或称θ连接)在连接运算符为“=”号时,即θ=0时的一个特例<br>SELECT *FROM TABLENAME,TABLENAME1 WHERE TABLENAME.COLUMNNAME=TABLENAME1.COLUMNNAME;
复制代码
②外连接
  1. a)左连接:LEFT JOIN或LEFT OUTER JOIN
  2. SELECT *FROM TABLENAME LEFT OUTER JOIN TABLENAME1 ON TABLENAME.COLUMNNAME=TABLENAME1.COLUMNNAME;
  3. b)右连接:RIGHT JOIN 或 RIGHT OUTER JOIN
  4. SELECT *FROM TABLENAME AS T RIGHT OUTER JOIN TABLENAME1 AS T1 ON T.COLUMNNAME=T1.COLUMNNAME;
  5. c)全连接:FULL JOIN 或 FULL OUTER JOIN
  6. SELECT *FROM TABLENAME AS T FULL OUTER JOIN TABLENAME1 AS T1 ON T.COLUMNNAME=T1.COLUMNNAME;(Mysql不支持)
复制代码
(10)全文本搜索
[code]①什么是全文本搜索a)Mysql中最常用的引擎就是MyISAM和InnoDB搜索引擎,前者支持全文本搜索,后者不支持;b)为了进行全文本搜索,必须索引被搜索的列,而且要随着数据的改变不断地重新索引;若在创建表是进行适当的设置,Mysql就会自动进行索引和重新索引;c)创建表时设置全文本搜索CREATE TABLE TABLENAME(COLUMNNAME TYPE,FULLTEXT(COLUMNNAME))②进行全文本搜索的基本语法:Match()和Against()a)其中Match()指定被搜索的列,Against()指定要使用的搜索表达式;(一般不区分大小写)b)传递给Match的值必须必须要与FULLTEXT中定义的一致,且如果定义了多列则次序也需要一致;c)全文本搜索会自动根据搜索的匹配优先级排序列出结果;SELECT *FROM TABLENAME MATCH(COLUMNNAME) AGAINST('需要搜索的内容');③进行全文本搜索的更高级语法a)使用扩展查询SELECT *FROM TABLENAME MATCH() AGAINST('需要搜索的内容' with query expansion);b)布尔文本搜索SELECT *FROM TABLENAME MATCH AGAINST('需要搜索的内容' IN BOOLEAN MODE)SELECT *FROM TABLENAME MATCH AGAINST('a +b' IN BOOLEAN MODE);--+表示包含,必须存在+后面的内容;SELECT *FROM TABLENAME MATCH AGAINST('a -b' IN BOOLEAN MODE);---表示不包含,必须不存在+后面的内容;SELECT *FROM TABLENAME MATCH AGAINST('a >b' IN BOOLEAN MODE);-->表示包含,而且增加等级;SELECT *FROM TABLENAME MATCH AGAINST('a

举报 回复 使用道具