数据定义语句(DDL)
数据库操作
<!-- 登录数据库-->
mysql -h主机地址 -u用户名 -p密码
查看所有数据库
<!--查看所有数据库-->
show databases;
创建新的数据库
create database test20210926;
选择数据库
use xxl-job;
查看数据库中的所有表
show tables;
删除数据库
drop database test20210926;
表操作
创建表
create table student(student_name varchar(10),age int(3));
查看表定义
desc student;
查看表的详细信息
show create table student \G;
删除表
drop table student;
添加表字段
alter table student add column school varchar(40);
修改表字段
alter table student modify column student_name varchar(20);
表字段重命名
alter table student change student_name name varchar(10);
修改表名
alter table student rename student_info;
数据操纵语句(DML)
插入语句
指定名称插入
insert into student (name,age,school)values('张三',18,'xxx大学');
不指定名称插入(所有的字段都需要插入,按照顺序)
insert into student values('李四',18,'xxx大学');
批量插入
insert into student values('小明',21,'xx大学'),('小红',20,'xxx大学');
修改数据
update student set school='yy大学' where name='小红';
删除数据
delete from student where name='小红';
查询语句
查询所有数据
select * from student;
查询不重复的记录
select distinct school from student;
条件查询
select * from student where id=1;
排序
<!--倒序-->
select * from student order by id desc;
<!--升序-->
select * from student order by id asc;
获取前两条
select * from student order by id desc limit 2;
分页查询
<!--从0条开始记录2条-->
select * from student order by id desc limit 0,2;
连接查询
select a.*,b.* from student a left join school b on a.school=b.name;
子查询
select * from student where school in (select name from school where name='xxx大学');
记录联合
select name from student union select name from school;