MySql数据库

1,创建数据库

create database [if not exists] db_name;

创建的时候设置字符集

create database [if not exists] db_name character set utf8;

创建数据库时设置字符编码,并带校对规则

create database [if not exists] db_name character set utf8 collate utf8_bin;

 

2,查看数据库

show databases;

查看创建数据库时的语句

show create database db_name;

3,删除数据库

drop database db_name;

 

4,修改数据库

alter database mydb2 character set gbk;

5,选择数据库、查看选择的数据

use db_name;select database();

=======================================================================================

1,创建表

create table table_name

{

field1 type,

field2 type,

field3 type

}

 

2,查看表的信息

desc table_name;

列出当前数据库的所有代表

show tables;别忘了加S哦!

查看创建表的语句

show create table table_name;

3,修改表

alter table table_name add column type增加列

alter table table-name modify column type 修改列名

alter table table_name drop column删除列名

alter table table_name change old_name New_name type修改列名

rename table table_name to new_table_name;修改表名

4,删除表

drop table table_name;

=======================================================================================

1,insert插入数据

insert into table_name values(value1,value2,value3),(value4,value5,value5);

2,update更新表

update table_name set column = new_value where column = '条件'

3,delete删除语句

delete from table_name where column = '条件';

delete from table_name删除所有内容

truncate table_name 删除所有内容,效率比较高

=======================================================================================

4,select查询语句

select distinct column from table;去除相同的结果

select * from table_name where column between value1 and value2;

select * from table_name wherer column in value1 and value2;

select * from table_name where column like '张%';{%代表任意多个字符,"_"代表一个任意字符}

 

-----------------------------------------------------------------------------------------------------------------------------

order by

select * from table_name order by column asc/desc;"asc"代表升序;"desc"代表降序;

----------------------------------------------------------------------------------------------------------------------------

聚合函数

sum,count,avg,max/min

select sum(column) from table_name;

select count(*) from table_name;

select ave(*) from table_name;

select max(column) from table_name;

select min(column) from table_name;

-----------------------------------------------------------------------------------------------------------------------------

分组操作

group by

select * from table_name group by column;

-----------------------------------------------------------------------------------------------------------------------------

关键字的先后的出现顺序

select    from     where      group by      having       order by

------------------------------------------------------------------------------------------------------------------------------

执行顺序是

from    where    select    group  by    having    order  by

------------------------------------------------------------------------------------------------------------------------------

关键字where和having的注意点

1,where是在分组前使用,而having是在分组后使用.

2,使用where的地方都可以使用having,

3,having可以使用聚合函数,where中不可以使用聚合函数;

4,在having中使用的字段必须在having之前使用过.

--------------------------------------------------------------------------------------------------------------------------------

左连接查询

select * from table1_name right join table2_name on table1.id = table2.id;

右链接查询

select * from table1_name left join table2_name on table1.id = table2.id;

全链接查询

select * from table1_name right join table2_name on table1.id = table2.id

union

select * from table1_name left join table2_name on table1.id = table2.id;

内连接查询

select * from table1_name inner join table2_name on table1.id = table2.id;

 

5.外键

外键就是在多的方设置一的方的id.

foreign key(值) references dept(id)

 6.

show variables like'character%';

set names gbk;指定当前窗口所使用的编码集

通过修改my.ini 修改字符集编码

总结:如果查询的值有一个为null 的,用聚合函数计算的,结果就会出现null,解决的方案是,用ifnull(column,0),把为空的字段值设为0就行了,再用聚合函数计算就不会出现类似问题了.