mysql 表达式运算函数_Mysql创建库、修改表、Mysql表达式与函数、Mysql比较运算符...

创建库

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;