基本操作
进入数据库
mysql -u root -p
修改密码
- 注意：newpassword为自己设的新密码，分号不能丢
ALTER USER 'root'@'localhost' IDENTIFIED BY 'newpassword';
- 提示成功后输入(刷新权限)
FLUSH PRIVILEGES;
远程访问
允许远程访问的前提是操作系统开放了数据库端口的权限
允许他人访问本机(host1为允许访问的主机的IP地址,可以设为'%',允许任何人访问)
use mysql; update user set host='host1' where user='root';
- 访问他人主机
建立连接时将主机和密码设为目标主机的IP和数据库登录密码
基本指令
查看所有数据库
show databases;
创建数据库
create database student;
使用数据库
use database_name;
创建表格
create table infor(
sno varchar(15) not null,
name varchar(20) not null,
sex varchar(10) not null,
age int,
major varchar(30) not null,
class varchar(30) not null,
primary key(sno)
)engine=InnoDB default charset=utf8;
更新数据
update infor set age=23 where name='LZ';
删除信息
delete from infor where sno='001';
删除表格
drop table student;
删除数据库
drop database dbname;
快速创建表格
- 1、复制表格
create table stu like infor;
- 2、复制指定列,可以添加限制条件
create table stu2 as select name,sno from infor where age >= 23;
增加一个列
Alter table stu2 add column age int;
添加主键
Alter table stu2 add primary key(sno);
删除主键
Alter table stu2 drop primary key;
添加外键
- 首先创建score表
Alter table score add foreign key(sno) references infor(sno);
列属性设置
列描述
comment: 表示描述，没有实际含义，是专门用来描述字段的
create table my_friend(
name varchar(20) not null comment '姓名',
age tinyint not null comment '年龄'
)charset utf8;
show create table my_friend;
默认值
default，某一数据会经常性出现某个具体的值
create table my_default(
name varchar(20) not null,
age int unsigned default 0,
sex default '男'
)charset utf8;
限制属性值的范围
create table my_default(
name varchar(20) not null,
age int unsigned default 0,
sex enum('男','女') default '男'
)charset utf8;
查看所有表格
- 当前数据库
show tables;
显示表格属性
- 显示当前数据库中的表格infor属性
desc infor;
插入数据
insert into infor(sno, name, sex, age, major, class)
values('01', 'xiaocaicai', 'male', 22, 'python', 'python01'),
('02', 'LEIZI', 'male', 18, 'java', 'java01'),
('03', '由美', 'fomale', 22, 'html', 'html01'),
('04', '丸子', 'fomale', 18, 'UI', 'UI01');
查询表格
- 1.查询所有
select * from infor;
- 2.查询sno,name
select sno, name from infor;
- 3.条件查询,年龄大于十八的
select * from infor where age >18;
- 4.年龄在(18,23]
select * from infor where age > 18 and age <=23;
- 5.where条件设置,只查询18和23，还有not in
select * from infor where age in (18,23);
- 6.查询结果排序(desc降序, asc升序)
select * from infor where age > 18 and age <=23 order by age desc;
- 7.限制查询条件,从下标0到下标1（不含下标2）
select * from infor where age > 18 and age <=23
order by age desc
limit 0,2;
- 8.模糊查找
| 匹配字段 | 结果 |
|---|---|
| %E | 以E结尾 |
| E% | 以E开头 |
| %E% | 含有E的任意字符 |
| _E | E前有一个字符 |
| E__ | E后有两个字符 |
查找name以'L'开头的人员信息
select * from infor where name like 'L%';
- 9.外键关联
create table score(
sno varchar(15) not null,
math float,
english float,
Chinese float,
history float,
primary key(sno)
)engine=InnoDB default charset=utf8;
==>
insert into score(math, english, Chinese, history)
values('01', 87, 93, 78, 88),
('02', 76, 78, 80, 90);
添加外键
Alter table score add foreign key(sno) references infor(sno);
注意: 设置外键后数据的更新操作会受到关联表(主表)的限制,
即需要先更新主表才能更新子表(设置外键的表),这样可以防止主副表数据不一致.-
10.复合条件查询
- 子条件查询
select * from stu where sno in( select * from score where math >70 and math <80 );
- 查询两表按需取列
select stu.*,score.math,score.english from stu,score where stu.sno=score.sno;
- 自然合并去重
select * from stu natural join score where stu.sno=score.sno;
按组查询
- 1.使用group by,只返回组的第一行
select * from stu group by sex;
- 2.使用group_concat(),查看每个组的详细信息
select *,使用group_concat()
SELECT *,group_concat(sno, name) FROM stu GROUP BY class;
- 3.聚合函数
count(): 统计记录的数目
count(*)以优化形式快速返回,count(columns)统计列中非空的数量
sum() : 求字段的和
avg() : 求字段的平均值
max() : 求字段的最大值
min() : 求字段的最小值
- count(*):示例
select count(*) as '总人数' from stu;
select major as '专业',count(*) as "人数" from stu group by major;
- avg(): 示例
select avg(age) as '平均年龄' from stu;
- sum(): 示例
select sum(age) from stu;
- max(): 示例
select max(age) from stu;
+综合示例
select major,count(*) as '总人数',sum(age) as '年龄和值',
max(age) as '最大年龄',min(age) as '最小年龄'
from stu group by major;
- 4.使用having 对分组结果进行二次筛选
select major,count(*) as '人数大于2的'
from stu group by major having count(*)=2;
