说明
在本篇教程中使用以下命名
库名:test
表名:cs
列名:a,b
数据表的增删改查
修改表结构
#创建数据库UTF8格式
create database test default charset utf8;
#创建数据表
create table cs(a int(10),b varchar(200)) default charset utf8;
#查看表结构
desc cs;
#查看创建库的sql语句
show create database test;
#查看创建表的sql语句
show create table test;
#修改表的字段定义,变更为varchar(180)
alter table cs modify a varchar(180);
#表添加新字段c,类型为varchar(100)
alter table cs add column c varchar(100);
#表删除字段c
alter table cs drop column c;
#表字段b改名为d,并且数据类型改为varchar(50)
alter table cs change b d varchar(50);
#表添加字段c,数据类型为int(4),添加在字段a后面
alter table cs add c int(4) after a;
#表修改字段c,将它放在最前面
alter table cs modify c int(4) first;
#修改表名,cs改为cs1
alter table cs rename cs1;
#插入数据
insert into cs(a,b)values('14','2333');
insert into cs(a,b)values('122','测试N'),('16','测试2'),('17','测试3'),('18','测试4');
#a列的14为行,b列为列,修改后的为修改的内容
update cs set b='修改后的' where a=14;
#清空表
truncate table cs;
#删除行数据
update cs a,cs2 b set a.b=a.b*b.q,b.w=a.a where a.a=b.q;
delete from cs where a='169.52';
查询表数据
#过滤掉a列重复数据
select distinct a from cs
#查询a为14的数据
select * from cs where a=14
#查询a小于10并且大于4的数据
select * from cs where a=10 and a>4
#a列从小到大排列(asc是从小到大,desc是从大到小)
select * from cs order by a asc;
#从第三条数据以下,显示3条数据
select * from cs order by a asc limit 3,3;
#计算数据库有多少条数据
select count(1) from cs;
select a,count(1) from cs group by a with rollup;
#sum=a的和,max=最大的a,min=最小的a
select sum(a),max(a),min(a) from cs;
#表连接
select a,b,w from cs,cs2 where cs.a=cs2.q;
#左链接,以左面数量为基准,右面多余的不要了(right是右链接)
select a,w from cs left join cs2 on cs.a=cs2.q;
#子查询
select * from cs where a in(select q from cs2);
#数据联合显示(union all换成union可以过滤掉重复数据)
select * from cs union all select * from cs2;
表数据类型
#表前头数字填
alter table cs modify a int(10) zerofill;
#设置id自动增长
create table ai(id int(14) AUTO_INCREMENT NOT NULL , PRIMARY KEY(ID));
#总共5位数,小数点后面占了其中2位
float(5,2)
float 有效数字6/7位
double 有效数字15/16 位
#以字符串形式存储,适合货币等精度高的
decimal 有效数字 28/29 位
#2017-07-11 | 2017-07-11 15:10:40 | 15:10:40 | 2017
date/datetime/time/year
#2017-07-11 15:10:40(会转换成国家本地的时区,存储时期为1970-2038,不适合存储久远的时间,值为空的话会自动插入系统默认时间)
timestamp
#最大长度65534,但utf8编码每字占3字节所以utf8最大长度为21845
varchar
#最大长度255,会清除字符串末尾的空格
char
#和char和varchar类似,不过是存二进制的,binary在位数不够时会填充0(例如3位,插入a,等于a\0\0)
binary/varbinary
#表里只能插入a和b,插入别的会自动插入a,自动转换大小写,只能一次填写一个值例如b(a,b会插入失败)
enum('a','b')
#和enum一样,但是可以插入类似(a,b)这样的值,重复的值只取一次,例如(a,b,b,a)=(a,b)
set('a','b')
sql语句运算符
#查看数字的二进制(hex(a)是显示十六进制)
select bin(a) from ai;
#+ - * / % 算术运算符
select 4*6;
#= != < > <= >= 常用的比较运算符
select 4<6;
#不等于(不能比较null) <=>//等于(可以判断null) //特殊的比较运算符
<>
#约等于10>=5 and 10<=14
select 10 between 5 and 14;
#判断in后面的数组里有没有10
select 10 in (1,2,4,5,10)
#a是否为null
select a is null
#a里是不是没有null
select a is not null
#abc里是否含有字符串a(abc,%abcde% == 0)
select 'abc' like '%a%';
#和like%a% 类似
select 'abc' regexp 'ab';
#与,或,非,逻辑异或
and和&& or和|| not和! xor
#与 或 异或 取反(二进制取反) 位右移(二进制右移三位,左边补0) 位左移(和右移同理)
(位运算符就是转换成二进制再计算) & | ^ ~ >> <<
字符串拼接
#拼接字符串
select concat('as00','wfq')
#字符串替换,从第十个开始,4个字符串替换成abc
select insert('123456789987654321',10,4,'abc');
#转换成小写(upper是转换大写)
select lower('FGHJHB');
#返回字符串最左面的3个字符(right是右边)
select left('abcdefg',3);
#填充左侧字符串直到长度为7,等于abcd123(rpad是填充右面)
select lpad('123',7,'abc');
数据库清理碎片
optimize table table_name;
导入sql文件
source d:/name.sql;
索引与视图
#删除索引
drop index cssy on cs;
#查看索引情况
explain select * from cs where c = 'huaji' \G;
#创建索引
create index cssy on cs (c(5));
#查看表所有索引
show index from cs2;
#sql创建视图
create or replace view st as select cs2.a,cs2.b,cs2.c,cs3.w from cs2,cs3;