mysql基础sql语法

说明

在本篇教程中使用以下命名

库名: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;