mysql知识点总结资料讲解

1、资料收集于网络，如有侵权请联系网站删除1.访问数据库语句(1)连接数据库mysql -u 用户名-p 回车- 输入密码（如果没有设置密码，则直接回车即可）(2)查看已存在的数据库show databases;(3)创建新的数据库create database 数据库名 ;(4)删除已经存在的数据库drop database 数据库名 ;(5)选择需要使用数据库use 数据库名 ;(6)退出数据库exit:先隐性提交数据，再退出；quit: 不提交数据，直接退出；2.数据库执行顺序:- from -> where -> group -> select3.表的相关操作(1)创建表

2、Create table 表名（字段名，字段类型）比如：create table user(idint primary key,username varchar(20),password varchar(30),word 可编辑资料收集于网络，如有侵权请联系网站删除);(2)查看表结构desc 表名 ;(3)修改表 ,追加字段 (列 )alter table 表名 add 新增字段 字段类型 ,add 新增字段 字段类型 ;(增加多个字段 ,中间用逗号隔开 )例如 :alter table user add sex char(1),add address varchar(100);(4) 向表

3、中插入数据 若插入的是所有字段,则可以不写字段名比如 :insert into user values('zs','123456',null,null); 若插入的是一部分字段, 则必须写字段名比如 :insert into user(username,password)values( 'ls','123456' ) ；注意：若插入的主键值重复，则会报错，建议对主键id 使用 auto_increment自增(5)查询表数据 查询所有数据select * from表名 ;字段去重显示：select distinct字段名from

4、表名 ; 查询指定字段select 字段名 ,字段名from表名 ;例如：select username,password from user; 在显示时给字段取别名word 可编辑资料收集于网络，如有侵权请联系网站删除select字段名别名 ,字段名别名from表名 ;例如：select username'用户名',password'密码 'from user; 在查询时增加where 筛选条件select * from user where sex='M' and username='bajie' 修改字段数据update 表

5、名set 字段名 ='新数据 ',字段名 ='新数据 ' where字段名 ='实际值 '例如：update user set email='ts',mobile='110' where username='tangseng'注意：修改数据时一定要过滤，否则全表的数据将会被修改(5)删除数据 delete from 表名 where 字段名 ='实际值 '例如： delete from user where username='tangseng'注意：删除数据时一定要

6、过滤，否则全表的数据将会被清空 删除整张表drop table表名 ;(6) 排序order by字段asc(升序 )/desc( 降序 )默认情况是升序例如： select * from item where name like '%联想 %' order by price asc; select * from item where name like '%升序） ;联想 %' order by price（省略排序方式时,默认为 select * from item order by category_id desc,price asc （多个字段同时排序，

7、从左到右依次排序，若第一个字段数据有重复，再按照第二个排序，依次类推）;(7) 分页查询limit begin,size注： begin 是当前页的起始行号(从 0 开始 )word 可编辑资料收集于网络，如有侵权请联系网站删除size 是本页要显示的最大行数例如：查询第 1 条到第 10 条的数据的sql（即查询第一页的数据）：select * from table limit 0,10;查询第 10 条到第 20 条的数据的sql（即查询第一页的数据）：select * from table limit 10,20;(8) 聚合函数 sum:求和select sum(字段名)别名from表

8、名 ;例如：select sum(stock) as total_stock from item; avg:求平均值select avg(字段名 ) 别名 from表名 ;select avg(price) as avg_price from item; count: 求返回数据的行数查询数据总条数：select count(*) from item where name like '%联想 %' max/min: 求最大值/ 最小值select min(price) from item where name like '%联想 %'注意：使用聚合函数时,附带i

9、d,name等细节数据是无意义的（count除外） ROUND:截取到小数点后一位小数,剩余部分四舍五入Trunc:截取到小数点后两位,剩余部分舍去(9) 分组group by字段select字段名from表名group排序字段名;例如：select category_idfrom item group by category_id;(10) 关联查询 等值连接word 可编辑:查询出A 和B 相匹配的数据资料收集于网络，如有侵权请联系网站删除selectu.id,from useru,addressawhere u.id=a.user_id; 内连接 :inner 可以省略不写（建议写上）s

10、electu.id,from useruinner join addressa on u.id=a.user_idwhere 1=1;注意：只连接匹配的行,其作用与等值连接相同 左外连接：表 A left join表B on条件selectu.id,from user uleft join address a on u.id=a.user_id;注意：包含左边表的全部行（不管右边的表中是否存在与它们匹配的行），以及右边表中全部匹配的行 右外连接：表A right join表B on条件selectu.id,from user uright join address a on u.id=a.us

11、er_id;注意：包含右边表的全部行（不管左边的表中是否存在与它们匹配的行），以及左边表中全部匹配的行 全外连接：表A full join表B on条件selectu.id,from user uright join address a on u.id=a.user_id;注意：包含左、右两个表的全部行，不管另外一边的表中是否存在与它们匹配的行。word 可编辑资料收集于网络，如有侵权请联系网站删除(11) 索引创建索引： create index 索引名on 表名 (字段 ,字段 ,字段 ,.)例如： create index index_of_item2 on item2(title(10

12、),selling_point(10),price);删除索引： drop index索引名on 表名 ;4.其他几个常用操作(1)case when 语句例如 :CASEWHEN 地址LIKE '%山东 %'THEN '山东 'CASEWHEN 地址LIKE '%广东 %'THEN '广东 'ELSE 其'他 'ENDAS地'址'注意： then 后面可以跟运算比如： then'广东 '+ '省 '(2) 日期操作 当月月初 :date_sub(date_sub(d

13、ate_format(now(), '%y-%m-%d'),interval extract(day from now() - 1 day), interval 0 month); 当月月末 :date_sub(date_sub(date_format(now(), '%y-%m-%d'),interval extract(day from now() day), interval - 1 month); 获取年：word 可编辑资料收集于网络，如有侵权请联系网站删除SELECT DATE_FORMAT(2017-12-12 ,'%Y') FRO

14、M DUAL; 获取上个月当前时间:SELECT DATE_SUB(NOW(),INTERVAL 1 MONTH) FROM DUAL; 获取上个月的年份:DATE_FORMAT(DATE_SUB('2019-01-01',INTERVAL 1 MONTH),'%Y')日期转换为具体格式( 两种形式 ):DATE_FORMAT(生产日期 ,'%Y-%m-%d')=2017-07-05DATE_FORMAT(生产日期 ,'%y-%m-%d')=17-07-05格式描述%a缩写星期名%b缩写月名%c月，数值%D带有英文前缀的月中的天%

15、d月的天，数值 (00-31)%e月的天，数值 (0-31)微秒%f%H小时(00-23)%h小时(01-12)%Iword 可编辑资料收集于网络，如有侵权请联系网站删除小时(01-12)%i分钟，数值 (00-59)%j年的天(001-366)%k小时(0-23)%l小时(1-12)%M月名%m月，数值 (00-12)AM或PM%pword 可编辑资料收集于网络，如有侵权请联系网站删除时间， 12- 小时（ hh:mm:ss AM或 PM）%r%S%s%T%U%u%V%v%W%w%X%x秒 (00-59)秒 (00-59)时间 , 24-小时 (hh:mm:ss)周 (00-53)星期日是一

16、周的第一天周 (00-53)星期一是一周的第一天周 (01-53)星期日是一周的第一天，与%X使用周 (01-53)星期一是一周的第一天，与%x使用星期名周的天（ 0= 星期日 , 6= 星期六）年，其中的星期日是周的第一天，4 位，与%V使用年，其中的星期一是周的第一天，4 位，与%v使用%Y年，4 位%y年，2 位(3) 空值处理如果 image 字段为空 ,则显示无：select ifnull(image,' 无 ')image,ifnull(detail,' 无 ')detail from item;word 可编辑资料收集于网络，如有侵权请联系网站删除

17、(4)自定义变量 :局部变量的声明,没有 的字段代表是列名:系统里面的全局变量(5) 隐藏显示REPLACE(手机号码 , SUBSTR(字符串 ,起始位置 ,截取长度 ), '替代形式 ') 别名例如 :REPLACE(手机号码 , SUBSTR(手机号码 ,4,4), '*')手机号码注意 :开始位置是从1 开始的(6) 为空最好写成 =不为空最好写成<>不然在某些软件上数据查询可能会出错(7) 建议所有的 SQL语句都是用 explain 分析执行计划 .即:explain select * from user;其中 :id代表select查询

18、的序列号select_type中simple表示简单的select, 没有union子查询primary表示子查询外面的select 查询dependent union代表union中第二个或后面的select 语句union result代表利用nuion查询完的结果table代表输出的行所用的表type代表联合查询所使用的类型,从好到坏依次是:system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>ind