数据库命令记录

好记性不如烂笔头，每次看了忘，忘了看，这次做一个记录！
查看存在的数据库

mysql > show databases;

转到自己需要查看的数据库

mysql > use 数据库名;

查看数据库中的表

mysql >show tables;

显示表列

mysql >show columns from 表名;

从example表中,检索名为id的列(输出结果的顺序是杂乱无章的)

mysql >select id from example;

从example表中,检索名为id的列，title的列，time的列

mysql >select id , title ,time from example;

time这一列的日期很多都是重复的，现在只需要每个日期返回一次，列名前加关键字distinct

mysql >select  distinct time from example;

限制输出五行，限制加关键字：limit 数字 （显示的结果是select的基础上限制的，select返回的是无序的，第一行是0）

mysql >select id from example limit 5;

限制输出的从XX到XX行，如，限制输出搜索结果的第2到8行

mysql >select id from example limit 2，8;

排序输出关键字：order by 列名，列名，列名（前边的检索结果将会按照by后边的列名排序输出 ）默认升序

mysql >select id from example order by  id; #按照id排序输出
mysql >select id from example order by  time;#按照time排序输出
mysql >select id , title ,time from example order by id , title ；
#检索三列，按照前两列结果排序，先按照id排序，然后按照tilte排序

降序排列，关键字：desc ，想降序哪个字段就写在那个字段后边

mysql >select id , title ,time from example order by id  desc, title ；
#搜索结果按照id降序排序，降序对title无效

过滤条件搜索，加关键字where。
如果同时使用order by和where,order by 在where的后边
where =" 字符串" ，字符串不区分大小写

#搜索id = 18的有关字段
mysql >select id , title ,time from example where id = 18;
#可以用表中任意的字段通过where字段顾虑
mysql >select id , title ,time from example where  name = "hahahha"
#id为1到20之间的数据
mysql >select id , title ,time from example where id between 1 and 20;
#返回time为空的
mysql >select id , title ,time from example where time  IS NULL;

where

and操作符 /or操作符
and操作符的优先级大于or，因此，设置条件时最好加括号
每额外添加一条过滤语句就增加一个and或者or

#id小于18    且    time等于time = "2019-01-01"的数据
mysql >select id , title ,time from example where id  <  18  and  time = "2019-01-01"；
#id小于18    或者   time等于time = "2019-01-01"的数据
mysql >select id , title ,time from example where id  <  18  or  time = "2019-01-01"；

in操作符
in (限定,限定,限定),就是限定过滤的条件范围，范围就在in后边的括号里
比如:
“where id in(1001,1002) 等价于：where id = 1001 and id =1002

#输出id为1，2，3的数据
mysql >select id , title ,time from example where id in (1,2,3)；

not操作符 匹配除了后边的条件的一切

#输出除了id为1，2，3的所有数据
mysql >select id , title ,time from example where  id not  in (1,2,3)；

插入操作 insert into

这种方法不推荐
插入一条完整的数据，如果某一列没有值，应该给NULL，各个列必须使用他们在表定义中出现的次序填充

#插入一行完整的数据到costomer表,
#注意，id虽然是自增的，但是必须要给值，给的值起一个占位作用，实际id依然值自增之后的值，
#insert into customer values('id','name','age');
#给ＮULL占位
mysql >insert into customer values(NULL,'bob','18');

推荐的方法
比较繁琐，但是安全，id自然自增
插入省略某些行的条件是：
（1）该列定义允许为NULL
（2）在表定义给出默认值，如果不给默认值，将使用默认值

mysql >insert into customer  （name,age）values('bob','18');

如果插入数据的列名相同，一次插入多条数据，每组值括起来，用逗号分开就行

mysql >insert into customer  （name,age）values('bob','18')，（'Tom','20'），（'Mark','30'）；

插入检索的数据 insert ....select

#从custnew 检索年龄大于20的数据插入到customers表
mysql >insert into customers(name,age) select name age from custnew where age > 20

更新数据：update,更新时注意where的使用
基本形式：update 表名 set 列名 = "新值" （where 条件）如果没where条件，表中这一列全部被更新
多个列更新，set后边用逗号隔开
(1)更新特定的行
(2)更新所有的行

#更新id 为1005的人的邮箱
mysql >update customers set email = '123@369.com' where id = 1005；
#更新邮箱、名字、工作。其中工作设置为空（假如表允许job为NULL）
mysql >update customers set email = '123@369.com'，name = 'Bob',job = NULL where id = 1005；

注意：update如果更新多行，只要有一行出现错误，所有的更新被取消，如果想忽略出现的错误，继续更新则使用关键字update ignore

删除数据delete
delete不能删除表
形式： delete from 表名 （where 条件） 省略where将删除表中所有数据
(1)表中删除特定的行
(2)表中删除所有的行

mysql >delelte from customer where id = 1006

创建表create table

创建表示例
注意：
(1) NULL值就是没有值或者缺值，NOT NULL值的列不接受该列没有值得行，即插入时该列必须有值
(2)主键必须是唯一的，主键用于单独列，则值必须唯一，如果多个列为主键，则组合必须唯一
(3)AUTO_INCERENT表示每进行一次insert操作时，自动对该列增量，select last_insert_id()返回最后自增的id值
(4)使用默认值，可以通过设置default设置默认值
(5) InnoDB是一个可靠的事务处理引擎，不支持全文本搜索；MEMORY数据存储在内存，速度特别快； MyISAM是一个性能极高的引擎，支持全文本搜索，不支持事务处理。
默认值
更新表 alter table
(1)更改表名
(2)更改列
增加列：

#给表vendors 增加一个名为ven_phone 列，设置类型为char(200)
mysql >alter table vendors add ven_phone char(200)

删除列：

#删除表vendors  的列ven_phone 
mysql >alter table vendors  drop column ven_phone ;

定义外键：
alter table 表名 add constraint 外键名 foreign key (列名) references 外键表名（外键列名）

#给表orderitems 增加一个外键fk_orderitems_orders ，外键来自于表orders 的order_num字段
mysql >alter table orderitems add constraint fk_orderitems_orders foreign key(order_num) references orders (order_num)

删除表drop
drop table 表名

#删除表custormers
mysql >drop table custormers;

重命名表rename
rename table 旧表名 to 新表名

mysql >rename table customers  to new_customers  

like模糊查询：
当你的查找条件不成熟时，比如你只记得title中以中国开头的标题等，建议少用，查
询速度慢
（1）%百分号：匹配任意多的字符

#查找title以中国开头
mysql >select id , title ,time from example where  title like  '中国%';
#查找title包含中国的
mysql >select id , title ,time from example where  title like  '%中国%';
#查找title以中国结尾的
mysql >select id , title ,time from example where  title like  '%中国';

（2）_下划线：仅且只能匹配一个字符

#匹配包含 X国的title X 代表任意的：中国，美国。。等等
mysql >select id , title ,time from example where  title like  '_国%';

正则表达式匹配：regexp 基本上和平时学的正则匹配一样的

mysql >select id , title ,time from example where  title regexp '中国';

like和regrexp有个重要的区别：
假设现在的titile有两个值：

title	time
中国	2019-08-03
我爱中国	2019-08-03

下面的like语句只能检索出第一条数据  如果'%中国'，那么就可以检索出两个了
mysql >select id , title ,time from example where  title like  '中国';

正则表达式可以检索两条数据
mysql >select id , title ,time from example where  title regexp '中国';

正则表达式的or : |   ，匹配中国、美国  
mysql >select id , title ,time from example where  title regexp '中国 | 美国';
匹配几个字符之间  匹配中国、美国，简化版的 |
mysql >select id , title ,time from example where  title regexp '[中美]国';

特殊字符查找：必须加双斜线：'\\' 比如 '\\.'表示查找. '\\\' 就表示查找\

#包含.的title
mysql >select id , title ,time from example where  title regexp '\\.';

特殊字符表

创建计算字段
（1） concat(参数，参数，参数) 将列拼接在一起。参数可以是列名或者字符串，参数的个数是任意的

#将id title 和time拼接在一起，中间填充字符串****
mysql >select  concat(id , '****',title ,'****',time ) from example

（2） Rtrim()函数，去除右边空格
LTrim()去除左边的空格
Trim()去除两边的空格

mysql >select  concat( rtrim (id) , '****',trim(title) ,'****', ltrim(time )) from example

（3）算术计算 关键字 as 算术操作符包括： + 加、 - 减、 * 乘、 / 除

# 计算每一行的 id  * price ，结果作为新的一行result 输出
#输出结果是三列表格：一列id  一列price   一列结果
mysql >select id , price, id * price as  result  from example  

（4）文本函数

# upper函数将文本装换为大写，输出两列，一列原始url，一列转换的url_upper
mysql >select url  , upper (url ) as  url_upper from example  

文本函数
文本函数

#输出和YLei读音相似的数据，比如Y.Lee 等 sounddex就读音
mysql >select name from example where soundex(name)  = soundex ('Y Lie');

日期和时间处理函数:yyyy-mm-dd

#找到指定日期的数据 日期格式必须为：
mysql >select id from example where data(time) = '2019-08-03'

日期处理函数
数值处理函数

聚集函数

聚集函数

（1）avg()返回列、行的平均值,avg的参数必须给出，如果求多个列平均值，则需要多次使用avg函数
avg函数忽略NULL

#返回price这一列的平均值
mysql >select avg(price) as avg_price from example ;

（2）count()函数计数。确定表中行的数目或符合特定条件的行的数目
count(*)对于多有的都计数
count(column)排除NULL值

#返回表总共有多少行
mysql >select count(*) as num from example ;
#返回日期非空的有多少行
mysql >select count(time) as time_count from example;

（3）max()函数求列最大，需要指定列,列可以是文本等，忽略NULL

#找价格最大的
mysql >select max(price) as price_max from example

（4）min()函数求列最小，需要指定列

#找价格最小的
mysql >select min(price) as price_min from example

（5）sum()函数，返回指定列只的和，可以是计算得到，忽略NULL

#计算number列之和
mysql >select sum(number) as items_total from example
 # 计算number  *  prices 列之和
mysql >select sum(number  *  price ) as items_total from example

mysql 5支持在以上五个函数里加入 distinct,但是count(*)里不能加入distinct。

mysql >select avg(distinct price) as avg_price from example ;

这五个函数也可以组合

mysql >select count(*) as num, 
            min(price) as min_price,
            max(price) as max_price,
          vag (price) as vag_price from example;

分组group by :就是统计一个字段有几个值，每个值有多少个
有NULL作为单独一组返回，多个NULL为一组group by
group by必须使用和select相同的表达式等，不能使用别名（as后的就是别名）
group by 在where之后，order by之前

# 下面这个统计 表example中time字段包含几个时间，每个时间总共有多少行数据
mysql >select time  count(*) as num_time from  example group by time;

时间是31号的数据有6行，29号的数据有50行

过滤分组having,where是对于行的过滤，对分组的过滤就是having，基本和where一样，但是放在group by 后边，
having 支持所有的where操作
where就是数据分组前过滤，having就是数据分组后过滤

#下面这个统计 表example中time字段包含几个时间，每个时间总共有多少行数据
#然后输出时间总数大于10的时间
mysql >select time  count(*) as num_time from  example group by time having count（*）> 10; 

时间总行数大于10的就只有29号的50行

子查询：就是嵌套查询，前一个查询的结果作为后一个查询的条件,嵌套可以一直嵌套

#先查找tilte是中国的id ,然后去这些id里找url ,
mysql >select url from example where  id in(select id from example where title = "中国")

联结： 连接的条件是存在两个表，A，B ，其中B的外键是A的主键

A表vendors：供应商id（主键）和名字 vendors

vend_id	vend_name
1001	华为
1002	苹果
1003	小米

B表product，pro_id（主键） ,pro_name ,pro_price ,pro_vend(外键)

pro_id	pro_name	pro_price	pro_vend
6666	手机	8888	1001
6667	路由器	666	1001
7777	手机	5555	1002
7778	路由器	888	1002

#从两个表中 找出供应商的名字，产品的名字和价格，其中 通过供应商id连接了两个表，最后排个序
#where句子里的就是联结条件
mysql >select   vend_name ,pro_name ,pro_price from vendors ,products 
            where vendors.vend_id =  products.pro_vend
            order by vend_name ,pro_name;

一种联结的关键字：inner join .....on 其中inner join 前后是需要联结的表名字，on后边的条件和where一样

#和前边的联结语句输出一样
mysql >select vend_name.por_name,pro_price from vendors inner join products
            on vendors.vend_id = products.pro_id

表别名：和前边的别名一样都是as操作,一般联结用的多，或者你的表名很长或者需要多次使用

#表example设置别名为e
mysql >select time ,id from example as e where e.url = '*****';

组合查询UNION
链接多条select语句，并将结果作为一个表返回
规则：union必须由两条及其以上的select语句组成，之间用union分开，每个查询必须包含相同的列。
union会主动去除重复的行 ， 关键字union all 不会主动去除重复的行
在union使用order by时只能在最后一条select语句使用，结果是对所有的select结果进行排序

#查找价格不大于5元的，查找id为1001和1002 的
mysql >select vend_id ,pro_id from products where pro_price <= 5 union select vend_id ,pro_id from  products  where  vend_id  in (1001,1002)