MYSQL基本操作

-- SQL : Structured Query Language (结构化查询语言)
-- 1.DDL： Data Definition Language - 数据库定义语言 - create/drop/alter
-- 2.DML: Data Manipulation Language - 数据操作语言 - insert/delete/update/delete
-- 3.DCL Language -数据控制语言 - grant/revoke/commit/rollback

如果存在名为school的数据就删除

drop database if exists school;

创建school数据库悲切设置默认字符集为utf8

create database school default charset utf8

切换到school 数据库上下文环境

use school

如果存在tb_student就删除

drop table if exists tb_student

创建学生表

# comment 注释
create table tb_student
(
stuid int not null comment '学号',
stuname varchar (20) not null comment '姓名',
stusex enum('男', '女') default '男' comment '性别',
stubirth date comment '出生日期',
stuaddr varchar(255) comment '家庭住址',
primary key (stuid)
);

result

修改tb_student表的结构 添加一个新的列

alter table tb_student add column stutel char(11) comment'联系电话';

修改tb_student表的结构 删除一个列

alter table tb_student drop column stutel;

修改tb_student表的表结构 修改一个列

alter table tb_student change column stuname stuname vachar(31)
not null comment '姓名';

删除课程表tb_course如果存在的话

drop table if exists tb_course

创建课程表tb_course

#UNSIGNED属性就是将数字类型无符号化,INT的类型范围是-2 147 483 648 ～ 2 147 483 647， INT UNSIGNED的范围类型就是0 ～ 4 294 967 295。
create table tb_course
(
cid int not null,
cname varchar(31) not null,
credit tinyint unsigned not null,
#timestamp: 在创建新记录的时候把这个字段设置为当前时间
cdate timestamp default now(), 
primary key (cid)
);

result

录入数据

insert into tb_student values
(1001, '宁宁', '女', '1996-11-28', '四川成都');
insert into tb_student values 
(3001, '宁宁', '女', '1996-11-28', '四川成都');
insert into tb_student (stuid, stuname, stubirth) 
values(1002, '小铃铛', '1996-11-24');

一次插入多条数据

insert into tb_student (stuid, stuname, stusex)
values
(1003, '小龙坎', default),
(1004, '马路边', '女'),
(1005, '韩悦式', '女'),
(1006, '翠叠苑','男');

insert into tb_course (cid, cname, credit)
values
(0001,'语文', 3),
(0002,'数学', 5),
(0003,'英语', 4),
(0004,'python',2),
(0005,'SQL',3);

删除记录

delete from tb_student where stuid=3001;
delete from tb_student where stuid>1005;
delete from tb_student where stuid between 1003 and 1005;

删全表

truncate table tb_student;

更新记录

update tb_student set stubirth='1995-5-5'  where stuid=1003;
update tb_student set stubirth='1996-6-6',stuaddr='河北保定' where stuid=1004;

update tb_sourse set credit='1' where cid in (2,4)
update tb_sourse set credit='6' where cid=2 or cid=4;

查询记录

select * from tb_student;
select * from tb_course;

student
course

投影

select stuid, stuname,stusex from tb_student;

result

别名 不用写as也可以

select stuid as 学号, stuname 姓名，stusex as性别 from tb_student；

result

筛选

# 选出性别为男的学生
select stuid, stuname, stusex from tb_student where stusex='男';
# 选出90后，并且学号小于1003的学生
select stuid, stuname from tb_student where stubirth between '1990-1-1' and '1999-12-31' and stuid<1003;

判断控制(null)不能用等号=或<>，使用is 或 is not

# 查询学生表家庭住址为空的学生
select * from tb_student where stuaddr is null;
# 查询学生表生日不为空的学生
select * from tb_student where stubirth is not null;

-- 模糊查询 不给精确的条件
-- %通配字符可以有0个或者多个
-- _精准字符，一个代表一个字符

insert into tb_student values (1007,'李小龙','男','1980-2-4','四川遂宁');
insert into tb_student values (1008,'小猪','女','1980-7-15','四川达州');

select * from tb_student where stuname like '小%';#找出姓小的所有学生
select * from tb_student where stuname like '小_';#找出两个字名字 并且姓小的学生
select * from tb_student where stuname like '_小%';
select * from tb_student where stuname like '小_ _';

排序

# 通过学号降序查看学生信息
select * from student order by stuid desc
#通过性别和学号排序
select * from tb_student order by stusex asc,stuid desc;

查看选取的部分

# 只看前三行
select * from tb_student order by stuid desc limit 3;
# 跳过四行看三行
select * from tb_student order by stuid desc limit 3 offset 4;
select * from tb_student order by stuid desc limt 4,3;

distinct 去重 ，函数的引用

select distinct substr(stuname,1,1) from tb_student;
select substr(stuname,2,length(stuname)) from tb_student;

1
2

聚合函数

select max(credit) from tb_course;
select min(stubirth) from tb_student;
select sum(credit) from tb_course;
select avg(credit) from tb_course;
select count(credit) from tb_course;

分组查询

select stusex,count(stuid) from tb_student group by stusex;

result

创建用户

#@'限制用户从哪里可以登陆'
create user 'nn'@'localhost' identified by '1qaz2wsx';
# 授权操作
grant all privileges on *.* to 'nn'@'localhost';
grant all privileges on *.* to 'nn'@'localhost' with grant option;

#召回所有权限
revoke all privileges on *.* from 'nn'@'localhost';

grant select on school.* to 'nn'@'localhost';
grant insert,delete,update on school.* to 'nn'@'localhost';
grant create.drop.alter on school.* to 'nn'@'localhost';