Mysql数据库基础笔记

已于 2022-06-17 23:28:32 修改
阅读量1.2k 收藏 2
点赞数 2
CC 4.0 BY-SA版权
分类专栏： MYSQL 文章标签： 数据库 mysql
于 2022-06-01 22:25:20 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_44932745/article/details/125091593

一、前言

  • mysql和linux php,apache配合紧密，LAMP架构
  • mysql开源免费
  • 默认端口3306
  • set name gbk

二、安装启动服务

mysqld -install
net start mysql
mysql -uroot -proot

三、修改用户密码

mysql> set password for root@localhost=password('root');			//加密码
mysql> select user,password from mysql.user;						//查看密码
mysql> use mysql;
mysql> update user set password=password('123') where user='root';	//改密码
mysql> flush privileges;											//刷新权限
忘记root密码
  1. 关闭正在运行的MySQL服务
  2. 管理员运行DOS窗口，转到mysql\bin目录
  3. mysqld --skip-grant-tables //启动MySQL服务的时候跳过权限表认证
  4. 再开一个DOS窗口(因为刚才那个DOS窗口已经不能动了)，输入mysql回车
  5. 修改密码
  6. 重启服务

四、基本语句

1.库操作

mysql> show databases;						//查库
mysql> use mysql;							//选库
mysql> create database db1 charset utf8;	//创建库
mysql> drop database db1;					//删除数据库
											//mysql中数据库不能改名，表/列可以改名

2.表操作

mysql> show tables;							//查表
+---------------+
| Tables_in_db1 |
+---------------+
| class         |
| student       |
+---------------+
mysql> create table student(				//创建表
    -> Snum int,
    -> Sname varchar(10),
    -> Ssex char(2)
    -> )engine myisam charset utf8;
mysql> drop table student;					//删除表
mysql> rename table student to stu;			//重命表名
mysql> insert into stu values				//插入数据
    -> (1,'张三'),
    -> (2,'lisi'),
    -> (3,'王五');
mysql> select * from stu;					//查数据
mysql> truncate stu;						//清空表数据
mysql> insert into student values
    -> \c									//退出本语句
mysql> create table class(					//创建表
    -> id int primary key auto_increment,
    -> sname varchar(10) not null default '',
    -> sex char(1) not null default '',
    -> company varchar(20) default 'null',
    -> salary decimal(6,2) default 0.00,
    -> other varchar(20) default '无'
    -> )engine myisam charset utf8;
mysql> desc class;							//查看表结构
+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| id      | int(11)      | NO   | PRI | NULL    | auto_increment |
| sname   | varchar(10)  | NO   |     |         |                |
| sex     | char(1)      | NO   |     |         |                |
| company | varchar(20)  | YES  |     | null    |                |
| salary  | decimal(6,2) | YES  |     | 0.00    |                |
| other   | varchar(20)  | YES  |     | 无      |                |
+---------+--------------+------+-----+---------+----------------+
mysql> alter table class add score tinyint unsigned not null default 0;	//增加一个score列
mysql> alter table student add money float(7,2) default 0;

五、表操作

1、增——Create

mysql> create table db1.member(										//建表
    -> id int unsigned auto_increment primary key,
    -> username char(20) not null default '',
    -> gender char(1) not null default '',
    -> weight tinyint unsigned not null default 0,
    -> birth date not null default 0,
    -> salary decimal(8,2) not null default 0,
    -> lastlogin timestamp not null default 0
    -> )engine myisam charset utf8;
mysql> show create table member;								//查看建表语句
+--------+------------------------------------------------------------------------------+
| Table  | Create Table                                                                 |
+--------+------------------------------------------------------------------------------+
| member | CREATE TABLE `member` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `username` char(20) NOT NULL DEFAULT '',
  `gender` char(1) NOT NULL DEFAULT '',
  `weight` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `birth` date NOT NULL DEFAULT '0000-00-00',
  `salary` decimal(8,2) NOT NULL DEFAULT '0.00',
  `lastlogin` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+--------+------------------------------------------------------------------------------+

2、改——Alter

mysql> alter table student add age tinyint not null default 0;	//默认加的列在表最后
mysql> alter table test add gender char(1) not null default 0 after star;	
																//把新列加在某列后
mysql> alter table test add id int unsigned not null auto_increment primary key first;																	//把新列加载最前面
mysql> alter table test drop gender;							//删除列
mysql> alter table test modify gender char(4) not null default '';																						//修改列类型
mysql> alter table test change gender sex char(2);				//修改列名和列类型

3、删——Drop

mysql> drop table student;

4、查——Show

mysql> show tables;			//查表名
mysql> desc student;		//查表结构

六、数据操作

1、增——Insert

mysql> insert into class
    -> (id,sname,sex,company,salary,other) values (1,'张三','女','','10000.00','工作地址在广州');
mysql> insert into class
    -> (sname,sex,salary) values ('李四','女','9000');
mysql> insert into class (sname) values ('王五');
mysql> insert into class (sname) values ('王五'),('赵六'),('顾七'); 
mysql> insert into class values
    -> (7,'吴八','男','百度',8734.32,'深圳任职');
mysql> select * from class;
+----+-------+-----+---------+---------+----------------+
| id | sname | sex | company | salary  | other          |
+----+-------+-----+---------+---------+----------------+
|  1 | 张三  | 女  |         | 9999.99 | 工作地址在广州 |
|  2 | 李四  | 女  | null    | 9000.00 | 无             |
|  3 | 王五  |     | null    |    0.00 | 无             |
|  4 | 王五  |     | null    |    0.00 | 无             |
|  5 | 赵六  |     | null    |    0.00 | 无             |
|  6 | 顾七  |     | null    |    0.00 | 无             |
|  7 | 吴八  | 男  | 百度    | 8734.32 | 深圳任职       |
+----+-------+-----+---------+---------+----------------+
mysql> insert into db1.goods select goods_id,cat_id,goods_name from gyshop.goods;															//从其他库导入插入数据，要求导入所有列

2、改——Update

  • update 表名 set 列1=’ ‘，列2=’ ’ where expr;
mysql> update class set sex='男',company='皇家亲戚' where sname='王五';
mysql> update class set salary = 5000 where salary =0.00;
mysql> update class set other = '目前离职状态' where salary <=9000 and sex = '';
mysql> select * from class;
+----+-------+-----+----------+---------+----------------+
| id | sname | sex | company  | salary  | other          |
+----+-------+-----+----------+---------+----------------+
|  1 | 张三  | 女  | 千度     | 9999.99 | 工作地址在广州 |
|  2 | 李四  | 女  | 千度     | 9000.00 | 无             |
|  3 | 王五  | 男  | 皇家亲戚 | 5000.00 | 无             |
|  4 | 王五  | 男  | 皇家亲戚 | 5000.00 | 无             |
|  5 | 赵六  |     | 千度     | 5000.00 | 目前离职状态   |
|  6 | 顾七  |     | 千度     | 5000.00 | 目前离职状态   |
|  7 | 吴八  | 男  | 百度     | 8734.32 | 深圳任职       |
+----+-------+-----+----------+---------+----------------+
mysql> update student set bangji=Snum;

3、删——Delete

  • delete from 表名 where expr;
mysql> delete from class where sname ='王五' or company='百度';
mysql> select * from class;
+----+-------+-----+---------+---------+----------------+
| id | sname | sex | company | salary  | other          |
+----+-------+-----+---------+---------+----------------+
|  1 | 张三  | 女  | 千度    | 9999.99 | 工作地址在广州 |
|  2 | 李四  | 女  | 千度    | 9000.00 | 无             |
|  5 | 赵六  |     | 千度    | 5000.00 | 目前离职状态   |
|  6 | 顾七  |     | 千度    | 5000.00 | 目前离职状态   |
+----+-------+-----+---------+---------+----------------+
mysql> delete from class;									//直接删除所有表数据
mysql> select * from class;
Empty set (0.00 sec)
mysql> truncate stu;										//清空表数据

4、查——Select

  • select 列1，列2，列n from 表名 where expr；
mysql> select * from class;								//查表所有数据
+----+-------+-----+---------+--------+-------+
| id | sname | sex | company | salary | other |
+----+-------+-----+---------+--------+-------+
|  8 | 张三  | 男  | null    |   0.00 | 无    |
|  9 | 李四  | 女  | null    |   0.00 | 无    |
| 10 | 王五  | 女  | null    |   0.00 | 无    |
+----+-------+-----+---------+--------+-------+
mysql> select sname from class where sex='女';			//查询指定列数据
+-------+
| sname |
+-------+
| 李四  |
| 王五  |
+-------+

1） where条件查询

where是针对原表的一个表达式布尔判断

mysql> select goods_id,goods_name from goods where goods_id = 2;
mysql> select goods_id,goods_name from goods where goods_id != 2;
mysql> select goods_id,goods_name from goods where goods_id <> 2;
mysql> select goods_id,goods_name from goods where shop_price > 2000;
mysql> select goods_id,goods_name from goods where shop_price >= 4;
mysql> select goods_id,goods_name from goods where goods_id in (1,4);
mysql> select goods_id,goods_name from goods where goods_id between 2 and 4;
mysql> select goods_id,goods_name from goods where goods_name like '平板%';
mysql> select goods_id,goods_name from goods where goods_name like '%平板%';
mysql> select goods_id,goods_name from goods where goods_name like '__平板';
mysql> select goods_id,goods_name from goods where goods_name like '手机K_';
mysql> select goods_id,goods_name from goods where goods_id != 2 and goods_id !=3;
mysql> select goods_id,goods_name from goods where goods_id not in (2,3);
mysql> select goods_id,goods_name from goods where goods_name not like '平板%';
mysql> select goods_id,goods_name from goods where goods_id > 1 and goods_id < 3 or goods_id > 4 and goods_id < 7;
mysql> select goods_id,goods_name from goods where shop_price >= 4 and goods_id > 4 and goods_id < 7;
mysql> select goods_id,goods_name from goods where shop_price = 0 and (goods_id < 4 or goods_id > 7 and goods_id <= 9) and goods_name like '%平板%';
mysql> select goods_name,market_price-shop_price from goods where 1;
mysql> select goods_name,(market_price-shop_price) as discount from goods where 1;
mysql> select goods_name,(market_price-shop_price) as discount from goods where (market_price-shop_price) > 1000;

2）group by分组查询

  • 五个聚合函数：sum,avg,max,min,count,group
mysql> select max(market_price) from goods;
mysql> select min(market_price) as MIN from goods;
mysql> select sum(goods_number) from goods;
mysql> select sum(goods_number) from goods where cat_id=3;
mysql> select avg(shop_price) from goods;
mysql> select count(*) from goods;

mysql> select cat_id,count(*),sum(market_price) from goods group by cat_id;
mysql> select max(goods_id),cat_id from goods group by cat_id;
+---------------+--------+
| max(goods_id) | cat_id |
+---------------+--------+
|             8 |      0 |
|             1 |      1 |
|             9 |      2 |
|            12 |      3 |
+---------------+--------+
  • 严格的讲，以group by a,b,c为列，则select的列只能在a,b,c里选择，语义上才没有矛盾；

3）having 筛选

mysql> select goods_name,(market_price-shop_price) as discount from goods where (market_price-shop_price)>=500;
+----------------+----------+
| goods_name     | discount |
+----------------+----------+
| 手机           |  1100.00 |
| 平板屏幕总成   |   500.00 |
| 平板屏幕单机版 |   500.00 |
+----------------+----------+

mysql> select goods_name,(market_price-shop_price) as discount from goods having discount >= 500;
+----------------+----------+
| goods_name     | discount |
+----------------+----------+
| 手机           |  1100.00 |
| 平板屏幕总成   |   500.00 |
| 平板屏幕单机版 |   500.00 |
+----------------+----------+
mysql> select cat_id,sum(market_price) as discount from goods group by cat_id having discount >5000;
+--------+----------+
| cat_id | discount |
+--------+----------+
|      2 |  6000.00 |
|      3 |  7997.00 |
+--------+----------+ 
mysql> select name,avg(score),sum(score<60) as gk from result group by name having gk>=2;

4）order by 排序

mysql> select goods_name,shop_price from goods order by shop_price asc;		//升序
+----------------+------------+
| goods_name     | shop_price |
+----------------+------------+
| 夹子           |       4.00 |
| 手机K1         |    2000.00 |
| 手机K349       |    2000.00 |
| 平板屏幕单机版 |    2500.00 |
| 平板屏幕总成   |    2500.00 |
| 手机           |    2899.00 |
+----------------+------------+
mysql> select cat_id from goods group by cat_id order by cat_id desc;	//降序
+--------+
| cat_id |
+--------+
|      3 |
|      2 |
|      1 |
|      0 |
+--------+
mysql> select goods_name,cat_id,shop_price from goods order by cat_id asc,shop_price desc;																	//多字段排序
+----------------+--------+------------+
| goods_name     | cat_id | shop_price |
+----------------+--------+------------+
| 荣耀平板       |      0 |       0.00 |
| 荣耀平板       |      0 |       0.00 |
| 风扇           |      0 |       0.00 |
| 夹子           |      1 |       4.00 |
| 平板Plus       |      2 |    6699.00 |
| 平板屏幕单机版 |      2 |    2500.00 |
| 平板屏幕总成   |      2 |    2399.00 |
| 平板           |      2 |    2299.00 |
| 手机           |      3 |    2899.00 |
| 手机K1         |      3 |    2000.00 |
| 手机K349       |      3 |    2000.00 |
| 手机R15x       |      3 |       0.00 |
+----------------+--------+------------+
mysql>  select goods_name,cat_id,shop_price from goods where cat_id=2 order by shop_price asc;
+----------------+--------+------------+
| goods_name     | cat_id | shop_price |
+----------------+--------+------------+
| 平板           |      2 |    2299.00 |
| 平板屏幕总成   |      2 |    2399.00 |
| 平板屏幕单机版 |      2 |    2500.00 |
| 平板Plus       |      2 |    6699.00 |
+----------------+--------+------------+

5）limit 限制结果条数

mysql>  select goods_name,cat_id,shop_price from goods where cat_id=2 order by shop_price asc limit 2;													//等同于limit 0,2
+--------------+--------+------------+
| goods_name   | cat_id | shop_price |
+--------------+--------+------------+
| 平板         |      2 |    2299.00 |
| 平板屏幕总成 |      2 |    2399.00 |
+--------------+--------+------------+
mysql>  select goods_name,cat_id,shop_price from goods where cat_id=2 order by shop_price asc limit 1,2;												//'1'是偏移量，'2'是取出条目
+----------------+--------+------------+
| goods_name     | cat_id | shop_price |
+----------------+--------+------------+
| 平板屏幕总成   |      2 |    2399.00 |
| 平板屏幕单机版 |      2 |    2500.00 |
+----------------+--------+------------+

6）子查询

  • where型子查询：把内层查询的结果作为外层查询的比较条件
  • from型子查询：把内层的查询结果当成临时表，供外层sql再次查询
  • exists型子查询：把外层的查询结果，拿到内层，看内层的查询是否成立
mysql> select * from goods where goods_id =(select max(goods_id) from goods);
mysql> select goods_id,goods_name,cat_id from goods where goods_id in (select max(goods_id) from goods group by cat_id) order by cat_id;
+----------+------------+--------+
| goods_id | goods_name | cat_id |
+----------+------------+--------+
|        8 | 荣耀平板   |      0 |
|        1 | 夹子       |      1 |
|        9 | 平板Plus   |      2 |
|       12 | 手机K349   |      3 |
+----------+------------+--------+
mysql> select * from (select * from goods order by cat_id asc,goods_id desc) as temp group by cat_id;
mysql> select goods_name,shop_price from goods where exists (select * from price where other != 'null' and price.goods_name=goods.goods_name);
+------------+------------+
| goods_name | shop_price |
+------------+------------+
| 夹子       |       4.00 |
| 手机       |    2899.00 |
+------------+------------+
mysql> select goods_name,shop_price from goods where exists (select * from price where price.goods_name=goods.goods_name);
+------------+------------+
| goods_name | shop_price |
+------------+------------+
| 夹子       |       4.00 |
| 手机       |    2899.00 |
| 风扇       |       0.00 |
| 平板       |    2299.00 |
+------------+------------+

7）union 联合查询

  • 把两次或多次查询结果合并起来，要求两次查询的列数一致
  • 作用是合并结果集
 mysql> select goods_id,goods_name,shop_price from goods where shop_price >4000 union select goods_id,goods_name,shop_price from goo
ds where shop_price <4;
+----------+------------+------------+
| goods_id | goods_name | shop_price |
+----------+------------+------------+
|        9 | 平板Plus   |    6699.00 |
|        3 | 风扇       |       0.00 |
|        7 | 荣耀平板   |       0.00 |
|        8 | 荣耀平板   |       0.00 |
|       11 | 手机R15x   |       0.00 |
+----------+------------+------------+
mysql> select goods_id from goods union select goods_name from price;
+----------+
| goods_id |
+----------+
| 1        |
| 2        |
| 3        |
| 4        |
| 5        |
| 6        |
| 7        |
| 8        |
| 9        |
| 10       |
| 11       |
| 12       |
| 夹子     |
| 手机     |
| 风扇     |
| 平板     |
+----------+
mysql> select goods_id from goods union select goods_id from price order by goods_id desc;												//重复结果会去重，并也可以对结果集再排序
+----------+
| goods_id |
+----------+
|       12 |
|       11 |
|       10 |
|        9 |
|        8 |
|        7 |
|        6 |
|        5 |
|        4 |
|        3 |
|        2 |
|        1 |
+----------+
mysql> (select goods_id from goods order by goods_id desc limit 3) union (select goods_id from price order by goods_id asc limit 3);
+----------+
| goods_id |
+----------+
|       12 |
|       11 |
|       10 |
|        1 |
|        2 |
|        3 |
+----------+
mysql> select goods_id from goods union all select goods_id from price order by goods_id desc;													//union all 重复数据不去重
+----------+
| goods_id |
+----------+
|       12 |
|       11 |
|       10 |
|        9 |
|        8 |
|        7 |
|        6 |
|        5 |
|        4 |
|        4 |
|        3 |
|        3 |
|        2 |
|        2 |
|        1 |
|        1 |
+----------+

8）连接查询

  • 左连接：A left join B on 条件——条件为真，则B表对应的行取出
  • 右连接：A right join B on 条件——条件为真，则A对应的行取出
  • 内连接：A inner join B on 条件——左右连接的交集
  • 外连接：A outer join B on 条件——左右连接的并集(mysql中不支持)
mysql> select goods.goods_name,price.other from goods,price where goods.goods_name=price.goods_name;				//查询结果包含多张表的不同列内容;
+------------+----------+
| goods_name | other    |
+------------+----------+
| 夹子       | 售卖中   |
| 手机       | 预售商品 |
| 风扇       | NULL     |
| 平板       | NULL     |
+------------+----------+
mysql> select * from student,aaa;				//求两表的笛卡尔积(两表相乘)
mysql> select * from teacher;
+------+--------+--------+
| Tnum | Tname  | Tclass |
+------+--------+--------+
|    1 | 李老师 |      1 |
|    2 | 王老师 |      2 |
|    3 | 张老师 |      3 |
+------+--------+--------+
mysql> select * from student;
+------+-------+--------+
| Snum | Sname | bangji |
+------+-------+--------+
|    1 | 张三  |      1 |
|    2 | 李四  |      2 |
|    3 | 王五  |      3 |
|    4 | 老六  |      3 |
+------+-------+--------+
mysql> select * from achievement;
+----+------+------+
| id | Snum | mark |
+----+------+------+
|  6 |    1 |    1 |
|  7 |    2 |   49 |
|  8 |    3 |   88 |
|  9 |    4 |   92 |
+----+------+------+
mysql> select * from student,teacher where student.Tclass=teacher.Tclass;	
																//两表相乘再匹配，占用内存大
+------+-------+--------+------+--------+--------+
| Snum | Sname | Tclass | Tnum | Tname  | Tclass |
+------+-------+--------+------+--------+--------+
|    1 | 张三  |      1 |    1 | 李老师 |      1 |
|    2 | 李四  |      2 |    2 | 王老师 |      2 |
|    3 | 王五  |      3 |    3 | 张老师 |      3 |
|    4 | 老六  |      3 |    3 | 张老师 |      3 |
+------+-------+--------+------+--------+--------+

mysql> select * from student left join teacher on student.Tclass=teacher.Tclass; //左连接
+------+-------+--------+------+--------+--------+
| Snum | Sname | Tclass | Tnum | Tname  | Tclass |
+------+-------+--------+------+--------+--------+
|    1 | 张三  |      1 |    1 | 李老师 |      1 |
|    2 | 李四  |      2 |    2 | 王老师 |      2 |
|    3 | 王五  |      3 |    3 | 张老师 |      3 |
|    4 | 老六  |      3 |    3 | 张老师 |      3 |
+------+-------+--------+------+--------+--------+
mysql> select student.Snum,Sname,student.Tclass,Tname,mark from student left join teacher on student.Tclass=teacher.Tclass left join achievement on student.Snum=achievement.Snum;
																			//三张表左连接
+------+-------+--------+--------+------+
| Snum | Sname | Tclass | Tname  | mark |
+------+-------+--------+--------+------+
|    1 | 张三  |      1 | 李老师 |    1 |
|    2 | 李四  |      2 | 王老师 |   49 |
|    3 | 王五  |      3 | 张老师 |   88 |
|    4 | 老六  |      3 | 张老师 |   92 |
+------+-------+--------+--------+------+
mysql> select * from m left join t as t1 on hostTeamID=t1.teamID left join t as t2 on gustTeamID=t2.teamID;														//两次连接同一张表
+-------+----------+----------+-----------+---------+------+--------+------+--------+
|matchID|hostTeamID|gustTeamID|matchResult|matchTime|teamID|teamName|teamID|teamName|
+-------+----------+----------+-----------+---------+------+--------+------+--------+
|     1 |        1 |        2 | 2:0       |2006-05-21|   1 | 国安    |    2 | 申花   |
|     2 |        2 |        3 | 1:2       |2006-06-21|   2 | 申花    |    3 |公益联队 |
|     3 |        3 |        1 | 2:5       |2006-06-25|   3 | 公益联队 |    1 | 国安   |
|     4 |        2 |        1 | 3:2       |2006-07-21|   2 | 申花    |    1 | 国安   |
+-------+----------+----------+-----------+----------+-----+--------+-------+-------+

七、视图

1)基本概念

  • 视图是由查询结果形成的一张虚拟表，是表通过某种运算得到的一个投影
  • 表的数据变化要影响到视图的变化
  • 视图数据变化一般会影响表的数据变化

2)视图操作

1.创建视图

mysql> create view 视图名 as select 语句;
mysql> create view student as select name as '姓名',school as '学校',case sex when 1 then '男' when 2 then '女' end as 性别 from role;
Query OK, 0 rows affected (0.01 sec)

2.删除视图

mysql> drop view 视图名;

3.修改视图

mysql> alter view test as select id,name,sex from role;
mysql> update test set sex = 1 where name='毛利兰';
mysql> create view SSS as select id,name,sex,性别  from student left join test on name =姓名;
mysql> select * from SSS;
+------+------------+------+------+
| id   | name       | sex  | 性别 |
+------+------------+------+------+
|    1 | 毛利兰     |    1 | 男   |
|    2 | 工藤新一   |    1 | 男   |
|    3 | 江户川柯南 |    1 | 男   |
|    4 | 越前龙马   |    1 | 男   |
|    5 | 灰原哀     |    2 | 女   |
|    6 | 唐泽雪穗   |    2 | 女   |
+------+------------+------+------+
mysql> delete from SSS where id =1;
ERROR 1395 (HY000): Can not delete from join view 'db1.sss'
mysql> delete from test where id =1;

视图包含下述结构，则它是不可更新的：

  • 聚合函数(sum(),min(),max(),count())
  • distinct
  • group by
  • having
  • union或union all
  • 子查询
  • join

4.查看视图

视图一旦创建完毕，就可以像表一样查询

mysql> select name as '姓名',school as '学校',case sex when 1 then '男' when 2 then '女' end as 性别 from role;
+------------+--------------+------+
| 姓名       | 学校         | 性别 |
+------------+--------------+------+
| 毛利兰     | 帝丹高中     | 女   |
| 工藤新一   | 帝丹高中     | 男   |
| 江户川柯南 | 帝丹小学     | 男   |
| 越前龙马   | 青春学园     | 男   |
| 灰原哀     | 帝丹小学     | 女   |
| 唐泽雪穗   | 清华女子学院 | 女   |
+------------+--------------+------+
mysql> select * from student;
+------------+--------------+------+
| 姓名       | 学校         | 性别 |
+------------+--------------+------+
| 毛利兰     | 帝丹高中     | 女   |
| 工藤新一   | 帝丹高中     | 男   |
| 江户川柯南 | 帝丹小学     | 男   |
| 越前龙马   | 青春学园     | 男   |
| 灰原哀     | 帝丹小学     | 女   |
| 唐泽雪穗   | 清华女子学院 | 女   |
+------------+--------------+------+
mysql> select 姓名 from student where 性别='男';
+------------+
| 姓名       |
+------------+
| 工藤新一   |
| 江户川柯南 |
| 越前龙马   |
+------------+

3)视图作用

1.可以简化我们的查询

复杂的统计时，先用视图生成一个中间结果，再查询视图

2.更精细的权限控制

例如只允许开放某些字段，像password密码字段不开放，可以通过创建视图实现

3.数据多时，可以分表

create view article as select title from article1 union select title from article2...union select tile article3;

八、函数

1）数学函数

1. abs(x)

返回x的绝对值

2. bin(x)

返回x的二进制(oct返回八进制，hex返回十六进制)

mysql> select bin(3);
+--------+
| bin(3) |
+--------+
| 11     |
+--------+

3. ceiling(x)

返回大于x的最小整数值

mysql> select ceiling(3.34);
+---------------+
| ceiling(3.34) |
+---------------+
|             4 |
+---------------+

4. exp(x)

返回值e的x次方

5. floor(x)

返回小于x的最大整数值

mysql> select floor(1.23)
+-------------+
| floor(1.23) |
+-------------+
|           1 |
+-------------+
mysql> update test set num = floor(num/10)*10 where num >= 20 and num <= 39;
														//把二十几和三十几的都改为20和30

6. greatest(x1,x2,…,xn)

返回集合中最大的值

mysql> select greatest(1,2,99,101);
+----------------------+
| greatest(1,2,99,101) |
+----------------------+
|                  101 |
+----------------------+

7. least(x1,x2,x3,…,xn)

返回集合中最小的值

mysql> select least(1,2,99,101);
+-------------------+
| least(1,2,99,101) |
+-------------------+
|                 1 |
+-------------------+

8. ln(x)

返回x的自然对数

9. log(x,y)

返回x的以y为底的对数

mysql> select log(2,8);
+----------+
| log(2,8) |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)

10. mod(x,y)

返回x/y的模(余数)

mysql> select mod(5,2);
+----------+
| mod(5,2) |
+----------+
|        1 |
+----------+

11. pi()

返回pi的值(圆周率)

mysql> select pi();
+----------+
| pi()     |
+----------+
| 3.141593 |

12. rand()

返回0-1内的随机值

mysql> select rand();
+--------------------+
| rand()             |
+--------------------+
| 0.4453301303510835 |

13. round(x,y)

返回参数x的四舍五入的有y位小数的值

mysql> select round(3.35,1);
+---------------+
| round(3.35,1) |
+---------------+
|           3.4 |
+---------------+

14. sign(x)

返回代表数字x的符号的值

15. sqrt(x)

返回一个数的平方根

mysql> select sqrt(4);
+---------+
| sqrt(4) |
+---------+
|       2 |
+---------+

16. truncate(x,y)

返回数字x截短为y位小数的结果

mysql> select truncate(3.343443,2);
+----------------------+
| truncate(3.343443,2) |
+----------------------+
|                 3.34 |
+----------------------+

2）聚合函数

1. avg(col)

返回指定列的平均值

2. count(col)

返回指定列中非null值的个数

求总行数

count(*)和count(1)取的是绝对的行数，哪怕某一行所有字段全为NULL也计算在内

count(列名)取的是该列不为null的所有行的行数

mysql> select count(*) from goods;
mysql> select count(goods_sn) from goods;

3. min(col)

返回指定列的最小值

4. max(col)

返回指定列的最大值

5. sum(col)

返回指定列的所有值之和

6. group_concat(col)

返回由属于一组的列值连接组合而成的结果

mysql> select group_concat(goods_name) from goods;
+---------------------------------------------------------------------------------------+
| group_concat(goods_name)                                                              |
+---------------------------------------------------------------------------------------+
| 夹子,手机,风扇,平板,平板屏幕总成,平板屏幕单机版,荣耀平板,荣耀平板,平板Plus,手机K1,手机R15x,手机K349|
+---------------------------------------------------------------------------------------+

3）字符串函数

1. ascii(char)

返回字符的ascii码值

2. bit_length(str)

返回字符串的比特长度

3. concat(s1,s2,…,sn)

将s1,s2,…,sn连接成字符串

mysql> select concat(goods_id,goods_name) from goods;
+-----------------------------+
| concat(goods_id,goods_name) |
+-----------------------------+
| 1夹子                       |
| 2手机                       |
| 3风扇                       |
| 4平板                       |
+-----------------------------+
mysql> select goods_name,concat('荣耀',substring(goods_name from 3)) from goods where goods_name like '__平板';
+------------+---------------------------------------------+
| goods_name | concat('荣耀',substring(goods_name from 3)) |
+------------+---------------------------------------------+
| 苹果平板   | 荣耀平板                                    |
| 华为平板   | 荣耀平板                                    |
+------------+---------------------------------------------+
mysql> update goods set goods_name=concat('荣耀',substring(goods_name from 3)) where goods_name like '__平板';

4. concat_ws(sep,s1,…,sn)

将s1,s2,…,sn连接成字符串，并用sep字符间隔

mysql> select concat_ws('!',goods_id,goods_name) from goods;
+------------------------------------+
| concat_ws('!',goods_id,goods_name) |
+------------------------------------+
| 1!夹子                             |
| 2!手机                             |
| 3!风扇                             |
+------------------------------------+

5. insert(str,x,y,instr)

将字符串str从第x位置开始，y个字符长的字串替换为字符串instr，返回结果

mysql> select insert(goods_name,2,1,'娜') from goods;
+-----------------------------+
| insert(goods_name,2,1,'娜') |
+-----------------------------+
| 夹娜                        |
| 手娜                        |
| 风娜                        |
| 平娜                        |
| 平娜屏幕总成                |
| 平娜屏幕单机版              |
| 荣娜平板                    |
| 荣娜平板                    |
| 平娜Plus                    |
| 手娜K1                      |
| 手娜R15x                    |
| 手娜K349                    |
+-----------------------------+

6. find_in_set(str,list)

分析逗号分割的list列表，如果发现str，返回str在list中的位置

mysql> select find_in_set('手机',group_concat(goods_name)) from goods;
+----------------------------------------------+
| find_in_set('手机',group_concat(goods_name)) |
+----------------------------------------------+
|                                            2 |
+----------------------------------------------+

7. lcase(str)或lower(str)

返回将字符串str中所有字符改变为小写后的结果

mysql> select lcase('I LOVE YOU');
+---------------------+
| lcase('I LOVE YOU') |
+---------------------+
| i love you          |
+---------------------+

8. left(str,x)

返回字符串str中做左边的x个字符

mysql> select left('LOVE',2);
+----------------+
| left('LOVE',2) |
+----------------+
| LO             |
+----------------+

9. length(s)

返回字符串str中的字符数

mysql> select length('LOVE');
+----------------+
| length('LOVE') |
+----------------+
|              4 |
+----------------+

10. ltrim(str)

从字符串str中切掉开头的空格

11. position(substr,str)

返回子串substr在字符串str中第一次出现的位置

12. substring

截取字符串，substring(str,pos,len)、substring(str,pos)、substring(str from pos)、substring(str from pos for len)等同

mysql> select substring('今天天气真好！',3);
+-------------------------------+
| substring('今天天气真好！',3) |
+-------------------------------+
| 天气真好！                    |
+-------------------------------+

mysql> select substring('今天天气真好！',3,2);
+---------------------------------+
| substring('今天天气真好！',3,2) |
+---------------------------------+
| 天气                            |
+---------------------------------+

mysql> select substring('今天天气真好！' from 3);
+------------------------------------+
| substring('今天天气真好！' from 3) |
+------------------------------------+
| 天气真好！                         |
+------------------------------------+

mysql> select substring('今天天气真好！' from 3 for 2) as '截取字符串';
+------------+
| 截取字符串 |
+------------+
| 天气       |
+------------+

4）日期和时间函数

1.now()

返回当前的日期和时间

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2022-06-15 09:49:19 |
+---------------------+
mysql> select from_unixtime(unix_timestamp());
+---------------------------------+
| from_unixtime(unix_timestamp()) |
+---------------------------------+
| 2022-06-15 10:35:08             |
+---------------------------------+

2.curdate()

current_date() 返回当前日期

mysql> select curdate();
+------------+
| curdate()  |
+------------+
| 2022-06-15 |
+------------+
mysql> select extract(year_month from current_date);
+---------------------------------------+
| extract(year_month from current_date) |
+---------------------------------------+
|                                202206 |
+---------------------------------------+
mysql> select extract(day_second from current_date);
+---------------------------------------+
| extract(day_second from current_date) |
+---------------------------------------+
|                                  2022 |
+---------------------------------------+
mysql> select extract(year from current_date);
+---------------------------------+
| extract(year from current_date) |
+---------------------------------+
|                            2022 |
+---------------------------------+

3.curtime()

current_time() 返回当前时间

mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 10:04:25  |
+-----------+

mysql> select current_time();
+----------------+
| current_time() |
+----------------+
| 10:04:31       |
+----------------+
mysql> select extract(hour_minute from current_time);
+----------------------------------------+
| extract(hour_minute from current_time) |
+----------------------------------------+
|                                   1030 |
+----------------------------------------+

4. dayofweek()

返回日期所代表的一星期中的第几天(1-7)

以周日为开始第一天

mysql> select dayofweek(now());
+------------------+
| dayofweek(now()) |
+------------------+
|                4 |
+------------------+
mysql> select dayofweek(20220613);
+---------------------+
| dayofweek(20220613) |
+---------------------+
|                   2 |
+---------------------+

5.dayofmonth()

返回一个月的第几天(1-31)

mysql> select dayofmonth(now());
+-------------------+
| dayofmonth(now()) |
+-------------------+
|                15 |
+-------------------+

6.dayofyear()

返回一年的第几天

mysql> select dayofyear(curdate());
+----------------------+
| dayofyear(curdate()) |
+----------------------+
|                  166 |
+----------------------+

7.dayname()

返回日期的星期名

mysql> select dayname(now());
+----------------+
| dayname(now()) |
+----------------+
| Wednesday      |
+----------------+

8.hour()

返回小时值(0-23)

mysql>  select hour('12:34:44');
+------------------+
| hour('12:34:44') |
+------------------+
|               12 |
+------------------+
1 row in set (0.00 sec)
mysql> select hour(now());
+-------------+
| hour(now()) |
+-------------+
|          11 |
+-------------+

9.minute()

返回分钟值(0-59)

mysql> select minute(now());
+---------------+
| minute(now()) |
+---------------+
|            14 |
+---------------+
mysql> select minute('12:34:44');
+--------------------+
| minute('12:34:44') |
+--------------------+
|                 34 |
+--------------------+

10.month()

返回月份值(1-12)

mysql> select month(now());
+--------------+
| month(now()) |
+--------------+
|            6 |
+--------------+

11.monthname()

返回月份名

mysql> select monthname(now());
+------------------+
| monthname(now()) |
+------------------+
| June             |
+------------------+
mysql> select monthname(0412);
+-----------------+
| monthname(0412) |
+-----------------+
| April           |
+-----------------+

12.quarter()

返回一年中的季度(1-4)

mysql> select quarter(now());
+----------------+
| quarter(now()) |
+----------------+
|              2 |
+----------------+
1 row in set (0.00 sec)

mysql> select quarter(712);
+--------------+
| quarter(712) |
+--------------+
|            3 |
+--------------+

13.week()

返回所代表一年中第几周（0-53）

mysql> select week(now());
+-------------+
| week(now()) |
+-------------+
|          24 |
+-------------+

14.year()

返回年份(1000-9999)

mysql> select year(now());
+-------------+
| year(now()) |
+-------------+
|        2022 |
+-------------+

15.period_diff()

返回两个日期值之间的差值(月数)

mysql> select period_diff(202206,202203);
+----------------------------+
| period_diff(202206,202203) |
+----------------------------+
|                          3 |
+----------------------------+
mysql> select period_diff(202206,202103);
+----------------------------+
| period_diff(202206,202103) |
+----------------------------+
|                         15 |
+----------------------------+

5）加密函数

1.md5()

计算MD5校验和

mysql> select md5('123');
+----------------------------------+
| md5('123')                       |
+----------------------------------+
| 202cb962ac59075b964b07152d234b70 |
+----------------------------------+

2.password()

返回字符串的加密版本，加密过程不可逆

mysql> select password('123');
+-------------------------------------------+
| password('123')                           |
+-------------------------------------------+
| *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
+-------------------------------------------+

3.sha()

计算字符串str的安全散列算法(sha)校验和

mysql> select sha(123);
+------------------------------------------+
| sha(123)                                 |
+------------------------------------------+
| 40bd001563085fc35165329ea1ff5c5ecbdbbeef |
+------------------------------------------+

4.encode()

encode() 加密

decode() 解密

mysql> select decode(encode('xufen','key'),'key');
+-------------------------------------+
| decode(encode('xufen','key'),'key') |
+-------------------------------------+
| xufen                               |
+-------------------------------------+
mysql> select aes_decrypt(aes_encrypt('root','key'),'key');
+----------------------------------------------+
| aes_decrypt(aes_encrypt('root','key'),'key') |
+----------------------------------------------+
| root                                         |
+----------------------------------------------+

6）控制流函数

1. case 值 when … then …when…then…else … end

mysql> select * from aaa;
+------+-----+
| name | sex |
+------+-----+
| 李四 |   1 |
| 王五 |   2 |
| null |   0 |
+------+-----+
mysql> select name as 姓名,case sex when 1 then '男' when 2 then '女' else '未知' end as 性别 from aaa;
+------+------+
| 姓名 | 性别 |
+------+------+
| 李四 | 男   |
| 王五 | 女   |
| null | 未知 |
+------+------+

2.if(expr1,expr2,expr3)

若expr1为真，则返回expr2，否则返回expr3

类似于三目运算符

mysql> select if(1,1,0);
+-----------+
| if(1,1,0) |
+-----------+
|         1 |
+-----------+
mysql> select name,if(sex=2,'优先','等待') as vip from aaa;
+------+------+
| name | vip  |
+------+------+
| 李四 | 等待 |
| 王五 | 优先 |
| null | 等待 |
+------+------+

3.ifnull(expr1,expr2)

若expr1不为null，则返回expr1,否则返回expr2

mysql> select ifnull(0,1);
+-------------+
| ifnull(0,1) |
+-------------+
|           0 |
+-------------+
1 row in set (0.00 sec)

mysql> select ifnull(NULL,'YES');
+----------------+
| ifnull(NULL,1) |
+----------------+
|            YES |
+----------------+

4.nullif(expr1,expr2)

若expr1=expr2,则返回NULL，否则返回expr1

mysql> select nullif('1','2');
+-----------------+
| nullif('1','2') |
+-----------------+
| 1               |
+-----------------+

mysql> select nullif('2','2');
+-----------------+
| nullif('2','2') |
+-----------------+
| NULL            |
+-----------------+

7）格式化函数

1.format(x,y)

把x格式化为以逗号隔开的数字序列，y是结果的小数位数

mysql> select format(99999999.34342222,3);
+-------------------------+
| format(99999999.3434,3) |
+-------------------------+
| 99,999,999.343          |
+-------------------------+

2.date_format(date,fmt)

mysql> select now(),date_format(now(),'%y/%m/%d %r');
+---------------------+----------------------------------+
| now()               | date_format(now(),'%y/%m/%d %r') |
+---------------------+----------------------------------+
| 2022-06-16 00:04:01 | 22/06/16 12:04:01 AM             |
+---------------------+----------------------------------+

mysql> select date_format(19990101,'%y-%m-%d');
+----------------------------------+
| date_format(19990101,'%y-%m-%d') |
+----------------------------------+
| 99-01-01                         |
+----------------------------------+

mysql> select date_format(now(),'%h:%i %p');
+-------------------------------+
| date_format(now(),'%h:%i %p') |
+-------------------------------+
| 12:06 AM                      |
+-------------------------------+

3.time_format()

mysql> select time_format(now(),'%r');
+-------------------------+
| time_format(now(),'%r') |
+-------------------------+
| 12:12:26 AM             |
+-------------------------+

4.inet_aton(ip)

返回ip地址的数字表示

mysql> select inet_aton('192.168.1.1');
+--------------------------+
| inet_aton('192.168.1.1') |
+--------------------------+
|               3232235777 |
+--------------------------+

5.inet_ntoa(num)

返回数字所代表的IP地址

mysql> select inet_ntoa(3232235777);
+-----------------------+
| inet_ntoa(3232235777) |
+-----------------------+
| 192.168.1.1           |
+-----------------------+

8）类型转换函数

1.cast()

把一个值转化为指定的数据类型，类型有binary,char,date,time,datetime,signed,unsigned

mysql> select cast(now() as signed integer),curdate();
+-------------------------------+------------+
| cast(now() as signed integer) | curdate()  |
+-------------------------------+------------+
|                20220615234814 | 2022-06-15 |
+-------------------------------+------------+

9）系统信息函数

1.user()

返回当前用户及所在主机

mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
mysql> select system_user();
+----------------+
| system_user()  |
+----------------+
| root@localhost |
+----------------+

2.database()

返回当前正在操作的库名

mysql> select database();
+------------+
| database() |
+------------+
| db1        |
+------------+

3.version()

返回数据库版本

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.5.16    |
+-----------+

4.connection_id()

返回当前客户的连接id

mysql> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
|               6 |
+-----------------+

5.found_rows()

返回最后一个select查询进行检索的总行数

mysql> select found_rows();
+--------------+
| found_rows() |
+--------------+
|            3 |
+--------------+

九、SQL优化

1）基本写法优化

  1. 少使用select * ,尽量使用具体字段；
  2. 对于条件来说等号之类两边的字段类型要一致，字符串不加单引号索引会失效；
  3. 尽量少使用Order by 排序，对于需要多个字段进行排序的可以使用组合索引；
  4. 对于groud by语句要先过滤后分组；
  5. 在查询时减少使用null，对字段有多个null的可以加默认值；
  6. 少使用like，对于需要使用的，如需要使用尽量用like abc%这种，不要把%放字段前面；
  7. 在where后面少使用函数或者算数运算；
  8. 去除的distinct过滤字段要少，避免distinct *；
  9. 不要超过5个以上的表连接。

2）建立使用合适索引

  1. 对于高频筛选字段可以适当的建立索引；
  2. 一个表的索引最好不要超过5个，多了会影响插入修改；
  3. 不要对值是有限重复的字段建立索引，如性别等；
  4. 使用组合索引一定要遵守最左原则。

3）替代优化

  1. 不要使用not in 和<>，这个会破会索引，not in 可以用not exists来代替，<>可以分成两个条件>或者|<等；
  2. 使用连接(join)来代替子查询；

十、 案例

1）按周统计加班时间

mysql> select * from jiaban;
+------+------------+
| num  | dt         |
+------+------------+
|    5 | 2022-03-10 |
|    6 | 2022-03-11 |
|    7 | 2022-03-12 |
|    8 | 2022-03-13 |
|    9 | 2022-03-14 |
|   10 | 2022-03-15 |
|   11 | 2022-03-16 |
|   12 | 2022-03-17 |
|   13 | 2022-03-18 |
|   14 | 2022-03-19 |
|   15 | 2022-03-20 |
|   16 | 2022-03-21 |
+------+------------+
mysql> select sum(num),week(dt) as week from jiaban group by week;
+----------+------+
| sum(num) | week |
+----------+------+
|       18 |   10 |
|       77 |   11 |
|       31 |   12 |
+----------+------+

十一、其他

1)保存操作记录

mysql> tee C:\Users\LQN\Desktop\Temp\0525.sql
Logging to file 'C:\Users\LQN\Desktop\Temp\0525.sql'
mysql> #把敲的sql及结果都输出到一个sql文件里
mysql> #便于溯源

2)数据类型

1.整型

类型大小参数范围
tinyint1unsigned/zerofill0-255/-128-127
smallint2unsigned/zerofill0-65535/-32768-32767
mediumint3unsigned/zerofill
int4unsigned/zerofill
bigint8unsigned/zerofill

2.浮点型

类型大小描述
float(M,D)4/8M叫“精度”，代表“总位数”
decimal(M,D)D是“标度”，代表“小数位”
mysql> alter table student add money float(7,2) default 0;

3.字符串型

类型描述长度
char定长类型尾部空格补够长度0-255
varchar变长类型有1-2个字节来标志该列的内容长度0-65535
text文本类型搜索速度慢
blob二进制类型存储图像和音频等二进制信息

4.日期/时间类型

类型说明标准格式范围
date日期YYYY-MM-DD1000-01-01到9999-12-31
time时间HH:MM:SS
datetime日期时间YYYY-MM-DD HH:MM:SS
year年份类型YYYY1901-2155
timestamp时间戳默认插入数据的 当前时间

3)NULL

  • null是空类型，比较时只能用专门的 is null 和 is not null 来比较
  • 效率不高，影响提高所有效果
  • 建表时往往用 not null default ‘’ 或者 not null default 0
mysql> insert into aaa
    -> values
    -> (NULL,NULL);
mysql> select * from aaa;
+------+------+
| name | sex  |
+------+------+
| 李四 | 女   |
| 王五 | 男   |
| null | 女   |
| NULL | NULL |
+------+------+
mysql> select * from aaa where name = null;
Empty set (0.00 sec)

mysql> select * from aaa where name != null;
Empty set (0.00 sec)

mysql> select * from aaa where name != 'null';
+------+------+
| name | sex  |
+------+------+
| 李四 | 女   |
| 王五 | 男   |
+------+------+
mysql> select * from aaa where name = 'null';
+------+------+
| name | sex  |
+------+------+
| null | 女   |
+------+------+
mysql> select * from aaa where name is null;
+------+------+
| name | sex  |
+------+------+
| NULL | NULL |
+------+------+
mysql> select * from aaa where name is not null;
+------+------+
| name | sex  |
+------+------+
| 李四 | 女   |
| 王五 | 男   |
| null | 女   |
+------+------+

4)PHP

<?php
$conn=mysql_connect('localhost','root','123456');
$sql='use test';
mysql_query($sql,$conn);

$sql='set names utf8';
mysql_query($sql,$conn);
    
$sql='select goods_name,cat_id,shop_price from goods';
$rs=mysql_query($sql,$conn);

$list=array();
while($row=mysql_fetch_assoc($rs)){
    $list[]=$row;
}
//print_r($list);exit;
?>
<!DOCTYPE html>
<html lang="zh-CN"
<head>
<title>打印表单</title>
<script type="text/javascript"></script>
<style type="text/css"></style>
</head>
<body>
    <h1>
        做个报价单
    </h1>
    <table border="1">
        <tr>
        <td>商品名称</td>
        <td>商品所属类目</td>
        <td>商品价格</td>
        </tr>
        <?php foreach ($first as $v) { ?>
    	<tr>
            <td><?php echo $v['goods_name'];?></td>
            <td><?php echo $v['cat_id'];?></td>
            <td><?php echo $v['shop_price'];?></td>
        </tr>
		<?php } ?>
    </table>
</body>
</html>
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值