配置文件
MySQL本地的文件my.ini中配置了MySQL数据库对应的端口号、存储路径、数据存储路径等信息。(修改后需要重新启动MySQL服务)
服务启动与停止
方式一:计算机——右击管理——服务;
方式二:通过管理员身份运行命令行:
- 启动:net start 服务名
- 停止:net stop 服务名
登录与登出
登录:mysql -h要登录的主机名 -u用户名 -p,然后输入密码(常用mysql -u root -p)
登出:exit或者ctrl+C
常用命令
常用数据库操作:
- 查看所有数据库:SHOW DATABASES;
- 创建数据库:CREATE DATABASE 数据库名;
- 操作数据库:USE 数据库名;
- 删除数据库:DROP DATABASE 数据库名;
- 查看自己正在操作哪个数据库:select database();
权限操作:
- 查看权限:SHOW GRANTS FOR 用户名;
- 赋予权限:GRANT ALL ON 数据库名.* TO ‘用户名’ @ ‘%’ IDENTIFIED BY ‘密码’ WITH GRANT OPTION;
- 移除权限:REVOKE ALL ON 数据库名.* FROM ‘用户名’ @ ‘%’;
#授予通过网络方式登录的tom用户,对所有库所有表的全部权限,密码设为abc123.
grant all privileges on *.* to tom@'%' identified by 'abc123';
#给tom用户使用本地命令行方式,授予test这个库下的所有表的插删改查的权限。
grant select,insert,delete,update on test.* to tom@localhost identified by 'abc123';
隔离级别:
-
每启动一个 mysql 程序, 就会获得一个单独的数据库连接. 每个数据库连接都有一个全局变量 @@tx_isolation, 表示当前的事务隔离级别。
-
查看当前的隔离级别:
SELECT @@tx_isolation;
-
设置当前 MySQL 连接的隔离级别:
set transaction isolation level read committed;
-
设置数据库系统的全局的隔离级别:
set global transaction isolation level read committed;
常用表操作:
-
查看数据库其下的表:SHOW TABLES;
- 或SHOW TABLES FROM 数据库名;(用于跨库查询)
-
查看表结构:DESCRIBE 表名;
- 或者SHOW COLUMNS in 表名; \SHOW COLUMNS from 表名;
-
删除表:DROP TABLE IF EXISTS 表名;
-
创建表:
CREATE TABLE IF NOT EXISTS person(
id INT UNSIGNED AUTO_INCREMENT,
username VARCHAR(100) NULL,
password VARCHAR(40) NOT NULL,
birthday DATE,
INDEX username_index(username(100)),
PRIMARY KEY (id)
)ENGINE=InnoDB;
- 添加表字段:
alter table person add hometown varchar(10);
- 删除表字段:
alter table person drop hometown;
- 修改表字段:
alter table person modify hometown char;
- 修改表名:
alter table person RENAME TO person1;
- 创建索引:
create index username_index ON person(username(100));
#或者
alter table person ADD INDEX password_index(password);
- 删除索引:
drop index password_index ON person;
- 插入数据:
insert into person values(值1,值2,....); #值的顺序与字段在表中的顺序一致
insert into person(username,password) values ('刘星宇','123457'); #为指定字段赋值
insert into person(username,password)
select username,password from test; #复制另一张表中对应字段的数据
- 修改数据:
update person set username='刘星星',birthday='1998-03-18';
- 删除数据:
truncate table person; #删除表中所有数据
delete from person where username='刘星星'; #根据条件删除表中数据
- 查询数据:
select * from person;
- 去掉重复元素:
select distinct(username) from person;
select count(distinct username) from person;
- 模糊查询,%表示任意多个字符(包括0),_表示任意单个字符,需要转义时用 \ :
select * from person where username like "刘%"; #查询以“刘”开头的数据,只有这样使用索引以下两种不使用索引
select * from person where username like "%星"; #查询以“星”结尾的数据
select * from person where username like "%星星%"; #查询包含“星星”的数据
select * from person where username like "_星_刘%"; #查询第二个字符是“星”,第四个为“刘”的数据
- 合并结果集:
select username from person1 union select username from person2
- 连接(left jion/right jion/jion/逗号):
select * from person1 left jion person2 where col1=col2 #获取左表所有记录,即使右表没有对应匹配的记录
select * from person1 right jion person2 where col1=col2 #获取右表所有记录,即使左表没有对应匹配的记录
select * from person1 jion person2 where col1=col2 #获取两个表中字段匹配成功的记录
select * from person1, person1 where col1=col2 #与jion相同
- 分组:
select 分组函数,列(该列要求出现在group by后面) from 表名
where 条件
group by 分组列表
having 分组后筛选条件
order by 子句
select count(*) from person group by username ='高尔基' #统计person表中用户名为“高尔基”的用户数目
- 排序(排序字段如果涉及函数或表达式,可以用别名代替):
select * from person order by id ASC #默认就是ASC,id可以省略,按id升序排列
select * from person order by id DESC #按id降序排列
select * from person order by id ASC, username DESC; #id升序,username降序
- 分页:
select * from person limit 5; #返回前5条记录
select * from person order by id limit 10; #根据id排序后,返回1到5条记录
select * from person limit 0,5; #同上,返回前5条记录
select * from person limit 5,10; #返回第6到第15条记录
设当前页数为page,每页的条目数为size
select 查询列表 from 表 limit (page-1)*size,size
- 获取前10条记录
select top 10 * from tablename order by newid()
- 起别名:
select username as userName from person where id=1; #as可以省略;如果别名中有特殊符号,需要引号括起来。
- 连接。MySQL中的“+”加号,只能做加法运算,不能做连接,会把字符型自动转换为数值性。(转换失败就赋值为0)如果需要做连接操作,可以用concat关键字。
select concat(username,password) as info from person;
- 条件表达式、逻辑表达式and、or、not:
select * from person where salary > 1200;
select * from person where id <> 2;
select * from person where salary > 1200 and id >= 2;
select * from person where not(salary > 1200 or id >= 2);
- between and(包含边界值)
select * from person where salary between 1000 and 2000; #查询薪水在1000到2000的数据
- in:用于判断某字段的值是否属于列表(集合)中的某一项。不能使用% _等模糊查询。
select * from person where username in('刘星宇','刘星星');
- is null:判断某个字段是null。(=或<>不能用于判断null值)
- is not null:判断某个字段不是null。
- <=>:安全等于,既可以判断null值,又可以判断数值,可读性差。
函数
- 字符函数:
character_length("title") #字符串title的字符数
concat("title","author") #将字符串title和author合并为一个字符串
concat_ws(",","title","author") #将字符串title和author合并为一个字符串,合并时使用逗号作为分隔符
lower("title") #将字符串title的内容转为小写
upper("title") #将字符串title的内容转为大写
reverse("title") #反转字符串title
substr("titletitletitle",1,3) #截取索引1开始,长度为3的子字符串(MySQL索引从1开始)
instr("titletitletitle","title") #返回子串第一次出现的索引,找不到则返回0
trim("a","aaaaitlaaa") #去除首尾的字符“a”
lpad("title",12,"a") #左边用“a”填充至长度12
rpad("title",12,"a") #右边用“a”填充至长度12
replace("ilovayou","you","he") #用“he”替换掉“iloveyou”中的“you”
- 数学函数:
sum、avg用于处理数值型数据;max、min、count用于处理任何数据类型;都忽略null值。
和分组函数一同查询的字段有限制,要求是group by后面的字段,其余都不行。
abs("price") #求price的绝对值
avg("price") #求price的平均值
count("price") #求price的总记录数(不包括为null的记录),和count(1)、count(*)效果相同,count(*)效率较高
max("price") #求price的最大值
min("price") #求price的最小值
sum("price") #求price的和
rand() #返回0-1之间的随机数
round() #四舍五入
ceil() #向上取整
floor() #向下取整
truncate(1.89999,2) #截断,保留二位,其余舍弃
mod(10,3) #取10/3的余数,结果为1
- 日期函数:
now() #datetime类型,日期和时间,2020-06-05 15:52:37
adddate("date",n) #date加上n天的时间
addtime("date",n) #date加上n秒的时间
curdate() #当前日期
current_time() #当前时间
current_timestamp() #当前日期时间
datediff(d1,d2) #d1和d2相隔的天数
period_diff(d1,d2) #d1和d2相隔的月数
subdate(d,n) #d减去n天的日期
subtime(d,n) #d减去n秒的时间
connection_id()#服务器当前连接数
流程控制
-
IF(expr,v1,v2)#如果表达式 expr 成立,返回结果v1;否则,返回结果v2。
-
IFNULL(v1,v2)#如果 v1 的值不为 NULL,则返回 v1,否则返回 v2
-
CASE expression WHEN condition1 THEN result1 WHEN condition2 THEN result2 .. WHEN conditionN THEN resultN ELSE result END #当有一个成立之后,后面的就不执行了
约束
- NOT NULL :非空,用于保证该字段的值不能为空。
- DEFAULT:默认值,用于保证该字段有默认值。
- PRIMARY KEY:主键,用于保证该字段的值具有唯一性并且非空。
- UNIQUE:唯一,用于保证该字段的值具有唯一性,可以为空。
- FOREIGN KEY:外键,用于限制两个表的关系,用于保证该字段的值必须来自于主表的关联列的值,在从表添加外键约束,用于引用主表中某些的值。
create table if not exists t_stuinfo(
id int primary key, #主键
stuName varchar(20) not null, #非空
gender char(1) check(gender='男' or gender='女'), #检查约束,MySql没有效果但不报错
seat int unique, #唯一约束
age int default 18, #默认(值)约束
majorId int references major(id) #外键约束,MySql没有效果,但不报错
);
修改表约束:
alter table 表名称 modify column 列名 列类型 新约束;
#例如:
alter table 表名称 modify column 列名 列类型 not null;
alter table 表名称 modify column 列名 列类型 primary key;
删除表约束:
alter table 表名称 drop primary key; #删除主键约束
alter table 表名称 drop index(索引名) 设置唯一时的名称; #删除唯一约束
alter table 表名称 drop foreign key(索引名) 设置外键时的名称; #删除外键约束