unitall mysql_两天学完MySQL的一些笔记，让建库到跑路不在只是个梗

sql笔记

重要提醒：每行语句结尾都要;

w3school学习网址http://www.w3school.com.cn/sql/index.asp

数据库安装我这就不介绍了，我用的是MySQL5.6版本。学习过程推荐按照w3c的教程过一遍，学习的时候多动脑子思考问题，其实MySQL也不过如此。

图片发自简书App

单词：Constraint 约束

primary 主要的

references 参考

alter 改变

duplicates 副本

Records 记录

affected 受影响的

Warnings 警告

truncate 缩短/清空

连接 musql -u root -p

创建

create database mypig;

删除

drop database mypig;

使用表

use pig;

显示所有数据库

show databases;

创建表

create table pig_tbl(

pig_id int not null auto_increment,

pig_name varchar(100) not null,

pig_age int not null,

pig_author varchar(50) not null,

create_time date,

primary key(pig_id)

);

create table layui(

id int not null auto_increment,

name varchar(10) not null,

primary key(id)

);

删除表

DROP TABLE layui;

插入数据

insert into pig_tbl

(pig_name,pig_age,pig_author,create_time)

values

("parper",26,"your",NOW());

查询数据

1.select pig_name,pig_id from pig_tbl where(pig_age>22);

2.select * from pig_tbl where pig_author="xiaoxi";

关键字 binary 区分大小写

select * from pig_tbl where binary pig_author="Xiaoxi";

更新数据

update pig_tbl set pig_name="top" where pig_id =4;

取唯一的值

select distinct pig_author from pig_tbl;

用多个作为唯一条件

select distinct pig_author,pig_id,pig_name from pig_tbl;

打包作者为一组

select pig_id,pig_name,pig_author from pig_tbl group by pig_author;

AND 运算符实例

select pig_id,pig_name from pig_tbl where pig_author='xiaoxi' and pig_id=5;

按年龄排序

select pig_name,pig_age from pig_tbl where(pig_age>=24) order by pig_age;

规定返回的记录的数目

select pig_name from pig_tbl limit 3;

模糊查询

select * from pig_tbl where pig_name like '%o%';

模糊使用not不包括

select * from pig_tbl where pig_name like '%o%';

使用 _ 通配符

select *  from pig_tbl where pig_name like 'p_r_e_';

WHERE子句中规定多个值，表中选取xx和xx的值：

select *  from pig_tbl where pig_name in ('book','parper');

BETWEEN ... AND... 取两个值之间的数据范围

select *  from pig_tbl where pig_age between 24 and 30;

not between取两个值之间的数据之外

select *  from pig_tbl where pig_age not between 24 and 30;

使用as 设置列别名

select pig_id as id,pig_name as name,pig_age as age  from pig_tbl where pig_age not between 24 and 30;

引用两个表，以id为主键关联

select pig_id, pig_tbl.pig_name,layui.name from pig_tbl,layui where pig_tbl.pig_id=layui.p_id;

使用别名查询

select p.pig_id, p.pig_name,l.name from pig_tbl as p,layui as l where p.pig_id=l.p_id;

使用inner join 内联接，id 倒叙

select p.pig_id as id, p.pig_name,l.name from pig_tbl as p inner join layui as l where p.pig_id=l.p_id order by id;

左联left join xxx on

select p.pig_id as id, p.pig_name,l.name from pig_tbl as p left join layui as l on p.pig_id=l.p_id order by id;

union合并多条select结果集 合并两个集合的name

select name from layui union select name from layui2;

UNION ALL 列出所有的值

select name from layui union all select name from layui2;

一个表中选取数据，然后把数据插入另一个表中

create table new_pig (select pig_id,pig_name from pig_tbl where(pig_age>25));

删库

drop database my_db;

SQl建表时添加的约束-------

NOT NULL 约束强制列不接受 NULL 值。

UNIQUE 约束唯一标识数据库表中的每条记录。

PRIMARY KEY 约束唯一标识数据库表中的每条记录。

添加UNIQUE 约束命名,以及为多个列定义 UNIQUE 约束

create table perfire(

id int(10) not null,

firsetName varchar(255),

lastName varchar(255),

city varchar(100),

address varchar(100),

constraint uc_persionId unique(id,firsetName)

);

添加 UNIQUE 约束

alter table layui2 add unique (id);

撤销 UNIQUE 约束

alter table layui2 drop unique (id);

如果已有主建会报错Multiple primary key defined，先删除再添加

添加主键约束

alter table perfire add constraint primary key (id,city);

删除主键约束

alter table perfire drop primary key;

FOREIGN KEY 外键约束

--外键必须是另一个参考表的主键

create table orders(

id_o int not null,

order_no int not null,

id_p int(10) not null,

primary key (id_o),

constraint fk_per foreign key (id_p)

references layui2(id)

);

删除foreign key外键约束

alter table orders drop foreign key fk_per;

添加外键

alter table orders add foreign key (id_o) references layui2(id);

设置default约束

alter table layui alter name set default 'liuzhou';

撤销default约束

alter table layui alter name drop default;

添加索引

create index p_sion on layui (p_id desc);

降序索引某个列中的值

create index p_sion_2 on layui (p_id,id);

多个用逗号隔开

撤销索引

alter table layui drop index p_sion;

仅删除表内的数据

truncate table lay2;

给表添加新列

alter table layui2 add birthday date;

alter table layui2 add passtest int(4) default '0';

修改列的数据类型

alter table layui2 modify column pt varchar(50) default 'beijing';

修改列的名字，数据类型

alter table layui2 change column pts city varchar(66);

删除表的某列

alter table layui2 drop column datree;

每次插入新记录时，自动地创建主键字段的值。

在建表时给主键添加字段--AUTO INCREMENT

CREATE TABLE Product(

P_Id int NOT NULL AUTO_INCREMENT,

City VARCHAR(255),

PRIMARY KEY (P_Id)

);

视图是基于 SQL 语句的结果集的可视化的表

create view view_pig as select * from pig_tbl;

日期时间

函数描述

NOW()返回当前的日期和时间

select now()；

CURDATE()返回当前的日期

select curdate();

CURTIME()返回当前的时间

select CURTIME();

DATE()提取日期或日期/时间表达式的日期部分

select date('2008-12-29 16:25:46.635');

EXTRACT()返回日期/时间按的单独部分

select extract(year from '2008-12-29 16:25:46.635');

DATE_ADD()给日期添加指定的时间间隔

select date_add('2019-04-01',interval 6 year);

DATE_SUB()从日期减去指定的时间间隔

select pig_id,pig_name,date_sub(create_time,interval 2 day) as date from pig_tbl;

DATEDIFF()返回两个日期之间的天数

select datediff('2019-04-01','2019-04-04');

DATE_FORMAT()用不同的格式显示日期/时间

select date_format(now(),'%Y-%m-%d %h:%i') as format;

来个日期用法查询集合

SELECT

now() AS now,

curtime() AS curtime,

curdate() AS curtime,

date_format(now(), '%Y-%m-%d %h:%i') AS format,

date_add('2019-04-01', INTERVAL 6 YEAR) AS date_add,

datediff('2019-04-01', '2019-04-04') AS datediff,

extract(

YEAR

FROM

'2008-12-29 16:25:46.635'

) AS extract,

date('2008-12-29 16:25:46.635') AS date,

pig_id,

pig_name,

date_sub(create_time, INTERVAL 2 DAY) AS date_sub

FROM

pig_tbl

LIMIT 1;

+---------------------+----------+------------+------------------+------------+----------+---------+------------+--------+-----------+------------+

| now                | curtime  | curtime    | format          | date_add  | datediff | extract | date      | pig_id | pig_name  | date_sub  |

+---------------------+----------+------------+------------------+------------+----------+---------+------------+--------+-----------+------------+

| 2019-04-04 14:10:33 | 14:10:33 | 2019-04-04 | 2019-04-04 02:10 | 2025-04-01 |      -3 |    2008 | 2008-12-29 |      1 | pig_green | 2019-03-30 |

+---------------------+----------+------------+------------------+------------+----------+---------+------------+--------+-----------+------------+

tyep参数可选值

MICROSECOND

SECOND

MINUTE

HOUR

DAY

WEEK

MONTH

QUARTER

YEAR

SECOND_MICROSECOND

MINUTE_MICROSECOND

MINUTE_SECOND

HOUR_MICROSECOND

HOUR_SECOND

HOUR_MINUTE

DAY_MICROSECOND

DAY_SECOND

DAY_MINUTE

DAY_HOUR

YEAR_MONTH

选取某列中带有 NULL 值的记录

select * from layui2 where pt is null;

IFNULL() 函数，如果为空，可以返回0方便计算

select p_id+(id+ifnull(passtest,0)) as sum from layui2;

coalesce的，作用是将返回传入的参数中第一个非null的值

select p_id+(id+coalesce(count,0)) as sum from layui2;

select coalesce (null,null,1);

函数

avg()平均值

select avg(p_id) as avg from layui2;

count() 返回指定列的数目

select count(*) from layui2;

max()返回该列最大值

select max(p_id) from layui2;

min()返回该列最小值

select min(p_id) from layui2;

sum()返回该列总数

select sum(p_id) from layui2;

GROUP BY 语句--根据一个或多个列对结果集进行分组

select pig_name ,sum(pig_age),pig_author from pig_tbl group by pig_author;

查找sum()年龄大于50 的作者

select pig_name,pig_author as author,sum(pig_age) from pig_tbl group by author having sum(pig_age)>50;

UCASE 函数把字段的值转换为大写

select ucase(name) from layui2;

LCASE 函数把字段的值转换为小写。

select lcase(name) from layui2;

MID 函数用于从文本字段中提取字符

select mid(name,1,3) as c_name from layui2;

length()返回该列值得字符长度

select length(name) as len from layui2;

ROUND 函数用于把数值字段舍入为指定的小数位数。

select name, round(p_id,1) as unit from layui2;