MySQL使用入门,全套基础操作

2020-05-11 00:05:53 82118

MySQL


登录

  1. mysql -uroot -p123
  2. mysql -uroot -p密文显示密码
  3. mysql -h127.0.0.1 -uroot -p123通过IP远程连接,这里是本地
  4. mysql --host=127.0.0.1 --user=root --password=123

退出

  1. exit
  2. quit

目录结构
配置文件:my.ini
数据库:文件夹
表:文件,frm

通用语法

  1. 可以单行或多行书写,以;结尾
  2. 可用空格和缩进来提高可读性
  3. 不区分大小写,关键词建议大写
  4. 单行注释: – 注释内容 or # 注释内容; --后必须加空格,#无所谓
  5. 多行注释:/* */

SQL分类

  1. DDL(Data Definition Language)数据定义语言,用来定义数据库对象:数据库,表,列等。关键字:create,drop,alter等
  2. DML(Data Manipulation Language)数据操作语言,用来对数据库中表的数据进行增删改。关键字:insert,delete,update等
  3. DQL(Data Query Language)数据查询语言,用来查询数据库中表的记录。关键字:select,where等
  4. DCL(Data Control Language)数据控制语言,用来定义数据库的访问权限和安全级别,及创建用户。关键字:grant,revoke等

基本数据类型

  • double(m, d): m是长度,d是多少个小数位
  • timestamp:时间戳类型,如果不赋值或者赋值null,则默认使用当前的系统时间,来自动赋值。
  • varchar(m):m是最大字符个数,超出就会报错

DDL: CRUD

数据库的CRUD

创建

1. 创建自己的数据库create database db1;,可以用同样的方法查看,同名数据库只能有一个

2. 创建的时候判断是否存在create database if not exists db1;,这样即使重名了也不会报错,已经存在了就不会再创建了

3. 创建时设置字符集create database db2 character set gbk;

4. 创建时判断是否存在并设置字符集create database if not exists db3 character set gbk;


查询

1. 查看所有数据库名称show databases;

2. 查看某个数据库的字符集show create database 数据库名称;
可以看到如何创建


修改

1. 修改字符集类型alter database db3 character set utf8;,注意这里utf8没有-


删除

1. 删除数据库drop database db3;

2. 判断是否存在再删除drop database if exists db3;


使用数据库

1. 查询当前正在使用的数据库名称select database();

2. 使用数据库use db1;


数据库表的CURD

创建

create table 表名( 参数名,参数类型 )

 create table student(
id int,
name varchar(32),
age int,
score double(4,1),
birthday date,
insert_time timestamp
);

复制表create table 表名 like 被复制表名;


查询

展示所有表show tables;

查看表结构desc 表名;

查看表的字符集show create table students;


修改

修改表名alter table student rename to students;

修改字符集alter table students character set gbk;

添加一列alter table 表名 add 列名称 列类型;

修改列名称alter table 表名 change 列名称 新列名称 新列类型;,类型也可以直接改掉

删除列alter table 表名 drop 列名;


删除

删除drop table 表名;

判断并删除drop table if exist 表名;


DML: 增删表中数据

添加数据

添加输入insert into 表名(列名1,...) values(值1,...);

注意

  1. 列名和值要一一对应
  2. 如果表明后不定义列名,则默认给所有列添加值
  3. 除了数字类型,其他类型需要用引号,如日期:“1000-2-1”

删除数据

删除表中数据delete from 表名 where id=1;

删除表,并创建空表truncate table 表名;

注意 :如果不加where,就删除全部数据,但是不推荐用这个方法删除所有记录,因为是逐条删除,效率低。


修改数据

修改update 表名 set 列名=新值,... where 条件;

注意:如果不加where就会修改全部数据


DQL: 查询表中的记录

基础查询

从表里查询所有selesc * from 表名;

1. 多个字段的查询select 字段名1,字段名2,... from 表名;,如果想查询所有,可以用*

2. 去除重复dictinct 字段名1,字段名2,... from 表名;,如果是对各字段,全部重复才会被去除

3. 计算列字段名1 + 字段名2,一般可以使用四则运算来计算,ifnull(a,b)可调整null,a是需要被判断的值,b是如果是null后替换的值

4. 起别名字段名1 as 新名,字段名2 新名, 字段名1 + 字段名2 as 新名,可以使用as也可以使用空格


条件查询

1. 标准运算符:> < <= >= = <>, <>表示不等于,也可以用 !=

2. 在。。。之间: SELECT * FROM students WHERE age BETWEEN 11 AND 14;,包含边界

3. 在。。。里面select * from students where age in (11,12,14, 16);

4. 是否为NULLSELECT * FROM students WHERE birthday IS NULL;,还可用IS NOT NULL,但不能用=来判断

5. 与SELECT * FROM students WHERE age >= 11 AND age <= 14;,也可以用&&

6. 或SELECT * FROM students WHERE age=12 OR age=14;,也可以用|

7. 非NOT或者!

8. 模糊查询SELECT * FROM students WHERE NAME LIKE '刘_';_是单个任意字符的模糊,%是零个或多个字符的模糊。


排序查询

语法:oreder by 字段1 排序方法1,字段2,排序方法2 … 先排序字段1,如果相同的话,再用后面的字段排序。ASC是升序,DESC是降序。

SELECT * FROM students ORDER BY age ASC, score DESC;


聚合函数

1. count:计算个数,出现null的话会不被计算个数,所以要么找一个不包含null的列,要么改变null

SELECT COUNT(NAME) FROM students;
SELECT COUNT(IFNULL(birthday,0)) FROM students;  # 改变null的方法
SELECT COUNT(id) FROM students;

2. maxSELECT MAX(age) FROM students;

3. minSELECT MIN(age) FROM students;

4. avgSELECT AVG(age) FROM students;,不考虑NULL,就当没有

注意:聚合函数的计算都会排除NULL值。


分组查询

语法group by 分组字段;

SELECT NAME, AVG(age) Age FROM students WHERE age> 1 GROUP BY NAME HAVING COUNT(id)< 5;,这里也可以换名
where和having区别

  1. where在分组前,挑选符合条件的进行分组;having在分组后,对分成的组进行筛选
  2. where不能加聚合函数,having可以加聚合函数(因为where的时候还有组,所以无法聚合,而having时已经有组了,所以可以聚合)

分页查询

语法limit 开始的索引,每页查询的条数
SELECT * FROM students LIMIT 0,3;

注意:limit操作是mysql的一个"方言"


DCL管理用户

先切换到mysql工具库
查询user表,%表示任意用户可用

管理用户

添加用户

CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
CREATE USER 'zs'@'localhost' IDENTIFIED BY '123';
CREATE USER 'ls'@'%' IDENTIFIED BY '123';

修改密码

UPDATE USER SET PASSWORD = PASSWORD('新密码') WHERE USER = '用户名';
SET PASSWORD FOR '用户名'@'主机名' = PASSWORD('新密码');

授权

查询权限

SHOW GRANTS FOR '用户名'@'主机名'

授予权限

GRANT 授权列表 ON 数据库名.表名 TO '用户名'@'主机名';
GRANT SELECT, DELETE ,UPDATE ON db3.account TO 'lisi'@'%';
# 授予所有库所有表所有权限
GRANT ALL ON *.* TO 'lisi'@'%';

撤销权限

REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名'

约束

作用:对表中的数据进行限定,保证数据的正确性、有效性和完整性

非空约束

关键词NOT NULL

声明为非空约束

  1. 可在创建的时候声明
 CREATE TABLE stua(
id INT,
NAME VARCHAR(20) NOT NULL -- name为非空
);
  1. 可用修改来后期增加
ALTER TABLE stua MODIFY NAME VARCHAR(20) NOT NULL;

取消声明

ALTER TABLE stua MODIFY NAME VARCHAR(20);

唯一约束

创建唯一约束:方法和非空约束相同

删除唯一约束ALTER TABLE stu DROP INDEX phone_number;

关键词UNIQUE

注意NULL可以重复


主键约束

创建主键

  1. 创建时声明
CREATE TABLE stu(
id INT PRIMARY KEY, -- 给id添加主键约束, 注意这里是两个关键词
NAME VARCHAR(20)
)
  1. 后期声明
ALTER TABLE stu MODIFY id INT PRIMARY KEY;

删除主键ALTER TABLE stu DROP PRIMARY KEY;

创建主键自动增长

CREATE TABLE stu(
id INT PRIMARY KEY AUTO_INCREMENT, -- 给id添加主键约束, 注意这里是两个关键词
NAME VARCHAR(20)
)

自动增长的使用

INSERT INTO stu VALUE(NULL,'a'); # 从1开始,后面每次自动+1
INSERT INTO stu VALUE(10,'a');     # 从指定的值开始,后面每次自动+1

删除自动增长

ALTER TABLE stu MODIFY id INT;  # 并不能删除主键

外键约束

作用:将两个表的某列关联到一起,节约空间,关联后不能随意改动,会受相互影响

创建外键约束

create table 表名(
...
外键列
constraint 外键名称 foreign key(自身的外键列名称)references 主表名称(主表列名称)
);

删除外键ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

创建表之后,添加外键

alter table 表名 add constraint 外键名称 foreign key(自身的外键列名称)references 主表名称(主表列名称)

级联更新和级联删除
更新:值改变时两边一起边
删除:删除一个id时,另一个表里对应的所有都删除

alter table 表名 add constraint 外键名称 foreign key(自身的外键列名称)references 主表名称(主表列名称)ON UPDATE CASCADE # 更新
alter table 表名 add constraint 外键名称 foreign key(自身的外键列名称)references 主表名称(主表列名称)ON UPDATE DELETE # 删除

注意:外键可以为NULL,不可以为不存在的值


表之间的关系

一对多:在多的一方建立外键

多对多:建立一个中间表,分别放两个表的主键,两个主键作为新表的外键,可将两个主键一起做成一个复合主键

一对一:任意一方添加外键指向另一方的主键,但是要让外键唯一,加唯一约束

旅游案例

# 一对多,旅游类型和线路
# 先创建少的
CREATE TABLE ly_tag (
tag_id INT PRIMARY KEY AUTO_INCREMENT,
tag_name VARCHAR(20) NOT NULL UNIQUE # 注意非空和唯一
)

DROP TABLE ly_tag;
DROP TABLE ly_route;

INSERT INTO ly_tag VALUES(NULL,'蜜月'), (NULL, '毕业');
# 多的	
CREATE TABLE ly_route(
route_id INT PRIMARY KEY AUTO_INCREMENT,
route_name VARCHAR(20),
cid INT,
FOREIGN KEY (cid) REFERENCES ly_tag(tag_id)
)

INSERT INTO ly_route VALUES(NULL, '三亚', 1), (NULL, '夏威夷', 1), (NULL, '华山', 2), (NULL, '黄山', 2)

SELECT * FROM ly_tag;
SELECT * FROM ly_route;

# 用户表,和旅游路线是多对多的
CREATE TABLE t_user(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20) NOT NULL UNIQUE
)

INSERT INTO t_user VALUES(NULL, '张三'), (NULL, '李四');

# 创建一个关联表,实现多对多
CREATE TABLE tag_favorite(
rid INT,
uid INT,
PRIMARY KEY(rid,uid), # 建立复合主键
FOREIGN KEY (rid) REFERENCES ly_route(route_id),
FOREIGN KEY (uid) REFERENCES t_user(id)
)

三大范式

三大范式要求

  1. 第一范式(1NF):每一列都是不可分割的原子数据项
  2. 第二范式(2NF):在第一范式基础上,非马属性必须完全依赖于候选码,即在1NF基础上消除非主属性对主码的部分函数依赖
  3. 第三范式(3NF):在第二范式基础上,任何非主属性不依赖于其他非主属性,即在2NF基础上消出传递依赖

不满足第一范式

函数依赖:属性组确定唯一值:学号,课程名称–>分数

完全依赖:A需要B属性组中全部的信息才能得出,称A完全依赖B

部分完全依赖:A需要B属性组中部分的信息就能得出,称A部分完全依赖B

传递参数依赖:A --> B, B --> C 通过A唯一确定B,通过B唯一确定C,则称C传递函数依赖于A。例如:学号–>系名,系名–>系主任

:如果在一张表中一个属性或属性组,被其他所有属性所完全依赖(通过该确定唯一的其他),则称这个属性为码。该表中码为(学号,课程)

主属性:码属性组中的所有属性

非主属性:除过码属性的属性

第二范式就是要消出部分依赖

根据第二范式做修改


备份和还原

备份

命令行

mysqldump -uroot -proot db1 > d://a.sql

图形化工具


还原

命令行

create database db1;
use db1;
source d://a.sql;

图形化工具


多表查询

内连接

基本思路

  1. 从哪些表中查询数据
  2. 条件是什么
  3. 查询哪些字段

隐式内连接
使用where条件来消除无用的数据

SELECT
t1.`name`, t1.`dep_id`
FROM
employee t1, 
depart t2
WHERE
t2.`id` = t1.`dep_id`

显示内连接
INNER可以省略

SELECT 字段列表 FROM1 INNER JOIN2 ON1.`字段1` =2.`字段2`
SELECT * FROM employee INNER JOIN depart ON employee.`dep_id` = depart.`id`
SELECT * FROM employee JOIN depart ON employee.`dep_id` = depart.`id`

外连接

左外连接:查询的是左表的所有数据和交集部分,如果左边有右边没有,那么也会显示

SELECT 字段列表 FROM1 LEFT JOIN2 ON1.`字段1` =2.`字段2`

右外连接

SELECT 字段列表 FROM1 RIGHT JOIN2 ON1.`字段1` =2.`字段2`

子查询

就是查询中套着查询

SELECT * FROM employee WHERE employee.`dep_id` = (SELECT MAX(dep_id) FROM employee)
SELECT * FROM dept t1, (SELECT * FROM emp WHRER emp.`join_data` > '2011-11-11') t2 # 可作为虚拟表

事务

基本内容
START TRANSACTION:事务开始
COMMIT:提交事务,改动生效
ROLLBACK:回滚,回到事务开始时的状态

注意
DML默认提交

设置默认
1代表自动提交,0代表手动提交

SELECT @@autocommit = 1; # 设置为自动,不需要commit
UPDATE money SET hava=20;

SELECT @@autocommit = 0;
UPDATE money SET hava=30;
COMMIT; # 不写的话窗口关掉就会回滚

四大特征

  1. 原子性:不可分割的最小单位,要么同时成功,要么同时失败
  2. 持久性:当事务提交或回滚后,数据库会持久化的保存数据
  3. 隔离性:多个事务之间相互独立
  4. 一致性:事务操作前后数据总量不变

同时处理的问题
如果多个事务同时处理一批数据,则会引发一些问题,存在的问题:

  1. 脏读:一个事务,读取到另一个事务中还没有提交的数据
  2. 不可重复读(虚读):在同一事务中,两次读取到的数据不一样
  3. 幻读:一个事务操作数据表中所有的记录,另一个事务添加了一条数据,则第一个事务查询不到自己的修改

隔离级别

隔离语句 隔离能力 产生的问题
read uncommitted 读未提交 脏读、不可重复读、幻读
read committed(Oracle默认) 读已提交 不可重复读、幻读
repeatable read(MySQL默认) 可重复读 幻读
serializable 串行化

隔离级别从小到大安全性越来越高,但是效率越来越低

查询隔离级别select @@tx_isolation;

设置隔离级别SET GLOBAL TRANSACTION ISOLATION LEVEL 隔离语句,重启才会生效

转载:https://blog.csdn.net/McEason/article/details/106028315

随缘而来,乘风而去,山高海阔,自有我风采!
所属分类:数据库