MySQL基础操作命令
操作数据库的步骤:连接, 打开库, 操作, 关闭退出
MySQL版本:Ver 8.0.20
1、连接数据库(在cmd终端)
mysql -h localhost -u root –p
可能还有-P(端口号,默认3306)
2、sql语法特点:
(1)SQL 语句可以换行, 要以分号结尾
(2)如果提示符为 '>’, 那么需要输入一个回车
(3)命令打错了换行后不能修改, 可以用 \c 取消
3、数据库操作
show databases;
create database testdb default charset=utf8;
create database if not exists testdb default charset=utf8mb4;
use testdb;
drop database testdb;
4、数据表操作
show tables;
建表1:
create table score(
name varchar(10),
scroe int
)engine=innodb default charset=utf8;
建表2:
create table if not exists users(
id int unsigned not null primary key auto_increment,
name varchar(4) not null,
age tinyint,
sex enum('男','女')
)engine=innodb default charset=utf8;
查看表结构:
desc score;
查看建表语句:
show create table score;
删除数据表:
drop table testtable;
修改表名:
alter table score rename as stu_score;
更改表中自增的值的起始值:
alter table stu_score auto_increment=100;
查看表状态信息:
show table status from djldb where name='score'\G;
修改表引擎:
alter table score engine='myisam';
5、数据操作(增删改查)
插入:
insert into score(name,scroe) values('jacky',82);
insert into score(name,scroe) values('Tom',70),('Jessica',85);
查询:
select * from score;
select name,scroe from score;
select * from score where name='jacky';
select 字段|* from 表名
[where 搜索条件]
[group by 分组字段 [having 分组条件]]
[order by 排序字段 排序规则]
[limit 分页参数]
select * from user where age not between 20 and 30;
select * from user where age between 20 and 30 and sex='女';
select * from user where (age=23 or age=18) and sex='女'; #不加括号,默认先and再or
select * from user where name like '%j%'; # 模糊搜索,name中含有j的
select * from user where name like '__'; # 模糊搜索,name是2个字符的
修改:
update score set scroe=90 where name='jacky';
update score set scroe=100,name='ding' where name='jacky';
update score set scroe=scroe-6 where name='ding';
update stu_score set score=90 where score=70 or score=85;
update stu_score set score=90 where score in(70,85); # 等价于上面那句
删除:
delete from score where name='ding';
delete from stu_score where sid>=100 and sid<=101;
delete from stu_score where sid between 100 and 101; # 等价于上面那句
6、退出数据库
\q或者exit 或者 quit
7、修改表结构
添加字段:
alter table user add num int not null;
alter table user add email varchar(20) after age;
alter table user add pid int(11) not null default "1" after age;
alter table user add aaa int first;
删除字段:
alter table user drop aaa;
修改字段(modify不能修改字段名;change可以修改字段名):
alter table user modify num tinyint not null default 10; #如果之前num列有null值则应该先改变null值为其他值
alter table user change num mm char(5);
8、统计函数(聚合函数)
select max(age),min(age),sum(age),avg(age),count(age) from user;
select max(age) as max_age, min(age) min_age,
sum(age),avg(age) avg_age,
count(age) from user; # 起别名,as可有可无
select count(*) from user;
select count(phone) from user; # null值不计数,空值计数
select classid,count(*) from user group by classid;
select classid,sex,count(*) as num from user group by classid,sex; # 二次分组
# 统计classid=2的男女生人数
select classid,sex,count(*) from user where classid=2 group by sex;
# 统计每个班的平均age,并按照平均age降序排列
select classid,avg(age) from user group by classid order by avg(age) desc;
# 统计女生最多的班级
select classid,sex,count(*) from user where sex='女' group by classid order by count(*) desc limit 1;
# having对分组结果再过滤
select classid,count(*) from user group by classid having count(*)>=4;
9、排序
select * from user order by age desc; # desc降序;默认升序
select * from user order by classid, age desc; #多字段排序
10、提取部分数据(分页)
select * from user limit 2; # 显示2条
select * from user limit 3,2; # 跳过3条,显示2条;
select * from user order by age desc limit 2; # 显示age最大的2条数据
11、其他
md5加密:
select md5('123456');
查看支持的字符集:
show charset;
12、导出(不要连接mysql,直接在cmd中操作)
数据库导出:
mysqldump -u root -p djldb > C:\Users\lori\Desktop\mysql_code\djldb.sql
数据表导出:
mysqldump -u root -p djldb user > C:\Users\lori\Desktop\mysql_code\djldb_user.sql
13、导入(事先已建好djldb2库;不要连接mysql,直接在cmd中操作)
mysql -u root -p djldb2 < C:\Users\lori\Desktop\ mysql_code \djldb_user.sql
mysql -u root -p djldb2 < C:\Users\lori\Desktop\ mysql_code \djldb.sql
14、用户权限管理
查看现有用户:
use mysql;
select host,user from user;
新建用户,并授权相应权限:
# 创建zhangsan 用户,登录地址任意,密码123456
create user 'zhangsan'@'%' identified by '123456';
# 给zhangsan授权djldb2库的select,insert权限
grant select,insert on djldb2.* to 'zhangsan'@'%';
新建用户lisi,并授权djldb2的所有权限:
create user 'lisi'@'%' identified by '123456';
grant all on djldb2.* to 'lisi'@'%';
删除用户:
drop user 'lisi'@'%';
15、根据计算结果进行查询和排序
# djltest是表名,Eng和Math是列名,根据Eng和Math计算出了新的数据totle,根据此数据进行排序
select *, Eng+Math as totle from djltest order by totle desc;
select id, Eng+Math as totle from djltest order by totle desc;