浅析MYSQL基础

最新推荐文章于 2021-01-26 12:03:41 发布

吴一尘

最新推荐文章于 2021-01-26 12:03:41 发布

阅读量262

点赞数

分类专栏： mysql 文章标签： mysql基础

本文链接：https://blog.csdn.net/qq_38238296/article/details/86251397

版权

mysql 专栏收录该内容

17 篇文章 6 订阅

订阅专栏

MYSQL基础详解

1.数据库的基本操作

登录数据库：mysql -u"数据库名" -p"密码"
创建数据库：create database 名称（character set 字符集）
查看数据库：

查看所有数据库：show databases;

查看当前正在使用的数据库：select database();

查看数据库的创建过程：show create database 名称;
修改数据库字符集：alter database 数据库名 character set 字符集;
删除数据库：drop database 名称;
选中数据库：use 数据库名;

2.表的基本操作

2.1.创建表

create table 表名(
	列名1 类型(长度) 列的约束,
    列名2 类型(长度) 列的约束,
    列名3 类型(长度) 列的约束
);

列的类型（常见）
1. char：固定长度非Unicode的字符串
2. varchar：可变长度非Unicode的字符串
3. int：整数
4. time：存储时间
5. timestamp：时间戳
6. nchar：固定长度 Unicode 字符数据
7. nvarchar：可变长度 Unicode 字符数据
列的约束（常见）
1. 主键：primary key(非空且唯一)
2. 唯一键：unique（唯一）
3. 非空：not null
4. 自增长：auto_increment（每增加一行，数值增加1）
5. 外键：foreign key

**注意：**将列增加auto_increment属性的时候必须是一个键。否则会报错：1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key

2.2.查看表

所有表：show tables

mysql> show tables;
+-------------------+
| Tables_in_student |
+-------------------+
| test              |
| user              |
+-------------------+

查看表的创建过程：show cretae table 表名;

mysql> show create table user;
| Table | Create Table  

| user  | CREATE TABLE `user` (
  `uid` int(11) NOT NULL AUTO_INCREMENT,
  `uname` varchar(2) DEFAULT NULL,
  `pword` int(11) DEFAULT NULL,
  `phone` int(11) DEFAULT NULL,
  PRIMARY KEY (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8

查看表的结构：desc 表名;

desc user;
+-------+------------+------+-----+---------+----------------+
| Field | Type       | Null | Key | Default | Extra          |
+-------+------------+------+-----+---------+----------------+
| uid   | int(11)    | NO   | PRI | NULL    | auto_increment |
| uname | varchar(2) | YES  |     | NULL    |                |
| pword | int(11)    | YES  |     | NULL    |                |
| phone | int(11)    | YES  |     | NULL    |                |
+-------+------------+------+-----+---------+----------------+

查看当前表的数据：select * from 表名;(*代表所有元素,可将其换成列名)

mysql> select * from user;
+-----+-------+-------+--------+
| uid | uname | pword | phone  |
+-----+-------+-------+--------+
|   1 | 张三  |   123 | 123456 |
+-----+-------+-------+--------+

2.3.修改表

添加列：alter table 表名 add 列名类型约束;

mysql> alter table user add infor varchar(5);
Database changed
Records: 1  Duplicates: 0  Warnings: 0

修改列：alter table 表名 modify 列名新类型；

mysql> alter table user modify infor varchar(2);
Database changed
Records: 1  Duplicates: 0  Warnings: 0

修改列名：alter table 表名 change 原列名新列名类型（必须加类型）

mysql> alter table user change infor tes varchar(3);
Database changed
Records: 1  Duplicates: 0  Warnings: 0

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

mysql> alter table user drop tes;
Database changed
Records: 1  Duplicates: 0  Warnings: 0

2.4.重命名表

rename table 原表名 to 新表名;

mysql> rename table user to news;
Database changed

2.5.删除表

drop table 表名;

mysql> drop table news;
Query OK, 0 rows affected

3.数据的基本操作

3.1插入数据

insert into 表名(列名1,列名2,列名3) values(值1,值2,值3)；
insert into 表名 values(值1,值2,值3);这种写法必须是插入全列名，如何你只想插入某个列的值，那么就用上面的写法。

3.2删除数据

delete from 表名条件;

drop，truncate，delete三者之间的区别

delete语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作。该操作不会重置auto_incremnt变量

truncate操作直接将表中的数据全部删除，但保留数据结构该操作记录并不会在日志中保存，所以删除后的数据难以恢复。该操作会重置auto_increment变量（当插入数据时从1开始继续）

上面两个都是针对数据的，而drop则是直接将表全部删除（结构和数据）

truncate和drop是属于ddl操作立即生效,原数据不放到rollback segment中,不能回滚. 操作不触发trigger.
对表内存区别

drop表后，表不存在了所以内存也没有了

truncate table table_name 立刻释放磁盘空间，不管是 Innodb和MyISAM 。truncate table其实有点类似于drop table 然后create
速度上：drop>truncate>delete
安全性：小心使用drop和truncate，因为他两不支持回滚。

查询某个数据库的大小：

mysql> use information_schema;
Database changed
这步必须进行：mysql中的库和表的相关信息够保存在information_schema数据库中
mysql> select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data  from tables where table_schema='student';
+--------+
| data   |
+--------+
| 0.03MB |
+--------+
如果觉得数字小可以将后面的1024去掉 MB改成B

查询某个表的大小：

mysql> use information_schema;
Database changed
mysql> select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data  from tables where table_name='product';
+--------+
| data   |
+--------+
| 0.02MB |
+--------+
简单写法：
mysql> select sum(data_length) data_length from tables where table_name='product';

删除操作什么情况下会释放空间：https://blog.csdn.net/qq_38238296/article/details/86251018

关于mysql碎片产生：https://yq.aliyun.com/articles/676637

3.3更新数据

update tablename set 列名=列值，列名=列值 where条件;

3.4.查看数据

select [distinct]/[*]/[列名] from 表名 [where条件];

distinct是去除重复数据。

1.别名查询

表别名：select f.id,f.name from test as f;
每个派生表都必须有自己的别名
列别名：select id as 学号,name as 姓名 from test;这个as可以省略

注意：这个别名是在查询结果上设置的，也就是说并没有修改列的名称，只是在显示的时候显示的是别名。并且在做查询的时候，并不能直接使用别名。

2.运算查询

select id+10 from test; 这个运算查询和上面一样都是在查询结果上做操作。

还可以设置别名:select id+10 增长后 from test;

3.条件查询

select [*]/[列名] from 表名 [where条件];

例如（> >= <= <>不等于 between or and in：在某个范围内获得值）

mysql> select * from product where price between 700 and 2000;
+----+------+-------+
| id | name | price |
+----+------+-------+
|  3 | nike |   888 |
|  5 | kris |  1888 |
+----+------+-------+
2 rows in set

4.模糊查询like

_ ：代表单个字符
%：代表多个字符
in：在某个范围内获得值

例：查询name中带有牛的那行


mysql> select * from product where name like '%牛%';
+----+------+-------+
| id | name | price |
+----+------+-------+
|  2 | 蒙牛 |    88 |
+----+------+-------+
1 row in set

例：查询id在1,4,5里面的信息


mysql> select * from product where id in(1,4,5);
+----+------+-------+
| id | name | price |
+----+------+-------+
|  1 | 伊利 |    68 |
|  4 | 阿迪 |   688 |
|  5 | kris |  1888 |
+----+------+-------+
3 rows in set

5.排序查询order by

asc：升序(默认)
desc：降序
limit：限制几条数据

例:查询name中带“s”的并按照降序排列；

mysql> select * from product where name like '%s%' order by price desc;
+----+------+-------+
| id | name | price |
+----+------+-------+
|  5 | kris |  1888 |
|  7 | sam  |  1688 |
+----+------+-------+

mysql> select * from product order by price asc limit 0,3;
+----+------+-------+
| id | name | price |
+----+------+-------+
|  1 | 伊利 |    68 |
|  2 | 蒙牛 |    88 |
|  4 | 阿迪 |   688 |
+----+------+-------+

6.聚合函数

sum()：求和
avg()：求平均数
count()：统计数量
max()：最大值
min()：最小值

注意：where后不能接聚合函数

例：查询出价格最高的那行数列

mysql> select max(price) from product;
+------------+
| max(price) |
+------------+
|       2888 |
+------------+

7.分组：group by

例1：根据num字段分组，分组后统计name的个数

mysql> select * from product;
+----+------+-------+-----+
| id | name | price | num |
+----+------+-------+-----+
|  1 | 伊利 |    68 |   1 |
|  2 | 蒙牛 |    88 |   1 |
|  3 | nike |   888 |   2 |
|  4 | 阿迪 |   688 |   2 |
|  5 | kris |  1888 |   3 |
|  6 | tom  |  2888 |   3 |
|  7 | sam  |  1688 |   3 |
+----+------+-------+-----+

mysql> select num,count(*) from product group by num;
+-----+----------+
| num | count(*) |
+-----+----------+
|   1 |        2 |
|   2 |        2 |
|   3 |        3 |
+-----+----------+

having关键字可以连接聚合函数出现在分组之后

例2：根据num分组，分组统计后每组商品的平均价格并且价格>60;

mysql> select num,avg(price) from product group by num having avg(price)>100;
+-----+------------+
| num | avg(price) |
+-----+------------+
|   2 | 788.0000   |
|   3 | 2154.6667  |
+-----+------------+

编写顺序：S-F-W-G-H-O

select-from- where-group by-having-order by

执行顺序：F-W-G-H-S-O

先知道表名在条件判断，分组，然后条件筛选再控制显示，最后将结果排序

注意：当有子查询的时候执行顺序：

如果是嵌套子查询先执行子查询在执行主查询
如果是相关子查询则先执行主查询在执行子查询。

**相关子查询和嵌套子查询的区别：**https://blog.csdn.net/qq_38238296/article/details/86251165

max函数和group by 遇到的问题

例：你需要以num分组然后输出每个分组的最大价格的商品信息

mysql> select *,max(price) from product group by num;
 
+----+------+-------+-----+------------+
| id | name | price | num | max(price) |
+----+------+-------+-----+------------+
|  1 | 伊利 |    68 |   1 |         88 |
|  3 | nike |   888 |   2 |        888 |
|  5 | kris |  1888 |   3 |       2888 |
+----+------+-------+-----+------------+
上面很显然不是我们所期望的数据
原因： group by默认返回每一组的第一条数据（每一组的数据排序都是按默认顺序排序的）
应该用下面的语句：
mysql> select * from product p where p.price = (select max(price) from product where num=p.num);
+----+------+-------+-----+
| id | name | price | num |
+----+------+-------+-----+
|  2 | 蒙牛 |    88 |   1 |
|  3 | nike |   888 |   2 |
|  6 | tom  |  2888 |   3 |
+----+------+-------+-----+

上面语句的意思是：
    price 子查询(int num){
        retun max(pice);
    }
    for(int i=0;i<row[].length;i++){
         子查询(row[i].num);
    }
    * 主查询(maxprice){
    return 商品信息*
    }
执行过程：
	1.取出这个元组：|  1 | 伊利 |    68 |   1 |    num=1
	2.select max(price) from product where num=1;
	3.select * from produt p where p.price=88;
	4.得到商品信息：|  2 | 蒙牛 |    88 |   1 |
	5.依次取出接下来的元组然后执行1-4.

关于max和group by的详细解释可以看这位博主的文章：https://blog.csdn.net/hj12312/article/details/79376215

吴一尘

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
浅析MYSQL基础

文章目录MYSQL基础详解1.数据库的基本操作2.表的基本操作2.1.创建表2.2.查看表2.3.修改表2.4.重命名表2.5.删除表3.数据的基本操作3.1插入数据3.2删除数据drop，truncate，delete三者之间的区别3.3更新数据3.4.查看数据1.别名查询2.运算查询3.条件查询4.模糊查询like5.排序查询order by6.聚合函数7.分组：group bymax函数和g...
复制链接

扫一扫