对数据库的操作
增:
create database [if not exists] db_name [character set, collate];
🌰:
//创建数据库
create database db01;
//如果不存在则创建数据库
create database if not exists db02;
//创建数据库并指定字符集utf-8
create database db03 character set utf8;
//创建数据库并指定字符集utf-8,而且设置排序规则不区分大小写
create database 28_sql3 character set utf8 collate utf8_general_ci
删:
drop database [if exists] db_name;
🌰:
//删除数据库
drop database db01;
//如果存在则删除数据库
drop database if exists db02;
改:
alter database db_name [character set, collate]
🌰:
//修改字符集和排序规则
alter database db01 character set utf8 collate utf8_bin;
查
show databases;
🌰:
//查看有哪些数据库
show databases;
//查看该数据库的建库语句
show create database db01;
对表的操作
增
create table tb_name(
col_name type,
col_name type,
col_name type
)[engine, character set, collate];
🌰:
create table users(
id int,
name varchar(20),
gender varchar(10)
);
删
drop table tb_name;
🌰:
//删除表
drop table users;
改
//添加列
alter table tb_name add column new_col_name type;
alter table tb_name add column new_col_name type after col_name;
alter table tb_name add column new_col_name type first;
//修改列
alter table tb_name change column col_name new_col_name type;
alter table tb_name modify column col_name type;
//删除列
alter table tb_name drop column col_name;
//重命名/迁移
ename table tb_name to new_tb_name;
//修改存储引擎,字符集,校对集
alter table tb_name [engine, character set, collate];
🌰:
//增加年龄字段
alter table users add age int;
//修改字段名字和长度
alter table users change gender sex varchar(5);
//修改长度
alter table users modify sex varchar(10);
//删除一列
alter table users drop age;
//修改表名
rename table users to user2;
//修改字符集
alter table user2 character set utf8mb4 collate utf8mb4_general_ci;
查
show tables;
describe/desc tb_name
show create table tb_name;
🌰:
//查看表里面有哪些字段
desc employee;
describe employee;
//查看建表的SQL语句
show create table employee;
对数据的操作
增
insert into tb_name values ();
🌰:
insert into users values (1, '刘备','男', 45);
删
delete from users where xxx;
🌰:
//删除id为1001的用户
delete from users where id = 1001;
改
update users set xxx;
🌰:
//把表中所有的salary都设置成99
update users set salary = 99;
查
select xxx from tb_name where xxx;
🌰:
//查询所有男用户的所有信息
select * from users where gender = '男';
其他关键字
设置主键
🌰:
create table user2(
//主键代表这个字段是唯一的,不可以重复,影响查询效率
id int PRIMARY KEY auto_increment,
name varchar(20)
);
设置不能为空
🌰:
create table user2(
id int PRIMARY KEY auto_increment,
//NOT NULL表示这个字段插入的时候不能为空
name varchar(20) not nuLL
);
and 和 or
🌰:
//查询各科都及格的同学的信息
select * from students where chinese>=60 and english >= 60 and math >= 60;
//查询一班和二班的同学的信息
select * from students where class = '一班' or class = '二班';
between … and …
🌰:
//查询数学成绩在80-90的学生(包含)
select * from students where math between 80 and 90;
not in
🌰:
//查询不在一班和二班的学生
select * from students where class not in ("一班","二班");
like
🌰:
//模糊查询 %: 表示通配 _:表示占位
//查询姓黄的学生
select * from students where name like "黄%";
//查询叫黄xx的学生
select * from students where name like "黄__";