MySQL数据库入门（二）

 外键(foreign key)：

定义

如果一个实体的某个字段指向另一个实体的主键，就称为外键

被指向的实体，称之为主实体（主表），也叫父实体（父表）。

负责指向的实体，称之为从实体（从表），也叫子实体（子表）

作用：

用于约束处于关系内的实体

增加子表记录时，是否有与之对应的父表记录

例如：比如，A表中的一个字段，是B表的主键，那他就可以是A表的外键。

 

为已经添加好的数据表添加外键:

语法:alter table 表名 add constraint FK_ID foreign key(你的外键字段名) REFERENCES 外表表名(对应的表的主键字段名);

创建表时添加一个主键

create table test(

id int,

constraint FK_ID foreign key(id) referencesclass(class_id)

);

 

删除外键

语法： ALTER TABLE table-name DROP FOREIGN KEY key-id;

例如： ALTER TABLE test DROP FOREIGN KEY `FK_ID`

 

使用注意事项

在删除外键的时候，应该先删除子表，然后再删除含有外键列的父表

想子表插入父表外键列没有的值的时候会报错

 

创建表（表结构从某个表复制过来）

create table xx like xx;

复制表结构和表数据

reate table xx as select * from xx;

 

 

运算符

1.     安全等于运算符 <=>

例如：select 1<=>null;

select null<=>null;

2.       LEAST运算符

语法格式为：LEAST（值1,值2,...值n），其中值n表示参数列表中有n个值。在有两个或多个参数的情况下，返回最小值。

假如任意一个自变量为NULL，则LEAST()的返回值为NULL

例如：使用LEAST运算符进行大小判断

SELECTLEAST(2,0),LEAST('a','b','c'),LEAST(10,NULL)

结论：当参数中是整数或者浮点数时，LEAST将返回其中最小的值；

当参数为字符串时，返回字母中顺序最靠前的字符；

当比较值列表中有NULL时，不能判断大小，返回值为NULL

 

3.        REGEXP 运算符

正则函数

expr REGEXP 匹配条件，如果expr满足匹配条件，返回1；

如果不满足，则返回0；

若expr或匹配条件任意一个为NULL，则结果为NULL

SELECT 'ssky' REGEXP  '^s' ,  'ssky'  REGEXP 'y$'  , 'ssky' REGEXP'.sky','ssky' REGEXP '[ab]';

常用的几种通配符：

（1）'^'匹配以该字符后面的字符开头的字符串

（2）'$'匹配以该字符后面的字符结尾的字符串

（3）'.'匹配任何一个单字符

（4）'[...]'匹配在方括号内的任何字符。例如，“[abc]" 匹配a、b或c。

字符的范围可以使用一个'-'，“[a-z]”匹配任何字母，而“[0-9]”匹配任何数字

（5）'*' 匹配零个或多个在他前面的字符。例如，“x*”匹配任何数量的'*'字符，“[0-9]*”匹配任何数量的数字，而“.*”匹配任何数量的任何字符。

 

4.  逻辑运算符

逻辑与运算符：AND或者&&

逻辑或运算符：OR或者||

 

数学函数

1.       求余函数MOD(X,Y)

返回x被y除后的余数。例如：SELECT MOD(31,8)

 

2.        四舍五入函数TRUNCATE(X,Y)

返回被舍去至小数点后y位的数字x  例如：SELECTTRUNCATE(1.32,1)

 

字符串函数

1.       CHAR_LENGTH(STR)返回值为字符串str所包含的字符个数

例如：SELECT CHAR_LENGTH('DATE')

 

2.     合并字符串函数CONCAT_WS（x,s1,s2,......）

CONCAT_WS（x,s1,s2,......）,CONCAT_WS代表CONCAT withSeparator

第一个参数x是其他参数的分隔符，分隔符的位置放在要连接的两个字符串之间。分隔符可以是一个字符串，也可以是

其他参数。如果分隔符为NULL，则结果为NULL

例如：SELECT CONCAT_WS('-','1st','2nd','3rd'),CONCAT_WS('-','1st',NULL,'3rd')

3.       删除空格的函数TRIM(S)

TRIM(S)删除字符串s两侧的空格。例如：SELECTTRIM(' book ')

4.       TRIM(S1 FROM S)删除字符串s中两端所有的子字符串s1。s1为可选项，在未指定情况下，删除空格

例如：SELECT TRIM('xy' FROM 'xyxboxyokxxyxy')

 

 

 

日期函数

获取当前日期的函数和获取当前时间的函数

 

CURDATE()、CURRENT_DATE()、CURRENT_TIMESTAMP()、LOCALTIME()、NOW()、SYSDATE()

 

加密函数

1、加密函数PASSWORD(STR)

例如：SELECT PASSWORD('NEWPWD')

2、MD5加密

例如：SELECT MD5('123')

 

查询语句

SELECT f_id,f_name FROM fruits WHERE s_idIN(101,102)

SELECT f_id,f_name FROM fruits WHERE s_idNOT IN(101,102)

SELECT f_id,f_name FROM fruits WHEREf_price BETWEEN 2 AND 10

SELECT f_id,f_name FROM fruits WHEREf_price NOT  BETWEEN 2 AND 10

 

带like的字符匹配查询

1、百分号通配符“%”，匹配任意长度的字符，甚至包括零字符

SELECT f_id,f_name FROM fruits WHERE f_nameLIKE 'b%y'

2、下划线通配符“_”,一次只能匹配任意一个字符

SELECT f_id,f_name FROM fruits WHERE f_nameLIKE '____n'

 

查询空值

SELECT * FROM customers WHERE c_city IS NULL

SELECT* FROM customers WHERE c_city IS NOTNULL

 

AND、OR、DISTINCT关键字

SELECT f_id,f_name FROM fruits WHERE f_nameLIKE '____n' AND f_id='bs2'

SELECT f_id,f_name FROM fruits WHERE f_nameLIKE '____n' OR f_id='bs2'

SELECT DISTINCT s_id FROM fruits

 

在group by中使用with rollup

SELECT s_id ,COUNT(1) AS total FROM fruitsGROUP BY s_id WITH ROLLUP

 

 

子查询

ANY关键字接在一个比较操作符的后面，表示若与子查询返回的任何值比较为TRUE，则返回TRUE

返回tbl2表的所有num2列，然后将tbl1中的num1的值与之进行比较，只要大于num2的任何一个值，即为符合查询条件的结果

SELECT num1 FROM tbl1 WHEREnum1>ANY(SELECT num2 FROM tbl2)

ALL关键字接在一个比较操作符的后面，表示与子查询返回的所有值比较为TRUE，则返回TRUE

SELECT num1 FROM tbl1 WHEREnum1>ALL(SELECT num2 FROM tbl2)

 

 

合并查询

使用UNION关键字，合并结果时，两个查询对应的列数和数据类型必须相同。

各个SELECT语句之间使用UNION或UNION ALL关键字分隔

UNION：执行的时候删除重复的记录，所有返回的行都是唯一的

UNION ALL：不删除重复行也不对结果进行自动排序

 

SELECT s_id,f_name,f_price   

FROM fruits

WHERE f_price<9.0

UNION

SELECT s_id,f_name,f_price

FROM fruits

WHERE s_id IN (101,103)

 

mysql查询的五种子句

where(条件查询)、having（筛选）、group by（分组）、order by（排序）、limit（限制结果数）

1. where 常用运算符：

  比较运算符

  > ,  < ,=  , != （< >）,>=   ,  <= 

 in(v1,v2..vn) 

 between v1 and v2    在v1至v2之间（包含v1,v2）

  逻辑运算符

  not ( ! ) 逻辑非

  or( || )    逻辑或

  and( && )  逻辑与

 模糊查询

 like像

 通配符:

 %  任意字符

 _   单个字符

2. group by 分组

一般情况下group需与统计函数（聚合函数）一起使用才有意义

mysql中的五种统计函数：

（1）max：求最大值

（2）min：求最小值

（3）sum：求总数和

（4）avg：求平均值

（5）count：求总行数

3.having和where的异同点

where针对表中的列发挥作用，查询数据

having对查询结果中的列发挥作用，筛选数据

4.order by

 （1） order byprice  //默认升序排列

 （2）order byprice desc //降序排列

 （3）order byprice asc //升序排列，与默认一样

 （4）order byrand() //随机排列，效率不高

5.limit

   limit [offset,] N

   offset 偏移量，可选，不写则相当于limit0,N

   N     取出条目 

 

 

子查询

1.where型子查询

(把内层查询结果当作外层查询的比较条件）

 

2. from型子查询

   (把内层的查询结果供外层再次查询)

 

3. exists型子查询

    （把外层查询结果拿到内层，看内层的查询是否成立）

    查询哪些栏目下有商品，栏目表category,商品表goods

    select cat_id,cat_name from category where exists(select * from goodswhere goods.cat_id = category.cat_id);

4.union的用法

  （把两次或多次的查询结果合并起来，要求查询的列数一致，推荐查询的对应的列类型一致，可以查询多张表，多次查询语句时如果列名不一样，则取第一次的列名！如果不同的语句中取出的行的每个列的值都一样，那么结果将自动会去重复，如果不想去重复则要加all来声明，即union all）

 

5.左连接，右连接，内连接

1)  1、左连接

 以左表为准，去右表找数据，如果没有匹配的数据，则以null补空位，所以输出结果数>=左表原数据数

 语法：selectn1,n2,n3 from ta left join tb on ta.n1= ta.n2 [这里on后面的表达式，不一定为=，也可以>，<等算术、逻辑运算符]【连接完成后，可以当成一张新表来看待，运用where等查询】

2、右连接

  aleft join b 等价于 b right join a

  推荐使用左连接代替右连接

  语法：selectn1,n2,n3 from ta right join tb on ta.n1= ta.n2

3、内连接

   查询结果是左右连接的交集，【即左右连接的结果去除null项后的并集（去除了重复项）】

   语法：selectn1,n2,n3 from ta inner join tb on ta.n1= ta.n2