进入数据库系统 cd E:\mysql-8.0.18-winx64\bin;
启动mysql服务 net start mysql
登录 mysql -u root -p
修改密码 ALTER USER 'root'@'localhost' IDENTIFIED BY 'xxxxxx';
查询所有数据库 mysql> show databases;
创建数据库 mysql> create database xxxx
查看数据库 mysql> show create database xxxx;
删除数据库 mysql> drop database xxxx;
修改数据库 mysql> alter database xxxx defalut character set utf8;
查看所有表 mysql> show tables;
进入表 mysql> use xxxx;
进入指定数据库
创建表 mysql> create table student(
-> sid int,
-> sname varchar(20),
-> sage int
-> );
查看表结构 mysql> desc student;
删除表 mysql> drop table student;
修改表
添加字段 mysql> alter table student add column sgender varchar(2);
删除字段 mysql> alter table student drop column sgender;
修改字段类型 mysql> alter table student modify column remark varchar(100);
修改字段名称 mysql> alter table student change column sgender gender varchar(2);
修改表名称 mysql> alter table student rename to teacher;
插入全部字段 INSERT INTO student VALUES(1,'张三','男',20);
插入部分字段 INSERT INTO student(id,NAME) VALUE(3,'王五');
修改所有数据 update student set gender='女';
//修改整个表的所有字段的数据值
带条件的修改 update student set gender='女' where id=1;
修改多个数据 update student set gender='女',age=30 where id=1
删除所有的数据 delete from student;
另一种方式的删除 truncate table student;
//删除整张表
delete from
: ①可以带条件删除 ②只能删除表的数据,不能删除表的约束
③使用delete from删除的数据可以回滚(事务)
truncate table
: ①不能带条件删除 ②既可以删除表的数据,也可以删除表的约束
③使用truncate table删除的数据不可以回滚
带条件的删除 delete from student where id=2;
查询所有数据 select*from student;
查询指定列 select id,name from student;
查询时指定别名(as) //在多表查询时经常使用表的别名(查询出来的字段数据会显示为别名)
select id as '编号’,name as '姓名' from student as s;
查询时添加常量列 //在查询表时添加一个班级列,字段为’java就业班’,别名’年级’
select id,name,gender,age,'java就业班' as '年级' from student;
查询时合并列 //只能合并数值了类型的字段,通常用于统计2列数值类型字段的数据和
select id,name,(math+english) from student;
查询时去除重复 //例如查询性别(不去重的话会显示重复)
select distinct gender form student;
select distinct(gender)form student;
条件查询(where) 逻辑条件 and(与) or(或)
比较条件 > < >= <= == <>(不等于) between and(>=且<=)
判空条件(null 空字符串) is null / is not null / ==’’ / <>’’
模糊条件 like //通常使用以下替换标记:
– % 表示任意个字符
– _ 表示替换一个字符
select * from student where id=2 and name='李四';
select * from student where id=2 or name='张三';
select * from student where math>70;
select * from student where math>=70 and math<=90;
select * from student where math betreen 70 and 90;//与上面一句相同
select * from student where address =='' or is null;
select * from student where address <>'' and is not null;
select * from student where name like '张%';
select * from student where name like '张_ _';(匹配几个就用几个)
select id,name,(math+english+chinese) as '总成绩' from student;
聚合查询 常用函数:sum() avg() max() min() count()
select sum(math) as 'math的总成绩' from student; //查询所有学生数学成绩和
select avg(math) as 'math的平均分' from student; //查询数学的平均分
select max(math) as 'math的最高分' from student;
select min(math) as 'math的最低分' from student;
select count(*) from student; //统计字段的最大数(包括空)
select count(id) from student; //统计id的数量(不包括空)
分页查询(limit 起始行,查询几行)//起始行从0开始
select * from student limit 0,2;
//查询第1,2条记录
查询排序(order by) order by 字段 asc/desc 默认情况下按照插入记录的顺序排序
select * from student order by id asc; //正序排序
select * from student order by math asc,english desc;按数学正序,英语反序
分组查询(group by) 分组之前用where关键字,分组之后用having关键字
//按照性别进行分组,并统计每个组的人数
select gender,count(*) from student group by gender;
//筛选出人数大于2的记录
select gender,count(*) from student group by gender having count(*)>2;