★数据库的安装
服务端:1,初始化 --> mysqld --initialize-insecure
2,启动服务端 --> mysqld
- 把服务端变成Windows服务:
2.1 绝对路径下:mysqld --install
2.2 启动服务 --> net start MySQL
停止服务 --> net stop MySQL
会自动创建:用户名:root 密码:空
客户端:3,连接服务端 --> mysql -u root -p
4,发送指令(相关操作)
★转储SQL文件
备份数据库:
mysqldump -u root db5> db5.sql -p # 把db5备份到【命令行的当前目录】
数据表结构
mysqldump -u root -d db5 > db5.sql -p # 把db5备份到【命令行的当前目录】
数据表结构+数据
导入数据库:
# 先提前创建数据库 create database db5
mysql -uroot -p<密码> db5 < /root/db5.sql
★用户管理
创建用户
create user '用户名'@'IP地址' identified by '密码'; # '%'代指任意地址
删除用户
drop user '用户名'@'IP地址';
修改用户
rename user '用户名'@'IP地址'; to '新用户名'@'IP地址';
修改密码
set password for '用户名'@'IP地址' = Password('新密码')
PS:用户权限相关数据保存在mysql数据库的user表中,所以也可以直接对其进行操作(不建议)
★授权管理
查看权限
show grants for '用户'@'IP地址'
授权
grant 权限 on 数据库.表 to '用户'@'IP地址' # all privileges --> 所有权限
取消权限
revoke 权限 on 数据库.表 from '用户'@'IP地址'
★操作数据库
查看数据库
show databases;
使用数据库
use <数据库>;
创建数据库
create database <数据库> default charset utf8; # 指定字符编码为utf8
删除数据库
drop database <数据库>;
★操作表
◇创建表
create table 表名(
列名 类型 是否可以为空,
列名 类型 是否可以为空
)engine=innodb default charset=utf8 # 指定引擎为innodb,支持事务;指定字符编码为utf8
一种特殊的唯一索引,不允许有空值;
如果主键使用单个列,则它的值必须唯一;
如果是多列,则其组合必须唯一。
关键字:primary key
create table test2(
id int auto_increment primary key, # 主键
username char(20),
age int
)engine=innodb default charset=utf8;
)
或
create table test4(
nid int,
num int,
primary key(nid,num) # 联合主键
)
主键
一个特殊的索引,只能是指定内容
关键字:constraint...foreign key...references
create table color(
nid int auto_increment primary key,
name char(16) not null
)
create table fruit(
fid int auto_increment primary key,
smt char(32),
color_id int not null,
constraint fk_cc foreign key (color_id) references color(nid) # 外键
)
外键
如果为某列设置自增列,插入数据时无需设置此列,默认将自增(表中只能有一个自增列)
关键字:auto_increment
create table test6(
nid int auto_increment primary key, # 是主键
num int null
)
或
create table test7(
nid int auto_increment, # 是索引
num int null,
index(nid)
)
注意:对于自增列,必须是索引(或主键)。
设置自增的步长和起始值:
- 会话级 --> 仅仅当前会话有效
show session variables like 'auto_inc%'; 查看会话级步长和起始值
set session auto_increment_increment = 2; 设置步长
show session auto_increment_offset = 10; 设置起始值
- 全局级 --> 所有会话有效
show global variables like 'auto_inc%'; 查看全局级步长和起始值
set global auto_increment_increment = 2; 设置步长
show global auto_increment_offset = 10; 设置起始值
自增
null 可空
not null 不可空
▶创建普通字段时:默认 null
▶创建主键字段时:默认 not null
▶创建自增字段时:默认 not null
是否为空
创建列时可以指定默认值,当插入数据时如果未主动设置,则自动添加默认值
create table test8(
nid int not null default 2,
num int not null
)
默认值
unique uql (num) 单列唯一
unique uql (num,xx) 联合唯一
特点:唯一约束 (可含null)
create table test10(
id int auto_increment primary key,
username char(32),
password char(32),
unique uni1 (username)
)
或
create table test9(
id int auto_increment primary key,
username char(32),
password char(32),
unique uni1 (username,password)
)
唯一索引
◇查看表
show tables;
◇删除表
drop table 表名;
◇补充
desc 表名; 看看每个字段的属性
show create table 表名 \G; 看怎么创建的
alter table 表名 auto_increment=1 设置从哪里开始自增
alter table score default character set utf8; 设置数据库的编码
alter table app01_userinfo change name name varchar(32) character set utf8; 将相应的字段设置成utf8
★操作数据
◇增
insert into test1(username,age) values('sun',19);
insert into test1(username,age) values('sun',19),('ni',22);
insert into test1(username,age) select username,age from test2; # 从另外一张张表的数据复制过来
◇删
delete from test2; # 清空表
truncate table t2 # 清空表
delete from test1 where age > 23; # 符合条件则删除
◇改
update test1 set username='s1' where username='sq';
◇查
select * from test2;
select username from test1;
select username from test1 where age < 18;
select username as name from test1; # as
select username from test1 where age < 18 and name = 'sun'; # and
select id,username from test1 where age in(18,19); # in
select id,username from test1 where age between 18 and 20; # between
其他
select * from test1 where username like 's%'; # 通配符%表示任意数量的字符
select * from test1 where username like 's_'; # 通配符_表示一个字符
通配符
select * from test1 where age > 18 limit 2; # 只取符合条件的2条记录
select * from test1 where age > 18 limit 2,3; # 从第2条开始取后面的3条记录
分页limit
select * from test1 order by id desc; # 倒序(从大到小)查看记录
select * from test1 order by id asc; # 正序(从小到大)查看记录【默认】
select * from test1 order by id asc,sid desc; # 先按id从小到大排,再按sid从大到小排
select * from test1 order by id desc limit 3; # 倒序(从大到小)查看3条记录
select * from test1 where age > 18 order by id desc limit 4; # 倒序(从大到小)查看符合条件的4条记录
排序
select count(tid),tname from teacher group by tname; # 聚合tname 返回tname出现的次数
by tname 意思根据tname分组,只要tname重复的就合成一条
count(tid) 这里列就返回出现的次数
select max(tid),tname from teacher group by tname; # 聚合tname
by tname 意思根据tname分组,只要tname重复的就合成一条
max(tid) 意思谁的tid大我就返回谁
select count(tid),tname from teacher group by tname having count(tid) > 1; # 返回出现次数大于1的
对于聚合函数结果进行二次筛选时,必须使用having
补充:
对于聚合函数结果进行二次筛选时,必须使用having,
1,但排序不用having
2,分组前可以写where条件,分组后不可以写where条件
聚合函数:
count 计数
max 最大
min 最小
sum 和
avg 平均值
分组
select * from score
left join student on student.sid = score.student_id
left join course on course.cid = score.course_id
left join teacher on teacher.tid = course.teacher_id
关键字:
left join 左边全部显示 (主表全显示)
right join 右边全部显示 (被连接的表全显示)
inner join 没有None才显示 (有None的值不显示)
连表
select * from user where user.id in (子查询)
select * from (子查询)
select users.id,users.name,(子查询) from users
临时表(子查询)
select id,username,(case when age > 18 then 'old' else 'young' end ) as ret from test1
# 如果age大于18返回'old',否则返回'young'
select id,name,(if(isnull(score),0,score)) from test2
# 如果score为空,返回0,否则返回score本身
特殊条件判断