MySQL基本操作

素颜马尾好姑娘i 2022-04-06 07:42 394阅读 0赞

MySQL基本操作

一个库中有多张表，每张表里有多条数据
表分成行（row 横向）与列（column ）纵向

1. DDL 数据定义语言

  1. create database 数据库名;
  2. create table 表名(列定义); (重点)
  3. drop database 数据库名;
  4. drop table 表名;
  5. alter table 表 ... (添加列, 修改列, 删除列, 重命名列8.0才有)
  6. alter user 用户
  7. create -- 创建xx定义， drop -- 删除xx定义， alter -- 修改xx定义

建表

  1. create table 表名(
  2.     列1名 类型,
  3.     列2名 类型,
  4.     ...
  5. );

例如：

  1. create table student(
  2.     id int,
  3.     name varchar(10),
  4.     sex char(1)
  5. );

整数类型： tinyint(1个字节), smallint（2个字节）, int(4个字节), bigint（8个字节）
无符号数字 tinyint unsigned (0~255)

浮点类型： float, double
定点小数： decimal(总位数, 小数位数)
decimal(10, 2) 小数部分两位，整数部分最大8位

字符类型
char(长度) char(10) 表示最多存10个字符, 定长，效率高
varchar(长度) varchar(10) 表示最多存储10个字符，变长

“abc” “abc “ 存储时，长度不足，用空格补齐
“abc” “abc” 存储时，根据实际长度存储，可以节省空间
日期类型
datetime
timestamp

唯一主键
每张表只能有一个主键
主键的值必须是唯一，且非空的

  1. create table 表名(
  2.     列1名 类型 primary key,
  3.     列2名 类型,
  4.     ...
  5. );

自增列，用来解决主键冲突问题
在主键列后加入：auto_increment
1 2 3 4 …
因为id列由数据库维护，所以有了自增列后就不需要给id列赋值了

  1. create table student(
  2.     id int primary key auto_increment,
  3.     name varchar(10),
  4.     sex char(1)
  5. );

从1001开始自增

  1. create table b (
  2.     id int primary key auto_increment,
  3.     name varchar(20)
  4. );
  5. alter table b auto_increment=1001;

添加列
语法：alter table 表名 add 列名 数据类型;
例如：给student新增一个age列

  1. alter table stduent add age tinyint unsigned;

修改列
语法：alter table 表名 modify 列名 新类型;
例如要修改列的长度定义原来varchar(10)

  1. alter table student modify name varchar(20);

删除列
语法：alter table 表名 drop 列名;

重命名列
语法：alter table 表名 rename column 旧列名 to 新列名;

2. DML (数据操控语言) (重点)

insert

语法1：

  1. insert into 表名(列...) values(值...);  插入一行

语法2：

  1. insert into 表名(列...) values(值...), (值...), (值...), (值...);  插入多行
  3. create table student2(
  4.     id int primary key,
  5.     name varchar(20),
  6.     sex char(1)
  7. );

语法3：从表1查询，把查询结果插入表2

  1. insert into 表2 select * from 表1;

如果两张表结构不一样，可以在select后加具体的列名，以便和新表的列相匹配
例如：

  1. create table student3(
  2.     id int primary key,
  3.     name varchar(20)
  4. );
  5. insert into student3 select id,name from student;

load data

可以把外部文本文件的内容导入到数据库表中
语法：load data infile ‘文件路径\文件名字’ into table 表名;

  1. create table hero(
  2.     id int primary key,
  3.     name varchar(10),
  4.     loc varchar(10),
  5.     sex char(1),
  6.     birth int,
  7.     death int,
  8.     power int
  9. );

要让load data命令生效，必须修改设置（在my.ini文件中）：

  1. [mysqld]
  2. character-set-server=utf8mb4
  3. secure-file-priv=

其中secure-file-priv默认是null值，表示不允许加载文件
可以改为具体目录名，表示只能从这个目录加载文件
如果改为””，表示可以从任意目录加载文件

例如：加载之前heroes.txt，把数据存入hero：

  1. load data infile 'e:\\heroes.txt' into table hero;

如果文件中的列分隔符是, 不是默认\t 键，需要用 fields TERMINATED BY来指定分隔符

  1. load data infile 'e:\\person.txt' into table person fields TERMINATED BY ',';

source

  1. source 文件路径/文件名
  • 其文件内容必须是合法的sql语句
  • 与load的区别：不用引号，建议使用/分隔路径，文件编码与操作系统编码一致（gbk）

update 更新

语法：

  1. update 表名 set 列名=新值 where 条件;
  3. update person set sex='男';             // 把所有记录性别都改为男
  4. update person set sex='男' where id=1;  // 只修改id=1的性别为男

delete 删除

语法：

  1. delete from 表名; // 删除表中所有记录(危险操作)
  2. delete from 表名 where 条件; // 删除满足条件的记录

select 查询

语法：

  1. select 列名... from 表 where 条件 group by 分组条件 having 分组筛选条件 order by 排序条件 limit;

条件 where

  1. = 等值匹配
  2. != 不等值匹配
  3. > 大于
  4. < 小于
  5. >= 大于等于
  6. <= 小于等于

逻辑运算符组合多个条件
逻辑与（两个条件同时成立） and 例如：

  1. select * from hero where sex='女' and loc='建业';

逻辑或（两个条件有一个成立，结果就是真） or

  1. select * from hero where name='小乔' or id=200;
  2. 逻辑非 (条件取反) not

列 between 值1 and 值2 等价于 列 >= 值1 and 列 <= 值2， 注意小值要在前面，包含边界的值
列 in (值1,值2,… 值n) 等价于 列=值1 or 列=值2 … or 列=值n 注意值列表的长度
like 模糊查询 其中匹配通配符 % 表示匹配0~多个任意字符
通配符 _ 表示匹配1个任意字符
例如：

  1. select * from hero where power between 85 and 90;
  2. select * from hero where power >= 85 and power <=90;
  4. not in
  5. not like
  6. not between ... and

排序条件

排序条件：列名 升降序 如果升降序关键字省略，默认是asc
升序-> 由小到大 asc
降序-> 由大到小 desc

  1. select * from hero order by power desc limit 10;

多列排序： 排序条件1, 排序条件2 …
先按照条件1排序，条件1中取值相同的，再按照条件2排序

限制返回结果个数

  1. limit m;    // 最多返回m个结果
  2. limit n,m;  // 最多返回m个结果，n代表起始下标，下标从0开始

经常用来实现分页应用，假设每页10条

  1. 第一页 limit 0,10;
  2. 第二页 limit 10,10;
  3. 第三页 limit 20,10;

分组条件

  1. select count(*),max(sal),min(sal),sum(sal),avg(sal),deptno from emp group by deptno;
  3. count(*)  表示求每组的个数
  4. max(列) 求最大值
  5. min(列) 求最小值
  6. sum(列) 求和
  7. avg(列) 求平均值

分组之后，

  • select子句中只能出现分组条件列和组函数，其他列不能出现在select中，
  • order by 子句中只能出现分组条件列和组函数，其他列不能出现在order by中，
    例如：
    select deptno,max(sal),ename from emp group by deptno; // ename不符合刚才的规定
    select deptno,max(sal) from emp order by ename; // 错误的

having 也是过滤

  1. where > group by > having > select > order by > limit // sql语句的执行顺序
  3. select count(*), deptno from emp where count(*) >=5 group by deptno; // 因为where先执行，这时候还没有分组，不知道个数,错误
  4. select count(*), deptno from emp group by deptno having count(*)>=5;

有时候筛选条件既可以写在where 上，也可以写在having (优先采用where)

  1. select count(*), deptno from emp where deptno=10 or deptno=30 group by deptno;
  2. select count(*), deptno from emp group by deptno having deptno=10 or deptno=30;

多列分组 （了解）

多个列取值都相同的分为一组

  1. group by 列1，列2 ...
  3. select count(*),deptno,job from emp group by job,deptno;

多列分组时，列的顺序不影响结果

多表结构和连接查询

  1. select ... from 表1 inner join 表2 on 连接条件 
  2.     where group by having order by limit;
  4. select empno,ename,sal,emp.deptno,dept.deptno,dname,loc from emp inner join dept on emp.deptno = dept.deptno;

表1 表1别名

  1. select empno,ename,sal,e.deptno,d.deptno,dname,loc 
  2.     from emp e inner join dept d on e.deptno = d.deptno;

几种连接查询

  1. 表1 inner join 表2 on 连接条件  (内连接：两张表的记录必须完全满足连接条件，才会出现在最后结果中)
  2. 表1 left outer join 表2 on 连接条件  (左外连接)
  3. 表1 right outer join 表2 on 连接条件  (右外连接)
  5. select empno, ename, e.deptno, d.deptno, d.dname, d.loc 
  6.     from emp e left outer join dept d on d.deptno=e.deptno;

left outer join 位于连接左侧的表，不管是否连接到了记录，都会出现在结果中
符合连接条件的记录，和内连接效果一样
不符合连接条件的记录，对应另一张表的列都是null值

right outer join 位于连接右侧的表，不管是否连接到了记录，都会出现在结果中

outer可以省略

连接查询的等价写法

  • 内连接的等价写法 (重要)

    select … from 表1,表2 where 连接条件;

    select e.empno,e.ename,e.deptno,d.deptno,d.dname from emp e, dept d where e.deptno=d.deptno;

  • mysql 独有的 (了解)

    select … from 表1 inner|left join 表2 using(deptno); // 两张表的连接列名要相同

    select e.empno,e.ename,e.deptno,d.deptno,d.dname from emp e inner join dept d using(deptno);

常用函数

select count(*) from emp; // 求整张表的行数
select max(sal) from emp; // 求整张表的工资最大值

Bit Functions 位运算函数
Comparison operators 比较运算符
Control flow functions 流程控制
Date and Time Functions 日期函数

  1. year() 截取年份
  2.     month()
  3.     date()
  4.     date_add(日期 时间间隔); 其中时间间隔的语法：interval n 单位
  5.     select empno,ename,date_add(hiredate, interval 1 month ),hiredate from emp; 加一个月
  6.     select empno,ename,date_add(hiredate, interval 3 day ),hiredate from emp; 加3天
  7.     SELECT EXTRACT(DAY_MINUTE FROM '2009-07-02 13:02:03'); 提取日期中的从天到分钟的部分
  8.     select now() 获取当前时间

Encryption Functions 加密
Information Functions
Logical operators 逻辑运算符
Miscellaneous Functions 剩余的函数
Numeric Functions 数学函数

  1. rand() 生成一个从[0.0 ~ 1.0) 之间的随机小数， 小于1.0
  2.     floor() 舍去小数
  3.     round() 四舍五入

String Functions 字符串函数

  1. left(字符串, n)  n代表从左边要截取的字符
  2.     lower()
  3.     upper()
  4.     substr(字符串,下标, 长度) 下标从1开始
  5.     求字符串长度的例子：select * from hero where char_length(name)=4;

导出数据

  1. cmd > mysqldump -uroot -p 库名 >> 要保存的文件.sql （source的逆操作, 内部是sql语句）
  2. 把表中的数据保存到文本文件中 （load data infile的逆操作）
    select * from 表 into outfile ‘文件路径\文件名’

case when

可以配合select工作, 把一列的取值根据不同的条件进行翻译
类似于 java 中的if else if
语法：

  1. case
  2.    when 条件1 then 结果1
  3.    when 条件2 then 结果2
  4.    ...
  5.    else 结果n
  6. end

2000以下显示低工资，2000~3000显示中等，3000以上显示高工资

  1. select empno, ename, sal, 
  2. case 
  3.     when sal <= 2000 then '低工资'
  4.     when sal >2000 and sal <=3000  then '中等工资'
  5.     else '高工资'
  6. end 工资级别  from emp;

3. DCL

用户权限

grant 授权
revoke 回收权限

创建用户

  1. create user 用户名 identified by '密码';

授权语法：

  1. grant 权限 to 用户名;

例如：把查询test库中所有表的查询权限授权给user1
grant select on test.* to user1;

回收权限

  1. revoke 权限 from 用户名;

例如：回收之前分配的权限:
revoke select on test.* from user1;

事务和锁

事务

transaction 事务 : 把多条sql语句视为一个整体执行,这些sql 要么都成功, 其中有一个失败了，之前的操作也需要撤销

两个人同时操作一张表的同一条数据

  1. 中国银行账户表 account
  2. id        name       balance(余额)
  3. 1         张三       2000
  5. 建设银行账户
  6. id        name       balance(余额)
  7. 2         张三       3000
  9. 张三转账
  10. try { 
  11.     begin;
  12.     update account set balance=balance+1000 where id = 2;    
  14.      // 出现异常
  15.     update account set balance=balance-1000 where id = 1; 
  16.     commit;
  17. } catch( Exception e ) { 
  18.     rollback;
  19. }

mysql的事务控制
默认情况下，是一条语句一个事务
要多条语句一个事务，需要通过 begin commit rollback 来控制事务

begin; // 表示事务的起点 等价方式： start transaction
sql1
sql2
…
commit(提交，表示结果都将生效) 和 rollback(回滚，用来撤销事务内的更改) 表示事务的终点

TCL (事务控制语言)

锁(了解)

  1. 客户1                                                               客户2
  2. begin;                                                              begin;
  3. update emp set sal = 4000 where empno = 7369;
  4.                                                             update emp set ename = 'abc' where empno = 7369; // 被阻塞
  5.                                                             直到超时为止

InnoDB 行级锁, 只要两个客户端更新的是不同的行，互不干扰
MyISAM 表锁，是锁住整个表

增删改（insert update delete) 都会在行上加排他锁（X锁）
查询可以加共享锁 (S锁)表示可以同时查询，但其他人不能增删改（insert update delete)

  1. select * from 表 lock in share mode;  添加共享锁-别人可以再加共享锁，但不能再加排他锁

查询时加排他锁

  1. select * from 表 for update;   添加排他锁， -别人不能再加共享锁和排他锁

mysql对查询有特别的优化：不用锁也能实现并发访问: 多版本并发访问(MVCC)

查询

  1. 1   张三    8000 旧版本
  2.                 修改
  3.                 1   张三    7000
  4. select * from 表 就是利用的多版本并发查询, 好处：并发性高！

java.util.concurrent.CopyOnWriteArrayList 线程安全的ArrayList: 修改时会产生一个集合的副本，修改都是在副本上进行，查询查的是旧集合的内容，所以查询和修改可以并行执行，等修改结束会用副本替换旧的内容
线程安全的List： Vector 全部方法加锁

事务隔离性（了解）

有不同的隔离级别，
隔离级别越低，并发性越好，但数据的一致性差
隔离级别越高，并发性差，但数据的一致性高
由低到高四种：
读未提交 < 读提交 < 可重复读(mysql默认) < 序列化读

错误的级别由高到低
脏读 ， 不可重复读， 幻读

脏读（读未提交）

脏读是指在一个事务处理过程里读取了另一个未提交的事务中的数据。

  1. 7369的工资1000
  2. 事务1                                              事务2
  3. begin                                              begin
  4. 修改7369的工资为8000
  5.                                                    select * sal from emp where empno=7369;// 8000
  6. rollback

将隔离级别提高到读提交,可以避免脏读

不可重复读（读提交）

不可重复读是指在对于数据库中的某个数据，一个事务范围内多次查询却返回了不同的数据值，这是由于在查询间隔，被另一个事务修改并提交了。

  1. 7369的工资1000
  2. 事务1                                              事务2
  3. begin                                              begin
  4.                                                    select * sal from emp where empno=7369; // 1000
  5. 修改7369的工资为8000
  6. commit;
  7.                                                    select * sal from emp where empno=7369; // 8000

要避免脏读、不可重复读：将隔离级别提高到可重复读隔离级别

幻读（可重复读）

幻读是事务非独立执行时发生的一种现象。例如事务T1对一个表中所有的行的某个数据项做了从“1”修改为“2”的操作，这时事务T2又对这个表中插入了一行数据项，而这个数据项的数值还是为“1”并且提交给数据库。而操作事务T1的用户如果再查看刚刚修改的数据，会发现还有一行没有修改，其实这行是从事务T2中添加的，就好像产生幻觉一样，这就是发生了幻读。

幻读和不可重复读都是读取了另一条已经提交的事务（这点就脏读不同），所不同的是不可重复读查询的都是同一个数据项，而幻读针对的是一批数据整体（比如数据的个数）。
　　
一边查询，另一边做insert操作

  1. 事务1                                              事务2
  2. begin                                              begin
  3. 查询到10,20,30,40条记录
  4.                                                    insert 50号部门
  5.                                                    commit
  6. insert 50号部门 报错，主键冲突

要避免脏读、不可重复读、幻读：将隔离级别提高到序列化读
所谓的序列化读就是把多版本并发退化到锁的并发控制：select语句上会被偷偷加上共享锁

现在来看看MySQL数据库为我们提供的四种隔离级别：

① Serializable (串行化)：可避免脏读、不可重复读、幻读的发生。

② Repeatable read (可重复读)：可避免脏读、不可重复读的发生。

③ Read committed (读已提交)：可避免脏读的发生。

④ Read uncommitted (读未提交)：最低级别，任何情况都无法保证。

事务四特性 (ACID)

原子性 A 多个sql要作为一个整体运行，不可分割
一致性 C 一个事务内结果应当一致
隔离性 I
持久性 D 事务一旦提交，事务内的修改就应当永久生效

4. 查询性能提升

提升查询性能最有效的方法，建立数据库索引
索引是对现有的数据进行排序，在排序的结果中搜索，效率会很高
索引的数据结构是:B+树
create index 索引名 on 表(列);
// 向 big 的name 建立一个索引
create index idx_name on big(name);

mysql的主键会自动创建索引，用主键作为查询条件，搜索速度最快

https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

索引使用的注意事项：

  • 使用了空间换了时间，会占用额外存储
  • 索引提升查询性能的同时，影响数据的增删改( 在这张表的查询远高于增删改时，建立索引有意义 )
  • null 值不适合索引，要加索引的列上一般添加一个not null约束, 并给一个默认值
  • 区分度低的列不适合建索引
    男，女
    50 50
    索引值和总记录数的比例越小，区分度就高
    3 / 30万
  • mysql 主键索引(聚簇索引， 把这一行的值都存储到叶子节点上) 和 普通索引（叶子节点只存储了主键的值和索引列的值）
    所以查询普通索引时，会搜索两遍，首先走普通索引，再走主键索引，（称为回表）

避免回表的办法，1. 不用滥用select * ， 2. 建立复合（组合）索引

create index idx_name on big(name, sex, age);
先按name排序，name相同的再按sex排序

  1. where name='张1' and sex = '男'
  2.                         最左匹配规则
  • 从大量的数据中找到少量数据时，才能充分利用索引

删除索引

语法：

  1. alter table 表名 drop key 索引名
  2. alter table big drop key idx_name;

5. sql语句的执行流程

  1. sql 发送给数据库服务器
    连接层
  2. 管理连接，验证连接是否有效（连接池）
    服务层

  3. 查询缓存处理，缓存中已有的数据直接返回, 从mysql 8.0开始，这个功能没有了
    select * from student 10 条学生记录放入了查询缓存

    1. insert ... 
    2.  update
    3.  delete

  4. sql 需要词法分析、语法分析，生成语法解析树 – 由分析器完成
    select * from student where id = 1;
    select * from student where id = 2;
    select * from student where id = 3;

    select * from student where id = ?
    ? 1
    ? 2
    mysql 中默认没有打开这个功能，需要打开开关：
    useServerPrepStmts=true
    cachePrepStmts=true
    prepStmtCacheSize=25

  5. 进行sql的优化处理，选择索引 – 由优化器
  6. 调用存储引擎 – 执行器
    存储引擎层
  7. 去索引搜索，读取磁盘数据
    mysql 默认的存储引擎是InnoDB( 支持事务，支持外键，聚簇索引按（主键顺序排列，在叶子节点存储所有记录） )
    MyISAM( 不支持事务，不支持外键, 不支持灾难恢复 )
    Memory( 内存 , hash索引, 只能支持等值比较)

其他sql总结 （了解）

查看有哪些库: show databases;
查看库的创建语句： show create database 库名;
使用库： use 库名;
查看有哪些表： show tables;
查看表结构： desc 表名;
查看建表语句： show create table 表名;
\G 可以取代; 效果是把表行转列
查看系统变量的值： select @@系统变量名;

select @@transaction_isolation; // 查看事务隔离级别
select @@port; // 查看服务器端口号
select @@character_set_server; // 查看默认字符集
select @@secure_file_priv;
…

发表评论

表情：
评论列表 （有 0 条评论，394人围观）

还没有评论，来说两句吧...

相关阅读

    相关 mysql基本操作

    之前当然包括现在经常想学一些高大上的关于mysql的操作或优化管理技巧，其实有这些想法是好的，但往往忽略一些问题，那些基本的操作都熟练掌握了吗？如果这些都办不到，基本可以确定是

    我就是我我就是我/ 0 赞/ 357 阅读

    相关 MySql基本操作

    一、MySQL服务器的启动与关闭 1、启动MySQL服务器 开始 -> 运行   输入“cmd”，然后在命令提示符下输入“net start MySQL”指令。 2、连接

    墨蓝墨蓝/ 0 赞/ 336 阅读