Mysql笔记

1.创建数据库命令: creat database 数据库名称

2.删除数据库命令: drop database 数据库名称

3.mysql数据字段的类型对数据库的优化很重要,mysql数据库支持多种数据类型,大致分为数值,日期/时间,和字符串(字符)类型.

4.插入数据: 

        INSERT INTO table_name ( field1, field2,...fieldN )  VALUES  ( value1, value2,...valueN );

5.查询: select * from 表名 where 条件;

 WHERE 子句的字符串比较是不区分大小写的

6.修改数据:  UPDATE 表名 SET field1="value1",field2="value2"...  where条件;

7.like子句: SELECT * FROM `user` where 表名 LIKE "关键字字符串%"; (%:  占位符)

8.查询条件语句汇总:

       or: where id = 1002 or id= 1003; 

       in: where id in(1002,1003);    // in操作符语句更简洁,并且in的执行速度比or快,而且使用in操作符可以执行更加复杂的嵌套查询

        not in: where id not in(1002,1003);

        between and:  where id between 1001 and 1005;

        not between and: where id not between 1001 and 1005;

        and: where id between 1001 and 1005 and id < 1003;

        distinct: select distinct id from tablename; // distinct操作符过滤重复行的数据

9.查询总数

        SELECT COUNT(*) as Total FROM `employee` where e_id = 1001;

10.分组

        SELECT e_id, COUNT(*) as Total FROM `employee` GROUP BY e_id;

11.order by: 排序, 多字段排序使用逗号分隔

12.limit: 限制返回记录条数

        SELECT * FROM `employee` LIMIT 1,4;

13: 内连接 inner jion

        SELECT department.d_id,d_name,name,e_salary FROM employee,department WHERE employee.d_id = department.d_id;