mysql知识点总结归纳.doc

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

2、名（字段名，字段类型） 比如： 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

3、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

4、 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=ts ,mobile=110 where username=tangseng; 注意：修改数据时一定要过滤，否则全表的

5、数据将会被修改 (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

6、 （省略排序方式时,默认为 升序）; 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（即查询第一页的数据） ：se

7、lect * 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

8、(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,addre

9、ss a where u.id=a.user_id; 内连接:inner 可以省略不写（建议写上） select u.id, from user u inner join address a on u.id=a.user_id where 1=1; 注意：只连接匹配的行,其作用与等值连接相同 左外连接： 表 A left join 表 B on 条件 select u.id, from user u left join address a on u.id=a.user_id; 注意：包含左边表的全部行（不管右边的表中是否存在与它们匹配的行） ，以及右 边表中全部匹配的行 右外连接： 表 A r

10、ight 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 表

11、名(字段,字段,字段,.) 例如：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_form

12、at(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 D

13、UAL; 获取上个月的年份: DATE_FORMAT(DATE_SUB(2019-01-01,INTERVAL 1 MONTH),%Y) 日期转换为具体格式(两种形式): DATE_FORMAT(生产日期,%Y-%m-%d) =2017-07-05 DATE_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 分钟，数

14、值(00-59) %j 年的天 (001-366) %k 小时 (0-23) %l 小时 (1-12) %M 月名 %m 月，数值(00-12) %p AM 或 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=星期

15、六） %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

16、,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 查询

17、完的结果 table 代表输出的行所用的表 type 代表联合查询所使用的类型,从好到坏依次是: systemconsteq_refreffulltextref_or_nullindex_mergeunique_subquery index_subqueryrangeindexALL 注意:一般查询至少要达到 range 级别,最好能达到 ref. possible_keys:指出 MySQL 能使用哪个索引在该表中找到行。如果是空的，没有相关的索引。 这时要提高性能，可通过检验 WHERE 子句，看是否引用某些字段，或者检查字段不是适合 索引。 key:显示 MySQL 实际决定使用的键。如

18、果没有索引被选择，键是 NULL。 key_len:显示 MySQL 决定使用的键长度。如果键是 NULL，长度就是 NULL。 ref:显示哪个字段或常数与 key 一起被使用。 rows:这个数表示 mysql 要遍历多少数据才能找到，在 innodb 上是不准确的。 extra: 如果是 only index，这意味着信息只用索引树中的信息检索出的，这比扫描整个表要快。 如果是 where used，就是使用上了 where 限制。 如果是 impossible where 表示没有用 where，一般就是没查出来啥。 如果此信息显示 Using 或者 Using temporary 的话会很吃力，WHERE 和 ORDER BY 的索引经 常无法兼顾，如果按照 WHERE 来确定索引，那么在 ORDER BY 时，就必然会引起 Using ， 这就要看是先过滤再排序划算，还是先排序再过滤划算。