进入mysql命令
mysql -u root -p
此命令可以加-P端口号,默认是3306,如果端口号是3307,则
mysql -u root -p -P3307
查看mysql的占用接口
show global variables like 'port';
创建utf-8的数据库
CREATE DATABASE `test2` DEFAULT CHARACTER SET utf8
管理数据库
查看数据库
show databases;
查看默认字符集
show create database day15;
添加数据库
create database databaseName;
删除数据库
drop database dabaseName;
修改数据库
alter database databaseName default character set gbk;
管理表
选择数据库
use tableName;
查看表
show tables;
创建表
create table student(
id int,
name varchar(20),
age int
);
查看表结构
desc student;
删除表
drop table student;
修改表
添加字段
alter table student add column gender varchar(1);
修改字段
alter table student modify column gender int;
修改字段名称
alter table change column gender sex int;
删除字段
alter table student drop column sex;
修改表名称
alter table student rename to teacher;
管理数据
描述数据类型字段
DESC student;
插入数据
insert into student values(1,'eric',20);
insert into student(id,NAME)VALUES(2,'jacky');
修改数据
--修改一个字段,批量操作少用
update student set name='rose';
--按条件修改
update student set name='eric' where id=2;
--修改多个字段
update student set name='jacky',age=40 where id=1;
删除数据
--删除全部数据
delete from student;
--按条件删除
delete from student where id=2;
--删除全部数据
truncate table student;
查看数据
select * from student;
数据查询
一般查询
-- 查询所有字段
select * from student;
--查询指定字段
select name ,age from student;
--查询使用别名
select name as '姓名',age as '年龄' from student;
-- 可以省略不写
select name '姓名',age '年龄' from student;
--查询时合并
select name as '姓名',(servlet+jsp) as '总成绩' from student;
--查询时去除重复记录
select distinct gender from student;
--第二种语法
select distinct(gender) from student;
条件查询
--与 or(并集) 和 and(交集)
select * from student where id=1 and name ='eric';
select * from student where id=2 or gender='男';
-- 空(is null)=" 非空(is not null)<>"
select * from student where gender is not null and gender<>";
比较条件
-- 查询servlet成绩大于75分的学生
select * from student where servlet<75;
-- 查询jsp成绩小于90分的学生
select * from student where jsp<90;
-- servlet>76 and <90
select * from student where servlet>=76 and serlvet<=90;
--等价与
select * from student where servlet between 76 and 90;
模糊查询(like)(%任意字符)(—代表一个字符)
--查询姓‘张’的学生
select * from student where name like '张%';
--查询包含‘张’字的学生
select * from student where name like '%张%';
--查询姓张且姓名只有两个字的学生
select * from student where name like '张_';
分页查询(limit)
-- 起始行,从0开始,查询记录数
-- 查询第1,2条记录(第1页)
select * from student limit 0,2;
-- 查询第3,4条(第2页)
select * from student limit 2,2;
--查询第5,6条记录(第3页)
select * from student limit 4,2;
聚合-查询
-- 最大的函数
select max(servlet) from student;
-- 最小函数
select min(jsp) from student;
-- 平均函数
select avg(servlet) from student;
-- 统计数量(count)
select count(*) from student;
select count(id) from student;
查询后排序(order by asc升序 desc降序)
--按id排序
select * from student order by id desc;
-- 按照姓名排序 默认情况
select * from student order by name asc;
-- 多个排序条件
select * from student order by servlet asc,jsp desc;
分组查询
-- 查询男女多少人
select gender,count(*) from student group by gender;
-- 看看
select address,count(*) from student group by address;
分组查询后帅选
select gender,count(*) from student where servlet>30 group by gender having count(*)>2;
修改数据库
update student set username=?,password=?,fullname=? where id=?
修改主键名字
-- 创建表的时候
create table test1(id int primary key,name varchar(20));
--or
create table test1(id int, name varchar(20),constraint PK_KM_T primary key(id));
-- 添加的时候
alter table tb add constraint pk_id primary key(id);
other
三表查询
select * from (KM_TEAM T INNER JOIN KM_STAGE S ON T.ID=S.TEAM_ID) INNER JOIN KM_MEMBER M ON T.ID=M.TEAM_ID;
复制表结构
create table TMP_OUTLISTERRORGW_BAK like TMP_OUTLISTERRORGW
复制数据库表
insert into TMP_OUTLISTERRORGW_BAK SELECT * FROM TMP_OUTLISTERRORGW
查看mysql数据存储路径信息(分号不能掉)
show variables like '%datadir%';
C:\ProgramData\MySQL\MySQL Server 5.7\Data\
三种方法查看mysql版本号
mysql> status;
mysql> select version();
mysql> show variables like "%version%";
linux远程连接创建账号及密码
GRANT ALL PRIVILEGES ON *.* TO 'root1'@'%' IDENTIFIED BY 'm!123456' WITH GRANT OPTION;
FLUSH PRIVILEGES;
# 启用ssl
grant select on *.* to 'dba'@'%' identified by 'xxx' REQUIRE SSL;
linux重启
service mysqld restart
linux更改密码配置
set global validate_password_policy=0;
查看数据库数据物理存放位置
show global variables like "%datadir%"
如何查看本地MySQL的安装路径
show variables like "%char%";