Mysql的基本使用

创建库

create database [if not exsits] bdname [create-specification][default] character set charset_name[defalut] collate collation_name[] 可有可无------------------------------------------|创建mydb库create database mydb1;|创建一个utf-8字符集的mydb2create database mydb2 character set utf8 collate utf8_general_ci;|将数据库的字符集改成gb2312alter database mydb2 character set gb2312;|查看数据库show databases;show create database mydb3;|删除库drop database dbname;|备份库（windows命令）mysqldump -u用户名 -p密码 dbname > file name.sqlmysqldump -u用户名 -p密码 mydb2 > c:\\test.sql|恢复库(先建立库)1. source c:\test.sql2. 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-265bit(M) M默认是1 1-64bool,boolean   0-1smallint .... 2^16bigint ...2^32float(M,D) ... M 显示长度，的为小数位数double（M,D)... 比float精度更高char(size)  0-255varchar(Size) 0-65535blob  longblobText(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);|删除imageliealter table haha drop image;|修改表名为heherename table haha to hehe;|修改表的字符集utf8alter table hehe character set utf8;|修改列名id - uidalter 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，，，inlike ''is nullandor|查询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;

其他 时间日期 数学函数 字符串 看文档