MySQl的库操作、表操作和数据操作

Wesley13
• 阅读 444

一、库操作

  1.1库的增删改查

  (1)系统数据库:

  MySQl的库操作、表操作和数据操作

  performance_schema:用来收集数据库服务器的性能参数,记录处理查询时发生的各种事件、锁等现象

  mysql:授权库,主要存储系统用户的权限信息

  test:MySQl数据库系统自动创建的测试数据库

  (2)数据库操作

  创建:create database db1 charset utf8;

  (数据库命名规则:可以是字母、数字、下划线等的组合,不能单独使用数字,不能使用关键字例如create select等)

  查看:show databases;  或者 show create database db1; 

  选择数据库:use db1;

  删除:drop database db1;

  修改:alter database db1 charset utf8;

二、表操作

  2.1存储引擎介绍

  (1)类似于处理文本用txt类型,图片用png,音乐用mp3类型,数据库的表也有不同类型。表类型(存储和操作此表的类型)又称存储引擎。MySQL数据库提供了多种存储引    擎。

  (2)查看MySQL支持的存储引擎:

    show engines\G  #查看所有支持的存储引擎

    MySQl的库操作、表操作和数据操作

    show variables like 'storage_engine%';  #查看正在使用的存储引擎

    MySQl的库操作、表操作和数据操作

  (3)部分存储引擎的简单介绍:有InnoDB、MyISAM、NDB、Memory、Infobright、NTSE、BLACKHOLE等;其中InnoDB是MySQL默认和最常用的一个存储引擎,具备高可用性、高性能以及高可扩展性。其他详情点击:具体介绍

  (4)使用存储引擎

    建表时指定

    create table innodb_t1(id int,name char)engine=innodb;

    show create table innodb_t1;

2.2表的增删改查

(1)表介绍

    MySQl的库操作、表操作和数据操作

   (2)创建表 create table student(sid int(11),sname char(10),gender enum('男','女'),class_id int(11));

       查看库下所有表  show tables;

       往表中插入数据  insert into student values(10,'nuo','女',5);

   (3)查看表结构  desc student;  或者 show create table student\G;  #查看详细表结构,可加\G

      MySQl的库操作、表操作和数据操作

   (4)修改表结构

    (4.1)修改存储引擎  alter table student engine=NDB;

    (4.2)添加字段  alter table student add age int not null;

    (4.3)删除字段  alter table student drop age;

    (4.4)修改字段类型  alter table student modify age int(9) not null primary key;

    (4.5)删除主键  alter table student drop primary key;

   (5)复制表

`复制表结构+记录 (key不会复制: 主键、外键和索引)     mysql> create table new_service select * from service;

    只复制表结构     mysql> select * from service where 1=2; //条件为假,查不到任何记录     Empty set (0.00 sec)     mysql> create table new1_service select * from service where 1=2;
    Query OK, 0 rows affected (0.00 sec)     Records: 0 Duplicates: 0 Warnings: 0

    mysql> create table t4 like employees;

  (6)删除表 drop table student;`

2.3数据类型

mysql常用数据类型有 数值类型 int , float 等、字符串类型 char varchar 、日期类型 datatime等、枚举类型和集合类型

(1)数值类型

MySQl的库操作、表操作和数据操作

(2)日期类型

MySQl的库操作、表操作和数据操作

MySQl的库操作、表操作和数据操作

MySQl的库操作、表操作和数据操作

(3)字符串类型 char 和 varchar

MySQl的库操作、表操作和数据操作

(char定长,varchar不定长,虽然varchar使用起来较为灵活,但是从整个系统的性能角度来说,char数据类型的处理速度更快,有时甚至可以超出varchar处理速度的      50%。因此,在选择时,应该综合考虑,以求达到最佳的平衡。)

(4)枚举类型和集合类型

枚举enum(),集合set();枚举单选,集合可多选;

    MySQl的库操作、表操作和数据操作

2.4完整性约束

not null (非空)、default (默认)、primary key(主键,唯一)、foreign key(外键)、unique(唯一)、auto_increment(自增,整数类型且为主键)

    (1)设置唯一约束unique:  

MySQl的库操作、表操作和数据操作 MySQl的库操作、表操作和数据操作

方法一:
create table department1(
id int,
name varchar(20) unique,
comment varchar(100)
);


方法二:
create table department2(
id int,
name varchar(20),
comment varchar(100),
constraint uk_name unique(name)
);

create table service(
id int primary key auto_increment,
name varchar(20),
host varchar(15) not null,
port int not null,
unique(host,port) #联合唯一
);

View Code

    (2)primary key

MySQl的库操作、表操作和数据操作 MySQl的库操作、表操作和数据操作

 1 # 单列做主键
 2 #方法一:not null+unique
 3 create table department1(
 4 id int not null unique, #主键
 5 name varchar(20) not null unique,
 6 comment varchar(100)
 7 );
 8 
 9 #方法二:在某一个字段后用primary key
10 create table department2(
11 id int primary key, #主键
12 name varchar(20),
13 comment varchar(100)
14 );
15 
16 #方法三:在所有字段后单独定义primary key
17 create table department3(
18 id int,
19 name varchar(20),
20 comment varchar(100),
21 constraint pk_name primary key(id); #创建主键并为其命名pk_name

单列做主键

MySQl的库操作、表操作和数据操作 MySQl的库操作、表操作和数据操作

1 create table service(
2 ip varchar(15),
3 port char(5),
4 service_name varchar(10) not null,
5 primary key(ip,port)
6 );

多列做主键

    (3)foreign key

MySQl的库操作、表操作和数据操作 MySQl的库操作、表操作和数据操作

#表类型必须是innodb存储引擎,且被关联的字段,即references指定的另外一个表的字段,必须保证唯一
create table department(
id int primary key,
name varchar(20) not null
)engine=innodb;

#dpt_id外键,关联父表(department主键id),同步更新,同步删除
create table employee(
id int primary key,
name varchar(20) not null,
dpt_id int,
constraint fk_name foreign key(dpt_id)
references department(id)
on delete cascade
on update cascade 
)engine=innodb;


#先往父表department中插入记录
insert into department values
(1,'欧德博爱技术有限事业部'),
(2,'艾利克斯人力资源部'),
(3,'销售部');


#再往子表employee中插入记录
insert into employee values
(1,'egon',1),
(2,'alex1',2),
(3,'alex2',2),
(4,'alex3',2),
(5,'李坦克',3),
(6,'刘飞机',3),
(7,'张火箭',3),
(8,'林子弹',3),
(9,'加特林',3)
;

View Code

    (4)auto_increment

MySQl的库操作、表操作和数据操作 MySQl的库操作、表操作和数据操作

#不指定id,则自动增长
create table student(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') default 'male'
);


#应该用truncate清空表,比起delete一条一条地删除记录,truncate是直接清空表,在删除大表时用它
mysql> truncate student;
Query OK, 0 rows affected (0.01 sec)


#设置步长
sqlserver:自增步长
    基于表级别
    create table t1(
        id int。。。
    )engine=innodb,auto_increment=2 步长=2 default charset=utf8

mysql自增的步长:
    show session variables like 'auto_inc%';

    #基于会话级别
    set session auth_increment_increment=2 #修改会话级别的步长

    #基于全局级别的
    set global auth_increment_increment=2 #修改全局级别的步长(所有会话都生效)



清空表:

delete from t1; #如果有自增id,新增的数据,仍然是以删除前的最后一样作为起始。

truncate table t1;数据量大,删除速度比上一条快,且直接从零开始,

View Code

三、数据操作

  3.1数据的增删改

   假设已经建了一张表student

    增加:insert into student values

        (值1,值2,值3...),

        (值1,值2,值3...),

        (值1,值2,值3...);

    删除:delete from student

        where condition

    更新:update student set

        字段1=值1,

        字段2=值2,

        where condition;

  3.2单表查询

   一、单表查询基本语法

    select 字段1,字段2... from 表名

      where 条件

      group by field

      having 筛选

      order by field

      limit 限制条数

   二、关键字在执行中的优先级

    重点中的重点:关键字的执行优先级

      from

      where

      group by

      having

      select

      distinct   去重

      order by

      limit

   三、where约束

      1、比较运算符:> < >=  <= <>  !=

      2、between 80 and 100 值在10到20之间

      3、in(80,90,100) 值是10或20或30

      4、like 'egon%'
        pattern可以是%或_,
        %表示任意多字符
        _表示一个字符

      5、逻辑运算符:在多个条件直接可以使用逻辑运算符 and or not

   四、group by 分组

    如果我们用unique的字段作为分组的依据,则每一条记录自成一组,这种分组没有意义     多条记录之间的某个字段值相同,该字段通常用来作为分组的依据

   五、聚合函数

    #强调:聚合函数聚合的是组的内容,若是没有分组,则默认一组

    示例:

      SELECT COUNT(*) FROM employee;

      SELECT COUNT(*) FROM employee WHERE depart_id=1;

      SELECT MAX(salary) FROM employee;

      SELECT MIN(salary) FROM employee;

      SELECT AVG(salary) FROM employee;

      SELECT SUM(salary) FROM employee;

      SELECT SUM(salary) FROM employee WHERE depart_id=3; 

   六、having

    having和where不一样的地方在于:

      1、执行优先级从高到低  where > group by > having

      2、where 在group by 之前,因此where中可以有任意字段,但是绝对不可以使用聚       合函数

      3、having发生在分组group by之后,因此可以使用分组的字段,无法直接取到其他       字段,可以使用聚合函数。

   七、order by 查询排序

      按单列排序:

      SELECT * FROM employee ORDER BY salary;

      SELECT * FROM employee ORDER BY salary ASC;

      SELECT * FROM employee ORDER BY salary DESC;

      按多列排序:先按照age排序,如果年纪相同,则按照薪资排序

      SELECT * from employee

      ORDER BY age, salary DESC;

   八、使用正则表达式查询

      SELECT * FROM employee WHERE name REGEXP '^ale';

      SELECT * FROM employee WHERE name REGEXP 'on$';

      SELECT * FROM employee WHERE name REGEXP 'm{2}';

      小结:

      对字符串匹配的方式

      WHERE name = 'egon';

      WHERE name LIKE 'yua%';

      WHERE name REGEXP 'on$';

  3.3多表查询

    多表连接查询

    符合条件连接查询

    子查询

    一、多表连接查询

      select 字段 

        from 表一  inner|left|right  join 表二

        on 表一.字段 = 表二.字段;

      (1)交叉连接:不适用任何匹配条件,生成笛卡尔积

        假设我们已经生成employee,department两张表

        select * from employee,department;

       (2) 内连接:只连接匹配的行

        select employee.id department.name 

          from employee inner join department

          on employee.id = department.id;

       (3)外连接之左连接:优先显示左表全部记录

        select employee.id department.name 

          from employee left join department

          on employee.id = department.id;

       (4)外连接之右连接:优先显示右表全部记录

        right join ,其他参考左连接。

       (5)全外连接 :显示左右两个表全部记录

        select * from employee left join department on employee.dep_id = department.id

        union

        select * from employee right join department on employee.dep_id = department.id;

   二、符合条件连接查询

     #示例:以内连接的方式查询employee和department表,并且以age字段的升序方式显示

      select employee.id,employee.name,employee.age,department.name

        from employee,department

        where employee.dep_id = department.id and age > 25

        order by age asc;   

   三、子查询

      # 子查询是将一个查询语句嵌套在另一个查询语句中。

      #2:内层查询语句的查询结果,可以为外层查询语句提供查询条件。

      #3:子查询中可以包含:IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字

      #4:还可以包含比较运算符:= 、 !=、> 、<等

MySQl的库操作、表操作和数据操作 MySQl的库操作、表操作和数据操作

1 带IN关键字的子查询
#查询平均年龄在25岁以上的部门名
select id,name from department
    where id in 
        (select dep_id from employee group by dep_id having avg(age) > 25);

#查看技术部员工姓名
select name from employee
    where dep_id in 
        (select id from department where name='技术');

#查看不足1人的部门名
select name from department
    where id in 
        (select dep_id from employee group by dep_id having count(id) <=1);
2 带比较运算符的子查询
#比较运算符:=、!=、>、>=、<、<=、<>
#查询大于所有人平均年龄的员工名与年龄
mysql> select name,age from emp where age > (select avg(age) from emp);
+---------+------+
| name | age |
+---------+------+
| alex | 48 |
| wupeiqi | 38 |
+---------+------+
rows in set (0.00 sec)


#查询大于部门内平均年龄的员工名、年龄
select t1.name,t1.age from emp t1
inner join 
(select dep_id,avg(age) avg_age from emp group by dep_id) t2
on t1.dep_id = t2.dep_id
where t1.age > t2.avg_age;
3 带EXISTS关键字的子查询
EXISTS关字键字表示存在。在使用EXISTS关键字时,内层查询语句不返回查询的记录。
而是返回一个真假值。True或False
当返回True时,外层查询语句将进行查询;当返回值为False时,外层查询语句不进行查询
#department表中存在dept_id=203,Ture
mysql> select * from employee
    ->     where exists
    ->         (select id from department where id=200);
+----+------------+--------+------+--------+
| id | name       | sex    | age  | dep_id |
+----+------------+--------+------+--------+
|  1 | egon       | male   |   18 |    200 |
|  2 | alex       | female |   48 |    201 |
|  3 | wupeiqi    | male   |   38 |    201 |
|  4 | yuanhao    | female |   28 |    202 |
|  5 | liwenzhou  | male   |   18 |    200 |
|  6 | jingliyang | female |   18 |    204 |
+----+------------+--------+------+--------+

#department表中存在dept_id=205,False
mysql> select * from employee
    ->     where exists
    ->         (select id from department where id=204);
Empty set (0.00 sec)

View Code

点赞
收藏
评论区
推荐文章
blmius blmius
3年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时,报错:原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。全局s
皕杰报表之UUID
​在我们用皕杰报表工具设计填报报表时,如何在新增行里自动增加id呢?能新增整数排序id吗?目前可以在新增行里自动增加id,但只能用uuid函数增加UUID编码,不能新增整数排序id。uuid函数说明:获取一个UUID,可以在填报表中用来创建数据ID语法:uuid()或uuid(sep)参数说明:sep布尔值,生成的uuid中是否包含分隔符'',缺省为
待兔 待兔
4个月前
手写Java HashMap源码
HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程22
Jacquelyn38 Jacquelyn38
3年前
2020年前端实用代码段,为你的工作保驾护航
有空的时候,自己总结了几个代码段,在开发中也经常使用,谢谢。1、使用解构获取json数据let jsonData  id: 1,status: "OK",data: 'a', 'b';let  id, status, data: number   jsonData;console.log(id, status, number )
Stella981 Stella981
3年前
Python3:sqlalchemy对mysql数据库操作,非sql语句
Python3:sqlalchemy对mysql数据库操作,非sql语句python3authorlizmdatetime2018020110:00:00coding:utf8'''
Stella981 Stella981
3年前
KVM调整cpu和内存
一.修改kvm虚拟机的配置1、virsheditcentos7找到“memory”和“vcpu”标签,将<namecentos7</name<uuid2220a6d1a36a4fbb8523e078b3dfe795</uuid
Wesley13 Wesley13
3年前
mysql设置时区
mysql设置时区mysql\_query("SETtime\_zone'8:00'")ordie('时区设置失败,请联系管理员!');中国在东8区所以加8方法二:selectcount(user\_id)asdevice,CONVERT\_TZ(FROM\_UNIXTIME(reg\_time),'08:00','0
Wesley13 Wesley13
3年前
00:Java简单了解
浅谈Java之概述Java是SUN(StanfordUniversityNetwork),斯坦福大学网络公司)1995年推出的一门高级编程语言。Java是一种面向Internet的编程语言。随着Java技术在web方面的不断成熟,已经成为Web应用程序的首选开发语言。Java是简单易学,完全面向对象,安全可靠,与平台无关的编程语言。
Wesley13 Wesley13
3年前
MySQL部分从库上面因为大量的临时表tmp_table造成慢查询
背景描述Time:20190124T00:08:14.70572408:00User@Host:@Id:Schema:sentrymetaLast_errno:0Killed:0Query_time:0.315758Lock_
为什么mysql不推荐使用雪花ID作为主键
作者:毛辰飞背景在mysql中设计表的时候,mysql官方推荐不要使用uuid或者不连续不重复的雪花id(long形且唯一),而是推荐连续自增的主键id,官方的推荐是auto_increment,那么为什么不建议采用uuid,使用uuid究
Python进阶者 Python进阶者
10个月前
Excel中这日期老是出来00:00:00,怎么用Pandas把这个去除
大家好,我是皮皮。一、前言前几天在Python白银交流群【上海新年人】问了一个Pandas数据筛选的问题。问题如下:这日期老是出来00:00:00,怎么把这个去除。二、实现过程后来【论草莓如何成为冻干莓】给了一个思路和代码如下:pd.toexcel之前把这