创建库
create database [if not exsits] bdname [create-specification]
[default] character set charset_name
[defalut] collate collation_name
[] 可有可无
------------------------------------------
|创建mydb库
create database mydb1;
|创建一个utf-8字符集的mydb2
create database mydb2 character set utf8 collate utf8_general_ci;
|将数据库的字符集改成gb2312
alter database mydb2 character set gb2312;
|查看数据库
show databases;
show create database mydb3;
|删除库
drop database dbname;
|备份库(windows命令)
mysqldump -u用户名 -p密码 dbname > file name.sql
mysqldump -u用户名 -p密码 mydb2 > c:\\test.sql
|恢复库(先建立库)
1. source c:\test.sql
2. mysql -uroot -p密码 newdatabase < c:\\test.sql
建立表语法
|建立表
create table table_name(
filed1 datatype,
files2 datatype
) character set utf8 collate utf8_general_ci;
create table haha(
id int(10) unsigned zerofill
) character set utf8 collate utf8_general_ci;
数据类型:
Tinyint 【unsigned|zerofillo】 -128 -127 unsigned 无符号 0-265
bit(M) M默认是1 1-64
bool,boolean 0-1
smallint .... 2^16
bigint ...2^32
float(M,D) ... M 显示长度,的为小数位数
double(M,D)... 比float精度更高
char(size) 0-255
varchar(Size) 0-65535
blob longblob
Text(clob) longText(long)
Date(YYYYMM-dd)
DataTime(YYYY-MM-dd hh:mm:ss)
TimeStamp 时间戳 记录insert 。update操作用的时间
修改表
alter table table_name
[add,modify,drop]...
|给表添加image列
alter table haha add image blob;
|修改image列,让他数据类型为varchar(100)
alter table haha modify image varchar(100);
|删除imagelie
alter table haha drop image;
|修改表名为hehe
rename table haha to hehe;
|修改表的字符集utf8
alter table hehe character set utf8;
|修改列名id - uid
alter table hehe change column id uid int(5);
CRUD
Insert
insert into table_name(field1,…) values(value1,…);
|插入中文数据 乱码问题
show variables like ‘chara%’;
set character_set_client=gb2312;
Update
|修改所有的uid 为20;
update hehe set uid = 20;
|修改uid=20的image 为50,salary为500
update heeh set image=50,salary = 500 where uid =50;
|增加uid=20 salary 100
update hehe set salary = salary + 100 where uid=20;
Delete
|删除某些记录
delete from hehe where column_name = xxxx;
|删除所有记录
1.delete from hehe;[一行一行删]
2.truncare table hehe;【整个表删除。再重构表结构】
表达式与函数
1
|查询所有记录
1.select * from hehe;
2.select uid,image,name from hehe;
|过滤重复记录
select distinct uid from hehe;
2 表达式
|显示的uid+1
select uid+90 from hehe;
|统计uid,id的总和
select (uid+id) from hehe;
|使用别名显示uid - cid
select uid as cid from hehe;
3
|查询uid大于20的记录
select * from hehe where uid >20;
…
4 比较运算符
【>= 、<=、 =、 < 、>、 <>】
between 。。。and,,,
in
like ''
is null
and
or
|查询uid 12和20的记录
select * from hehe where uid in(12,20);
select *from hehe where 1=1 and uid=12 and image ;
order by
|根据uid 升序/逆序排列
select * from hehe order by uid asc/desc;
常用函数
“`
|合计函数count
select count(*)/count(uid) from hehe where uid < 20;
|求和函数sum
select sum(uid),sum(id) from hehe;
|平均函数avg
select avg(uid) from hehe;
select sum(uid)/count(uid) from hehe;
|Max、MIN
select MAX(uid),image from hehe;
select MIN(uid) from hehe;
|group by分组 可用于统计
select uid,image from hehe group by uid;
select uid,count(uid) from hehe group by uid;