设为首页 加入收藏

TOP

Mysql创建库、修改表、Mysql表达式与函数、Mysql比较运算符
2017-10-19 09:21:58 】 浏览:207
Tags: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;

】【打印繁体】【投稿】【收藏】 【推荐】【举报】【评论】 【关闭】 【返回顶部
上一篇数据库存储过程 下一篇MongoDB连接池介绍,连接池重要参..

最新文章

热门文章

Hot 文章

Python

C 语言

C++基础

大数据基础

linux编程基础

C/C++面试题目