MySQL必知必会books

数据库 waitig 384℃ 百度已收录 0评论

《 MySQL必知必会》

structured querylanguage,MySQL是一个DBMS软件

第1.2.3章

schema,提要纲要,column列。row行

  1. 数据库:一某种有组织的方式来储存的数据集合
  2. DBMS databasemanegersystem数据库管理系统
  3. table,表,某种特殊类型数据的结构化清单
    1. 一个数据中,表名称有唯一性
    2. 表的特性,我们用schema[模式]来描述
  4. column,列,表中的一个字段,所有的表都是由多个列组成

    1. 每列的数据类型datatype必须是一样,这就要求列分得比较细致了,方便管理
  5. row,行,就是储存具体数据的

  6. primarykey,主键,一组或多组可以唯一标识表中每行的列。
    1. 任意两行的都不具有相同的[主键值]
    2. 任意一行都必须有主键值
    3. 不更新主键列的值。不重用主键列的值
  7. 常见操作及操作符(operator)

    SHOW databases; #显示所有数据库
    USE lengqian;#选择要使用的数据库
    SHOW TABLES;#显示所有table表
    SHOW COLUMNS FROM student;显示表的所有的列,显示列的属性等
    #显示一个table的所有行,属性有field,type,Null,Key,Default,Extra(。。是否允许null,键信息,默认值,其他信息)
    
    ##########SELECT,指明从那个table里检索什么,与LIMIT进行检索,distinct:
    SELECT name FROM student;检索单个列
    SELECT name, score FROM student;检索多个列
    SELECT * FROM student;检索所有的行
    SELECT DISTINCT score FROM student;列举出score列种不同的行,这里可以查看有多少个不同的分数
    SELECT name FROM studet LIMIT 5;检索满足条件的前面五个
    SELECT score FROM student LIMIT 2,8;检索从第3个和后面7个(第2行后面的8个,第一行是第0行)
    
    ########排序数据,结合order by,desc
    SELECT score FROM student ORDER BY score;#数据时默认从小到大排序
    SELECT score FROM student ORDER BY score DESC;降序排列,字母或者数字都行
    SELECT score,name FROM student ORDER BY score DESC, name;#DESE只针对其前面有效
    SELECT score FROM student ORDER BY score LIMIT 1;#检索出分数最高的一行
    SELECT score FROM student ORDER BY score DESC LIMIT 3;#倒数三名
    
    #########过滤数据 结合子句WHERE(>,!=,between等操作符)
    select score from student where score >=60;
    select score, name from student where score between 60 and 80;#包括60和80
    select name from student where name IS NULL#IS NULL就是无值
    #########过滤说句,结合子句WHERE(and,or ,in ,not等操作符)
    SELECT sname,score FROM student WHERE score =80 OR score = 90";
    SELECT sname,score FROM student WHERE score IN (60,100);#包含60,100
    SELECT sname, english_score, math_score FROM student WHERE (english_score =60 or english=80) AND math_score >60;
    ######多个操作符的时候要考虑计算顺序,常用括号。and>or,在计算顺序上。
    SELECT sname, english_score,math_score FROM student WHERE english_score =60 OR english_score = 80 AND math_score >60;  
    

    `

  8. 通用配符,LIKE,

    以上检索是精准的检索,LIKE通用配符是一个"模糊"操作,比精确稍粗狂点,有点正则表达式的赶脚。仅仅与%,_配合使用。条件完全吻合才能返回。
    #### %就是代表任意数量的任意字符(0个也行), _就是代表一个字符,不多不少
    SELECT sname FROM student WHERE sname LIKE "leng%";
    SELECT sname FROM student WHERE sname LIKE "%ABC%";#不区分大小写
    SELECT sname FROM studnet WHERE sname LIKE "%qian";
    SELECT sname FROM student WHERE sname LIKE "_abc";
    #注意:这里的匹配不区分大小写,通用配符占用资源比较多,所有常用在其他操作符后面的
    
  9. 正则表达式

    正则表达式也是一个配符方式,和纯LIKE稍粗狂不同之处,正则表达式匹配的是对象中存在即可,而不是完全匹配,匹配的方式更多,有字母、数字、位置等。
    ###基本匹配字符" " , ". "任意一个字符
    SELECT sname FROM student WHERE sname REGEXP "LENG";#只要sname列值中有leng这四个字符即可,不分大小写
    ###进行OR匹配,即|和or是一样的
    SELECT sname FROM student WHERE sname REGEXP "liu|li";#
    SELECT sname FROM studnt WHERE sname REGEXP ".ng";#在正则表达式中.表示任意一个字符
    ###进行多个任意匹配之一,"[12]"="[1],[2],[12]"三种情况
    SELECT id FROM products WHERE id REGEXP "[0-9]old";匹配“任意数字old”
    ###匹配特殊字符,结合\\\转义(两个反斜杠,其中有一个是mysql自己解释),在mysql中特殊字符有. [ ] _ |
    SELECT id FROM products WHERE id REGEXP "\\\ |";#检索有|的行列值
    ###重复元字符,*  任意个;+  一个或者任意个;? 0个或者1个;{n}指定次数;{n,}至少n个;{n,m}匹配数量的范围
    SELECT id,name FROM products WHERE id REGEXP "\\.examples?\\)" ;匹配含有".example)"或者“.examples)”的文本。
    SELECT phon,name FROM list WHERE phone REGEXP "^1[0-9]{10}";匹配1开头、一共11位数字的文本,大概就是手机号码了。
    ###定位元字符 ^开头,如^S;$结尾,如t$.
    
  10. 计算字段,就是用拼接等方式将多个列合并起来,用于我们计算总量等

    ###concat拼接
    SELECT name, number, CONCAT(name, "(",number , ")") AS list FROM student;如lengqian(13027136666),AS 语句可省略,list为别名。
    ####执行算数计算,算数操作符+-*/
    SELECT number ,price, number*price AS list FROM products;直接返回计算结果
    
  11. 使用数据处理函数

    ####文本处理函数,upper,lower,length(),soundex()
    SELECT name FROM student WHERE SOUNDEX("name")=SOUNDEX("lenggqian");#读起来差不多的函数
    SELECT Upper(name) FROM student;名字全部转化为大写字母。
    ####时间日期处理函数,curtime,curdate()
    ####s数值处理函数,Abs,Cos()
    
  12. 汇总函数

    ###汇聚函数,avg,max,min,count,sum(),可以多个组合使用
    SELECT MAX(mark),AVG(price) AS avg_price FROM prodects;
    
  13. 分组数据,将原始表格按我们要求进行分组,以及后期的过滤、计算等。难点在于groupby 和orderby的区分,having和where的区别

    ###创建分组,GROUP BY --分组的依据,group语句必须在where之后,order by之后
    SELECT name, SUM(score) AS total_score FROM student GROUP BY name;按照name分组,同一个name的score相加,返回一个表格。
    ###过滤分组,此时是HAVing,过分分组,而不是WHERE,where过滤行。
    ###分组和排序,在实际使用当中,groupby出来的顺序默认是按照其后面的列,如上文的name,若是要求按照其他列排序,则可以在最后加上order by语句。
    ###SELECT 子句顺序,之前所学的子句,有一定顺序而言,
    SELECT >FROM >WHERE >GROUP BY >HAVING >ORDER BY >LIMIT 
    

    10/2/2017 9:19:55 PM

  14. 使用子查询,就是在同一个语句中,查询是基于另外一个查询上面的。

    ###利用子查询进行过滤
    现在,我们知道表a有id,product列,表b有id,name列
    我们已知name="lengqian",求对应的product数量。
    SELECT product FROM a 
    WHERE id IN (SELECT b_id FROM b WHERE name="lengqian" ); #查询顺序是由内到外的。
    

15.联结,是一种机制,用来在一条select语句中关联表。初衷是为了节省资源,出现二义性列时,必须使用完全限定列名

    ###在a表总有id,company,b表中有id,product列。#id是a的主键,a_id是b的外键,通过这个产生联结。
    SELECT company, product 
    FROM a,b
    WHERE a_id = b_id
    ORDER BY company;
    ###上面为内部联结,还有一个更地道的使用方法inner join ,on
    SELECT company, product
    FROM a INNER JOIN b
    ON a_id = b_id
    ORDER BY company;

16 .高级联结,有“表别名,“自联结”,自然联结,外部联结

        ###表别名,只能在查询总使用,不返回到客户机
        SELECT verd_id, prod_name
        FROM vendors AS v, products AS p
        WHERE v.vend_id = p.vend_id;            

        ###自联结,表是同一个,在products表中有pro_id,prd_name,prod_id列,我们已知id为xiaomi,求出生成xiaomi的公司所生产的所有产品。
        SELECT p1.prod_id, prod_name 
        FROM products AS p1,products AS p2
        WHERE p1.vend_id = p2.vend_id
        AND p2.prod_id ="XIAOMI";
        ##利用子查询实现上面功能。
        SELECT vend_id , prod_name
        FROM vendors, products
        WHERE vend_id = (SELECT vend_id FROM 
        products WHERE prod_id = "XIAOMI");

        ###自然联结和外部联结见书本

17.组合插叙,UNION,简单的将相似的两个查询结果综合起来,如
###这种情况下,or也能实现其基本功能。

18.###全文搜索

3.使用mysql:

create database  mysql_shiyan; #穿件数据库
use mysql_shiyan; #启用某个数据库
create table A (id int(10),name char(20),phont int(12));#创建表格A及表格A中的表头详细信息
create table B ( menorwomen char(10),age int(3));#创建表格B
insert into A(id,name,phont) values(01,"lengqian",12344444);#添加信息的三种方法。。。
insert into A(id,phont) values(02,345666768);
insert into A values(03,"zouqian",4456465);
select * from A;#显示表格A内容,表格样式
drop database mysql_shiyan;#删除
drop table xxx;

3.约束类型

  • primary key,主键:主键 (PRIMARY KEY)是用于约束表中的一行,作为这一行的唯一标识符,在一张表中通过主键就能准确定位到一行,因此主键十分重要。主键不能有重复且不能为空。还有一种特殊的主键——复合主键。主键不仅可以是表中的一列,也可以由表中的两列或多列来共同标识
  • foreign key,外键:外键 (FOREIGN KEY) 既能确保数据完整性,也能表现表之间的关系。一个表可以有多个外键,每个外键必须 REFERENCES (参考) 另一个表的主键,被外键约束的列,取值必须在它参考的列中有对应值。
  • default ,默认键:默认值约束 (DEFAULT) 规定,当有 DEFAULT 约束的列,插入数据为空时,将使用默认值。
  • unique ,唯一:唯一约束 (UNIQUE) 比较简单,它规定一张表中指定的一列的值必须不能有重复值,即这一列每个值都是唯一的。
  • not null,非空:非空约束 (NOT NULL),听名字就能理解,被非空约束的列,在插入值时必须非空。在MySQL中违反非空约束,不会报错,只会有警告

4.关键字

1.SELECT

1.select:从一个或多个表中检索信息。
SELECT id FROM people;#会检索id列的所有id号码
SELECT * FROM people;#会检索people表格的所有列
SELECT DISTINCT id FROM people;检索出来不同的id号码
SELECT id
FROM people
LIMIT 5,8;#从id列的第6行检索出来8个id号码。

2.子句clause

SELECT id,name,price
FROM people
ORDER BY prince;#此处的子句用来排序的,以prince列来排序,默认是从小到大,从a到z。
SELECT id,name,price
FROM people
ORDER BY prince DESC;#倒序,descend下降

拓展:结合limit和DESC可检索最大或最小的数字:
SELECT id,name,price
FROM people
ORDER BY prince
LIMIT 1;

3、WHERE

SELECT id,name,price
FROM people
WHERE price = 250;#检索出来prince为250的行,多个就多个!

4、LIKE操作符号,wildcard,通用符,用来匹配一部分的特殊字符

%,代表是0,1甚至无数个字符。    

SELECT id,name,price
FROM people
WHERE name LIKE %leng%;#检索name列中,xxxlengxx的行。%就是代替字符,leng%,检索前面四个字符是leng, l%g,检索首字符是l,最后字符是g的对象。

_,下划线,只能匹配单个字符
SELECT id,name,price
FROM people
WHERE id LIKE 1_;#只能是一个字符

5正则表达式regexp

.表示任意一个字符:
SELECT id,name,price
FROM people
WHERE id REGEXP ".00";#

or,|,这个两个都是代表或:
SELECT id,name,price
FROM people
WHERE prince LIKE "100 or200 or300";#匹配这三个数字的prince列

[xyz],匹配字符集中任何一个,记住,是一个,等同于"x|y|z"
[^xyz],匹配XYZ以外任何字符,一个或多个。

重复元字符
*   匹配前面字符0或者无数次
+   匹配前面字符1或者无数次
?       匹配前面字符0或者1次
{n}     匹配前面字符n次
{n,}    匹配至少n次
{n,m}   匹配前面字符n到m次

定位元字符
^       第一位
$      最后

SELECT id,name,price
FROM people
WHERE peince REGEXP "^[0-2]{3}" 1;#匹配第一位是0,1或者2一共3次的字符串,比如012,221xx,222xx等等.

10创建计算字段,field

作用:

  • 将不同表列中信息,用合适的格式组合起来,作为我们需要的信息,比如,name列,country列,计算 SELECT CONCAT(name,"(",country,")") FROM xxx;结果就是lengqian(China)
  • 执行算数计算,将已有信息经过简单运算,直接输出我们需要的结果
SELECT id, <br>
price, <br>
quantity, <br>
prince*quantity AS expanded_prince  #计算结果返回,还有+-/基本运算符号。 <br>
FROM list <br>
ORDER BY id; #最终显示的列一共有4列,最后一列是我们的计算结果。</li>

11使用数据处理函数

1,Upper()函数,转大写:

SELECT name,Upper(name) AS cap_name
FROM list
ORDER BY name;#会出现name,cap_name列,后者字母全部为大写。

2,日期时间处理函数:

SELECT name,price 
FROM list
WHERE DATE(lq_date)="2017-8-24";#从表中lq_date列中选择日期为2017-8-24的行。
注意:日期格式一定是yyyy-mm-dd
还有TIME()函数

12汇总数据

函数有:AVG(),COUNT(),MAX(),MIN(),SUM(),average平均

SELECT AVG(s_price) AS avg_price 
FROM producrs;#将products表中的s_price列的所有行数据取平均数,返回。

SELECT COUNT(*) AS cou_row 
FROM producrs;#将products表中的列数计算,返回。

SELECT AVG(s_price) AS avg_price 
FROM producrs
WHERE pro_name="xiaomi";#将products表中的xiaomi价格取平均数,返回。

也可多个函数一次操作
SELECT AVG(s_price) AS avg_price ,
MAX(sprice) AS max_prince,
MIN(sprice) AS min_price,
FROM producrs;

13分组数据

将原数据按照自己的意愿分成一组组我们需要的数据,比如在这里,我们需要知道每一种产品及其总数。

SELECT product,SUM(amount_price) AS sum_product     #选择product列,并创建sum_product列
FROM table
GROUP BY product #group by          #按照product进行分组。
HAVING sum_product>2000;                    #总数不得少于2000,HAVing可用于分组
                                            #order仅适用于行,having可代替order
注:sum改为COUNT(*)就是返回个数

14使用子查询

15联结表

大概意思就是,一定的语句将两个表格A,B联系起来,返回我们需要的信息:
例如A中有sid,sname列,B中有id,mark列,
我们需要从B的marK列中找到mark分数低于60的,并且在A中学生sname名字

SELECT sname,sid,id,mark #sname,sid等等出场顺序不同,导致后面列的顺序不同
FROM A,B
WHERE A.sid=B.id and mark>=60; #WHERE语句表明符合一定条件,注意“完全限定列名”

20更新和删除数据

UPDATE,DELETE
UPDATE student

SET sname="lengqian"
WHERE sid=100;将sid=100这一列的snme改为lengqian

DELETE student
WHERE sid=100;#删除sid=100这一列

21创建和修改表

创建新表:
CREATE TABLE a(aid int NOT NULL AUTO_INCREMENT,  #常见aid列,并且自动增加
aname char(20) NOT NULL, #不可为空
aprice char(10) NULL DEFAULT 1000, #不赋值的时候,默认价格为1000
PRIMARY KEY(aid) #设计主键,为aid
)

新增列:
ALTER TABLE a
ADD alocation char(20);

删除列:
ALTER TABLE a
DROP COLUMN slocation;

删除表:
DROP TABLE a;

重命名表:
RENAME TABLE a  TO b;

22使用视图

视图,即是虚拟的表,用show tables可以看到,但是不能删除等操作。简单地说,就是按照我们一定的需求,从原表格中生成的一个虚拟表格。也可以看做是一个指令集合。

CREATE VIEW pricenotnull AS
SELECT price 
FROM a
WHERE prince IS NOT NULL;#将重新生成的表格,成为我们需要的视图
##使用视图
select * from pricenotnull
where...
#删除视图
drop view xxx;

23使用存储过程

好比一个模块,将我们的一些列语句打包成为一个过程,procedure过程

DELIMITER //
CREATE PROCEDURE avgprice ()
BEGIN
SELECT AVG(price) AS avgprice
FEOM product;
END //
###数据库问题吧,一直显示mysql.proc不存在(mysql.procedure),重新构造数据库还是一样报错。10/5/2017 9:17:47 PM 

24使用游标

cursor,光标,游标

25触发器

就是在执行了特定语句时,程序会自动执行相关操作。用INSERT,DELETE和UPDATE三种,仅对于表才能执行,视图及虚拟表格都不行。

例如,,每次新增一个订单的时候,程序自动从库存总量中减掉该订单的数量,返回我们需要的剩余总量。

28安全管理

就是对数据库的操作者进行分类,root用户和普通用户,防止错误操作带来的数据错误。

1显示所有用户:

USE mysql;
SELECT user FROM user;#就可以显示所有的用户了

2新建用户:

`CREATE USER lengqian IDENTIFIED BY "lengqian"`

3查看并编辑用户的权限:

SHOW GRANTS FOR lengqian;


GRANT SELECT ON product.* TO lengqian;#赋予lengqian用户在product数据库所有表格的select操作

4更改用户密码:

SET PASSWORD FOR lengqian =PASSWOED("lengqianlengqian");

something need to know 10/5/2017 8:49:45 PM

  1. 自己手动录入table行时,发现,在输入5行之后,再输入的话,id(auto_increment)自动从6开始了。
  2. 输入行的是要要注意了
  3. create table xxx(id not null auto_increment,
    city not null default “wuhan”,
    primary key (id));
    ###插入行
    insert into xxx(id, city)
    values(null,”guangzhou”),
    (null,null);#city直接显示为null.

本文由【waitig】发表在等英博客
本文固定链接:MySQL必知必会books
欢迎关注本站官方公众号,每日都有干货分享!
等英博客官方公众号
点赞 (0)分享 (0)