sql---数据库基本操作，增删改查（学习笔记）

命令行启动mysql

mysql -uroot -p123456  --连接数据库（用户名root 密码123456）

创建数据库

  -- mysql关键字不区分大小写,表名或者字段名可以用``，但是values内容必须是''

CREATE DATABASE IF NOT EXISTS westos;

删除数据库westos

DROP DATABASE IF EXISTS westos;

查看数据库

SHOW DATABASES;

使用数据库

-- 数据库名或者字段名是特殊字符，必须用``包起来
USE `school`; 
-- 我使用的数据库管理工具是navicat premium，执行该语句好像不生效，需要手动选择数据库，具体原因我也不清楚

创建表

create table `student`(
    `id` int(4) not null comment '学号',
    `name` varchar(20) not null default '匿名' comment '姓名',
    `pwd` varchar(20) not null default '123456' comment '密码',
    `sex` varchar(2) not null default '女' comment '性别',
    `birthady` datetime default null comment '生日',
    `address` varchar(20) default null comment '地址',
    primary key(`id`) 
)
-- `id`是tab上面那个`，comment '学号'是单引号

查看创建语句

show create database school  -- 查看创建数据库school语句
show create table student -- 查看创建表student语句
desc student  -- 显示表结构

修改和删除表

修改

alter table student rename as stu  -- 修改表名
alter table stu add age int(3)  -- 增加字段
alter table stu modify age varchar(12) -- 修改字段约束
alter table stu change age age1 int(1)  -- 重命名

删除

alter table stu drop age1  -- 删除字段
drop table if exists stu  -- 删除表

插入

insert into `stu` (`id`,`name`,`sex`,`pwd`) values ('3','张三','男','123456') -- 插入数据
insert into `stu` (`name`) values ('张三'),('李四')    -- 插入多条数据

修改数据

update `stu` set `name`='mike' where `name`='张三'  -- 修改表stu里面name='张三'的数据

删除数据

delete from `表名` where [条件]

truncate 完全清空一个数据库表,表的约束和索引不会变

truncate table `表名`

• delete和truncate的区别

  • 相同点:都能删除表的数据

  • 不同点:truncate会重新自增列和计数器归零

  • truncate不会影响事物

查询

查询

查询带有一个或者多个条件时，返回的结果是布尔值

select `字段` from `表名`

别名，函数concat(a,b)

select `字段` as 学号 from `表名`
select CONCAT('姓名:',name) as 新名字 from `stu`

去重distinct

select distinct `id` from `stu`

运算符	语法	描述
and &&	a and b a && b	逻辑与
or ||	a or b a || b	逻辑或
not !	not a ! a	逻辑非

select * from `stu` where `id`=1 && `name`='mike'
-- 模糊查询
select * from `stu` where id between 1 and 10

模糊查询

运算符	语法	描述
is null	a is null	如果操作符为null，结果为真
is not null	a is not null	如果操作符为不为null，结果为真
between	a between a and b	若在a和b之间结果为真
like	a like b	sql匹配,如果a匹配b，结果为真
in	a in (a1,a2...)	假设a在a1或者在a2...中的某个值，结果为真

-- like查询姓刘的同学
-- like结合，%（表示0到任意字符），_（表示一个字符）
select name from `stu` where `name` like '刘%'
​
-- like查询姓刘的同学,后面只带一个字
select name from `stu` where `name` like '刘_'
​
-- like查询姓刘的同学,后面只带两个字
select name from `stu` where `name` like '刘__'
​
-- like查询名字中间带'嘉'的同学,‘%嘉%’
select name from `stu` where `name` like '%嘉%'
​
-- in(具体的一个或者多个值)
-- 查询学号为1001，1002，1003的同学
select `name`,`id` from `stu` where `id` in (1001,1002,1003)
​
-- 查询在广州的同学
select `name`,`id` from `stu` where `address` in ('广州')
​
--查询地址为null的同学
select `name`,`id` from `stu` where `address` is null or `address`=''

联表查询

join on 连接查询,字段加了``，就必须要用join on

inner join两表的共同部分，left join左表，right join 右表

where 等值查询

-- inner join
select a.stunum,stuname 
from stu as a 
inner join sturesult as b
on a.stunum=b.stunum
-- left join 
select a.stunum,stuname 
from stu as a 
left join sturesult as b
on a.stunum=b.stunum
-- 联合多表查询
select a.stunum,stuname,stuid 
from stu as a 
left join sturesult as b
on a.stunum=b.stunum
inner join perstu as c
on b.stuid=c.stuid

自连接

sql里面有一个表

categoyardId	pid	categoyardName
3	1	软件开发
5	1	美术设计
4	3	数据库
8	2	办公信息
2	1	信息技术
6	3	web开发
7	5	ps技术

父表

categoyardid	categoyardName
3	软件开发
5	美术设计
2	信息技术

子表

pid	categoyardName
3	数据库
2	办公信息
3	web开发
5	ps技术

查询父类对应子类关系

父类	子类
软件开发	数据库、web开发
美术设计	ps技术
信息技术	办公信息

select a.categoyardName,b.categoyardName 
from categoyard as a,categoyard as b
where a.`categoyard`=b.`pid`