SQL语句大全

一:简介:

sql语句不分大小写(先复习学习再学习一定要多敲),sql一般用于增删改查(库,表,内容) 1:建表语句详细说明

2:foreign key外键的详细介绍

一:自己回忆sql语法

1:命令行进入数据库:mysql -u root -P 密码

2:数据类型和约束

数据类型:  

整数:int 小数:double 字符串:varchar 日期:data

数据约束:

主键约束:primary key 自动增长约束:auto_increment (一般给主键加) 唯一约束:unique 非空约束:not null

默认约束:default(默认值,为某一个字段设置默认值) 外键约束:foreign key 

3:数据库

①创建数据库语法:

create database wh_test1 ; create database if not exists wh_test2 ; (创建数据库如果wh_test2不存在就创建);

②:展示/查询当前连接的账户可见的数据库

show databases; 查看所有的数据库

show tables from mysql ; (查看指定数据库下面的全部表)

show create database wh_test1;(查看自己创建的库的格式)

③:删除数据库

drop database wh_test2; drop database if exists wh_test1;

④:修改数据库(一般库名不允许修改只能是删了重建)

alter database wh_test1 rename to wh_test ;目前此语法已不再支持,一般是重建库再次把数据导入

alter database wh_test1 modify name wh_test;

4:数据表

创建数据表语法: 创建表必须要有字段和类型,约束非必要条件

①:格式: create table 表名 (字段1 类型 约束,字段2 类型 约束,字段3 类型 约束,,,)

create table wh_student

(id int not null ,

name varchar(10) not null ,

age int not null,

hometown varchar(10) not null)

②:创建数据库表(类型和约束和备注的结合,comment是备注的意思)

create table wh_class

(id int(30) not null auto_increment comment '班级表id,整数,非空,自增长',

name varchar(30) not null comment '班级名,字符串,非空',

type varchar(10) not null comment '班级类型,字符串,非空',

nummer int(11) not null comment '班级号,整数,非空',

age double(10,2) not null comment '班级平均年龄,小数,非空',

create_time datetime comment '创建时间,日期,可以为空',

primary key (id) comment '单独给id添给主键,主键只能有一个',

unique (name,nummer) comment '添加唯一键,name和nummer这两个字段组合起来是唯一存在的')

ENGINE = INNODB auto_increment = 2 DEFAULT charset = utf8 comment '表名:班级表,最后设置默认引擎,从2开始自增,字符集是utf-8';

ENGINE=InnoDB不是默认就是这个引擎吗？

——是的，如果不写也是ok,就会走默认的，在这里写上是因为可以很清楚的看到这个建表语句用了哪些，而且在创建表的时候，写上也是一个很好的习惯

AUTO_INCREMENT=2,它不是自增的吗？为什么还要设数字？

——这个是自增的，在这里设置数字的意思是想要让这条语句在增长的时候，从2开始自增。

utf8不是已经在my.ini里设置过了?

——这个虽然在my.ini设置过了，但设置的是mysql的的语言编码，而这里创建的时候不设置，就会出现乱码问题，二者的作用域是不一样的，在创建表单的时候，这个charset会作用到这个表上，他代表mysql简历数据库数据表时设定字符集为utf-8

drop table if exists t_user;

CREATE TABLE t_user (

id int(11) NOT NULL AUTO_INCREMENT comment '用户id',

user_name varchar(30) DEFAULT NULL comment '用户名字',

age int(10) default 18 comment '年龄',

sex varchar(10) default null comment '性别',

PRIMARY KEY (id)

) engine =innodb auto_increment = 0 default charset= utf8mb4 ;

③修改数据库表语法:

alter table wh_student1 rename to wh_student666; 修改表的名字

alter table wh_student666 add (love varchar(100) not null );

alter table t_user add (teacher varchar(10) default "王豪");

④查找数据库表的语法:

show tables ; 展示所有的数据库表

desc wh_class; 展示创建表的结构

show create table wh_class ;

⑤删除数据库表的语法

drop table wh_student666 ;

truncate table wh123;

5:表内容的增删改查

①增

insert into wh_class (id,name,type,nummer,age,create_time) values(1 ,"1班", "尖子班", 30 ,16.1 ,"2023-05-05" )

insert into wh_class values (null,"4班","普通班",50,17.1,"2023-05-08 12:00:00"),(null,"5班","普通班",50,17.1,"2023-05-08 12:00:00"),(null,"6班","普通班",50,17.1,"2023-05-08 12:00:00"),(null,"7班","普通班",50,17.1,"2023-05-08 12:00:00");

delimiter $$ # 定义结束符

drop procedure if exists addTestData; # 存储过程名叫：addTestData

create procedure addTestData()

begin

declare number int;

set number = 1;

while number <= 100 #插入N条数据

do

insert into t_user(id,user_name)

values(null,concat('姓名_',number)); # 为了区分姓名，我们加上后缀

set number = number + 1;

end

while;

end $$;

call addTestData(); #调用存储过程,相当于上面写了一个方法,这里调用这个方法

②删

delete from wh_class where id = 4;

truncate table t_user ;

drop table t_user

③改

update t_user set age = 21 where id < 10 ;

update t_user set age = 15 where id > 90 ;

④查

select * from wh_class;

select * from t_user;

select distinct age from t_user; #去重

select count(*) from t_user where age = 18; #> < = !+ 这些是比较运算符

select avg(age) from t_user ; # 求平均年龄 avg:平均 count:全部 max:最大 min:最小 sum:求和

select sum(age) from t_user where age = 18 or age =19; #and or 都输入逻辑运算符

select count(*) from t_user where age in (18,19,20);

select distinct age from t_user order by age desc; #去重后再次分组

select age from t_user group by age ;

select * from t_user order by age desc limit 5;

单独整理一下索引内容:索引这一块自己务必需要搞好了.

增加索引(增加索引的目的是提高查询效率,增加约束的目的是保证数据的正确和唯一性)

①:增加索引: create index name_index on wh_student (name);

②:添加降序索引字段:create index name_index on wh_student(name desc);

③:给已有表的不止一个字段添加索引:create index wh_student_index on wh_student (name,sex); ④:添加唯一索引: create index default def_index on wh_student(name);

二:hm_note

1:概念

①:字符:计算机中文字,字母,符号等形式表示数据的集合

②:字符集:具有相同属性的字符的组合,例如中文,英文,数学符号

③:排序(编码)规则:将字符集转化为计算机可以识别的计算机进制的规则,规则有多种常用的utf8_general_ci

2:类型和约束

类型:

整数: int() 和 bigint() 全部展示为整数

小数: double(m,d) decimal(m,d) 对于数值位数很多的数字,要特别注意

字符串:varchar() 这个类型最全面

时间日期:datetime :包含年月日所有的时间

约束:

主键:primary key :主键一张表只能有一个主键,并且添加了主键会默认添加一个主键索引

外键:foreign key :外键主要用于表关联使用,和其余的表有连接的关系的时候一般会把字段添加为外键方便连接

#外键也是索引的一种,一般是主表和从表关联时使用,从表设置外键的字段在主表中是主键字段,

非空:not null :非空约束,表述这个字段不可以为空,必须有数值

自增:auto_increment :自增一般是整数类型的增长,一般和主键结合使用

默认:default :一般给一个字段有一些默认值

无符号:unsigned : 无符号约束,一般用于一些数据只能有正数不能为负数,像年龄等信息

二:库操作

1:数据库

①创建数据库

create database wh_hm_note charset utf8 collate utf8_general_ci; #charset:字符集 collate:核对

create database wh_1 charset utf8mb4 collate utf8mb4_general_ci;

create database WH charset utf8 collate utf8_general_ci;

②:使用数据库

use wh_hm_note;

③:修改数据库

alter database wh_hm_note charset utf8mb4 collate utf8mb4_general_ci ;

④:删除数据库

drop database wh;

drop database WH;

⑤:查看当前的库

show databases;

select database(); #查看当前使用的库

show create database wh_test1; #查看创建库的语句

select @@version 和 select version() #一样都是数据库版本的语句

三:数据表的操作

1:数据表

注意事项:符号英文,字段和字段之间用逗号连接,字段的类型必须有约束可选

①:创建表

CREATE TABLE wh_hm_student (

id INT ( 10 ) auto_increment COMMENT '默认id',

NAME VARCHAR ( 10 ) NOT NULL COMMENT '学生姓名',

sex VARCHAR ( 3 ) NOT NULL COMMENT '性别',

class VARCHAR ( 5 ) DEFAULT '尖子班' COMMENT '班级',

student_number INT ( 10 ) COMMENT '学号',

height DECIMAL ( 5, 2 ) NOT NULL COMMENT '身高',

PRIMARY KEY ( id ))

ENGINE = INNODB auto_increment = 0 DEFAULT charset = utf8 COMMENT '学生信息表';

创建eg:创建一个学生表，学号是整型无符号类型，学号有主键约束，并且是自增长的,姓名长度为10, 年龄整型无符号身高保留两位有效数字，并且长度为5

create table student_new

( student_id int(10) unsigned primary key auto_increment comment '学号',

name varchar(10) default null comment'姓名',

age int(10) unsigned not null comment'年龄', #unsigned是符号,标志的意思 因为年龄不能为负数,所以通常加这个约束

height decimal(5,2) not null comment '身高')

engine = innodb auto_increment = 1 default charset = utf8 comment '学生表';

#接下来的语句全部是介绍外键使用的(创建表时直接加外键)

#1创建主表

create table student1

( id int(10) auto_increment comment '学生表id',

name varchar(10) not null comment '学生表学生姓名',

age int(10) not null comment '学生表年龄',

primary key (id) )

engine = innodb auto_increment = 1 default charset = utf8 comment '学生表';

#2创建从表(创建表的时候就建立好了外键)

create table student_score

(id int(10) auto_increment comment '学生成绩表id',

class varchar(10) default null comment '学生成绩表班级',

subject varchar(10) not null comment '学生成绩表科目',

student_id int(10) default null comment '学生成绩表和学生表的关联字段,稍后用作外键',

student_name varchar(10) default null comment '学生成绩表的名字和学生表的名字相关联',

primary key (id),

key s_id (student_id) comment '创建外键字段,起个外键名字(具体用于外键的字段),下面用于连接',

constraint s_id foreign key ( student_id) references student (id) ) #'从表的外键和主表的主键建立联系'

engine = innodb auto_increment = 1 default charset = utf8 comment '学生成绩表'

# 给已经创建的表增加外键

#创建主表

create table a1

(id int(10) primary key auto_increment comment '员工表的id',

name varchar(10) not null comment '员工表名字',

hometown varchar(10) not null comment '员工表的家乡')

engine = innodb auto_increment = 1 default charset = utf8;

#创建从表

create table b1

(id1 int(10) primary key auto_increment comment '表的id',

name1 varchar(10) not null comment '表名字',

hometown1 varchar(10) not null comment '表的家乡')

engine = innodb auto_increment = 1 default charset = utf8;

#给从表增加外键

alter table b1 add constraint idd foreign key (id1) references a1 (id); #修改表增加外键约束

②:删除表

drop table wh_hm_student ;

drop table if exists student_new ; # 这种语法的删除就是表不存在也不会报错,但是个人感觉不太好,不报错感觉没删成功

③:查看当前所有的表

show tables; #查看当前的所有表

desc students; #展示数据表的结构

show create table students ; #展示创建某个表的语句

④:修改表

alter table a1 add age int(10) not null;

alter table a1 change name name1 varchar(11) not null ; #修改表的字段,把name的名字改成name1

alter table student add love1 varchar(10) default null ;

alter table student drop love1;

四:数据操作

①:增加数据 备注:先知道表结构能更好的去插入数据

insert into student values (null,'美猴王1',1000) ,(null ,'猪八戒1',900),(null,'唐僧',90); #给所有字段加数据,字段的类型和约束用,字段之间也用,自增长的字段可以用0,或null来代替

insert into student ( name,age,height) values ('白骨精',999,160),('牛魔王',999,180.01)

②:修改数据

update student set age = 9999 # 这个是修改age这个字段的这一列所有的数据全部是9999

update student set age = 9999 where name = '美猴王1'; # 修改某一个数据为特定值

update student set age = 6666 , love = '泡妞' where name = '猪八戒';

③:删除数据

drop table student_copy1;#删除表,删除所有内容和表结构

truncate table student_copy1 ; # 删除数据保留表的结构,要删就只能删除全部数据不能只删除单行数据

delete from student where name = '猪八戒1';

④:查找数据

select * from student ; #查找表中全部数据

select name,age,love from student where name = '孙悟空';

select * from students;

五:数据库扩展语法

①:起别名:as

select * from student as st ; #给表起别名,as可以省略不写

select name n1 ,age a1 from student s1 where name = '孙悟空'; # 给表和字段都添加别名,as省略不写

②:数据去重:distinct

select distinct age from student ; # select distinct 字段 from 表名 : 将字段去重后展示出来

select count(distinct age) from student ; # 展示某个字段的数据去重后总共有多少个 ,聚合函数还可以用sum,avg,max,min

③:条件查询:where (where是对原始数据的筛选,所以where后不可带聚合函数)

select * from students where age > 20; #比较运算符 > >= < <= != <>

select * from students where age > 20 and class = '1班'; #逻辑运算符 and并 or或 not非

select * from students where name like "王%" ; #模糊查询like

select * from students where age between 15 and 25 and class in ("1班","2班"); #范围查询 between and 连续范围 ; in 非连续范围

select * from students where card is null or card = ''; # 空判断 is null 内容为空,直接没填写 ; "" 空字符串,填写了,填写内容就是空或空格

④:排序:order by (升序 asc:可以不写 ;降序 desc )

select * from students order by age desc, class ; #按照多个字段排序,用,隔开并且在字段后面填写上规则

⑤:聚合函数:count(),max(),min(),sum(),avg()

select count(age) from students ;

⑥:分组查询;group by

select 字段1,count(*) from 表 group by 字段1 having 字段1,聚合函数 # select 后面只能是分组的普通字段或者是聚合函数,having 后面也是普通字段或者是聚合函数,并且会按照字段的顺序排序

-- eg1:查询各种性别的人数

select sex,count(*) from students group by sex ;

-- eg2:查询每个班级中各种性别的人

select class,sex,count(*) from students group by class,sex;

-- eg3:查询每个班级中各种性别的人数,并按照班级降序排列

select class,sex,count(*) from students group by class,sex order by class desc;

-- eg4:查询男生总人数（两种方式实现)

select sex,count(*) from students where sex = '男'; select sex,count(*) from students group by sex having sex != "女";

-- eg5:查询班级平均年龄大于22岁的班级有哪些和班级的总人数

select class,count(*) from students group by class having avg(age) > 22;

-- eg6:查询每个班级中女生的平均年龄大于22岁的班级有哪些

select class,sex from students group by class,sex having sex = '女' and avg(age) > 22;

⑦:分页查询:limit

select * from 表 limit start,count; start:开始的下标,下标从0开始, count:总共统计多少个数据

-- eg2：查询第4到第6行学生信息

select * from students limit 3,3;

-- eg3：查询学生信息表中年龄最大的前三名学生信息

select * from students order by age desc limit 3;

⑧:连接查询: inner(内连接) ; left (左连接) ; right (右连接)

select s1.studentno,s1.name,s1.age,s1.class,s2.score from students s1 left join scores s2 on s1.studentNo = s2.studentNo ;

-- 例4：查询王昭君的成绩，要求显示姓名、课程号、成绩

select s1.name,s2.courseNo,s2.score from students s1 inner join scores s2 on s1.studentNo = s2.studentno where s1.name = "王昭君";

-- 例5：查询王昭君的数据库成绩，要求显示姓名、课程名、成绩

select s1.name,c1.name,s2.score from students s1 inner join scores s2 on s1.studentNo = s2.studentno inner join courses c1 on s2.courseNo = c1.courseNo where s1.name = "王昭君" and c1.name = "数据库" ;

-- 例7：查询男生中最高的成绩，要求显示姓名、课程名、成绩

select s1.name,c1.name,s2.score from students s1 left join scores s2 on s1.studentNo = s2.studentno left join courses c1 on s2.courseNo = c1.courseNo where s1.sex = "男" order by s2.score desc limit 1;

-- 例8: 查询所有学生的成绩，包括没有成绩的学生，需要显示课程名

select s1.name,c1.name,s2.score from students s1 left join scores s2 on s1.studentNo = s2.studentno left join courses c1 on s2.courseNo = c1.courseNo order by s2.score desc;

六:复杂查询

select * from areas ;

-- ：查询河南省所有的市

select * from areas a1 inner join areas a2 on a1.pid = a2.aid where a2.atitle = '河南省';

-- ：查询郑州市的所有的区县

select * from areas a1 inner join areas a2 on a1.pid = a2.aid where a2.atitle = '郑州市';

-- ：查询河南省的所有的市和区

select a1.atitle,a2.atitle,a3.atitle from areas a1 inner join areas a2 on a1.aid = a2.pid left join areas a3 on a3.pid = a2.aid where a1.atitle = '河南省' ;

七:子查询

①:一行一列

-- 例1：查询大于平均年龄的学生

select * from students where age > (select avg(age) from students) ;

-- 例2：查询王昭君的成绩，要求显示成绩

select score from scores where studentno = (select studentno from students where name = "王昭君");

②:一行多列

-- 例3：查询和王昭君同班、同龄的学生信息

select * from students where class =(select class from students where name = "王昭君") and age =(select age from students where name = "王昭君") ;

③:多行一列

-- 例4：查询18岁的学生的成绩，要求显示成绩

select * from scores where studentno in (select studentNo from students where age > 18 );

-- 例5：查询数据库和系统测试的课程成绩

select courseNo from courses where name in ("数据库" , "系统测试");

select * from scores where courseNo in (select courseNo from courses where name in ("数据库" , "系统测试"));