SQL语句笔记

SQL语句

一切符号均用英文,SQL语句不区分大小写

show databases; //显示数据库

use ly; //使用ly数据库，创建表之前要先使用

show tables; //显示某某数据库中的表

#创建表

create table student(
id int(10) auto_increment primary key,
name varchar(10) not null unique,
age int(3) default 18,
sex char(2),
cls varchar(10)
);

#表中必须定义主键，主键是一个表中的唯一标识

主键放在字段后，而外键就必须设置为一个字段

//设置主键

primary key

//设置外键

constraint id_fk foreign key (外键) references 主表名 (要关联的主键);

unique; //唯一

not null; //不为空

int(11)

varchar(255)

float

date

auto_increment; //自动递增，默认值为1，若字段没有设置自增就必须赋值

#改变编码格式 utf-8
set character_set_results = gbk;

#添加 编码格式 utf-8
set character_set_client = gbk;set character_set_connection = gbk;

表设计

以stu表为例

desc stu; //查看表的设计

//alter关键字用于操作表

alter table stu change sex sex varchar(2); //将sex字段的char(2)修改为varchar(2)

alter table stu change name sname varchar(12);//将字段name的名称修改为sname

alter table stu add phone int(12) unique;//添加字段phone长度为12，并且唯一

alter table stu drop phone; //删除字段phone

drop table stu;//删除表

delete from stu; //清空表

alter table stu modify sex varchar(2) after cls; // 将sex字段移到cls后面，sex后面的约束必须写

alter table stu rename stustu; // 将表stu命名为stustu

1. select name AS 中文名 from 表名 ;
2. 主要关键字是AS
3. AS 也可以省略，形式如下：
4. select name 别名 from 表名；

show full columns from stu;//查看已有表的所有字段注释

多表关联

create table teacher(
id int(10) primary key auto_increment comment '教师表主键',
name varchar(50) not null comment '教师姓名'
);

create table student(
id int(10) primary key auto_increment comment '学生表主键',
name varchar(50) not null comment '学生姓名',
age int(10) not null comment '学生年龄',
tid int(10) comment '教师表主键(外键)',
constraint fk_teacher foreign key (tid) references teacher(id)
);
teacher 是主键表
student 是外键表
primary key 是设置主键
auto_increment 自增
comment 注释
constraint fk_teacher 是给外键约束创建个名称
foreign key (tid) 是外键表student对应外键tid列
references teacher(id) 是主键表teacher对应的主键列id

数据处理

*添加数据

//添加单个数据

insert into stu(sname,sex,age,cls) values(‘李小红’,‘女’,21,‘1908A’);

//批量添加数据

insert into stu(sname,sex,age,cls) values(‘田七’,‘男’,29,‘1906A’),
(‘王五’,‘男’,21,‘1908A’),
(‘赵六’,‘男’,23,‘1908b’),
(‘小花’,‘女’,20,‘1907b’);

//添加单个字段

insert into stu(sname) values(‘小黑’)；//添加单个字段

#若不根据字段添加，必须按照表设计的字段顺序添加，不能多也不能少
insert into stu values(2,‘小白’,17,‘1608A’,‘女’);

*删除数据

#清空表数据

delete from stu;

#根据 id删除

delete from stu where id = 2;

#批量删除
delete from stu where id in(1,2,3);

*修改数据

#张三 改为 小张
update stu set sname=‘小张’ where sname=‘张三’;

*查询数据

//查询所有字段

select * from stu;

#查询 id,name
select id,sname from stu;

#查询年龄大于18的学生姓名 > < = >= <=
select sname from stu where age >18;

#查询不是王五的学生姓名 != <>
select sname from stu where sname !=‘王五’;
select sname from stu where sname <> ‘王五’;

#年龄在21-25之间的学生信息
select * from stu where age between 21 and 25;

#查询姓李的学生信息 %多个字符
select * from stu where sname like ‘李%’;

查询姓李X的学生信息 代表一个字符
select * from stu where sname like '李’;

第一个参数是下标 第二参数是条数 分页
select * from stu limit 3,3;

#查询姓小 并且年龄20的学生信息 and 两个条件必须成立
select * from stu where sname like ‘小%’ and age = 20;

#查询姓小 或者年龄20的学生信息 or 只有一个条件成立
select * from stu where sname like ‘小%’ or age = 21;

#查询年龄和李小白相同的学生信息
select * from stu where age = (select age from stu where sname = ‘李小白’) and sname <> ‘李小白’;

查询id是1,4,7,8的学生信息
select * from stu where id in(1,4,7,8);

#将学生的年龄从小-大排序 asc（小到大） desc(从大到小排序)
select * from stu order by age asc;

select * from stu order by age desc;

*其他

#order by 排序 group by 分组 都可以不用where
#where having 都是条件语句 having只能与group by 联合使用 不可单独使用 where可以单独使用

#聚合函数 count(字段) count(数字1) count()统计数量 max() min() avg() sum()
select count(id) as ‘计算’ from stu;
select count(1) ‘计算’ from stu;
select count() co from stu;

//获取最大值
select max(age) from stu;

//获取最小值

select sname,min(age) from stu;

#求平均值
select avg(age) from stu;

#求和
select sum(age) from stu;

#统计男生和女生的人数
select sex,count(id) from stu group by sex;

#查询男生的年龄大于23的人数
select sex,count(id) from stu where sex = ‘男’ and age > 23 group by sex ;

select sex,count(id) from stu group by sex having sex =‘男’ ;//男生的人数

select sex,count(id) from stu where sex =‘男’ group by sex having age > 23;(语法错误，having不识别age列 group by sex 条件having必须和sex的赋值有关)

select sex,count(id) from stu group by sex having sex =‘男’ and age=23 ;(语法错误，因为having是条件 后面不能在接其他内容，having只能与group by 联合使用)

//获取系统当前时间
select sysdate();

//获取系统当前时间
select now();

//获取系统年
select year(sysdate());

//获取系统月
select month(sysdate());

//获取系统日
select day(sysdate());

//获取表中的date字段的年
select year(表字段)

//获取表中的date字段的月
select month(表字段)

//获取表中的date字段的日
select day(表字段)

*多表查询

#查询学生信息 老师 以及学生分数 等值连接
select s.*,t.tname,sc.score from stu s,teacher t,sc where s.id = sc.sid and t.tid = sc.tid;

inner
#内连接
select s.*,t.tname,sc.score from stu s inner join sc on sc.sid = s.id inner join teacher t on t.tid = sc.tid;

#左连接 将左边表数据全部查出 其他获取交集
select s.*,t.tname,sc.score from stu s left join sc on sc.sid = s.id left join teacher t on t.tid = sc.tid;

#右连接 将右边表数据全部查出 其他获取交集

select s.*,t.tname,sc.score from stu s right join sc on sc.sid = s.id right join teacher t on t.tid = sc.tid;

*创建视图

在实际开发中，用户可以根据自己的需求，通过视图的方式，获取基本表中自己需要的数据，这样既能满足用户的需求，也不会破坏基本表原来的结构，从而保证了基本表中数据的安全性，视图就相当于子表

create database student;//先创建数据据库

create table stu;//在创建数据表

create view stu_view as select s.id,s.name,s.gread from stu s;//创建视图

create view stu_view (新的字段名) as select s.id,s.name,s.gread from stu;//创建视图

select * from stu_view;//使用视图