mysql基本语句

MySQL 是一个关系型数据库管理系统

MySQL 是一种关联数据库管理系统，关联数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。

一、增删改查

1、增：插入数据

创建数据库:create database if not exists HA;    	                      
创建表:create table student(id int(20),name char(40),age int);
插入数据(insert):insert into student values(1,'zs',20),(2,'lisi',21);   
插入指定列数据：insert into student(id,name) values(3,'ww'),('4','zl');  
插入字段(alter):alter table students add uid int(20) after age;	
enum：alter table students add sex enum('M','W');     只能写一个参数
set：create table kdata17 (type set('a','b','c','d','f'));   可以写多个，不能重复

2、删：删除数据

删除数据库(drop):drop database HA;
删除数据库下的表(drop):drop database if exists `HA-test`;		
删除表(drop):drop table student;
删除表中数据(delete):delete from student where id=2;					
删除字段(alter):alter table student drop uid;		        
删除自动增长内容:truncate table items;		

3、改：修改数据

更改内容(update):update student set age='20' where id=2;   
更改字段(alter):alter table student change name stname char(20);			
更改表名:alter table aaa rename bbb;
添加一列每周的字段:update incident_issues set cx_zhou=date_part('week',created_at)  
select date_part('week',created_at),created_at	from incident_issues ORDER BY created_at desc;  

4、查：查询数据

(1)查看表结构:desc  student;   	        
查看创建执行命令:show create table student\G		        
查看当前所在数据库:select database();	 	
查看两列:select stname,age from student;	                 
select * from incident_issues where labels in ('AD','WD','GVP')
select * from incident_issues where iid is not null and labels in ('AD','WD','GVP')
(2)去除重复查询(distinct):select distinct name,age from student;
或和(where):select id,name,age from student where id>2 or age>25;or/and
区分大小写(binary):select * from student where binary name='kill';
升序降序(order by):select id,name from student order by id desc,name desc;   asc/desc
模糊查询(like):select * from student where age like '23';	      
select * from student where name (not) like '%i%';	
(3)查询(limit)：select * from table limit m,n;语句**
m表示从哪行开始  n是指从第m+1条开始,取n条记录     
查看1-3行：select bName,price from books limit 0,3;
(4)嵌套查询：比较占空间
select bName,bTypeId from books where bTypeId=(select bTypeId from category where bTypeName='网络技术');
(5)多表连接查询
内连接：select a.bname,a.price,b.btypename from books a inner join category b on a.btypeid=b.btypeid;
       根据表中的共同字段进行匹配,最常用
左连接：select a.bname,a.price,b.btypename from books a left join category b on a.btypeid=b.btypeid;
       a是主表，指定的内容都显示。b是从表，没有的内容显示null。
右连接：select a.bname,b.* from books a right join category b on a.btypeid=b.btypeid;
       b是主表，指定的内容都显示。a是从表，没有的内容显示null。 
多表连接：select article.aid,article.title,user.username,type.typename from article INNER JOIN user ON article.uid=user.uid INNER JOIN type ON article.tid=type.tid;
       多表连接就是在原有两个表连接语句上继续添加字段和条件
多字段条件查询：select * from rawdata.ad where day=20201106 and profile_id=383 and query_string like '%ck=2%'

二、字符类型

1、数值类型

tinyint 有符号(-128,127) 无符号(0,255)->小整数型;占1字节空间
int 有符号(-2147483648,2147483647 无符号(0,4294967295);占4字节
float 单精度浮点类型;占四字节 double 双精度浮点类型;占8字节
数值类型：默认都为有符号类型,要变无符号需加修饰符
unsigned保存正数:10 ； zerofill 剩下的空值补0:00000000010
create table aaa (fiu int unsigned,fiuz int [unsigned] zerofill);

2、日期和时间

date 日期值 2019-12-22;占3字节
time 时间值 22:30:44;占3字节
year 年份值 2019;占1字节
datetime 混合日期和时间值 2019-12-22 20:30:00;占8字节

3、字符串类型

char 0-255 定长字符串 char(10)不管写几个字符都占10字节,右边没有占满的用空格填充。中文字符占2字节,英文字符占1字节
varchar 0-65535 可变长字符串 由于开头和结尾占去3个字节,还有65532个字节。每个字符(中文/英文)都占两个字节,每个字节都要+1，所以一个字就是占3个字节，+1是为了保存实际占用了多少字节
查询速度快用(效率高)char 省空间用varchar

三、外键约束

foreign key：指定从表字段
references：指定主表字段

创建外键：

create table `user`(id int(11) not null auto_increment, name
varchar(50) not null, sex int(1) not null, primary key(id));
create table `order`(o_id int(11) auto_increment, u_id int(11), username varchar(50), money int(11), primary key(o_id), index(u_id), foreign key [order_f_key] (u_id) references user(id) on delete cascade on update cascade);

添加外键：

create table `user1`(id int(11) not null auto_increment, name
varchar(50) not null, sex int(1) not null, primary key(id));
create table order1(o_id int(11) auto_increment, u_id int(11) default ‘0’, username varchar(50), money int(11), primary key(o_id), index(u_id)) ENGINE=innodb;
如果两个表都创建好了,但在创的时候并没有添加指定外键,那么可以通过这种方式添加外键：
alter table order1 add foreign key(u_id) references user1(id) on delete cascade on update cascade, ENGINE =innodb;

连级更新和连级删除（更新删除主键，从键只能跟随主进行修改）

update user set id=6 where id=2;
delete from user where id=1;

删除外键：

alter table order1 drop foreign key bk;

外键特点：

1.必须使用ENGINE指定存储引擎为：innodb.
2.外键字段和关联字段，数据类型必须一致。
3.on delete cascade on update cascade 添加级联删除和更新

四、视图

使数据库看起来结构简单清晰,减小查询语句的复杂程度
创建内连接视图：create view bc as select a.bname,a.price,b.btypename
from books a inner join category b on a.btypeid=b.btypeid;
更改为左连接视图：alter view bc as select a.bname,b.btypename
from books left join category b on a.btypeid=b.btypeid;
删除视图：drop view bc;

五、运算/函数

1、运算符

逻辑运算符：and or not
算术运算符：=、<>、>、<、>=、<=
select bName,price from books where price>60;
select bName,price from books where price<>60;
select bName,price from books where price in (50,60,70);
查找不在30到60之间的书名和价格：select bName,price from books
where price not between 30 and 60;
或者：select bName,price from books where price<30 or price>60；

2、算数运算+、-、*、/

给所有价格小于40元的书籍，涨价5元：
update books set price=price+5 where price<40;
给所有价格高于70元的书籍打8折：
update books set price=price0.8 where price>70;

3、算术运算函数

sum()求和：select sum(price) from books;
avg()平均值：select avg(price) from books where bId<=3;
max()最大值：select bName,price from books where
price=(select max(price) from books);
min()最小值：select bName,price from books where
price=(select min(price) from books);
count()统计行数：select count(*) from books where price>40;

总结3：

别名:select sum(price) as 图书总价 from books;
去重:select count(distinct price) from books where price>40;
分组查询(group by)：
男女各多少人select sex,count() from student group by sex;
查看各id有多少人:select id,count(*) from student group by id;
判断性别平均年龄:select sex,avg(age) from student group by sex;