MySQL-学习笔记

命令行操作数据库：
terminal：
/Applications/XAMPP/bin
./mysql -u root -p
输入密码

创建数据库
create database database_name;
create database database_name if not exist;
切换数据库
use database_name;
删除数据库
drop database database_name;
＃重命名数据库：数据库的重命名需要导出数据库，新建数据库，然后再重命名
显示数据库列表：
show database;

创建表：
create table tbl_user(id int(3), name varchar(8), password varchar(20));
重命名表：
alter table tbl_user rename to tbl_members;
显示数据库中的所有表：
show tables;
show tables from database_name;
显示表结构：
describe table_name;
添加字段：
alter table tbl_user add email varchar(255) not null;
修改字段：
alter table tbl_user change id newid int(3);
删除字段：
alter table tbl_user drop email;

数据库文件列表解析：
*.opt —>数据库的配置信息
*.frm —>表结构信息
*.lbd —>数据记录，表的索引信息

数据类型—>保证数据的存储空间和效率；

整数类型：tinyint(1字节), smallint(2字节), mediumint(3字节), int(4字节) , bigint(8字节)
alter table tabledata add intsix int(6) zerofill; —>int(6) 6表示显示的位数，zerofill表示空位数用0填充，默认用空格代替

浮点类型：float, double, decimal
alter table tabledata add float2 float(5,2); —>5表示总共有5位，2表示小数点后2位

Bool类型：
tinyint —> true==1, false==0

字符类型：char, varchar(可变长度), blob（区分大小写）, text（一般大文本存储）, tinytext, longblob…
create table table_string(charstring char(10)); —> char(10) 表示长度不能超过10，如果超过数据会被截断
create table table_string(charstring varchar(10)); —> varchar(10) 表示默认长度为10，如果超过位数会自动延长
alter table table_string change charstring charstring char(10) binary; —> 将charstring字段修改成二进制存储，使有大小写区分；
Enum：
alter table table_string add gender enum(‘m’,’n’); —> 只能插入括号内字符，否则会出错
Set:
alter table table_string add setcol set(‘A’,’B’,’CD’); —>可以插入多个字符

日期和时间类型：date(YYYY-MM-DD), time(HH:MM:SS), year(YYYY), datetime, timestamp;
create table tabledata(birthday date);
insert into tabledata values(‘2015-04-01’)(20150401) —> 插入字符或者数值都可以
alter table tabledata add datetimecol2 datetime(2); —>2表示秒以后的位数
alter table tabledata add time1 timestamp; —> 第一个timestamp默认插入当前时间，第二个timestamp默认插入0；

插入数据：
insert into tabledata[] values();

连接到数据库的三种方式：
1）shell方式 —>terminal终端
/Applicatioin/XAMPP/Bin/mysql -h localhost -u Root -p
mysql —help
2）MySQL Workbench —>client
3）phpMyAdmin —>web

算术运算符
select 1+2; =3
select 2+null; =null
select ‘2’ *’5’ ; =10 —>字符会自动转换为数字，不能转换的字符会用0代替
select sint - intsix from tabledata; —> 字段与字段相减

比较运算符
= <> != > <=> between in is null like regexp

select 1=2; =0(false)
select 1=null; =null —>不能比较
select 1<=>null; =0(false) —> 对null类型的安全比较
select binary ‘jike’ = ‘JIKE’; =0(false) —>区分大小写
select ‘jike’ = ‘JIKE’; =1(true) —>不区分大小写
select ‘i’ > ‘k’ ; =0(false) —>按字母表去比较
select ‘b’ between ‘a’ and ‘c’; —>?
select not ‘d’ between ‘a’ and ‘c’;
select 5 in(2,3,5,6); =1(true)
select ‘jikexueyuan’ like ‘jike%’; =1(true) —>％表示多个字符
select ‘jikexueyuan’ like ‘ike’; =1(true) —>表示单个字符的匹配

逻辑运算符：
not/! and/&& or/|| xor
select (4>3) xor (3>5); =1(true);

位运算符：
& | ^ ~ >> <<
select 9 | 4; =13 —>9变成2进制，然后取或运算
运算的优先级应该用()保证运算顺序

基本的插入数据
create table addressbook(
fname varchar(255) null);
decribe addressbook;
insert into addressbook (fname) values(‘xxx’);
create table forums(name varchar(150) not null,
category varchar(50) default ‘UNIX’ not null,
postsperpage smallint default 15 not null));

自动递增：
create table users(uid tinyint not null auto_increment,
uname varchar(50) not nul default ‘’,
primary key(uid));
唯一性：
alter table users add uique(uname);

更新数据和删除数据：
update users set uname = ‘jike’ where uid = 2;
delete from users where uid = 1;
delete from users; —>默认不能直接删除整个表，必须要关闭安全模式才可以直接删除
show variables like ‘%sql_safe%’;
set sql_safe_updates = 0; —>关闭安全模式
insert into users(uname) values (‘hey’);

truncate table users; —> 清空数据表，自增键会重置
delete from users; —>删除数据表，自增键不会重置

数学函数：
ABS(-5.8); =5.8 —>取绝对值
ceiling(5.8); =6 —>得到比这个数大的整数
floor(5.8); =5 —>得到比这个数小的整数
greatest(1,2,3,8,12); =12 —>取最大值
least(1,2,3,8,12); =1 —>取最小值
mod(2,3); =2 —>取余数
pi(); =3.141593 —>取圆周率
rand(2); —>随机数
round(10.2364,2); —>2表示保留两位小数
truncate(10.2364,2); —>从2位小数截断
sign(-5.4); —>判断正负
sin(1); —>正弦
cos(1); —>余弦
degree(1); —>弧度转换为角度
radian(57); —>角度变成弧度
pow(2,3); —>2的3次方＝8；
exp(3); —>？
sqrt(4); —>4的平方根=2
log(8); —>对数
bin(10); —>10进制转换成2进制

聚合函数：
avg(字段) from table_test; —>平均数据；
min()
max()
count()
std —>标准偏差
variance —>标准方差

字符函数：
length(‘hello’); =5 —>字符串的长度
lcase(‘hello’); —>转换为小写
lower(‘hello’); —>转换为小写
ucase(‘hello’); —>转换为大写
upper(‘hello’); —>转换为大写
strcmp(‘hello’,’yes’); —>比较字符串
position(‘yes’ in ‘hyesman’); —>查找
replace(‘yes’,’y’,’hh’); —>替换y—>hh
(‘yes’,2,1,’hh’);
concat(‘hello’,’world’); —>合并字符串
concat_ws(‘hello’,’world’); —>
left(‘helloworld’,3); —>从左边开始，取3位字符
right() —>从右边开始，取n位字符
lpad() —>从左边开始填充字符
rpad() —>从右边开始填充字符
trim() —>去掉空格
substring (‘macket’,2,3); —>第2位开始取，取3位
ASCII(‘a’) —>取ASCII码

时间与日期函数：
now(); —>现在的日期和时间
curtime(); —>现在的时间
curdate(); —>今天的日期
year(20030312); —>取年份
mouth(20030312); —>取月份
mouthname();
dayofyear();
dayofweek();
dayname();
week();
hour(’14:56’); —>取小时
second(); —>取秒
minute(); —>取分钟
date_add(now(),interval 3 mouth) —>加3个月
date_sub(now(),interval 3 day) —>减3天

数据加密函数:
select password(’secret’); —>加密(不可逆)
select encrypt(’secret’,’abc’); —>unix系统的加密函数
select encode(’secret’,’abc’); —>加密
select decode(‘uname’,’abc’); —>解密
aes_encrypt(’secret’,’abc’);
aes_decrypt(uname,’abc’) from users;
select md5(’secret’); —>查询md5值
select sha(’secret’); —>查询md5值

控制流函数：
select if(1>10,2,3); —>如果true = 2,如果false ＝ 3；
select ifnull(1,2); —>true=2,false=1;
nullif(1,2);
case ‘green’ when ‘red’ then xxx

格式化：
date_format(now(),%w,%d,%m,%y,%r);
time_format(‘100:21:12’,’%h:%i %p’);
inet_aton(‘192.168.210.191’); —> ip地址转成数字
inet_ntoa(‘number’); —>数字转成ip
cast(’99’ as signed); —>?
convert(’23’,signed);