mysql 学习笔记

select database(); 查看当前数据库

show create database mDataBase;  查看已经创建好的数据库mDataBase

create database mDataBase character set utf8;  创建字符编码为utf8的数据库mDataBase

alter database mDataBase character set utf; 修改数据库mDataBase 的编码

数据库变量的类型

整形  int 4个字节

浮点型  double 8个字节 ,float 4个字节(不建议)

时间  datatime 2015.07.22 09:00 默认为null，timestamp 2015.07.22 09:00 默认为当前时间

字符型

  小于256个字节  varchar ,char 。varchar可变字符串，char固定字符串

  大于256个字节  text，blob。text字符，blob字节

create table t1(id int not null)

drop table t1

alter table t1 drop id

alter table t1 add name varchar(16 ) primary key

alter table t1 change id ids int

alter table t1 modify name varchar(18)

rename table t1 to t2

insert into t1 values (21,'zhangsan')

insert into t1(id) values(62) // value后面只有一个值时可以写value否则写values

update t1 set name=muss where id 342

update t1 set salary=salary+3000 where name='eric'

delete from t1 where id=928             ------>这是dml

trancate table t1     ----->这是ddl

select * from employee1 where gender='female'  and age<30

select * from employee1 where department='art' or age<25

select * from employee1 where name in ('zhangsan','lisi','laowu')  查询三个员工的记录

select * from employee1 where name not in ('zhangsan','lisi','laowu')  查询不是三个员工的记录

select * from employee1 where name is null   不应该写name==null    ，跟js一样 有专门判断是不是null的方法

select * from employee1 where name is not null 

select * from employee1 where age between 20 and 30   一个条件

select * from employee1 where age>20 and age<30     两个条件

select * from employee1 where name is not null

select * from employee1 where not name is null      跟上面语句的结果一样

select * from employee1 where name like '__浩'       模糊查询 前两个字段是任意字符第三个字符是浩的人

select * from employee1 where name like '陈%'      第一个字符陈的人

select distinct  department from corporation    列出所有部门名称，不要重复出现

select salary+ifnull(comm,0) from employee          mysql中null加任何数据都为null，所以有了ifnull方法。if comm is null return 0

select salary+ifnull(comm,0) as total_income  from employee   更改组个列名的名称

select * from employee order by salary desc ,empnum asc    主要按照salary排序，如果salar相同按照empnum排序

聚合函数

select count(*) from emp;

select count(employeename) where  salary is not null and  salary+ifnull(comm,0)>2500

select sum(salary) from employee