MySQL基础

还是接上次MySQL基础的文章，这次我们从最基本的建表语句开始，不要多想，只要照做就行。

一、数据库DDL语言

1、建表

MySQL建表语法为：

  create table <表名> （字段1 数据类型（）[其他关键字] [约束条件] [comment ' ']，字段2 数据类型（）[其他关键字] [约束条件] [comment ' ']，字段3 数据类型（）[其他关键字] [约束条件] [comment ' ']）;

下面是实际的建表，后面的所有操作均是以这张表为基础。

create table emp(id int comment '员工编号',    workno varchar(10) comment '员工工号',name varchar(10) comment '员工姓名',    gender char(1) comment '性别',    age tinyint(10) unsigned comment '年龄',    idcard char(18) comment '身份证号码',    entrydate date comment '入职时间'     )comment '员工表';

 2、DESC查看表的详细结构    

建表后，我们可以查看建表的详细的结构和字段信息，可以用下面的语句。

desc <表名>;

desc全程为：describe，desc是它的缩写，运行后效果如下：

可以查看到表的结构，很是详细。

filed:表示字段名称；

Type：表示数据类型；

Null：表示字段是否为空，yes表示可以为空，no 表示比剋为空；

Key：索引列，这一列一般会有四种状态：' '，'PRI'，'UNI'，'MUL'。

• ' '：该列值非索引列，可以重复。；

• 'PRI'：该列是主键列，不能为空；

• 'UNI'：唯一主键列，不能为空；

• 'MUL'：该列的值可以重复。

Default：默认值；

Extra：其它的信息；

3、新增列

表建好以后，有时候很根据需要再次添加新的一列，这是时候可以使用下面的语句：

alter table <表名> add <要添加的字段名> 数据类型( ) 

下面以emp表为例，给这个表添加一个昵称nickname字段，语句如下：

  alter table emp add nickname varchar(20) comment '昵称' ;

运行后desce后如下：

可以看到，改字段被添加到emp表中。

4、字段的修改

修改字段，比如将nickname修改为username，语法：

alter table <表名> change <旧字段名> <新字段名> 数据类型（）；

下面以emp表为例，把这个的nickname字段修改为username，语句如下：

  alter table emp change nickname username varchar(30) comment'用户名';

运行后desce后如下：

可以看到，字段名已经修改。

5、修改表名

  alter table emp rename to employee;

执行这条语句后，效果如下：

很明显，emp已经不存在；

使用show tables可以查看现在的表。

  show tables;

运行后如下：

6、添加字段

表建好之后，有时候需要新增一个字段，这时候使用下面的语句：

语法：alter table <表名> add <字段名> <数据类型>(长度);

比如，给foodtable新增一个week字段，语句如下：

  alter table foodtable add week char(2) comment'星期';

运行后如下：

二、数据库DQL语言

7、条件查询

基本语法：select 字段列表 from 表名 条件列表；

where后面可以跟的条件有，比较运算符和逻辑运算符

比较运算符	功能
>	大于
<
>=
<=	小于等于
<> 、!=
between...and...	在...范围内(包含最大最小值)
in(...)	多选一
like	占位符
is null	是null

还有：

逻辑运算符	功能
and 、&&	并且（多个条件同时成立）
or 、||	或者（多个条件任意一个成立）
not 、！	非，不是

知道了这些，我们可以操作一些复杂的查询，比如：

给定emp表，并 有如下数据(数据全部为虚拟，如有巧合纯属雷同)，看到的这些数据其实是局部的数据，实际一共有11000条这样子的数据：

① 现在有如下需求：

查询emp表中身份证号码以X结尾的占比是多少，并以百分比的形式展示？

我的思路是：

先查询身份证号总数量，直接使用conut()函数，然后在统计出x结尾身份证数量，最后计算出占比。

思路很简单，但写起来不是那么容易。

SELECT    t.n AS 身份证号总数量 ,    t.d AS x结尾身份证数量 , (concat( TRUNCATE (( t.d / t.n )* 100, 2 ), '%' )) AS 占比 FROM    (SELECTcount( NAME ) AS n,(SELECTcount( idcard ) AS 尾号 x FROM        emp WHERE        idcard LIKE '%x'      ) AS d FROM      emp     ) AS t;

运行这条语句后，效果如下：

② 查询年龄20到21之间的员工信息，语句如下：

  select * from emp where  age between 20 and 21;

执行后：

③ 计算司龄超过3年的员工的信息只要前10条记录；

  select *   from  (  select *, round(((select datediff(now(),entrydate))/365),0)as 司龄    from emp   )   as t1   where t1.司龄 >=3;

执行后：

④ 分组查询

分组查询使用group by 关键字；

 select gender,count(gender) from emp group by gender limit 30;

效果如下：

三、多表联查

新建comp表。

comp表作为存储员工的公司信息的表，建表语句如下：

 create table comp (    cid int not null comment '公司编号',    company varchar(255) default null comment '公司名',    dept varchar(255) default null comment '部门',    eid int not null comment '员工编号',    primary key (cid),  key eid (eid),  constraint eid foreign key (eid) references emp (id))  comment '员工公的司表';

这个表的数据，我们采用 自动给生成的，很便捷就会有一万条数据。

新建一个表listed。

listed表里面放截至2022年10月20日中国境内的上市公司名单。

建表语句如下：

create table listed (  sid int default null comment '公司信息编号',  date date default null comment '核发日期',  name varchar(255) default null comment '公司名',  local varchar(255) default null comment '所在地',  number double default null comment '发行总数',  seal_bus varchar(255) default null comment '主承销商',  stocks_short varchar(255) default null comment '股票简称',  stocks_number int default null comment '股票代码',  listed_date date default null comment '上市日期',  cid int default null comment 'cid',  key cid (cid), constraint cid foreign key (cid) references comp (cid))  comment '上市公司信息表';

然后插入数据：

insert into listed values(  1   ,  "  2022/10/13  "  ,  "  福建南方路面机械  "  ,  "  浙江  "  ,  2710.17   ,  "  兴业证券  "  ,  "  南方路机  "  ,  603280.00   ,  "    "  ,  60   );insert into listed values(  2   ,  "  2022/9/29  "  ,  "  浙江云中马  "  ,  "  浙江  "  ,  3500.00   ,  "  中信建投证券  "  ,  "  云中马  "  ,  603130.00   ,  "    "  ,  59   );insert into listed values(  3   ,  "  2022/9/22  "  ,  "  青岛三柏硕健康科技  "  ,  "  青岛  "  ,  6094.40   ,  "  中信建投证券  "  ,  "  三柏硕  "  ,  1300.00   ,  "  2022/10/19  "  ,  58   );insert into listed values(  4   ,  "  2022/9/22  "  ,  "  山东邦基科技  "  ,  "  山东  "  ,  4200.00   ,  "  中信证券  "  ,  "  邦基科技  "  ,  603151.00   ,  "  2022/10/19  "  ,  57   );insert into listed values(  5   ,  "  2022/9/16  "  ,  "  陕西美能清洁能源集团  "  ,  "  陕西  "  ,  4690.00   ,  "  西部证券  "  ,  "  美能能源  "  ,  1299.00   ,  "  2022/10/19  "  ,  56   );insert into listed values(  6   ,  "  2022/9/8  "  ,  "  深圳市好上好信息科技  "  ,  "  深圳  "  ,  2400.00   ,  "  国信证券  "  ,  "  好上好  "  ,  1298.00   ,  "  2022/10/19  "  ,  55   );insert into listed values(  7   ,  "  2022/9/8  "  ,  "  苏州可川电子科技  "  ,  "  江苏  "  ,  1720.00   ,  "  中信证券  "  ,  "  可川科技  "  ,  603052.00   ,  "  2022/10/11  "  ,  54   );insert into listed values(  8   ,  "  2022/9/1  "  ,  "  箭牌家居集团  "  ,  "  广东  "  ,  9899.67   ,  "  中信证券  "  ,  "  箭牌家居  "  ,  1322.00   ,  "  2022/10/11  "  ,  53   );insert into listed values(  9   ,  "  2022/9/1  "  ,  "  无锡化工装备  "  ,  "  江苏  "  ,  2000.00   ,  "  兴业证券  "  ,  "  锡装股份  "  ,  1332.00   ,  "  2022/9/20  "  ,  52   );insert into listed values(  10   ,  "  2022/9/1  "  ,  "  上海紫燕食品  "  ,  "  上海  "  ,  4200.00   ,  "  广发证券  "  ,  "  紫燕食品  "  ,  603057.00   ,  "  2022/9/26  "  ,  51   );insert into listed values(  11   ,  "  2022/8/25  "  ,  "  浙江博菲电气  "  ,  "  浙江  "  ,  2000.00   ,  "  财通证券  "  ,  "  博菲电气  "  ,  1255.00   ,  "  2022/9/30  "  ,  50   );insert into listed values(  12   ,  "  2022/8/25  "  ,  "  浙江正特  "  ,  "  浙江  "  ,  2750.00   ,  "  国泰君安证券  "  ,  "  浙江正特  "  ,  1238.00   ,  "  2022/9/19  "  ,  49   );insert into listed values(  13   ,  "  2022/8/25  "  ,  "  圣晖系统集成集团  "  ,  "  江苏  "  ,  2000.00   ,  "  东吴证券  "  ,  "  圣晖集成  "  ,  603163.00   ,  "  2022/10/13  "  ,  48   );insert into listed values(  14   ,  "  2022/8/18  "  ,  "  山东嘉华生物科技  "  ,  "  山东  "  ,  4114.00   ,  "  国泰君安证券  "  ,  "  嘉华股份  "  ,  603182.00   ,  "  2022/9/9  "  ,  47   );insert into listed values(  15   ,  "  2022/8/18  "  ,  "  深圳市豪鹏科技  "  ,  "  深圳  "  ,  2000.00   ,  "  安信证券  "  ,  "  豪鹏科技  "  ,  1283.00   ,  "  2022/9/5  "  ,  46   );insert into listed values(  16   ,  "  2022/8/18  "  ,  "  内蒙古欧晶科技  "  ,  "  内蒙古  "  ,  3435.64   ,  "  国信证券  "  ,  "  欧晶科技  "  ,  1269.00   ,  "  2022/9/30  "  ,  45   );insert into listed values(  17   ,  "  2022/8/11  "  ,  "  浙江五芳斋实业  "  ,  "  浙江  "  ,  2518.58   ,  "  浙商证券  "  ,  "  五芳斋  "  ,  603237.00   ,  "  2022/8/31  "  ,  44   );insert into listed values(  18   ,  "  2022/8/11  "  ,  "  北京利仁科技  "  ,  "  北京  "  ,  1848.44   ,  "  安信证券  "  ,  "  利仁科技  "  ,  1259.00   ,  "  2022/8/31  "  ,  43   );insert into listed values(  19   ,  "  2022/7/28  "  ,  "  胜通能源  "  ,  "  山东  "  ,  3000.00   ,  "  国元证券  "  ,  "  胜通能源  "  ,  1331.00   ,  "  2022/9/8  "  ,  42   );insert into listed values(  20   ,  "  2022/7/28  "  ,  "  博纳影业集团  "  ,  "  新疆  "  ,  2749.04   ,  "  华龙证券  "  ,  "  博纳影业  "  ,  1330.00   ,  "  2022/8/18  "  ,  41   );insert into listed values(  21   ,  "  2022/7/21  "  ,  "  深圳市智微智能科技  "  ,  "  深圳  "  ,  6175.00   ,  "  中信证券  "  ,  "  智微智能  "  ,  1339.00   ,  "  2022/8/12  "  ,  40   );insert into listed values(  22   ,  "  2022/7/21  "  ,  "  温州源飞宠物玩具制品  "  ,  "  浙江  "  ,  3410.00   ,  "  光大证券  "  ,  "  源飞宠物  "  ,  1222.00   ,  "  2022/8/18  "  ,  39   );insert into listed values(  23   ,  "  2022/7/14  "  ,  "  广东魅视科技  "  ,  "  广东  "  ,  2500.00   ,  "  民生证券  "  ,  "  魅视科技  "  ,  1229.00   ,  "  2022/8/8  "  ,  38   );insert into listed values(  24   ,  "  2022/7/14  "  ,  "  常熟通润汽车零部件  "  ,  "  江苏  "  ,  1987.33   ,  "  国金证券  "  ,  "  常润股份  "  ,  603201.00   ,  "  2022/7/29  "  ,  37   );insert into listed values(  25   ,  "  2022/7/14  "  ,  "  农心作物科技  "  ,  "  陕西  "  ,  2500.00   ,  "  华泰联合证券  "  ,  "  农心科技  "  ,  1231.00   ,  "  2022/8/19  "  ,  36   );insert into listed values(  26   ,  "  2022/7/14  "  ,  "  辽宁鼎际得石化  "  ,  "  辽宁  "  ,  3336.67   ,  "  海通证券  "  ,  "  鼎际得  "  ,  603255.00   ,  "  2022/8/18  "  ,  35   );insert into listed values(  27   ,  "  2022/6/30  "  ,  "  新疆立新能源  "  ,  "  新疆  "  ,  23333.34   ,  "  申万宏源证券  "  ,  "  立新能源  "  ,  1258.00   ,  "  2022/7/27  "  ,  34   );insert into listed values(  28   ,  "  2022/6/30  "  ,  "  杭州楚环科技  "  ,  "  浙江  "  ,  2009.35   ,  "  民生证券  "  ,  "  楚环科技  "  ,  1336.00   ,  "  2022/7/25  "  ,  33   );insert into listed values(  29   ,  "  2022/6/30  "  ,  "  晋拓科技  "  ,  "  上海  "  ,  6795.20   ,  "  海通证券  "  ,  "  晋拓股份  "  ,  603211.00   ,  "  2022/7/25  "  ,  32   );insert into listed values(  30   ,  "  2022/6/23  "  ,  "  弘业期货  "  ,  "  江苏  "  ,  11077.78   ,  "  中信建投证券  "  ,  "  弘业期货  "  ,  1236.00   ,  "  2022/8/5  "  ,  31   );insert into listed values(  31   ,  "  2022/6/23  "  ,  "  劲旅环境科技  "  ,  "  安徽  "  ,  2784.83   ,  "  国元证券  "  ,  "  劲旅环境  "  ,  1230.00   ,  "  2022/7/15  "  ,  30   );insert into listed values(  32   ,  "  2022/6/23  "  ,  "  上海宝立食品科技  "  ,  "  上海  "  ,  4001.00   ,  "  国泰君安证券  "  ,  "  宝立食品  "  ,  603170.00   ,  "  2022/7/15  "  ,  29   );insert into listed values(  33   ,  "  2022/6/16  "  ,  "  深圳市德明利技术  "  ,  "  深圳  "  ,  2000.00   ,  "  东莞证券  "  ,  "  德明利  "  ,  1309.00   ,  "  2022/7/15  "  ,  28   );insert into listed values(  34   ,  "  2022/6/16  "  ,  "  江西天新药业  "  ,  "  江西  "  ,  4378.00   ,  "  中信证券  "  ,  "  天新药业  "  ,  603235.00   ,  "  2022/7/12  "  ,  27   );insert into listed values(  35   ,  "  2022/6/9  "  ,  "  广东扬山联合精密制造  "  ,  "  广东  "  ,  2698.33   ,  "  光大证券  "  ,  "  联合精密  "  ,  1268.00   ,  "  2022/6/30  "  ,  26   );insert into listed values(  36   ,  "  2022/6/2  "  ,  "  慕思健康睡眠  "  ,  "  广东  "  ,  4001.00   ,  "  招商证券  "  ,  "  慕思股份  "  ,  1323.00   ,  "  2022/6/23  "  ,  25   );insert into listed values(  37   ,  "  2022/5/26  "  ,  "  北京福元医药  "  ,  "  北京  "  ,  12000.00   ,  "  中信建投证券  "  ,  "  福元医药  "  ,  601089.00   ,  "  2022/6/30  "  ,  24   );insert into listed values(  38   ,  "  2022/5/19  "  ,  "  润贝航空科技  "  ,  "  深圳  "  ,  2000.00   ,  "  国信证券  "  ,  "  润贝航科  "  ,  1316.00   ,  "  2022/6/24  "  ,  23   );insert into listed values(  39   ,  "  2022/5/12  "  ,  "  浙江铖昌科技  "  ,  "  浙江  "  ,  2795.35   ,  "  国信证券  "  ,  "  铖昌科技  "  ,  1270.00   ,  "  2022/6/6  "  ,  22   );insert into listed values(  40   ,  "  2022/4/22  "  ,  "  江西阳光乳业  "  ,  "  江西  "  ,  7070.00   ,  "  东兴证券  "  ,  "  阳光乳业  "  ,  1318.00   ,  "  2022/5/20  "  ,  21   );insert into listed values(  41   ,  "  2022/4/22  "  ,  "  安徽拓山重工  "  ,  "  安徽  "  ,  1866.67   ,  "  民生证券  "  ,  "  拓山重工  "  ,  1226.00   ,  "  2022/6/22  "  ,  20   );insert into listed values(  42   ,  "  2022/4/22  "  ,  "  武汉里得电力科技  "  ,  "  湖北  "  ,  2121.00   ,  "  中原证券  "  ,  "  里得电科  "  ,  1235.00   ,  "  2022/6/22  "  ,  19   );insert into listed values(  43   ,  "  2022/4/21  "  ,  "  浙江联翔智能家居  "  ,  "  浙江  "  ,  2590.68   ,  "  中信建投证券  "  ,  "  联翔股份  "  ,  603272.00   ,  "  2022/5/20  "  ,  18   );insert into listed values(  44   ,  "  2022/4/14  "  ,  "  江苏华辰变压器  "  ,  "  江苏  "  ,  4000.00   ,  "  甬兴证券  "  ,  "  江苏华辰  "  ,  603097.00   ,  "  2022/5/12  "  ,  17   );insert into listed values(  45   ,  "  2022/4/14  "  ,  "  铭科精技控股  "  ,  "  广东  "  ,  3535.00   ,  "  华林证券  "  ,  "  铭科精技  "  ,  1319.00   ,  "  2022/5/12  "  ,  16   );insert into listed values(  46   ,  "  2022/4/8  "  ,  "  永泰运化工物流  "  ,  "  宁波  "  ,  2597.00   ,  "  安信证券  "  ,  "  永泰运  "  ,  1228.00   ,  "  2022/4/29  "  ,  15   );insert into listed values(  47   ,  "  2022/4/8  "  ,  "  嘉环科技  "  ,  "  江苏  "  ,  7630.00   ,  "  中金公司  "  ,  "  嘉环科技  "  ,  603206.00   ,  "  2022/5/6  "  ,  14   );insert into listed values(  48   ,  "  2022/3/30  "  ,  "  中国海洋石油  "  ,  "  香港  "  ,  260000.00   ,  "  中信证券  "  ,  "  中国海油  "  ,  600938.00   ,  "  2022/4/21  "  ,  13   );insert into listed values(  49   ,  "  2022/3/30  "  ,  "  中国海洋石油  "  ,  "  香港  "  ,  260000.00   ,  "  中金公司  "  ,  "  中国海油  "  ,  600938.00   ,  "  2022/4/21  "  ,  12   );insert into listed values(  50   ,  "  2022/3/10  "  ,  "  重庆望变电气(集团)  "  ,  "  重庆  "  ,  8329.19   ,  "  中信证券  "  ,  "  望变电气  "  ,  603191.00   ,  "  2022/4/28  "  ,  11   );insert into listed values(  51   ,  "  2022/3/3  "  ,  "  广州鹿山新材料  "  ,  "  广东  "  ,  2300.30   ,  "  中信证券  "  ,  "  鹿山新材  "  ,  603051.00   ,  "  2022/3/25  "  ,  10   );insert into listed values(  52   ,  "  2022/2/24  "  ,  "  成都立航科技  "  ,  "  四川  "  ,  1925.00   ,  "  华西证券  "  ,  "  立航科技  "  ,  603261.00   ,  "  2022/3/15  "  ,  9   );insert into listed values(  53   ,  "  2022/2/24  "  ,  "  深圳市康冠科技  "  ,  "  深圳  "  ,  4248.75   ,  "  华林证券  "  ,  "  康冠科技  "  ,  1308.00   ,  "  2022/3/18  "  ,  8   );insert into listed values(  54   ,  "  2022/2/10  "  ,  "  兴通海运  "  ,  "  福建  "  ,  5000.00   ,  "  中信证券  "  ,  "  兴通股份  "  ,  603209.00   ,  "  2022/3/24  "  ,  7   );insert into listed values(  55   ,  "  2022/2/10  "  ,  "  万控智造  "  ,  "  浙江  "  ,  6000.00   ,  "  国泰君安证券  "  ,  "  万控智造  "  ,  603070.00   ,  "  2022/3/10  "  ,  6   );insert into listed values(  56   ,  "  2022/1/25  "  ,  "  上海宏英智能科技  "  ,  "  上海  "  ,  1836.00   ,  "  中信证券  "  ,  "  宏英智能  "  ,  1266.00   ,  "  2022/2/28  "  ,  5   );insert into listed values(  57   ,  "  2022/1/25  "  ,  "  金徽矿业  "  ,  "  甘肃  "  ,  9800.00   ,  "  华龙证券  "  ,  "  金徽股份  "  ,  603132.00   ,  "  2022/2/22  "  ,  4   );insert into listed values(  58   ,  "  2022/1/18  "  ,  "  浙江比依电器  "  ,  "  浙江  "  ,  4666.50   ,  "  中信证券  "  ,  "  比依股份  "  ,  603215.00   ,  "  2022/2/18  "  ,  3   );insert into listed values(  59   ,  "  2022/1/14  "  ,  "  广东粤海饲料集团  "  ,  "  广东  "  ,  10000.00   ,  "  第一创业证券  "  ,  "  粤海集团  "  ,  1313.00   ,  "  2022/2/16  "  ,  2   );insert into listed values(  60   ,  "  2022/1/7  "  ,  "  威海百合生物技术  "  ,  "  山东  "  ,  1600.00   ,  "  广发证券  "  ,  "  百合股份  "  ,  603102.00   ,  "  2022/1/25  "  ,  1   );

运行后如下(部分数据):

接下来开始多表联查.

① 查询每个员工所在的公司以及部门

select e.id,e.name 员工姓名,c.company 公司名称,c.dept 所在部门 from emp e ,comp c where e.id = c.cid limit 10;

由于数据很多，我们只取前10条，运行后如下：

最后我们来个三表联查，查询所在公司的具体信息。

select  kk.name 员工姓名,  l.name 公司名称,  l.stocks_short 股票简称,l.Listed_date 上市日期,l.number 发行总数from  (select e.id,e.name,c.company,c.dept from emp e ,comp c where e.id = c.cid)as kk,listed l where  l.cid = kk.idlimit 10;

运行后如下：

在navicat中直观点：

MySQL很有趣，希望大家都努力热爱它。

好了，今天的先到这里，小编手指头都疼了，睡觉！

---

最后送给大家伊扎古壁纸。

文，完结！