Mysql数据库

Mysql数据库（补充）

#创建数据库
CREATE DATABASE mydatebase;
#选择数据库
USE mydatabase;
#创建表
CREATE TABLE mytable(
      id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
      col1 INT NOT NULL DEFAULT 1,
      col2 VARCHAR(45) NULL,
      col3 DATE NULL
);
#查看表的结构
DESC mytable;
#添加列
ALTER TABLE mytable ADD col CHAR(20);
#删除列
ALTER TABLE mytable DROP COLUMN col;
#删除表
DROP TABLE mytable;
#插入数据-->普通插入
INSERT INTO mytable(col1,col2) VALUES (val1,val2);
#插入检索出来的数据
INSERT INTO mytable(col1,col2) SELECT col1,col2 FROM mytable2;
#将一个表的内容插入到一个新表
CREATE TABLE newtable AS SELECT * FROM mytable;
#更新数据
UPDATE mytable SET col = val WHERE id = 1;
#删除
DELETE FROM mytable WHERE id = 1;
#truncate table可以清空表，也就是删除所有行。
TRUNCATE TABLE mytable;
#查询
#distinct,相同的值只会出现一次，它作用于所有列，也就是说所有的值都相同才算相同
SELECT DISTINCT col1,col2 FROM mytable;
#limit，限制返回的行数，可以两个参数，第一个参数为起始行，从0开始
#第二个参数为显示的条数
SELECT * FROM mytable LIMIT 5;#返回前五行
SELECT * FROM mytable LIMIT 0,5;
#返回第3~5行：
SELECT * FROM mytable LIMIT 2,3;
#排序：可以按多个列排序，并且为每个列指定不同的排序方式
#ASC：升序（默认）
#DESC:降序
SELECT * FROM mytable ORDER BY col1 DESC,col2 ASC;
#过滤
/*
不进行过滤的数据非常大，导致通过网络传输了多余的数据，从而浪费了网络宽带，
因此尽量使用sql语句过滤不必要的数据，而不是传输所有的数据到客户端中然后由客户端进行过滤

*/
SELECT * FROM mytable WHERE col IS NULL;

过滤

不进行过滤的数据非常大，导致通过网络传输了多余的数据，从而浪费了网络宽带，
因此尽量使用sql语句过滤不必要的数据，而不是传输所有的数据到客户端中然后由客户端进行过滤

SELECT * FROM mytable WHERE col IS NULL;

下表显示了where子句的可用操作符

操作符	说明
=	等于
<	小于
>	大于
<> !=	不等于
<= !=	小于等于
>= !<	大于等于
BETWEEN	在两个值之间
IS NULL	为null值

注意：null与0、空字符串都不同

AND 和 OR用于连接多个过滤条件。优先处理AND，当一个过滤表达式涉及到多个AND和OR时，可以使用()来区分优先级，使得优先级关系更清晰。

IN操作符用于匹配一组值，其后面也可以接一个SEKLECT子句，从而匹配子查询得到的一组值。

NOT操作符用于否定一个条件。

通配符

通配符也是用在过滤语句中，但它只能用于文本字段。

• %匹配>=0个任意字符；
• _匹配==1个任意字符；
• []可以匹配集合内的字符，例如[ab]将匹配字符a或者b.用^可以对其进行否定，不匹配集合内的字符。

使用like来进行模糊查询，通配符匹配。

select * 
from mytable
where col like '[^AB]%;#不以A和B开头的任意文本

不要滥用通配符，通配符位于开头处匹配会非常慢。

计算字段

在数据库服务器上完成数据的转换和格式化的工作往往比客户端快得多，并且转换和格式化后的数据量更少的话可以减少网络通信量。

计算字段通常需要使用AS来去别名，否则输出的时候字段名为计算表达式。

select col1* col2 as alias
from mytable

concat()用于连接两个字段。许多数据库会使用空格把一个值填充为列宽，因此连接的结果会出现不必要的空格，使用TRIM()可以去除首尾空格。

函数

各个DBMC的函数都是不相同的，因此不可移植，以下主要是MYSQL的函数。

汇总

函数	说明
AVG()	返回某列的平均值
COUNT()	返回某列的行数
MAX()	返回某列的最大值
MIN()	返回某列的最小值
SUM()	返回某列值之和

AVG()会忽略NULL行。

使用DISTINCT可以让汇总函数值汇总不同的值。

文本处理

函数	说明
LEFT()	左边的字符
RIGHT()	右边的字符
LOWER()	转换为小写字符
UPPER()	转换为大写字符
LTRIM()	去除左边的空格
RTRIM()	去除右边的空格
LENGTH()	长度
SOUNDEX()	转换为语言值

其中，SOUNDEX()可以将一个字符转换为描述其语音表示的字母数字模式。

select * 
from mytable
where SOUNDEX(COL1) = SOUNDEX('apple')

日期和时间处理

• 日期格式：YYYY-MM-DD
• 时间格式：HH:MM:SS

函数	说明
AddDate()	增加一个日期
AddTime()	增加一个时间
CurDate()	返回当前时间
CurTime()	返回当前时间
Date()	返回当前的日期部分
DateDIFF()	计算两个日期之差
Date_Add()	高度灵活的日期运算函数
Date_Format()	返回一个格式化日期或时间串
Day()	返回一个日期的天数部分
DayOfWeek()	对应一个日期，返回对应的星期几
Hour()	返回一个时间的小时部分
Minute()	返回时间的分钟部分
Month()	返回一个时间的月份部分
Now()	返回一个当前的时间与日期
Second()	返回一个时间的秒部分
Time()	返回一个时间日期的时间部分
Year()	返回一个日期的年份部分

mysql > select NOW()；
2018-11-28 20:24:00

数值处理

函数	说明
SIN()	正弦
COS()	余弦
TAN()	正切
ABS()	绝对值
SORT()	平方根
MOD()	余数
EXP()	指数
PI()	圆周率
RAND()	随机数

分组

分组就是把所有的具有相同的数据值得行放在同一组中。

可以对同一分组数据使用汇总函数进行处理，例如求分组函数的平均值等。

指定的分组字段除了能按字段进行分组，也会自动按字段进行排序。

select col ,count(*) as num
from mytable
group by col;

Group BY 自动按分组字段进行排序，Order by也可以按汇总字段来进行排序

select col,count(*) as num
from mytable
group by col
order by num;

WHERE过滤后，HAVING过滤分组，进行过滤应该先与分组过滤。

分组规定：

• GROUP BY子句出现在WHERE子句之后,ORDER BY子句之前；

• 除了汇总字段外，select语句中的每一个字段都必须在GROUP BY子句中给出。

• NULL的行会单分为一组；

• 大多数的SQL实现不支持GROUP BY列具有可变长度的数据类型。

  子查询

  子查询中只能返回一个字段的数据

  可以将子查询的结果作为WHERE语句过滤条件：

  select * 
  from mytable
  where clo1 in (select col2 from mytable2);

  下面的语句可以检索出客户的订单数量，子查询语句会对第一个查询检索的从每一个客户执行一次：

  select cus_name,(select count(*) from Orders where 	   Orders.cus_id= Customers.cust_id) as orders_num
  from Customers
  Order by cust_name;
  

  连接

  连接用于连接多个表，使用JOIN关键字，并且条件语句使用ON而不是WHERE

  连接可以替换子查询，并且比子查询的效率一般会更快。

  可以用AS给出列名，计算字段和表名去别名，给表名去别名是为了简化SQL语句以及连接相同表。

  内连接

  内连接又称为等值连接，使用INNER JOIN关键字

  select A.VALUE,B.value
  from mytable as A inner join table as B
  on A.KEY = B.KEY
  

  有没有条件语句的情况下返回笛卡尔积。

  自连接

  自连接可以看成内连接的一种，只是表时自身而已。

  一张员工表，包括员工姓名和员工所属部门，要找出与Jim处在同一部门的所有员工姓名。

  子查询版本：

  select name
  from employee
  where department = (
       select department
       from employee
       where name = "Jim"
  );
  

  自连接版本：

  select e1.name 
  from employee as e1 inner join employee as e2
  on e1.department = e2.department
  and e2.name = "Jim";
  

  自然连接

  自然连接是把同名列通过等值测试连接起来，同名列可以有多个。

  内连接和自然连接的区别，内连接提供连接的列，二自然连接自动连接所有同名列。

  select A.value ,B.value 
  from tablea as A natural join table as B;
  

  外连接

  外连接保留了没有关联的那些行，分为左外连接，右外连接以

  及全外连接，右外连接就是保留了左表没有关联的行。

  检索所有顾客的订单信息，包括还没有订单信息的顾客。

  select customer.cust_id,Orders.order_num
  from Customer left outer join orders
  on customers.cust_id = orders.cust_id
  

  customers表：

  cust_id	cust_name
  1	a
  2	b
  3	c

  orders表：

  order_id	cust_id
  1	1
  2	1
  3	3
  4	3

  结果：

  cust_id	cust_name	order_id
  1	a	1
  1	a	2
  3	c	3
  3	c	4
  2	b	null

  组合查询

  使用UNION来组合两个查询，如果第一查询返回M行，第二查询返回N行，那么查询的组合就是M+N行。

  每一查询必须包含相同的列，表达式和聚集函数。

  默认会去除相同行，如果要保留相同行，使用UNION ALL。

  只能包含一个ORDER BY子句，并且必须位于语句的最后。

  select col
  from mytable
  where col=1
  union
  select col
  from mytable 
  where col=2
  

  视图

  视图是虚拟的表，本身不包含数据也就不能对其进行索引操作。

  对视图的操作和普通表的操作一样。

  视图具有以下好处：

  • 简化复杂的sql操作，比如复杂的连接；

  • 只使用实际表的一部分数据；

  • 通过只给用户访问视图的权限，保证数据的安全性；

  • 更改数据格式个表示。

    create view myview as
    select concat(col1,col2) as concat_col,col3*col4 as compute_col
    from mytable
    where col5 = val;