[SQL] 常用SQL语句

数据库

连接数据库

mysql -h 10.20.66.32 -u root -p 123456

-h 后面是mysqlServer所在地址，-u后面是用户名，-p后面是密码

查看数据库/表

show databases;
show tables;

使用数据库

use test;

查看表结构

desc winton

建表

-- 语法 
create table 表名称( 字段名 字段名类型 字段描述符，字段名 字段类型 字段描述符);

--下面为用例
create table t1(
    id int not null primary key, 
    name char(20) not null
);

删除表

drop table test;

修改表

添加字段

-- 语法 
alter table 表名 add(字段名 类型 描述符)；

--下面为用例
alter table t1 add(score int not null);

移除字段

-- 语法 
alter table 表名 drop colunm 字段名，drop colunm 字段名；

--下面为用例
alter table t1 drop column score;

变更字段

-- 语法 
-- 全部修改
alter table 表名 change 旧字段名 新字段名 新字段类型 新字段描述符
-- 只修改类型和约束 不修改名称
alter table 表名 modify 列名 类型及约束;

--下面为用例
alter table t1 change name score int not null;

全字段插入

insert into winton values(001,'zww'),(002,'rs');

即插入一整条数据可以一次插入多行

个别字段插入

insert into winton(id，name) values(004，“zs”)，(005，“ls”);

即只插入该条数据的一部分字段可以一次插入多行

值的顺序与给出的列顺序对应

查询表

单表查询

-- 语法 
select 字段一,字段二 from 表名;

--下面为用例
select * from t1;
select id from t1;

多表查询

-- 语法 
select 表一字段,表二字段,表三字段,…… from 表一, 表二, 表三, ……；

--下面为用例
select t1.id,t1.score,winton.name from t1, winton;

条件查询

-- 语法 
select 表一字段，表二字段 from 表一，表二 where 条件；

--下面为用例
select * from t1 where socre>90;
select t1.id,t1.score,winton.name from t1,winton where t1.id=winton.id;

嵌套查询

-- 语法 
select 字段一,字段二…… from 表名 where 条件(查询);

--下面为用例
select name from winton where id=(select id from t1 where score=90);

并查询

(select id from t1 )union(select id from winton);

即查询结果取并集

交查询

select id from t1 where id in (select id from winton);

即查询结果取交集

模糊查询

-- 查询姓黄的学生
select * from students where name like ‘黄%’;

-- 查询姓黄并且“名”是一个字的学生
select * from students where name like ‘黄_’;

-- 查询姓黄或叫靖的学生
select * from students where name like ‘黄%’ or name like ‘%靖’;

%表示任意多个任意字符 _表示一个任意字符

注：如果数据是字符型，必须使用单引号或双引号

范围查询

非连续范围查询

select * from students where id in(1,3,8);

查询编号是1或3或8的学生

in表示在一个非连续的范围内

连续范围查询

select * from students where id between 3 and 8 and gender=1;

查询编号为3至8的男生

between … and … 表示在一个连续的范围内

连接查询

内连接

select * from students inner join classes on students.cls_id = classes.id;

内连接是等值连接，它使用“=、>、<、<>”等运算符根据每个表共有的列的值匹配的行

外连接

外连接查询中的outer可省略

左连接(left join 或 left outer join)

select * from students as s left join classes as c on s.cls_id = c.id;

左连接又称左向外连接，查询的结果集包括SQL语句中左表的所有行，以及右表中匹配的行。

如果左表的某行在右表中没有匹配行，则用空值表示

右连接(right join 或 right outer join)

select * from students as s right join classes as c on s.cls_id = c.id;

右连接也称右向外连接，查询的结果集包括SQL语句中右表的所有行，以及左表中匹配的行。

如果右表的某行在左表中没有匹配的行，则用空值表示

完全外连接(full join 或 full outer join)

select * from students as s full join classes as c on s.cls_id = c.id;

此处使用了as为表起别名，目的是编写简单

完全外连接，查询的结果集包括SQL语句中左表和右表的所有行。

如果某行在另一个表中没有匹配行时，则用空值表示。

交叉连接(cross join)

SELECT <字段名> FROM <表1> CROSS JOIN <表2> [WHERE子句]

-- 或者
SELECT <字段名> FROM <表1>, <表2> [WHERE子句]

返回结果就是两个表的笛卡尔积，即两边依次匹配行连接在一起

返回结果数量就是两个表的数据行相乘。

需要注意的是，如果每个表有 1000 行，那么返回结果的数量就有 1000×1000 = 1000000 行，数据量是非常巨大的。

如果在交叉连接时使用 WHERE 子句，MySQL 会先生成两个表的笛卡尔积，然后再选择满足 WHERE 条件的记录。

因此，表的数量较多时，交叉连接会非常非常慢。一般情况下不建议使用交叉连接。

总结

select * from 表1 inner/left/right join 表2 on 表1.列 运算符 表2.列;

删除数据

-- 语法 
delete from 表名 where 条件；

--下面为用例
delete from winton where id=4;

更新数据

-- 语法 
update 表名 set 更改的字段名=值 where 条件；

--下面为用例
update t1 set score=69 where id=2;

求和

select sum(score) from t1;

注：sum(字段) 对字符串和时间无效

求平均值

select avg(score) from t1;

注：avg(字段)对字符串和时间无效

计数

select count(*) from t1;

表示计算总行数

注：count(字段名)不包含NULL；

COUNT(常量) 和 COUNT(*) 表示的是直接查询符合条件的数据库表的行数。

而COUNT(列名)表示的是查询符合条件的列的值不为NULL的行数。

COUNT(*)是SQL92定义的标准统计行数的语法，因为是标准语法，所以MySQL数据库进行过很多优化。

如果表多个列并且没有主键，则 count(1) 的执行效率优于 count(*)
否则基本一致

常用的修饰符

distinct 字段中值唯一
```
select distinct name from winton;
```
limit查询结果数限制
```
select * from winton limit 2;
```
limit 5，5 第一个数表示开始位置，第二个数表示检索行数

order by 排序

select * from winton order by name;
slelect * from winton order by name desc;
select * from winton order by name asc;

注：默认是升序 desc 降序 asc 升序

group by 分组
```
select name from winton group by name;
```

创建索引

创建普通索引

-- 语法 
create index 索引名称 on 表名 (字段一，字段二，……);

--下面为用例
create index wintonIndex on winton (name);

创建唯一索引

-- 语法 
create unique index 索引名 on 表名 (字段一,字段二,……)；

--下面为用例
create unique index wintonIndex on winton (id);

注意unique index 要求列中数据唯一，不能出现重复。

创建前缀索引

-- 在email列创建前缀索引 按前三个字符建立索引
CREATE INDEX idx_author_email ON author(email(3));

创建全文索引

create fulltext index content_tag_fulltext on fulltext_test(content,tag);

移除索引

-- 语法 
drop index 索引名 on 表名；

--下面为用例
drop index wintonIndex on winton;

建表时创建索引

KEY | INDEX [<索引名>] [<索引类型>] (<列名>,…)

在 CREATE TABLE 语句中添加此语句，表示在创建新表的同时创建该表的索引。

UNIQUE [ INDEX | KEY] [<索引名>] [<索引类型>] (<列名>,…)

在 CREATE TABLE 语句中添加此语句，表示在创建新表的同时创建该表的唯一性索引。

FOREIGN KEY <索引名> <列名>

在 CREATE TABLE 语句中添加此语句，表示在创建新表的同时创建该表的外键。

视图

创建视图

-- 语法 
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

--下面为用例
create or replace view v_student as 
select Sno,Sname
from student;

删除视图

drop view v_student;

面试问题

创建字段时设置属性的区别

1、`int(N)`

int(N) ，N 表示最大显示宽度(字段设置zerofill属性时可查看区别)，不指定N时，默认为11。N 的值跟 int(N) 所占多少存储空间或最大能存储多大长度数据并无任何关系。也就是int(3)、int(4)、int(8) 在磁盘上都是占用 4 btyes 的存储空间。

当 int 字段类型设置为无符号且填充零(UNSIGNED ZEROFILL)时，当数值位数未达到设置的显示宽度时，会在数值前面补充零直到满足设定的显示宽度，为什么会有无符号的限制呢，是因为 ZEROFILL 属性会隐式地将数值转为无符号型，因此不能存储负的数值。

只有给字段加了zerofill参数后，如果实际插入的数据宽度小于字段设置的显示宽度N时，将自动在数字前填充0。同时，如果插入的数据宽度大于设置的显示宽度N时，不会出现数据无法插入或数据被截断的情况。

create table tb2 (id1 int(3) zerofill)

2、char(N)与 varchar(N)

char(N)和varchar(N)最大的区别在于存储需求空间上的区别上，前者为固定长度，存储需求为N字符长度。后者为可变长度(“var”意为“variable”，可变的)，存储需求为实际值的长度+1(如果N>255，则+2)。其中N，不管针对char还是varcahr均有最大字符长度不允许超过N的意思。

char(N)的N取值范围为255。
varchar(N)的N取值范围为65535，超过了自动转换为text类型。

3、`varchar(50)`和`varchar(100)`是否一样？

硬盘存储需求一样，内存需求不一样。

即对于VARCHAR数据类型来说，硬盘上的存储空间是根据实际字符长度来分配存储空间的

而对于内存来说，是使用N个字符空间的内存块来保存值，即varchar(50)占用50个字符内存空间，

varchar(100)占用100个字符内存空间。

数据库