mysql知识点总结.doc

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)创建表Create table 表名（字段名，字段类型）比如：create table user(id int primary key, username varchar(20), password varchar(30),);(2)查看表结构desc 表名;(3)修改表,追加字段(列)alter table 表名 add 新增字段 字段类型,add 新增字段 字段类型;(增加多个字段,中间用逗号隔开)例如:alter table user add sex char(1),add address varchar(100);(4) 向表中插入数据若插入的是所有字段,则可以不写字段名比如: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 表名; 查询指定字段select 字段名,字段名 from 表名;例如：select username,password from user;在显示时给字段取别名select 字段名 别名,字段名 别名 from 表名;例如：select username 用户名,password 密码 from user;在查询时增加where筛选条件select * from user where sex=M and username=bajie修改字段数据update 表名 set 字段名=新数据,字段名=新数据 where 字段名=实际值;例如：update user set email=,mobile=110 where username=tangseng;注意：修改数据时一定要过滤，否则全表的数据将会被修改(5)删除数据delete from 表名 where 字段名=实际值;例如：delete from user where username=tangseng;注意：删除数据时一定要过滤，否则全表的数据将会被清空删除整张表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) 分页查询 limit begin,size注：begin是当前页的起始行号(从0开始)size是本页要显示的最大行数例如：查询第1条到第10条的数据的sql（即查询第一页的数据）：select * from table limit 0,10; 查询第10条到第20条的数据的sql（即查询第一页的数据）：select * from table limit 10,20; (8) 聚合函数sum:求和select sum(字段名) 别名 from 表名;例如：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 %联想%;注意：使用聚合函数时,附带id,name等细节数据是无意义的（count除外）ROUND:截取到小数点后一位小数,剩余部分四舍五入Trunc:截取到小数点后两位,剩余部分舍去(9) 分组group by 字段select 字段名 from 表名 group 排序字段名;例如：select category_id from item group by category_id;(10) 关联查询等值连接:查询出A和B相匹配的数据select u.id,from user u,address awhere u.id=a.user_id;内连接:inner可以省略不写（建议写上）select u.id,from user uinner join address a on u.id=a.user_idwhere 1=1;注意：只连接匹配的行,其作用与等值连接相同左外连接： 表A left join 表B on 条件select u.id,from user u left join address a on u.id=a.user_id;注意：包含左边表的全部行（不管右边的表中是否存在与它们匹配的行），以及右 边表中全部匹配的行右外连接： 表A right join 表B on 条件select u.id,from user u right join address a on u.id=a.user_id;注意：包含右边表的全部行（不管左边的表中是否存在与它们匹配的行），以及左 边表中全部匹配的行全外连接： 表A full join 表B on 条件select u.id,from user u right join address a on u.id=a.user_id;注意：包含左、右两个表的全部行，不管另外一边的表中是否存在与它们匹配的行。(11) 索引创建索引：create index 索引名 on 表名(字段,字段,字段,.)例如：create index index_of_item2 on item2(title(10),selling_point(10),price);删除索引：drop index 索引名 on 表名;4.其他几个常用操作(1)case when语句例如:CASE WHEN 地址 LIKE %山东% THEN 山东CASE WHEN 地址 LIKE %广东% THEN 广东 ELSE 其他 END AS 地址注意：then后面可以跟运算比如：then 广东+ 省(2) 日期操作当月月初:date_sub(date_sub(date_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);获取年：SELECT DATE_FORMAT(2017-12-12,%Y) FROM 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带有英文前缀的月中的天%d月的天，数值(00-31)%e月的天，数值(0-31)%f微秒%H小时 (00-23)%h小时 (01-12)%I小时 (01-12)%i分钟，数值(00-59)%j年的天 (001-366)%k小时 (0-23)%l小时 (1-12)%M月名%m月，数值(00-12)%pAM 或 PM%r时间，12-小时（hh:mm:ss AM 或 PM）%S秒(00-59)%s秒(00-59)%T时间, 24-小时 (hh:mm:ss)%U周 (00-53) 星期日是一周的第一天%u周 (00-53) 星期一是一周的第一天%V周 (01-53) 星期日是一周的第一天，与 %X 使用%v周 (01-53) 星期一是一周的第一天，与 %x 使用%W星期名%w周的天 （0=星期日, 6=星期六）%X年，其中的星期日是周的第一天，4 位，与 %V 使用%x年，其中的星期一是周的第一天，4 位，与 %v 使用%Y年，4 位%y年，2 位(3) 空值处理如果image字段为空,则显示无：select ifnull(image,无) image,ifnull(detail,无) detail from item;(4)自定义变量:局部变量的声明,没有的字段代表是列名:系统里面的全局变量(5) 隐藏显示REPLACE(手机号码, SUBSTR(字符串,起始位置,截取长度), 替代形式) 别名例如:REPLACE(手机号码, SUBSTR(手机号码,4,4), *) 手机号码注意:开始位置是从1开始的(6) 为空最好写成= 不为空最好写成不然在某些软件上数据查询可能会出错(7) 建议所有的SQL语句都是用explain分析执行计划.即:explain select * from user;其中:id代表select查询的序列号select_type中simple表示简单的select,没有union子查询primary表示子查询外面的select查询dependent union代表union中第二个或后面的select语句union result代表利用nuion查询完的结果table代表输出的行所用的表type代表联合查询所使用的类型,从好到坏依次是:systemconsteq_refreffulltextref_or_nullindex_mergeunique_subqueryindex_subqueryrangeindexALL注意:一般查询至少要达到range级别,最好能达到ref.possible_keys:指出MySQL能使用哪个索引在该表中找到行。如果是空的，没有相关的索引。这时要提高性能，可通过检验WHERE子句，看是否引用某些字段，或者检查字段不是适合索引。key:显示MySQL实际决定使用的键。如果没有索引被选择，键是NULL。key_len:显示MySQL决定使用的键长度。如果键是NULL，长度就是NULL。ref:显示哪个字段或常数与key一起被使用。rows:这个数表示mysql要遍历多少数据才能找到，在innodb上是不准确的。extr